
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.
- Select the Quantity column.
- From the Home tab, click on “Conditional Formatting.”
- From the Condition Formatting drop down list, select “Highlight Cells Rules” and select “Greater Than …”
- Type 0 in the first box and choose a colour format from the right.
- Follow steps 2 to 4 but for step 4, type 5 and choose a different colour scheme.
- Follow steps 2 to 4 but for step 4, type 10 and choose a different colour scheme.
- 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.
How do your Excel skills stack up?
Test NowNext up:
- Different communication styles, Part 1 – the best communicators know this, so should you.
- Have you ever…?
- Round, RoundUp and RoundDown in Excel
- How to scrape a website
- How to create an e-mail template in Outlook
- How to avoid reinventing the wheel
- Quick ways to automate in Photoshop – Part 2: Modifying an Action
- What is new in Office 365
- Find a filter result without filtering in Excel
- Managing application settings in Windows Store Apps
Previously
- Create a Windows 8.1 Enterprise Reference Image with MDT 2013
- Are You a Smarter Buyer?
- Installing ClockworkWorkMod recovery on your Samsung device
- Termination is possible
- Outlook rules rule!
- Group Managed Service Accounts in Windows Server 2012
- Automate your table of contents in Microsoft Word
- Windows To Go! Part 3.
- The Moth: a bright idea that can help you influence better
- Reduce those pesky meeting request response messages in Outlook