Conquering Pivot-Table-Phobia

Nothing strikes terror in people like someone asking for help creating a pivot table in Microsoft Excel.  This blog is intended to demystify the creation of a pivot table.

First, let’s start with an understanding of what a pivot table does.  A pivot table takes a range of raw data and allows for sorting, comparing, and filtering to produce a useful view of the data.

In the example below, we have data that compares laptop models. The boss decided he wants to buy 1 Dell and 1 Xi3 and wants to know how much each total cost is and the grand total of the purchase.  Our intern did the research and created the spreadsheet below.



We want to whittle this down to just the data we want. To do this, we should use a pivot table since we have to filter both the brand and the total cost.

Now, we will insert a pivot table into the workbook. We need to click on the Insert tab and choose Pivot Table from the Tables section.


The Create Pivot Table box appears. We will take the default values and click OK



We will now check the Brand box to show the column that has the names of the laptops.

By default, the field is placed in the Rows area.


Since we want to show the laptops in columns, we will move the Brand field to the Columns area


Now we just want the total cost data , so we will drag the Total Cost field to the Values area. This will give us the values plus a grand total


And now, we want to see just Dell and Xi3

We can click on the drop down arrow in the Column Labels box. We uncheck HP and Asus.


We now have the total individual cost of the Dell and Xi3 laptops and the grand total.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s