To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.
Every company is swimming in data. Your financial-analysis team undoubtedly has many Excel workbooks with thousands of rows of detailed invoice data. Using the techniques in this article, you can turn that ugly data into a useful report using just a dozen mouse clicks. While the first six clicks create a ho-hum pivot table, it is the second half-dozen clicks that provide the magic.
Find a Suitable Data Set
You should have an invoice register or similar data. There should be no blank rows or blank columns, and you do not want totals or subtotals in the data. You can use any number of columns, but you want to have one column that contains an invoice date and another column that contains revenue. Ideally, the data set will include two (or more) years of data.
First Six Clicks Create a Pivot Table
Select any cell in the data set. In Excel 2003 or older, choose Data, Pivot Table, or Pivot Chart Report, and click Finish. In Excel 2007 or newer, choose Insert, Pivot Table, and click OK. You will now have a blank pivot table.
Add your date field to the Row Labels area of the pivot table. Add the revenue field to the Data area (Excel 2003) or the Values area (Excel 2007). You will now have more than 500 rows of daily dates running down the left side of the report.
Magic Six Clicks Roll Up to Months and Years
Click on one of the cells that contain a date in the left column of your pivot table. In Excel 2007 or Excel 2010, choose the Group Field icon from the PivotTable Tools Options tab of the ribbon. In Excel 2003, right-click the date cell and choose Group and Show Detail, Group. You will now see the Grouping dialog box offering to let you group to Seconds, Minutes, Hours, Days, Months, Quarters, or Years. Choose both Months and Years as shown in Figure 1. Click OK.
You will now have a report with years in column A, months in column B, and revenue in column C. In a clever move, Microsoft has added a new “Years” field to the Pivot Table Field List.
Drag this field to the Column area in order to compare last year to this year as shown in Figure 2.
Bill Jelen is the author of 32 books, including Pivot Table Data Crunching, and is the host of MrExcel.com. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.