Looking to move your Excel reports from impressive to brilliant? Follow these quick excel tips that separate the experts from the rookies.
Yesterday's article focused on why pivot tables are so important and this can be found here. This article will give you five very quick tips you can execute to start using Pivot Tables in Microsoft Excel today.
Step 1: Organize your Data
Organizing your data is very important when creating pivot tables. Simply put, the more column headers you have in the raw data, the more options you have to filter and collapse the information. I always try to incorporate as many flags as possible in the raw data because it allows me to mould the information as it becomes collapsed.
It is always better to have a taller data set than a wider dataset when working with Pivot Tables. Consider monthy sales data where you have column for each month (ie 1 sales row for all months) rather than a row for each month (ie 12 sales records for all months). A column for each month has one row with a column header for each month making it difficult to filter or reorganize that data. However, if you have a column header titled "month" with Jan-Dec below it, the data becomes much more easier to work with once in a Pivot Table.
Remember - every column needs a title in a pivot table so make sure you don't have any blanks.
Step 2: Load Data into Pivot Table
Once the data is organized simply click:
- "Insert" tab in excel 2007
- Select Pivot Table
- Select your data range
- Choose Ok
- Pivot Table is now live on a new tab
The next steps will describe how to manipulate the information once loaded into a pivot table.
Step3: Drag and Drop Headers
With the Pivot Table now live you will now see 5 boxes in the "Pivot Table Field List" in the right hand side of the screen.
- Top Section - are all of the column headers created in your raw data
- Report Filter - This area allows you to include/exclude certain things (ie - Region)
- Column Labels - This puts your data across the top (ie - month)
- Row Labels - This will put your information in a row (ie net sales, margin etc)
- Values - This field will be aggregated based on what level of detail is put in the other fields (Sum, average, max or min etc)
By simply dragging and dropping each of your column headers into the four quadrants you can begin to shape and re-arrange your data.
Step 4: Set the Aggregate Function
Once you move a column header into the "Values" quadrant in the field list you can double click that header and choose how you would like to aggregate the information.
Note that depending on the level of detail you are showing in the other fields, you will get different results. Ie) if I am aggregating the regions within a district, my summation will be different than if I am aggregating regions within a country.
Just be mindful of the records included in the values option.
Step 5: Seek Conclusions
Continue to rearrange the data until you can begin seeing conclusions in the data. Often, I start with a top line view to see something like sales by country. Then if I see a certain country performing better than others, I'll dive deeper into that country to see what regions or districts are accounting for the most sales.
Any variable you add into the mix will tell you something new about the data so adding a "new store" flag will help you see if the sales growth is coming from new vs. existings stores for example.
Hope this helps, Pivot Tables are visual and difficult to explain but with a lot of practice you'll soon become a pro.
Thanks, talk to you tomorrow