How To Use Excel Conditional Formatting And More Advanced Formatting Methods

Excel function tutorials, Excel macros

It seems simple enough, you need to format according to specific rules. Why? Generally to easily spot data that fits a specific criteria. I’ll use a very common example. Suppose that you have a list of stocks and want to compare their current price to yesterday’s close in %. Then, you would like to easily spot the stocks that displayed gains from those that didn’t. Here is the original data:

In order to make it easier, I would like to have all stocks that show gains in green and others in red. The first option would be to do it by hand. I could sort the stocks by Gain/Loss and then manually adjust the color:

Manual Formatting Adjustments

I do get the result that I want although I would need to do this exercise every single time that I update the prices which leads to lost time, and risk of error. It’s clearly not an ideal situation. What else could be done?

Conditional Formatting

In this case, I can use a criteria that will apply to a range of cells and will determine their format. Here is how to do it:

#1-Select the range of cells and then select: format/conditional formatting

#2-Select the rules you would like:

The result as you can see below is great

Not only that, any time the prices are changed, the Gain/Loss number will update automatically. It’s a perfect solution right? Not quite. While conditional formatting is great, it does have some limitations that sometimes make it insufficient. Suppose that you either need to or want to make the entire line of any stock that is either +5% or -5% appear in the same format, to make it even more obvious. Seems simple enough but that is not possible with conditional formatting. You need a third way.

Formatting Using Macros

If you are not comfortable creating your own macros, I recommend that you take a look at our excel macro introduction. As we discussed, it’s always easier to start by “recording” a macro and then modifying it when you’re not used to doing so. In our case, let’s simply take a look at the code to change an entire line to green and start from there. So I record a macro, and here is the result and code:

As you can see the current macro simply changes the color of line #2. In reality, I want the macro to go through each line and change the background color for lines that have a gain better than 5% (0.05). So let’s modify the macro. I will also add 2 lines to remove any background color when starting.

As you can see, the code is still fairly simple and could be modified but it gives me much more flexibility and could be used to format based on almost any condition! Hopefully this helps you out. I added a button and as always, you can download the spreadsheet here to check for yourself


Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss