If you use Excel for storing and reporting on your data, you are probably already a capable user. However, there are a few tricks that can make your data shine (metaphorically). These tips (written for Excel 2013 version) will help you to go from ‘familiar with Excel’ to ‘impresses colleagues with Excel skills’. Try them out, if you haven’t already. But remember: always save your data and make a back-up before trying anything new!
Excel for viewing data
We’ll start with something simple. If you have massive amounts of data, it’s helpful to format it in ways that make it easier to handle. Converting your data into a table will make it easier to read by colouring alternate rows in different shades. That can be a huge help when you’re going to be staring at that same data for months or years! Table formatting can also make it easier to apply actions to all your data at once.
To format data as a table, highlight the data (including the header row if you have one) and select Insert –> Table. You can then use the table design options on the top ribbon to customise your table. Give your table a name on the left-hand side to enable you to quickly refer to the table data when writing formulae. Hint: when creating your table, leave an extra blank row at the bottom. That way, any new rows you insert at the bottom will be included in the table area.
You can also apply data outlines to allow you to collapse or expand data for less or more detail as needed. If, for instance, you have data broken down by years, months, and days, you can create an outline to allow you to quickly view data by just the year while hiding the finer detail of months and days. Outline options are in the Data tab – just hit the drop-down arrow under ‘Group’ to apply an Auto Outline to your selected data. Then toggle the plus and minus buttons next to your rows/columns to show or hide details. You can remove the outline by pressing the drop-down arrow under ‘Ungroup’. (This doesn’t work for all types of data, so don’t stress if you can’t use the feature.)
Excel for reporting on data
Pivot tables and charts are your friends! They are a great way to save time when generating reports out of your data. If you haven’t used them before, you’re in for a treat. Probably the easiest way to learn how pivot tables and charts will work for your data is to have a play. Create a copy of your data (to preserve the original) and experiment.
You can create your pivot table by clicking Insert –> PivotTable and selecting the table or range containing your data. You’ll then get options to drag fields into any of four areas: Value, Column, Row, and Filter. This will determine how your data is laid out and summarised. The order of fields in each of these areas will affect the layout as well.
Pivot charts allow you to almost instantly generate charts based on the data in your pivot tables. If you are already experienced with regular Excel charts and graphs, using pivot charts will be quite intuitive. The value of using pivot charts, though, is that they allow you to narrow down a large data set with pivot tables before creating your charts or graphs.
Excel as a modelling tool
You can use Excel to figure out what impact a change in one or more conditions will have on an overall result. This is called a ‘What-If Analysis,’ and it works by quickly manipulating your data in different ‘scenarios’. You’ll need to have formulae set up to calculate a result based on a set of variables. From there, you can play around with the options in the Data tab by pressing the ‘What-If Analysis’ button. There are 3 kinds of what-if analyses:
Scenario manager enables you to compare multiple scenarios. You might have data on mortgages, and you want to test the impact on that data of a high or low interest rate, combined with rising or falling house prices. You can set up a range of scenarios to combine those factors (high interest / rising prices; high interest / falling prices; low interest / rising prices; low interest falling prices). Then you can flick between scenarios to quickly view the impact on your data.
Goal seek allows you to work backwards from a particular goal. If, for example, you want to figure out the population required for NZ to hit a particular level of tax revenue (keeping all other variables constant) you can use ‘Goal seek’ to set a goal for the value of the tax revenue, by changing the value in your population cell.
Data table works to substitute values into a formula to display different results. You can use either one-input tables to test the effects of one changing variable; or two-input tables to test two. This is much like using a formula and putting different inputs into the formula; but it’s generally quicker for you to see and also quicker for Excel to process!
There is also a brilliant add-in called ‘Solver‘ that allows you to optimise multiple variables for a given output, taking into account set constraints. Say you want to figure out the optimal conditions for getting the best fuel efficiency from a car. Fuel efficiency is impacted by lots of conditions, but you have to follow the rules of the road – for example, efficiency increases at higher speeds, but you can’t drive over certain speed limits. As long as you have a formula set up to calculate fuel efficiency based on your variables, you use the ‘Solver’ to figure out the best variables. Set a goal to ‘max’ the number in the fuel efficiency cell, after specifying your constraints. That will automatically adjust the variables to the optimal level to maximise fuel efficiency.
The Solver isn’t installed by default, so you’ll need to activate it by going to File –> Options –> Excel Options –> Add-ins –> Manage –> Excel Add-ins. Select the Solver Add-in box and click OK.
If you have any favourite Excel tips that have helped in your research, share them in the comments below!
