Pivot tables<\/a> are without a doubt one of the most powerful tools available in Excel. They allow you to summarize and analyze large and complex data in a very simple way. However, if you\u2019ve never used them before they can be a bit intimidating.<\/p>\nIn this article, I\u2019ll show you just how easy it is to create and use a pivot table in Excel!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
What is a pivot table?<\/h3>\n
As I mentioned before, a pivot table is a feature in Excel that helps you summarize and analyze data.<\/p>\n
But what exactly is a pivot table? The easiest way to explain is to show you an example.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/1.-Pivot-table-dataset.png” alt=”pivot table in excel 1. dataset” title_text=”1. Pivot table dataset” align=”center” _builder_version=”4.21.2″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Here I have some data about employees at a department store, including their sales in 2019 and 2020, and their most recent bonus.<\/p>\n
Now imagine I\u2019m the manager of the department store and I want to know the amount of bonuses I\u2019ve paid for each department.<\/p>\n
By using a pivot table, I can quickly get that information.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/2.-PT-bonuses-by-dept.png” alt=”pivot table in excel 2. bonuses by department” title_text=”2. PT bonuses by dept” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Pretty cool, right? Pivot tables help summarize and analyze data in all kinds of ways.<\/p>\n
But before I get into those details, I\u2019ll show you how to get started with creating a pivot table in Excel.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to create a pivot table in Excel<\/h3>\n
Excel makes it really simple to insert a pivot table into your spreadsheet. The exact details will be a bit different depending on what version of Excel you\u2019re running, and on what system. But the overall process will be the same.<\/p>\n
Follow the easy steps below to create your pivot table in Excel.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
1. Organize your data<\/h4>\n
The first step is to set up your data. It should be in a table with headers, and it should be as clean as possible. Remove any blank rows, and make sure that each column contains the same type of data (for example, don\u2019t mix numbers and text).<\/p>\n
Once your data table is cleaned up, you\u2019re ready to create your pivot table.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
2. Insert a pivot table<\/h4>\n
To insert a pivot table, click on the Pivot Table<\/b> button. This will typically be under the Insert<\/b> tab, but can also be under the Data<\/b> tab, depending on your version of Excel.<\/p>\n
Once you\u2019ve located the Pivot Table button, click it to get the following screen.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/3.-PT-button.png” alt=”pivot table in excel 3. button” title_text=”3. PT button” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Within this Create PivotTable<\/b> screen, select the data that you want to analyze, and choose where the pivot table itself will go.<\/p>\n
In the Location section, select your entire dataset including the headers. Below that, choose where to insert the pivot table. For this example, I\u2019ve chosen to insert it on the same worksheet right beside my source data.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/4.-PT-create-table.png” alt=”pivot table in excel 4. create table” title_text=”4. PT create table” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
There\u2019s also an option to insert a Recommended Pivot Table<\/b>. If you select this option, Excel takes a look at your data and generates and populates a pivot table automatically.<\/p>\n
This is a good option for simple datasets, so feel free to give it a try!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
3. Build your pivot table<\/h4>\n
Press OK and you\u2019ll create an empty pivot table, along with the Pivot Table Builder screen (it can also be called the Pivot Table Fields screen depending on your version of Excel).<\/p>\n
This is the part where you choose what aspects of the source data you want to show in the pivot table.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/5.-PT-builder-empty.png” alt=”pivot table in excel 5. empty builder” title_text=”5. PT builder empty” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In this screen, you drag and drop the data fields into the section you want them. As you can see, you can designate fields as rows, columns, values, or filters.<\/p>\n
I\u2019ll show more of how this works later.<\/p>\n
Choosing what fields to add to the pivot table can be the most challenging part of the process. You have a lot of autonomy here to summarize the data in any way you see fit.<\/p>\n
Remember the first example, where I showed the bonuses by department? I\u2019ll build that again here so you can see the process.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/6.-PT-builder-bonuses-by-dept.png” alt=”pivot table in excel 6.” title_text=”6. PT builder bonuses by dept” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In order to build this pivot table, the first thing I did was drag the Department<\/b> field into the Row Labels<\/b> box. This makes a pivot table row for each unique value in the Department column of my source data (e.g. clothing, food, etc.).<\/p>\n
Next, I dragged the Bonus<\/b> field down to the Values<\/b> box, since I want to summarize the bonuses paid in each department.<\/p>\n
Notice that it says \u201cSum of Bonus\u201d in the Values section. Any time you generate a value, you need to tell Excel exactly what to show.<\/p>\n
In this case I chose Sum<\/b>, because I want the bonuses added up for each department.<\/p>\n
There\u2019s a whole bunch of options however: count, average, maximum, etc. To change the type, just select the field in the Value section and you\u2019ll see all the options.<\/p>\n
When you choose the fields to display, you can see the pivot table updating in real time. This allows you to play around and get the configuration you want.<\/p>\n
Once you\u2019re happy with your pivot table, just select any cell outside the table and the Pivot Table menu will disappear. Now you\u2019ve created your pivot table!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
How to use pivot tables<\/h3>\n
Now that you\u2019ve seen how to create a pivot table, I\u2019ll talk about some common uses for them, as well as some tips to help you get the exact table you want.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Different value types<\/h4>\n
As I mentioned before, pivot tables can crunch your data in a variety of ways. In the previous example I used Sum to add up the bonuses for each department.<\/p>\n
This time, I\u2019ll use Average<\/b> to get the average sales within each department.<\/p>\n
Again, I start by dragging the fields to the proper sections. When I drag the 2020 Sales field into the Values section, it defaults to Sum. To change this, I select the field and choose Average from the list of options.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/7.-PT-value-type-options.png” alt=”pivot table in excel 7.” title_text=”7. PT value type options” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Once I do that, the pivot table updates automatically and shows me the average sales in each department!<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/9.-PT-2-year-sales.png” alt=”pivot table in excel 8.” title_text=”9. PT 2-year sales” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Two-dimensional tables<\/h4>\n
You can use pivot tables to generate two-dimensional tables with multiple columns as well as rows.<\/p>\n
To expand on the previous example, what happens if I want to see the average sales in 2019 and 2020? I do that by adding both the 2019 and 2020 sales to the Values section.<\/p>\n
This generates two values columns: one for each field.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/9.-PT-2-year-sales-1.png” alt=”pivot table in excel 9. two-dimensional” title_text=”9. PT 2-year sales” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Note that you can even add another field directly to the Column Labels section to get an even more granular summary of your source data!<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Filtering<\/h4>\n
Another cool feature of pivot tables is the option to filter. This way, you can control which data is shown.<\/p>\n
To filter a field that is already included in a row or column, simply click on the dropdown icon:<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/10.-PT-filter-rows.png” alt=”pivot table in excel 10. filtering” title_text=”10. PT filter rows” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In this case, I choose which departments I want to include in the pivot table.<\/p>\n
You can also add filters for fields that are not in your pivot table as rows or columns.<\/p>\n
For example, suppose I want to remove Frank\u2019s sales numbers from the calculation of average sales.<\/p>\n
I do this by adding the Name<\/b> field to the Filter section in the Pivot Table Builder. This creates a filter option at the top of the pivot table.<\/p>\n
Then I simply click on the dropdown icon and deselect Frank.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/11.-PT-filter-names.png” alt=”pivot table in excel 11. filter names” title_text=”11. PT filter names” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Formatting pivot tables<\/h4>\n
The cells in pivot tables can be formatted just like regular cells. You can change the number type (to a currency, date, etc.), decimal level, font, color, and so much more.<\/p>\n
You can even rename the headers to replace the auto-generated ones.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Refreshing data<\/h4>\n
If the source data changes, you can refresh a pivot table to make sure it\u2019s including the most up-to-date information.<\/p>\n
Right-click on any cell of the pivot table and press Refresh Data<\/b> to do so.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/12.-PT-refresh-data.png” alt=”pivot table in excel 12. refresh data” title_text=”12. PT refresh data” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Percent of total<\/h4>\n
Pivot tables allow you to change the way values are displayed.<\/p>\n
For instance, I could choose to view the bonuses by department as a percentage of the total bonuses paid.<\/p>\n
To do this, right-click on the data and choose Field Settings<\/b>. From this menu, you then choose how to display the data.<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/13.-Field-settings-percent-of-total.png” alt=”pivot table in excel 13.” title_text=”13. Field settings percent of total” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
(Depending on your version of Excel, you may be able to do this directly from the right-click without going into Field Settings).<\/p>\n
[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/14.-PT-percent-of-total.png” alt=”pivot table in excel 14″ title_text=”14. PT percent of total” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”100%” width_tablet=”100%” width_phone=”100%” width_last_edited=”on|phone” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Final thoughts<\/h3>\n
Pivot tables are a fantastic way to summarize and analyze data in Excel. Once you learn how to make them, they\u2019re quick and easy to work with, and extremely powerful!<\/p>\n
If you want to learn more and maximize your Excel skills, take a look at our Excel functions<\/a> blog posts.<\/p>\n[\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"
Pivot tables are without a doubt one of the most powerful tools available in Excel. They allow you to summarize and analyze large and complex data in a very simple way. However, if you\u2019ve never used them before they can be a bit intimidating. In this article, I\u2019ll show you just how easy it is […]<\/p>\n","protected":false},"author":40,"featured_media":32623,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[51],"tags":[39,48,28],"class_list":["post-32622","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-features","tag-connections-t","tag-excel","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/32622","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=32622"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/32622\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/32623"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=32622"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=32622"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=32622"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}