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

Data Analysis

  • How to Create Thermometer Chart in Excel
  • Excel Bar Chart
  • Reverse List in Excel
  • How To Create Frequency Distribution in Excel
  • Subtotal function in Excel

References

  • How to get last row in numeric data in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get first column number in range in Excel
  • How to use Excel LOOKUP Function
  • How to use Excel COLUMN Function

Data Validations

  • Excel Data validation specific characters only
  • Excel Data validation don’t exceed total
  • Excel Data validation require unique number
  • Excel Data validation must contain specific text
  • Excel Data validation unique values only

Category: Formatting

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

How to create simple Time Sheet in Excel

by

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 …

Continue Reading

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

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

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

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

Change Fractions Format in Excel

by

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 …

Continue Reading

Merge Cells in Excel

by

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

Continue Reading

Highlight blank cells in Excel

by

This tutorial shows how to Highlight blank cells in Excel using the example below; Formula =ISBLANK(A1) Explanation If you want to highlight cells that are blank or empty with conditional formatting, you can do so with a simple formula based on the ISBLANK function. For example, if you want to highlight blank cells in the range …

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 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 bottom values in Excel

by

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

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

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 cells that begin with in Excel

by

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 …

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

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 3 smallest values with criteria in Excel

by

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

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

Conditional formatting based on another cell in Excel

by

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 …

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

Conditional formatting column is blank in Excel

by

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 …

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

Conditional formatting date past due in Excel

by

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 …

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

Conditional formatting dates overlap in Excel

by

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 …

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

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

  • How to use Excel AND Function
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function
  • Check multiple cells are equal in Excel
  • IF, AND, OR and NOT Functions Examples in Excel

Date Time

  • Get days between dates ignoring years in Excel
  • Convert decimal seconds to Excel time
  • How to calculate quarter from date in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign data to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Group numbers with VLOOKUP in Excel
  • How to randomly assign people to groups in Excel

General

  • Index and match on multiple columns in Excel
  • Creating and Opening an existing file in Excel
  • How to get random value from list or table in Excel
  • Count cells that do not contain errors in Excel
  • 3D SUMIF for multiple worksheets in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning