• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Include Grand Totals in Pivot Charts

You are here: Home / Excel Charts / Include Grand Totals in Pivot Charts
Include Grand Totals in Pivot Charts
May 6, 2019 by Mynda Treacy

If you work with PivotTables, then you’ve probably found that you can’t include grand totals in Pivot Charts, or subtotals for that matter.  And if you’ve ever created a stacked column chart then you’ll have likely wanted to include grand totals as column labels, like this:

include grand totals in pivot charts

And then remembered you can’t.

One workaround is to create a regular chart from a PivotTable, then you can include the Grand Totals in the source data range.

Another option is to use CUBE functions to connect to the PivotTable source data. The nice thing about CUBE functions is you can get the PivotTable to create them for you and they can retain connectivity to Slicers. That’s right, you don’t even need to learn how to write CUBE formulas!

My data, shown below, is formatted in an Excel table called, Table1. It’s product sales by date and region:

product sales by date and region

Download Workbook

Download the workbook and follow along:

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Automatically Create CUBE Formulas

The key to CUBE formulas is that your data needs to be referenced from Power Pivot*, aka the Data Model.

*Power Pivot is available in for Excel 2010, Excel 2013/2016 Office Professional Plus, Office 2016 Professional, any version of Excel 2019 or Office 365, or the standalone edition of Excel 2013/2016. Click here for the full list.

Step 1: Load data to the Data Model

Insert a new PivotTable and at the dialog box check the ‘add this data to the Data Model’ box:

add this data to the Data Model

Note: Excel 2010 users will need to load the data to Power Pivot via the Power Pivot tab > Add Linked Table. Then from the Power Pivot window Home tab > Insert PivotTable.

Step 2: Build the PivotTable

Create the PivotTable that will support your Pivot Chart. I like to insert a chart at the same time to make sure the PivotTable layout is going to result in a chart that looks the way I want.

I’m using a stacked column chart, therefore I need the series names in the column labels and the dates in the rows, so they form my horizontal axis labels:

stacked column chart

Tip: My dates are grouped into years and months: to do this, right-click the date in the PivotTable > Group.

Optional: If you’d like to filter your chart with a Slicer, insert it now. I’ve inserted a Slicer for the Region field. More on this in step 6.

Step 3: Convert the PivotTable to CUBE Formulas

Select any cell in the PivotTable > PivotTable Analyze tab > OLAP Tools > Convert to Formulas:

convert to formulas

If you inspect the cells in what was your PivotTable, you’ll see they’re now CUBE formulas, as shown below:

CUBE formulas

The CUBE formulas are directly referencing the Data Model. Any changes to the Power Pivot Data Model will be reflected in the CUBE formulas.

Step 4: Insert a Regular Chart

Now that you’ve converted the PivotTable to CUBE formulas you can insert a regular chart i.e. not a PivotChart. I’m using a stacked column chart.

Step 5: Format the Chart

The Grand Total value is the top segment of the stacked column chart. We need to hide this, but first let’s select the grand total series and add Data Labels > Inside Base:

add data labels to inside base

Next, with the grand total series still selected go to the Format tab > Shape Fill > No Fill

select format tab then select shape fill then no fill

Hide the gridlines and vertical axis, and place the legend at the top (be sure to delete the legend entry for ‘Grand Total’; select it in the legend and press the Delete key):

legend at the top

Step 6: Connect CUBE Formulas to Slicers (Optional)

My data set allows me to filter the data by region and I’m going to do this with the Slicer I inserted in step 2.

First, right-click the Slicer > Slicer Settings and find its ‘name to use in formulas’:

connect CUBE formulas to slicers

Now, edit the CUBE formulas in the values area to include the Slicer ‘name to use in formulas’ in the next ‘member_expression’ argument, like so:

=CUBEVALUE("ThisWorkbookDataModel",$F$17,$G19,H$18,Slicer_Region)

Be sure to copy the formula to all the values area cells:

copy formula to all the values area cells

Step 7: Format and Arrange Slicer and Chart

Now all that’s left is to align the Slicer to the chart and make it look nice:

format and arrange slicer and chart

Notice the Slicer acts as a header for the chart and informs the user what regions it relates to without the need for an extra heading.

Tip: This CUBE formula technique also works with PivotTables based on an OLAP data source like SSAS Cubes.

Learn Power Pivot

Power Pivot is hugely versatile and enables you to work with a lot of data and or, data spread across multiple tables. If you’d like to learn Power Pivot please consider my Power Pivot course. There is more information on what Power Pivot can do on the course page linked to above.

Include Grand Totals in Pivot Charts
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Charts Posts

interactive python charts in excel

Interactive Python Charts in Excel

How to build interactive Python Charts in Excel and regular charts with Slicers connected to data from Power Query.
Circle Progress Charts

Excel Progress Circle Charts

How to easily create dynamic Excel Progress Circle Charts, using doughnut charts and some wizardry, including Slicers to change the data.
professional vs amateur chart formatting

Pro Excel Chart Formatting

10 tell-tale signs that show you’re a chart amateur and the Excel chart formatting you should use instead.

Excel Scroll and Sort Table

Excel scroll and sort table using dynamic array formulas is far simpler than the old approach which required multiple tables, formulas and helper columns.
picture fill excel charts

Picture Fill Excel Charts

Using a stylish picture fill in your Excel Charts is a simple way to make your data visualizations more captivating and memorable
excel speedometer charts

Excel Speedometer Charts

How to build Excel Speedometer Charts or Gauge Charts as they're also know, why they are BAD and what to use instead.
burn up burn down charts

Excel Project Management Burn Down and Burn Up Charts

Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. They are useful for monitoring the progress of a project.
wee people font charts

Excel WeePeople Font Charts

Excel WeePeople Font Charts are a nice change from generic shapes for waffle charts, bar/column charts and more.
excel dot map charts

Excel Dot Map Charts

Interactive Excel dot map charts are not built-in, but with some creative use of Excel’s built-in tools we can create something unique.
Excel S Curve Charts

Excel S-Curve Charts

Easy Excel S-curve Charts made with PivotTables for project management. Track progress by including budget amounts.


Category: Excel Charts
Previous Post:IF Formula BuilderIF Formula Builder
Next Post:Power Query Get Data from Web by ExamplePower Query Get Data from Web by Example

Reader Interactions

Comments

  1. yl wong

    September 16, 2020 at 11:29 pm

    Dear Mynda,

    Recently I’m faced with the same issue.
    I want to share the way I solved it, keeping within the orbit of PIVOT Tbl & Chart.
    Use a Calculated Item as Grand Total on a stacked Column PivotChart.

    Change the Chart Type of the Calculated Item to a Line Chart, add Labels and Format the Line to NONE.

    Reply
    • Mynda Treacy

      September 17, 2020 at 8:59 am

      Nice! Thanks for sharing 🙂

      Reply
  2. Nicolas Matzinger

    March 27, 2020 at 2:45 am

    Hi
    That works great for the Grand Total at the top right position of the pivot table.
    How can I include a stacked column bar in the pivot chart for the Grand Total of all shown months just next to the “Jul” bar (let’s name that “All Months”)?

    Thanks a lot
    Nico

    Reply
    • Mynda Treacy

      March 27, 2020 at 9:19 pm

      Hi Nico,

      It can’t be done with a Pivot Chart. You’d have to create a regular chart that references PivotTable data.

      Mynda

      Reply
      • Richard harris

        May 26, 2020 at 6:11 am

        Actually, you can make this work with two charts; one being a transparent overlaying the pivot chart.

        I created a regular stacked pivot chart; then overlayed a normal line chart using name formulas to provide the values.

        Total named formula =OFFSET(A5,1,MATCH(“Grand Total”,$A$5:$F$5,0)-1,COUNTA($A:$A)-5,1)

        (My table was in A5, and my grand total would move between columns B & F depending on the slicer selection). This gives me an array of the values.

        I used an index name formula = OFFSET(A6,0,0,COUNTA($A:$A)-5,1) to get the primary row name

        Plotted these two items on a regular linen chart, changed background transparency and voila…. no cubes (and works on Mac and Windows)…..

        Reply
        • Mynda Treacy

          May 26, 2020 at 9:04 am

          Yep, that’s another great way to achieve this result. My concern with using two charts is that the vertical axis might get out of sync between the two charts and sometimes the charts can be rendered differently on users’ PCs that have different screen resolution etc. I’m always wary of these potential issues and make sure I put measures in place by using a ghost series to fix chart axis heights, and test the chart appearance on multiple machines before distributing my reports.

          Reply
          • Richard Harris

            May 29, 2020 at 3:54 am

            For my example, the range of values are within a known range – so I set my axis height manually.

            I’ll have to watch for the screen resolution one though.

            Btw, love your videos – gives me lots of new ideas!!

          • Mynda Treacy

            May 29, 2020 at 8:45 am

            Great to hear, Richard 🙂

  3. Justin

    March 14, 2020 at 1:49 pm

    This chart method saves a lot of time. However, i am facing challenges when data is added for subsequent months. Since the table with Cube formula is in range, it doesn’t capture any new data added for new month automatically. Is there any solution that can solve this please?

    Reply
    • Mynda Treacy

      March 14, 2020 at 3:15 pm

      Hi Justin,

      You can use a dynamic named range for your chart series so that any new rows/columns added to your cube formula table are automatically included in the chart. If you get stuck please post your question on our Excel forum where you can also upload a sample file.

      Mynda

      Reply
  4. Eric

    February 20, 2020 at 4:19 am

    This works in one year, but if I have 2 years of data grouped by year first, then per month, the bars are messed up. Any alternatives?

    Reply
    • Mynda Treacy

      February 20, 2020 at 11:32 am

      Hi Eric,

      Is your chart data laid out as shown in step 6, with the year in one column, then the month and so on? If so, it shouldn’t matter how many years you have in your chart. Perhaps you can post your question in our Excel forum where you can upload a sample Excel file and help you further.

      Mynda

      Reply
  5. Arti Jadhav

    June 26, 2019 at 2:55 pm

    After connecting to Slicer : putting slicer name in “Cubevalue” formula I will get #value error,
    Please help to solve this.

    Reply
    • Catalin Bombea

      June 27, 2019 at 1:45 pm

      Hi Arti,
      Try to create a pivot table and use the same fields and slicers as in your formula, this way you will see if any measure fails.

      Reply
      • Kevin Peterson

        February 14, 2020 at 3:52 am

        This answer doesn’t seem to fit with the question. When I follow the instructions above I too received the $value error. There is a step that I’m missing.

        Reply
        • Catalin Bombea

          February 15, 2020 at 5:20 pm

          Hi Kevin,
          You have to make sure your measures work, if they return error values, the CUBEVALUE formula will obviously not work.
          Can you please upload a sample file with your attempts so we can see what’s wrong? Use our forum, you can create a new topic after sign-in.

          Reply
          • Kevin Peterson

            February 16, 2020 at 3:22 am

            Unfortunately my data has an extra complication / filter. My Column headers are based on a measure. I think that is then creating the #value error.

          • Catalin Bombea

            February 17, 2020 at 1:35 pm

            Might be, can’t say without seeing a sample file.

        • Chao

          August 13, 2021 at 2:29 am

          I found out that for the slicers to work, they can not be added to the “Columns” field of your pivot table. Like in the example, “Product” is under the column field, but not a slicer. “Region” is a slicer so that it cannot be added to the column field. That’s how i fixed the $value error.

          Reply
  6. Mark

    June 7, 2019 at 1:01 am

    It’s nice to see CUBE formulas getting a bit of usage – great work.

    Reply
    • Mynda Treacy

      June 7, 2019 at 8:48 am

      Thanks, Mark!

      Reply
  7. Marc Crawford

    May 10, 2019 at 6:17 am

    I sent questions about above chart but have since found the answers but thank you and very nice.

    Reply
    • Mynda Treacy

      May 10, 2019 at 12:26 pm

      You’re welcome, Marc.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.