May 05, 2015

Let’s assume you have a column full of numbers, as shown in Figure 1 below. Let's say you need to visually group the Quantity column into 4 groups or ranges as follows (0-5, 6-10, 11-15, 16-) as highlighted in Figure 2. How would you go about doing this?

Well, here’s one way you can go about grouping the data in Microsoft Excel.

1. Select the Quantity column.
2. From the Home tab, click on “Conditional Formatting.”
3. From the Condition Formatting drop down list, select “Highlight Cells Rules” and select “Greater Than …”
4. Type 0 in the first box and choose a colour format from the right.
5. Follow steps 2 to 4 but for step 4, type 5 and choose a different colour scheme.
6. Follow steps 2 to 4 but for step 4, type 10 and choose a different colour scheme.
7. Follow steps 2 to 4 but for step 4, type 15 and choose a different colour scheme.

By now, I’m sure you've got the pattern and can apply it to any other range you need. Your result should be similar to Figure 2 above.

You can also use the Sort tool to sort based on colours so that the data is arranged in visual groups.

Once sorted, your data should look like the below.

Another way of doing this is to select the “Less Than …” option from the Conditional Formatting options rather than “Greater Than …”. If you do this though, you need to remember to start from the top range and put 16 first then 11 and then 6. The order is important as conditional formats apply in the order they were last entered.