Table Excel: Applying Conditional Formatting to a New Column
In this article, we will discuss how to create a new column in Excel and apply conditional formatting to it, using a formula. This technique is particularly useful when dealing with large datasets, where you want to highlight specific values in a new column based on conditions in existing columns.
Creating a New Column
Let's assume we have an existing table with data as shown below:
data = {
"Name": ["John", "Mike", "Sara", "Tom"],
"Age": [25, 30, 28, 32],
"Salary": [5000, 6000, 5500, 7000]
}
Now, let's add a new column named "Status" to our table:
data.Status = [];
for (let i = 0; i < data.Name.length; i++) {
if (data.Age[i] > 30) {
data.Status[i] = "Senior";
} else {
data.Status[i] = "Junior";
}
}
Applying Conditional Formatting
To apply conditional formatting to the new "Status" column, follow these steps:
- Select the cells in the "Status" column.
- Go to the "Home" tab in the Excel ribbon and click on "Conditional Formatting" in the "Styles" group.
- Select "Highlight Cell Rules" and then "Conditional Format Rules Manager" from the dropdown menu.
- Click on "New Rule" in the "Conditional Format Rules Manager" dialog box.
- Select "Format cells that do not contain" in the "Select a Rule Type" section.
- In the "Format" tab, choose the desired formatting for the cells you want to highlight (e.g., fill color, font color, etc.).
- Go back to the "New Format Rule" dialog box and click on "Format values where this formula is true".
- Enter the formula to define the condition for highlighting the cells (e.g., "=IF(B2="Senior",TRUE,FALSE)"). Make sure to replace "B2" with the cell reference to the corresponding value in the "Status" column.
- Click "OK" to apply the conditional formatting.
In this article, we learned how to create a new column in Excel and apply conditional formatting to it using a formula. This technique can be applied to large datasets, where you want to highlight specific values in a new column based on conditions in existing columns.
For further reading, check out these resources: