How To Use Pivot Tables On Mac In 2022

Not too long ago, we covered how to use the Numbers app on Mac. Specifically, we looked at the basics as well as some advanced techniques you can use within this app. One of the advanced techniques we looked at was pivot tables. And in this post, we’re going to explore pivot tables on Mac in a lot more depth.

For those that don’t know, the Numbers app on Mac is an app that mirrors Microsoft Excel. It allows you to create and manage spreadsheets, acting as a great tool for viewing, organizing, filtering, and showcasing data.

Whether you’re using Numbers at home for personal use or looking to work this app for a living, this tutorial should give you an idea of what pivot tables are, how they work, and how and why you should be using them.

Also, to be clear, we aren’t going to be looking at Excel pivot tables. While I believe this feature works relatively similarly on both platforms, we’re just going to be covering the Numbers version of this feature today.

Alright, let’s get to it!

What are pivot tables on Mac?

First things first, let’s cover what pivot tables on Mac are. After all, I’m sure there are a lot of users who have heard of this feature and have been told they need to use it, but aren’t really sure what it is.

To put it simply, a pivot table is just an alternative way to display a group of data. That’s it, nothing too crazy or mind-blowing. It’s a lot like a normal table of data, except that you can manipulate and change the way the data is displayed without destroying the data.

For instance, say you just want to see monthly data from months that end in “-er”. With a normal table, you can only scroll through and look at that data. With a pivot table, however, you can quickly check and uncheck some boxes, and suddenly, you’re looking at the four months out of the year that end in “-er”.

Of course, you can do a lot more with pivot tables, but that’s the basic idea. Take a bunch of data that you’ve already entered, and start filtering through it faster than you could otherwise.

How to use pivot tables on Mac

Alright, now that you have an idea of what pivot tables on Mac are, let’s get into the specifics of how to use them. Again, these are pretty simple, so don’t worry too much. And remember, you’ll learn the most about pivot tables by playing around with them yourself, so don’t hesitate to follow along in a Numbers spreadsheet.

Choose your source data

The first thing you’re going to need to do when creating pivot tables on Mac is to choose your source data. Pivot tables are always made from an existing source of data, so you need to start by selecting the data you’re going to be using.

This data should generally be formatted as a typical spreadsheet table. Rows and columns of data organized by headings. If you aren’t sure how to do this, then go ahead and refer back to our Numbers Basics guide.

To choose your source data for a pivot table, just select the rows, columns, and/or data you want to use. Then, in the Menu Bar, select Organize, then Create Pivot Table. From the dropdown menu, select either On Current Sheet (this will select all of the data in this spreadsheet) or For Selected Cells on Current Sheet (this will only create the pivot table based on the data you’ve selected).

Once you choose one of these options, you should see a pivot table appear instantly alongside your data.

To view the data that you’ve just added, go ahead and look to the right of the Numbers window. Here you’ll see the Pivot Options sidebar.

If you’ve labeled your columns in your original table (i.e., not the pivot table) then you should see corresponding Fields in this sidebar with empty checkboxes next to each of them. Go ahead and check each of these boxes. That will populate your pivot table with data, like so:

And that’s it! That’s how you create a simple pivot table from your data.

Turning fields into columns and rows

Pretty quickly, you should be able to get an idea of how the pivot table helps you break down data. You can now turn data “on” or “off” by checking or unchecking these boxes. So if your columns are separated by months, years, employees, whatever, you can quickly sort through them.

This is only the beginning how you can organize pivot tables on Mac, though. You can add rows and columns from the fields that your pivot table contains by dragging and dropping those fields into the Columns and Rows boxes. Let’s explain that in a little more detail right now.

In the right-hand side panel, you should see three sections. Fields, Columns, and Rows. Values is there too, but we’re not looking at that just yet.

Bring your attention (and your mouse) over to the Fields section. Hover your mouse over any of the fields (the labels with checkboxes next them) and drag and drop that field into the Columns box. Like so:

Immediately, you should notice the pivot table change. Go ahead and remove that field from the Columns box by dragging and dropping it outside of that box. Anywhere on your spreadsheet and it’ll disappear.

Next, drag and drop one of the fields into the Rows box. That might change your pivot table to look like this:

The more fields you have, the more you can experiment with this. Essentially, this is giving you new ways to explore the relationships within your data. You can also use it to better organize your data, separating columns and rows based on various fields.

Filtering a pivot table to make data more clear

Next up, you can use filtering to make pivot tables on Mac easier to read. Filters allow you to focus on specific pieces of data, excluding bits that aren’t relevant to what you’re currently measuring for.

And best of all, you can turn filters on and off as you work. So you’re never locked into the way you’ve decided to filter data; changing views is easy.

Alright, so here’s how to do it! First, make sure that your pivot table is selected. To select it, just click anywhere on the pivot table.

Then, in the top-right of your screen, choose Organize. Then, choose Filter.

Select the dropdown menu labeled Add a Filter… and choose any of your pivot table fields.

You can choose an option from the Quick Filters that appear. This is helpful if you’re trying to rule out options like “Nil” or “0”, etc. You can just uncheck them with the Quick Filter option, and they’ll disappear from your pivot table’s view.

Otherwise, you can create a rule. There are number-based rules, alphabetic rules, and so on, that you should be familiar with. You can set rules to hide or show values below or above a certain threshold.

To remove a filter, just click the faint trashcan icon next to it on its right side. Or, as mentioned, you can disable and enable filters for your pivot table by clicking the Filters switch at the top-right of the screen.

Refreshing your pivot table

Last but not least, we’re going to be looking at how you can refresh your pivot tables on Mac. After all, you’re probably going to be adding data and columns to your source table as you work. When you add this data, you want your pivot table to reflect the changes you’ve made.

This is incredibly easy to do! Just select your pivot table, then click Organize in the top-right of the screen, then choose Pivot Options just below that.

You should see a little refresh wheel in this area, next to your Source Data. Clicking this refresh wheel will instantly update the data in your pivot table to match that of your source table.

And that’s it! That’s all it takes to update your pivot table information.

When should you use pivot tables on Mac?

Before closing out this post on pivot tables on Mac, I did want to discuss when you should use pivot tables. After all, they’re only useful in certain situations, and you don’t want to spend time configuring and using them when you don’t need to.

In general, the time to use a pivot table is when you already have a huge table of data and you want to start breaking that data down. This can be difficult to do with a traditional data table, but it’s maddeningly easy with a pivot table (as we’ve covered).

That’s the primary use case for a pivot table. It’s a quick and efficient way to parse through a large amount of data, bringing what you need to know to the forefront quickly and conveniently.