Chapter 5 Custom Filters in a Pivot Table:
In addition to the standard filtering options, Excel provides custom filtering capabilities within a pivot table. Custom filters allow you to define specific filtering conditions based on your analysis requirements:
Custom Text Filters: Custom text filters enable you to create custom filtering conditions using text criteria. This allows for more advanced text-based filtering, such as filtering data that contains specific words or phrases.
Custom Date Filters: Custom date filters allow you to define custom filtering conditions for date and time fields. You can filter data based on specific date ranges, periods, or relative dates.
Custom Number Filters: Custom number filters provide the flexibility to create custom filtering conditions based on numerical criteria. You can filter data based on specific ranges, greater than or less than values, or advanced calculations.
5.0.1 Sorting and Filtering Best Practices:
To make the most of sorting and filtering in a pivot table, consider the following best practices:
Understand Your Analysis Objectives: Before sorting and filtering data, have a clear understanding of your analysis objectives. Determine the specific insights you want to derive from the data, and identify the relevant sorting and filtering criteria.
Refresh and Update Data: It is essential to refresh and update the pivot table data regularly, especially when working with dynamic datasets. This ensures that the sorting and filtering reflect the latest information.
Utilize Multiple Sorting and Filtering Criteria: Instead of relying on a single sorting or filtering criterion, consider using multiple criteria to gain deeper insights. Combining different criteria allows you to focus on specific subsets of data and explore various aspects of your information.
Document Your Sorting and Filtering Choices: It is helpful to document the sorting and filtering choices you make in your pivot table. This allows you to replicate your analysis, track your decision-making process, and share your findings with others.
5.0.2 Sorting and Filtering Report Filters:
Report filters are another powerful feature in pivot tables that allow you to filter data across the entire table. Sorting and filtering options for report filters work similarly to row and column fields, enabling you to narrow down the data displayed in the pivot table based on specific criteria.
Sorting Report Filters: You can sort report filters alphabetically or numerically to arrange the filter values in a desired order. This makes it easier to locate specific values and navigate through the filter options.
Filtering Report Filters: Filtering report filters allows you to include or exclude specific values or apply custom filtering conditions. This helps in focusing the analysis on subsets of data that are relevant to your analysis objectives.
5.0.3 Clearing Sorting and Filtering:
Excel provides options to clear sorting and filtering in a pivot table when you want to revert to the original state:
Clear Sorting: To remove sorting from a field in a pivot table, you can click on the sort icon next to the field and select “Clear Sort.”
Clear Filtering: To remove filtering from a field in a pivot table, you can click on the filter icon next to the field and select “Clear Filter.”
5.1 Formatting Pivot Tables
Formatting is a crucial aspect of pivot table customization as it helps improve the visual appeal, readability, and overall presentation of the data. With Excel’s extensive formatting options, you can enhance the appearance of your pivot tables and highlight key insights. In this section, we will explore the importance of formatting in a pivot table, discuss various formatting techniques, and provide practical tips for effective formatting.
5.1.1 Applying Pivot Table Styles:
Pivot table styles are predefined formatting sets in Excel that provide a consistent and visually appealing look to the pivot table. Excel offers a variety of built-in styles that you can apply with a single click. Here are some key aspects of applying pivot table styles:
Selecting a Style: To apply a pivot table style, select any cell within the pivot table, navigate to the PivotTable Tools Design tab in the Excel ribbon, and choose a style from the available options. Excel provides various styles with different color schemes, font styles, and border designs.
Modifying Style Elements: Pivot table styles are highly customizable. You can modify individual elements of a style, such as fonts, colors, shading, and borders. This allows you to tailor the style to match your specific preferences or branding requirements.
Creating Custom Styles: Excel also allows you to create custom pivot table styles by modifying existing styles or creating new ones from scratch. This feature provides flexibility in designing unique styles that suit your specific needs.
5.1.2 Cell Formatting in a Pivot Table:
Cell formatting enables you to modify the appearance of individual cells within a pivot table. This helps in highlighting specific values, emphasizing key insights, or improving data readability. Here are some common cell formatting options:
Font Formatting: Excel provides various font formatting options, including font type, size, color, and style (bold, italic, underline). You can modify the font formatting for specific cells or ranges within the pivot table to draw attention to important information.
Alignment and Indentation: Excel allows you to adjust the alignment and indentation of text within cells. You can align text to the left, center, or right of a cell and apply indentation for a more organized and structured presentation of data.
Number Formatting: Number formatting helps in displaying numeric values in a desired format, such as currency, percentage, decimal places, or scientific notation. By applying number formatting, you can improve the readability and interpretation of numeric data within the pivot table.
Conditional Formatting: Conditional formatting allows you to apply formatting rules based on specific conditions or criteria. This helps in visually highlighting patterns, trends, or outliers in the data. You can define custom rules or choose from a variety of predefined formatting options like color scales, data bars, icon sets, and more.
5.1.3 Charting from a Pivot Table:
Charting is a powerful visualization tool that enables you to present data in a graphical format. Excel allows you to create various types of charts directly from a pivot table. Here’s how you can chart from a pivot table:
Selecting Data: Click on any cell within the pivot table, navigate to the PivotTable Tools Analyze or Options tab, and click on the “PivotChart” button. Excel will automatically select the pivot table data for chart creation.
Choosing a Chart Type: Excel offers a wide range of chart types, including column charts, bar charts, line charts, pie charts, and more. Select the chart type that best represents your data and analysis requirements.
Modifying Chart Elements: Once the chart is created, you can customize various chart elements such as titles, legends, axes, gridlines, data labels, and colors. Excel provides a set of formatting options that allow you to fine-tune the appearance of the chart to match your preferences.
Interacting with the Chart: Pivot charts are dynamic and interactive. You can drill down into the underlying data by clicking on chart elements, change chart views, and filter the data directly from the chart. This provides a more engaging and exploratory experience while analyzing the data.
5.1.4 Formatting Row and Column Headers:
Row and column headers in a pivot table provide important contextual information about the data. Formatting these headers enhances the visual clarity and organization of the pivot table. Here are some formatting options for row and column headers:
Font and Font Formatting: You can modify the font type, size, color, and style (bold, italic, underline) of the row and column headers. This allows you to emphasize the headers and improve their readability within the pivot table.
Background Color: Excel enables you to apply background colors or shading to the row and column headers. This helps in visually differentiating the headers from the data cells and improves the overall appearance of the pivot table.
Borders and Gridlines: Applying borders and gridlines to the row and column headers adds structure and delineation to the pivot table. Excel provides various border styles and thickness options that allow you to customize the appearance of the headers.