How Florida Professionals Use Pivot Tables in Microsoft Excel

No matter how much data you have, unless you can analyze your information and glean a deeper meaning from your data, it’s just taking up space. How can you take large amounts of data and quickly present it in a way that gives you greater insight?

What makes Microsoft Excel so sophisticated as a data analysis tool is its intuitive features, including pivot tables. Every column of data in a Microsoft Excel spreadsheet can represent a dataset in a pivot table in a user-friendly automated workflow designed to create easy-to-read data tables.

YouTube video

How to Create a Pivot Table

Pivot tables are an extremely useful and popular tool within Microsoft Excel. Creating a pivot table is easy with a few simple steps:

  1. Select the cells that have the data you want to include in your pivot table, making sure there are no empty rows or columns
  2. In your menu toolbar, choose “Insert” and then “PivotTable”
  3. When the user widget prompts you to choose your data, opt for “Select table or range”
  4. Verify the cell range you want to be represented in “Table/Range”
  5. When prompted, you’ll need to decide if you want your pivot table to be in an existing worksheet or a new worksheet, and then choose the cell location for where the table will display within that worksheet
  6. After you click “OK”, your next step involves choosing fields for your pivot table

Tips to Prepare Your Data for Use In a Pivot Table

There are a few steps you should take to make sure your data is ready to be used in a pivot table:

  • Make sure your raw data is set up properly, in rows and columns without any empty rows or columns – but it’s okay to have empty cells.
  • Make sure you have a column heading for each column – this helps Microsoft Excel both recognize and label your data set. Bonus tip: Try making the column header entry bold.
  • Don’t mix dates and text in the same column

Why You’ll Love Using Pivot Tables

When you add a row to a data set that is defined for use in a pivot table, that row of data automatically gets added to a pivot table when you refresh your data. Plus, any new columns you add to a data set will be added to your pivot table fields list. One of the most popular features of pivot tables is the ability to filter, sort, group, or conditionally format portions of your data so you can focus on a smaller data set.

If you’re new to using pivot tables, there’s a great built-in feature for Microsoft Excel users called “Recommended PivotTable”. Microsoft Excel instinctively reviews your data set and determines a meaningful layout for your data. In step #2 above, instead of choosing “Insert” and then “PivotTable”, you’ll choose “Insert” and then “Recommended PivotTable” and experiment with the different ways Microsoft Excel recommends your data be presented.

You can also connect external data sources, like SQL Server tables, XML files, Microsoft Access databases, and more.

4it Tech Insights

We use cookies to gather information about the way you interact with our website, to create reports, and overall help us in improving the website. To learn more about our cookie policy, view our Privacy Policy. By clicking “Accept & Close”, you consent to the use of cookies unless you have disabled them.