Home Chart How-to Create a Dynamic Excel Pivot Table Dashboard Chart

How-to Create a Dynamic Excel Pivot Table Dashboard Chart

In my last 2 posts:

I showed you how to setup your data in preparation for creating a dynamic dashboard chart

Part 1: think-like-a-database-designer-before-creating-an-excel-dashboard-chart

And then I showed you how to create an Excel Pivot Table or Data Table and Insert Slicers

Part 2: how-to-insert-slicers-into-an-excel-pivot-table


In this tutorial, I will show you some of the tips and tricks to the final part of creating a dynamic dashboard chart.


Dynamic Charts For Excel Tables

1) Arrange your data so that the right most column of your Excel Table represents the value for your chart.

You can re-arrange your data in an Excel Table by dragging and dropping the column header of the table from where it is now to where you want it.image

In our case, we want the Year and Month to be to the left of Group so that the chart will create a multi-category horizontal axis.image NOTE: You cannot move the Excel Table Columns unless you have cleared the filters or slicers.

In our case, we want the Year as the biggest grouping followed by month and then Group and Category.image

2) Create your Excel Chart

When you have your data setup in the right order in the Excel Table, then highlight the data you want to chart and then select the Insert Ribbon and then select the 2-D Clustered Column Chart in the Chart Group.image You have now created a Dynamic chart using an Excel Table.image

3) Update Chart Options

However, one of the short comings of an Excel Table is that when use a filter or use a slicer to filter the table, it hides the rows.  This may cause your chart to grow, shrink and move with the Excel Rows.  I recommend turning this option off when you make an Excel Table Chart.  To remove this option, right click on the chart and select Format Chart Area.  Then select the Chart Options and choose “Don’t move or size with cells” image

You are all set.  Your users can now use the Slicers to filter the data the data dynamically to the values they want to see.  Also, if you add new rows of data for the next month’s data, Excel will automatically add the new data to your chart.

Here is another tutorial on this topic but without the slicer option if you don’t have Excel 2013:



Pivot Tables

1) Create Pivot Chart

Select anywhere in the Pivot Table you created in the previous tutorial and then click on the Insert Ribbon and then select the 2-D Clustered Column Chart in the Chart Group.


2) Update Pivot Fields

We create our Pivot Table with all of the groupings in the Rows as you see here.


This will result in a chart that looks like the chart we create with our Excel Table like this:

image However, you may want to consider changing your PivotTable Fields to create new series by moving a grouping to the Columns section as you see here:image By moving the Year to the Columns, you will create a new series in your Pivot Chart as you see here:image Regardless of how you want to choose to display your data, the Dynamic Pivot Chart for your Excel Dashboard is now completed and your users can use the Slicers to see the data they are most interested in viewing.image

Video Demonstration

Sample File Download



What Pivot Table or Excel Table settings did I miss or do you prefer to use when creating Pivot Chart or Dynamic Excel Table Chart?  Let me know in the comments below.



Want to Receive the Next Post?
Join My Newsletter
Give it a try, you can unsubscribe anytime.
Privacy Policy
Subscribe Now!   (Privacy Policy)

Stay Connected!

Get the latest post emailed straight to your inbox!