Conditional formatting in Excel: how to change cells colour based on value

Change cells colours based on value and become a spreadsheet pro! Watch our videos and examples to learn how to use conditional formatting in MS Excel.

Conditional formatting is a great way to distinguish data in your Excel table!

Learn how to create a rule in Excel so cells change colour, based on numbers or text, for example. Use conditional formatting with the IF formula or else and understand your worksheet data within seconds.

Highlight percentage of completion or the highest value in a column with this handy tool!

Ready to use cell formatting in MS Excel?

Conditional formatting: a rule created with one or multiple conditions dictates the format of a cell or its content (for Excel).

Find the conditional formatting menu in the Home tab in Excel.

In Excel, create a new rule for conditional formatting by selecting Home, Conditional Formatting then New Rule… or you can use existing templates (greater than, top 10%, etc.).

The New Formatting Rule window will appear; you can now create your own rule.

New Formatting Rule window with a 2-colour scale style.

Check the examples below to learn how to create rules for text and values.

In this example, we want to colour code cells to highlight youngest and oldest persons.

We decide to use colour scales, with cells containing the highest numbers being highlighted in red (vs white for the youngest ages).

If you want to change colours, click Conditional Formatting then Manage Rules. Select the rule you want to edit by double-clicking it or by selecting it then clicking Edit Rule…

Use conditional formatting in Excel to highlight cells containing specific words for example or client names.

In this example, we want to colour cells containing email addresses in “.org” in blue and the ones in “.com” in yellow.

Note that you can change only the text colour if you want, or mix text colour and cell colour, change text to bold, etc.

Many of our students ask how to use conditional formatting with the IF function in Excel. There are plenty of ways of doing it but here is one example of conditional formatting and IF used together.

In this example, the IF formula is =IF(F2=TRUE,”Yes”,””) for row 2, =IF(F3=TRUE,”Yes”,””) for row 3, etc.

We want “Yes” to be written in green for IF=TRUE and the cell to be coloured in red for “IF is not TRUE”.

Watch our 50 sec. video and create rules for conditional formatting within seconds in the future!

Now, our Excel table is responsive!

Check this free tutorial if you need a refresher for the IF function and click here to learn how to create checklists in Excel!

Now, you know how to find, create, edit conditional formatting in Excel and increase the level of professionalism of your Excel worksheets.

Advertising
Analytics

Categories

AllAdvertisingFormatsPlatformsKPI'sAd News
What is Programmatic Advertising?

What is Programmatic Advertising?

Understand what programmatic advertising is and how it works to increase your ROI and become a programmatic specialist.

Read More
Programmatic Advertising: What is a DSP?

What are DSPs?

A DSP is a great tool to use but what does DSP stand for in marketing? What are some Demand-Side Platforms examples? Today, we answer your questions!

Read More

Best DSPs for Programmatic Advertising

Ted Jordan shares his list of best DSPs for Programmatic Advertising so you don’t have to lose time searching the internet to find the right Demand-Side Platform for your campaigns.

Read More
Programmatic Advertising: What is a SSP?

Supply-Side Platforms (SSPs) Explained

SSPs basics in advertising: learn what Supply-Side Platforms are used for and who use them. Get familiar with more programmatic advertising technical terms.

Read More

What is O&O Advertising?

Discover one more important jargon term used in Programmatic Advertising: O&O advertising. Get familiar with O&O formats and advantages.

Read More
Programmatic: Pixels in Advertising 101

Advertising pixels: types, pros and best practices

Learn the basics of Programmatic Advertising with Ted Jordan! Today, we explain what a pixel is in advertising with simple words and expertise. Ready to learn?

Read More

Categories

AllFunctionsToolsShortcut Keys

How to change shapes in Excel column charts

Learn how to easily change the columns shape in your Excel charts so your colleagues and customers will remember your impacting graphs.

Read More

How to use the LEFT, RIGHT and MID functions

Learn how to extract text in Excel with the LEFT, RIGHT and MID functions. Plenty of examples to learn from and functions are explained with simple words. If you start...

Read More

Excel IF statement: how to use it for 3 conditions

Learn how to use the Excel IF statement with multiple conditions and combine it with OR and/or AND functions!

Read More
Navigating with Tab, Enter, and Shift Key on Excel.

How to move between cells when arrow keys are not working (Excel)

Learn how to move between Excel cells without using your mouse or arrow keys. Discover more shortcuts and tricks below!

Read More

How to use the IFERROR function (Excel + Google Sheets)

Discover how to use IFERROR in Excel or in Google Sheets in 4 easy steps! No more errors on your documents or dashboards with IFERROR.

Read More
Cope Paste Options on excel with shortcut keys

Copy paste values like a pro! (Excel+Google Sheets)

Copy and paste values (not formulas) using shortcut keys in Excel or Google Sheets! Learn how spreadsheets professionals do it within seconds and become one of them. Save time now...

Read More