Conditional Formatting: How to make a dynamic spreadsheet in Excel

If you have downloaded and used enough excel templates, you have probably come across some spreadsheets that have the cells changing colour when different things happen.

Although this may seem extremely fancy, it doesn’t involve knowing how to code or really any super technical knowledge of Excel. There is a tool built right into Excel which allows you to do this. It lives in the Home tab of the ribbon, and is called Conditional Formatting. Here’s how it works:

You set up a series of rules for a cell. Linked with these rules, you set up a format that you want for the cell. For a simple, albeit not uncommon example, imagine setting up a cell that goes red when it contains a negative number, green when it contains a positive number, and stays white when the number is neither positive nor negative (or it equals zero.)

There are limitless ways you can use this tool, so instead of going through all of the different rules, let’s build an example.

Download the excel worksheets at the bottom of the blog post to see examples and try it for yourself.

We are going to build an example of a DCF sensitivity table, and we will highlight cells green if they are above the current share price, and red if they are below the current share price.

Note: The table is not representative of how an actual DCF Model would work. It just has numbers in it to experiment with conditional formatting. For information on how the DCF works, see THIS BLOG POST.

Setting this up is quite easy. All you have to do, is highlight the cells that contain the stock prices, and go to the conditional formatting menu.

You then click “Highlight Cell Rules > Less Than” and then select the cell that contains the current stock price. Then you choose the formatting. Excel has a handy preset format of a red fill with dark red text, and a green fill with dark green text. Choose the red one. You repeat this process for the greater than, choosing the green option this time, and you will see the result is that everything above the current stock price is green, and everything below the current stock price is red. Everything equal to the current stock price has no formatting.

We will do one more example. We are going to turn THIS list of stock prices into a list from which we can see a trend. This uses the same rules as before, except instead of using a set cell this time, what you do is reference the cell below. Remember to remove the anchoring for the reference cell.

Do this for the bottom left cell, and then copy and paste the format into the other cells. You do this by copying the cell using Ctrl-C, and you paste the formatting using Alt-E-S-T. Now we can see when a stock price moves up or down compared to the previous period.

Formatted Worksheet

Non-Formatted Worksheet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.