In this article, we will learn How to Coloring One Record in Each Group in a Range in Excel.
Sometimes given a list of values we need to highlight only first output or unique values. For example if we need to highlight the entries where a customer once transacted in a store. If an entry is repeated, then it shouldn't be highlighted. Excel provides us with a conditional formatting tool and some formula logic. Using Conditional formatting over cells and then adding values on it. So we need a formula for the data which highlights cells only matching one record matching values.
How to solve the problem?
For this we will be using the Conditional formatting obviously and COUNTIF function. This formula is derived using Expanding References. Where we first input formula to the first cell and then copy and paste the formatting (with formula) on to the required range. This formula only highlights the first record or the unique records.
cell : first cell of the range (we don't need to specify the last cell, expanding references will take care of it)
=1 : equals to operator to match criteria (first record)
All of these might be confusing to understand. Let's understand this formula by running it on an example. Here we have data with entries of the staff in an office. There are multiple entries of some staff members. We need to highlight the first entry. Follow the steps:
Use the formula for matching value less than 50000.
Select the format as you require, it offers many different types of formatting. We selected to color fill the record or cell as shown above. Click OK
Now copy the cell and paste till the range either using Ctrl + C (to copy) and Ctrl + V (to paste) or select the cell and drag it to right or bottom as you require to apply.
Copy the unformatted cell values and paste only the values using (Ctrl + Alt + V) or using paste special (opens using the right mouse button.
Click Ok and you can see, Conditional formatting does all the highlighting for you.
Here are all the observational notes regarding using the formula.
Hope you understood How to highlight or Color One Record in Each Group in a Range in Excel. Explore more articles on Excel highlighting cells with formulas here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at email@example.com.
Expanding References in Excel : The ranges that expand when copied in the below cells or to right cells are called expanding references. Where do we need expanding references? Well, there were many scenarios.
Find the partial match number from data in Excel : find the substring matching cell values using the formula in Excel.
How to Highlight cells that contain specific text in Excel : Highlight cells based on the formula to find the specific text value within the cell in Excel.
Conditional formatting based on another cell value in Excel : format cells in Excel based on the condition of another cell using some criteria in Excel.
IF function and Conditional formatting in Excel : How to use IF condition in conditional formatting with formula in Excel.
Perform Conditional Formatting with formula 2016 : Learn all default features of Conditional formatting in Excel.
Conditional Formatting using VBA in Microsoft Excel : Highlight cells in the VBA based on the code in Excel.
Popular Articles :
50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.