Excel provides predefined formatting options for font size, color, line spacing, alignment etc that allows users to quickly style worksheet cells.
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 …
This tutorial shows how to Highlight duplicate rows in Excel using the example below; Formula =COUNTIFS(A:A,$A1,B:B,$B1,C:C,$C1) Explanation Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to highlight entire rows that are duplicates you’ll need to use your own formula, as explained …
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 …
This tutorial shows how to Highlight duplicate columns in Excel using the example below; Formula =SUMPRODUCT((row1=ref1)*(row2=ref2)*(row3=ref3))>1 Explanation Excel contains a built-in preset for highlighting duplicate values with conditional formatting, but it only works at the cell level. If you want to find and highlight duplicate columns, you’ll need to use your own formula, as explained …
This tutorial shows how to Highlight dates that are weekends in Excel using the example below; Formula =OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1) Explanation If you want to use conditional formatting to highlight dates occur on weekends (i.e. Saturday or Sunday), you can use a simple formula based on the WEEKDAY function. For example, if you have dates in the …
This tutorial shows how to Highlight dates in the next N days in Excel using the example below; Formula =AND(A1>TODAY(),A1<=(TODAY()+days)) Explanation If you want to highlight dates that occur in the next N days with conditional formatting, you can do so with a formula that uses the TODAY function with AND. This is a great …
This tutorial shows how to Highlight dates between in Excel using the example below; Formula =AND(A1>=date1,A1<=date2) Explanation If you want to highlight dates between two dates with conditional formatting, you can use a simple formula that uses the AND and DATE functions together. For example, if you have dates in the range B4:G11, and want to …
This tutorial shows how to Highlight dates greater than in Excel using the example below; Formula =A1>DATE(year,month,day) Explanation If you want to highlight dates greater than or less than a certain date with conditional formatting, you can use a simple formula that relies on the date function. For example, if you have dates in the …
This tutorial shows how to Highlight missing values in Excel using the example below; Formula =COUNTIF(list,A1)=0 Explanation To compare lists and highlight values that exist in one but not the other, you can apply conditional formatting with a formula based on the COUNTIF function. For example, to highlight values A1:A10 that don’t exist C1:C10, select A1:A10 and …
This tutorial shows how to Highlight multiples of specific value in Excel using the example below; Formula =MOD(A1,value)=0 Explanation To highlight numbers that are multiples of a another number with conditional formatting, you can use a simple formula based on the MOD function. In the example shown, the formula used to highlight multiples of 9 is: …
This tutorial shows how to Highlight every other row in Excel using the example below; Formula =ISEVEN(ROW()) Explanation If you want to highlight every other row in a table (sometimes called zebra striping) with conditional formatting you can do so with a simple formula that uses either the ISEVEN or ISODD function. For example, if …
This tutorial shows how to Highlight entire rows in Excel using the example below; Formula =($A1=criteria) Explanation To highlight entire rows with conditional formatting when a value meets specific criteria, use a formula with a mixed reference that locks the column. In the example shown, all rows where the owner is “bob” are highlighted with the following formula …
This tutorial shows how to Highlight integers only in Excel using the example below; Formula =MOD(A1,1)=0 Explanation To highlight numbers that are integers, you can use a simple formula based on the MOD function. In the example shown, conditional formatting has been applied to the range B4:G11 using this formula: =MOD(B4,1)=0 Note: it’s important that CF …
This tutorial shows how to Highlight duplicate values in Excel using the example below; Formula =COUNTIF(data,A1)>1 Explanation Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight duplicate values. However, if you want more flexibility, you can highlight duplicates with your own formula, as explained in this article. …
This tutorial shows how to Highlight data by quartile in Excel using the example below; Formula =A1>=QUARTILE(data,quart) Explanation To highlight cells by quartile, you can apply conditional formatting with a formula that uses the QUARTILE function. In the example shown, we are using 4 different conditional formatting rules. Each rule highlights a quartile in the …
This tutorial shows how to Highlight rows with dates between In Excel using the example below; Formula =AND($A1>=start,$A1<=end) Explanation If you want to highlight rows that contain dates between two dates with conditional formatting, you can use a formula based on the AND and DATE functions. In the example, shown, the range B5:E11 has a …
This tutorial shows how to Highlight unique values in Excel using the example below; Formula =COUNTIF(data,A1)=1 Explanation Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight unique values. However, if you want more flexibility, you can highlight unique values with your own formula, as explained in this article. …
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 …
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 …
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 …
This tutorial shows how to Highlight unprotected cells in Excel using the example below; Formula =CELL(“PROTECT”,A1)=0 Explanation To highlight unprotected cells (cells that are unlocked) with conditional formatting, you can use a formula based on the CELL function. In the example shown, the custom formula used to set up conditional formatting is: =CELL(“PROTECT”,A1)=0 Where A1 represents the …
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 …
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 …
This tutorial shows how to create Data validation to allow text only in Excel using the example below; Formula =ISTEXT(A1) Explanation To allow only text in a cell, you can use data validation with a custom formula based on the ISTEXT function. In the example shown, the data validation applied to C5:C9 is: =ISTEXT(C5) How this …
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 …
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 …
This tutorial shows how to Highlight cells that end with in Excel using the example below; Formula =COUNTIF(A1,”*text”) Explanation Note: Excel contains many built-in rules for highlighting values with conditional formatting, including a rule to highlight cells that end with specific text. However, if you want more flexibility, you can use your own formula, as explained …
This tutorial shows how to create Data validation to allow numbers only in Excel using the example below; Formula =ISNUMBER(A1) Explanation Note: Excel has several built-in data validation rules for numbers. This page explains how to create a your own validation rule based on a custom formula. To allow only numbers in a cell, you can use …
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 …
Excel always reduces fractions to its smallest denominator. This example teaches you how to enter a fraction in Excel and how change the format of a fraction. 1. To enter the mixed fraction 1 4/7 (one and four sevenths), type 1 4/7. To enter the fraction 4/7 (four sevenths) into cell A1, select cell A1 and type …
This page illustrates how to merge selected cells into one large cell. Why Merge Cells in Excel? This can be useful if you want to make clear that a label in Excel applies to multiple columns. Before you start: this feature only keeps the value in the upper-left cell and deletes all other values. Go here to concatenate (join) strings in Excel. …
This tutorial shows how to Highlight 3 smallest values with criteria in Excel using the example below; Formula =AND(A1=criteria,B1<=SMALL(IF(criteria,values),3)) Explanation To highlight the 3 smallest values that meet specific criteria, you can use an array formula based on the AND and SMALL functions. In the example shown, the formula used for conditional formatting is: =AND($B3=$G$3,$C3<=SMALL(IF(color=$G$3,amount),3)) …
This tutorial shows how to Conditionally format based on another cell in Excel using the example below; Formula =A1>=$J$1 Explanation To apply conditional formatting based on a value in another cell, you can create a rule based on a simple formula. In the example shown, the formula used to apply conditional formatting to the range …
This tutorial shows how to Conditionally format column is blank in Excel using the example below; Formula =OR(A1=””,B1=””, C1=””) Explanation To highlight values in one column when values in one or more other columns are blank, you can use the OR function and a basic logical expression to test for blank or empty values. In …
This tutorial shows how to Highlight column differences in Excel using the example below; Formula =$A1<>$B1 Explanation If you want to highlight the differences between two columns of data with conditional formatting you can do so with a simple formula that uses the” not equal to” operator (e.g. <>) and mixed references. For example, if you …
This tutorial shows how to Highlight cells that equal in Excel using the example below; Formula =A1=”X” Explanation Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells that contain a specific value. However, if you want more flexibility, you can use your own formula, as explained …
This tutorial shows how to work Conditional formatting date past due in Excel using the example below; Formula =A1<TODAY() Explanation To highlight dates that are “past due” you can use a conditional formatting and a basic formula to check whether each date is less than today. In the example shown, conditional formatting has been applied to the range …
This tutorial shows how to work Conditional formatting dates overlap in Excel using the example below; Formula =SUMPRODUCT((start_date<=end_dates)*(end_date>=start_dates))>1 Explanation To highlight cells where dates overlap you can use conditional formatting with a formula based on the SUMPRODUCT function. In the example shown the formula in south E6 is: =SUMPRODUCT(($C6<=$D$5:$D$9)*($D6>=$C$5:$C$9))>1 This is the same formula used to highlight entire …
This tutorial shows how to work Conditional formatting gantt chart in Excel using the example below; Formula =AND(date>=start,date<=end) Explanation To build a Gantt chart, you can use Conditional Formatting with a formula based on the AND function. In the example shown, the formula applied to D5 is: =AND(D$4>=$B5,D$4<=$C5) How this formula works The trick with this approach is …
This tutorial shows how to Find duplicate values in two columns in Excel using the example below; Formula =AND(COUNTIF(range1,A1),COUNTIF(range2,A1)) Explanation To highlight duplicate values in two or more columns, you can use conditional formatting with on a formula based on the COUNTIF and AND functions. In the example shown, the formula used to highlight duplicate values …