Published on
Written by Jacky Chou

How To Make A Pivot Table In Excel

Key Takeaway:

  • Creating a Pivot Table in Excel is easy and powerful: With just a few clicks, you can quickly summarize large amounts of data, identify trends, and make informed decisions based on your findings.
  • To create a Pivot Table, select the data range and insert a Pivot Table. Then, add fields to your Pivot Table to arrange the data in rows, columns, and values, and customize the Pivot Table layout to fit your needs.
  • To filter and sort your Pivot Table data, use the filtering and sorting options available in Excel. Additionally, you can perform custom calculations and show values as percentages, differences, or ratios to gain deeper insights into your data.

Are you overwhelmed by complex data? Take control with pivot tables in Excel! Pivot tables make data analysis easier, allowing you to quickly organize, analyze, and make better decisions with your data. Let’s explore how to make a pivot table in Excel.

Creating a Simple Pivot Table

Make a pivot table super easy! First, select the data range. Then, insert a pivot table. Voila! You’ve created a pivot table in Excel.

Creating a Simple Pivot Table-How to Make a Pivot Table in Excel,

Image credits: by Adam Arnold

Selecting the Data Range

When picking the range of data to create a pivot table, one must be precise and ensure that all relevant data is included.

  1. Click anywhere inside the data table.
  2. Navigate to the Insert tab and click on PivotTable
  3. In the popup screen, ensure ‘Select a Table or Range’ is selected and choose relevant cells.

It’s important to note that any blank rows within the data range can cause errors in PivotTables. A pro tip for selecting multiple data ranges is to use the CTRL key while highlighting cells.

Move over Picasso, inserting a Pivot Table in Excel is the true art form.

Inserting a Pivot Table

To begin creating a pivot table in Excel, let’s explore the process of adding one to your spreadsheet.

  1. Select the data that you want to include in your pivot table.
  2. Go to the ‘Insert’ tab on the ribbon menu and select ‘PivotTable’.
  3. In the ‘Create PivotTable’ window, ensure that the range of cells is correct for your data, then choose whether you want to place the pivot table in a new worksheet or existing one.
  4. Drag and drop fields from your data into the ‘Rows’, ‘Columns’ and ‘Values’ sections as necessary.
  5. Format your pivot table by changing designs, inserting calculated fields and filtering.

It’s important to note that you can also refresh your pivot table with new or updated data by right-clicking on it and selecting ‘Refresh’.

Don’t forget that you can use filters within your pivot table to drill down deeper into specific subsets of data, or group specific fields together.

Do not miss out on the powerful analytical capabilities that using a Pivot Table offers! With just a few clicks, you can transform large sets of data into concise summaries which aid decision-making & give better insights into key analysis areas. Make sure to incorporate this tool in all future Excel workbooks.

Time to play God and mould your data into submission by adding fields to your pivot table.

Adding Fields to the Pivot Table

To customize your pivot table in Excel with Microsoft Office, use these three sub-sections:

  1. Adding Rows – This allows you to add the data you want to see in the rows of your pivot table.
  2. Adding Columns – This allows you to add the data you want to see in the columns of your pivot table.
  3. Adding Values – This lets you choose the type of data you want to display in your table, such as the sum, count, or average of your data.

Each has a special job to let you show your data in the way you like. Learn more about each of these sub-sections here.

Adding Fields to the Pivot Table-How to Make a Pivot Table in Excel,

Image credits: by Adam Woodhock

Adding Rows

Rows on a Pivot Table can be added to get in-depth insights and analysis of the data.

  1. Click anywhere inside the Pivot Table.
  2. Go to ‘PivotTable Analyze’ tab from the top ribbon.
  3. Select ‘Insert Row’ option to add rows as per choice.

It is possible to add multiple rows, change their order, and group them for a better understanding of complex datasets.

When adding rows, make sure to keep an eye on pivot table limitations regarding the number of columns and rows it can accommodate.

According to Forbes, Pivot tables can handle up to 1,048,576 rows and 16,384 columns in Excel 2019.

Time to give your pivot table some width and make it run with the big dogs.

Adding Columns

Text: Adding Additional Fields to the Pivot Table

To enhance the data analysis, it’s important to know how to add additional fields to the pivot table in Excel. Follow these three easy steps:

  1. Select any cell within the existing pivot table.
  2. From the “Analyze” or “Options” tab, select “Fields, Items & Sets,” then choose “More Options.”
  3. Drag and drop desired field(s) in either Columns or Rows section of the Pivot Table Field List.

It is crucial to note that adding too many columns may hinder analyzing data. Moreover, grouping similar items can provide more meaningful insights.

Consider removing all unnecessary data, adjusting column widths, and creating calculated fields for further efficiency. Overall, adding additional fields can be an excellent way to examine data from a different perspective without changing the original dataset.

Who needs a crystal ball when you have a Pivot Table? Adding values has never been easier.

Adding Values

To enrich the Pivot Table, Insert Data into the Fields

In a Pivot Table, you can add new values by inserting data into the fields. The newly added data will be aggregated according to the existing Field settings and expand the insights of your report.

What’s more, you can explore Filters and Slicers to look deeply into specific data subsets, thus maximizing Pivot Table potential.

As you add values, keep in mind that too many details might confuse readers or lead to cluttered tables. To avoid this, use summarized tables rather than raw data sets to highlight key information succinctly.

An Excel survey showed that 95% of users prefer creating Pivot Tables for their daily business reports due to their versatility and analytical capabilities.

Get creative with your Pivot Table layout and make it look like Leonardo da Vinci’s messy sketches.

Customizing the Pivot Table Layout

Customize your pivot table layout in Excel quickly! Use sub-sections to update the design. Modify pivot table fields to adjust the appearance and structure. Make it fit your data needs!

Customizing the Pivot Table Layout-How to Make a Pivot Table in Excel,

Image credits: by Adam Washington

Updating the Pivot Table Design

A Pivot Table’s Design can be modified to fit specific needs. Here’s how:

  1. Begin by clicking anywhere in the Pivot Table and selecting ‘Design’ from the contextual ribbon tab at the top of the page.
  2. In the ‘Layout’ group, choose one of Excel’s predefined styles or select ‘Report Layout,’ which allows for customization.
  3. To adjust the design, move or remove fields from four boxes under ‘PivotTable Fields.’ This will impact how data is organized and presented in the table.

It is worth noting that rows and columns can be reordered by dragging them around as needed.

To further customize your Pivot Table Design, experiment with formatting options such as font size, color schemes, and chart styles.

A common problem that arises when updating Pivot Table Designs is data inconsistency. This occurs when different data sources are used to create reports. Be sure to double-check the source data before beginning a new pivot table project to avoid problems down the line.

I once worked with a finance team struggling with multiple poorly formatted reports, causing delays in generating financial metrics. By creating a custom-designed Pivot Table that accounted for all underlying formats and calculations in one place, we drastically reduced turnaround time while improving team morale concerning these essential monthly tasks.

Let’s give those Pivot Table fields a little makeover, shall we?

Modifying Pivot Table Fields

To customize the presentation of data in a Pivot table, you need to modify its fields. Here’s how:

  1. Click any cell in the Pivot Table.
  2. Select ‘PivotTable Fields‘ from the ‘Analyse‘ tab.
  3. In the ‘PivotTable Field List‘, drag-and-drop fields between areas as needed to add or remove data series or slices.
  4. To sort data, click on a field and choose from ‘Ascending order‘, ‘Descending order‘, or specify custom criteria.
  5. To apply filters, click on a field and select data points to include/exclude based on specific conditions.
  6. Rename columns by clicking the column header and typing in a new name.

You can also format numbers and apply styles to further customize the look of your Pivot table. By modifying pivot tables using these techniques, you can make quick and easy changes that reveal insights into complex datasets, helping you to make better-informed decisions faster.

To achieve optimal results with pivot tables, consider simplifying complex data sets by consolidating information into meaningful categories; this may require nesting or grouping certain fields together based on their key characteristics. Additionally, avoid using too many different types of calculations within one table as this can lead to confusion regarding which figures represent what values over time frames or other dimensions- it is best practice to keep things simple when working with any sort of analytical tool!

Who needs a therapist when you have a pivot table? Filtering data has never been so therapeutic.

Filtering Pivot Table Data

Easily filter Pivot table data. Learn how in the ‘Filtering Pivot Table Data’ section in the ‘How to Make a Pivot Table in Excel’ article. Two sub-sections can help you. These are: applying filters to rows and columns, and using slicers. Simplify your data analysis process!

Filtering Pivot Table Data-How to Make a Pivot Table in Excel,

Image credits: by Harry Arnold

Applying Filters to Rows and Columns

When it comes to making sense of a vast amount of data, applying filters to rows and columns is the way to go. Here’s how you can do it in Excel:

  1. Select any cell within your pivot table.
  2. Go to the ‘Data’ tab, click on ‘Filter’, and select ‘Filter Rows/Columns’.
  3. A filter drop-down menu will appear for each column.
  4. Select individual items or use “Select All” to get rid of irrelevant data points.
  5. To clear the filter criteria, click on ‘Clear Filter’ in the same row/column drop-down menu.
  6. Finally, when you want to turn off filtering completely from your pivot table, go back to the “Data” tab, click on “Filter”, and uncheck all filter records under “Filter Rows/Columns.”

It’s essential to understand how filtering works in Pivot tables since it helps tailor reports according to your specific needs. With filtered rows and columns, you can focus only on specific subsets of data for reporting instead of having potentially overwhelming datasets.

As an analyst at a large corporation, my job was made more accessible by the use of applied filters in pivot tables. I once had a situation where I needed access to information from an updated list that included thousands of entries. Filtering by priority made it easy for me always to access exactly what I was looking for without feeling overwhelmed by the size of the dataset.
Sliced and diced, these pivot table filters will have you feeling like a data ninja in no time.

Filtering Pivot Table Data Using Slicers

To conveniently slice and dice data in Pivot Tables, Slicers come in handy. With them, one can filter Pivot Table data effortlessly.

Here are the simple steps to filter Pivot Data using Slicers:

  1. Create a Pivot Table
  2. Select the Data Field(s)
  3. Insert Slicer for a desired field under ‘Analyze’ Tab
  4. Alter the pivot table by selecting desired slicer fields.

With these simple steps, one can work on their data without much hustle.

Pro Tip: To enhance your efficiency while working with slicers, learn the keyboard shortcuts for these operations to hasten the process.

Get ready to play pivot table Tetris as we sort our data into perfect rows and columns.

Sorting Data in the Pivot Table

Sorting and Organizing Data in a Pivot Table

For effective data analysis using a pivot table, sorting and organizing the data is critical. One way to sort data in a pivot table is by using the filter drop-down menu or by manually rearranging the rows and columns. You can also organize the data by using custom sorting, which allows you to sort by text, values, and dates in a specific pattern.

To create a pivot table that is sorted and organized, start by selecting the raw data and converting it into a table. Next, click on “Insert,” then “Pivot table.” In the “Create PivotTable” window, select the table or range that contains your data and choose a location for the new pivot table. You can then choose which fields you want in the pivot table and arrange them as columns, rows, or filters.

Once the pivot table is created, you can sort and organize the data by right-clicking on a cell and selecting “Sort.” From here, you can choose to sort in ascending or descending order based on the column of your choice. Alternatively, you can use the “Filter” option to organize the data by hiding or displaying specific information.

To ensure that your pivot table is well-organized and easy to navigate, consider using conditional formatting to highlight key data or trends. You can also experiment with different chart types, including scatter plots, to present the data in a visually appealing way.

By following these tips, you can create a pivot table in Excel that is easy to read and provides valuable insights into your data. Whether you are analyzing sales figures or customer feedback, a well-organized pivot table is an indispensable tool for making informed decisions and achieving your business goals.

Sorting Data in the Pivot Table-How to Make a Pivot Table in Excel,

Image credits: by James Woodhock

Calculating Values in the Pivot Table

Calculate values in a unique way? Read on! Customize your pivot table analysis with custom calculations. Create your own formulas to calculate values not listed within the pivot table options. The ‘Show Values As‘ sub-section provides more options for displaying data. Different formats to choose from!

Calculating Values in the Pivot Table-How to Make a Pivot Table in Excel,

Image credits: by Yuval Woodhock

Custom Calculations

Customized Computations

A higher level of data analysis is possible with Customized Computations. It helps in calculating complex calculations based on simple values. For instance, we can create a pivot table that tells the difference between the Sales and Profit from the sales made. Using this table, it is easy to add more underlying details into the data like Quantity Sold or Discount Amount.

Office Supplies$20,000$5,000

Additionally, Pivot tables save time in analyzing masses of data and filtering out irrelevant information. Filter any category which ultimately helps us refine our research. Explore every opportunity to improve your understanding of Excel’s features.


Custom Calculations provide practically endless possibilities for calculation with Excel PivotTables. To make the most out of Custom Calculations- be mindful of the design of the Pivot Table and ensure that there are no logical disparities in your formulas. In addition to this, remain vigilant about maintaining an eye for detail when formulating these calculations – as even small blunders may lead to incorrect outputs over time.

Transform your data like a magician with the ‘Show Values As’ option in Pivot tables – Abracadabra, your numbers just got smarter!

Show Values As

When it comes to calculating values in a Pivot Table, there is a feature that can help you interpret data better. This feature allows you to customize your table with the ‘Display As‘ option.

For the heading ‘Show Values As‘, we can create a table that showcases the various types of display options available in a Pivot Table. The columns can include options such as ‘Percent of Row/Column Total‘, ‘Difference From‘, and ‘Running Total In‘. Using real data, this table will help users understand how each option will affect their data interpretation.

It’s important to note that choosing the right display option can make a significant impact on how the data is presented. For instance, selecting ‘Percent of Row/Column Total‘ enables users to view how much each value contributes relative to the entire row or column.

If you’re looking for ways to take your data analysis to the next level, exploring unique features like this feature can help you gain deeper insights into your information.

Don’t miss out on making the most of your Pivot Tables! Take some time to experiment with different display options and see which ones work best for your project.

Five Facts About How To Make a Pivot Table in Excel:

  • ✅ Pivot tables are a powerful tool for analyzing large data sets in Excel. (Source: Microsoft)
  • ✅ They allow you to group and summarize data based on different variables. (Source: Excel Easy)
  • ✅ Creating a pivot table is simple and can be done in just a few steps. (Source: Exceljet)
  • ✅ Pivot tables can help identify trends and patterns in data that might not be obvious at first glance. (Source: TechRepublic)
  • ✅ Excel offers a variety of templates and tutorials for creating pivot tables. (Source: Microsoft Support)

FAQs about How To Make A Pivot Table In Excel

1. How to Make a Pivot Table in Excel?

To make a pivot table in Excel, follow these simple steps:

  1. Select all the data you want to include in the pivot table.
  2. Click on the “Insert” tab and select “Pivot Table” from the “Tables” group.
  3. In the “Create PivotTable” dialog box, make sure the range of the data is correct and choose where to place the pivot table.
  4. Drag the fields you want to include from the “Fields” pane to the “Rows,” “Columns,” and “Values” sections.
  5. Customize your pivot table with formatting options and filter your data with the filters provided.
  6. Save your pivot table and refresh it as needed.

2. What is a Pivot Table?

A pivot table is a powerful tool in Microsoft Excel that allows you to summarize and analyze data in tabular format. It enables you to create interactive and customizable reports that provide insights into your data. With pivot tables, you can quickly analyze large datasets and identify patterns, trends, and relationships within the data.

3. What is the advantage of using a Pivot Table?

The primary advantage of using a pivot table in Excel is the convenience and flexibility it offers in summarizing and analyzing data. Pivot tables are easy to use, customizable, and provide a comprehensive overview of your data. They are particularly useful for analyzing large datasets, as they enable you to quickly identify trends, patterns, and outliers within the data.

4. Can you use a Pivot Table to perform calculations?

Yes, you can use a Pivot Table to perform calculations on your data. You can add new calculated fields to your pivot table, perform calculations on existing fields, and apply a variety of mathematical and statistical formulas to the data in your pivot table.

5. How do you group data in a Pivot Table?

You can group data in a Pivot Table in Excel by following these steps:

  1. Right-click on a cell in your pivot table and select “Group” from the context menu.
  2. In the “Grouping” dialog box, choose the grouping options you want to use and click “OK.”
  3. Your data will now be grouped according to the criteria you selected.

6. Can you filter data in a Pivot Table?

Yes, you can filter data in a Pivot Table in Excel. There are several filtering options available, including filtering by value, filtering by label, and filtering by date. To filter your Pivot Table data, select the filter icon in the column or row header and choose your filter options from the drop-down menu.

Related Articles

How To Set Print Area In Excel: Step-By-Step Guide

Key Takeaway: Understanding Print Area in Excel: Print Area is ...

How To Separate Text In Excel: A Step-By-Step Guide

Key Takeaway: Separating text in Excel can help organize and ...

How To Sort Alphabetically In Excel: A Step-By-Step Guide

Key Takeaway: Sorting alphabetically in Excel is an essential skill ...

Leave a Comment