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

Data Analysis

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Everything about Charts in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Understanding Anova in Excel

References

  • Two-way lookup with VLOOKUP in Excel
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to get last row in numeric data in Excel
  • How to use Excel ROW Function
  • Count rows that contain specific values in Excel

Data Validations

  • Excel Data validation allow weekday only
  • Excel Data validation unique values only
  • Excel Data validation whole percentage only
  • Excel Data validation must not contain
  • Excel Data validation require unique number

Tag: Cell Ranges & References

Understanding Named Range, Named Constant, Name Manager in Excel Formulas

by

By using names  you can make your formulas much easier to understand and maintain i.e to update, audit, and manage. You can define a name for a cell range, function, constant, or table. Name: A word or string of characters in Excel that represents a cell, range of cells, formula, or constant value. Named Range …

Continue Reading

Dynamic Named Range in Excel

by

A dynamic named range expands automatically when you add a value to the range. 1. For example, select the range A1:A4 and name it Prices. 2. Calculate the sum. 3. When you add a value to the range, Excel does not update the sum. To expand the named range automatically when you add a value to the range, execute the following …

Continue Reading

Cell References: Relative, Absolute and Mixed Referencing Examples

by

Cell references in Excel are very important. Building a structure or a template in excel using formula one needs to understand the difference between relative, absolute and mixed reference. Relative Reference To identify relative referencing, it is simply the ‘cell name’  as illustrated below. N/B: Cell Name comprises of a column label and a row number of …

Continue Reading

Flash Fill in Excel

by

Use flash fill in Excel 2013 or later to automatically extract or combine data. Join 1. For example, use flash fill to join the last names in column A and the first names in column B to create email addresses. 2. First, tell Excel what you want to do by entering a correct email address in cell C1. 3. …

Continue Reading

Automatically fill series of cells in Excel using AutoFill

by

This lesson illustrates a quick and easy way to enter data in Excel using the fill handle. The fill handle is used to automatically fill data in series in a worksheet e.g serial Number, Days of the Week, Months of the year, Date and Time, etc. 1. For example, enter the value 10 into cell …

Continue Reading

With vs Without Array Formula in Excel

by

Array Formulas: Without Array Formula, With Array Formula and F9 Key This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple calculations in one cell. With Array Formula We don’t need to store the range in column D. Excel can store this range in its memory. A range stored in Excel’s memory is called …

Continue Reading

Excel Autofill Cell Ranges, Copy, Paste

by

Understanding Cell Ranges, Auto fills, Copy/Paste, Insert Row, Column in Excel Auto fill options in excel is to cell based on another or fill column with same value without dragging CELL Ranges People find it difficult to understand Cell Ranges in Excel. To effectively work with formulas it is apparent to have a good knowledge …

Continue Reading

Check if multiple cells have same value in Excel

by

To confirm that a range of cells all have the same value, you can use a formula based on the COUNTIF function. Formula =COUNTIF(range,”<>value”)=0 Explanation In the example shown, the formula in C9 is: =COUNTIF(C5:C8,”<>ok”)=0 Note: this formula is not case-sensitive, you can find a case-sensitive formula here. How this formula works This formula relies on …

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 retrieve first match between two ranges in Excel

by

To retrieve the first match in two ranges of values, you can use a formula based on the INDEX, MATCH, and COUNTIF functions. Formula =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) Explanation In the example shown, the formula in G5 is: =INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0)) where “range1” is the named range B5:B8, “range2” is the named range D5:D7. How this formula works In this example the named range …

Continue Reading

Check if range contains a value not in another range in Excel

by

To test if a range contains any values (i.e. at least one value) not in another range, you can use the SUMPRODUCT function with MATCH and ISNA. The MATCH function receives a single lookup value, and returns a single match if any. In this case, however, we are giving MATCH an array for lookup value, …

Continue Reading

How to get first column number in range in Excel

by

You can get the first column (i.e. the starting column number) in a range with a formula based on the COLUMN function. Formula =MIN(COLUMN(range)) Explanation In the example shown, the formula in cell F5 is: =MIN(COLUMN(data)) where data is a named range for B5:D10 How this formula works When given a single cell reference, the COLUMN function …

Continue Reading

How to test a range for numbers in Excel

by

To test a range for numbers, you can use a formula based on the ISNUMBER and SUMPRODUCT functions. See example below: Formula =SUMPRODUCT(–ISNUMBER(range))>0 Explanation In the example shown, the formula in xxxx is: =SUMPRODUCT(–ISNUMBER(C5:C9))>0 How this formula works Working from the inside out, the ISNUMBER function will return TRUE when given a number and FALSE if …

Continue Reading

How to create dynamic named range with OFFSET in Excel

by

One way to create a dynamic named range with a formula is to use the OFFSET function together with the COUNTA function. Dynamic ranges are also known as expanding ranges – they automatically expand and contract  to accommodate new or deleted data. Note: OFFSET is a volatile function, which means it recalculates with every change to a …

Continue Reading

How to add sequential row numbers to a set of data in Excel

by

To add sequential row numbers to a set of data with a formula, you can use the ROW function. See example below: Formula =ROW()-offset Explanation In the example shown, the formula in B5 is: =ROW()-4 How this formula works When not given a reference, the ROW function returns the row number of the current row. …

Continue Reading

How to create dynamic named range with INDEX in Excel

by

This tutorials show examples one and two dynamic named ranges created. The first is created with the INDEX function together with the COUNTA function. Dynamic named ranges automatically expand and contract when data is added or removed. Formula =$A$1:INDEX($A:$A,lastrow) Explanation This page shows an example of a dynamic named range created with the INDEX function together with the COUNTA function. …

Continue Reading

How to count total number of cells in a rectangular range in Excel

by

If you need to count the total number of cells in a rectangular range, you can do so with the ROWS and COLUMNS functions. Formula =ROWS(range)*COLUMNS(range) Note that this formula only works for a single rectangular range of cells. Explanation In the example, the active cell contains this formula: =ROWS(B4:C9)*COLUMNS(B4:C9) How this formula works There …

Continue Reading

How to test for all values in a range are at least in Excel

by

To test if all values in a range are at least a certain threshold value, you can use the COUNTIF function together with the NOT function. Formula =NOT(COUNTIF(range,”<65″)) Explanation In the example shown, the formula in G5 is: =NOT(COUNTIF(B5:F5,”<65″)) How this formula works At the core, this formula uses the COUNTIF function to count any …

Continue Reading

How to count total columns in range in Excel

by

If you need to count the number of columns in a range, use the COLUMNS function. COLUMNS counts the number of columns in and returns the result as a number. Formula =COLUMNS(range) Explanation In the example, the active cell contains this formula: =COLUMNS(B4:C9)

Continue Reading

How to return TRUE when all cells in range are blank in Excel

by

To return TRUE when all cells in a range are blank or empty, you can use a formula based on SUMPRODUCT and a logical test that suits the use case. Formula =SUMPRODUCT(–(range<>””))=0 Explanation In the example shown, the formula in E5 is: =SUMPRODUCT(–(B5:D5<>””))=0 How this formula works Working from the inside out, this formula contains …

Continue Reading

To count total rows in a range in Excel

by

If you need to count the number of rows in a range, use the ROWS function. The ROWS counts the number of rows in the supplied range and returns that number as the result. See illustration below: Formula =ROWS(range) Explanation In the example, the active cell contains this formula: =ROWS(B4:C9)

Continue Reading

How to get address of last cell in range in Excel

by

To get the address of the first cell in a named range, you can use the ADDRESS function together with ROW and COLUMN functions. Formula =ADDRESS(ROW(range)+ROWS(range)-1,COLUMN(range)+COLUMNS(range)-1) Explanation In the example shown, the formula in G6 is: =ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1) How this formula works The ADDRESS function builds an address based on a row and column number. Working …

Continue Reading

How to get address of first cell in range in Excel

by

To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions. See example below; Note: The ADDRESS function builds an address based on a row and column number. Formula =ADDRESS(ROW(range),COLUMN(range)) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data)) …

Continue Reading

How to get first row number in range in Excel

by

You can get the first row (i.e. the starting row number) in a range with a formula based on the ROW function. Formula =MIN(ROW(range)) Explanation In the example shown, the formula in cell F5 is: =MIN(ROW(data)) where data is a named range for B5:D10 How this formula works When given a single cell reference, the …

Continue Reading

How to get address of named range in Excel

by

To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. Formula =ADDRESS(ROW(nr),COLUMN(nr))&”: “&ADDRESS(ROW(nr)+ROWS(nr)-1, COLUMN(nr)+COLUMNS(nr)-1) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data),4) &”:”&ADDRESS(ROW(data)+ROWS (data)-1,COLUMN(data)+COLUMNS(data)-1,4) where “data” is the named range B5:D10 How this formula works The core of …

Continue Reading

How to get relative column numbers in a range in Excel

by

This tutorial shows how to get a full set of relative column numbers in a range using an array formula based on the COLUMN function. Formula {=COLUMN(range)-COLUMN(range.firstcell)+1} On the worksheet, this must be entered as multi-cell array formula using Control + Shift + Enter This is a robust formula that will continue to generate relative numbers …

Continue Reading

How to get relative row numbers in a range in Excel

by

To get a full set of relative row numbers in a range, you can use an array formula based on the ROW function. See example below; Formula {=ROW(range)-ROW(range.firstcell)+1} Note: this is an array formula that must be entered with Control + Shift + Enter. If you’re entering this on the worksheet (and not inside another formula), make …

Continue Reading

How to get last row in mixed data with blanks in Excel

by

To get the last relative position (i.e. last row, last column) for mixed data that may contain empty cells, you can use the MATCH function as described below. Note: this is an array formula and must be entered with Control+Shift+Enter. Formula {=MATCH(2,1/(range<>””))} Explanation In the example shown, the formula in E5 is: {=MATCH(2,1/(B4:B10<>””))} Last *relative* …

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

Delete Blank Rows at Once in Excel

by

When you have empty rows cluttering your spreadsheet and you need to tighten it up, easy and fast? Removing blank rows is the first thing you should do! This example teaches you how to delete blank rows or rows that contain blank cells. 1. On the Home tab, in the Editing group, click Find & Select. 2. …

Continue Reading

How to Copy, Paste and Retain Exact Copied Formula in another Cell in Excel

by

When you copy a formula, Excel automatically adjusts the cell references for each new cell the formula is copied to. For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1. When you copy this formula to cell B3 (select cell A3, press CTRL + c, select …

Continue Reading

3D-reference example in Excel

by

A 3D-reference in Excel refers to the same cell or range on multiple worksheets. First, we’ll look at the alternative examples. 1. On the Company sheet, select cell B2 and type an equal sign = 2. Go to the North sheet, select cell B2 and type a + 3. Repeat step 2 for the Mid and South sheet. Result. …

Continue Reading

How to work on another Workbook in Excel — External References

by

This chapter shows how to Create External Reference, Alert , Edit Links in Excel. An external reference in Excel is a reference to a cell or range of cells in another workbook. Below you can find the workbooks of three divisions (North, Mid and South). Create External Reference To create an external reference, execute the following steps. 1. Open all workbooks. 2. In …

Continue Reading

Convert text string to valid reference in Excel using Indirect function

by

Applying Excel  INDIRECT function to Cell Reference, Range Reference, Named Range, Worksheet Reference. Use the INDIRECT function in Excel to convert a text string into a valid reference. You can use the & operator to create text strings. Cell Reference Use the INDIRECT function in Excel to convert a text string into a valid cell reference. 1. For example, take a look …

Continue Reading

Circular Reference Problem Solved

by

You’ve entered a formula, but it’s not working. Instead, you’ve got this message about a “circular reference.” Circular Reference occurs your formula is trying to calculate itself. A formula in a cell that directly or indirectly refers to its own cell is called a circular reference. 1. For example, the formula in cell A3 below directly refers …

Continue Reading

Hide and Unhide Columns or Rows in Excel

by

Only show just the data that you need to see or print by hiding columns or rows in Excel. Sometimes it can be useful to hide columns or rows. Hide To hide a column, execute the following steps. 1. Select a column. 2. Right click, and then click Hide. Result: Note: to hide a row, select a row, …

Continue Reading

How To Skip Blanks Using Paste in Excel

by

Normally, when you copy a range of data cells into a new range that contains data already, it overwrites the new range. But, what if you want to add content from new range excluding blanks to the old cell range. Use the ‘Paste Special Skip Blanks’ option and Excel will not overwrite existing values with blanks. 1. Select the range B1:B12. 2. Right …

Continue Reading

AutoFit Column Width, AutoFit Row Height in Excel

by

You probably know how to change the width of a column in Excel, but do you know how to automatically fit the widest entry in a column? AutoFit Column Width The default width of a column is 64 pixels. 1. You can change the width of a column by clicking and dragging the right border of the column …

Continue Reading

Fibonacci Sequence in Excel

by

How to create Fibonacci series? It’s easy to create all sorts of sequences in Excel. For example, the Fibonacci sequence. The Fibonacci numbers form a sequence. After two starting numbers, the next number in the sequence is the sum of the two preceding numbers. The Fibonacci 10 numbers in the Fibonacci sequence are: 0, 1, 2, 3, …

Continue Reading

How to use Custom Lists to fill range in Excel

by

If you create a custom list in Excel, you can easily fill a range with your own list of departments, clients, cities, credit card numbers, etc. This can save time and reduce errors. First, we will look at an example of a built-in list. 1. Type Sun into cell B2. 2. Select cell B2, click on the lower …

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

  • Not Equal To ‘<>‘ operator in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function example in Excel
  • IF with boolean logic in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get month name from date in Excel
  • How to calculate Day of the Year in Excel
  • Add decimal minutes to time in Excel
  • Get day from date in Excel
  • How to calculate most recent day of week in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel
  • Running count group by n size in Excel
  • Group times into 3 hour buckets in Excel

General

  • How to Create Calendar in Excel
  • List worksheet index numbers in Excel
  • How to test a range for numbers in Excel
  • Basic numeric sort formula in Excel
  • How to calculate profit margin percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning