Raw and unorganized data are troublesome to summarize and comprehend, especially when you have a large number of data sets. It isn’t just tiresome and constricting, but also obnoxious and hard to imagine. In these situations, making Pivot Tables (abbreviated as PV Table), and Pivot Charts be life preservers! They help to condense, analyze and break down your data, allowing you to present data in an organized format which is easier to comprehend. Essentially, Pivot Charts are a magnificent method of picturing information in a viable and productive way.
By Billion Photos and By Asier Romero
What Is an Excel PV Table and Chart?
A PV Table, also known as Pivot Table, is a built-in feature of MS Excel. It improves visualization of data by arranging and summarizing selected rows and columns of data in a spreadsheet. A Pivot Chart, another built-in feature of MS Excel, is a visual representation of a PV Table. A Pivot Chart comes in handy if you have a large amount of data.
Let’s look at an example. Say you have a business with employees and you want to know who’s working the longest hours. You decide to use Excel to do this. You also decide that at the end of each month you are going to give out an award to the employee with the highest number of hours as a way of honoring their commitment to your business.
If you have a lot of employees this could be extremely tedious and time consuming to do manually, and you would likely end up making mistakes. However, with a PV Table or Pivot Chart this becomes a much more reasonable undertaking that is done relatively quickly and easily.
Note: These are built-in features of Microsoft Excel as well as Microsoft Access. The major difference is that in MS Excel or any other Microsoft software, a Pivot Chart can easily be copied and pasted. In MS Access, a pivot chart cannot be copied and pasted.
Inserting a PV Table in Excel
In order to insert PV Table in MS Excel, the following steps must be performed consecutively:
- Select any cell in the spreadsheet.
- Select Insert Tab on the Menu Bar, and then under Tables section, select PV Tables.
- The Create Pivot Table dialogue box appears.
- Select the desired range of cells and the location where the PV Table needs to be created as well as inserted.
- Select OK button in the dialogue box.
Now you’ve created your PV Table, which makes data visualization much easier. It also helps you draw conclusions based on analysis, and use these conclusions for statistical analysis and studies.
Note: Any change made in a PV Table are consequently and promptly reflected in the Pivot Chart and vice-versa. In other words, they are dependent Excel features that simultaneously reflect changes.
Filtering a PV Table in MS Excel
Filtering a Pivot Chart is what it sounds like—it’s the process of using a specific condition to filter the data in some way. The result is a Pivot Chart that presents filter-specific data only. By doing this, the filtered PV Chart improves your ability to visualize and analyze your overall data.
The following illustrations demonstrate the filtering of a Pivot Chart:
- Use the standard filters denoted by the drop-down menus against Product, or Country, or Category. For instance, use the Country filter to show only the aggregate sum of products exported to India.
- To create a filtered PV Table, remove the Country filter and add the Category Field as a filter.
Inserting Slicer in MS Excel for filtering PV Tables
In order to modify and filter PV Tables in MS Excel, Slicers can be easily inserted for effective data representation and analysis. Both Report Filter and Slicer have the same operation and objective, so let’s take a look.
Follow these steps sequentially in order to insert a Slicer into MS Excel:
- Select any cell inside the PV Table created in the spreadsheet.
- Select the Analyze tab on Menu Bar and click on Insert Slicer under the Filter section.
- Insert Slicer dialogue box appears. Check box against the field name where the slicer needs to be inserted and select OK.
Now, the slicer is inserted and the requisite PV Table is created.
Changing Pivot Chart Type in MS Excel based upon a PV Table
A PV Table can be represented through a PV Chart in numerous ways.
In order to effectively visualize and analyze your data, you must select an appropriate PV Table that will give you the data that you want on your PV Chart.
Thus, the significance of knowing the procedure for Inserting and Filtering a Pivot Chart—and the selection of a particular Pivot Chart type for data representation—are both equally important. One can change a Pivot Chart at any point in time.
- Select the Pivot chart created using PV Table.
- Select the Design tab on Menu Bar, click on Change Chart Type under Type section.
- Choose the desired form of Pivot Chart that you want to use to effectively represent and analyze your data.
- Select OK.
Now you’ve changed the Pivot Chart type.
To effectively represent and analyze data, knowing how to create and alter Pivot Charts is an important skill.
Refreshing PV Tables
Modifications to any data that makes up a PV Table or Pivot Chart are not automatically updated or saved. You need to use the Refresh function to update these charts with new information.
To Update or Refresh Pivot Charts, the following steps need to be carried out sequentially:
- Edit the data you want updated on the spreadsheet.
- Select any cell inside the PV Table or Pivot Chart created.
- Right click and select Refresh option.
Changing Data Sources of PV Tables and Pivot Charts in Excel
At times, a PV Table or Pivot Chart relies on information that can be sourced from an existing spreadsheet or from another new spreadsheet. Keeping in mind the end goal of producing PV Tables and Pivot Charts for significant data representation and analysis, data sources should be effectively changed. This also makes Pivot Charts and PV Tables more descriptive, and is an effective method to keep a spreadsheet with related information together.
In order to change data sources of PV Tables and Pivot Charts, the following steps must be performed in MS Excel:
- Select any cell inside the PV Table or Pivot Chart created, based on unrevised or older data.
- Click on Analyze Tab on Menu Bar, and further click on Change Data Source option under Data section.
- Choose the appropriate data source and click on OK.
- Click on Refresh.
Note: It is advisable for any proficient Excel user to update the data of a spreadsheet that is inserted into a PV Table. This helps save time as the data source is updated automatically. However, in order to generate the updated PV Table and Pivot Chart, Refresh needs to be clicked.
Advantages of a Pivot Chart
- It is an effective way to visually represent data.
- It facilitates Data Analysis.
- It helps the user draw various inferences, and forms the basis of statistical development.
- It is an efficient way to manage large amounts of raw data by relating them through filtering and slicing.