4. Filter Area
The Area Filter in the PivotTable allows users to filter the data to make the analysis more focused. With Area Filters, users can select specific criteria that they want to display, allowing them to gain deeper insights from relevant data.
For example, you can show sales only for a specific region. By applying filters to those regions, you can analyze sales performance in that area without being distracted by data from other regions.
Tips
- Add Slicers for easier navigation: Slicers are visual tools that make it easy for users to select and apply filters. With the addition of Slicer, interacting with data becomes more intuitive and faster.
- Use filters on important categories, such as time or location: Focusing filters on significant categories, such as time range or location, will help in getting more relevant and useful insights.
How to Use the 4 Areas in a PivotTable
1. Select Data and Create a PivotTable
The first step is to select the data you want to analyze. Make sure the data is well-organized and has clear headers for each column. After selecting the data, go to the Insert tab in Microsoft Excel and select PivotTable. In the window that appears, select a location for the new PivotTable, either in the same worksheet or in the new worksheet.
2. Drag and Drop Data to Each Area
Once the PivotTable is created, you will see the PivotTable Fields panel. Here, you can drag and drop data to each area:
- Enter numeric data into the Values Area: Drag columns that contain numerical data (such as sales or revenue) to the Values Area. It allows you to calculate the total, average, or number of units.
- Add a primary category to the Row Area: Drag the column containing the primary category (such as the product name or location) to the Row Area. This will group the data based on those categories.
- Use Column Area for data comparison: If you want to compare data based on a specific parameter (for example, time), drag the column to the Column Area. It helps you see the comparison between categories in a column format.
- Use Area Filter to focus on a subset of data: Drag the column you want to use as a filter (such as a region or period) to Filter Area. This allows you to filter the data and focus on a specific subset.
3. Adjust Format and Aggregation as Needed
Once all the data is in the right place, the final step is to adjust the format and aggregation according to your analysis needs:
- Set number format: Right-click on a value in the Value Area and select Value Field Settings to change the aggregation method (Sum, Calculate, Average) according to the analysis needs.
- Apply visual formatting: Use formatting options in Excel to change the appearance of numbers to make them easier to read, such as adding currency symbols or setting decimal numbers.