Microsoft Excel: Getting Started With Pivot Tables

Analyzing data can be difficult, but with Microsoft Excel, it can be so much easier. Pivot tables, a feature in Excel, are a convenient way to summarize, analyze, and organize data for your use. In this video, we discuss how to get started using pivot tables, and how they can improve your experience when using Excel.

Pivot tables take large amounts of data and analyze it by organizing it into a table. With pivot tables, you can examine differences, similarities, highs, and lows in datasets. “Source data” is what users call the data the table is based on. A pivot table has four different areas: row labels, values, column labels, and the report filter. Each column of the table represents a different category of information.

How To Prep Your Data

Before creating a pivot table, you must first prepare your data. You should start by organizing the data into rows and columns, with no blank areas except for cells. Then put similar data in the same columns. After this, 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 select which values are shown, hover over a cell, right-click, select “number format”, choose the category, and make any changes you want. In the same area, you can also change how data is represented.

More Helpful Tips

When in an open pivot table, two tabs will be shown in the ribbon: “pivot table analyze” and “design”. Here you can refresh the table (which is not automatic). Refreshing is important because it keeps the data you are working with 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 are working with a dataset that changes often and your range needs to be updated frequently, 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.

If you are working with multiple values in a pivot table, Excel will create groups. Beside each group, there will be the “-” option. This hides the details of the group by collapsing them. This can also be done by pressing “collapse field” in the ribbon.

To change the way the pivot table looks, go to “design”. In this area, 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 how you want 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?

Pivot tables make working with data much less challenging and more convenient. This is especially true for anyone who is new to working with statistics or programs of this nature. If you still have more questions about pivot tables or working with Microsoft Excel in general, we would be more than happy to help. Reach out to us by phone or online with any issues you have. Our IT professionals are prepared to assist you so that you can get moving in the right direction.