How to Automatically Format Excel Cells

When I was working in the hospital, I would receive Excel data weekly that showed details on our quality scores across different departments. I am sad to admit I used to spend hours manually highlighting data that was either above or below certain thresholds. When I moved from the hospital into the IT world, I learned about an amazing feature in Excel I could have been using to save time. In this post, we covered how to copy formats from one cell to another, but there is another tool that can automatically format data in Excel based on the contents in the cells themselves.

Conditional Formatting

Excel has an option called Conditional Formatting, which is a powerful tool that automatically updates the format of cells based on what data is inside them. This singular feature would have saved me many hours formatting spreadsheets to highlight data, had I known about it at the time.

How to Use

The Conditional Formatting option is on the Home ribbon next to the general cell formatting options:

There are a number of ways you can highlight cells based on the data contained, including:

Greater than
Less than
Between
Equal to
Other options around text, date, and duplicate values

Overall, the tool can handle almost all automatic formatting needs you might have. After you select the type of logic you want to use, you are able to plug in the actual values and select the format you want the cell to have.

Excel provides good formats for use, like red, green, and yellow. I recommend sticking with these standard options, as they do a good job of highlighting these cells for the user. However, there is the option to create your own formats if you so desire.

One word of caution

When you click on a cell, you can see what data is in the cell by looking at the formula bar. However, there is no easy way to see if a cell has auto-formatting rules in place. My recommendation is to make sure that when applying these rules, you apply them to an entire column afteryou are finished with the workbook. If you do it before you have finished entering the data, there is a chance you could add a new row that will not have these auto-formatting rules.

This is a powerful tool in Excel that many people do not know about. I make sure to think about using it anytime there is a need to highlight cells that meet certain criteria before I start trying to highlight them by hand. Have Excel do the work for you!