The aim of this article is to show you how to turn a straightforward list of date/values into a pretty graph where we can easily compare values from previous years on a month by month basis.
First we need some data – here are some meter readings taken every month for the past two years. The third column (“units used”) contains a formula to calculate the difference between a reading and the previous reading to calculate the number of units used.
1. The first step is to highlight the data that you want to graph, select the Insert tab and click on PivotTable > PivotChart as shown below.
Tip: Include a range of empty cells at the bottom if you will be adding more values in the future as it means you won’t have to regenerate the graph every time.
2. The dialog box that appears will contain the range of cells you selected, and the default option is to insert the PivotChart into a new worksheet (which you can change to the existing worksheet if you want to). Click OK to proceed.
3. A simple bar chart will be added to the worksheet, and a pane will appear on the right of the screen containing the column names and a series of boxes. Ensure only the columns that are to be displayed in the graph are ticket (“Date” and “Units Used”) in our example.
4. Now comes the fancy part where we group the data by month and year.
a) Start by placing the cursor on the first date cell in the pivot table.
b) Select the Options tab under PivotTable Tools
c) Click Group Selection
5. In the dialog that appears, hold down the shift key and select both Months and Years then click on OK.
6. In the Pane of Pivot Options on the right hand side of the screen, drag the Years field from the Row Labels box to the Column Labels box.
7. At this point, you can see that the PivotTable has been updated to show the months down the side of the table, and each year in the data set will be shown in a separate columns. The PivotGraph has also been updated to display the data in groups, with multiple bars per month to indicate the year on year values.
8. The final step is to change the graph type from a bar graph to a line graph by selecting the Design tab under PivotChart Tools and then clicking on the Change Chart Type button.
9. The finished graph is shown below. You can style various elements of the graph individually, or you can select from the range of pre-built styles in the Design tab.