Requirement
You are given an Excel file with some data inside it and have been asked to generate visual charts out of it so that it can be presented to the management team. The Excel file has worksheet with table containing hundreds of rows and multiple columns. You need to generate visuals for each of the columns or combinations of it and show chart in same excel file. How would you do that?
Solution
Excel comes with pivot table and pivot charts feature – which are very powerful features for data analysis. Most of the users who have worked on Excel must have come across a situation where he/she has been asked to generate visuals out of the data available.
Pivot Tables and Pivot Charts in Excel
A PivotTable is a powerful tool in excel to calculate, summarize, and analyze data which helps you to understand comparisons, various patterns, and trends in your data.
Let’s see how we can use it.
Below is the sample data in my Excel sheet >> Its sales units and sales volumes data by month for the last three years
Select the first cell in your sample data >> Select Insert Tab from top >> You will find options to insert Pivot tables >> Click on PivotTable
![]()
&
It will open another popup to choose the parameters. Your sample data table range will be selected automatically. It asks where you want to add this PivotTable. I wanted to add it in the same worksheet, so I have chosen the existing worksheet. Click on the up arrow button to specify the cell where you want the table to be added.
Choose the cell where the PivotTable gets inserted
As you can see below it opens the property’s configuration window on the right side. Here you can see all the columns from your sample data. There are sections to choose from depending on how you want your pivot table to present the data. You can decide what information to be shown as rows and as columns on those data to be used as filters.
E.g. I wanted to see Total sales units by month so far. So, it should add up the data for the months for all the years. As you see below I have chosen Months as Rows, Sales unit as values. You can choose what aggregate function you want to apply to your values like Sum, Avg, %, Count etc.
Another similar example where I wanted to see Total sales volume by Month >> So I have dragged Sales Volumes field into the Values section.
Let’s see how to add Slicer for this tabular report. Now as I have a total of Sales Units and Sales Volume by month, I want to see it per year. So why not show slicer with years as values.
Right click on Year field >> From the menu select "Add as Slicer" as shown below.
As shown below it will add slicer section beside your pivot table. You can select a year and data will get filtered in the pivot table. You can clear your selection, there is an option to choose multiple years as well.
As you can see below I have selected 2019 and the data in my pivot table is filtered accordingly.
That’s it for this part of the article. In the next article we will see how to add visuals; i.e. pivot charts in the same worksheet. Thanks for reading.