Pivot Table and Chart — Microsoft Excel
In this article, we will delve into the intricacies of pivot table and charts using microsoft excel and we also build the concept of it’s different uses in finance and how we can use it for our financial projects.
Pivot Table
Pivot tables is the most potent tool of Microsoft Excel to analyze data. The best part about using a pivot table is that it calculates data quickly and efficiently. It
summarizes the huge data table and the features of slicers, and the timeline is the add-on for the last 2-3 versions.
Inserting a Pivot Table
Pivot tables are an essential tool for analyzing large sets of data in Microsoft Excel. They allow you to quickly and easily summarize and analyze complex data sets,
identify patterns, and gain insights into your data. In this article, we will guide you through the process of inserting a pivot table in Excel, step-by-step. Whether you’re
a beginner or an experienced Excel user, you’ll find the instructions and tips in this
article useful for creating powerful pivot tables that will help you make informed business decisions. We will do this with Car Sales Data.
The above figure, shows the data of cars sales from Jan 19 to Dec
21 for a company in 4 different cities and 17 different cars. Now, we need to analyze this with the help of a pivot table. So, first we need to insert a pivot table.
- Select any cell/row/column of the table or range.
- Click on the Insert Tab > Tables Group > PivotTable > From Table and Range.
After selecting From Table/Range, a pivot table from the selected table or range will display.
- We can change the range, but it is okay for now. It is recommended to select New Worksheet, but we can work on the pivot table on the Existing Worksheet as well. For now, we will go with New Worksheet.
- Drag Months in Rows Columns, as shown in below figure.
- Drag different field, as shown in the figure below.
The pivot table will be displayed like this:
- We can change fields as per requirements.
Get Pivot Data
Getpivotdata gives us the desired result for a particular field(s). The function works only with a Pivot Table.
As we can see in the above figure, All results can be checked in pivot table as well.
Inserting a Slicer
We can use a slicer to know the exact data for an item/period and so on:
- To insert Slicers, go to PivotTable Analyze > Filter Group > Insert Slicer.
- Select whatever slicers you want to analyze.
- Slicer boxes will appear.
Now we can select the slicers whatever we want. Let’s take Bhopal from City Slicer, Car – Creta, Year 2020. The result will be 3,57,00,000/-, We can play with slicers much more.
We can select multiple items at one go from different slicers. Like now, we want to select cities > Delhi, Jhansi, Car – BMW, Audi, Years > 2020, 2021. To make multiple selections, use the Ctrl key and select using the mouse.
We can also use Pivot Charts for the same data and the features of pivot charts are the same as those of Pivot Tables. We follow the default Pivot Chart, we can
customize the chart features.
Pivot Table has the same options as a normal table in MS Excel in the Design tab.
Note that we must refresh the Pivot Table after every change to/update in our data and/or data sources, as a PivotTable doesn’t update automatically.
Conclusion
Finally, we will learning the fundamentals of pivot table and chart using microsoft excel and we also understand the importance of pivot table/chart in accounting and finance, the reason is that, whenever we make any type of financial project it is important to analyze the up/down trends patterns using chart and tables, In this scenario our pivot table and chart help us in that and on the other hand it is very useful for any other important office based tasks as well.
