The simple sheet below tracks units sold by personnel and date. Not too long ago, I showed you a conditional format formula that highlights the maximum and minimum values for each month. That’s just one of many ways you might need to analyze your data. For instance, you might need to know the latest date each person made a sale – that’s a common request.
Even if you sort the data by date or personnel, a formulaic solution will be complicated (and might take hours to build and test). The quickest solution is to avoid that approach and use a pivot table as follows:
- Select the data range by clicking any cell in the range and pressing [Ctrl]+[Shift]+8.
- Click the Insert tab and choose PivotTable from the PivotTable dropdown in the Tables group. In Excel 2003, choose PivotTable and PivotChart Report from the Data menu to launch the PivotTable wizard, which will walk you through the process. The remaining instructions are for 2007 and 2010 users.
- In the resulting dialog, accept the default settings by clicking OK without making any changes.
- From the task pane on the right, drag the Personnel field to the Row Labels list (at the bottom-right).
- Drag the Date field to the Values list.
- From the Count Of Date dropdown, choose Value Field Settings.
- In the resulting dialog, select Max on the Summarize Values By tab, and click OK.
- Select the dates in column B (B4:B7) and choose Short Date from the Number Format dropdown in the Number group (on the Home tab).
- To hide the grand totals row, click the contextual Design tab and choose Off For Rows And Columns from the Grand Totals dropdown in the Layout group.
A pivot table won’t always be adequate, but unless you must identify each subset’s latest entry within the context of all the records, the pivot table just might be the quickest and easiest solution.
To display the first entry of each subset, choose Min instead of Max in step 8 above.
