Excel Charts – How do you create a chart from a Data Subtotals sheet? This is how…

On a course last week, we had a good request from an attendee. They asked how to create a chart from a Data Subtotal sheet but only showing the subtotals and not the details. As it was such a good request, we decided to do this week’s blog on it!

Data Subtotals allow you to quickly add in subtotals and totals in to your data after you have sorted it in some order. The subtotals are then applied on each change in the sorted data, for example at each change in department or in location.

Below are the steps to firstly apply the Subtotals to the data and then to create the chart. Once you have read the steps, click on the button below to download the spreadsheet and give it a go!

Steps for creating Excel charts from a data subtotals sheet:

  1. Sort data into warehouse sequence (Data Sort)
  2. Use Data Subtotals to show the stock values summed by warehouse
  3. Choose Outline 2 (from left hand side)

Excel Charts - subtotals screen image

** Now the trick here is to show only visible cells, so press F5 and choose Special and select visible cells only – this has to be done for certain versions of Excel, but it doesn’t hurt to do it even if your version doesn’t require it to **

  1. Now select the warehouses and the stock values
  2. Press F11 to create a chart sheet. Choose to show Data Labels from Chart Elements (little + top right of chart)

Excel Charts - subtotal chart image

Download the Data Subtotals Spreadsheet here

We always enjoy having different requests or queries on our courses as they are often something that we haven’t thought about ourselves!

Quite a few of our hints and tips that are on our website have come from a query on a course whereas some are just from popular topics on our courses.

Liked this week’s hint and tip on creating Excel charts from Data Subtotals? Take a look at the others we have on the website here.

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We have placed cookies on your device to help make this website better.

You can use this tool to change your cookie settings. Otherwise, we’ll assume you’re OK to continue.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy