If you’re like many spreadsheet app users, you may be intimidated by pivot tables. But if you start with the basics and learn how to create a pivot table in Microsoft Excel, it can become your favorite feature.

What are pivot tables in Excel?

A pivot table provides an interactive way to organize, group, calculate, and analyze data. You can manipulate the same data in several different ways to see exactly what you need. PivotTables give you a robust way to work with a data set to spot patterns, review summaries, and calculate counts, averages, or totals.

Normally you would create a pivot table if you have a large amount of data. This is what makes the pivot table such a valuable tool; its ability to make large amounts of data more manageable for analysis .

Microsoft uses “Pivot Tables” as a single word in its documentation and interfaces surrounding “pivot tables”. Therefore, you may see both terms as you use the app, and we’ll cover them in this tutorial accordingly.

Make a basic pivot table in Excel

To start, select your data . You can create a pivot table from a range of cells or an existing table structure. Just make sure you have a header row at the top and no empty columns or rows.

So you have two ways to do the pivot table. You can use one of Excel’s recommended pivot tables, or create the table yourself.

Use a recommended pivot table

Like insert a chart in excel with the recommended chart options, you can do the same thing with a pivot table. Excel then reviews your data for tables that fit.

Go to the Insert tab and click on “Recommended PivotTables” on the left side of the ribbon.

Click Recommended PivotTables

When the window opens, you will see several pivot tables on the left. Select one to see a preview on the right. If you see one you want to use, select it and click “OK.”

Recommended pivot tables

A new sheet will open with the pivot table you chose. You’ll also see the PivotTable Fields sidebar on the right that allows you to edit the table, which we explain below.

Recommended pivot table inserted

Make your own pivot table

If you want to dive right in and create your own pivot table, go to the Insert tab and choose “Pivot Table” on the ribbon.

Click on pivot table to make your own

You will see a window appear for PivotTable From Table or Range. At the top, confirm the data set in the Table/Range box. Then decide if you want the table in a new worksheet or in the existing one. To analyze multiple tables, you can check the box to add it to the data model. Click OK.”

Pivot table settings box

You’ll then see the PivotTable and PivotTable Fields sidebar, ready for you to create your table or edit the recommended table you inserted.

Pivot table sidebar to build the table

Create or edit the pivot table

Using the PivotTable Fields sidebar, start by choosing the fields at the top that you want to include by checking the boxes.

Note: You can check and uncheck the boxes for the fields you want to use at any time.

Excel then places those fields in the boxes at the bottom of the sidebar where it thinks they belong. This is where you will decide how you want to place them on your table.

Fields available for the pivot table

Depending on the type of data in your sheet, you’ll see things like numbers in the Values ​​box, dates and times in the Columns box and textual data in the Rows box. These are the default values ​​for those data types, but you can move them anywhere you like.

As an example, we want to see our Months as columns instead of rows. Just drag that field from the Rows box to the Columns box and your table will update accordingly. Alternatively, you can use the dropdown arrows next to the fields to move them around.

Field moved to a different frame

If you have more than one field in a box, the order also determines the location in the pivot table. In this example, we have Department first and Location second in the Rows box, which is how they are grouped in the table.

Row Box Order

But by moving Location over Department, we see each of our locations as the main fields, which is what we want. Then we simply use the minus and plus buttons next to each location to expand the group and see the departments.

Changed the order of the rows

Because you can move the fields between the boxes with simple drag and drop actions, this allows you to easily find the best option for your analysis of data .

Filter or sort the pivot table

The advantages of use a table in excel include the ability to filter and sort your data as needed. Pivot tables offer these same features.

You’ll see built-in filters for your first column, and depending on your data layout, maybe more than one column. To apply a filter to the column, click the filter button next to the header and choose how to filter the data as you normally would in an Excel table.

Filter a column in pivot table

To sort, click the button and select a sort option.

Sort a pivot table

Add a table filter

also can apply a filter to the top level of the table. Using our example, we want to filter the entire table to see each Department, one at a time. Drag the field you want to use as a filter to the Filters box in the PivotTable Fields sidebar.

Field moved to Filters box

You’ll see your table update to bring this filter to the top. Then click the filter button to apply the one you want at that time.

Table filter applied

To remove this top-level table filter, simply drag the field out of the Filter box in the sidebar.

Well, there you have it! The basic basics you need to create a pivot table in Excel. Hopefully this tutorial gets you off to a great start with your very own pivot table!

RELATED:   Parallels 17 now lets you run Windows 11 on your Mac

Leave a Reply

Your email address will not be published. Required fields are marked *