• 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

Pivot Charts

You are here: Home / Excel PivotTables / Pivot Charts
pivot charts
May 26, 2015 by Mynda Treacy

Iโ€™ve always thought after inserting a PivotTable that Excel should ask โ€œwould you like a PivotChart with that?โ€. I think PivotCharts are Microsoftโ€™s equivalent of McDonaldโ€™s famous upsell โ€“ โ€œWould you like fries with that?โ€

Iโ€™m not saying you should have Excel Pivot Charts with every PivotTable meal but they do go nicely together. However, beware; PivotCharts have their own set of rules which you must abide by, and for that reason they come with a health warning!

And if Pivot Charts are the equivalent of fries then Slicers are the ketchup โ€“ you can have them with your PivotTable and or Pivot Charts. More on Slicers in a moment.

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

Be sure to watch till the end to see my blooper ๐Ÿ™‚

Inserting a Pivot Chart

In Excel Pivot Charts can be inserted without first creating a PivotTable. Youโ€™ll find them on the Insert tab. In Excel 2007/2010 they're in the Tables group and in Excel 2013 you'll find them in the Charts group:

Insert Excel PivotChart

The drop down gives you the option to either insert a PivotChart or Insert a PivotChart & PivotTable, which appears to be superfluous* since either option opens the menu youโ€™re probably familiar with (notice the only difference is one says โ€˜PivotTableโ€™ and the other says โ€˜PivotChartโ€™):

Insert Excel PivotChart menu

And when youโ€™ve selected your data and where you want to place the PivotChart, or PivotTable & PivotChart, youโ€™ll have an empty PivotTable and PivotChart (irrespective of whether you choose to insert just a PivotChart), and the field list is open ready for you to build your chart and table:

Excel PivotChart field list

Notice how with the PivotChart selected the Field List (above) has a Legend (Series) and Axis (Categories) sections instead of the usual Columns and Rows for a PivotTable. Nice touch.

*Note: it is only possible to insert a PivotChart without a PivotTable in Excel 2013 if you check the 'Add this data to the Data Model' check box upon inserting the PivotChart. But then you're working in Power Pivot and that has its own implications, which is for another day.

Adding a PivotChart Later

Sometimes we build the PivotTable and then decide to add a PivotChart. Youโ€™ll find the option on the PivotTable Tools: Analyze/Options tab (when the PivotTable is selected).

Insert Excel PivotChart menu

Switch Rows/Columns

When you insert a chart you might find that you need to change the location of the rows and column labels to better suit the layout of the chart. If so you can either go back to the field list and switch them or right-click the chart > Select Data > Switch Row/Columns, or on the PivotChart Tools: Design Tab > Switch Row/Columns.

Switch rows/columns in Excel PivotChart

Changing the Chart Type

You can change the chart type by right-clicking on the PivotChart > Change Chart Type or on the PivotChart Tools: Design menu (see image above).

PivotChart Limitations

PivotTables are fussy; they only let you use their data in a PivotChart, i.e. you can't insert any old chart from the Insert Chart menu. In fact you canโ€™t insert an XY Scatter, Bubble or Stock chart with your PivotTable data.

PivotCharts are all or nothing; one of the most common questions I get asked is โ€˜how can I only display some of the data from the PivotTable in the chart?โ€™ The answer is you canโ€™t pick and choose which data in the PivotTable is displayed in your chart. Itโ€™s all or nothing, with the exception of Grand Totals which are not displayed in your charts.

Field Buttons and Slicers

Once youโ€™ve built your PivotChart youโ€™ll find there are Field Buttons on the chart.

Field buttons on Excel PivotChart

The buttons with a drop down arrow are interactive and you can use them to change the filters and sort options, which is pretty powerful:

Field buttons on Excel PivotChart

However, I prefer to use Slicers for filtering as the Field Buttons are space hogs and you can't customise them, or their location. To remove them right-click any of the Field Buttons > choose to either Hide All or Hide Value Field Buttons:

Hide Field buttons on Excel PivotChart

Slicers

If you have Excel 2010 or later you can use Slicers instead of the Field buttons for a more intuitive interface.

Slicers with Excel PivotChart

pivot charts

More Pivot Chart Posts

Change PivotTable Aggregation Methods using Excel Slicers

Change PivotChart Aggregation Methods using Excel Slicers

Change Pivot Chart Aggregation Methods using Excel Slicers and Power Pivot. Disconnected tables and the SWITCH function are the secret sauce.

Excel Pivot Chart Drill Down Buttons

Excel Pivot Chart Drill Down Buttons are an easy way for users to drill up/down through levels of data in charts. They're new in Excel 2016.

More Excel PivotTables Posts

Auto Refresh PivotTables

Auto Refresh PivotTables isnโ€™t on by default, and the process differs depending on if your PivotTables is loaded to the data model or not.

Show Items with no Data in PivotTables

Show Items with no Data in PivotTables allows you to maintain a constant structure to your PivotTable or Pivot Chart axis when filtering.

Force Excel Slicers to Single Select

There's no build in way to force Excel Slicers to single select but we can use these clever warnings to persuade your users.
excel pivottable p&L

Excel PivotTable Profit and Loss

Creating an Excel PivotTable Profit and Loss Statement means you can use Slicers and Conditional Formatting and have the P&L automatically update.

Excel PivotTable Field List Tips

Customize the Excel PivotTable Field List to suit your needs. Find how to turn the PivotTable Field List on and off and other handy tips.

Hide Blanks in Excel PivotTables

Hide blanks in Excel PivotTables caused by empty cells in your source data. Iโ€™m talking about PivotTable cells containing the (blank) placeholder.
Excel Slicer Formatting

Excel Slicer Formatting

Excel Slicer Formatting is essential because theyโ€™re big and chunky. In this tutorial I show you how to make Excel Slicers small.
Excel PivotTable Quick Explore

Excel PivotTable Quick Explore

Drill down into data hierarchies using PivotTables and Pivot Charts with Excel PivotTable Quick Explore. New in Excel 2013 onward.
excel online pivottables

Excel Online PivotTables

Excel Online PivotTables are now available from the Insert tab of the ribbon. There are some limitations that are covered in this post.

Excel PivotTable Error Handling

Excel PivotTable error handling and why you canโ€™t calculate the percentage change when the prior period is zero or blank.




Category: Excel PivotTablesTag: pivot chart
Previous Post:Highlight Selected Cell In Excel and Preserve Cell FormatHighlight Selected Cells in Excel and Preserve Cell Formatting
Next Post:Excel Fill Value Down Rows based on CriteriaExcel Fill Down IF

Reader Interactions

Comments

  1. CRAIG L BEUTHIN

    May 8, 2020 at 1:54 am

    It wasn’t a blooper it was sweet/cute.

    Reply
  2. Nabeel

    February 21, 2019 at 12:28 am

    Hey Maynda,

    I have created Dashboard in excel with different charts table , but I got face one thing when I click on any charts he show with his property I don’t want to need this i want to locked or fixed this charts

    Reply
    • Philip Treacy

      February 21, 2019 at 8:55 am

      Hi Nabeel,

      I don’t understand the issue.

      Can you please start a topic on the forum and supply your workbook.

      Regards

      Phil

      Reply
  3. Bill T.

    September 8, 2017 at 6:45 am

    Mynda, how can I make a dynamic chart title on a pivot chart that is based on the filtered field?

    Reply
    • Mynda Treacy

      September 8, 2017 at 9:17 am

      Hi Bill,

      This would only be feasible if there is only likely to be between 1 and 3 items selected in your Slicer. Anymore than that and your chart title will be too long.

      If so, then in a cell in your worksheet you build a formula that picks up the rows visible in your PivotTable and concatenates them. These visible rows contain the items selected in your Slicer. You can then link the chart title to the cell containing your concatenation formula.

      These tutorials go into more detail:

      https://www.myonlinetraininghub.com/excel-dynamic-text-labels
      https://www.myonlinetraininghub.com/excel-textjoin-function

      If you get stuck please post your question on our Excel Forum and a sample Excel file so we can help you in more detail.

      Mynda

      Reply
  4. Maryam Maleki

    July 9, 2017 at 8:07 am

    Thank you for sharing your knowledge of Excel! All these tools and tips are very helpful for better presentation of data.

    Reply
    • Mynda Treacy

      July 9, 2017 at 1:50 pm

      You’re welcome, Maryam. I’m glad you find them useful.

      Mynda

      Reply
  5. Anne

    January 2, 2016 at 7:36 am

    Thanks for all your tips.

    Reply
    • Mynda Treacy

      January 2, 2016 at 5:55 pm

      Great to know you like them, Anne ๐Ÿ™‚

      Reply
  6. Raghu

    May 29, 2015 at 5:27 pm

    Hi Mynda,
    how to add Field Buttons on Chart.
    I use 2007

    Thanks & kind regards
    Raghu

    Reply
    • Mynda Treacy

      May 29, 2015 at 7:44 pm

      Hi Raghu,

      In Excel 2007 you don’t have the Field buttons on the chart. Instead you have a PivotChart Filter Pane which hovers above your worksheet. You can turn it on by selecting the PivotChart and then on the PivotChart Tools: Analyze tab select ‘PivotChart Filter’.

      I hope that helps.

      Kind regards,

      Mynda

      Reply
  7. Suz coomber

    May 27, 2015 at 5:30 pm

    As usual, some really helpful information. Your website and courses are easy to follow and very clear. Thank you.

    Reply
    • Mynda Treacy

      May 27, 2015 at 8:16 pm

      Thanks, Suz ๐Ÿ™‚

      Reply
  8. MICHAEL MANGANO

    May 26, 2015 at 11:50 pm

    Hi, I love your site and all of the Excel tips you give. When I tried to download the workbook it did not work. Thanks!

    Reply
    • Mynda Treacy

      May 27, 2015 at 9:07 am

      Hi Michael,

      Thanks for your kind words.

      Sorry about the workbook download link, Iโ€™ve fixed it now. Here is the correct link:

      http://trainingworkingfiles.s3.amazonaws.com/blog/pivot_charts.xlsx

      Kind regards,

      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.