An invoice register is generally a fairly boring report. You have rows and rows of detail data without any clear trends. But by using the following steps, you can quickly generate a chart of the top five customers in the invoice register in Microsoft Excel.
Start with a typical invoice register as shown in Figure 1.
Select one cell in the customer column. Use the AZ button to sort the customers in alphabetical sequence.
Select one cell in the invoice register. On the Data tab, choose Subtotals.
In the Subtotals dialog box, open the first drop-down and choose Customer. Make sure the Function drop-down is set to Sum, and your revenue column is checked in the field list at the bottom.
Click OK to add the subtotals as shown in Figure 4. Note that the Subtotals command added three new Group and Outline buttons to the left of column A.
Click the #2 Group and Outline button to show only the customer rows. Initially, the customers are arranged in alphabetical order. Click one cell in the revenue column and use ZA to sort the data so the largest customers are at the top.
Select the top five customers. Use the chart icons on the Insert tab to choose either a column or bar chart. Once you have the chart, you can customize using the Chart Tools tabs in the ribbon.
A default setting in Excel charts is that data in hidden rows are not shown in the chart. Although the top five customers might take up 300 rows in the invoice register, since only five rows are visible below the headings, your chart will only show five points.
Bill Jelen, aka "MrExcel," has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.
This was first published in September 2013