Your infographics are only as good as your story, and your data is only as useful as the value of the insights you get from it. You don’t have to be a power user to make a powerful point, but you need Excel skills to analyse your data, and organise your spreadsheets.A good, clean dataset that’s got all its cells in a row is pretty damn vital when it comes to making an infographic. We asked Aidan Corbett, CEO of Virtuoso [Update! Virtuoso is now Kubicle] to give us some tips on effective data manipulation in Excel, and he’s great, so he did.
Tip 1: Arrange your data records in rows, not columns
First, the basics. Excel expects data records to be arranged in rows, not columns. Following this convention will make sorting, formatting and filtering data much easier. The first row of cells should contain the headings of each column (e.g. Date, Address, Email address). Below this, arrange one data record on each row. Don’t worry about running out of rows — there are more than a million of them available on each sheet.
Tip 2: Add and delete rows, not cells.
If you want to add or delete data records, the safest way to do this is to add or delete full rows. Moving cells or groups of cells can easily create errors in your dataset. This is the kind of dead reckoning mistake that can skew your whole analysis. Don’t do it! Move the whole row (or save the original dataset separately, which is a good idea anyway) if you don’t want to lose the content.
Tip 3: Use Sort and Filter to search for relevant data.
Sort and Filter allow you to quickly search for data using dropdown buttons on each column. First select the full dataset (including the headings), then go to the ‘Data’ tab in the ribbon and click on the ‘Filter’ option. To remove filters, just press the ‘Clear’ button button in the same tab.
Tip 4: Change the format of your data.
Sometimes data columns are not presented in the ideal format. For example, I might want to add a $ sign and comma separators to a revenue column. To make changes to your data format, select the column of data to be changed and then press the arrow button in the bottom righthand corner of the ‘Number’ section of the ‘Home’ tab. This displays the Format Cell dialog box, which will allow you format your column in a myriad of ways.
Tip 5: Remove the gridlines.
I find that removing the gridlines on my worksheet often makes data easier to read. To do this, simply go to the View tab on the Ribbon and uncheck the ‘Gridlines’ box in the ‘Show’ section of the ribbon.
Tip 6: Use different colours for formulas and raw data.
Some datasets combine raw data with formulas (e.g. Quantity x Price = Total revenue). If your dataset contains columns of formulas, I recommend changing the colour of these numbers to distinguish them from the raw data.
Tip 7: Format your data as a Table
Excel allows you to format your data as a Table. Tables add great format styles to your dataset and automatically include filter buttons on top of each column. Simply select the full dataset (including the headings) and click the ‘Format as Table’ button in the ‘Home’ tab of the ribbon.
Tip 1: Arrange your data records in rows, not columns
First, the basics. Excel expects data records to be arranged in rows, not columns. Following this convention will make sorting, formatting and filtering data much easier. The first row of cells should contain the headings of each column (e.g. Date, Address, Email address). Below this, arrange one data record on each row. Don’t worry about running out of rows — there are more than a million of them available on each sheet.
Tip 2: Add and delete rows, not cells.
If you want to add or delete data records, the safest way to do this is to add or delete full rows. Moving cells or groups of cells can easily create errors in your dataset. This is the kind of dead reckoning mistake that can skew your whole analysis. Don’t do it! Move the whole row (or save the original dataset separately, which is a good idea anyway) if you don’t want to lose the content.
Tip 3: Use Sort and Filter to search for relevant data.
Sort and Filter allow you to quickly search for data using dropdown buttons on each column. First select the full dataset (including the headings), then go to the ‘Data’ tab in the ribbon and click on the ‘Filter’ option. To remove filters, just press the ‘Clear’ button button in the same tab.
Tip 4: Change the format of your data.
Sometimes data columns are not presented in the ideal format. For example, I might want to add a $ sign and comma separators to a revenue column. To make changes to your data format, select the column of data to be changed and then press the arrow button in the bottom righthand corner of the ‘Number’ section of the ‘Home’ tab. This displays the Format Cell dialog box, which will allow you format your column in a myriad of ways.
Tip 5: Remove the gridlines.
I find that removing the gridlines on my worksheet often makes data easier to read. To do this, simply go to the View tab on the Ribbon and uncheck the ‘Gridlines’ box in the ‘Show’ section of the ribbon.
Tip 6: Use different colours for formulas and raw data.
Some datasets combine raw data with formulas (e.g. Quantity x Price = Total revenue). If your dataset contains columns of formulas, I recommend changing the colour of these numbers to distinguish them from the raw data.
Tip 7: Format your data as a Table
Excel allows you to format your data as a Table. Tables add great format styles to your dataset and automatically include filter buttons on top of each column. Simply select the full dataset (including the headings) and click the ‘Format as Table’ button in the ‘Home’ tab of the ribbon.
Want to know more about data analysis, beyond the basics? Our friends at Kubicle can help. We like the videos because they’re simple, straightforward, and in bite-sized chunks. Kubicle's online videos help individuals and companies develop advanced Excel and PowerPoint skills, with content tailored specifically for business, and developed by experts who’ve worked for top-tier management consultancies and multinationals.
And don’t worry, they have the basics, too, for those of us who know our Excel skills are holding us back when it comes to making sexy infographics. Including an intro to statistics, for those of us who may or may not have slept through this class in college because we were art history majors (oops). Find out more from thekubicle.com.