Formatting

Clean and reformat telephone numbers using SUBSTITUTE function in Excel

One way to clean up and reformat telephone numbers is to strip out all extraneous characters, then apply Excel’s built-in telephone number format. Formula =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,”(“,””),”)”,””),”-“,””),” “,””),”.”,””)+0 Note that the cell appears in the middle, with function names above and substitutions below. Not only does this make the formula easier to read, it also makes it… read more »

Shade alternating groups of n rows in Excel

This tutorial shows how to Shade alternating groups of n rows in Excel using the example below; Formula =ISEVEN(CEILING(ROW()-offset,n)/n) Explanation To highlight rows in groups of “n” (i.e. shade every 3 rows, every 5 rows, etc.) you can apply conditional formatting with a formula based on the ROW, CEILING and ISEVEN functions. In the example… read more »

Highlight rows that contain in Excel

This tutorial shows how to Highlight rows that contain in Excel using the example below; Formula =SEARCH(text,cocatenated_columns) Explanation If you want to highlight rows in a table that contain specific text, you use conditional formatting with a formula that returns TRUE when the the text is found. The trick is to concatenate (glue together) the… read more »

Highlight row and column intersection exact match in Excel

This tutorial shows how to Highlight row and column intersection exact match in Excel using the example below; Formula =OR($A1=row_val,A$1=col_val) Explanation To highlight intersecting row(s) and column(s) with conditional formatting based on exact matching, you can use a simple formula based on mixed references and the OR function. In the example shown, the formula used… read more »

Highlight numbers that include symbols in Excel

This tutorial shows how to Highlight numbers that include symbols in Excel using the example below; Formula =IF(ISNUMBER(B4),B4<input,IF(LEFT(B4)=”<“,(MID(B4,2,LEN(B4))+0)<input)) Explanation To highlight numbers less than a certain value, including numbers entered as text like “<9”, “<10”, etc., you can use conditional formatting with a formula strips the symbols as needed and handles the result as a… read more »

Highlight rows with blank cells in Excel

This tutorial shows how to Highlight rows with blank cells in Excel using the example below; Formula =COUNTBLANK($B5:$F5) Explanation To highlight rows that contain one or more blank cells. you can conditional formatting with a simple formula based on the COUNTBLANK function. In the example shown, the range B5:F15 is formatted with a conditional formatting rule… read more »

Highlight values not between X and Y in Excel

This tutorial shows how to Highlight values not between X and Y in Excel using the example below; Formula =OR(A1<lower,A1>upper) Explanation If you want to use conditional formatting to highlight cells that are NOT between two values (a lower and upper limit), you can use a simple formula that returns TRUE when a value meets… read more »

Highlight values greater than in Excel

This tutorial shows how to Highlight values greater than in Excel using the example below; Note: Excel contains built-in “presets” for highlighting values above / below / equal to certain values, but if you want more flexibility you can apply conditional formatting with your own formula as explained in this article. Formula =A1>X Explanation If… read more »

Highlight values between in Excel

This tutorial shows how to Highlight values between in Excel using the example below; Formula =AND(A1>=lower,A1<=upper) Explanation Note: Excel contains many built-in “presets” for highlighting values above / below / between / equal to certain values, but if you want more flexibility you can apply conditional formatting using your own formula as explained in this… read more »

Highlight top values in Excel

This tutorial shows how to Highlight top values in Excel using the example below; Formula =A1>=LARGE(data,N) Explanation To highlight the highest values in a set of data with conditional formatting, you can use a formula based on the LARGE function. In the example shown, the formula used for conditional formatting is: =B4>=LARGE(data,input) Note: Excel contain a… read more »

Streamlined Topics