Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Compare Two Lists in Excel
  • Randomize/ Shuffle List in Excel
  • How To Create Pareto Chart in Excel
  • How to Use Solver Tool in Excel
  • How to Sort by Color in Excel

References

  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Extract data with helper column in Excel
  • How to get relative column numbers in a range in Excel
  • Approximate match with multiple criteria in Excel
  • How to get last row in numeric data in Excel

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation must contain specific text
  • Excel Data validation allow weekday only
  • Excel Data validation unique values only
  • Excel Data validation must begin with

Category: Formatting

Excel provides predefined formatting options for font size, color, line spacing, alignment etc that allows users  to quickly style worksheet cells.

Shade Alternate Rows in Excel

by

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 …

Continue Reading

How To Compare Two Lists in Excel

by

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 …

Continue Reading

Identify Duplicate Values vs Remove Duplicates in Excel

by

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

Continue Reading

Format Cells and Numbers in Excel

by

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 …

Continue Reading

Add Leading Zeros, Text And Colours Custom Number Format

by

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 …

Continue Reading

Basic outline numbering in Excel

by

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 …

Continue Reading

Clean and reformat telephone numbers using SUBSTITUTE function in Excel

by

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 …

Continue Reading

Shade alternating groups of n rows in Excel

by

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 …

Continue Reading

Highlight rows that contain in Excel

by

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 …

Continue Reading

Highlight row and column intersection exact match in Excel

by

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 …

Continue Reading

Highlight numbers that include symbols in Excel

by

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 …

Continue Reading

Highlight rows with blank cells in Excel

by

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 …

Continue Reading

Highlight values not between X and Y in Excel

by

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 …

Continue Reading

Highlight values greater than in Excel

by

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 …

Continue Reading

Highlight values between in Excel

by

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 …

Continue Reading

Highlight top values in Excel

by

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 …

Continue Reading

Highlight unprotected cells in Excel

by

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 …

Continue Reading

Highlight unique values in Excel

by

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. …

Continue Reading

Highlight rows with dates between In Excel

by

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 …

Continue Reading

Excel Data validation allow text only

by

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 …

Continue Reading

Excel Data validation allow numbers only

by

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 …

Continue Reading

Highlight dates in same month and year in Excel

by

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, …

Continue Reading

Highlight dates in the next N days in Excel

by

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 …

Continue Reading

Highlight dates that are weekends in Excel

by

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 …

Continue Reading

Highlight duplicate columns in Excel

by

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 …

Continue Reading

Highlight duplicate rows in Excel

by

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 …

Continue Reading

Highlight duplicate values in Excel

by

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. …

Continue Reading

Highlight integers only in Excel

by

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 …

Continue Reading

Highlight entire rows in Excel

by

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 …

Continue Reading

Highlight every other row in Excel

by

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 …

Continue Reading

Highlight multiples of specific value in Excel

by

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: …

Continue Reading

Highlight missing values in Excel

by

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 …

Continue Reading

Highlight dates greater than in Excel

by

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 …

Continue Reading

Highlight dates between in Excel

by

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 …

Continue Reading

Highlight data by quartile in Excel

by

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 …

Continue Reading

Highlight column differences in Excel

by

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 …

Continue Reading

Highlight cells that equal in Excel

by

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 …

Continue Reading

Highlight cells that end with in Excel

by

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 …

Continue Reading

Highlight cells that contain one of many in Excel

by

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 …

Continue Reading

Highlight cells that contain in Excel

by

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 …

Continue Reading

Posts navigation

  • 1
  • 2
  • Next

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • How to use IFS function in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • IF, AND, OR and NOT Functions Examples in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Calculate retirement date in Excel
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation
  • Get last day of month in Excel

Grouping

  • Group arbitrary text values in Excel
  • Map text to numbers in Excel
  • Calculate conditional mode with criteria in Excel
  • Running count group by n size in Excel
  • How to randomly assign people to groups in Excel

General

  • Creating and Opening an existing file in Excel
  • List sheet names with formula in Excel
  • Share Excel data with Word documents
  • How to count total columns in range in Excel
  • How to get original price from percentage discount in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning