Microsoft Excel: Getting Started With Pivot Tables

Having trouble organizing data for your company? Pivot tables are a great way to analyze statistics for your business, without dealing with complicated, hard to use programs. In this video, we discuss how to get started using pivot tables, and how they can help you.

YouTube video

Understanding Pivot Tables

In Microsoft Excel, pivot tables allow users to organize and analyze large amounts of data, making it easier to understand. You can examine differences, similarities, highs, and lows in datasets with these tables. The data a table is based on is referred to as “source data”. A pivot table has four different areas: row labels, values, column labels, and the report filter. Each column represents a different category of data in the pivot table.

How To Prep Your Data

When creating a pivot table, you should first begin by preparing your data. The data should be organized into rows and columns, with no blank areas except for cells. Then put similar data in the same columns, so that it is grouped together. Format column headings differently than your data so that the system can tell which is which. Bolding or centering the column heading is a good way to do this. Finally, 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 build your pivot table further, go to “pivot table fields” and choose the fields to include. To pick the values that are shown, hover over a cell, right-click, select “number format”, choose the category, and make any changes you need. Here you can also change how data is represented.

More Helpful Tips

When a pivot table is open, two tabs will be shown in the ribbon (“pivot table analyze” and “design”). Here you can refresh the table, which is important because it keeps the data you are working with up to date. Be aware that refreshing does not happen automatically. To refresh, go to “pivot table analyze” and select “refresh” or use shortcut Alt + F5. If you are working with multiple tables, use “refresh all”.

If your dataset changes often and you need to update the range, go to “change data source” and change the range. Specific rows can also be extracted by double-clicking on a value. This will create a new pivot table.

Groups are created when multiple values are in the pivot table. Next to each group, there will be the “-” option. This collapses and hides the details of the group. This can also be done by pressing “collapse field” in the ribbon.

In “design” you can change the way the pivot table looks. Here you can also change how totals are displayed.

To filter through a pivot table, use the row or column dropdown and select the categories you would like to see. Right-clicking on a value and choosing what to include also filters the pivot table. Values that weren’t included can still be used to filter.

How Will Using Pivot Tables Help You?

Are you ready to get started using pivot tables in Microsoft Excel? With this guide, you will be able to easily understand and analyze data from your business. If you have more questions about how to use pivot tables, reach out to us here at Kraft Technology Group. We have IT professionals who can assist you will all your Microsoft needs. Contact us by phone or online today.