Look Who it Was Random video

Excel - Excel Chart of Just Top 5 Customers - Episode 762

587 views · Published 5 February 2009 · 2:13 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Chart the top customers.

Welcome back to the MrExcel netcast! In today's video, I'll be sharing a cool trick that will help you create a chart of your top customers in Excel. Our data set consists of transactional data and our manager has requested a graph of the top 5 customers. While this may seem like a daunting task, it's actually quite easy with a few simple tricks.

The first step is to sort the data by customer. To do this, simply select one cell in the CUSTOMER column and click the AtoZ button. Next, we'll add subtotals by going to DATA, SUBTOTALS, and selecting "At each change in customer". We'll use the SUM function to add up the revenue for each customer and click OK.

Once the subtotals are added, we'll collapse the data to the number 2 view and sort the revenue column in descending order by choosing one cell and clicking SORT DESCENDING, ZtoA. This will give us a list of customers in descending order by revenue. Now, to select the top 5 customers, we'll use the ALT+; shortcut. This will select only the visible cells and not the hidden detail rows.

With the top 5 customers selected, we'll press F11 to create a chart. And just like that, Excel will give us a chart with our top 5 customers - Walmart, General Motors, Exxon, Ford, and Citigroup. This same trick can be used to create charts for the top 10 or 20 customers, but be careful as there are limitations when the selection becomes too large for the series formula.

I hope you found this trick helpful in creating charts for your top customers. Thanks for watching and don't forget to subscribe to the MrExcel netcast for more useful tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Introduction
(00:19) Sorting by customer
(00:29) Adding subtotals
(00:39) Collapsing to number 2 view
(00:50) Sorting descending by revenue
(01:00) Selecting top 5 customers
(01:10) Using ALT+; shortcut
(01:21) Creating chart with top 5 customers
(01:33) Limitations of the trick
(01:51) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding subtotals in Excel
Creating a chart in Excel with top 5 customers
Limitations of selecting a large number of non-contiguous cells in Excel
MrExcel netcast tutorial on creating charts from transactional data
Producing top 10 or top 20 charts in Excel
Selecting visible cells in Excel using ALT+;
Sorting data by customer in Excel
Sorting data in descending order in Excel
Tips for using the ALT+; shortcut in Excel
Tricks for working with large data sets in Excel
Using F11 to create a chart in Excel
YouTube tutorial on creating charts from transactional data

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152133/



A combination of six cool tricks allow you to take detailed transactional data and produce a chart of the top five customers in about 12 clicks. Episode 762 shows you how.

More from this channel