Microsoft Excel
Getting Started With Pivot Tables

Pivot tables are a feature in Microsoft Excel that allows you to summarize large amounts of data. Using them, you can examine differences, similarities, highs, and lows in datasets.
Page Top

Microsoft Excel: Getting Started With Pivot Tables

Looking for ways to make analyzing data for your business easier? Pivot tables are a great way to do this, making information easy to work with and understand. Pivot tables are a must-use feature when doing any type of work in Microsoft Excel. In this video, we discuss how pivot tables can assist you when working with statistics.

Pivot tables are a feature in Microsoft Excel that allows you to summarize large amounts of data. Using them, you can examine differences, similarities, highs, and lows in datasets. The data used to create the pivot table is called the “source data”. A pivot table has four different areas: row labels, values, column labels, and the report filter. Each column in the table represents a different category of information.

How To Prep Your Data

Before using data in a pivot table, it must first be prepared. Start by organizing the data into rows and columns, with no blank areas except for cells. Put similar data in the same columns as well. Format column headings differently than your data so that the system is able to tell the difference between the two. Try bolding or centering the data to do this. Lastly, create a data island to separate unnecessary information from the data.

How To Create A Pivot Table

To create a pivot table, go to “insert”, either “recommended pivot tables” or “pivot table”, confirm the range you are using, hit “new worksheet” and then “ok”. To further build your pivot table, go to “pivot table fields” and choose the fields you wish to include. To customize which values are shown, hover over a cell, right-click, select “number format”, choose the category, and make any changes that are needed. You can also change how data is represented in the same area.

More Helpful Tips

When a pivot table is open, two tabs will be shown in the ribbon: “pivot table analyze” and “design”. In this area, you can refresh the table (which is not automatic) or do other helpful things. Please note that refreshing is important because it ensures that all the data you are working with is up to date. To do this, go to “pivot table analyze” and select “refresh” or use shortcut Alt + F5. If you are working with multiple tables, use “refresh all”.

If you have a dataset that changes often and your range needs to be updated, go to “change data source” and change the range. You can also extract specific rows by double-clicking on a value. This will create a new pivot table.

When multiple values are present in a pivot table, Excel will create groups. Next to each group, the “-” option will be shown. This collapses the details of the group, hiding them from sight. This can also be done by pressing “collapse field” in the ribbon.

To change the color or look of the pivot table, go to “design”. Here you can also change how totals are displayed.

To filter a pivot table, use the row or column dropdown (depending on which you would like to filter) and select the categories you wish to see. Right-clicking on a value and choosing how you would like for it to be sorted also filters the pivot table. Values that were not used in the table can also be used to filter the data.

How Will Using Pivot Tables Help You?

Analyzing data can be tricky, but with pivot tables, it becomes a lot easier. If you would like to learn more about pivot tables or Microsoft Excel in general, contact us online or by phone. At ChaceTech, we believe that technology should be easy to use. Reach out to our IT professionals if you would like assistance finding IT solutions to enhance your business.

Information Technology Aligned With Your Business Goals?
ChaceTech is a complete IT services & IT support company working with organizations in Houston and across Harris County.