This article is part of an Essential Guide, our editor-selected collection of our best articles, videos and other content on this topic. Explore more in this guide:
3. - Accounting and finance Excel tips: Read more in this section
- Use the MATCH function to find the next largest value
- How to build a year-over-year report using a pivot table
- Rank the top five customers in an Excel invoice register
Explore other sections in this guide:
- 1. - Problems with using Excel for finance
- 2. - Moving from Excel to financial management applications
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.