• 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
    • SALE 20% Off All Courses
    • 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
    • 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
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Create Regular Excel Charts from PivotTables

You are here: Home / Excel Charts / Create Regular Excel Charts from PivotTables
Regular Charts from PivotTables
July 5, 2016 by Mynda Treacy

PivotTables are a great way to summarise your data, but PivotCharts can be a pain in the, um, neck.

PivotChart Restrictions


  • They’re not as customisable as a regular Excel chart
  • They only play nice with data from one PivotTable
  • PivotCharts aren't available for all chart types e.g. Sunburst, Scatter, Histograms, Waterfall and more.

In this post I'm going to show you 3 methods you can use to trick Excel into creating a regular chart based on a PivotTable, allowing you to have all the benefits of PivotTables with the flexibility of regular charts.

Download the Workbook

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.

Watch the Video

Subscribe YouTube

How to Create Regular Excel Charts from PivotTables

Method 1: Manual Chart Table

A while ago I showed you how to create Excel charts from Multiple PivotTables. And this is great if your data needs arranging into contiguous cells so it can be plotted as one series, or if the source data is inconsistent in the two PivotTables and needs organising first.

For example, the chart below consists of values from both the Actual and Budget PivotTables. Notice the sort order of the categories isn't the same in each PivotTable. By using GETPIVOTDATA to return the values from the respective PivotTables I don't need to worry about the order of the categories, even if the sort order in the PivotTables changes later.

create regular Excel charts from PivotTables using dynamic named ranges

Method 2: Dynamic Named Range

However, if the order of the data is identical in each PivotTable (and you can be certain it always will be), or if you only have one PivotTable, then you can skip the Manual Chart Table and simply reference the PivotTables using Dynamic Named Ranges. This is also useful if you're expecting the data to grow and you need the range to automatically expand to include the new data.

create regular Excel charts from PivotTables using dynamic named ranges

Note: I don’t recommend using the chart colours above. I used yellow and blue so you could more easily follow the data trail.

Tip: remove the Grand Total columns and rows from your PivotTable as you don’t need them in your chart and they will only interfere with your dynamic named ranges.

Dynamic Named Ranges in Charts

Once you’ve set up your Dynamic Named Ranges you need to insert them in your chart. I’ve set up the following dynamic named ranges:

  • chart_actual
  • chart_budget
  • chart_axis

Insert the Chart:

  1. Insert an empty chart by selecting any empty cell > Insert tab > Column Chart (or whatever chart type you want)
  2. Right-click the chart > Select Data
  3. In the legend entries side of the dialog box click ‘Add’:

    chart source dialog box

  4. I’ll add the Actual series first. In the Edit series dialog box, cell D4 contains my series name and my series values are the dynamic named range chart_actual. Note: you must prefix the dynamic named range with the sheet name enclosed in apostrophes and an exclamation mark on the end e.g. ‘2. Dynamic Ranges’!

    chart series dialog box

  5. Repeat for the Budget series
  6. Now add the dynamic named range for the axis labels by clicking ‘Edit’ under Horizontal Axis Labels:

    chart axis labels dialog box

Now you’re ready to format your chart as you wish.

And the best part is when you refresh your PivotTable and it expands/contracts your chart will automatically adjust, just like a Pivot Chart only better.

Method 3: Bait and Switch

This method works well with charts that can ignore empty cells, like the Treemap and Sunburst etc. It's a bit less work than the previous methods:

  1. Create a PivotTable containing the data for the chart and insert a Slicer if required.
  2. Copy and paste the PivotTable as 'values' in some empty cells adjacent to the PivotTable.
  3. Insert the chart based on the pasted cells from step 2.
  4. Edit the chart range to point back to the PivotTable cells.
  5. Delete the data you pasted in step 2.

Create Regular Excel Charts from PivotTables

Regular Charts from PivotTables

More Excel Charts Posts

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.
chart axis switch

Excel Chart Axis Switch

Use radio button form controls to create an Excel chart axis switch enabling you to toggle pannel charts between same axis and own axis.

Excel Charts with Shapes for Infographics

Excel Charts with Shapes for Infographic styling and increased interest in your charts. Easy to insert but there are a few tricks required.
excel pyramid chart

Excel Pyramid Charts

Excel Pyramid charts are useful for visualising demographic data across multiple categories. Let’s look at 3 ways we can build them in Excel.

Highlighting Periods in Excel Charts

Highlighting Periods in Excel Charts helps your users interpret them more quickly and or focus their attention on a point or area.
stacked bar waffle chart

Stacked Bar Excel Waffle Charts

Stacked Bar Excel Waffle Charts are an alternate to using conditional formatting to build waffle charts, and some say they're easier.




Category: Excel Charts
Previous Post:How to record a macro in ExcelHow to Record a Macro in Excel
Next Post:Excel ClipboardExcel Clipboard

Reader Interactions

Comments

  1. Jeff McClellan

    August 15, 2022 at 11:42 pm

    Thank you for this excellent turorials. I follow your series religiously and appreciate all that I’ve learned from you. However, I have a problem with this set of instructions for creating the manual table as described in the first option.

    When I change the reference in the Manual Table for “Revenue”, I get a #REF. What am I doing wrong?

    GETPIVOTDATA(“[Measures].[Revenue]”,$A$2,”[Calendar].[Year]”,”[Calendar].[Year].&[2018]”)
    GETPIVOTDATA(“[Measures].[Revenue]”,$A$2,”[Calendar].[Year]”,F3)

    A B C D E F G H I
    1 Pivot Manual Table
    2 Row Labels Revenue Profit GM% Year Revenue Profit GM%
    3 2018 $19,807,854 $10,212,533 59% 2018 #REF!
    4 2019 $19,376,476 $9,818,026 60% 2019
    5 2020 $16,822,609 $9,016,694 62% 2020
    6 2021 $21,163,994 $12,134,045 65% 2021
    7 2022 $18,812,588 $11,978,403 70% 2022
    8 Grand Total $95,983,521 $53,159,702 62%

    Reply
    • Jeff McClellan

      August 16, 2022 at 1:15 am

      Thank you, but I solved the problem. I needed to Concatenate the Year value into the GETPIVOTDATA formula like this:

      GETPIVOTDATA(“[Measures].[Revenue]”,$A$2,”[Calendar].[Year]”,”[Calendar].[Year].&[“&$F3&”]”)

      Reply
  2. Jon Peltier

    July 23, 2022 at 12:49 am

    Nice tutorial on a technique many people don’t know is possible. I’ve written a few related articles, years ago.

    A quick note on Names for a dynamic chart.

    In general you can edit the SERIES formula, and change cell references to Names right in the Formula Bar. But you will have trouble with names like “chart_axis” and “chart_actual”. If a name begins with C or R (or the corresponding first letters of a language’s words for column and row), Excel will refuse to accept these names in the Formula Bar. You can still add them in the Select Data Source dialog, as you demonstrate, but I much prefer to edit the formulas directly. I usually prefix the names of my Names with X_ or Y_, which not only reminds me they are defined for a chart, but also for which set of values they will be used.

    Reply
    • Mynda Treacy

      July 23, 2022 at 8:51 am

      Ooh, great tip, Jon! Thanks for sharing.

      Reply
  3. scott

    May 29, 2020 at 6:17 pm

    thanks for making this post as it deals with an issue I have been having trouble with today…having a chart run off a picot table that has sum calculations and formulas based on the count values that I needed to plot but was unable to.

    Reply
    • Philip Treacy

      May 31, 2020 at 10:36 am

      You’re welcome, glad it was helpful.

      Reply
  4. Yves

    May 26, 2020 at 7:31 pm

    Great post. Thanks a lot.
    Yves

    Reply
    • Mynda Treacy

      May 26, 2020 at 8:29 pm

      Glad it was helpful, Yves 🙂

      Reply
  5. Andrew Labib

    May 22, 2020 at 3:25 am

    Hi Mynda,

    Really, wonderful blog, I have learned a lot from your videos and I have found a lot of solutions that helped me a lot, really thanks.

    my question concerning this topic above is, How can I add a dynamic % labels like :
    – actual sales vs. budget
    – actual sales vs. actual last year per product per customer or sales man
    in Regular Excel Charts from PivotTables

    thanks in advance

    Reply
    • Mynda Treacy

      May 22, 2020 at 8:53 am

      Hi Andrew,

      Great to hear you’re finding out tutorials helpful 🙂

      Here are some tutorials on creating dynamic text labels:

      https://www.myonlinetraininghub.com/excel-dynamic-text-labels
      https://www.myonlinetraininghub.com/excel-dynamic-text-formulas-trick-free-video

      Mynda

      Reply
  6. Gene

    May 17, 2020 at 8:47 am

    Thank you for this article. I followed the steps and was able to create a regular chart off my pivot table using Dynamic Named Ranges. The chart plotted nicely; however, when I added a slicer to make the chart dynamic, the chart did not plot correctly anymore when I used the slicer. Is there a way to have the dynamic ranges work when a slicer is applied, and some ranges are filtered out?

    Reply
    • Mynda Treacy

      May 17, 2020 at 9:25 am

      Hi Gene,

      Yes, you should ensure the ‘COUNTA’ range takes into account the field that is being filtered by the Slicer so that it automatically adjusts the range. If you’re still stuck, please post your question and sample Excel file on our forum where we can help you further.

      Mynda

      Reply
  7. Bukenya Ivan

    March 16, 2020 at 6:22 pm

    Thanks Mynda for your great Lessons and Video tutorials, because they have really helped me alot.

    Reply
    • Mynda Treacy

      March 16, 2020 at 7:14 pm

      Great to know, Ivan 🙂

      Reply
  8. ali

    October 24, 2019 at 7:45 pm

    Thanks Mynda for your time in teaching. Wishing you a nice day. Best regards

    Reply
    • Mynda Treacy

      October 24, 2019 at 8:21 pm

      Glad you found it helpful, Ali 🙂

      Reply
  9. maggie

    April 16, 2018 at 9:36 am

    Sensational as always

    Reply
    • Mynda Treacy

      April 16, 2018 at 9:45 am

      Thanks, Maggie!

      Reply
  10. stephanrs

    February 28, 2017 at 5:47 am

    Hello Mynda, love these blog articles, especially your inspirational XL dashboards.

    1 Question:
    Dynamic Chart Defined Name/Formula to display “2 ROW” HEADER from Dynamic Table, instead of the conventional 1 row hdr, in Excel 2003? Hence just in formula not Pivot Table/List, the purpose is to exclude dynamic combo box selection with ‘field blanks’

    Oddly thought this would be easy, but Range/Axis edits don’t work for desired results and can’t find any 2 ROW HEADER Dynamic Chart examples.

    Reply
    • Catalin Bombea

      March 1, 2017 at 4:11 am

      Hi Stephan,
      The best solution is to merge the headers into a single row, multiple headers are simply not right, no excel tool will work with multiple headers: Power Query, Power Pivot, Pivot Tables, defined tables.
      Catalin

      Reply
  11. Carlos Sanchez

    February 25, 2017 at 10:16 pm

    Hi Mynda,
    Thank you for the great hint.
    Could you please give me some directions?
    I frequently prepare charts for different customers, each Excel file may contain 12 to 30 charts. Currently I manually type the customer’s name in the header of each chart, how can I build a macro or to automate this process so and have all headers done automatically once I do the first?
    Thank you in advance
    Carlos Sanchez

    Reply
    • Mynda Treacy

      February 26, 2017 at 10:28 am

      Hi Carlos,

      I’d use a dynamic text label and link all labels to one cell in your workbook.

      Mynda

      Reply
  12. Paul S.

    July 9, 2016 at 7:51 am

    Love the idea of doing a chart off a “regular” table based on a pivot table. Gives a lot more options of what you can do.

    I also love the way you did the budget part of the chart – the solid border and no fill. I tried it on one of my charts. I get the side borders, but I cannot get the top border to show over top the actual numbers. What is the secret to getting the top border to show for the budget?

    Thanks!

    Reply
    • Mynda Treacy

      July 9, 2016 at 12:52 pm

      Hi Paul,
      Glad you found this post useful.

      To get the budget border to show on top of the actual columns you need to make sure the Actual series is at the top of the list and budget is below. In step 6 above you can see in the image that Actual is first, then budget.

      To change the order of your series simply right-click the chart > Select Data. That will open the dialog box you see in step 6. Then use the up/down arrows to rearrange the series order.

      Let me know if you get stuck.

      Mynda

      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...

Course Sale

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.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

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

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.