Google Sheets - Conditional Formatting

Summary

This video explains the basics of conditional formatting, a feature that allows you to format cells based on specific conditions. It demonstrates how to highlight cells with values above $12,000, apply multiple formatting rules, and dynamically adjust formatting when data changes. The tutorial also covers advanced techniques like using cell references, applying color scales, and creating rules based on formulas or row-by-row comparisons. Overall, the video serves as an introduction to conditional formatting, offering tips for efficient data visualization.

Takeaways

• 🖥️ Conditional formatting is used to apply specific formatting to cells based on certain conditions.
• 🔍 To apply conditional formatting, select the area, go to 'Format', and choose 'Conditional Formatting'.
• 🎨 You can highlight cells based on various conditions such as being greater than a specific value, empty, or not empty.
• 📈 The script demonstrates how to highlight numbers above $12,000 using 'greater than' as the condition.
• 🌟 Conditional formatting is dynamic, meaning it updates automatically when the cell values change.
• 🗑️ To remove conditional formatting, go back to 'Conditional Formatting' and click the trash icon next to the rule.
• 🚫 You cannot apply multiple conditional formats to the same area; it will only show the highest priority rule.
• ✅ The script shows how to use cell references in conditional formatting to make it dynamic, such as referencing a cell with a value that changes.
• 📊 It's possible to apply conditional formatting based on formulas, not just static values, which allows for more complex and flexible rules.
• 📈 The video covers using color scales to color-code cells based on their values relative to the highest and lowest values in a range.
• 🔄 The order of conditional formatting rules matters, as it determines which rule takes precedence when multiple rules are applied to the same range.

Q & A

What is conditional formatting in Excel?

Conditional formatting in Excel is a feature that allows you to apply specific formatting to cells based on certain conditions, such as values being above or below a certain threshold.

How do you apply conditional formatting to highlight cells above a certain value?

To highlight cells above a certain value, you select the range of cells, go to the 'Format' menu, choose 'Conditional Formatting', select 'Greater Than', and enter the value to compare against.

What are the different types of conditions available in conditional formatting?

There are many conditions available in conditional formatting, including cell values being greater than, less than, equal to, between, not between, and even formatting based on specific text or dates.

How can you dynamically change the value that conditional formatting is based on?

You can dynamically change the value that conditional formatting is based on by using a cell reference instead of a static value. This way, if the value in the referenced cell changes, the conditional formatting will update accordingly.

What is the purpose of the 'greater than or equal to' option in conditional formatting?

The 'greater than or equal to' option in conditional formatting allows you to highlight cells that are not only greater than a certain value but also equal to that value.

Can you apply multiple conditional formatting rules to the same range of cells?

Yes, you can apply multiple conditional formatting rules to the same range of cells. The order in which the rules are applied determines which formatting takes precedence when there are conflicts.

How do you remove conditional formatting from a cell or range of cells?

To remove conditional formatting, you can go back to 'Conditional Formatting' in the 'Format' menu, select the rule you want to remove, and click the trash icon to delete it.

What is a color scale in conditional formatting and how is it used?

A color scale in conditional formatting is a gradient of colors applied to a range of cells based on their values. It can be used to visually represent the distribution of data, with the lowest values having one color and the highest values having another.

How can you use formulas in conditional formatting to compare values in different cells?

You can use formulas in conditional formatting to compare values in different cells by using cell references in the formula field. For example, to highlight cells where the actual number is greater than the expected number, you would use a formula like '=A2 > B2'.

What is the significance of using absolute and relative cell references in conditional formatting?

Absolute cell references (using $ signs) are used when you want the same cell to be referenced regardless of where the formatting is applied. Relative cell references are used when you want the reference to change based on the position of the cell in the range being formatted.

Outlines

00:00

🎨 Introduction to Conditional Formatting

This paragraph introduces the concept of conditional formatting in spreadsheets, which is a feature that allows users to apply specific formatting to cells based on certain conditions. The video demonstrates how to highlight cells with values above $12,000 by selecting the range, navigating to the 'Conditional Formatting' option, and choosing the 'greater than' condition with a value of 12,000. It also explains how to customize the formatting style, such as background color, font color, and boldness. The dynamic nature of conditional formatting is emphasized, where changes in cell values automatically update the formatting. The paragraph concludes with instructions on how to remove conditional formatting and the concept of applying multiple conditions to the same range, which are applied in the order they were created.

05:01

🔢 Advanced Conditional Formatting Techniques

The second paragraph delves into more advanced uses of conditional formatting, such as referencing a cell value instead of hard-coding a number. It illustrates how to create a rule that formats cells based on a value in another cell (e.g., highlighting numbers greater than the value in cell F1). The importance of using absolute cell references (e.g., $F$1) is highlighted to ensure the rule applies correctly throughout the selected range. The paragraph also explores using formulas for conditional formatting, like highlighting values above the average of a set of numbers. It discusses the option to create color scales based on cell values and how to adjust the priority of multiple conditional formats applied to the same range.

10:02

📊 Applying Conditional Formatting to Data Comparisons

The final paragraph showcases how to use conditional formatting for comparing data, such as highlighting cells where actual numbers exceed expected numbers. It explains the process of setting up a rule that compares values cell-by-cell (e.g., comparing the value in column B to the value in column A for each row). The paragraph also covers the use of color scales to represent data distribution, with options to customize the colors for minimum, midpoint, and maximum values. The video emphasizes the flexibility of conditional formatting, allowing for dynamic updates as data changes and the ability to create custom rules based on cell references and formulas.

Keywords

💡Conditional Formatting

Conditional formatting is a feature in spreadsheet applications that allows cells to be automatically formatted based on the cell's value or the result of a formula. In the video, the concept is central as it demonstrates how to apply different formatting styles to cells that meet specific criteria, such as highlighting numbers above $12,000. This feature is crucial for visualizing data patterns and making data analysis more intuitive.

💡Formatting Rules

Formatting rules in the context of conditional formatting define the conditions under which a cell's appearance changes. The video explains how to set up these rules, such as 'greater than' a certain value, and apply them to a range of cells. This is showcased when the presenter sets a rule to highlight cells with values above $12,000, illustrating how formatting rules can be tailored to meet specific analytical needs.

💡Cell References

Cell references are used to point to specific cells within a spreadsheet. The video script mentions using cell references in conditional formatting to tie the formatting condition to a cell's value. For instance, the presenter refers to cell F1 when setting a condition to highlight values above what is stored in that cell, demonstrating the dynamic nature of conditional formatting.

💡Absolute Reference

An absolute reference in spreadsheets is a type of cell reference that does not change when the formula is copied to another cell. The video explains the importance of using an absolute reference (e.g., $F$1) when setting up conditional formatting that should remain tied to a specific cell value regardless of where it's applied. This is crucial for maintaining the integrity of conditional formatting across different parts of a spreadsheet.

💡Dynamic Application

The term 'dynamic application' in the video refers to the way conditional formatting automatically updates when the underlying data changes. The script illustrates this by showing that if a cell's value changes to meet the conditional format's criteria, the formatting is applied or removed accordingly. This feature is key to keeping spreadsheets up-to-date and visually representing the most current data.

💡Color Scales

Color scales are a type of conditional formatting that applies a gradient of colors across a range of cells based on their values relative to the highest and lowest values in the range. In the video, the presenter uses color scales to visually represent data distribution, such as highlighting percentages from red (lowest) to green (highest), providing a quick visual summary of data trends.

💡Formulas

Formulas in spreadsheets are used to perform calculations and can also be used within conditional formatting to define the conditions for formatting. The video script includes examples of using formulas to calculate averages and to compare actual numbers with expected numbers, showcasing the versatility of formulas in creating complex and meaningful conditional formatting rules.

💡Highlighting

Highlighting in the context of the video refers to the visual emphasis placed on cells that meet certain conditions through the use of colors or font styles. The script describes how to use conditional formatting to highlight cells above a certain value, such as $12,000, to make them stand out. This is a common use of conditional formatting to draw attention to important data points.

💡Data Visualization

Data visualization in the video is achieved through the use of conditional formatting to represent data in a visually understandable format. By applying different colors and styles to cells based on their values, the presenter enhances the spreadsheet's ability to convey information at a glance, which is a fundamental aspect of data analysis and presentation.

💡Comparison Operators

Comparison operators are used in formulas to compare values and return true or false based on the comparison. In the video, the presenter uses comparison operators like 'greater than' to set conditions for conditional formatting. For example, setting a rule to highlight cells where the actual number is greater than the expected number, demonstrating how these operators are integral to creating conditional formatting rules.

Highlights

Introduction to conditional formatting and its purpose of formatting cells based on a condition.

How to apply conditional formatting to highlight numbers above a specific value, such as $12,000.

Accessing the conditional formatting feature through the 'Format' menu.

Explanation of the default range selection for conditional formatting.

Selection of the 'greater than' condition to highlight values above a specified amount.

Demonstration of real-time changes in formatting as conditions are set.

Customization of the cell's appearance through background color, font color, and bold styling.

Understanding that conditional formatting is dynamically applied and updates as cell values change.

Instructions on how to remove conditional formatting if needed.

Capability to apply multiple conditional formats to the same range with different conditions.

Importance of the order of conditional formats and how it affects the final appearance.

Tying conditional formatting to a cell value using a cell reference for dynamic changes.

Using absolute cell references to ensure conditional formatting works correctly with cell references.

Example of applying conditional formatting based on a calculated average value.

Explanation of how to use formulas in conditional formatting for dynamic results.

Demonstration of conditional formatting for values between two specified numbers.

Use of color scales in conditional formatting to represent data visually.

Adjusting color scales to reflect different data points, such as minimum, midpoint, and maximum.

Applying conditional formatting to compare actual numbers against expected numbers in a side-by-side comparison.

Final thoughts and a call to action for viewers to subscribe for more content.

Transcripts

alright so in this video let's talk

about conditional formatting so

conditional formatting is when you want

to format things based on a condition

pretty much the way it sounds so for

example we might want to highlight every

number in this area that's above $12,000

right so to do something like that what

do you do you just select the area go

under format and scroll down to

conditional formatting we click on

conditional formatting we get this

screen so this screen shows us the range

to which we're applying conditional

formatting which is by default what we

had selected so that should we find if

we did our selection right so if I

scroll down here I have different

conditions so tons of conditions to

choose from if I open this there is a

big list we could highlight empty cells

or cells that are not empty in this

particular case we're trying to

highlight every number that's above

12,000 so I'm gonna scroll down until I

find this option greater than so we can

do greater than greater than or equal to

if we want to include that number in our

highlighted numbers that 12,000 exactly

will do greater than or equal to I'm

just gonna do greater than so I'll do

this and then the value and here I'm

gonna type 12,000 now you can already

see what's happening in the background

so we have C green for 12,000 and the

rest stays what it is now then you

select the formatting so you just choose

how do you want that cell to look so if

you don't want to be green you want a

different background color you just go

ahead and choose that if you want a

different font color you go ahead and

choose your different font colors you

can also make it bold as you can see or

basically just apply some styling to

your cell so once we're done we hit done

and we have successfully applied

conditional formatting so if I X out of

the screen and go here we'll see every

cell above 12,000 is highlighted in this

formatting now conditional formatting is

dynamically applied to your cells what

that really mean

uses that if this number changes to

something that's less than 12,000 it's

just gonna lose that formatting I'm

gonna undo that command Z and the same

way if we have a cell that gets a number

that's greater than 12,000 we will also

apply that formatting dynamically to the

range that we used so I'm going to undo

that so that's basics of conditional

formatting basically highlight the array

1 conditional formatting for format

conditional formatting and start looking

for the option you want now if you

decide you want to remove conditional

formatting you pretty much do the same

thing so if we go back to conditional

formatting see the rule we did apply

shows up if you want to change it maybe

you want to change the formatting or

something like that you go here and

click on this and start changing

whatever you need to change I'm gonna X

out of that otherwise if you just want

to get rid of it you go back to

conditional formatting and you can just

highlight the rule and sit is little

like trash icon we click on that and the

conditional formatting is gone we can't

apply multiple conditional formatting to

the same area so let me show how that's

gonna work let's say I do conditional

formatting for all numbers again above

12,000 I'll do this color maybe I'll

change it to blue the way we had that's

fine done and then I'm gonna do another

rule so I'm gonna add another rule to

the same range and again choose greater

than and this time I'm gonna choose to

apply that to every cell that's greater

than 11,000 I hit done now we have two

conditional formatting applied to the

same range of numbers and you can see

everything about 12,000 is blue and

everything above 11,000 it's yellow now

the reason it works this way is because

these conditional formatting I applied

them in that same particular order that

I did

now if I didn't do them that particular

order what could have happened is that

this yellow could have appeared above

the other ones so let me just show you

what I mean by that so see here there is

this order you can change so if you

rolled over you'll see this with little

dots I can drag this and put it below

and now because this blue condition

appears as a lower priority in our list

it's yellow that overrides the blue and

we cannot really see the blue happening

so you sometimes might want to just

change this orders by dragging them to

choose the priority which one is the top

level the second level third level and

so on and you can keep adding more if

necessary I'm gonna delete these two

together another interesting thing about

conditional formatting is that instead

of hard-coding our number we can also

apply conditional formatting with a cell

reference so let me show you what I mean

so let's say I want to go here and type

a number let's say 12,000 maybe I'll

format it to make it look good that

really doesn't matter now what I'm gonna

do I'm gonna select these cells I'm

gonna go back to my conditional

formatting and then again apply the rule

I'm gonna say greater than and I'm gonna

select the value so this is where I used

to type 12,000 now instead what I'm

gonna do I'm gonna refer to this cell

this cell is f1 that's where the 12,000

number is so I'm gonna say it equals 2

so it has to be formula f1 and what I'm

gonna do now you'll see that if you look

at a background this isn't working the

way you would have thought it should so

it's not really doing f1 it's doing

something so it's not really

highlighting the right thing so to make

this work we have to do an absolute

reference so we'll do dollar F dollar 1

and that way now you'll see every number

above 12,000 is highlighted so I'm gonna

choose some styling here hit done X out

of this so what happened right now I

tied my conditional formatting to the

cell value what that means is that

if I go back here and change the cell

value to a different value let's say I

do

13,000 see my conditional formatting

will dynamically change because now

we're highlighting everything above this

13,000 so now we can tie it to a cell

now that opens a lot of possibilities

maybe we want to just give our

formatting to everything that's above

average so if we wanted to do that we

would just calculate an average value

here so we'll do average of all of this

and that should calculate the average

and then we'll highlight everything

that's greater than the average so

basically we'll color code everything

that's above average now if you want to

do above average without actually having

this cell with the value of that well

you could do you could just use that

formula that gives you the average I'm

gonna hit escape in your conditional

formatting so if I go back and edit that

conditional formatting now instead of

tying that to this value in f1 I can say

this is gonna be my formula and you have

to remember that these need to be locked

so I have to lock to absolute values so

if I do this that will still work that

will give us everything about average it

done but now it's not really tied to the

cell so now as this values change our

average will change and we'll highlight

everything above average so for example

if this number goes really high see now

our average goes a lot higher so the

only number that's above average is this

one on do command Z that gets us back to

this so basically the idea of this is

that we can use any formula and the

result of that formula could be now tied

to our conditional formatting I'm gonna

remove that so let me also do an example

of like between type of things you could

have two numbers and you could say I

want everything between this two numbers

to be highlighted

in that color so let's do percentages so

I'm gonna do ten percent and fifty

percent and then I'm gonna select this

area go under format conditional

formatting and then we'll choose the

option be tween and then the value the

first one is going to be a formula I'm

going to say equals to this which is f1

and it needs to be an absolute reference

so dollar F dollar one and then I'm

gonna say the second one is dollar F

dollar two which is basically a

reference to this cell and choose format

Inc whatever that's gonna be I'm just

gonna leave it default done and now we

have everything between this numbers

highlighted in that particular color

same idea and again this could be a

formula that calculates some sort of

percentage and the key thing here it can

be any formula right so I'm gonna delete

that that's gone I'm gonna also go back

and delete my conditional formatting

from here I'm gonna delete all of that

from here that's gone so looking good so

taking this to the next level let me go

to this other tab let's say we have

something like this we have our expected

numbers we have our actual numbers what

we want to happen if our actual number

is above our expected number we want to

highlight that in red so now it's not

just one number I'm tying this to I want

this to basically work row by row like

this so to do something like that what

I'm gonna do I'm gonna select the area

that's gonna get the formatting and I'm

gonna go in there format and I'm gonna

do conditional formatting so here I'm

gonna scroll down and choose greater

than option I want to make sure that

this happens when the value of that cell

is greater than the cell on the left so

what I'm gonna do as a value I'm gonna

do a formula equals b2 how

this time unlike the previous time I'm

not going to actually lock as an

absolute reference because if I lock

that as an absolute reference we will

basically just compare if every single

number here is greater than this first

number in a list what I want to do I

want to do robot role comparison so what

I do I just go and select b2 which is

the same value in the first cell in this

highlighted area so when I highlighted

this range see that's in the second row

so therefore when I do my comparison I

do it with that second row in the column

that I need that comparison to be done

and then I leave this as a relative

reference so now I can just give this

some sort of color so now if I go here

and this number is actually lower let's

say then this number let's type

something like that see that goes like

that so if I do 10,000 that loses

because this is less than that

or 11,000 should have the same effect

it's still less than this so with

conditional formatting you sometimes

also might want to do some color scales

so if I select all of these percentages

here on the right I go on the format

conditional formatting and instead of a

single color which is what I've been

doing so far I'm gonna move to this

color scales so with this color scales

we can now basically just do like the

lowest value with one color and the

highest value with a different color

right you can see we have this preview

so see the minimum now gets the green

color and a maximum gets whatever this

white color is so let's say if this

percentage is positive you want to make

that green right in that case you would

switch this to whatever you want so

let's say green and then a lower numbers

you would do let's say

read so now we go from red to green with

a color scale and red would be the

lowest number and the greenest number

would be the highest number here in the

list right and you might want to play

with this to see what you like and then

you could also do like a midpoint if you

want it the midpoint for you is a number

which is let's say zero and zero would

be the midpoint and you could choose

what color you want for your midpoints

and kind of decide how the color scale

works from that point on or in this

particular case when our actual is

larger than our expected that's actually

for us let's assume it's a bad thing so

we're gonna switch this green color

we'll go here or maybe we'll do blue for

this and for this we'll go red and then

you can decide whether you wanted

midpoint or not so I can do none and go

with this one for me midpoint kind of

makes sense I'll do something like that

and I'll say zero is a good midpoint for

me so yellow or we could go like green

or something I don't know and that's our

color scales so also useful sometimes

done close here we go so that pretty

much covers the basics of conditional

formatting from this point on you might

want to dive into like conditional

formatting good formulas which we'll do

in a different video but for now thanks

for watching please subscribe and I'll

see you next one