Master Excel pivot table formatting with our guide. Learn to create appealing, professional tables for clear data insights.
by Mihir Kamdar / Last Updated:
This comprehensive guide on mastering pivot table filter and sorting in Microsoft Excel. By the end of this article, you’ll be able to:
Download our step-by-step tutorial file now by clicking on the icon 
below and follow along to enhance your Excel skills practically and efficiently!
In today’s data-driven world, the ability to effectively present and communicate insights is a crucial skill for professionals across industries. Microsoft Excel’s pivot tables have become an indispensable tool for analyzing and summarizing large datasets, but creating a pivot table is just the first step. To truly make an impact and convey your message effectively, you need to master the art of pivot table formatting.
Before we dive into the intricacies of pivot table formatting, let’s establish a solid foundation by understanding what pivot tables are and is crucial for effective data communication.
A pivot table is a powerful Excel tool that allows you to summarize, analyze, and present large amounts of data in a flexible and dynamic way. It enables you to quickly identify patterns, trends, and insights by aggregating and rearranging data based on different dimensions and measures.
Example: Let’s say you have a sales dataset containing information about products, regions, sales representatives, and revenue. With a pivot table, you can easily summarize the total revenue by product category, compare sales performance across different regions, or analyze the contribution of each sales representative to the overall revenue.
Before we can start formatting our pivot table, we need to create one. In this chapter, we’ll walk through setting up a pivot table using a sample sales dataset.
Example Dataset: For this tutorial, we’ll be using a sample sales dataset named “SalesData.xlsx”. The dataset contains information about product sales, including fields such as Product Category, Product Name, Sales Region, Sales Representative, Order Date, and Revenue. You can download the sample dataset from the Office Tech Skill website or create a similar dataset of your own to follow along.
Step 1: Open the “SalesData.xlsx” file in Microsoft Excel.
Step 2: Select any cell within the dataset.
Step 3: Navigate to the “Insert” tab in the Excel ribbon.
Step 4: Click on the “PivotTable” button in the “Tables” group.
Step 5: In the “Create PivotTable” dialog box, ensure that the correct data range is selected. In this case, it should be the entire sales dataset, including headers.
Step 6: Choose whether to place the pivot table in a new worksheet or an existing one. For this tutorial, select “New Worksheet” and click “OK”.
Excel will create a new worksheet with a blank pivot table and display column fields in the PivotTable Fields pane on the right side of the screen.
Now that we have a blank pivot table, let’s add data to it to create a meaningful summary of our sales dataset.
Step 1: In the PivotTable Fields pane, locate the fields you want to add to your pivot table. For this example, let’s add the following fields:
Product Category (Rows)
Sales Region (Columns)
Revenue (Values)
Step 2: Drag and drop each field into the respective area (Rows, Columns, Values) of the PivotTable Fields pane or simply check the box next to the field name to add it to the default area.
Step 3: The pivot table will automatically populate with the summarized data based on the selected fields.
To create a clear and logical structure for your pivot table, it’s essential to arrange the row labels and the column label headers appropriately.
Step 1: To change the order of row labels, simply drag and drop the fields within the Rows area of the PivotTable Fields pane.
Step 2: To change the order of column and field headers here, drag and drop the fields within the Columns area.
Step 3: To add or remove subtotals and the grand total row and totals, right-click on any cell within the pivot table and select “Pivot Table Options”. In the “Totals & Filters” tab, you can choose to display or hide subtotals and the grand total row and totals for rows and columns.
With our pivot table set up and data organized, we’re now ready to explore the various formatting options available to make our data presentation more visually appealing and impactful.
In this chapter, we’ll explore the basic formatting options available in Excel to enhance the appearance and readability of our pivot table.
Excel provides a range of built-in styles that you can apply to your pivot table to instantly improve its visual appeal.
Step 1: Click on any cell within the pivot table to activate the “PivotTable Tools” contextual tab in the Excel ribbon.
Step 2: Navigate to the “Design” tab under “PivotTable Tools”.
Step 3: In the “PivotTable Styles” group, browse through the available styles and hover over each one to preview how it will look when applied to your pivot table.
Step 4: Click on the desired style to apply it to your pivot table.
Tip: Choose a new style name that aligns with your corporate branding and design guidelines, and ensure that it enhances the readability of your data.
Conditional formatting allows you to highlight specific cells or ranges based on certain conditions, making it easier to identify trends, outliers, or key data points.
Step 1: Select the cells or range within single cell of the pivot table that you want to apply conditional formatting to.
Step 2: Navigate to the “Home” tab in the Excel ribbon.
Step 3: Click on the “Conditional Formatting” button in the “Styles” group.
Step 4: Choose the desired conditional formatting rule from the dropdown menu, such as “Highlight Cells Rules”, “Top/Bottom Rules”, or “Data Bars”.
Step 5: Specify the criteria for the conditional formatting rule and select the formatting style to be applied when the criteria are met.
Step 6: Click “OK” to apply the conditional formatting to the selected cells.
Example: Let’s apply a conditional formatting rule to highlight the top 3 revenue values in each product category.
Select the revenue values in the pivot table.
Click on “Conditional Formatting” > “Top/Bottom Rules” > “Top 10 Items”.
In the “Top 10 Items” dialog box, select “3” and click “OK”.
Choose a formatting style, such as a green fill color, to highlight the top 3 revenue values.
While the built-in pivot table styles provide a quick way to format your pivot table, you may want to create a custom pivot table style that better aligns with your specific design requirements or corporate branding.
Step 1: Click on any cell within the pivot table to activate the “PivotTable Tools” contextual tab.
Step 2: Navigate to the “Design” tab under “PivotTable Tools”.
Step 3: In the “PivotTable Styles” group, click on the “More” button (the downward-facing arrow) to expand the styles gallery.
Step 4: Click on “New PivotTable Style” at the bottom of the gallery.
Step 5: In the “New PivotTable Style” dialog box, give your custom move table style a name and set the table element properties, such as fill color, font style, and border style, for each table element list of the pivot table (e.g., Row Headers, Column Headers, Values).
Step 6: Click “OK” to save your custom style.
Your new custom name style options will now be available in the PivotTable Styles gallery for future use.
The “Design” tab under “PivotTable Tools” offers several advanced formatting options that allow you to fine-tune the appearance of your pivot table.
Subtotals: Choose whether to display subtotals at the top or bottom of each group, or hide them altogether.
Grand Totals: Decide whether to show or hide grand totals for rows and columns.
Report Layout: Select from Compact, Outline, or Tabular layouts to change the arrangement of row and column labels.
Blank Rows: Insert or remove blank rows after each item to improve readability.
Banded Rows/Columns: Apply alternating shading to rows or columns for easier data scanning.
Experiment with these options to create a pivot table layout that best suits your data and audience.
To further customize the appearance of your pivot table, you can adjust the font size, color, and alignment of individual cells or ranges.
Step 1: Select the cells or range within the pivot table that you want to modify.
Step 2: Use the formatting options in the “Font” and “Alignment” groups on the “Home” tab of the Excel ribbon to make the desired changes:
Change the font style, size, and color
Apply bold, italic, or underline formatting
Align the cell contents horizontally and vertically
Indent or wrap the text within cells
Example: Let’s format the row headers to make them more prominent.
Select the row headers in the pivot table.
Change the font style to bold and increase the font size to 12.
Apply a dark blue font color to make the headers stand out.
Center-align the headers for a polished look.
By combining these three custom styles and formatting options with the built-in styles and design options, you can create a pivot table that effectively communicates your data story and aligns with your brand identity.
As you become more comfortable with the basic formatting style options, you can explore advanced techniques to take your pivot table design to the next level.
Applying appropriate number formatting to the values in your pivot table can greatly improve its readability and interpretability.
Step 1: Select the cells or range containing the values you want to format.
Step 2: Right-click on the selected cells and choose “Format Cells” from the context menu.
Step 3: In the “Format Cells” dialog box, navigate to the “Number” tab.
Step 4: Choose the appropriate number format category (e.g., Currency, Percentage, Number) and customize the field settings therein, such as decimal places and symbol placement.
Step 5: Click “OK” to apply the selected number format to the values.
Example: Let’s format the revenue values as currency with two decimal places.
Select the revenue values in the pivot table.
In the “Home” tab select “Currency” from the list.
Set the decimal places to 0.
Adding charts and graphs to your pivot table can help you visually communicate key insights and trends, making your data more engaging and easier to understand.
Step 1: Select any cell within your pivot table.
Step 2: Navigate to the “Insert” tab in the Excel ribbon.
Step 3: In the “Charts” group, click on the desired chart type (e.g., Column, Bar, Line, Pie).
Step 4: Choose a specific chart subtype from the dropdown menu.
Step 5: Excel will create a pivot chart based on the data in your pivot table.
Step 6: Use the “Chart Tools” contextual tabs (“Design”, “Layout”, and “Format”) to customize the appearance of your pivot chart, such as changing the chart title, adding data labels, and modifying the color scheme.
Example: Let’s create a column chart to visualize the revenue by product category.
Select any cell in the pivot table.
Go to the “Insert” tab and click on the “Column” chart type.
Choose a “Clustered Column” chart subtype.
Excel will create a pivot chart showing the revenue for each product category.
By incorporating charts and graphs, you can make your pivot table more visually appealing and easier to interpret, helping your audience quickly grasp the key takeaways from your data.
Different data types, such as dates, text, and large datasets, may require specific formatting techniques to ensure optimal presentation and analysis.
When working with date and time values in your pivot table, you can apply specific formatting to make the data more readable and relevant.
Step 1: Select the cells or range containing the date or time values.
Step 2: Right-click on the selected cells and choose “Format Cells” from the context menu.
Step 3: In the “Format Cells” dialog box, navigate to the “Number” tab.
Step 4: Choose “Date” or “Time” from the category list and select the desired format from the “Type” list.
Step 5: Click “OK” to apply the selected date or time format to the values.
Additionally, you can group date fields by years, quarters, months, or days to summarize your data at different levels of granularity.
Step 1: Right-click on a cell containing a date value in your pivot table.
Step 2: Select “Group” from the context menu.
Step 3: In the “Grouping” dialog box, choose the desired grouping level (e.g., Years, Quarters, Months, Days) and specify the starting and ending dates if needed.
Step 4: Click “OK” to apply the grouping.
Example: Let’s format the “Order Date” field in our pivot table to display only the month and year, and group the dates by quarter.
Select the cells containing the “Order Date” values.
Right-click and choose “Format Cells”.
In the “Format Cells” dialog box, select “Date” from the category list and choose the “mmm yyyy” format from the “Type” list.
Click “OK” to apply the month and year format to the “Order Date” values.
Right-click on a cell in the “Order Date” field and select “Group”.
In the “Grouping” dialog box, select “Quarters” and click “OK” to group the dates by quarter.
When dealing with text and categorical data in your pivot table, you can apply specific formatting techniques to make the information more organized and easier to read.
Step 1: Select the cells or range containing the text or categorical data.
Step 2: Use the formatting options in the “Home” tab of the Excel ribbon to:
Apply text wrapping to fit long text within cells
Adjust the text alignment (left, center, or right) for better readability
Change the font style, size, and color to emphasize important categories
Additionally, you can use the “Sort” and “Filter” options in the pivot table to organize and focus on specific categories.
Step 1: Click on the dropdown arrow next to a text or categorical value field in the pivot table.
Step 2: Choose “Sort A to Z” or “Sort Z to A” to arrange the categories in ascending or descending order.
Step 3: To filter the data, uncheck the categories you want to exclude from the pivot table and click “OK”.
When working with large datasets and complex pivot tables, it’s essential to apply formatting techniques that make the data more manageable and easier to navigate.
Use the “Collapse” and “Expand” buttons to show or hide different levels of detail in the row and column labels.
Apply “Banded Rows” or “Banded Columns” to alternate shading for better readability.
Use the “Subtotals” and “Grand Totals” options to control the display of aggregated values.
Implement conditional formatting to highlight important data points or trends.
Utilize the “Report Filter” field to create interactive filters for users to focus on specific subsets of the data.
Example: Let’s format a large and complex pivot table to make it more user-friendly.
Add the “Region” field to the “Report Filter” area to allow users to filter the data by specific regions.
Collapse the “Product Category” field to show only the high-level summary by default.
Apply “Banded Rows” to alternate the shading of rows for better readability.
Use conditional formatting to highlight the top 5 products by revenue in each category.
By applying these formatting techniques, you can make even the most complex pivot tables more accessible and easier to analyze for your audience.
To ensure your pivot tables are effective, professional-looking, and easy to understand, consider the following best practices and tips:
Use a consistent font style, size, and color scheme throughout your pivot table and workbook.
Maintain adequate white space between rows and columns to improve readability.
Apply text wrapping and adjust row heights to ensure all data is visible and easy to read.
Use alignment and indentation to create a clear hierarchy and structure within your pivot table.
Use high-contrast color schemes to ensure your pivot table is readable for users with visual impairments.
Provide alternative text descriptions for pivot table elements and charts to support screen reader users.
In addition to creating dashboards and reports, you may also need to present your pivot table insights in presentations or slideshows. By applying specific formatting techniques and considering your audience, you can create impactful and engaging presentations that effectively communicate your data story.
Use a clear and legible font style and size that can be easily read from a distance.
Apply a high-contrast color scheme that ensures data is visible on projection screens or displays.
Simplify pivot table layouts by focusing on key data points and insights.
Use white space effectively to avoid overwhelming the audience with too much information.
CSopy and paste your pivot table as an image for a static snapshot of the data.
Adjust the size and position of the embedded pivot table to fit the slide layout and maintain readability.
How do you format a pivot table nicely? To format a pivot table nicely, follow these steps:
1. Apply a built-in pivot table style from the “Design” tab in the “PivotTable Tools” contextual tab.
2. Customize the style by modifying fonts, colors, and other formatting options to match your preferred design or branding.
3. Use the “Format” pane to adjust individual formatting elements, such as number formats, alignment, and borders.
4. Apply conditional formatting to highlight specific values or trends in your data.
5. Adjust column widths and row heights to ensure the data fits well within the cells and is easy to read.
To change the layout of your pivot table:
1. Click on any cell within the pivot table to activate the “PivotTable Tools” contextual tab.
2. Go to the “Design” tab and locate the “Report Layout” group.
3. Click the “Report Layout” dropdown and choose from the available options: “Compact Form,” “Outline Form,” or “Tabular Form.”
4. Experiment with each layout to determine which one best suits your data and desired presentation.
To change the data format in a pivot table:
1. Select the cells or range within the pivot table that you want to format.
2. Right-click the selection and choose “Format Cells” from the context menu.
3. In the “Format Cells” dialog box, select the “Number” tab.
4. Choose the appropriate category (e.g., Number, Currency, Percentage) and customize the formatting options, such as decimal places and symbol placement.
5. Click “OK” to apply the selected formatting to the pivot table data.
To update a pivot table without changing the format:
1. Click on any cell within the pivot table.
2. Go to the “PivotTable Analyze” contextual tab in the ribbon.
3. Click the “Refresh” button in the “Data” group to update the pivot table with the latest data from the source.
4. The pivot table will now display the updated data while preserving the existing formatting and layout.
In this comprehensive guide, we’ve explored the world of pivot table formatting in Microsoft Excel, covering everything from basic formatting options to advanced techniques, real-world examples, and best practices for collaboration and continuous learning.
You’ve learned how to:
By mastering the art of pivot table formatting, you can transform raw data into visually appealing, insightful, and impactful presentations that inform, persuade, and drive action.
