Microsoft Excel: Getting Started With Pivot Tables

Pivot tables are a highlight of Microsoft Excel. Pivot tables provide organizational and analytical capabilities that make them a regularly used tool by many businesses. In the following video, we will discuss how pivot tables can be utilized in the workplace.

YouTube video

Understanding Pivot Tables

Similarities, differences, highs, and lows can all be examined in datasets by using pivot tables. Before developing a pivot table you should learn a few things. The data being used and arranged in the pivot table is referred to as the “source data”.  Pivot tables consist of four differing areas, which are “row labels”, “column labels”, “values”, and the “report filter”. Furthermore, each column in the pivot table represents a different section of data. This assists in the organization of all data.

How To Prep Your Data

All data should be organized and prepared prior to inputting it into a pivot table. You should start by making columns and rows and only leaving blank areas for cells. Any data that is similar should be grouped together in the same columns. The column heading should be a different format than your data. This can be completed by bolding or centering column headings. By doing this it helps the system process the data more efficiently and complete the task quicker.

How To Create A Pivot Table

To develop a pivot table, select “insert”, pick “recommended pivot tables” or “pivot table”, next select the range you are going to use, select “new worksheet”, then click “okay”. To enlarge the pivot table find “pivot table fields” and select the number of fields you want to include. To determine the values and pick a category, hover over a cell, right-click, and then click “number format”. Doing this will allow you to manipulate how the data is being shown.

More Helpful Tips

Once opening a table, “pivot table analyze” and “design” will appear. It is important to refresh your data regularly to keep it up to date. This can be done by selecting “pivot table analyze” and hitting “refresh”. Another option available is to use the keyboard shortcut Alt +F5. To refresh multiple tables at once, use “refresh all”. By selecting “change data source” you can manipulate the range. Also, by double clicking on specific values, you can use them to create new tables with select values.

Excel will automatically create groups if multiple values exist. When new groups are created a “-” will become available alongside each group. This option will hide the details of any group that you choose. This feature can also be used by selecting “collapse field”, which is located in the ribbon. Selecting “design” will allow you to manipulate the display of totals and the table’s appearance.

You can filter through tables by using the column or row dropdowns. To only see certain categories, click on the ones you want to view, and the others will be hidden. Categories can be filtered by clicking on a value and selecting it. Completing this action will exclude all values that were not selected, although each value can still be used to filter.

How Will Using Pivot Tables Help You?

Pivot tables are one of Excel’s best features when it comes to organizing and analyzing data. They can help you take your business’s statistical records to a whole new level. If you would like to learn more about pivot tables or Excel, reach out to us. Our IT professionals are ready to assist with all your technology needs. Contact us online or by phone today.