Microsoft Excel: Getting Started With Pivot Tables

Using pivot tables in Microsoft Excel can help you become more organized when dealing with your organization’s data. Pivot tables make the analysis, storage, and comprehension of data much easier, allowing you to better understand your business. In this video, we discuss how to use pivot tables if you are not yet familiar with them.

Understanding Pivot Tables

Pivot tables allow you to easily work with data, helping you gather valuable information about your company. You can examine differences, similarities, highs, and lows in your datasets. Before you dive into using pivot tables, here are some things you should know first. The data the pivot table is based on is called “source data”. The four different areas of a pivot table are “row labels”, “values”, “column labels”, and the “report filter”. Lastly, each column in a pivot table represents a different category of data, making your statistics easier to read.

How To Prep Your Data

You should never use unprepared data in a pivot table. To prepare your data, begin by organizing it into columns and rows, with no blank areas except for cells. Similar data should be grouped in the same columns. To make sure the system can tell the difference between the two, format column headings differently than your data by bolding or centering the column heading. You should also separate unnecessary information from the data 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”. Continue building it by going to “pivot table fields” and choosing the fields to include. Hover over a cell, right-click, select “number format”, and choose a category to select which values are shown. In this area you can also change how data is represented.

More Helpful Tips

“Pivot table analyze” and “design” will be shown when a table is open. You are able to refresh your data under “pivot table analyze”. Refreshing is not automatic, so make sure to do it often, or else your information will not be up to date. To refresh, go to “pivot table analyze” and select “refresh” or use the shortcut Alt + F5. To refresh multiple tables, use “refresh all”. You can also update the range by going to “change data source”. To create a new table out of a specific value, double-click on the value you would like to use.

When multiple values are present in a pivot table, Excel will create groups. Next to each group, there will be the “-” option. Use this button to hide the details of the group from view. This can also be done by pressing “collapse field” in the ribbon. Furthermore, the appearance of the pivot table and how totals are displayed can be changed under “design”.

If you need to filter through a table, use the column or row dropdowns. Once you are in the dropdown, select the categories you would like to see, and all others will be hidden from sight. You can also filter by right-clicking on a value and choosing what to show. This will exclude all unselected values. Values that were not included in the table can still be used to filter as well.

How Will Using Pivot Tables Help You?

Pivot tables are a handy tool for anyone struggling to analyze large amounts of data for their organization. If you would like to learn more about pivot tables, or if you have questions about Microsoft Excel in general, please feel free to reach out to us. At Sysoft Computer Consultants we are always prepared to help you with any of your IT needs. Contact us online or by phone today.