Step 1 - Create base data for the chart. Open up Excel and create new worksheet, rename it as "Data" and give some of the same data, as shown below.
![]()
Step 2 - Now, select the range of data from the "Data" worksheet and select "Pivot Chart & Pivot Table".
![]()
You will see the screen, shown below. Select the highlighted options and proceed further.
![]()
As soon as you click "OK", you will see the new worksheet it creates. Please rename it as "Report".
Step 3 - Now, you need to drag and drop the fields to the respective places, as shown below-
![]()
Finally, the report is ready.
Step 4 - Now, we need to build Macro to refresh the report automatically, when the workbook is opened or on clicking the button in the Worksheet.
First, save Excel Workbook as "Macro-enabled excel", as shown below. File-> save as ->
![]()
To enable the "Developer" tab, go to "Options", as shown below and click the check boxes, as highlighted in Yellow color.
![]()
![]()
Now, record Macro, as shown below-
![]()
The popup will come, as shown below-
![]()
Rename the Macro to "Refresh Data". Click the Chart control from the "Reports" Worksheet and from the top ribbon, select "Refresh".
![]()
Now, go to the coding part by clicking Visual Studio, as shown below-
![]()
You need to rename a few highlighted things and write the new function "Workbook_Open", as shown below-
![]()
Now, you are all set to go. Save Macro and close the Window.
![]()
Go to the top ribbon, select the "Design Mode" and "Insert" -> "button".
Now, a new button will be introduced in your chart Worksheet, as shown below-
![]()
Select "RefreshData" Macro from the list, as shown below and click OK.
![]()
After completing everything, please click on the "Design Mode" from the "Developer" ribbon.
![]()
Save Excel document.
Everytime you open Excel document, the Chart will be refreshed automatically with the data in the "Data" Worksheet. Also, when you click on the button "Refresh", it will refresh the chart.