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

Data Analysis

  • How to create a Histogram in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • How To Filter Data in Excel
  • Working With Tables in Excel
  • Excel Line Chart

References

  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to get relative row numbers in a range in Excel
  • Count rows with at least n matching values
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Count unique text values with criteria

Data Validations

  • Excel Data validation exists in list
  • Data validation must not exist in list
  • Excel Data validation require unique number
  • Excel Data validation must begin with
  • How To Create Drop-down List in Excel

Category: Formatting

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

Conditional formatting gantt chart weekends in Excel

by

This tutorial shows how to work Conditional formatting gantt chart weekends in Excel using the example below; Formula =WEEKDAY(date,2)>5 Explanation To build a Gantt chart with weekends shaded, you can use Conditional Formatting with a formula based on the weekday function.In the example shown, the formula applied the calendar, starting at D4, is: =WEEKDAY(D$4,2)>5 Note: this formula deals …

Continue Reading

Conditional formatting highlight target percentage in Excel

by

This tutorial shows how to work Conditional formatting highlight target percentage in Excel using the example below;  Formula =A1>=X =A1>=Y =A1<Y Explanation To highlight a percentage value in a cell using different colors, where each color represents a particular level, you can use multiple conditional formatting rules, with each rule targeting a different threshold. In the example shown, …

Continue Reading

Highlight approximate match lookup conditional formatting in Excel

by

This tutorial shows how to Highlight approximate match lookup conditional formatting in Excel using the example below; Formula =OR($B5=LOOKUP(width,widths),B$5=LOOKUP(height,heights)) Explanation To highlight rows and columns associated with an approximate match, you can use conditional formatting with a formula based on the LOOKUP function together with with a logical function like OR or AND. In the example …

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

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

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

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

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

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

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

Posts navigation

  • Previous
  • 1
  • 2

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

  • IF with wildcards in Excel
  • SWITCH function example in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • How to use Excel TRUE Function

Date Time

  • Basic Overtime Calculation Formula in Excel
  • How to calculate nth day of week in month in Excel
  • Get days between dates ignoring years in Excel
  • Count times in a specific range in Excel
  • Get days, hours, and minutes between dates in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group numbers with VLOOKUP in Excel
  • Categorize text with keywords in Excel
  • Group times into 3 hour buckets in Excel
  • Running count group by n size in Excel

General

  • How to choose page/paper size in Excel before Printing
  • How to test a range for numbers in Excel
  • Flash Fill in Excel
  • Spell Check in Excel
  • How to get Excel workbook path only
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning