Excel PivotCharts and formatting – After visualizing data with a PivotTable, you can use a PivotChart to further emphasize the values you want. Let’s look at how to change the design of PivotCharts and PivotTables.
Excel PivotCharts
PivotCharts are useful tools for analyzing and visualizing data. They can be used in many ways to summarize large data sets and make them easier to review. Because changes in the source data are tracked and reflected in the PivotChart, they also enable real-time analysis.
How to Use PivotCharts
- Select the source data created with a PivotTable, then select PivotChart.
- Below is the default PivotChart design.
- You can easily change the design to your preferred style using the PivotChart Design tab.
Excel Slicers
Using a Slicer can be considered an upgraded version of Excel filtering. Slicers offer the following benefits.
- Fast filtering
: Slicers let you quickly filter the data you want in a PivotTable. You can easily filter data by dragging or clicking a slicer, and because only filtered data is displayed, unnecessary information is removed. - Visual impact
: Slicers provide visually effective filtering. Filtered data is updated and displayed in real time, making it easy to identify changes in the data. - Various filtering options
: Slicers provide a variety of filtering options. For example, you can filter different types of data, including dates, numbers, and text, and combine multiple slicers for complex data analysis. - Ease of use
: Slicers are simple and intuitive. Select and drag the data you want, and filtering is applied immediately. Because only filtered data is displayed, you can easily find the information you need. - Flexible analysis
: Slicers enable flexible analysis. Because you can select and analyze only the data you need, you can eliminate unnecessary information and focus on what matters. You can also combine slicers for a variety of analyses.
How to Use Slicers
- To use the Slicer feature in a PivotTable, select INSERT SLICER below.
- Select the fields to use in the Slicer.
- The following is the default Slicer design.
- You can also easily change the Slicer’s appearance using the Design tab.
- In SLICER SETTINGS, you can also choose how items are displayed and sorted.
- When you select a Slicer item, the PivotChart is automatically linked and visualized.
- As shown below, the PivotTable and PivotChart are linked according to the items selected in the Slicer, visually displaying the values.
- Remove the Slicer filter to view all data.
Keep Excel PivotTable Column Widths Fixed
After creating a PivotTable, the column widths you adjusted for the PivotTable layout may reset whenever you move a filter. Let’s look at how to keep PivotTable column widths fixed.
How to Keep Column Widths Fixed in Excel
- Click the PivotTable, then select Options.
- In PivotTable Options, clear the automatic column width update setting.
Ungroup Excel PivotTable Items
The default PivotTable format groups data by item. You can change this through the Design tab.
Ungroup PivotTable Items and Change Their Positions
- Use the PivotTable Design tab and select REPORT LAYOUT.
- Choose the desired layout. As shown below, you can place PivotTable items in separate columns.
- You can also repeat item labels in the PivotTable.
Use Automatic Formulas by PivotTable Group
With a PivotTable, you can easily quantify source data values by using various formulas.
How to Use Advanced PivotTable Formulas
- For the sales quantities below, we will automatically insert the sales percentage by salesperson.
- Add the column for which you want to use an advanced PivotTable formula a second time.
- Select one of the duplicate sales quantity values and change it to sales percentage. Right-click the PivotTable and change “Show Values As” as shown below to obtain the desired value.
- As shown in the data below, the sales percentages have been calculated all at once. They can be updated when the source data changes.
- You can easily visualize data using PivotTable design options.
Conclusion
PivotTables are one of Excel’s best features for efficiently producing results that would otherwise require Excel formulas and formatting. By using PivotCharts, slicers, advanced formatting, the Design tab, and advanced formulas, you can create effective results. Understanding these features alone will be enough to achieve the results you want in Excel.