Introduction to Pivot Table in Excel - Advanced

In the previous blog post, we learned the Basics of Pivot Table in Excel. In this blog post, we will explore some of the advanced features of pivot tables, such as slicers, timelines, calculated fields, pivot charts, managing relationships, etc. These features can help you change your existing pivot table and create different views of your data every time.

Slicers

Slicers are used to filter your data by selecting specific values from a field. For example in this data table, we have a column named Product (Crayons, Geometry Box, Paper, Pen, Stapler, Sticky Notes)  you can use a slicer to show only the data for a particular product or products. 

Steps to add a Slicer: Select your pivot table ➡️ PivotTable Analyze ➡️ Click on "Insert Slicer" ➡️Choose the field for the slicer.

A slicer window will appear, where you can select a field and a slicer will be inserted. You can select one option or multiselect by holding down the CTRL key while making your selections.

You can also clear the filters or format, change the slicer settings, as per your requirement by selecting your PivotTable and then click on Slicer tab to get more formatting options

Timelines

Timelines are similar to slicers, but they are used to filter your data for a specific time period or periods.. A timeline can only be inserted when the data has a date column in it.

Steps to add Timeline: Select your pivot table ➡️ PivotTable Analyze ➡️ Click on "Insert Slicer" ➡️Choose the date field for timeline.

A timeline window will appear, where you can select a time range or ranges from the date field. The pivot table will be updated to show only the data for the selected time range. The timeline also allows you to group the dates by months, quarters, or years.

Note: You can insert both slicers and timelines by right-clicking on desired field in the pivot table and then choosing the "Add as Slicer" or "Add as Timeline" option.

Pivot Charts

Pivot Charts are your go-to tool for translating data into visual insights. They dynamically sync with your pivot table, allowing you to instantly visualize data.

Steps to add Pivot Charts: Select your pivot table ➡️ PivotTable Analyze tab ➡️ Click on "PivotChart."➡️ Choose the chart type ➡️ 

You can also customize the layout, format, and design as needed.

Calculated Fields

Calculated fields are used to create new fields based on the existing fields in the pivot table. They can help you perform calculations and analysis on the data. For example, in this data set that shows the cost and revenue of different products, you can use a calculated field to create a new field that shows the profit margin of each product.


Insert the correct formula for calculated field.

This calculated field can be used in pivot table as well.

NOTE:  A calculated field created for one Pivot Table does not automatically appear for another Pivot Table. If you want to use the same calculated field in multiple Pivot Tables, you'll need to recreate it for each Pivot Table individually.

Managing relationships

Relationships are used to connect two or more tables that have common fields. They can help you create pivot tables from multiple data sources. For example, if you have two tables that show the sales and customer details of different products, you can use a relationship to create a pivot table that shows the sales and customer information for each product.

How to Manage Relationships: 

Go to the Data tab ⬇️
Click on "Manage Data Model" to open the Power Pivot window ⬇️
Explore, create, edit, or delete relationships using the buttons on the Home tab ⬇️
Create a pivot table from multiple tables by going to the Insert tab, selecting "PivotTable," and choosing the "Use this workbook’s Data Model" option.


In conclusion, these advanced features of Pivot Tables help you to elevate your data analysis skills to new heights. Thank you for reading!

Comments

Post a Comment

Popular posts from this blog

XLOOKUP Function in Excel

Image
In our previous blog post we have already learned about 2 most useful lookup functions: VLOOKUP and HLOOKUP . In this blog post, we’ll explore what XLOOKUP is, its syntax, why you should use it, its pros and cons, and provide some practical examples. The  XLOOKUP  function revolutionize the way we search and retrieve data. XLOOKUP function is a modern and versatile replacement for traditional lookup functions in Excel. It allows you to find things in a table or range by row, regardless of whether the return column is on the left or right side. It’s like a search engine for your Excel data. Here’s what you need to know: Syntax : =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Arguments : lookup_value : The value you want to search for. lookup_array : The range or array to search. return_array : The range or array from which to return results. [ if_not_found ] : (Optional) Specify what to return if no match is found. [ match_...
Read more

Introduction to Pivot Table in Excel - Basics

Image
Pivot tables are one of the most powerful and useful features in Excel. Allowing us to quickly summarize, analyze, and explore large amounts of data in various ways. It is a special type of table that displays the summary of a data set and helps us in analyzing the data in seconds without knowing any formulas and functions. It consists of four areas: rows, columns, values, and filters. You can drag and drop different fields from your data set into these areas to create different views of your data. In this blog post, we will introduce the basics of pivot tables. Converting data to tabular data before inserting Pivot Table is an added advantage as this eliminates the need to frequently adjust the table range as new entries are added (select a cell from the data and click CTRL+T and then OK).  Additionally the following conditions need to be fulfilled: ✔ Each column should have unique headers, and the headers should be descriptive enough.       For ex. If Revenue...
Read more