Are you looking for new features to make working with Microsoft Excel easier? One of the best ways to analyze datasets is by using pivot tables. Pivot tables make organizing your business’s data easy and convenient to do. In this video, we discuss how you can get started using Microsoft Excel’s pivot tables for your company.
Before starting a pivot table, you should prepare your data to be used. The data must be organized into rows and columns, with no blank areas except for cells. All similar data should be in the same column as well. Column headings must be formatted differently than your data so that Excel is able to tell the difference between the two. Bolding or centering the data is one way to do this. Creating an island of data also separates it from unnecessary information.
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. Here 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. In this area, you can also change how data is represented.
When in a pivot table, two tabs will be shown in the ribbon: “pivot table analyze” and “design”. Here you can do things like refresh the table, which is not automatic. 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 your dataset changes often and you need to update your range, go to “change data source” and change the range. You can also extract specific rows by double-clicking on a value. Doing this creates a brand new pivot table.
If you have multiple values in one pivot table, groups will be created. Beside each group, the “-” option will be available. This will collapse 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 color and look of the pivot table, go to “design”. Here you can also change how totals are displayed.
To filter through a pivot table, to find things more easily, 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 you did not include in the table can also be used to filter the data.
Pivot tables make sorting through large amounts of data a breeze. In turn, this keeps your business more organized, saving you time and effort. If you would like to learn more about pivot tables or Microsoft Excel in general, contact us online or by phone. At Alliance Technology Partners, we have all the skills and expertise needed to assist you with your businesses’ technology needs. Reach out to us today.