Grouping similar text by color visually organizes large and complex datasets. Although Excel doesn’t have a direct formula to group by color, you can combine formulas, filters, conditional formatting rules, and helper columns to achieve this easily.
As a workaround, we’ll first apply conditional formatting rules to cells containing similar text. After that, we can use the SORTBY function to rearrange our dataset to group texts by color using those numbers.
Steps to group similar texts by color:
➤ To apply conditional formatting rules to color the cells with similar text, select the range, go to the Home tab >> Conditional Formatting drop-down >> New Rule >> Use a Formula to Determine Which Cells to Format.
➤ In the formula box, enter:
=MOD(MATCH(D2, UNIQUE($D$2:$D$10), 0), 10) =1
➤ Replace the cell references with the range you’re grouping. You can change 10 to however many distinct colors you want to use. Click Format >> Fill tab >> choose a color (color 1) >> Ok twice. Repeat the process with the same logic, changing =1 to =2, =3, etc., and using different colors.
➤ Now, create another helper column and insert this formula:
=SORTBY(A2:D10, D2:D10 , 1)
➤ Replace A2:D10 with your original data range and D2:D10 with the column range to sort by (Column D). Press Enter to sort the entire range in ascending order, grouping similar texts by color.
In this article, we’ll cover all the ways of grouping cells with similar texts and colors using Conditional Formatting, SORTBY function, and VBA coding.
Grouping Similar Texts by Manually Assigning Numbers to Cell Colors
Our sample dataset has 4 columns for Salesperson, Units Sold, Revenue ($), and Region. Our goal is to apply a unique fill color for each similar text group in Column . After that, we’ll use a formula to group the cells with the same fill color.
Here, we’ll use a conditional formatting formula first to color the duplicate texts and then use the SORTBY formula to rearrange them into groups. Remember that this method only works with conditional formatting, not manual color coding. Let’s get to the steps:
➤ Select the range you want to color (D2:D10). Go to the Home tab and click on the Conditional Formatting drop-down. Choose the New Rule option from the Menu.
➤ As the New Formatting Rule dialog box appears, click on Use a Formula to Determine Which Cells to Format from the Select a Rule Type group.
➤ In the Format Values Where This Formula Is True box, enter this formula:
=MOD(MATCH(D2, UNIQUE($D$2:$D$10), 0), 10) =1
➤ Here,$D$2:$D$10 is the range where we’re applying the colors and D2 is the first cell of the range. Replace the values according to your dataset. We inserted 10 to apply up to 10 different colors for the groups. You can change 10 to however many distinct colors you want to use.
➤ Click Format and use the Font or Fill tab to choose a color for the first group containing similar text (color 1). Press Ok.
➤ See the Sample section to check how the color works and click Ok to color the first group.
➤ Now, for the remaining groups, repeat the steps and only change =1 to =2, =3, etc., in the formula. Also, you must pick a different color while formatting.
➤ For example, we entered the following formula for the second group:
=MOD(MATCH(D2, UNIQUE($D$2:$D$10), 0), 10) =2
➤ Click on Format and choose a new color. Press Ok twice to color the second group like this:
➤ Here’s the final output after applying a different color to each group of similar texts.
➤ Now, create a helper column (Column F) and enter any of the following formulas in its first cell (F2):
To Sort the Entire Dataset
If the column you’re grouping is related to the remaining dataset, you must include the entire range in the formula to maintain data consistency and accuracy. So, enter this formula:
=SORTBY(A2:D10, D2:D10 , 1)
➤ Here, A2:D10 is our entire data range and D2:D10 with the colored column range to sort by (Column D). Replace the values according to your dataset. We used 1 to sort the range in ascending order (A to Z). To sort in descending order (Z to A), enter -1 instead.
➤ Press Enter and Excel will return a spilled range of your sorted data.
➤ Copy the range and select your original data range. Right-click and choose Paste Special.
➤ Select Values and press Ok. If Excel prompts you to confirm your selection, click Ok.
➤ As the conditional formatting formula recalculates, you now have grouped cells containing similar texts and colors. You can now delete the helper column.
To Sort a Single Column
When each column in your data range contains unrelated values, you can group similar texts by color in a single column. Enter the following formula:
=SORTBY(D2:D10, D2:D10 , 1)
➤ Here, the range D2:D10 is the range we’re grouping and the range we’re sorting by.
➤ Press Enter to spill the grouped column.
➤ Copy the spilled data, select your original column, and right-click on it.
➤ Choose Paste Special >> Values >> Ok.
➤ Here’s the final result after removing the helper column:
Customize Formula with VBA to Group Similar Texts by Color
In the previous method, we grouped our dataset using similar text values. To truly group your dataset by color using a formula, the only way is to use a VBA macro. Here’s how:
➤ First, use the conditional formatting method described above to color similar texts.
➤ Right-click on your sheet name and select View Code from the menu.
➤ In the Module box, paste any of the following codes to create a function that generates a number for cell colors:
Sub AssignColorNumbers()
Dim rng As Range, cell As Range
Dim ws As Worksheet
Dim colorValue As Long
' Ask user which range to process
On Error Resume Next
Set rng = Application.InputBox("Select the range to analyze:", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Loop through each cell and record its visible (displayed) color
For Each cell In rng
colorValue = cell.DisplayFormat.Interior.Color
cell.Offset(0, 1).Value = colorValue ' Write the color number in the next column
Next cell
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "Done! Color numbers written in next column.", vbInformation
End Sub
➤ Now, press Alt + Q to close the VBA Editor. Go back to the Excel tab and press Alt + F8 and select AssignColorNumbers and press Run.
➤ Select the colored range with your conditional formatting rule (D2:D10) and click Ok.
➤ Excel will now assign a unique color number to each group in the next column (Column E).
➤ Now, you can use a formula with the SORTBY function to group your dataset based on the index numbers.
➤ For example, we’ve inserted the following SORTBY formula to group similar texts by color:
=SORTBY(A2:E10, E2:E10, 1)
➤ Change A2:E10 to your original data range with the assigned color numbers and E2:E10 is the range to sort by.
➤ Press Enter to sort in ascending order. Copy the sorted data, right-click on your original data, choose Paste Special >> Values >> Ok. Your data is now grouped by color.
Applying the Sort by Color Feature to Group Similar Texts (Without Formula)
Another easy way to group texts without a formula, you can use Excel’s built-in Sort by Color feature. Here’s how:
➤ First, apply the conditional formatting rule mentioned above to color code similar texts.
➤ Now, select your dataset (A2:D10) and go to the Data tab >> Sort.
➤ In the Sort dialog box, select the column you want to sort by from the Sort By dropdown.
➤ Under Sort On, choose Cell Color. From the Order drop-down, select the first color you want to move to the top. You can also choose whether to place it On Top or On Bottom. Once done, click on Add Level.
➤ Choose the same values from the Sort By and Sort On drop-down. From the Order drop-down, pick a new color and choose On Bottom. Keep adding more levels to cover all the colors in your worksheet. You can skip the last color. Click Ok when you’re done.
➤ Excel will now apply the sort and group cells with similar colors and texts.
Frequently Asked Questions
How to combine cells with the same text in Excel?
If you have the same text appearing multiple times and want to combine related cells, enter this formula:
=TEXTJOIN(", ", TRUE, IF(A:A=A2, B:B, ""))
This formula joins all values from Column B where Column A matches A2. Replace the values as needed. Press Enter if you’re using Excel 365/2021. In older Excel versions, Ctrl + Shift + Enter .
How to filter similar texts by color in Excel?
After applying a fill color or using conditional formatting for coloring, you can filter similar texts by cell color using the Filter feature. Start by selecting your dataset and go to the Home tab >> Sort & Filter >> Filter (or press CTRL + SHIFT + L). Click the Filter Arrow on the column containing colored cells. Choose Filter by Color from the menu and select the color you want to filter out. Excel will instantly show only the rows with that color.
How do you aggregate by group in Excel?
The GROUPBY function (Excel 365/2021) allows you to group, aggregate, sort, and filter data directly in a formula. Let’s say you have 5 grouped regions in Column A and their corresponding sales values in Column B. To get the aggregated/summed values for each group (region) in Column C, enter this formula in C2:
=GROUPBY(A2:A10, B2:B10, SUM)
Press Enter and Excel will give you a list of unique regions and the summed sales values for each region.
Concluding Words
With the above-mentioned methods, you can easily group rows with the same color and similar texts. Using a VBA macro is the only way to accurately generate a number based on color.
For the other method, you have to rely on the text values instead of colors as Excel doesn’t have any feature to recognize colors. Keep in mind that color indexes can vary between different Excel versions and color palettes.
