Microsoft Excel Powerhacks
1. Filter to understand your data
Use Advanced Filter or a quick PivotTable to generate a list of unique categories – this will expose any typos or inconsistencies in classification.
2. Save calculation time
If you’re dealing with a massive database, and asking Excel to perform many calculations, then you can hard-paste formulae once they’re calculated. This will significantly speed-up a workbook that is slow to calculate. Make sure you keep one row of the formula – so you can copy down to update the calculation when you add to or change your source data. And make sure you shade that row differently, and clearly label what you have done.
3. Use bins to get more from your data
If you’ve a field such as customer size, categorise the data records in bins to try and understand the characteristics of each type. In this example we’ve just allocated stores into ‘Small’ or ‘Large’ bins according to their revenue. But we could have more ‘bins’, and allocate stores to them using the MATCH() function. We can then use other data – staff numbers for instance – to understand the characteristics of each of our bins.
4. Preserve your source data
When you’re about to spend your weekend crunching through a data dump, do yourself a favour and make sure you can back-out, or at least understand, any changes you have made. To gain insight from data you will often need to clean it (for typos or inconsistent categorisations for instance). Keep a separate, unedited worksheet containing the original data, and make any cleaning edits to a copy. Make sure you highlight and document any changes made so you can do it again.
5. Format consistently
Don’t save good formatting ONLY for the results of your analysis. Instead, focus on your spreadsheet – and your work – as a whole. Deciding on a few rules for formatting will make your analysis more comprehensible, not least to yourself. For example, shade raw input data cells one colour, calculations another, parameters a third… use consistent formatting for headings, and for units, and for annotations. You can do this with Excel’s built-in themes, but it’s not necessary and arguably less transferable – more important that you get the rules straight in your head.
(Thank you to Excelwithbusiness for the info!)