This is the first in a series of blog posts about pivot tables in Excel.
A pivot table is a tool in Excel which allows you to move around your data so you can see it under different headings e.g it can take a list of sales and allow you to show you sales by country, sales person etc.
One of the key things I see in my training consistently is the amount of additional unnecessary work people create for themselves by not using a pivot table.
Let me give you a scenario. Your boss asks you to product a report that shows you the percentages of sales of products in a particular country. You slave away and eventually produce the report. He or she is delighted and then asks you do the same for the other countries. You go away groaning because you knew what effort it took for you to do it the first time….(By the way, this is something we will be covering in later blog posts 🙂 )
On the other hand, if your data is set up correctly and you use an excel pivot table…that’s the work of minutes…(and you can quickly create charts as well…again the work of minutes.
So what is a pivot table? The first thing you need to do is organise your data correctly. You know how in these cookery programmes the chefs just have to focus on creating wonderful dishes (all their ingredients and tools are “prepared earlier”), well one of the first things you should do before getting stuck into a pivot table is make sure your list is pivot-ready. See the guidelines below
- Excel assumes that your data will be organised as follows:
- First row will contain titles e.g. name, product number. For ease of use, format this differently to the rest of your data
- Like with like: the only thing that should be in First names should be (you guessed it) first names
- No gaps:No blank rows, no blank columns. It’s very likely you will have blank entries so it might be worth your while to enter something in that e.g. you could use Find to locate blank cells and Replace to enter 0 (zero) or something similar into it.
- In Excel 2007 and later, a clever first step is to convert the data into a table. You do this by clicking in the list. Click on Insert. Click on Table. This converts your list into a table. I will cover tables in another tutorial. The main advantage of doing this is that when you add extra rows/columns the pivot table easily adjusts to reflect this (which doesn’t happen so easily if you do not have it formatted as a table.
So that’s the first step. In the next tutorial, I will be covering how to create your basic pivot table.
Here is an example of an optimally formatted list.
Example of correctly formatted Excel list suitable for pivot table use
Here is an example of a list that is NOT optimally formatted.
Excel Products for Sale
Here are products from some of my favourite Excel authors and bloggers. Please note these are affiliate links so I do earn a small commission from every sale.
MrExcel.com
Mr. Excel (aka Bill Jelen) is one of the Excel gurus. I love his stuff. He also has a slightly bonkers delivery (What! Excel! Fun!) that I thoroughly enjoy. And of course anyone who calls his Excel publishing book company Holy Macro! gets my vote.
MyExcelOnline.com
Xtreme Pivot Tables course
This is a very comprehensive course on pivot tables. It includes videos and workbooks. There are over 200 hours of videos on it and it takes you from having no knowledge of pivot tables to being a power user. If you know everything in this course, you know an awful lot about pivot tables. John explains the concepts in bite size chunks so they are easy to follow and even provides a tutorial spreadsheet so you can track your progress. This would be money well spent. If you would like to become the go-to person in your organisation for pivot tables, this would be an excellent starting point.
He also has a useful Pivot Table webinar…
This is also a useful chart helper.
Debra Dalgleish – Contextures.com
Master 30 Excel functions in 30 days –This is a rather brilliant e-book which covers 30 functions including Vlookup,Match, offset. It includes an Excel file, e-book and links to videos that shows you how to do it – all for the princely sum of $10. What I particularly like about it are her descriptions, explanations and what the limitation of each function is. Highly recommended. If you want to upgrade your Excel skills this is a very worthwhile investment.
Excel UserForms for Data Entry
The Excel UserForms for Data Entry ebook kit will take you through the steps to build a UserForm that stores data on a hidden worksheet. No programming skills are required — everything is explained in simple steps, with written instructions, screen shots, videos and workbooks.
PivotPowerPremiumRibbon
The PivotPower Premium add-in saves you time and effort, when working with Excel pivot tables. For example, you can quickly:
-save and apply default pivot table settings
-clear old items from an Excel pivot table
-change all the data fields from the Count function to the Sum function
Chandoo.org
Click here to view more details about Excel Dashboard Video Tutorial
Click here to view more details on Excel Dashboard Templates
Click here to view more details on Excel Dashboard Templates Premium
Click here to view more details on Excel Formula Helper E-Book
Click here to view more details on Excel Formula Crash Course – View Option
Click here to view more details on Excel Formula Crash Course – Download Option
Click here to view more details on Excel School, Online VBA Classes & Dashboard Templates
Click here to view more details on Advanced Excel & Power Pivot Training Classes
Click here to view more details on Excel School & Online VBA Training Classes
Click here to view more details on Excel School & Dashboard Templates
Click here to view more details on Excel School – View & Download
Click here to view more details on Excel School – View Only
Click here to view more details on Excel Project & Portfolio Management
Click here to view more details on Advanced Power Pivot Class
Click here to view more details on Project Management Templates 2003
Click here to view more details on Project Management Templates 2007
Click here to view more details on Project Management Templates Both
Click here to view more details on Project Portfolio Templates
Click here to view more details on the VLOOKUP Combo Book
Click here to view more details on the VLOOKUP eBook
Click here to view more details on Online VBA Training Classes – View & Download
Click here to view more details on Online VBA Training Classes – View Only