Excel Pivot Tables - The Basics
Are you on data overload and need a way to translate your data in to information that makes sense? With pivot tables in moments you can see who sold the most products in the coastal region or which quarters were the most profitable for all regions or one sales person.
This past week one of my readers asked me about Pivot Tables. I don’t use them often so I thought we could learn them together. So, here goes the first in a series about Excel Pivot Tables.
Creating pivot tables is a very powerful feature in MS Excel. You can use them to summarize, analyze, and present your data. Now you can convert columns of data like salesperson, region and revenues into a table that shows you how sales of products are doing in each region without making multiple tables.
By moving, or pivoting, columns (or rows) of data from one location to another using drag and drop you will be able to look at your data in different ways.
Here are some example uses of pivot tables:
- To find the average sales for each region for each product from a product sales data table.
- Creating a pivot report with sub-totals and custom formats
- Without writing a macro or formula you can filter, sort, and drilling-down into the data in the reports.
- Transposing data – moving rows to columns or columns to rows.
- Linking data sources outside Excel to make pivot reports.
Here are some things to keep in mind when creating your worksheet to use for pivot tables:
- You need at least three columns of data.
- Enter your data correctly. If there are errors, caused by incorrect data entry, it will create incorrect information and maybe hard to find the source of the error.
- Do not leave blank rows or columns when entering the data. This includes the blank row some people would leave between the column headings and data.
- Each column of your data becomes a field that you can use in the report.
- Fields summarize multiple rows of information from the source data.
- The names of the fields are taken from the column titles in your data.
- Make sure that you have names for each column across the first row of the worksheet in the source data.
- Be sure your data is consistent within the columns. In other words if you are working with dates be sure that in each row for the date column you don’t have spans of days (i.e. 03/04/13 vs 03/14/13 – 03/20/14). Or that the spelling of your regions matches in all data entries.
In order to get a handle on tickets sold for one of my clients so here is a snap shot of the data table we will be using as we learn about pivot tables. Next week will begin to work on pivoting our data!
If you need help with an Excel project or have questions – contact us at email@example.com for assistance.
Out of the Box Thinking To Create a Buzz
This past Christmas an airline created a very “out of the box” campaign that went viral in no time and got them a ton of publicity, name recognition and goodwill.
It’s called the “West Jet Christmas Miracle”. They set up a video chat with Santa and asked people on one flight what they wanted for Christmas. What happens next I won’t tell you because you have to see it for yourself here – https://www.youtube.com/watch?v=zIEIvi2MuEk
The idea might have cost them a bit of money to pull it off – but the warm feeling and the over 35 million views on YouTube will pay them back in multitudes in the future. Now, this is not the first time West Jet has done a video to promote their airline but this one is the best yet! Look at the other videos posted by West Jet on You Tube.
If you need some ideas that are different contact me at firstname.lastname@example.org to help you move forward.
You Have To Laugh
Funny Video: Grandma dances to Ice, Ice, Baby - https://www.youtube.com/watch?v=MJusMpZu-eM