On your computer, open a spreadsheet in Google Sheets.
Select the cells you want to apply format rules to.
Click FormatConditional formatting. A toolbar will open to the right.
Create a rule.
Single color: Under "Format cells if," choose the condition that you want to trigger the rule. Under "Formatting style, choose what the cell will look like when conditions are met.
Color scale: Under "Preview," select the color scale. Then, choose a minimum and maximum value, and an optional midpoint value. To choose the value category, click the Down arrow .
You can use wildcard characters to match multiple expressions. Wildcard characters can be used with the "Text contains" or "Text does not contain" fields while formatting.
To match any single character, use a question mark (?). For example, a text rule containing "a?c" would format cells with "abc," but not "ac" or "abbc."
To match zero (0) or more characters, use an asterisk (*) . For example, a text rule containing "a*c" would format cells with "abc," "ac," and "abbc" but not "ab" or "ca."
To match a question mark or asterisk in text, you can escape the wildcard characters by adding a tilde (~) in front of them. For example, a text rule containing "a~?c" would format cells with "a?c" but not "abc" or "a~?c."
Common Excel Errors:
#####: Excel is trying to display a number, but the column is too narrow to show the number in full. This is an easy fix.
The most common solution is to make the column wider. Double-click on the right column border at the top of the worksheet. This will automatically resize the column.
Drag the column to a width that enables you to see the full value in that cell.
Is a negative value stored in date format? No matter how wide the column is, the ###### error will be displayed, as negative date values are considered invalid. Change the number format from Date to another format, such as General.
#DIV/0: The formula is trying to divide by zero or an empty cell. It could be that the divisor (denominator) in the formula references a cell which is currently empty, or which returns a value of zero. Or, in the case of an AVERAGEIF formula, none of the cells within the range satisfy the criteria.
Correct the divisor (denominator), or delete the formula
#N/A: The formula cannot find a value or match in the data. The #N/A error most often occurs when using a lookup function. This error indicates that the value you searched for is not available; that is, it wasn’t found in the source data.
Could be result of a formatting mismatch. In the example below, though the ID Number 6055 does exist in the source data, the value in cell A4 has been formatted as text, while the lookup value in cell E2 is formatted as a number. Excel concludes that they are not the same value. Therefore the lookup value was not found.You can use the Text Trim function to remove irregular spaces. (Select column/Text/Trim)
#NAME?: The formula contains a name that Excel does not recognize.
Excel does not recognize the name of the function (built-in formula) you are trying to use. This is often the result of an error in spelling a function name or mistakenly placing an equal sign in front of the text.
#NULL!: The formula specifies an intersection of two areas that do not intersect. Excel does not recognize the name of the function (built-in formula) you are trying to use. This is often the result of an error in spelling a function name or mistakenly placing an equal sign in front of the text.
The most common reason for this is that you’ve typed a space between two cell references where you should have entered a comma to separate them, or a colon to separate the beginning of a range from its end.
#NUM!: The formula has a numerical problem, such as a negative number raised to a fractional power. The #NUM! error is usually displayed when the number is too large or too small, or when calculation is not possible. The largest number that Excel can accommodate is 10308.
#REF!: The formula refers to a cell that is not valid, such as a deleted cell or a moved worksheet. #REF! error in Excel is displayed when the reference to a cell is invalid. This usually happens when a formula is built, and then a cell, row, or column which is referenced in that formula gets deleted.
A similar message will be returned if you copy and paste a formula to a location that makes the relative reference invalid.
Possible solutions:
As a first resolution, try pressing Ctrl+Z on your keyboard to undo the last action. This works fine if you just accidentally deleted a reference needed by the formula and want to restore your sheet to what it was before. By default, Excel allows you to undo up to 100 actions to an unsaved workbook.
Using absolute cell references or named ranges will ensure that the original references are not shifted. Absolute references are made by placing dollar signs ($) before column names and row numbers.
Check for, and open any other workbooks referenced in the formula.
Double-check the syntax rules for the function you are attempting to use.#VALUE!: The formula uses the wrong type of argument or operand, such as text instead of a number.
If you’d like some “at your own pace” video training, it is available online through Microsoft.
Create a Yes or No dropdown box. (or basically any list)
Click in the Cell & go to Data/Data Validation/Data Validation
In Settings, choose LIST and then for source, type Yes, No. Ok.
If you want other choices for your drop down list, you can type anything in (separated by commas). This then creates a drop down list that you can copy down.
Conditional formatting. A toolbar will open to the right.
.
You can use the Text Trim function to remove irregular spaces. (Select column/Text/Trim)
