Google Sheets - Conditional Formatting
Summary
TLDRThis 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
🎨 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.
🔢 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.
📊 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.
Mindmap
Keywords
💡Conditional Formatting
💡Formatting Rules
💡Cell References
💡Absolute Reference
💡Dynamic Application
💡Color Scales
💡Formulas
💡Highlighting
💡Data Visualization
💡Comparison Operators
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
浏览更多相关视频
Excel Conditional Formatting in Depth
SAP ABAP Eğitimi 55 - OO ALV Cell Style
No More DAX? Power BI’s NEW Feature Explained (File Included)
Excel Tutorial for Beginners | Excel Made Easy
Smartsheet Tutorial: How to Create Automations, Build Workflows, & Set Up Conditional Formatting
Resource Allocation and planning using Excel and Pivot Tables With Demo | Planning Excel Template
5.0 / 5 (0 votes)