This article will take a brief look at how to create a PivotTable in Excel, as well as provide some tips for creating more effective, accessible PivotTables.
What are PivotTables?
One of Microsoft Excel’s most useful features for data analysis is PivotTables. These tools are used to summarize and analyze data, as well as to perform calculations on it that illustrate patterns, comparisons, and trends in the data. They also can sort, reorganize, count, total, average, group, and summarize the data from a table, as well as transform rows into columns, and vice-versa. PivotTables provide Excel users with quick answers to pressing business questions. Information that’s conveyed in a PivotTable is often more accessible to audience members than simply presenting them with numbers since it uses a table to visually tell a data-driven story.
Tips for Creating a PivotTable
It can be tricky at first to get the hang of creating PivotTables. Once you get a few core Excel principles down, however, it will be much easier to optimize this tool. The following are some tips to help create and customize PivotTables:
- Plan first. Instead of tinkering with Excel to try and find unique ways to visualize data, begin with a clear idea of what you want to understand and measure in a PivotTable. Taking pointed notes early in the creation process can save hours of time experimenting with various design choices.
- Clean the source data. If you want to save time down the road when working in Excel, make sure the data is clean. This can involve removing any blank columns or rows, and deleting any subtotals. All columns should also be assigned a distinct name.
- Group it manually. PivotTables in Excel automatically group data in various ways. However, users can also manually organize items into custom groups to better suit their needs.
- Display totals as percentages. It’s sometimes necessary to display a percentage instead of a count on a PivotTable. This feature is especially useful in instances such as when illustrating the breakdown of sales by product type and you’d like the sales to be shown as a percentage of total sales.
- Group numbers into ranges. All PivotTables in Excel can group numbers into buckets or ranges. This is helpful when you need to break down information by age group or other ranges.
- Rename fields to improve readability. Any time a field is added to a PivotTable, the name it was given from the source data is displayed. When incorporated into a PivotTable, value field names will show up as “Count of” or “Sum of.” Excel users can choose to change these names, though, to better reflect their own needs. In order to do so, choose the cell with the data field you’d like to rename and enter a new name there.
- Format every value field automatically. If you want to add a numeric field as a value to a PivotTable, the number format should be set directly onto the field. It’s never a good idea to directly format the displayed values in a PivotTable because as changes are made, the PivotTable will no longer be accurate. By setting the format on the field, Excel users can be certain that it will be shown in the intended format regardless of any changes to the size of the table.
- Clone the PivotTable to provide an alternative view. After the PivotTable has been set up, it may be necessary to see the data from a different view. One way to accomplish this is to rearrange the PivotTable to reflect a new view. However, if you are working on a report that will be continuously updated, it’s easier to clone the existing PivotTable in order to provide an alternative view of the data. This can be done by duplicating the worksheet where the PivotTable is located. Another way to clone it is to copy the PivotTable and paste it elsewhere. These tricks can be used to make as many copies as needed.
- Delete useless headings. By default, new PivotTables are displayed in the Compact layout. This layout will show “Column Lables” and “Row Labels” as PivotTable headings. However, these labels aren’t the most helpful headings for most users, in particular those who aren’t well-versed with PivotTables. There’s a simple way to replace these headings. If you switch the layout from Compact to Tabular or Outline layout, the PivotTable will instead show field names as headings. It’s also possible to delete the labels by selecting the Field Headers button on the “Analyze” tab in the Tools ribbon. This action will disable headings.
- Add white space around PivotTables. White space plays an integral role in the design of an effective PivotTable. Too little white space can make the layout look cluttered and even lead to confusion when audience members see the data. Adding white space around a PivotTable provides space for the information to breathe. In instances when you’d like to add white space to a PivotTable, you can add an extra row or two to the top, as well as an extra column to the left.
- Turn off AutoFit as needed. Each time a PivotTable is refreshed, the columns containing data are automatically adjusted to fit the data. This is a default Excel setting. While this is a helpful feature in some instances, it can become problematic if your worksheet includes more information than just the PivotTable. If you’d like to disable this feature, right-click in the PivotTable and select “PivotTable Options.” Uncheck “AutoFit Column Widths on Update” from the first tab of the options.
Get Started Learning Excel with Hands-On Classes
A great way to learn more about how to create PivotTables is enroll in one of Noble Desktop’s
Excel courses in NYC and live online. Classes are available for those who are new to Excel, as well as learners who regularly work with this spreadsheet application and wish to brush up on their skills. In addition, there are also in-person and live online Excel courses available through Noble Desktop or one of its affiliate schools. A variety of course options are available, ranging in duration from three hours to two days and costing between $229 and $1,099.
Noble Desktop’s Excel Bootcamp provides a great option for those who want to master core Excel concepts, such as working with VLOOKUP and What-If Analysis. Students who enroll in this rigorous, 21-hour course can elect to study in person in Manhattan or learn in the live online environment. This small class comes with the option of a free retake and covers a variety of Excel concepts applicable to the business world.
Those interested in finding Excel courses close to home can also browse more than 280 in-person Microsoft Excel classes to find nearby learning options.