[et_pb_section fb_built=”1″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.27.4″ _module_preset=”default” hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n
Pivot tables are a powerful tool for extracting and analyzing the information you need from a large dataset. They\u2019re an easy way to \u201cslice and dice\u201d data in spreadsheets: in other words, to filter and then group data. For example, you might want to examine sales for a specific year (slice) and then break it down by product category (dice).<\/p>\n
The significant advantage of pivot tables is that they are dynamic, quicker, and more reliable than manually typing formulas.<\/p>\n
Pivot tables enable different users to customize the structure and design of tables, allowing them to view the specific data they need. Within the same organization, each stakeholder is interested in different aspects of operations. Even within departments, coworkers use slightly different KPIs or filters to achieve the same goal.<\/p>\n
If you\u2019ve used Excel, you\u2019re probably already familiar with Pivot tables, and Google Sheets has the same tool.<\/p>\n
[\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
In this example, I\u2019m using publically available data from Airbnb listings in Paris. This dataset has listed rooms and apartments in Paris, including indicators such as neighborhood, room type and price.<\/span><\/p>\n Note<\/b>: your columns must have a named header in the top row to use the data in the pivot table.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2019\/11\/Create-pivot-table.jpg” alt=”how to create a pivot table in google sheets – 2″ title_text=”Create pivot table” align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n I want my pivot table to show the average prices for each neighborhood in Paris. This is where Pivot tables are really useful as they allow you to analyze information quickly without having to.<\/span><\/p>\n \u00a0<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2019\/11\/Pivot-table-airbnb-avg-price.jpg” alt=”how to create a pivot table in google sheets – 1″ title_text=”Pivot table airbnb avg price” align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Lets extract more data. Suppose I want to know how many days of the year these rooms are available.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2019\/11\/Availability-Airbnb-Paris-pivot-table.jpg” alt=”how to create a pivot table in google sheets – 3″ title_text=”Availability Airbnb Paris pivot table” align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n That\u2019s it: these are the basic steps for creating a pivot table in Google Sheets.<\/span><\/p>\n In this example, I\u2019ve created a pivot table that shows the average price for a room in each neighborhood of Paris and the number of days per year these rooms are available.<\/span><\/p>\n From a huge data set containing more than 60,000 Airbnb listings, I can easily reach various insights:<\/span><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][et_pb_row _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You have already filtered your data by creating a pivot table, but you might want to break that information down into further categories. You can do that by adding an extra filter in the pivot table editor.<\/span><\/p>\n Go to Filter. Again, Google Sheets provides automatic suggestions based on your data.<\/span><\/p>\n If you want to add a different filter, click Add. Next to Showing all items, click the \u23f7dropdown menu.<\/span><\/p>\n You can:<\/span><\/p>\n Note: if you apply a filter and then update your source data, you need to update your pivot table filter.<\/span><\/p>\n Looking at the Airbnb data, let\u2019s say I want to analyze my data by room type. First I add a column for room type. But I only want to see results for: hotel room, private room and shared room. <\/span>Under Filter I select those rooms.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2019\/11\/Pivot-table-Airbnb-filtered-by-room-type.jpg” alt=”Pivot table Airbnb room types” title_text=”Pivot table Airbnb filtered by room type” align=”center” _builder_version=”4.24.1″ _module_preset=”default” width=”100%” custom_margin=”30px||30px||true|false” border_radii=”on|12px|12px|12px|12px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”customize” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If Google\u2019s automatic suggestions don\u2019t match what data you need, you can adjust the Rows, Columns, Values, and Filter manually.<\/span><\/p>\n Deselect Google\u2019s default suggestions by clicking <\/span>X<\/b>, then <\/span>Add<\/b>, and selecting which row or column you want to pull data from.<\/span><\/p>\n You can change the header names by clicking directly on a row or column.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the pivot table editor on the right-hand side, go to <\/span>Values <\/b>> <\/span>Add <\/b>> <\/span>Calculated field<\/b>.<\/span><\/p>\n Enter your formula, then go to <\/span>Summarize by<\/b>, and click <\/span>Custom<\/b>. A new column appears called \u201cCalculated field 1.\u201d<\/span><\/p>\n For help with writing formulas in Google Sheets, see Google\u2019s <\/span>full function list here<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You\u2019ve now got an overview of how to create a pivot table in Google Sheets. Pivot tables have many uses and they can get quite complicated, hopefully this post helped you get started.<\/span><\/p>\nStep 2: Select the data range of the pivot table<\/span><\/h3>\n
\n
Step 3: Insert the pivot table<\/span><\/h3>\n
\n
Step 4: Pivot table editor<\/span><\/h3>\n
\n
Step 5: Customize the pivot table<\/span><\/h3>\n
\n
\n
\n
\n
\n
\n
\n
How to edit a pivot table in Google Sheets<\/span><\/h2>\n
Add a filter to the pivot table<\/span><\/h3>\n
\n
How to customize rows and columns<\/span><\/h3>\n
Add a custom formula<\/span><\/h3>\n
That\u2019s how to do a pivot table in Google Sheets<\/span><\/h2>\n