How do I use Conditional Formatting in Sheets?
Conditional formatting is the way to go if you're tired of manually handling the formatting of your work. The way this is done is by setting up predefined Cell Rules
according to which the formatting, colour, font or border of your spreadsheet will change as you go along, based on what values you've entered. This feature is accessed via the Conditional Formatting
button in the upper part of the Spreadsheet editor. Clicking on the button will show a drop-down menu, in which among the other options are arguably the two most important ones, namely the Highlight Cell Rules
and Manage Rules
. The first one is used to create new Cell Rules, while the later to manage, edit and delete them. While the options for formatting are many and varied they all abide by three main aspects which are set up initially by accessing the Highlight Cell Rules menu, these are:
- The first thing to set up is what kind of data the Cell Rule
should apply to. For example, if the aim is to highlight specific names in a long list a certain colour the Specific Text
option should be selected. If the goal is to highlight a specific date the Dates Occurring
option would be selected, and so on.
2. Data Rules
-After you've set what kind of data the Cell Rule
should apply to it's time to set in which cases this would happen. There are several categories here, these include:
- Cell Value - This category applies for numbers and includes the Between, Not between, Equal to, Not equal to, Greater than, Less than, Greater than or equal to and Less than or equal to options. For example, setting up a rule in which all values below a specific threshold are subject to conditional formatting would be done by selecting the Less than option.
- Specific text - Applies only to text values and includes the Containing, Not containing, Beginning with and Ending with options.
- Dates Occurring - Applies only to dates and includes the Yesterday, Today, Tomorrow, Last 7 days, Last week, This week, Next week, Last month, This month and This month options.
-After the What
of the Cell Rule have been set up it's time for the How aspect of it to be defined, namely how the formatting itself would be displayed. There are several options:
- Number - Used in cases when the value itself has a specific format that needs to be used, such as currency, time, dates, percentages and so on.
- Font - This option allows you to set up different cases that would change the font, font size, font colour and effects such as bold, italic and so on.
- Border - In this case it's not the value that is formatted, but rather the cell itself. Options here include different cell borders, line styles, as well as line colours.
- Fill - Used to highlight entries a specific colour.