Sales: (201) 262-5066
Microsoft Excel: Getting Started With Pivot Tables
Posted on February 7, 2021
Microsoft Excel: Getting Started With Pivot Tables
Are you wondering how to get the most out of your Excel program? One way to easily sort through and analyze datasets is by using pivot tables. In this video, we discuss how to get started using pivot tables for your Excel data.
Before we discuss what you can do with pivot tables, first you must understand what they are. Pivot tables are interactive tables that analyze large amounts of data in Microsoft Excel. They are able to examine differences, similarities, highs, and lows in a dataset. The data the pivot table is based on is called the source data. Each 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
When creating a new pivot table, you must first prepare your data to be used. Make sure that your data is organized into rows and columns, with no blank areas except for cells. Also check that all similar data is in the same column. Your column headings will need to be formatted differently than your data so that Excel is able to tell the difference. Bolding or centering the data should do the trick. You’ll want to create an island of data, separating it from unnecessary information.
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 would like to include. In this area, you can customize which values are shown. To change this, hover over a cell, right-click, select “number format”, choose the category, and make the changes. Under this tab you can also change how data is represented.
More Helpful Tips
When in a pivot table, you will see two tabs in the ribbon: “pivot table analyze” and “design”. Under this, you can do things like refresh the table, which is not automatic. 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 your dataset is changing a lot and your range needs updating, go to “change data source” and change the range. You can also extract certain rows by double-clicking on a value. This will create an entirely new pivot table.
When you are using multiple values within one pivot table, groups will be created. Next to each group, the – option will be available. This will collapse the details of the group. You can also do this by pressing “collapse field” in the ribbon.
To change the aesthetic of the pivot table, go to “design”. Here you can change things like the color of the table. You can also change how totals are displayed.
To filter through a pivot table, use the row or column dropdown (depending on which you would like to filter) and select the categories you would like to see. You can also sort by right-clicking on a value and choosing how you would like for it to be sorted. Values that are not being used in the table can also be used to filter the data.
Are You Ready To Get Started With Pivot Tables?
Pivot tables are a super-easy way to sort through large amounts of data, keeping your business more organized. If you have more questions about pivot tables or Microsoft Excel as a whole, contact us. Techspert’s IT professionals are able to assist you in any way you need. Helping you learn how to better use programs is our goal, so reach out to us today.