Formatting the Pivot Table | How to become an Expert of MS Excel - Class 6 PDF Download

Introduction

Pivot tables allow you to transform and summarize large datasets into more manageable and meaningful information. They provide an easy way to explore patterns, relationships, and trends in your data. Pivot tables are especially useful when dealing with large amounts of data that may be difficult to analyze directly.

Creating a Pivot Table

Before we delve into formatting, let's quickly review how to create a pivot table.
Example Code:
Assuming you have a dataset in Excel with headers and columns, follow these steps to create a pivot table:

1. Select the entire dataset.
2. In Excel, go to the "Insert" tab and click on "PivotTable".
3. Choose the range of data you want to analyze.
4. Select the location where you want to place the pivot table (new worksheet or existing worksheet).
5. Drag and drop the relevant fields into the pivot table area (rows, columns, values).

Now that we have a pivot table set up, let's move on to formatting.

Modifying the Pivot Table Structure

Before diving into formatting options, let's explore how to modify the structure of a pivot table. You can adjust the layout by rearranging fields and changing their positioning within the pivot table.
To move a field:

1. In Excel, click and drag the field from one area to another (e.g., from rows to columns).
2. Release the mouse button to drop the field into the new area.

To remove a field:

1. In Excel, click on the field in the pivot table.
2. Press the Delete or Backspace key on your keyboard.

Formatting the Pivot Table

Applying Number Formats

Formatting numbers in a pivot table is crucial for presenting data accurately. You can apply various number formats, such as currency, percentages, or date formats.
Example Code:
Assuming we have a pivot table named 'pivot_table', let's apply currency formatting to the "Sales" column:

pivot_table['Sales'].style.format('${:,.2f}')

Code Explanation:

• 'pivot_table['Sales']' refers to the "Sales" column in the pivot table.
• '.style.format('${:,.2f}')' applies the currency format to the column, displaying the values with a dollar sign, comma separators, and two decimal places.

Adding Conditional Formatting

Conditional formatting allows you to highlight specific cells based on certain conditions. This can help draw attention to important data points or outliers.
Example Code:
Assuming we have a pivot table named 'pivot_table', let's add conditional formatting to highlight values greater than $1,000 in the "Sales" column:

pivot_table.style.applymap(lambda x: 'background-color: yellow' if x > 1000 else '', subset=['Sales'])

Code Explanation:

• 'pivot_table.style.applymap()' applies a custom formatting to each cell in the pivot table.
• 'lambda x: 'background-color: yellow' if x > 1000 else '' ' defines the condition for applying the background color (yellow) to values greater than $1,000.
• 'subset=['Sales']' specifies that the conditional formatting should be applied to the "Sales" column only.

Changing Fonts and Colors

You can further enhance the visual appearance of your pivot table by modifying fonts, colors, and borders.
Example Code:
Assuming we have a pivot table named 'pivot_table', let's change the font size and color of the entire table:

pivot_table.style.set_properties(font_size='12pt', color='blue')

Code Explanation:

• 'pivot_table.style.set_properties()' modifies the properties of the entire pivot table.
• 'font_size='12pt'' sets the font size to 12 points.
• 'color='blue'' changes the font color to blue.

Sample Problems with Solutions

Problem 1: You have a pivot table displaying monthly sales data. Apply a percentage format to the "Growth Rate" column.

pivot_table['Growth Rate'].style.format('{:.2%}')

Problem 2: Add conditional formatting to highlight cells where the "Profit" column is negative.

pivot_table.style.applymap(lambda x: 'background-color: red' if x < 0 else '', subset=['Profit'])

Conclusion

Formatting pivot tables not only improves their visual appeal but also enhances the readability and understanding of the data. By applying number formats, adding conditional formatting, and changing fonts and colors, you can effectively present and analyze your data. Experiment with different formatting options to find the style that best suits your needs and make your pivot tables stand out.