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

Data Analysis

  • How To Create Frequency Distribution in Excel
  • How to calculate current stock or inventory in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Number and Text Filters Examples in Excel
  • How to combine 2 or more chart types in a single chart in Excel

References

  • How to retrieve first match between two ranges in Excel
  • How to create dynamic named range with OFFSET in Excel
  • How to use Excel ROWS Function
  • How to reference named range different sheet in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation allow weekday only
  • Data validation must not exist in list
  • Excel Data validation require unique number
  • Excel Data validation only dates between

Tag: AND function

Excel If, Nested If, And/Or Criteria Examples

by

IF function is one of the most used functions in Excel. This page contains many easy to follow IF examples. Simple If Examples The IF function checks whether a condition is met, and returns one value if true and another value if false. 1a. For example, take a look at the IF function in cell B2 below. …

Continue Reading

IF, AND, OR and NOT Functions Examples in Excel

by

Learn how to use Excel’s logical functions such as the IF, AND, OR and NOT function. If The IF function checks whether a condition is met, and returns one value if true and another value if false. 1. For example, take a look at the IF function in cell C2 below. Above Case Study Explanation: if the …

Continue Reading

AND function: Description, Usage, Syntax, Examples and Explanation

by

What is AND function in Excel? AND function is one of the Logical functions in Microsoft Excel that is used to determine if all conditions in a test are TRUE. Examples of AND function Here are some general examples of using AND by itself, and in conjunction with the IF function. Formula Description =AND(A2>1,A2<100) Displays TRUE if A2 is greater than …

Continue Reading

Check multiple cells are equal in Excel

by

To confirm two ranges of the same size contain the same values, you can use a simple array formula based on the AND function. See illustration below: Formula {=AND(range1=range2)} Explanation In the example shown, the formula in C9 is: {=AND(B5:D12=F5:H12)} Note: this is an array formula and must be entered with control + shift + enter. How …

Continue Reading

Check if multiple cells have same value with case sensitive in Excel

by

To verify that multiple cells have the same value with a case-sensitive formula, you can use a simple array formula based on the EXACT and AND functions. See example below: Formula {=AND(EXACT(range,value))} Explanation In the example shown, the formula in G5 is: =AND(EXACT(B5:F5,B5)) This is an array formula and must be entered with control + …

Continue Reading

How to set check register balance in Excel

by

This tutorial shows how to set a check register formula that calculates a running balance and also, blank values using formula based on simple addition and subtraction. See illustrations below: Formula =balance-debit+credit Explanation In the example shown, the formula in G6 is: =G5-E6+F6 How this formula works The value in G5 is hard-coded. The formula picks …

Continue Reading

How to check if cell contains some words but not others in Excel

by

To test a cell to see if contains certain words but not others, you can use an array formula based on the COUNT and SEARCH functions, wrapped in the AND function. Formula {=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0)} Explanation In the example shown, the formula in C5 is: {=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0)} This formula returns TRUE when B5 contains any of the words …

Continue Reading

Excel Data validation require unique number

by

Set criteria to accept only unique number, ie no duplicate. To allow only unique numbers in a given range, you can use data validation with a custom formula based on the AND, ISNUMBER, and COUNTIF functions. Formula =AND(ISNUMBER(A1),COUNTIF(range,A1)<2) Cell references in data validation formulas are relative to the upper left cell in the range selected …

Continue Reading

Excel Data validation whole percentage only

by

Set criteria to allow whole percentage only without decimals. To allow only whole number percentages like 5%, 10% and not 5.5%, 10.25%, etc. you can use data validation with a custom formula based on the TRUNC function. Formula =TRUNC(A1*100)=(A1*100) Notes: Data validation rules are triggered when a user adds or changes a cell value. Cell references …

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

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

Excel Data validation date in next 30 days

by

Using the example below, this tutorial shows how to create Data validation date in next 30 days in Excel. Formula =AND(A1>TODAY(),A1<=(TODAY()+days)) Explanation Note: Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula when you want more control and flexibility. To allow only …

Continue Reading

Excel Data validation allow uppercase only

by

Using the example below, this tutorial shows how to create Excel Data validation to allow uppercase only. Formula =AND(EXACT(A1,UPPER(A1)),ISTEXT(A1)) Explanation To allow a user to enter only uppercase TEXT,  you can use data validation with a custom formula based on the UPPER, EXACT, and AND functions. In the example shown, the data validation applied to C5:C7 is: …

Continue Reading

Excel Data validation only dates between

by

Set criteria to accept only dates in a particular period Excel has several built-in data validation rules for dates. This page explains how to create a your own validation rule based on a custom formula in case you want or need more control and flexibility. Formula =AND(A1>=date1),A1<=date2) Explanation To allow a user to enter only …

Continue Reading

If this AND that OR that in Excel

by

This tutorial shows how to calculate If this AND that OR that in Excel using the example below; Formula =IF(AND(A1=”x”,OR(B1=”y”,B1=”z”)),”x”,””) Explanation To test for various combinations of this AND that, or this OR that, you can use the IF function with the AND and OR functions. In the example shown, the formula in D6 is: =IF(AND(B6=”red”,OR(C6=”small”,C6=”medium”)),”x”,””) When an item …

Continue Reading

If cell is x or y and z in Excel

by

This tutorial shows how to calculate If cell is x or y and z in Excel using the example below; Formula =IF(AND(OR(A1=x,A1=y),B1=z),”yes”,”no”) Explanation You can combine logical statements with the OR and AND functions inside the IF function. If color is red or green and quantity is greater than 10 In the example shown, we …

Continue Reading

If this AND that in Excel

by

This tutorial shows how to calculate If this AND that in Excel using the example below; Formula =IF(AND(A1=”this”,B1=”that”),”x”,””) Explanation If you want to do something specific when two or more conditions are TRUE, you can use the IF function in combination with the AND function to evaluate conditions with a test, then take one action if the result …

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

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

Extract data with helper column in Excel

by

This tutorial shows how to Extract data with helper column in Excel using the example below; Formula =IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),””) Explanation One way to extract data in Excel is to use INDEX and MATCH with a helper column that marks matching data. This avoids the complexity of a more advanced array formula. In the example shown, the …

Continue Reading

How to use Excel AND Function

by

This Excel tutorial explains how to use the AND function with syntax and examples. Excel AND Function Description Microsoft Excel AND function tests a number of supplied conditions and returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE. That is; TRUE if ALL of the conditions evaluate to TRUE or FALSE otherwise …

Continue Reading

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

  • AND function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • IF function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Steps to create Dynamic calendar grid in Excel
  • How to calculate working days left in month in Excel
  • Calculate expiration date in Excel
  • Calculate retirement date in Excel
  • How to calculate nth day of week in month in Excel

Grouping

  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel
  • Group numbers with VLOOKUP in Excel
  • Calculate conditional mode with criteria in Excel
  • How to randomly assign people to groups in Excel

General

  • How to generate random number weighted probability in Excel
  • Excel Operators
  • How to calculate decrease by percentage in Excel
  • How to create dynamic named range with INDEX in Excel
  • How to calculate profit margin percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning