Microsoft Excel: Getting Started With Pivot Tables

Interested in using pivot tables to analyze your data and make understanding your organization’s statistics easier? Pivot tables are a great way to get the most out of Microsoft Excel, especially when using it to decipher data. In this video, we discuss what a pivot table is and how you can use them to improve your business.

Pivot tables are a way for companies to analyze large amounts of data in Microsoft Excel. With them, you can examine differences, similarities, highs, and lows in datasets. The data used to create the pivot table is called the “source data”. Pivot tables have four different areas: row labels, values, column labels, and the report filter. Each column in the pivot table represents a different category of information.

How To Prep Your Data

Data must be prepared before using it in a pivot table. First, it must be organized into rows and columns, with no blank areas except for cells. Similar data must be in the same column as well. Column headings need to be formatted differently than your data so that the system is able to tell the difference between the two. One way to do this is to bold or center the data. You can also separate data from unnecessary information by creating a data island.

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. You can also customize which values are shown by hovering over a cell, right-clicking, selecting “number format”, choosing the category, and making the changes. You can also change how data is represented in the same area.

More Helpful Tips

Two tabs will be shown in the ribbon when a pivot table is open: “pivot table analyze” and “design”. Here 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 find your dataset changing often and need to update your range, go to “change data source” and change the range. Specific rows can also be extracted by double-clicking on a value. When you do this a new pivot table will be created.

When you have multiple values in one pivot table, Excel will create groups. Next to each group, the “-” option will be shown. This collapses the details of the group so that you don’t have to see them. This can also be done by pressing “collapse field” in the ribbon.

To change the aesthetic (color, look, etc.) 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 Much Will Using Pivot Tables Help You?

Working with large amounts of data becomes so much easier when using pivot tables. Using this feature in Excel will keep your business more organized, saving you time and effort. To learn more about pivot tables or Microsoft Excel in general, contact us online or by phone. National Networks is prepared to help you with all your IT solutions, including working with pivot tables and other ways to analyze your data.