The Excel Skinny

A blog about Excel and its users

I steered clear of creating pivot tables on Microsoft Excel for many years.  Simply hearing the phrase "pivot table" in the office made me run the other way.  I didn't even know what they did, they just seemed extremely complex and daunting.  

I was eventually asked to create a pivot table and within the hour my fears were gone.  With Microsoft Excel for Mac, creating a pivot table is easily achieved in just a few steps using the toolbar.  No formulas or shortcuts necessary! 

Step 1: The original data. For this example our data for the pivot table will be in Sheet 1.

 

Step 2: Select the cell where you want the pivot table to appear. We are going to use A1 on Sheet2.

Step 3: Select the Data tab from the toolbar in Microsoft Excel.

 

Step 4: Click the small arrow next to the PivotTable icon and select the Create Manual PivotTable… option.

Step 5: The Create PivotTable dialog box will appear. Here we must select the original data table on Sheet1 for the “Use a table or a range in this workbook” Location: box.

To do so, click on Sheet1 and highlight your table:

 

This will auto-populate the information into the Create PivotTable dialog box. Click OK.

 

Step 6: Now the Create a PivotTable box will close and a PivotTable builder will appear.

 

In the upper portion labeled Field name, select the fields you wish to add to your pivot table. I have selected Order Number, Product, Unit Price, and Quantity.

I then moved Order Number from the Values box to the Row Labels, because I do not need a value for the Order Number.

Whenever you are satisfied with your pivot table, that should be changing in the background as you modify the options in the PivotTable builder, click the X to close the builder dialog box.

Step 7: Edit the cell with Row Labels to the correct terminology: In my example that would be Order Number.

And there you have it! A quick and simple way to make beautiful pivot tables for Microsoft Excel on Mac. Please let us know if you have any questions!