Conditionally formatting if several cells are empty (no numbers in the entire spreadsheet)

Question

I created a table in Excel and am trying to use Conditional Formatting to select a cell or row if all or all of the cells in the last four columns are empty. My columns consist of the names account , store manager , city , state , visit 1 , visit 2 , visit 3 and visit 4 .

When you visit an account, notes are recorded in the Visit box, and if the account does not need a visit, X is placed in each column of the Visit (some accounts need one visit, some two, some all four).

Is it possible for the account name and / or manager name to be highlighted when any visits are left blank, indicating that they need to configure the visit that is needed?

I tried the instructions below, but it didn't seem to work for the range of information I was looking for.

---

• Open the Conditional Formatting Rules Manager (Conditional Formatting-> Manage Rules).
• Click New Rule and select Use Formula to determine which cells to format.
• In the "Format where this formula is true:" field, enter the cell that you want to check if empty.
• Place a dollar sign in front of the letter of the cell reference to make it affect only this row, not the entire table or just the cell.
• Type = "" at the end of the field to check if the cell is empty.
• Click "Format ..." and go to the "Fill" tab to select a color for fill the line if true and click "OK".
• Click OK to close the New Rule dialog box.
• Change the "Applies to" value to the rule you just created in the area of ​​the entire table to apply the rule to it. (If your table has a reference name, you can enter it here)
• Click OK to close the Conditional Formatting Rules Manager.

Answer 1

How about just> Format only cells that contain - select Forms from the drop-down list

Answer 2

• Select columns A: H with A1 as the active cell.
• Open Home ► Styles ► Conditional Formatting ► New Rule.
• Select Use a formula to determine which cells should be formatted , and enter one of the following number 1 formulas in the format values ​​where this formula is true: text box.
  • To highlight the columns of the Account and Store Manager when one of the four dates is empty:
    =AND(LEN($A1), COLUMN()<3, COUNTBLANK($E1:$H1))
  • To highlight an account, storage manager and empty date fields when one of the four dates is empty:
    =AND(LEN($A1), OR(COLUMN()<3, AND(COLUMN()>4, COUNTBLANK(A1))), COUNTBLANK($E1:$H1))
• Click [Format] and select the Fill cell.
• Click [OK] to accept the formatting, and then [OK] again to create a new rule. In both cases, Refers to: Refers to =$A:$H

The results should be similar to the following.

---

_{CO The COUNTBLANK function was introduced in Excel 2007. It will count both true spaces and zero-length lines left by formulas (for example, "" ).}

Answer 3

The steps you took are not suitable, because the cell you want to format is not a trigger cell (presumably it will usually not be empty). In your case, you want the formatting applied to one set of cells in accordance with the status of the other other cells. I suggest with the data layout as shown in the figure (and thanks to @xQbert for running the appropriate formula) you select ColumnA and:

HOME> Styles - Conditional Formatting, New Rule ... Use a formula to determine which cells should be formatted, and Format values ​​where this formula is true::

 =AND(LEN(E1)*LEN(F1)*LEN(G1)*LEN(H1)=0,NOT(ISBLANK(A1))) 

Format ... , select formatting, OK, OK.

where I filled the yellow cells that trigger the result of the red fill.

Answer 4

If you put a dollar sign in front of the letter, you will only affect the column, not the row. If you want this to affect only the line, put the dollar before the number.

You can use = isblank () rather than = ""

I am also confused by your comment "no values ​​in the whole spreadsheet - just text" - text is a value.

Another hint - excel is in the habit of rewriting rules - I don't know how many rules I wrote, just to find that excel changed the values ​​in the "apply to" or "entry" fields.

If you could post an example, I will revise the answer. Conditional formatting is very subtle.