Pivot tables help us in storing large amounts of data in the form of meaningful reports. A pivot table helps companies and other people in general to manage their big data. Thus, they help in organizing data and make it easy to use and analyze. Pivot tables can create data summaries in different formats. Another reason for their popularity is their dynamic nature and the kind of flexibility they provide, even with large data. For example, there are so many companies that have to manage their big data and keep them in an organized way, but it takes a lot of time in doing so manually. Therefore, in order to save time and keep big data in an organized way, companies use pivot tables in MS Excel.
Here we will be discussing everything that you should know about pivot tables and their operation in order to help you work smarter.
How to create Pivot tables in MS Excel
Let’s use an example to see how you can use pivot tables in MS Excel to analyze your data more effectively. Following is the report of the total units sold by the states in a particular month:
- Click anywhere on the table and press CTRL+A then your entire table will get highlighted. Once your entire data gets highlighted there will be one particular box that will not get highlighted due to some reason.
- Then you can press CTRL+Q to open up this box. As soon as you press CTRL+ Q, a couple of different options will open up and then you can choose a specific pivot table that is best for analyzing your data. So when you will click on that pivot table then a new worksheet will open up for you and your pivot table will get created.
- Now if you want to see that how much each state has earned each month in this pivot table or how much each month for all the states units were sold you must do the following: In the right-hand side of the sheet we will have our months that are currently unchecked. So, if we check the months, it will create different pivot tables for us, where it will have the bold labels as the states and inside we will have the months and it will tell us how much units were sold for each month for that particular state.
- Now the data does look a bit bigger but it does look a little bit harder to read. To make this easier to read we can take the months out of the row section in the bottom right corner and drag it over to the column section.
- So now, you can see how many units were sold in the month of March for all the states. It will also show us the number of units that were sold for an entire state.
In this way you can create pivot tables in MS Excel.
Uses of pivot tables in MS Excel
Pivot tables have a lot of uses and a few of them are as follows:
- The very first use of pivot table is that it helps you in summarizing data in different formats.
- It is extremely easy to use and dynamic, so it will automatically change when the data gets changed.
- You can choose a specific pivot table for your data and then you can use that pivot table for analyzing your data in a much easier way.
- It basically does not change the data but it just summarizes the big data so that one can view it easily.
- Due to its speed and output, it is the most powerful tool in MS Excel. It helps you to analyze thousands of rows of data with just a few mouse clicks.
- If you have made any changes in the pivot table then you just need to refresh it and the data will get saved automatically.
- When a pivot table is created in Ms Excel that has multiple fields in the row label then MS Excel automatically adds a subtotal at the top and you can even change it which means that if you want to show the subtotal at the bottom then you can do that as well.
- There are three different types of pivot tables that you can choose from-Compact, Outline and Tabular form.
- If you want to format the values of your pivot table then you can do it by using number formatting. So, it allows you to do formatting in an easy way.
- You can remove blank cells from your pivot table by formatting the empty cells.
- Pivot tables help you in doing calculations very easily and there is an option called: ‘The Percent of GrandTotal’ calculation that helps you in calculating percentages immediately from a table filled with numbers. Similarly, the ‘Percent of Row Total’ calculation helps you in calculating the percentages immediately from a table filled with numbers.
A few interesting things that you can do with Pivot Tables in MS Excel
- While working with pivot tables, sometimes you might face problems while refreshing the tables. You might have aligned column widths according to your own needs and as soon as you refresh, they might go back to the original width. Here, some pivot table features will be of good use to you.
Just Right Click in the Pivot Table and select ‘PivotTableOptions’ and then see the ‘Layout & Format’ tab and under it, just “uncheck” the checkbox which says: ‘Autofit column widths on update.’ This process will help you maintain the column widths at what you changed them to.
- You can summarize a lot of data in a lot of ways using the ‘Summarize Values By’ option. With this option, you can apply a variety of functions to your data. A few of such functions are: sum, average, maximum, minimum, variation, count numbers and many more.
- Using pivot tables, you can easily sort your data in your desired format. You can apply sorts to any segment of the data or the entire data. You simply need to go to the ‘Sort’ option and then select the type of sorting you want to do. The flexibilities that pivot tables provide with their sort feature is something which makes them stand out.
- For making your excel tables or workbooks presentable or interactive, you can add some color to them and make them less boring. This can be done using various ‘Slicer Styles’ by simply going on the option ‘Excel Slicer’. Just follow this: Slicer Tools > Options > Slicer Styles and select the style that you want to use.
The pivot tables in MS Excel are extremely powerful and they have lots of advantages. Therefore, one should start using it as soon as possible without thinking about the data size. No matter how big the data is, pivot tables will easily summarize your data and present it in a presentable way. Pivot table also contains many tools that help in doing fast calculations. Learning how to use a pivot table is simple and once you know its basics, you can do a lot of experimenting with them in order to work in a better way. With more such experimenting you will be able to find out its numerous hidden features too. Thus, go ahead and learn everything about pivot tables of MS Excel, which will make your work simpler and reduce your efforts. All the best!