Excel tip: Charting the top five customers from an invoice register

Bill Jelen

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.

Excel invoice registerFigure 1

Select one cell in the customer column. Use the AZ button to sort the customers in alphabetical sequence.

Excel AZ buttonFigure 2

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.

Subtotals boxFigure 3

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.

Add SubtotalsFigure 4

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.

Excel ZA buttonFigure 5

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.

top five customersFigure 6

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.

View the next item in this Essential Guide: Using VLOOKUP in a range match or view the full guide: Ultimate Excel finance guide

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: