S1E1-Pivot Table

Hi, welcome to the first episode of Relax with Excel where you may learn something about Excel but will probably fall asleep.  Then again maybe you’ll learn in your sleep.  In this episode we’re going to cover the basics of a Pivot Table. 

First off, what is a Pivot Table.  Basically, it’s a way to quickly summarize and analyze data.  It can turn an ordinary table of data into a dynamic table that you can quickly change the column and rows fields or pivot them around to analyze your data. 

For example, if you have a table that’s a mailing list, you may have 5 columns: Name, Street Address, City, State and Zip code.  The rows would be the individual address records for someone like Oprah Winfrey or Tom Hanks. An example of the type of question a Pivot Table can help you answer would be: How many people live in New York or how many addresses have the word Terrace in them. 

Let's pretend you have a table that has over a thousand names and addresses and put them into Excel.  It’s in an Excel worksheet.  Again, it has five columns.  The first row would be the column fields: Name, Street Address, City, State and then Zip.  Any rows after would be the mailing list records.  For example, in row 2, column 1 would be Oprah Winfrey.  Column 2 would be 123 Main Street.  Column 3 would be New York City.  Column 4 would be New York and Column 5 would be 11354.  Of course, Oprah doesn’t live there, but you get the idea. 

Now pretend you have rows 3 –1000 with names and addresses of other celebrities.  This would mean you are quite the person, maybe a talent agent.  Click a cell with a value in it and Excel is usually good at figuring out how large of a range to include when creating a Pivot Table. Next in the Ribbon, click on the Insert Tab, then click the PivotTable icon. 

A smaller window will show up call PivotTable from table and range. In the field that has the label Table/Range it should show the range of cells that include all the columns and rows you need to use. If it doesn’t, you may need to select the range at this point. 

You can then choose where to place the Pivot Table, either in a New Worksheet or an Existing worksheet.   

This is probably the most basic items you’d want to choose from in this window to set up your Pivot Table.  If all is good, click the OK button. 

At this point you‘ll be back in a worksheet with a pane usually on the left where you can select the PivotTable Fields.  There’s an area where you can choose your fields.  In this case, it’ll be like Name or City.  

Then there’s an area where you can put those fields in.  It will either be in a Filters, Column, Row or Values area. 

Let say for this example we want to know how many celebrities live in each state. You would click on the Name field and drag it into the Value areas.  Excel will count the number of names, but that would be a total count, so if you already had 1000 records of names in the range, it’s not that useful.  But if you wanted to know the breakdown of names in each state, then select the State field and drag it in the Rows area. This will give you a top to bottom row view of the states in one column and the next column will give you the count per state.   

Now you know how many celebrities live in Alaska or Florida.  If you wanted to see this from left to right, then you can move the State field to the Columns area.  This view means you’d scroll from left to right, whereas before you’d scroll up and down.  This action is the pivot part of a Pivot Table, because you just pivoted or moved around the way you see the data.  And that’s the magic of Pivot Tables. 

That was a basic explanation of a Pivot Table and how to create one in Excel.  If you’re still awake, thanks for getting this far. If you’re asleep, pleasant dreams.