pivot table
3 ways to create a Pivot Table
There are 3 ways to create a Pivot Table and most users only know the first way. If you are new to Pivot Table or wonder what you need to learn Pivot Table, read on. If you are already creating Pivot Table and do not need to be convinced of why you need to know Pivot Table, just to the section on the 3 ways to create a Pivot Table
Why should you create a Pivot Table?
Pivot Table auto-calculates
Pivot Table auto-calculates the total for you. You don’t have to pull out your calculator and start punching all the numbers. All you have to do is to put the right column of numbers and text into the Pivot Table and the total is straight away presented to you. The beauty of that is it will not punch the wrong numbers like you. There is no human error in the total.
Pivot Table updates changes
Another benefit of using the Pivot Table is that it updates the total when the numbers in the source change. There is no need for you to re-calculate the number with your calculator. You save on the time and effort to perform the repetitive task find the total.
When should you use a Pivot Table?
When you have more rows than you can count with your fingers plus another colleague’s fingers. That number should be 20. When that happens, you would find that Pivot Table can calculate more efficiently than you. The beauty of a Pivot Table is that you can see the numbers based on a number of dimensions with just a few clicks.
In fact, the more rows you have, the more you are likely to benefit from Pivot Table.
Who should Pivot Table?
I have used Pivot Table in many situations and it has worked very well for me. If somebody gives me more than 20 rows of data, (the number is usually 10 times more), I would use a Pivot Table. Sales, HR, Marketers, Supply Chain, Finance are business units that produce lots of data. All of them should learn how to create a Pivot Table, at least the simple ones.
I have also used a Pivot Table to auto-calculate utility consumption of many households so that I could organize the households into different groups.
The 3 ways to create a Pivot Table
The first way
The first way to create a Pivot Table is to use a range. Those who know Pivot Table are likely to create their Pivot Table using this way. It works well but if when there is an increase in rows, they would have to re-range the data source. Most users tried to range up the entire column, including the blank rows below the data range, out of convenience. They can still get their updates if the number of rows increases but they forego some useful Pivot Table functions in the process.
The second way
The second way is to use a Table. Most of you must be thinking that their sources are already in a Table since it is organized by columns and rows with headers. The truth is, Excel has a new function called Table. Table will display your data with colours on alternate rows. It is easier to read your data in Table. Table is a great way to organize your data. When you add new rows below the last row in the Table, the new rows are automatically included into the Table. Same for columns. Pivot Table create using Table as a source will not have to worry whether their data gets added into the Pivot Table. They are automatically added. All you have to do is to refresh the Pivot Table.
The third way
The third way is to use an external Excel file. This is the best way as it reduces the risk of your data getting changed accidentally. All you have to do is to refresh your Pivot Table and the numbers are automatically updated. One advantage of using an external Excel file is that you do not have to include all the columns in the external Excel file into the Pivot Table. You can pick and choose which column you want to show up in your Pivot Table. In this way, confidential data are protected. And your Pivot Table is not overwhelmed with fields that you do not need. One extra advantage is that the third way paves the way for you to create your next Pivot Table from other external sources.
If you want to learn how to create Pivot Table using the different methods or using other external sources, drop us a message in our contact us page. We have courses to cater to your different needs.