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.

excel-1

 

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.

excel-2

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

excel-3

 

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.

excel-4.jpg

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

excel-5

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

excel-6.jpg

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.

excel-8

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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