Introduction to Pivot Table in Excel - Basics

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 column has header named as "Rev" then rename it as "Revenue"
✔ No empty columns or rows
✔ No total or subtotal rows
✔ No merged values

Why should you use a pivot table?

  1. It helps in summarizing a large set of data in seconds.
  2. Create interactive tables and charts using PivotChart, Slicers, Timeline, Calculated fields, etc.
  3. It is easy to learn and use, as you only have to drag and drop the column headers into the correct fields.
  4. It reduces manual calculations and errors.
  5. If you are not satisfied with the pivot table, you can simply delete it, as it does not affect the original data.

There are different ways to create a pivot table. 

Option 1: Insert Tab ➡️ PivotTable
  1. On the Insert tab, in the Tables group, click PivotTable. A dialog box will appear, showing the selected range and the location for the new pivot table.
  2. Click OK. A new worksheet will be created with an empty pivot table and a PivotTable Fields pane.

In the PivotTable Fields pane, drag and drop the fields that you want to use in the pivot table into the four areas: Rows, Columns, Values, and Filters. You can also check or uncheck the fields to add or remove them from the pivot table.

Option 2: Insert Tab ➡️ Recommended PivotTable

After clicking on OK, a new sheet will be created with the selected Pivot Table from the recommended pivot tables.

Option 3: Home Tab ➡️ Analyze Data

Analyze data is a powerful tool, you just have to ask right questions. Analyze Data is available to Microsoft 365 subscribers in English, French, Spanish, German, Simplified Chinese, and Japanese.

Reference video downloaded from microsoft.com

NOTE: To change the layout, format, or design of the pivot table, use the options on the PivotTable Tools tabs: Analyze and Design.

(Sample data file with customer, Order ID, Units Sold, Date, Revenue and Cost data)

Four main areas of a Pivot Table in Excel

A pivot table has four main areas where you can place the fields: values, filters, rows, and columns. Each area has a different function and effect on the data displayed in the pivot table.

Drag and drop the fields in the right areas

NOTE: You can also group the data by a certain category or interval by right-clicking on the field and choosing the Group option. You can group the data by numbers, dates, text, or custom groups and use it in rows or columns. You can also ungroup or collapse the groups as needed.

Normal table v/s Pivot table in Excel

Feature Normal Table Pivot Table
Representation of Data Shows data as it is entered. Shows a summarized view based on chosen fields.
Data Manipulation Allows direct entry, editing, and deletion of data. Requires changes in the source data; refreshes to update.
Structure and Layout Has a fixed structure and layout. Offers a flexible and dynamic structure; easy modification.
Calculations Requires manual use of formulas/functions for calculations. Automatically performs calculations based on selected values.
Level of Detail Displays one level of data. Can show multiple levels with subtotals, grand totals, and hierarchies.

I hope this blog post has helped you understand the basics of pivot tables in Excel. Pivot tables are a great tool to make your data analysis easier and faster. Try creating your own pivot tables and see what insights you can discover from your data. 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 - Advanced

Image
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 c...
Read more