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

Data Analysis

  • Excel Frequency Function Example
  • Example of COUNTIFS with variable table column in Excel
  • Working With Tables in Excel
  • How to add Trendline to a chart in Excel
  • Understanding Pivot Tables in Excel

References

  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel ROW Function
  • Lookup entire row in Excel
  • Multi-criteria lookup and transpose in Excel
  • Count rows with at least n matching values

Data Validations

  • Excel Data validation must begin with
  • Data validation must not exist in list
  • Excel Data validation with conditional list
  • Excel Data validation must not contain
  • Excel Data validation date in next 30 days

Category: Formatting

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

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

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

Conditional formatting gantt chart in Excel

by

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 …

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

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

Find duplicate values in two columns in Excel

by

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 …

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

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

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

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

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

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

  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • Nested IF function example in Excel
  • How to use Excel NOT Function
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • Add days to date in Excel
  • Get last working day in month in Excel
  • How to calculate quarter from date in Excel
  • SECOND function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group arbitrary text values in Excel
  • How to randomly assign people to groups in Excel
  • Group numbers at uneven intervals in Excel
  • Group times into 3 hour buckets in Excel
  • Running count group by n size in Excel

General

  • Excel Ribbon Quick Overview For Beginners
  • How to get Excel workbook path only
  • Delete Blank Rows at Once in Excel
  • How to create dynamic named range with INDEX in Excel
  • With vs Without Array Formula in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning