Sorting colors in Excel PivotTables

Ever found yourself wishing you could sort your Excel pivot table by color? While Excel doesn't have a built-in color sorting option, you can achieve this with a bit of a workaround. Let's dive into how you can sort your pivot table by color, making your data analysis more visual and insightful.

Sort data by color in Excel
Sort data by color in Excel

Before we begin, ensure your pivot table's cells contain color names or codes. This could be in the form of "Red", "Blue", HEX codes like "#FF0000", or RGB values similar to "255,0,0". Now that we're set, let's explore two methods to sort by color in Excel pivot tables.

Data Sorting in Excel Pivot Tables | Easy Step-by-Step Guide
Data Sorting in Excel Pivot Tables | Easy Step-by-Step Guide

Method 1: Using the AutoFilter Option

This method is simpler but works only when your colors are in the form of named colors (like "Red" or "Blue").

the pivot tables training poster is shown in blue and green, with instructions on how to
the pivot tables training poster is shown in blue and green, with instructions on how to

First, select any cell within your pivot table, then click on the 'Data' tab in the Excel ribbon. Next, click on 'Filter' and choose 'Sort by Color' from the dropdown menu. This will sort your pivot table rows or columns based on the color of the cell contents.

Sorting by Row Color

Excel Pivot Table Explained (Part 1) | Easy Guide for Beginners with Example
Excel Pivot Table Explained (Part 1) | Easy Guide for Beginners with Example

To sort rows by their color, select any cell in the row you want to sort, then navigate to 'Data' > 'Sort' > 'Sort by Color'. Choose 'Row Color' from the dropdown and click 'OK'.

prendicular needs like XML or JSON import, you might want to use our second method, which works with color codes as well.

Sorting by Column Color

How to Update an Excel Pivot Table - Even if the Source Data Changes (+ video tutorial)
How to Update an Excel Pivot Table - Even if the Source Data Changes (+ video tutorial)

For column sorting by color, select any cell in the column to be sorted, then go to 'Data' > 'Sort' > 'Sort by Color'. Select 'Column' color from the dropdown and click 'OK'.

Remember, this method works only when your pivot table cells contain named colors. If you're using color codes, proceed to method two.

Method 2: Sorting by Color using Built-in Custom Sort

How To Create A Pivot Table In Google Sheets - A Step By Step Guide, Google Sheets
How To Create A Pivot Table In Google Sheets - A Step By Step Guide, Google Sheets

This method is more flexible as it works with both named colors and color codes. We'll be using Excel's built-in custom sort feature to create custom sort rules for our color codes.

First, let's create our custom sort rules. Open a new worksheet, and in the first column, list your colors (or color codes if that's what your pivot table uses). In the second column, assign each color a sort order. For example, 'Red' could be '1', 'Green' could be '2', and so on.

How to Alternate Row Colors in Excel Without a Table (5 Methods) - ExcelDemy
How to Alternate Row Colors in Excel Without a Table (5 Methods) - ExcelDemy
Pivot Table Secrets : Repeat Label Items and Tabular Form in Excel #excel #exceltips #exceltutorial
Pivot Table Secrets : Repeat Label Items and Tabular Form in Excel #excel #exceltips #exceltutorial
10k views on how to use pivot table in excel
10k views on how to use pivot table in excel
Miss Excel
Miss Excel
50 Things You Can Do With Excel Pivot Table | Free Microsoft Excel Tutorials
50 Things You Can Do With Excel Pivot Table | Free Microsoft Excel Tutorials
Ultimate excel pivot tables tutorial
Ultimate excel pivot tables tutorial
Remove Duplicates from the Pivot Table
Remove Duplicates from the Pivot Table
Pivot Tables in Excel Explained: What Are They Actually For?
Pivot Tables in Excel Explained: What Are They Actually For?
an excel pivot table manual with the text sort in red and green above it
an excel pivot table manual with the text sort in red and green above it
Excel Pivot Tables - 101 Practical Examples for Every User
Excel Pivot Tables - 101 Practical Examples for Every User
Excel's Color Palette Compatibility Solution
Excel's Color Palette Compatibility Solution
Sort by Day of the week!
Sort by Day of the week!
How to add data bars to pivot tables!
How to add data bars to pivot tables!
How to make and use Pivot Table in Excel
How to make and use Pivot Table in Excel
Transform Data with Excel Pivot Tables - 50 Essential Tips
Transform Data with Excel Pivot Tables - 50 Essential Tips
How to Create a Pivot Table in Google Sheets
How to Create a Pivot Table in Google Sheets
Excel pivot table summarize data like a pro
Excel pivot table summarize data like a pro
How To Use Excel Fill Color Shortcut: A Quick And Easy Guide
How To Use Excel Fill Color Shortcut: A Quick And Easy Guide
the pivot table in 5 minutes info sheet with numbers, times and other information
the pivot table in 5 minutes info sheet with numbers, times and other information
How to Apply Conditional Formatting to a Pivot Table in Excel
How to Apply Conditional Formatting to a Pivot Table in Excel

Creating Custom Sort Rules

To create custom sort rules:

  • Select your color column.
  • Go to 'Data' > 'Sort' > 'Custom sort...'.
  • Click on 'Copy from ...' and choose 'Sort Rules...'.
  • Enter your sort order (1,2,3....).
  • Click 'OK' to create your custom sort list.

Applying Custom Sort to Pivot Table

Now that we have our custom sort rules, it's time to apply them to our pivot table:

  • Select any cell in your pivot table and go to 'Data' > 'Sort' > 'Custom sort...'.
  • Click on 'Copy from ...' and choose the sort list you've created.
  • Choose 'Values' in the 'Sort on' dropdown.
  • Choose 'Sort by sort position' in the 'Subtotal' dropdown.
  • Click 'OK' to sort your table.

After sorting, you might find the colors aren't borne out. To keep colors visible, you can use conditional formatting or change the cell fill colors based on their values.

There you have it! Now you can sort your Excel pivot table by color, enabling more engaging data visualization. Happy analyzing, and until next time, stay colorful!

Related Articles

How To Make Black And White Photo To Color In Photoshop How To Change Black And White To Color On Iphone How To Change Page Color In Indesign How To Fill Color In Excel Cell Using Formula How To Add Color To A Black And White Photo In Procreate Picture Of A Turkey In Color Free Printable Disguise A Turkey Printable Pdf Can You Count Colored Cells In Google Sheets How To Change Black And White To Color On Android How To Sort By Cell Color In Excel