I spent last week with a lovely company doing some Intermediate Excel training for their staff. They were particularly interested in the different ways you could analyse a set of data – Excel 2007 has some different options so I thought a general summary might be useful.
Graphs work really well when you need to present data. Don’t forget you can select data that is not next to each other by selecting one set, then holding the CTRL key on the keyboard and selecting the second set, etc. Do make sure that you select the same number of items though or it might go horribly wrong!
Excel 2007 has a facility to format a list of data as a table. This automatically adds filters to the column headings and you can add a total row to the bottom by clicking the Total Row option in the Design Tab (appears when you click on the Table). What is particularly good is if you have a table from Columns A to C, but then add a function in column D which uses cells in A to C, Column D is automatically included in the table and the function is copied to all relevant rows.
3. Conditional IF Statement
To keep track of my bank account, I use an Excel spreadsheet. Everytime I put something in or out of the account I write it on the Excel spreadsheet and keep a running total so I know how much is in the account at any one time. When I check the bank, I add a y next to any item that has registered in the bank account (OK, so I’m a bit annal!). I can then SUM all the totals that have a ‘Y’ next to them so I can quickly check whether my total matches the total from the bank. I do this using a SUMIF. (COUNTIF is also useful).
4. Conditional Formatting
If you have a set of numbers, it is a good idea to be able to see quickly whether certain numbers are better than or worse than expected. Conditional formatting has always been available in Excel 2003 – you can set numbers to be red if they are below a set number or blue if they are above, for example. In Excel 2007, you can use more complicated rules and you can use icons (flags, arrows, etc) to visually denote the results.
Set filters on a set of data to be able to only show the data that corresponds to set criteria. For example, a set of sales figures can be filtered to show sales by salesperson or area or department. Multiple criteria can be set, so show Sales by TOM in the NORTH or SOUTH.
6. Advanced Filters
You can use the same idea as above but you can set more complicated filters. Using advanced filters, you can show Sales by TOM in the NORTH and SAM in the SOUTH for example.
7. Pivot Tables
This is the traditional way people think of analysing data in Excel, but many find it difficult to understand. Although it is easier in Excel 2007 and 2010, it still forms part of my advanced course as I think there are easier ways of analysing data as described above.
Do let me know if you use any of these methods – or if there are other methods you prefer. Of course, do let me know if you need further information on the mechanics of the analysing!