Adding formatting to your Excel spreadsheet makes it look professional—but manually highlighting or changing text colors can take hours if you have a large spreadsheet. And if your data changes regularly, it’s nearly impossible to do it by hand.
With conditional formatting, you can automatically apply the formats you want to a specific subset of cells. You just need to know how to tell Excel which cells you want.
Here are five ways you can use conditional formatting to make your spreadsheet look more professional.
1. Highlight Alternating Rows
This is a simple one, but highlighting every other row goes a long way toward improving the readability of your spreadsheet.
First, highlight the data you’d like to format (in most cases, this will be all of the data in a table). Then click Conditional Formatting in the Home tab of the Ribbon and select New Rule:
In the Use a formula to determine which cells to format tab, enter this formula:
=MOD(ROW(),2)=1
Then use the Format button to select the fill color you’d like for your rows.
Once you have, hit OK, and you’ll have nicely highlighted alternating rows:
In our case, we highlighted odd rows start with row #2 to preserve the fill color on our header row. To highlight even rows, change the formula to this one:
=MOD(ROW(),2)=0
You can adjust the values in the MOD equation to highlight every third row, every fourth row, or any other interval that you like. You could also create multiple highlighting rules to create highlighted rows with different colors.
2. Use Dynamic Conditional Values
In most cases, conditional formatting works with a single value. You might highlight all of the cells that have a value above 10,000, for example. But by using dynamic conditional values, you can change the rules for formatting on the fly.
Let’s look at an example. In our spreadsheet, we have a list of sales by a number of salespeople, as well as the average sales value over the course of the year:
We can use conditional formatting to highlight values that are $10,000 above or below the average. And by using dynamic values, this will work even when the average changes.
First, we’ll highlight the relevant data (this doesn’t include the salespeople’s names, so we’ll start with B2). Then head to Conditional Formatting > Highlight Cells Rules > Greater Than.
When you click on it, you’ll be asked to enter a value. Instead of entering a number, though, we’ll click on a cell to create a cell reference. In our case, we’re clicking on H3:
Now, every cell containing a value larger than the value in H3 will get highlighted. That will be true even when H3 changes.
Finally, let’s add $10,000 to that value:
We’ve selected green fill with dark green text to highlight these.
Now, to do the same with values $10,000 less than the average, do the same thing again, only select Less Than in the Conditional Formatting menu, and use ($H$3)–10000 for the value:
Now any cell that’s $10,000 above or below the average will be highlighted, no matter how the average changes. (For readability, you may want to remove the alternating row highlights for an operation like this.)
3. Highlight a Row Based on a Single Value
Highlighting a single cell is great, but what if you want to highlight a whole row? In our example of the sales records, you might want to highlight the entire row if an employee’s total sales reach a certain value.
We’ve added a column for the total annual sales by each employee. Using the standard greater-than rules would highlight only the cell that contains the value.
To highlight the entire row, we’ll need to select the entire table, from B2 through the end, then use the Conditional Formatting menu to create a new rule:
Because we’ve selected the entire table, the format will be applied to the whole row. But specifying $F2 as the reference cell means Excel will only look at that cell.
Note: the dollar sign is important here, because it tells Excel to only look in column F. There’s no dollar sign before the 2, because we want it to check out all of the rows.
Hit OK, and every salesperson who sold over $250,000 for the year has been highlighted:
You can combine this with the dynamic highlighting above to highlight rows based on automatically updating values.
4. Quickly Apply Color Scales
Color scales give you a lot of information very quickly—but they can require numerous conditional formatting rules to get right. Instead of going through that process, you can use Excel’s built-in color scale rules to get a head start.
Just select the data you want to highlight and head to Conditional Formatting > Color Scales:
Select one of the color scales from the menu, and Excel will apply it to your data.
There are several different scales that you can use, so mouse over them to get a preview of what they look like and choose the best option for your data.
