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.

Advertisement

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.
Advertisement
  • 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
Advertisement
  • 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

LEAVE A REPLY

Please enter your comment!
Please enter your name here

FEATURED POSTS

network discovery keeps turning off server 2016

How to Fix Network Discovery If it Keeps Turning Off in Server 2016

Does network discovery keep turning off in your Windows server 2016? It is likely that one of its dependent services is not...
how to install windows 10 1903 update manually

How to Install Windows 10 1903 Update Manually

Windows 10 1903 Update was released in May, 2019. But some users are not yet offered the update via automatic update. The...
spotify web player not working

Spotify Web Player Not Working [Fixed]

Introduction Spotify Web Player may stop working for you with the following error messages: "Spotify Web Player an Error...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
DHCP Relay agent

DHCP Relay Agent: Configuration in Windows Server 2016

What is a DHCP Relay Agent? A DHCP Relay Agent allows DHCP clients in a different network subnet to...

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...

BEST OF ITECHGUIDES

powershell.exe -command

Powershell.exe Command: Syntax, Parameters and Examples

Introduction You may be wondering why write on Powershell.exe Command. Are there special commands for Powershell.exe? Yes! When you...
Absolute Reference vs Relative Reference Excel

Absolute Reference vs Relative Reference Excel: Quick Guide

What is Absolute Reference in Excel? Absolute reference in excel allows you to reference a particular row or column...
powershell delete folder or File

How to Delete Folders or Files in PowerShell

Introduction You can delete folders and files in PowerShell using the Delete method or Remove-Item Cmdlet. This guide shows...
how to install windows 10

How to Install Windows 10 on a Hard Drive with Pictures (Step-by-Step)

What you need to Install Windows 10 on a Hard Drive To install Windows 10 on a hard drive...
Windows 10 Network Discovery Keeps Turning Off

How to Fix Network Discovery If it Keeps Turning Off in Windows 10

Does your Windows 10 network discovery keep turning off? It is likely that one of its dependent services is not started.

RECENT POSTS

disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
bootrec /fixboot access is denied

How to Fix BootRec /FixBoot Access is Denied Error in Windows 10

Introduction BootRec /FixBoot Access is Denied Error in Windows 10? This error is likely caused by corrupt EFI directory.
Reset Windows 10

How to Reset Windows 10 (2 Methods)

Introduction If your Windows 10 is broken, one available way to fix it is to use Windows 10 reset....
system restore windows 10

How to Enable and Use System Restore in Windows 10

Introduction System restore in windows 10 is a very important tool that is probably ignored by most users. But...
windows has stopped this device code 43

How to Fix Windows Has Stopped this Device Code 43 Error

Introduction Sometimes you may receive "Windows has stopped this device code 43" error. This error is likely to come...
Windows could not automatically detect this network's proxy settings

How to Fix “Windows Could not Automatically Detect this Network’s Proxy Settings”

Introduction You suddenly lose the ability to connect to the internet. Then you run network troubleshooter and it returns...
printer offline

5 Ways to Restore Your Printer Online If Status is Offline

Introduction Is your printer offline? Setting it online is very simple but sometimes it is more than just setting...

How to Fix “BootMgr is Missing” Error in Windows 10

Introduction If you receive Fix "BootMgr is missing" Error in Windows 10, the default response is panic! But you...
windows 10 search featured

How to Fix Windows 10 Search If it Stops Working

Introduction Are you having troubles with Windows 10 search? Apparently, it is a fairly common problem with a number...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...

MUST READ

WhatsApp web

How to Use WhatsApp Web from a Browser on Your Computer

Introduction WhatsApp web is a WhatsApp service that allows you to use WhatsApp from a browser on your computer....
windows 10 lock screen timeout

How to Change Screen Time Out Setting in Windows 10

Introduction There are two easy ways to change Windows 10 lock screen timeout settings: Desktop...
SysWOW64 and File System Redirector Explained

SysWOW64 and File System Redirector Explained

Introduction A Windows 64-bits OS has a SysWOW64 folder. It also has a System32 folder. These folders contain OS...
how to login to spotify without facebook

How to Login to Spotify Without Facebook

This guide shows you how to login to spotify without Facebook if you are creating a new Spotify account or if you...

How to Fix “BootMgr is Missing” Error in Windows 10

Introduction If you receive Fix "BootMgr is missing" Error in Windows 10, the default response is panic! But you...

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