Move and Remove Fields and Items in Excel Pivot Tables

Microsoft Excel is one of the most powerful tools for data analysis, reporting, and visualization. Among its most potent features is the Pivot Table, great tool for data handling due to its large capacity for customizing the field arrangement. You can easily move and remove fields and items in your pivot table for managing and displaying your data according to your requirements.

In this article, we will explore in-depth how to move and remove fields and items in Excel Pivot Tables.

Key Takeaways:

• Pivot Tables are powerful tools in Excel for summarizing and analyzing large datasets efficiently.
• Fields in Pivot Tables can be placed in Rows, Columns, Values, or Filters areas to organize data dynamically.
• You can move fields using drag-and-drop, directly in the Pivot Table, or via right-click options.
• Individual items within fields can also be rearranged or filtered to customize data display.
• Removing unnecessary fields or items helps declutter Pivot Tables and makes analysis more focused.

 

Exercise Workbook:

Understanding Pivot Tables

Before diving into moving and removing fields, it’s important to understand what Pivot Tables are and how they work.

A Pivot Table is a data summarization tool that automatically sorts, counts, and totals data stored in a table or spreadsheet. It allows you to reorganize your data dynamically, making it easier to analyze.

Pivot Tables are made up of fields, which correspond to column headers in your source data. These fields can be placed in different areas of a Pivot Table:

• Rows: Fields placed here appear as row labels.
• Columns: Fields here appear as column labels.
• Values: Numeric fields typically go here for calculations such as sum, average, or count.
• Filters: Fields placed in this area can filter the data shown in the table.

Each of these areas allows for customization, which brings us to the importance of moving and removing fields and items.

 

Moving Fields in a Pivot Table

Using Drag-and-Drop in the Pivot Table Field List

The Pivot Table Field List is a panel that displays all the available fields from your dataset. To move a field in a Pivot Table, first click anywhere inside the Pivot Table to make the Field List appear. Then locate the field you want to move and click and hold its name. While holding the mouse button, drag the field to the desired area, such as Rows, Columns, Values, or Filters. Once the field is positioned where you want it, release the mouse button to drop it in place.

Moving Fields Directly in the Pivot Table

You can also move fields directly within the Pivot Table itself by hovering over the field label in the Pivot Table, then clicking and dragging it to a new position. Once you have it in the desired location, release it to drop it there.

Using Right-Click Options

STEP 1: Let us work with rows first. Let’s move the TONIC row down one spot.

Right-click on the TONIC row and go to Move > Move “TONIC” Down

It has now moved one spot below

STEP 2: You can also move it by typing the value as well!

Let us move it to the last Product row. Type in TONIC at the last Product row.

And just like that it has now moved to the bottom!

STEP 3: What if we want to update the hierarchy of our Fields? Right click on any Product row and go to Move > Move “PRODUCTS” To End

Now it’s the Salesperson that is now showing the Product Details

STEP 4: The same tips apply for the columns as well. Let us try it out!

Let’s move the 2014 column to the very end.

Right click on the 2014 column and go to Move > Move “2014” to End

And there you have it!

STEP 5: To remove the field. You simply right click on the field and select Remove.

In our scenario, right click on BOTTLES and select Remove “PRODUCTS”

You are now left with the Salesperson field for your rows!

This is how you easily move and remove fields and items in Pivot Table.

 

FAQs

1. What is a Pivot Table in Excel?

A Pivot Table is a tool in Excel that allows you to summarize, sort, count, and analyze large amounts of data quickly. It reorganizes your source data dynamically, so you can view it from different perspectives. Pivot Tables are composed of fields, which correspond to the column headers in your dataset. You can place fields in Rows, Columns, Values, or Filters areas to control how data is displayed. This makes Pivot Tables a versatile option for reporting and decision-making.

2. How can I move a field in a Pivot Table?

You can move a field by clicking anywhere in the Pivot Table to open the Field List, locating the field, and dragging it to the desired area such as Rows, Columns, Values, or Filters. Alternatively, you can drag the field directly within the Pivot Table itself to rearrange it. Another method is to right-click on the field and use the “Move” options, such as Move Up, Move Down, Move to Beginning, or Move to End. Moving fields helps analyze data from different angles without changing the source data. It’s a quick way to reorganize your Pivot Table layout.

3. How do I remove a field from a Pivot Table?

To remove a field, you can either uncheck it in the Pivot Table Field List or right-click on the field in the Pivot Table and select “Remove Field.” Removing fields doesn’t delete the underlying data; it only removes it from the current Pivot Table view. This is helpful when your Pivot Table becomes cluttered or you want to focus on specific data points. Fields in the Values area can also be removed if their calculations are no longer needed. Regularly removing unnecessary fields ensures cleaner and more readable Pivot Tables.

4. Can I rearrange individual items within a field?

Yes, you can rearrange individual items manually by clicking and dragging them to a new position within a row or column. Excel also allows you to sort items alphabetically, numerically, or using a custom list to maintain a specific order. This is useful when the default sorting does not match the logical or business order you need. Rearranging items helps emphasize important data points or organize information for easier analysis. Using both manual reordering and sorting options ensures your Pivot Table remains both accurate and visually clear.

5. What are some tips for managing fields and items in Pivot Tables?

Plan your Pivot Table layout before adding fields to avoid unnecessary rearrangements. Use clear and descriptive column headers in your source data for easy identification. Avoid overcrowding the Pivot Table with too many fields, which can make it confusing. Apply sorting and filtering to organize items efficiently and highlight key insights. Always refresh your Pivot Table after making changes to ensure calculations and data display remain accurate.