What Is a Pivot Table and How to Create One?

Why use a pivot table? They’re simple, versatile, and powerful. With a few clicks, you can uncover patterns, summarize information, and make decisions like a pro.

Generally speaking, pivot tables excel (pun intended) at:

• Presenting large amounts of data.

• Summarizing data.

• Organizing data by categories and subcategories.

• Filtering information.

• Presenting a concise and attractive outline of your data.

Can’t wait to know more? Let’s get lost in the forest of numbers and data analysis!

What is a pivot table?

A pivot table is a way to present data in an organized fashion. It usually comes packaged in a chart that can help you notice trends in your data. This can be invaluable for long rows and columns which hold values you need to track and compare.

On top of all that a pivot table will also allow you to interact with the dataset, compare metrics, underline outliers, and much more. Generally speaking, a pivot table is a visual storytelling helper for large amounts of data.

The majority of the spreadsheet software out there comes equipped with pivot table creation capabilities. Additionally, some business intelligence platforms also offer you the freedom to create easy-to-understand pivot tables.

Why would you want to use a pivot table?

How is a pivot table different from a regular table? We’re glad you asked!

In short, a pivot table can help you summarize large amounts of data in a way that is easy to understand and analyze. It can also organize an entire numbers sheet into a compact readable table. Essentially, this lets you get some useful insights out of your data.

A pivot table is simple

Arguably the best thing about pivot tables is their simplicity as they can make your data management a breeze. With the proper column labels and a good row label or two, a sheet of thousands of lines of data will be easy to understand by your whole team.

Pivot tables help you recognize patterns

With a quick snapshot of your data, you will have an easier time spotting patterns. This could potentially help you make accurate data predictions and do so at twice the speed.

It’s great for data analysis and summary

As already mentioned, pivot tables can easily handle enormous amounts of data and simplify it. The resulting data summary can then be more easily understood, enabling you to derive actionable insights much quicker.

How can a pivot table improve your workflow?

Some of the specific use cases you can use a pivot table for are:

• Comparing the total sales of different products – a pretty common use for a pivot table is when managing a store or a product-based business. If you have collected your data for the previous quarter, you can easily find out which product generated the most revenue.
  
  The most straightforward approach here would be to manually go through your spreadsheet and add the corresponding sales numbers every time a certain product appears. Sounds simple, right? Well, it may become a whole lot more complicated if you are running an online store with thousands of products as this could easily take you weeks.
  
  Luckily, a pivot table can automate the whole process. You can add the sales numbers for each product and calculate their respective sums in just a few clicks.

• Calculating percentage of sales for a specific product – this is another task you can perform manually but, again, it will take you forever to complete if you do so.
  
  However, a pivot table can give you the total values at the bottom of each column. It lets you easily calculate the sales percentage for each product as part of your total sales. You will simply need to right-click the cell with the total amount of sales, then select “Show Values As” → “% of Grand Total”.

• Adding default values to empty cells – sometimes, when you enter a dataset into Excel, it will not populate every cell. This is when a pivot table can make your life much easier.
  
  You can easily customize your pivot table to fill empty cells with a default value like N/A or TBD. In the long run, this can save you days of work, especially if you are working with lots of products.
  
  To do that, right-click your table and then select “PivotTable Options”. In the newly appeared window, you check the box labeled “For Empty Cells Show” and then enter the value you want displayed when an empty cell is encountered.

• Tracking the average transaction value across different customer segments – to track the average transaction value across different customer segments using a pivot table you should:
  
  1. Start by organizing your dataset with columns for customer ID, transaction amount, and customer segment.
  2. Create a pivot table by selecting your dataset and using the pivot table function in your spreadsheet software.
  3. In the pivot table, place the customer segments in the rows area and the transaction amounts in the values area.
  4. Use the “average” function to calculate the average transaction value for each segment. This setup will display the average transaction value for each customer segment, allowing easy comparison and analysis.
  
  For more granular insights, you can also add additional layers of segmentation, such as geographic location or time period. Pivot tables can also be updated with new data, ensuring your analysis remains current as your business evolves. This approach will provide you with a clear and more efficient way of analyzing and understanding the spending behavior of different customer segments.

• Analyzing your marketing campaigns in terms of lead generation, ROI and much more – to analyze your marketing campaigns in terms of lead generation, ROI, and other metrics using a pivot table, start by organizing your dataset with columns for campaign name, leads generated, marketing spend, revenue generated, and any other relevant metrics.
  
  Next, create a pivot table by selecting your dataset and using the pivot table function in your spreadsheet software, such as Microsoft Excel or Google Sheets. In the pivot table, place the campaign names in the rows area and your metrics (leads generated, marketing spend, revenue) in the values area.
  For more detailed analysis, you can add additional dimensions such as time period or geographic location to the pivot table. This allows you to see how campaigns performed over time or in different regions.
  
  Pivot tables can be easily updated with new data, ensuring your analysis remains current as your marketing efforts evolve. This approach provides a clear and efficient way to evaluate the effectiveness of your marketing campaigns and make data-driven decisions.

How do you create a pivot table?

As you have seen so far, working with pivot tables is a straightforward experience that only requires a basic understanding of Excel or a simpler spreadsheet program for that matter (feel free to download MobiSheets and try it for free). To get the most out of your pivot tables, however, there are a few things you will want to make sure are in order first.

• Organize your source data. Before you start you should prepare all the data you need and organize it accordingly. We are talking about correct fields, correct numbers, and of course, no empty cells.

• Create your pivot table. Once all your data is in order, you can proceed to create your pivot table. On Excel, this is usually done through “Insert” → “Pivot Table”.
  However, this process may differ slightly, depending on the specific software you are using. We will get into more detail about each of the most widely used spreadsheet software later on in the post.

• Arrange the layout. In some cases, a plain pivot table might not suffice to get the most out of your data. You may want to move some columns around and change their titles. At times you might also need additional borders so your table is easier to understand.

• Add extra fields. Just like with customizing your layout, you might need to add a few extra fields for better data analysis. This can be done by right-clicking the row where you want to add more fields. Then, select “Add Row” or “Add Column”.

• Remove the fields you do not need. If you want to do the opposite of what we previously discussed you will want to select the field you want removed, then right-click and click “Delete”.

Creating a pivot table in MobiSheets

Creating a pivot table in MobiSheets is a piece of cake. Sheets is one of the main pieces of software in the package so the team behind it made sure it is very intuitive and easy to organize your data, see patterns and trends, and compare the information you have.

Before you start, though, there are a few things you should do. First and foremost you will want to assemble your source data as a list of rows and columns under specific headings, describing their content. Then you should add a heading for every column and make sure there are no empty rows. Check our Pivot Table support page for a more detailed explanation.

Now, let’s get on with the pivot table business:

1. Select “Insert” from the ribbon at the top
   

2. Click on “PivotTable” in the dropdown menu.

3. An “nsert Pivot Table” window will pop up. There, you will see two categories to customize your calculation range:
   

   – “Table/Range” – MobiSheets automatically detects the source data and adds all the available information to your pivot table. If you need a part of the data, click on the grid icon and select the data range you want to include in your pivot table.
   – “Destination” – Lets you define where your table will go. Here you can choose between two options:
     – “New Worksheet” – This option lets you place your pivot table into a new worksheet. This worksheet will then go on the left of your existing worksheet. All of your source data headers will be listed as fields in Choose the fields you want to add to the report section in the “Pivot Table Options” panel.
     – “Existing Worksheet” – Lets you add your new table to an existing source data worksheet.

4. Click “OK” to confirm your choice.

5. Personalize your table in the “Pivot Table Options” menu.
   

Creating a pivot table in excel

Just like in Sheets, in Excel you will want to organize your data into rows and columns before you start. You should also convert your data range into an Excel table. Here is how you can do that:

1. Go to the “Insert” in the ribbon on top of your screen.
   

2. Click on “PivotTable”.

3. Select your prefered data source. If you already have the data in the selected Excel sheet, pick “From Table/Range”.
   

4. You will see the PivotTable from table or range menu.
   

5. Choose the exact range you desire for your pivot table

6. Pick the location where you want your pivot table inserted. This can be a New Worksheet or an Existing Worksheet. 

7. Click OK

8. Once your pivot table is created you will see a new menu appear on the right of your screen. There you can personalize your table.
   

   If you have not made any changes, Microsoft Excel will add the fields to the Layout section as follows:

   • Row Labels – non-numeric fields

   • Values area – numeric fields

   • Column Labels section – Online Analytical Processing date and time hierarchies.

   Removing fields from your table’s layout is done by unchecking the box next to the field’s name in the “Field” section of the “PivotTable” panel. Then, right-click the field and select “remove Field_Name”.

   If you would prefer to rearrange existing fields instead of adding or deleting fields, you should:

   • Drag and drop fields between the 4 areas of your “Layout” section. This can be done by clicking and holding the field name in the “Field” section and then dragging it to your preferred area in the “Layout” section. BE WARNED, however, that this will remove the field from the current area in the “Layout” section and place it in the new area.

   • Right-click the field name in the “Field” section and then select the area where you want to add the field.

   • Click on the field in the Layout section. This will display the options available for that particular field.

Creating a pivot table in Google Sheets

Creating a pivot table in Google Sheets is even simpler than in Excel. It just takes a few clicks as the user interface of Google Sheets is much more intuitive. Here are the exact steps to creating your first pivot table in Google Sheets:

1. Open a spreadsheet in Google Sheets.

2. Select the cells you want to include in your pivot table. Make sure every column you select has a header.

3. Select “Insert” from the menu at the top.
   

4. The Create pivot table menu will appear. Select your desired range.
   

5. Click on “Pivot table”. Select the pivot table sheet if it is not already open.

6. In the side panel, next “Rows” and “Columns”, click “Add” and then choose a value.
   

7. In some cases Google Sheets will recommend pivot tables based on the data you choose. To add a pivot table this way, choose a pivot table under “Suggested”.

8. Click on “Add” next to “Values” in the side panel.

9. Choose the value you want to see over your rows or columns.

10. Summarize and filter your data to your liking by clicking the down arrow.

Frequently Asked Questions

How do you delete a pivot table?

While pivot tables can help make your life a lot easier, sometimes you might want to delete one. In MobiSheets, this is easily done by:

1. Clicking anywhere within your table.

2. Pressing the Delete key.

3. Deleting the pivot table and the underlying data.

Our team put together an elaborate piece on how to delete a pivot table. We highly suggest referencing this article if you’re looking for more ways to delete a pivot table you no longer need.

How to move a pivot table to a new location?

You can easily move your pivot table around your spreadsheet. Here is how to do it:

1. Select the pivot table.

2. Under “PivotTable tools” on the “Analyze” tab, click “Move PivotTable”.

3. Choose where you want your table to be placed in the newly displayed “PivotTable” dialog box. This can either be a “New worksheet” or an “Existing worksheet”.

4. As an alternative, you can click on “Collapse Dialog” to temporarily hide the dialog box from view. Then, select the starting cell on your worksheet and choose “Expand Dialog”.

What is the difference between a spreadsheet and a pivot table?

A spreadsheet is a digital document that organizes data into rows and columns, enabling users to perform calculations, create charts, and analyze information. It is used for tasks like budgeting, inventory management, and data analysis, with software such as Microsoft Excel and Google Sheets providing various features for data manipulation and visualization.

A pivot table, a feature within spreadsheet software, allows users to summarize, analyze, and explore large datasets interactively. It enables dynamic rearrangement, sorting, filtering, and grouping of data to uncover trends and insights. Unlike static spreadsheet data, pivot tables offer multi-dimensional analysis by letting users drag and drop fields to create different views and aggregations.

Why is it called a pivot table?

The term “pivot table” comes from the ability to “pivot” or rotate data in the table, allowing users to rearrange and summarize the data in various ways. This pivoting action lets users view different perspectives and dimensions of the data by dragging and dropping fields to different positions, thus changing the data’s orientation and organization.

The name reflects the interactive nature of the feature, as it enables users to dynamically adjust their data analysis without altering the original dataset. By pivoting the data, users can quickly switch between different summary views, making it easier to identify patterns, trends, and insights.

Conclusion

And there you have it –  our deep dive into the magical world of pivot tables! From simplifying complex data to uncovering hidden patterns, these versatile tools can transform your data management game in no time, so it’s valuable to know how to use them.

Whether you’re using Excel, MobiSheets, or even Google Sheets, creating and mastering pivot tables is bound to make your workflow smoother and your decision-making sharper. So go ahead, unleash the power of pivot tables, and turn that data jungle into a well-organized paradise. Happy data wrangling and until next time!