How to Make a Pivot Table in Excel

-

|

Introduction

A Pivot Table allows you to analyze, summarize and calculate large data to help find relationships. With a Pivot Table in Excel you can see patterns and trends.

In this guide you will learn how to make a Pivot Table in Excel.

How to Make a Pivot Table in Excel

The table below shows a table of sales divisions of a company, the subdivisions and information about products.

If you are asked to find the average price of products in each division it will be time consuming to get that information directly from the table.

You may also want to know the total number of products in each division, by subdivision. A Pivot Table will help you easily group the data so as to extract and analyze the data.

You can make a Pivot Table using Excel’s recommended PivotTables. You could also make a Pivot Table from a blank PivotTable. In the next two sections, I will demonstrate each.

How to Make a Pivot Table With Excel’s Recommended PivotTables

In this example, I will show you how to make a Pivot Table in Excel using Recommended PivotTables.

Here are the steps:

  • Select the data you want to plot a Pivot Table for, including the table headers.
 How to Make a Pivot Table With Excel Recommended PivotTables - select data including column headers
  • Click the Insert tab. Then click Recommended PivotTables.
 How to Make a Pivot Table With Excel Recommended PivotTables  - click Insert tab. Then select Recommended PivotTables
  • Excel will open different pre-plotted PivotTables. On the left hand side, select any recommended PivotTable to previou it. Select the one that meets your need. Then click Ok.
 How to Make a Pivot Table With Excel Recommended PivotTables - select a Recommended PivotTable
A new PivotTable will be created in a new worksheet.

The PivotTable I selected have organized the original data in a way that can be easily analyzed. For the PivotTable to make sense, you will need to change some of the labels. For example, cell A4 is labelled Row Labels. From the original data, this is Subdivisions. Change cell A4 to Subdivision.

Also, cell B3 is called Column Labels. This is Divisions. Change this as well. You may also wish to rename the worksheet to a more recognizable name.

Here is the updated PivotTable.

How to Make a Pivot Table in Excel from a Blank PivotTable

You can also make a Pivot Table in Excel from a blank PivotTable.

Here is how:

  • Select the data you want to make a Pivot Table from, including the table headers.
  • Then click the Insert tab and select PivotTable. The Creat PivotTable option will load.
  • To create the PivotTable using the default options, click Ok. This will create a blank PivotTable as show below.

The next step is to insert data into the blank Pivot Table in Excel. This is as simple as dragging and dropping the columns to the Fields, Columns, Rows or Values boxes.

To make a Pivot Table similar to one in the last example:

  • Drag Division to Columns box. Then drag Subdivision to Rows box. Finally drag Number of units to Values box. This will produce a Pivot Table as shown in the image below.
Add data to blank Pivot Table in Excel
You can modify the column labels as we did in the previous section.

How to Customize a Pivot Table in Excel

The image below shows a Pivot Table in Excel. It is created from the original data shown in the second image below.

The Pivot Table shows the data organized by Divisions and Subdivisions. It then shows the total units and average Price per unit for each product in a Subdivision.

How to Customize a Pivot Table in Excel - finished Pivot Table sample
How to Customize a Pivot Table in Excel  - raw data

In this section, I will walk you through how to make a Pivot Table similar to the one above.

Here are the steps:

How to Customize a Pivot Table in Excel  - blank Pivot Table
  • Next, drag Division and Subdivision columns to the Rows box.
How to Customize a Pivot Table in Excel - drag column headers to Rows box
  • Then, drag Number of units and Price per unit to the Values box.
How to Customize a Pivot Table in Excel - drag column headers to Values box
By default when you add column headers to the Values box, Excel calculates the column as a SUM. But we need the last column, Sum of Price per unit as an Average, not a SUM.
  • To turn that column to average, double-click it. It will open for editing.
  • Beneath the Summarize value field by, select Average. Then click Ok. The column will be changed from Sum to Average. Excel will also label it accordingly.
The final part of this section is to change the reporting layout of the Pivot Table.

To change the layout to show Division and Subdivision in two separate columns with subtotals:

  • Click anywhere on the Table. Then click Design tab.
  • Next, click the Report Layout drop-down and select a layout. For this guide I will use Show in Tabular Form. The result is shown in the second image below.
As you can see from the image above, the last column is not formatted. You can format it as currency and make it 2 decimal places. You can also change the colors of the subtotals.

Conclusion

This guide showed different ways you can make a Pivot Table in Excel. The guide showed how to make you can make a Pivot Table using Recommended PivotTables.

It also showed how to make one from a blank Pivot Table. Finally, the guide concluded by demonstrating how to customize a Pivot Table in Excel.

I hope you found it insightful. If you have any question or comment use the “Leave a Reply” form at the end of the page.

Other Helpful Guides

Additional Resources and References

  • Was this post Helpful?
  • YesNo

LEAVE A REPLY

Please enter your comment!
Please enter your name here

ADVERTISEMENTS

FEATURED POSTS

Best Christian Movies on Netflix

The 10 Best Christian Movies on Netflix

This Itechguide reviews the 10 best christian movies on Netflix. The movies are listed in reverse order – with the 10th movie...
How to Change Orientation in Google Docs

How to Change Orientation in Google Docs

This Itechguide teaches you how to change orientation in Google Docs. The guide covers steps to change orientation in Google Docs from...
How to Delete Google Photos

How to Delete Google Photos

This Itechguide teaches you how to delete Google Photos. The guide offers steps to delete Google Photos from the Google Photos App...
How to Install Windows 10 1909 Update Manually

How to Install Windows 10 1909 Update Manually

This Itechguide teaches you how to install Windows 10 1909 update manually. Windows 10 1909 (otherwise known as Windows 10 November update)...
How to Create a Poll on Facebook

How to Create a Poll on Facebook

This Itechguide teaches you how to create a poll on Facebook. You can create a poll on Facebook from a Facebook page...

AMAZON DEALS

TRENDING POSTS

Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...
Spotify No Longer Supports this Version of Microsoft Edge

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...
Windows 10 Won't Boot

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

ADVERTISEMENTS

BEST OF ITECHGUIDES

How to Follow Someone on Facebook

How to Follow Someone on Facebook

This Itechguide teaches you how to follow someone on Facebook. The guide covers steps to follow someone on Facebook from a PC...
How to Calculate Variance in Excel

How to Calculate Variance in Excel

Introduction Variance measures the spread between numbers in a data set. You can calculate variance in Excel using the...
How to Delete Dropbox Account

How to Delete Dropbox Account

This Itechguide teaches you how to delete Dropbox account. If you Delete Dropbox account, your data is gone forever!
How to Unsubscribe on YouTube

How to Unsubscribe on YouTube

It is extremely easy to unsubscribe on YouTube. This is a step by step guide on how to unsubscribe from a YouTube...
Steps to Fix Windows Server 2016 Automatic Repair Loop

How to Fix Windows Server 2016 if Stuck at Automatic Repair Loop

Introduction This guide demos how to fix Windows Server 2016 automatic repair loop. Automatic repair loop means that your...

RECENT POSTS

How to Change Facebook Page Name

How to Change Facebook Page Name

This Itechguide teaches you how to change Facebook page name. The guide covers steps to change Facebook page name from a PC...
How to Change YouTube Channel Name

How to Change YouTube Channel Name

This Itechguide teaches you how to change YouTube channel name. The guide covers steps to change YouTube channel name from a PC.
How to Bold Text on Facebook Post

How to Bold Text on Facebook Post

This Itechguide teaches you how to bold text on Facebook post. The guide covers steps to bold text on Facebook post from...
Best Crime Documentaries on Netflix

The 10 Best Crime Documentaries on Netflix

This Itechguide reviews the 10 best crime documentaries on Netflix. The documentaries are listed in reverse order – with the 10th documentary...
Best Netflix Series

The 10 Best Netflix Series

This Itechguide reviews the 10 best Netflix series. The series are listed in reverse order – with the 10th series on top....

AMAZON DEALS

MUST READ

How to Install Windows 10 1909 Preview Build

How to Install Windows 10 19H2 Preview Build

Introduction If you are a member of Windows 10 Insider Program you can install Windows 10 19H2 Preview Build....
How to Make Photos Private on Facebook

How to Make Photos Private on Facebook

This Itechguide teaches you how to make photos private on Facebook. The guide covers steps to make photos private on Facebook from...
How to Make a Line Graph in Google Sheets

How to Make a Line Graph in Google Sheets

Introduction You can represent your data visually using a line graph in Google Sheets. This guide demonstrates how to...
How to Cancel YouTube Red (Now YouTube Premium)

How to Cancel YouTube Red (Now YouTube Premium)

Introduction You can cancel YouTube Red (or Premium) during the trial period. You could also cancel at any time...
How to Cancel Amazon Order

How to Cancel Amazon Order

This Itechguide teaches you how to cancel Amazon order. Guide offers steps to cancel Amazon order from a PC (Amazon.com) or from...

AMAZON DEALS

By using this website you agree to accept our Privacy Policy and Terms & Conditions