How to Create a Pivot Table in Excel: A Step-by-Step Tutorial

Pivot tables are data exploration tools used by Excel users to find patterns and trends in the information stored. This tutorial will walk you through tips on how to create a pivot table, from loading your data into Excel all the way to using it for analysis.

The “pivot table in excel step by step pdf” is a tutorial that will teach you how to create a pivot table in Excel. The tutorial includes screenshots, and a list of the steps that you need to follow.

One of Microsoft Excel’s most powerful — and terrifying — features is the pivot table. It’s useful since it can help you summarize and understand enormous amounts of data. It’s intimidating since you’re not an Excel whiz, and pivot tables have a reputation for being difficult.

The good news is that learning how to make a pivot table in Excel is a lot simpler than you would think.

But before we get started, let’s take a step back and make sure you understand what a pivot table is and why you would need one.

What is the definition of a pivot table?

A pivot table is a graphic that contains a summary of your data that allows you to report on and examine patterns depending on your data. Pivot tables are very handy if you have a lot of lengthy rows or columns with data that you need to keep track of and compare.

To put it another way, pivot tables help you make sense of the apparently infinite mess of statistics on your computer. And, more particularly, it allows you to arrange your data in a variety of ways to make it easier to draw useful conclusions.

The “pivot” portion of a pivot table refers to the fact that the data in the table may be rotated (or pivoted) to see it from various angles. When you do a pivot, you’re not adding to, subtracting from, or otherwise affecting your data. Instead, you’re only restructuring the data in order to extract meaningful information.

What is the purpose of pivot tables?

Don’t worry if you’re still not sure what pivot tables are or what they perform. Once you’ve seen it in action, this is one of those technologies that is lot simpler to grasp.

The goal of pivot tables is to provide easy-to-use tools for rapidly summarizing enormous volumes of data. They may be used to better comprehend, show, and analyze numerical data in depth, as well as to find and answer unexpected queries.

The following are seven potential instances in which a pivot table may be useful:

1. Comparing the overall sales of various items.

Let’s say you have a spreadsheet with monthly sales data for three distinct items — product 1, product 2, and product 3 — and you’re trying to find out which one is bringing in the most money. Of course, you may go through the spreadsheet and manually add the sales amount to a running total every time product 1 occurs. Then repeat the process for product 2 and 3 until you get totals for all of them. Isn’t it a piece of cake?

Consider your monthly sales spreadsheet, which includes hundreds upon thousands of rows. Sorting through them all by hand may take a lifetime. In less than a minute, you can use a pivot table to automatically aggregate all of the sales numbers for product 1, product 2, and product 3 — and compute their respective sums.

2. Displaying sales as a proportion of overall sales.

When you build pivot tables, the totals of each row or column are automatically shown. However, it isn’t the only figure you may generate automatically.

Assume you put quarterly sales data for three different goods into an Excel sheet and then converted the data into a pivot table. After adding up each product’s quarterly sales, the table would automatically give you three totals at the bottom of each column. But what if you wanted to know what proportion of overall business sales these product sales contributed to instead of simply that products’ sales totals?

Instead of simply the column total, you may arrange each column in a pivot table to tell you the column’s % of all three column totals. If, for example, three products sold for $200,000 and the first product generated $45,000, you can use a pivot table to claim that this product contributed 22.5 percent of total business sales.

Simply right-click the cell containing a sales total and choose Show Values As > percent of Grand Overall to display product sales as percentages of total sales in a pivot table.

3. Combining data that is duplicated.

You’ve just finished a blog makeover and have to alter a number of URLs. Unfortunately, your blog reporting software didn’t handle it adequately, and the “view” numbers for individual articles were divided across two URLs. As a result, you have two duplicates of each blog post in your spreadsheet. You must add the view totals for each of these duplicates to obtain correct data.

The pivot table comes into play in this situation. Rather than having to manually look for and combine all of the metrics from duplicate posts, you may summarize your data (through pivot table) by blog post title, and voila: the view metrics from those duplicate articles will be automatically aggregated.

4. Compiling a list of all employees in each department.

Pivot tables are useful for calculating items that are difficult to discover in a standard Excel spreadsheet. Counting rows that all have something in common is one of them.

If you have a list of workers on an Excel sheet, for example, and next to their names are the departments to which they belong, you may use this data to build a pivot table that displays each department name and the number of people who belong to it. The pivot table does away with the need to manually sort the Excel sheet by department name and count each entry.

5. Filling up blank cells with default values.

Every cell in Excel will not be filled with every dataset you add. If you’re waiting for fresh data to arrive before entering it into Excel, you can end up with a lot of empty cells that are unclear or need more explanation when presenting this information to your boss. Pivot tables can very handy in this situation.

A pivot table may be readily customized to fill empty columns with a default value, such as $0 or TBD (for “to be determined”). When numerous individuals are studying the same page, being able to easily identify these cells is a handy feature for huge tables of data.

Right-click your pivot table and choose PivotTable Options to automatically format the empty cells. Check the box labeled Empty Cells As and type what you want to see when a cell has no other value in the window that displays.

What Is a Pivot Table and How Do I Make One?

1. Fill in the blanks with your information in a series of rows and columns.
2. Sort your data according to a given criterion.
3. To make your pivot table, highlight the cells you want to use.
4. Drag a field into the “Row Labels” section.
5. Drag a field into the “Values” section.
6. Make adjustments to your calculations.

Let’s dive into the nitty-gritty of how to actually design a pivot table now that you have a better understanding of what they may be used for.

Step 1: Sort your information into rows and columns.

Every pivot table in Excel begins with a standard Excel table that contains all of your data. Simply insert your data into a specified set of rows and columns to create this table. To classify your values by what they represent, use the highest row or column.

For example, a column listing each “Top Page,” a column listing each URL’s “Clicks,” a column showing each article’s “Impressions,” and so on may be included in an Excel table with blog post performance statistics. (In the stages that follow, we’ll use that example.)

Step 2: Sort your information by a chosen criterion.

When you’ve put all of the data you need into your Excel sheet, organize it in some manner to make it simpler to handle once you’ve converted it to a pivot table.

Click the Data tab in the top navigation bar and then the Sort icon underneath it to sort your data. You may choose to arrange your data by any column and in any order in the window that opens.

To sort your Excel sheet by “Views to Date,” for example, choose this column title under Column and then choose whether to arrange your posts from smallest to biggest or largest to smallest.

You’ll be able to rearrange each row of your Excel sheet by the amount of views each blog post has received by selecting OK on the bottom-right of the Sort box.

Step 3: To make a pivot table, highlight your cells.

Highlight the cells you’d want to summarize in a pivot table after you’ve inserted data into your Excel spreadsheet and sorted it to your preference. Select the PivotTable icon from the Insert menu on the top navigation bar. You can also pick “PivotTable” and manually input the range of cells you want to include in the PivotTable by clicking anywhere in your worksheet and selecting “PivotTable.”

This will open an option box where you can choose whether to launch this pivot table in a new worksheet or retain it in the current worksheet, in addition to specifying your cell range. You may navigate to and away from a new sheet at the bottom of your Excel worksheet if you open one. Once you’ve made your decision, click OK.

You may also pick Recommended PivotTables to the right of the PivotTable icon and open a pivot table with pre-set options for how to arrange each row and column by highlighting your cells.

Note: If you’re using an older version of Excel, “PivotTables” may be found under Tables or Data rather than “Insert” in the top menu. From the Data menu in Google Sheets’ top navigation, you may construct pivot tables.

Step 4: In the “Row Labels” section, drag and drop a field.

Excel will build a blank pivot table for you when you finish Step 3. The next step is to drag and drop a field into the Row Labels section, labeled with the names of the columns in your spreadsheet. This will define the unique identifier the pivot table will use to arrange your data — blog post title, product name, and so on.

Consider the following scenario: you wish to arrange a large amount of blogging data by post title. Simply click and drag the “Top pages” box to the “Row Labels” section to do this.

Note that depending on whatever version of Excel you’re using, your pivot table may appear different. The essential ideas, however, remain the same.

Step 5: Drag a field into the “Values” section.

After you’ve decided how you’ll arrange your data, drag a field into the Values box to add some values.

Let’s imagine you wish to summarize blog post views by title, sticking with the blogging data example. Simply drag the “Views” field into the Values box to do this.

Step 6: Make any necessary adjustments to your calculations.

The total of a certain number will be computed by default, but depending on what you want to calculate, you may simply alter this to something like average, maximum, or minimum.

On a Mac, choose the option you want by clicking on the little I next to a value in the “Values” section, then hitting “OK.” Your pivot table will be changed as soon as you’ve made your choice.

To open the Value Field Settings menu on a PC, click on the little upside-down triangle next to your value and choose Value Field Settings.

When you’ve finished categorizing your data to your satisfaction, save your work and utilize it as needed.

Using Pivot Tables to Dig Deeper

You’ve now mastered the fundamentals of Excel pivot table design. With this knowledge, you can determine what you need from your pivot table and locate the answers you want.

You could discover, for example, that the data in your pivot table isn’t ordered the way you want it to be. If this is the case, the Sort feature in Excel may assist you. Alternatively, you may need to include data from a different source in your reporting, in which case the VLOOKUP function may be useful.

Note from the editor: This article was first published in December 2018 and has been revised for accuracy.

Watch This Video-

A pivot table is a type of worksheet that allows users to analyze data by grouping and aggregating it. To create a pivot table in Excel, you will need to follow the steps below:
1) Create a new workbook with the Pivot Table option selected.
2) On the Insert tab, select PivotTable Field List from the Analysis group.
3) Click on “Add Field” and then select an item from each field list.
4) Click on OK when done adding fields. Reference: where is a pivot table inserted.

Frequently Asked Questions

How do I create a pivot table in an Excel spreadsheet?

A: In Excel, go to the Data tab and select Pivot Table. Then you have a few options of how you would like your pivot table to be set up.

What is pivot table in Excel with example?

A: A pivot table is a spreadsheet function that allows you to summarize data in multiple columns and rows.

Related Tags

• how to create a pivot table in excel 2016
• pivot table example
• what is the first step for creating a pivot table
• what is a pivot table and how does it work