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

The pivot table is one of Microsoft Excel ‘s most mighty — and intimidating — functions. Powerful because it can help you summarize and make sense of large data sets. Intimidating because you ‘re not precisely an Excel adept, and pivot tables have always had a reputation for being complicated .
The good news : Learning how to create a pivot board in Excel is much easier than you might ‘ve been led to believe .

Download 10 Excel Templates for Marketers [Free Kit]

But before we walk you through the process of creating one, let ‘s take a step back and make certain you understand precisely what a pivot table is, and why you might need to use one .

What is a pivot table?

A pivot table is a drumhead of your data, packaged in a chart that lets you report on and explore trends based on your information. Pivot tables are particularly useful if you have farseeing rows or column that hold values you need to track the sums of and well compare to one another .

In other words, pivot tables extract meaning from that apparently endless clutter of numbers on your screen. And more specifically, it lets you group your data in different ways so you can draw helpful conclusions more easily .
The “ pivot ” separate of a pivot table stems from the fact that you can rotate ( or pivot ) the datum in the mesa to view it from a unlike perspective. To be clear, you ‘re not adding to, subtracting from, or differently changing your data when you make a pivot. alternatively, you ‘re merely reorganizing the data so you can reveal utilitarian information from it .

Free Templates

10 Excel Marketing Templates

Tell us a little about yourself below to gain access today:

What are pivot tables used for?

If you ‘re still feeling a bite confuse about what pivot tables actually do, do n’t worry. This is one of those technologies that are much easier to understand once you ‘ve seen it in action .
The function of pivot tables is to offer user-friendly ways to promptly summarize large amounts of data. They can be used to better understand, display, and analyze numeral data in detail — and can help identify and answer unanticipated questions surrounding it .
here are seven conjectural scenarios where a pivot mesa could be a solution :

1. Comparing sales totals of different products.

Say you have a worksheet that contains monthly sales data for three different products — product 1, product 2, and intersection 3 — and you want to figure out which of the three has been bringing in the most bucks. You could, of course, look through the worksheet and manually add the corresponding sales figure to a run total every meter product 1 appears. You could then do the same for product 2, and product 3 until you have totals for all of them. Piece of cake, right ?
now, imagine your monthly sales worksheet has thousands and thousands of rows. manually sorting through them all could take a life. Using a pivot table, you can mechanically aggregate all of the sales figures for merchandise 1, product 2, and intersection 3 — and calculate their respective sums — in less than a moment .

2. Showing product sales as percentages of total sales.

Pivot tables naturally show the totals of each row or column when you create them. But that ‘s not the only figure you can automatically produce .
Let ‘s say you entered quarterly sales numbers for three break products into an Excel sheet and turned this data into a pivot table. The table would mechanically give you three totals at the bottom of each column — having added up each merchandise ‘s quarterly sales. But what if you wanted to find the percentage these product sales contributed to all party sales, quite than just those products ‘ sales totals ?
With a pivot table, you can configure each column to give you the column ‘s percentage of all three column totals, alternatively of just the column total. If three product sales totaled $ 200,000 in sales, for exemplar, and the inaugural product made $ 45,000, you can edit a pivot mesa to rather say this product contributed 22.5 % of all company sales .
To show product sales as percentages of sum sales in a pivot postpone, merely right-click the cell carrying a sales total and choice Show Values As > % of Grand Total .

3. Combining duplicate data.

In this scenario, you ‘ve just completed a web log redesign and had to update a bunch of URLs. unfortunately, your blog reporting software did n’t handle it very well and ended up splitting the “ view ” metrics for single posts between two different URLs. therefore in your spreadsheet, you have two offprint instances of each individual web log post. To get accurate data, you need to combine the opinion totals for each of these duplicates .
That ‘s where the pivot table comes into dally. alternatively of having to manually search for and combine all the metrics from the duplicates, you can summarize your data ( via pivot table ) by blog mail title, and voilà : the horizon metrics from those duplicate posts will be aggregated mechanically .

4. Getting an employee headcount for separate departments.

Pivot tables are helpful for automatically calculating things that you ca n’t easily find in a basic Excel table. One of those things is counting rows that all have something in coarse .
If you have a list of employees in an Excel sheet, for exemplify, and future to the employees ‘ names are the respective departments they belong to, you can create a pivot table from this data that shows you each department mention and the number of employees that belong to those departments. The pivot board effectively eliminates your undertaking of sorting the Excel sheet by department name and counting each row manually .

5. Adding default values to empty cells.

not every dataset you enter into Excel will populate every cell. If you ‘re waiting for new data to come in before entering it into Excel, you might have lots of empty cells that look confusing or need foster explanation when showing this datum to your director. That ‘s where pivot tables come in .
You can easily customize a pivot postpone to fill empty cells with a default respect, such as $ 0, or TBD ( for “ to be determined ” ). For large tables of data, being able to tag these cells quickly is a utilitarian feature when many people are reviewing the like sheet .
To mechanically format the empty cells of your pivot table, right-click your table and click PivotTable Options. In the window that appears, check the corner labeled Empty Cells As and enter what you ‘d like displayed when a cell has no other value.

How to Create a Pivot Table

  1. Enter your data into a range of rows and columns.
  2. Sort your data by a specific attribute.
  3. Highlight your cells to create your pivot table.
  4. Drag and drop a field into the “Row Labels” area.
  5. Drag and drop a field into the “Values” area.
  6. Fine-tune your calculations.

now that you have a better sense of what pivot tables can be used for, let ‘s get into the kernel of how to actually create one .

Step 1. Enter your data into a range of rows and columns.

Every pivot table in Excel starts with a basic Excel postpone, where all your data is housed. To create this table, merely enter your values into a particular put of rows and column. Use the topmost row or the topmost column to categorize your values by what they represent .
For example, to create an Excel mesa of blog post operation data, you might have a column listing each “ circus tent Pages, ” a column listing each URL ‘s “ Clicks, ” a column listing each post ‘s “ Impressions, ” and so on. ( We ‘ll be using that example in the steps that follow. )
how to create a pivot table step 1: enter your data into a range of rows and columns

Step 2. Sort your data by a specific attribute.

When you have all the data you want entered into your Excel sheet, you ‘ll want to sort this datum in some means so it ‘s easier to manage once you turn it into a pivot table .
To sort your data, click the Data yellow journalism in the top navigation barricade and select the Sort picture underneath it. In the window that appears, you can opt to sort your data by any column you want and in any decree .
For case, to sort your Excel sheet by “ Views to Date, ” select this column championship under Column and then select whether you want to regulate your posts from smallest to largest, or from largest to smallest .
Select OK on the bottom-right of the Sort window, and you ‘ll successfully reorder each row of your Excel plane by the number of views each blog post has received .
how to create a pivot table step 2: sort your data by a specific attribute

Step 3. Highlight your cells to create your pivot table.

once you ‘ve entered data into your Excel worksheet, and sorted it to your wish, highlight the cells you ‘d like to summarize in a pivot table. Click Insert along the top navigation, and select the PivotTable icon. You can besides click anywhere in your worksheet, select “ PivotTable, ” and manually enter the roll of cells you ‘d like included in the PivotTable .
This will open an choice box where, in addition to setting your cell range, you can select whether or not to launch this pivot table in a new worksheet or keep it in the existing worksheet. If you open a new sail, you can navigate to and away from it at the bottom of your Excel workbook. once you ‘ve chosen, suction stop OK.
alternatively, you can highlight your cells, choice Recommended PivotTables to the right of the PivotTable icon, and open a pivot postpone with pre-set suggestions for how to organize each course and column .
how to create a pivot table step 3: highlight your cells to create your pivot table
Note: If you ‘re using an earlier translation of Excel, “ PivotTables ” may be under Tables or Data along the top navigation, rather than “ Insert. ” In Google Sheets, you can create pivot tables from the Data dropdown along the top navigation .

Step 4. Drag and drop a field into the “Row Labels” area.

After you ‘ve completed Step 3, Excel will create a lacuna pivot board for you. Your next dance step is to drag and drop a field — labeled according to the names of the column in your spreadsheet — into the Row Labels area. This will determine what unique identifier — blog post title, product name, and so on — the pivot table will organize your data by .
For example, let ‘s say you want to organize a bunch of blogging data by post title. To do that, you ‘d plainly click and drag the “ Top pages ” airfield to the “ Row Labels ” area .
how to create a pivot table step 4: drag and drop a field into the rows label area
Note: Your pivot table may look different depending on which version of Excel you ‘re working with. however, the general principles remain the same .

Step 5. Drag and drop a field into the “Values” area.

once you ‘ve established what you ‘re going to organize your data by, your adjacent step is to add in some values by dragging a field into the Values area .
Sticking with the blogging data exercise, let ‘s say you want to summarize blog post views by championship. To do this, you ‘d simply drag the “ Views ” field into the Values area .
how to create a pivot table step 5: drag and drop a field into the values area

Step 6. Fine-tune your calculations.

The kernel of a particular value will be calculated by default, but you can easily change this to something like average, maximum, or minimum depend on what you want to calculate .
On a Mac, you can do this by clicking on the belittled i next to a measure in the “ Values ” area, selecting the option you want, and clicking “ OK. ” once you ’ ve made your survival, your pivot table will be updated consequently .
If you ‘re using a personal computer, you ‘ll need to click on the humble inverted triangle next to your measure and choice Value Field Settings to access the menu .
how to create a pivot table step 6: fine tune your calculations
When you ’ ve categorized your data to your like, save your work and use it as you please .

Digging Deeper With Pivot Tables

You ‘ve nowadays learned the basics of pivot table universe in Excel. With this sympathy, you can figure out what you need from your pivot table and find the solutions you ’ ra looking for.

For example, you may notice that the data in your pivot table is n’t sorted the way you ‘d like. If this is the case, Excel ‘s Sort function can help you out. alternatively, you may need to incorporate data from another reference into your report, in which case the VLOOKUP routine could come in handy .
Editor ‘s bill : This mail was primitively published in December 2018 and has been updated for comprehensiveness .
excel marketing templates

generator :
Category : How To

Related Posts

Leave a Reply

Your email address will not be published.