Hey Siri, What’s A Pivot Table?

Numbers App Icon In The macOS DockVisit Photics.TV (YouTube) to see a video of this review. On September 28, 2021, Apple updated “Numbers”, their spreadsheets app, to version 11.2. “Numbers” is part of the iWork suite of apps, which includes “Pages” and “Keynote”. This update is especially noteworthy because it includes a feature that data crunchers will love — Pivot Tables.

Here’s the official word from Apple. “What’s New — Pivot tables provide a powerful and flexible way to view and analyze data. Create beautifully formatted pivot tables in just a few clicks.” I can tell you that this is true. I tested it out and “Numbers” makes it really easy to create a Pivot Table.

The “Numbers” app even includes an elegant way of explaining what is a Pivot Table and it shows how to make one. You can start by using the “Pivot Table Basics” template. It includes some sample data and a Pivot Table.

Pivot Table — iWork — Numbers App

Basically, there are three areas to the first sheet of this template — instruction are at the left, the Pivot Table is at the upper-right, and the data is at the lower-right. There’s a second sheet to this template. It’s called “Pivot Table Practice”. This is a good place to go if you want to learn more about creating Pivot Tables.

Now, I think that the include tutorial skips an important step — actually creating a Pivot Table. So, the actual first step is to select a table with data. Once you do that, the “Pivot Table” icon (in the toolbar) should be usable. However, this action creates the Pivot Table on a new sheet. If you don’t like that, you can simply cut the table and paste it onto the previous sheet.

Let’s do the unthinkable, let’s read the instructions. “How To Use: Follow these steps to recreate the pivot table on the Pivot Table Basics sheet”

“1 — Open Pivot Options. On the Pivot table, click or tap the shortcut to view Pivot Options.” So I clicked the table and “Pivot Options” appeared in the “Organize” pane. What’s next?

“2 — Add values. Select Units in the Fields list to see a grand total of its values in the pivot table.” 5,171… interesting! 🧐

“3 — Create columns. Drag the Date field to the Columns section to add columns for each month.” There are now three columns for the months. The data only covers three months — January, February, and March.

“4 — Drag the Power and Product fields to the Rows section to see groups and rows for their values.” Now it’s starting to look like a useful spreadsheet.

“5 — Click or tap the edit details button on a field to choose summary and grouping options. You can also add, remove, and reorder fields to find other interesting pivot table combinations.”

As an example of this, you can click the little letter “i” icon to bring up the “Field Options” popup window. There you can change things around, such as viewing the year in quarters instead of monthly.

It looks like Apple was right. This is a fast way to display data in a more useful way. Now we can answer those questions in the headline. “Hey Siri, What’s a Pivot Table?”

She cites a Wikipedia article. It says, “Pivot tables are a technique in data processing. They arrange and rearrange (or “pivot”) statistics in order to draw attention to useful information. This leads to finding figures and facts quickly making them integral to data analysis. This ultimately leads to helping businesses or individuals make educated decisions.”

That answers the first question. What about another question, is Apple’s “Numbers” app a replacement to Microsoft’s Excel? I think that “Numbers” is a solid alternative to Excel. With the addition of Pivot Tables, there’s one less reason to use Excel. Numbers has certainly been my replacement for Excel. Why would I pay a subscription fee when “Numbers” already takes care of my spreadsheet related tasks?

As an example, with the Widgets app, I wanted to add the Catholic Public Domain Version of the Bible. It’s over 35,000 rows long. In order to add that data to an SQLite database, I needed to convert the data to comma separated values (CSV). “Numbers” got the job done.

To Be Technical — The process of converting the Catholic Public Domain Version of the Bible to a CSV file involved four apps — A web browser (probably Safari) to load the HTML pages, “Visual Studio Code” to clean up that raw data with regular expressions (This giant blob of text might have been saved as .TXT or .CSV), “Numbers” for easier sorting/viewing/checking of that data with the option of CSV exporting of those changes, and the “DB Browser for SQLite” app for adding the CSV file to the Widgets SQLite database. Excel was not necessary. I’m having trouble remembering how much I used the Numbers app though, as much of the work was with Visual Studio Code. You can read more about this project in Widgets 3.1 — Happy Easter!

“Numbers” has other impressive features too, like iCloud support and cross-platform support. While I don’t normally edit spreadsheets on my iPhone or iPad, it’s nice to have the option. Also, the collaboration features are improving. Apple states that you can, “Work together in the same spreadsheet from across town or across the world.”

Think of all the office workers in the world, with Excel on their office computers, but they don’t really use the app. Why would a business want to constantly pay for software that they’re not using? True, there are power-users that simply need Excel. But for the rest of us, the Numbers app works just fine. And with this recent update, it just got even better.