Excel provides predefined formatting options for font size, color, line spacing, alignment etc that allows users to quickly style worksheet cells.
This example shows you how to use conditional formatting to shade alternate rows. Shading every other row in a range makes it easier to read your data. 1. Select a range. 2. On the Home tab, in the Styles group, click Conditional Formatting. 3. Click New Rule. 4. Select ‘Use a formula to determine which cells …
This example teaches you how to create a simple timesheet calculator in Excel. Cells that contain formulas are colored light yellow. 1. To automatically calculate the next 4 days and dates when you enter a start date, use the formulas below. 2. Select the cells containing the times. 3. Right click, click Format Cells, and select the right …
There’s a difference between identifying duplicated values/records and removing duplicated values/records in excel worksheet. To identify duplicate values/records in excel; Use conditional formatting. Home Tab → Styles Group → conditional formatting→Highlight Cell rule →Duplicate Values To remove duplicate values/records in excel; Use remove duplicate Data Tab → Data Tools Group → Remove Duplicate Command icon
Format Cells simply refers to style; font, font colour, font size, borders and more while Format Numbers refer to data pattern, ie percentage, currency and more. Format Numbers We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). 1. Enter the value 0.8 into cell B2. By default, Excel …
Excel Custom Number Format with Leading Zeros, Add Text, Colours Example How to add a leading zero in excel Beyond the built-in options: The Format Cells dialog offers so many options available “out-of-the-box.” The available format category and type options go far beyond the basics. But, if what is available still doesn’t meet your requirements, you …
These example describes how to compare two lists using conditional formatting. Example 1: Compare Lists of Customers for May 2010 and April 2010. Select cells in both lists (select first list, then hold CTRL key and then select the second) Go to Conditional Formatting > Highlight Cells Rules > Duplicate Values Press ok. See other examples Example …
This tutorial shows how to create 1st and 2nd level outline formatting using excel formulas. To generate basic outline numbering you can use COUNTA, MID, FIND, IF, and LEN functions. Note: The formula used will only handle a 2-level outline. Explanation In the example shown, the formula in D5 is: =COUNTA($B$5:B5)&”.”&IF(B5<>””,1, MID(D4,FIND(“.”,D4)+1,LEN(D4))+1) How this formula works At …
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 …
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 cells that begin with in Excel using the example below; Formula =SEARCH(“substring”,A1)=1 Explanation Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells that begin with specific text. However, if you want more flexibility, you can use your own formula, as …
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 cells that contain in Excel using the example below; Formula =ISNUMBER(SEARCH(substring,A1)) Explanation Note: Excel contains many built-in “presets” for highlighting values with conditional formatting, including a preset to highlight cells that contain specific text. However, if you want more flexibility, you can use your own formula, as explained in this …
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 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 Highlight cells that contain one of many in Excel using the example below; Formula =SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))>0 Explanation To highlight cells that contain one of many text strings, you can use a formula based on the functions ISNUMBER and SEARCH, together with the SUMPRODUCT function. In the example shown, the conditional formatting applied …
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 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 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 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 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 dates in same month and year in Excel using the example below; Formula =TEXT(A1,”myyyy”)=TEXT(date,”myyyy”) Explanation If you want to use conditional formatting to highlight dates that are in the same month and year as another date, you can use a simple formula based on the TEXT function. For example, …
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 …
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 …