New answers tagged

0

Applied to range D1 (or D1:D if you want the following custom CF formula to apply to the entire Column D): =B1+C1=0 Format as desired (i.e., red fill).


0

One quick way is to select the entire column you want un-formatted and choose from the main menu Format > Clear formatting. The shortcut key combination is the select the entire column you want unformatted and then hit Ctrl+\ (i.e., control and backslash). Another fairly quick way is to select an entire unformatted column, click the paintbrush tool in the ...


0

Use a custom formula based on countblank Green = =countblank(A3:D3) = 4 Orange = =countblank(A3:D3) <>4 Rules Outcome: Green/all blank Outcome: Orange/at least one cell not blank


0

You want to create a new Conditional Formatting rule and make that rule the first rule without reordering by dragging the rule up in the list. This answer is based on a short script that: defines the new rule using the options in Class ConditionalFormatRuleBuilder. uses the Javascript unshift to add the new rule to the beginning of the rules list. function ...


0

You mentioned: Highlight order date if it is more than a week old, and there is nothing in the cell for the receive date Please use the following rule: =AND(G2<>"",G2<TODAY()-7,ISBLANK(H2))


2

Addressing strictly what you've requested in your post, I added a sheet ("Erik Help") with a custom conditional formatting rule applied to Column A: =AND(LEN(A1),ISERROR(REGEXEXTRACT(A1,"^\d{4}\s\d{3}\s\d{3}$"))) This will highlight any entries in Column A that are not in the format "#### ### ###". You can view this rule by ...


3

You mentioned How can I use a filter or conditional formatting to find all numbers that are NOT in this format (#### ### ###). Please use the following conditional-formatting rule =IF(A1<>"",(IF(ISNUMBER(A1),TRUE,NOT(REGEXMATCH(A1,"^\d{4} \d{3} \d{3}$")))))


2

First substitute the white space. Paste this to Cell B2: =SUBSTITUTE(A2, " ", "") After that make your number to exactly 10 digit. Paste this to Cell C2: =IF(LEN(B2)=9,"0"&B2, IF(LEN(B2)=8,"00"&B2, IF(LEN(B2)=10,B2))) Lastly add the space back to the right place.Paste this to Cell D2: =LEFT(C2,4)&" &...


0

You haven't provided any range structure. But the decimal-only portion of any number can be found this way: =number - INT(number) So if your numbers in question were in A2:A, the CF custom formula might look like this: =A2-INT(A2)=.03


0

You want last week's rows to disappear all at once every Monday (Sunday night at 11:59 ideally). You use formatting (and possibly conditional formatting) within your "calendar" (for example, 24 December is highlighted in green). There are likely to be many ways in which your question could be answered. I have prioritised three factors: 1 - Any/all ...


Top 50 recent answers are included