Essential Guide

Ultimate Excel finance guide

A comprehensive collection of articles, videos and more, hand-picked by our editors
Q

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

Microsoft Excel consultant Bill Jelen, aka "MrExcel," demonstrates how to generate a chart of the top five customers in an invoice register.

FROM THE ESSENTIAL GUIDE:

Ultimate Excel finance guide

+ Show More

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.

This was first published in September 2013

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

Ultimate Excel finance guide

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchManufacturingERP

SearchSAP

SearchOracle

SearchBusinessAnalytics

SearchContentManagement

SearchDataManagement

SearchCRM

Close