In this Excel IFERROR, ISERROR, ISERR, IFNA and ISNA Tutorial, you learn how to use the IFERROR, ISERROR, ISERR, IFNA and ISNA functions in your worksheet formulas for the following:
Identify errors, including the #N/A error.
Handle errors, including the #N/A error, and return a specific:
Value;
Formula;
Expression; or
Reference.
Carry out VLookups that handle errors, including the #N/A error, and return a specific:
Value;
Formula;
Expression; or
Reference.
Check whether a specific value exists in a list or compare 2 columns.
This Excel IFERROR, ISERROR, ISERR, IFNA and ISNA Tutorial is accompanied by an Excel workbook containing the data and formulas I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.
Use the following Table of Contents to navigate to the section you're interested in.
Related Excel Tutorials
The following Tutorials may help you better understand and implement the contents below:
Formulas and functions:
Learn how to work with the LEFT, RIGHT, MID, LEN, FIND and SEARCH functions here.
Learn how to use worksheet functions in macros here.
Learn how to work with the VLookup function in VBA here.
You can find additional Tutorials in the Archives.
#1: IFERROR
IFERROR formula
To handle possible errors with the IFERROR function, use a formula with the following structure:
=IFERROR(Value,ValueIfError)
IFERROR process
To handle possible errors with the IFERROR function, follow these steps:
Specify the expression you want to check for errors (Value).
Specify that, if Value returns an error (IFERROR), another value (ValueIfError) is returned.
IFERROR formula explanation
Item: IFERROR
The IFERROR function:
Returns the value you specify (ValueIfError) if an expression (Value) returns an error; and
Returns the result of that expression (Value) otherwise.
In other words, IFERROR does the following:
Checks an expression (Value).
If Value returns an error, IFERROR returns the value you specify (ValueIfError).
If Value doesn't return an error, IFERROR returns the result of that expression.
Therefore, you usually use IFERROR to trap and handle errors in worksheet formulas. The IFERROR function deals with the following errors:
#N/A.
#VALUE!
#REF!
#DIV/0!
#NUM!
#NAME?
#NULL!
Item: Value
The value argument of the IFERROR function (Value) is a value, formula, expression or reference that Excel checks for errors.
If Value doesn't return an error, IFERROR returns the result of that expression.
Item: ValueIfError
The value_if_error argument of the IFERROR function (ValueIfError) is the value, formula, expression or reference that Excel returns if the value argument of the IFERROR function (Value) evaluates to an error.
IFERROR formula example
The worksheet formulas below handle possible errors with the IFERRORfunction, as follows:
Value: The quotient obtained by dividing:
The value specified in column G (G12 to G16); by
The value specified in column F (F12 to F16).
ValueIfError: The string “Total Sales are $ 0” (“Total Sales are $ 0”).
No.
IFERROR formula
1
=IFERROR(G12/F12,"Total Sales are $ 0")
2
=IFERROR(G13/F13,"Total Sales are $ 0")
3
=IFERROR(G14/F14,"Total Sales are $ 0")
4
=IFERROR(G15/F15,"Total Sales are $ 0")
5
=IFERROR(G16/F16,"Total Sales are $ 0")
Effects of using IFERROR formula example
The following image illustrates the results returned by the IFERROR formula that handles possible errors. As expected, the formulas (in cells H12 to H16):
Check an expression (Value) for errors; and
Return the following:
If Value returns an error: The string “Total Sales are $ 0” (ValueIfError).
If Value doesn't return an error: Value itself.
Notice the difference between the result returned by the IFERROR formula that handles errors in cell H16 and the result returned by the regular formula (without IFERROR) in cell H11.
#2: IFERROR then 0
IFERROR then 0 formula
To return 0 if an expression returns an error (with the IFERROR function), use a formula with the following structure:
=IFERROR(Value,0)
IFERROR then 0 process
To return 0 if an expression returns an error (with the IFERROR function), follow these steps:
Specify the expression you want to check for errors (Value).
Specify that, if Value returns an error (IFERROR), 0 (0) is returned.
IFERROR then 0 formula explanation
Item: IFERROR
The IFERROR function:
Returns the value you specify (0) if an expression (Value) returns an error; and
Returns the result of that expression (Value) otherwise.
In other words, IFERROR does the following:
Checks an expression (Value).
If Value returns an error, IFERROR returns the value you specify (0).
If Value doesn't return an error, IFERROR returns the result of that expression.
Therefore, you usually use IFERROR to trap and handle errors in worksheet formulas. The IFERROR function deals with the following errors:
#N/A.
#VALUE!
#REF!
#DIV/0!
#NUM!
#NAME?
#NULL!
Item: Value
The value argument of the IFERROR function (Value) is a value, formula, expression or reference that Excel checks for errors.
If Value doesn't return an error, IFERROR returns the result of that expression.
Item: 0
The value_if_error argument of the IFERROR function (0) is the value, formula, expression or reference that Excel returns if the value argument of the IFERROR function (Value) evaluates to an error.
To return 0 if an expression (Value) returns an error, set value_if_error to 0.
IFERROR then 0 formula example
The worksheet formulas below return 0 if an expression returns an error (with the IFERROR function),where Value is the quotient obtained by dividing:
The value specified in column G (G22 to G26); by
The value specified in column F (F22 to F26).
No.
IFERROR then 0 formula
1
=IFERROR(G22/F22,0)
2
=IFERROR(G23/F23,0)
3
=IFERROR(G24/F24,0)
4
=IFERROR(G25/F25,0)
5
=IFERROR(G26/F26,0)
Effects of using IFERROR then 0 formula example
The following image illustrates the results returned by the IFERROR formula that handles possible errors by returning 0. As expected, the formulas (in cells H22 to H26):
Check an expression (Value) for errors; and
Return the following:
If Value returns an error: 0.
If Value doesn't return an error: Value itself.
Notice the difference between the result returned by the IFERROR formula that handles errors by returning 0 in cell H26 and the result returned by the regular formula (without IFERROR then 0) in cell H21.
#3: IFERROR then blank
IFERROR then blank formula
To return a blank if an expression returns an error (with the IFERROR function), use a formula with the following structure:
=IFERROR(Value,"")
IFERROR then blank process
To return a blank if an expression returns an error (with the IFERROR function), follow these steps:
Specify the expression you want to check for errors (Value).
Specify that, if Value returns an error (IFERROR), a zero-length string (“”) is returned.
IFERROR then blank formula explanation
Item: IFERROR
The IFERROR function:
Returns the value you specify (“”) if an expression (Value) returns an error; and
Returns the result of that expression (Value) otherwise.
In other words, IFERROR does the following:
Checks an expression (Value).
If Value returns an error, IFERROR returns the value you specify (“”).
If Value doesn't return an error, IFERROR returns the result of that expression.
Therefore, you usually use IFERROR to trap and handle errors in worksheet formulas. The IFERROR function deals with the following errors:
#N/A.
#VALUE!
#REF!
#DIV/0!
#NUM!
#NAME?
#NULL!
Item: Value
The value argument of the IFERROR function (Value) is a value, formula, expression or reference that Excel checks for errors.
If Value doesn't return an error, IFERROR returns the result of that expression.
Item: “”
The value_if_error argument of the IFERROR function (“”) is the value, formula, expression or reference that Excel returns if the value argument of the IFERROR function (Value) evaluates to an error.
To return a blank if an expression (Value) returns an error, set value_if_error to a zero-length string (“”).
IFERROR then blank formula example
The worksheet formulas below return a blank if an expression returns an error (with the IFERROR function),where Value is the quotient obtained by dividing:
The value specified in column G (G32 to G36); by
The value specified in column F (F32 to F36).
No.
IFERROR then blank formula
1
=IFERROR(G32/F32,"")
2
=IFERROR(G33/F33,"")
3
=IFERROR(G34/F34,"")
4
=IFERROR(G35/F35,"")
5
=IFERROR(G36/F36,"")
Effects of using IFERROR then blank formula example
The following image illustrates the results returned by the IFERROR formula that handles possible errors by returning a blank (“”). As expected, the formulas (in cells H32 to H36):
Check an expression (Value) for errors; and
Return the following:
If Value returns an error: A zero-length string (“”).
If Value doesn't return an error: Value itself.
Notice the difference between the result returned by the IFERROR formula that handles errors by returning a blank in cell H36 and the result returned by the regular formula (without IFERROR then blank) in cell H31.
#4: IFERROR VLOOKUP
IFERROR VLOOKUP formula
To carry out a VLookup that handles possible errors (with IFERROR vs. IFNA), use a formula with the following structure:
To carry out a VLookup that handles possible errors (with IFERROR vs. IFNA), follow these steps:
Specify the value you want to look up (LookupValue) in the first (leftmost) column of a table (LookupTable).
Identify the cell range (a table array) containing the lookup table (LookupTable).
Specify the index number of the column (within LookupTable) from which you want to obtain a value (ColumnIndex).
Specify whether VLOOKUP searches for an exact or approximate match (RangeLookup).
Specify that, if VLOOKUP returns an error (IFERROR), another value (ValueIfError) is returned.
IFERROR VLOOKUP formula explanation
Formula #1: VLOOKUP(LookupValue,LookupTable,ColumnIndex,RangeLookup)
Item: VLOOKUP
The VLOOKUP function does the following:
Looks for a value (LookupValue) in the first (leftmost) column of a table (LookupTable); and
Returns a value in the same row but from another column you specify (ColumnIndex).
Item: LookupValue
The lookup_value argument of the VLOOKUP function (LookupValue) is the value you look up in the first (leftmost) column of LookupTable. In other words, LookupValue must usually be in the first column of the cell range you specify as LookupTable.
If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.
You can specify LookupValue as either:
A value;
A text string; or
A cell reference.
Item: LookupTable
The table_array argument of the VLOOKUP function (LookupTable) is the cell range in which VLOOKUP searches for the following:
The LookupValue in the first column of LookupTable; and
The value to return in the column you specify (ColumnIndex).
Therefore, the cell range you specify as LookupTable must usually include both of the following columns:
The first column, which must contain the LookupValue; and
The column from which VLOOKUP should return a value.
If VLOOKUP doesn't find LookupValue in the first column of LookupTable, it usually returns the #N/A error.
Item: ColumnIndex
The col_index_num argument of the VLOOKUP function (ColumnIndex) is the column number within the LookupTable from which VLOOKUP returns a value, as follows:
Column
ColumnIndex
Comments
First
1
Must usually contain the LookupValue. Otherwise, VLOOKUP usually returns the #N/A error.
Second
2
Third
3
…
…
#th
#
Item: RangeLookup
The range_lookup argument of the VLOOKUP function (RangeLookup) specifies whether VLOOKUP searches for an approximate or an exact match for LookupValue in the first column of LookupTable.
Set RangeLookup to TRUE when searching for an approximate match.
Set RangeLookup to FALSE when searching for an exact match.
Formula #2: IFERROR(VLOOKUP(…),ValueIfError)
Item: IFERROR
The IFERROR function:
Returns the value you specify (ValueIfError) if an expression (VLOOKUP(…)) returns an error; and
Returns the result of that expression (VLOOKUP(…)) otherwise.
In other words, IFERROR does the following:
Checks an expression (VLOOKUP(…)).
If VLOOKUP(…) returns an error, IFERROR returns the value you specify (ValueIfError).
If VLOOKUP(…) doesn't return an error, IFERROR returns the result of that formula.
Item: VLOOKUP(…)
The value argument of the IFERROR function (VLOOKUP(…)) is a value, formula, expression or reference that Excel checks for errors.
If VLOOKUP(….) doesn't return an error, IFERROR returns the result of that formula. For the explanation of this VLOOKUP function, please refer to the appropriate section in this Tutorial.
One of the most common errors returned by the VLOOKUP function is #N/A. The VLOOKUP function usually returns an #N/A error when you either:
Give an inappropriate value (including a value that isn't found in the first column of LookupTable) for the lookup_value argument (LookupValue); or
Use the function to locate a value in a table (LookupTable) that isn't properly sorted.
Item: ValueIfError
The value_if_error argument of the IFERROR function (ValueIfError) is the value, formula, expression or reference that Excel returns if the value argument of the IFERROR function (VLOOKUP(…)) evaluates to an error.
IFERROR VLOOKUP formula example
The worksheet formula below carries out an exact match VLookup and handles possible errors (with IFERROR vs. IFNA), as follows:
LookupValue: The value specified in cell M8 ($M$8).
LookupTable: The lookup table in cells A8 to E57 ($A$8:$E$57).
ColumnIndex: The column number specified in column K (K10).
RangeLookup: FALSE.
ValueIfError: The string “Sales Manager not found” (“Sales Manager not found”).
=IFERROR(VLOOKUP($M$8,$A$8:$E$57,K10,FALSE),"Sales Manager not found")
Effects of using IFERROR VLOOKUP formula example
The following images illustrate the results returned by the IFERROR VLOOKUP formula that carries out a VLookup that handles possible errors (with IFERROR vs. IFNA).
The image below displays the LookupTable.
The image below displays the results returned by IFERROR VLOOKUP. As expected, the formula in cell M10 does the following:
Looks for the value (LookupValue) specified in cell M8 (Shawn Brooks) in the first column (column A) of the lookup table (LookupTable).
The LookupValue (Shawn Brooks) isn't found in the first column of LookupTable. Therefore, the IFERROR VLOOKUP formula returns “Sales Manager not found”.
Notice the difference between the result returned by the IFERROR VLOOKUP formula that handles errors (cell M10) and the result returned by the regular VLOOKUP formula in cell M9 (#N/A).
#5: ISERROR
ISERROR formula
To check whether an expression returns an error (with the ISERROR function), use a formula with the following structure:
=ISERROR(Value)
ISERROR process
To check whether an expression returns an error (with the ISERROR function), specify the expression you want to check for errors (Value).
ISERROR formula explanation
Item: ISERROR
The ISERROR function:
Tests whether an expression (Value) returns an error; and
Returns:
TRUE if Value returns an error; or
FALSE otherwise.
The ISERROR function identifies the following errors:
#N/A.
#VALUE!
#REF!
#DIV/0!
#NUM!
#NAME?
#NULL!
Item: Value
The value argument of the ISERROR function (Value) is a value, formula, expression or reference that Excel checks for errors.
ISERROR formula example
The worksheet formulas below check whether an expression returns an error (with the ISERROR function),where Value is the quotient obtained by dividing:
The value specified in column G (G42 to G46); by
The value specified in column F (F42 to F46).
No.
ISERROR formula
1
=ISERROR(G42/F42)
2
=ISERROR(G43/F43)
3
=ISERROR(G44/F44)
4
=ISERROR(G45/F45)
5
=ISERROR(G46/F46)
Effects of using ISERROR formula example
The following image illustrates the results returned by the ISERROR formula that checks whether an expression returns an error. As expected, the formulas (in cells H42 to H46):
Check an expression (Value) for errors; and
Return the following:
TRUE: If Value returns an error.
FALSE: If Value doesn't return an error.
Notice the difference between the result returned by the ISERROR formula that checks whether an expression returns an error in cell H46 and the result returned by the regular formula (without ISERROR) in cell H41.
#6: IF ISERROR
IF ISERROR formula
To handle possible errors (with IF ISERROR vs. IFERROR), use a formula with the following structure:
=IF(ISERROR(Value),ValueIfError,Value)
IF ISERROR process
To handle possible errors (with IF ISERROR vs. IFERROR), follow these steps:
Specify the expression you want to check for errors (Value).
Test whether Value returns an error (ISERROR) and specify (IF) that:
If Value returns an error, another value (ValueIfError) is returned; and
In this VBA Tutorial, you learn how to find the last row or column with macros.
This VBA Last Row or Last Column Tutorial is accompanied by an Excel workbook containing the data and macros I use in the examples below. You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter.
Use the following Table of Contents to navigate to the section you're interested in.
Related Excel VBA and Macro Tutorials
The following VBA and Macro Tutorials may help you better understand and implement the contents below:
General VBA constructs and structures:
Learn the basics about working with macros and VBA here.
Learn the basic terms you should know to start working with VBA here.
#1: Find the Last Row in a Column Before an Empty Cell
VBA Code to Find the Last Row in a Column Before an Empty Cell
To find the last row in a column before an empty cell with VBA, use a statement with the following structure:
LastRow = CellInColumn.End(xlDown).Row
Process to Find the Last Row in a Column Before an Empty Cell
To find the last row in a column before an empty cell with VBA, follow these steps:
Identify a cell in the column whose last row (before an empty cell) you want to find (CellInColumn). This cell should be located above the empty cell.
Move down the column to the end of the region (End(xlDown)). This takes you to the cell immediately above the empty cell.
Obtain the row number of that last cell immediately above the empty cell (Row).
Assign the number of the last row to a variable (LastRow = …).
VBA Statement Explanation
Item: LastRow
Variable of the Long data type to which you assign the number of the last row found by the macro (CellInColumn.End(xlDown).Row).
Item: =
The assignment operator (=) assigns the number of the last row found by the macro (CellInColumn.End(xlDown).Row) to LastRow.
Item: CellInColumn
Range object representing a cell in the column whose last row (before an empty cell) you want to find. The cell represented by Range should be located above the empty cell.
You can usually work with, among others, the following properties to refer to this Range object:
Worksheet.Range.
Worksheet.Cells.
Item: End(xlDown)
The Range.End property returns a Range object representing the cell at the end of the region containing the source range (CellInColumn).
When searching for the last row in a column (before an empty cell), set the Direction parameter of the Range.End property to xlDown. xlDown specifies the direction in which to move (down).
CellInColumn.End(xlDown) is the equivalent to pressing the “Ctrl+Down Arrow” keyboard shortcut when CellInColumn is the active cell.
Item: Row
The Range.Row property returns the number of the first row of the first area in the source range.
When searching for the last row in a column (before an empty cell), the source range is the cell immediately above that empty cell (CellInColumn.End(xlDown)). Therefore, the Range.Row property returns the number of that row immediately above the applicable empty cell.
Macro Example to Find the Last Row in a Column Before an Empty Cell
The following macro (User-Defined Function) example finds the last row (before an empty cell) in the column containing the cell passed as argument (myCell).
Function lastRowInColumnBeforeBlank(myCell As Range) As Long
'Source: https://powerspreadsheets.com/
'finds the last row (before an empty cell) in the column containing myCell
'For further information: https://powerspreadsheets.com/vba-last-row-column/
'find the last row (before an empty cell) in the column containing myCell
lastRowInColumnBeforeBlank = myCell.End(xlDown).Row
End Function
Effects of Executing Macro Example to Find the Last Row in a Column Before an Empty Cell
The following image illustrates the effects of using the macro (User-Defined Function) example. As expected, Excel returns the number (16) of the last row before an empty cell (A17) in the column containing the cell passed as argument (A6).
#2: Find the Last Row Among Several Columns Before Empty Cells
VBA Code to Find the Last Row Among Several Columns Before Empty Cells
To find the last row (furthest down, before empty cells) among several columns with VBA, use a macro with the following statement structure:
OverallLastRow = 0
With Range
For Counter = .Column To .Columns(.Columns.Count).Column
ColumnLastRow = .Parent.Cells(.Row, Counter).End(xlDown).Row
If ColumnLastRow > OverallLastRow Then OverallLastRow = ColumnLastRow
Next Counter
End With
Process to Find the Last Row Among Several Columns Before Empty Cells
To find the last row (furthest down, before empty cells) among several columns with VBA, follow these steps:
Initialize the variable that will hold the number of the overall (across all columns) last row by setting it to 0 (OverallLastRow = 0).
Identify a cell range containing cells in the (several) columns you consider when searching for the last row before empty cells (With Range | End With). This cell range should begin above the empty cells.
Loop through each column in Range (For Counter = .Column To .Columns(.Columns.Count).Column | Next Counter).
Identify a cell in the column you're currently working with (.Parent.Cells(.Row, Counter)).
Move down the column to the end of the region (End(xlDown)). This takes you to the cell immediately above the empty cell in that column.
Obtain the row number of that last cell immediately above the empty cell (Row).
Assign the number of the last row in the column you're working with to a variable (ColumnLastRow = …).
Test if the number of the last row in the column you're working with (ColumnLastRow) is larger than the value currently held by the variable (OverallLastRow) that holds the overall (across all columns) last row number (If ColumnLastRow > OverallLastRow).
If the number of the last row in the column you're working with (ColumnLastRow) is larger than the value currently held by the variable (OverallLastRow) that holds the overall (across all columns) last row number, update the value held by the variable (Then OverallLastRow = ColumnLastRow).
VBA Statement Explanation
Line #1: OverallLastRow = 0
OverallLastRow is a variable of the Long data type to which you assign the number of the last row (among several columns) found by the macro.
The assignment operator (=) initializes OverallLastRow by assigning the value of 0.
Lines #2 and #7: With Range| End With
The With… End With statement executes a series of statements (lines #3 through #6) on a single object (Range).
Range is a Range object representing a cell range containing cells in the (several) columns you consider when searching for the last row (before empty cells). This cell range should begin above those empty cells.
You can usually work with, among others, the Worksheet.Range property to refer to this Range object.
Lines #3 and #6: For Counter = .Column To .Columns(.Columns.Count).Column | Next Counter
Item: For Counter = … To … | Next Counter
The For… Next statement repeats a group of statements (lines #4 and #5) a specific number of times (.Column To .Columns(.Columns.Count).Column).
Counter is the loop counter. If you explicitly declare a variable to represent Counter, you can usually declare it as of the Long data type.
The following are the initial and final values of Counter:
Initial value: The number of the first column in Range (.Column).
Final value: The number of the last column in Range (.Columns(.Columns.Count).Column).
Therefore, the For… Next statement loops through all the columns in Range.
Item: .Column
The Range.Column property returns the number of the first column of the first area in the source range (Range).
When searching for the last row among several columns (before empty cells), you use the Range.Column property twice, as follows:
.Column returns the number of the first column in Range.
.Columns(.Columns.Count).Column returns the number of the last column in Range.
Item: .Columns(.Columns.Count)
The Range.Columns property returns a Range object representing the columns in the source range (Range).
The Range.Count property returns the number of objects in a collection. When searching for the last row among several columns (before empty cells), the Range.Count property returns the number of columns in the source range (.Columns).
Therefore:
.Columns returns a Range object representing all columns in Range.
.Columns.Count returns the number of columns in Range.
.Columns.Count is used as an index number of the Range.Columns property (.Columns(.Columns.Count)). In other words, .Columns(.Columns.Count) refers to the column (within Range) whose index number is equal to the number of columns in Range (.Columns.Count). This is the last column in Range.
Line #4: ColumnLastRow = .Parent.Cells(.Row, Counter).End(xlDown).Row
Item: ColumnLastRow
Variable of the Long data type to which you assign the number of the last row in the current column (column number Counter) found by the macro (.Parent.Cells(.Row, Counter).End(xlDown).Row).
Item: =
The assignment operator (=) assigns the number of the last row found by the macro in the current column (.Parent.Cells(.Row, Counter).End(xlDown).Row) to ColumnLastRow.
Item: Parent
The Range.Parent property returns the parent object of the source range (Range).
When searching for the last row among several columns, the Range.Parent property returns a Worksheet object representing the worksheet that contains Range.
Item: Cells(.Row, Counter)
The Worksheet.Cells property returns a Range object representing all the cells in the worksheet. If you specify the row and column index, the Worksheet.Cells property returns a Range object representing the cell at the intersection of the specified row and column.
When searching for the last row among several columns (before empty cells):
.Row is the row index. The Range.Row property returns the number of the first row of the first area in the source range (Range).
Counter is the column index and loop counter of the For… Next statement that iterates through all the columns in Range.
Therefore, the Worksheet.Cells property returns a Range object representing the cell at the intersection of:
The first row of Range (.Row); and
The column through which the For… Next loop is currently iterating (Counter).
Item: End(xlDown)
The Range.End property returns a Range object representing the cell at the end of the region containing the source range (Cells(.Row, Counter)).
When searching for the last row among several columns (before empty cells), set the Direction parameter of the Range.End property to xlDown. xlDown specifies the direction in which to move (down).
.Parent.Cells(.Row, Counter).End(xlDown) is the equivalent to pressing the “Ctrl+Down Arrow” keyboard shortcut when .Parent.Cells(.Row, Counter) is the active cell.
Item: Row
The Range.Row property returns the number of the first row of the first area in the source range.
When searching for the last row in a column (column number Counter, before an empty cell), the source range is the cell immediately above that empty cell (.Parent.Cells(.Row, Counter).End(xlDown)). Therefore, the Range.Row property returns the number of that row immediately above the applicable empty cell.
Line #5: If ColumnLastRow > OverallLastRow Then OverallLastRow = ColumnLastRow
Item: If … Then …
The If… Then… statement conditionally executes a group of statements (OverallLastRow = ColumnLastRow), depending on the value of an expression (ColumnLastRow > OverallLastRow).
Item: ColumnLastRow > OverallLastRow
Condition of the If… Then… statement, which evaluates to True or False as follows:
True if ColumnLastRow is larger than (>) OverallLastRow.
False otherwise.
Item: OverallLastRow = ColumnLastRow
Statement that is executed if the condition tested by the If… Then… statement (ColumnLastRow > OverallLastRow) returns True.
The assignment operator (=) assigns the value held by ColumnLastRow to OverallLastRow.
Macro Example to Find the Last Row Among Several Columns Before Empty Cells
The following macro (User-Defined Function) example finds the last row (before empty cells) among the columns containing the cell range passed as argument (myRange).
Function lastRowInSeveralColumnsBeforeBlank(myRange As Range) As Long
'Source: https://powerspreadsheets.com/
'finds the last row (before empty cells) in the columns containing myRange
'For further information: https://powerspreadsheets.com/vba-last-row-column/
'declare variables to represent loop counter and hold value of last row in current column
Dim iCounter As Long
Dim iColumnLastRow As Long
'initialize variable that holds the number of the (overall) last row across several columns
lastRowInSeveralColumnsBeforeBlank = 0
With myRange
'loop through each column in myRange
For iCounter = .Column To .Columns(.Columns.Count).Column
'find last row (before an empty cell) in the current column
iColumnLastRow = .Parent.Cells(.Row, iCounter).End(xlDown).Row
'if the number of the last row in the current column is larger than the row number held by lastRowInSeveralColumnsBeforeBlank, update value held by lastRowInSeveralColumnsBeforeBlank
If iColumnLastRow > lastRowInSeveralColumnsBeforeBlank Then lastRowInSeveralColumnsBeforeBlank = iColumnLastRow
Next iCounter
End With
End Function
Effects of Executing Macro Example to Find the Last Row Among Several Columns Before Empty Cells
The following image illustrates the effects of using the macro (User-Defined Function) example. As expected, Excel returns the number (17) of the last row before empty cells (A14, B15, C16, D17, E18) among the columns containing the cell range passed as argument (A6 to E6).
#3: Find the Last Row in a Column
VBA Code to Find the Last Row in a Column
To find the last row in a column with VBA, use a macro with the following statement structure:
With CellInColumn.Parent
LastRow = .Cells(.Rows.Count, CellInColumn.Column).End(xlUp).Row
End With
Process to Find the Last Row in a Column
To find the last row in a column with VBA, follow these steps:
Identify a cell in the column whose last row you want to find (CellInColumn) and the worksheet containing this cell (CellInColumn.Parent).
Identify the last worksheet cell in the column containing CellInColumn (.Cells(.Rows.Count, CellInColumn.Column)).
Move up the column to the end of the region (End(xlUp)). This takes you to the last cell in the column.
Obtain the row number of that last cell in the column (Row).
Assign the number of the last row to a variable (LastRow = …).
VBA Statement Explanation
Lines #1 and #3: With CellInColumn.Parent | End With
Item: With … | End With
The With… End With statement executes a series of statements (line #2) on a single object (CellInColumn.Parent).
Item: CellInColumn
Range object representing a cell in the column whose last row you want to find.
You can usually work with, among others, the following properties to refer to this Range object:
Worksheet.Range.
Worksheet.Cells.
Item: Parent
The Range.Parent property returns the parent object of the source range (CellInColumn).
When searching for the last row in a column, the Range.Parent property returns a Worksheet object representing the worksheet that contains CellInColumn.
Line #2: LastRow = .Cells(.Rows.Count, CellInColumn.Column).End(xlUp).Row
Item: LastRow
Variable of the Long data type to which you assign the number of the last row found by the macro (.Cells(.Rows.Count, CellInColumn.Column).End(xlUp).Row).
Item: =
The assignment operator (=) assigns the number of the last row found by the macro (.Cells(.Rows.Count, CellInColumn.Column).End(xlUp).Row) to LastRow.
Item: .Cells(.Rows.Count, CellInColumn.Column)
The Worksheet.Cells property returns a Range object representing all the cells in the worksheet. If you specify the row and column index, the Worksheet.Cells property returns a Range object representing the cell at the intersection of the specified row and column.
When searching for the last row in a column:
.Rows.Count is the row index and returns the number of the last row in the worksheet (CellInColumn.Parent). For these purposes:
The Worksheet.Rows property returns a Range object representing all rows on the worksheet (CellInColumn.Parent).
The Range.Count property returns the number of objects in a collection. When searching for the last row in a column, the Range.Count property returns the number of rows in the worksheet (CellInColumn.Parent).
CellInColumn.Column is the column index and returns the number of the column containing CellInColumn. For these purposes, the Range.Column property returns the number of the first column of the first area in the source range (CellInColumn).
Therefore, the Worksheet.Cells property returns a Range object representing the cell at the intersection of:
The last row of the worksheet (.Rows.Count); and
The column containing CellInColumn (CellInColumn.Column).
Item: End(xlUp)
The Range.End property returns a Range object representing the cell at the end of the region containing the source range (.Cells(.Rows.Count, CellInColumn.Column)).
When searching for the last row in a column, set the Direction parameter of the Range.End property to xlUp. xlUp specifies the direction in which to move (up).
.Cells(.Rows.Count, CellInColumn.Column).End(xlUp) is the equivalent to pressing the “Ctrl+Up Arrow” keyboard shortcut when Cells(.Rows.Count, CellInColumn.Column) is the active cell.
Item: Row
The Range.Row property returns the number of the first row of the first area in the source range.
When searching for the last row in a column, the source range is the last cell in the column (.Cells(.Rows.Count, CellInColumn.Column).End(xlUp)). Therefore, the Range.Row property returns the number of that last row.
Macro Example to Find the Last Row in a Column
The following macro (User-Defined Function) example finds the last row in the column containing the cell passed as argument (myCell).
Function lastRowInColumn(myCell As Range) As Long
'Source: https://powerspreadsheets.com/
'finds the last row in the column containing myCell
'For further information: https://powerspreadsheets.com/vba-last-row-column/
'identify worksheet containing myCell
With myCell.Parent
'find the last row in the column containing myCell
lastRowInColumn = .Cells(.Rows.Count, myCell.Column).End(xlUp).Row
End With
End Function
Effects of Executing Macro Example to Find the Last Row in a Column
The following image illustrates the effects of using the macro..
My main purpose at Power Spreadsheets is to help you become an Excel Power User. I work (and will continue to work) very hard to achieve this purpose.
However, Excel is a massive topic. Therefore, I'll probably won't be able to cover absolute everything about Excel.
I'm also aware that I don't have all the answers. I'm constantly learning new things about Excel.
This Resources List is my attempt to gather and organize as many useful Excel resources as possible. Much of my knowledge of Excel comes from some of the materials and experts I share here. I hope they help you as much as they've helped me.
This post includes values, such as Alexa and Amazon Best Sellers Rank. These values change from time to time. I specify the period during which these values were gathered at the beginning of each section
Use the following Table of Contents to navigate to the section you're interested in.
Some of the links in this Excel Resources List are affiliate links, which means that if you choose to make a purchase, I will earn a commission. This commission comes at no additional cost to you.
I visit websites before including them in this Resources List. I try to avoid listing spam, low quality or dangerous websites.
However, due to the sheer amount of resources I list below, I haven't thoroughly audited and/or used all of them. Furthermore, resources may have been modified since the last time I visited the relevant website. The titles, names, descriptions or similar that appear below are, generally, those set by the creators of the relevant resource (not me) within the relevant HTML code or similar. Please make sure to carry out your own due diligence prior to taking action, such as visiting, buying or downloading anything. For further information regarding this, please refer to the applicable legal documents (including without limitation the Terms and Conditions and the Limit of Liability and Disclaimer of Warranty) at the Footer below.
Official Microsoft Resources
Blogs, Forums and Websites
Analysis Services Team Blog | Information, tips, news and announcements about Azure Analysis Services (Azure AS) and SQL Server Analysis Services (SSAS) directly from the product team.: blogs.msdn.microsoft.com.
Books are organized according to their Amazon Best Sellers Rank in Books or in Paid Kindle Store (if Books isn't applicable) as of 5 June 2018.
Use the links below to visit the book's webpage in Amazon. PowerSpreadsheets.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.
In this Excel XOR Tutorial, you learn how to use the XOR function in your worksheet formulas for purposes of returning a logical Exclusive Or of the applicable arguments.
The Excel XOR Tutorial is accompanied by an Excel workbook containing the data and formulas I use in the examples below. You can get immediate free access to this example workbook by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.
Use the following Table of Contents to navigate to the section you're interested in.
Related Excel Tutorials
The following Tutorials may help you better understand and implement the contents below:
Learn how to use worksheet functions in macros here.
You can find additional Tutorials in the Archives.
#1: XOR with 2 conditions
XOR with 2 conditions formula
To use the XOR function with 2 conditions in Excel, use a formula with the following structure:
=XOR(Condition1,Condition2)
XOR with 2 conditions process
To use the XOR function with 2 conditions in Excel, specify 2 conditions (Condition1 and Condition2).
XOR with 2 conditions formula explanation
Item: XOR
The XOR function returns an Exclusive Or of the 2 conditions (Condition1 and Condition2). In other words, the XOR function does the following:
Carries out 2 conditional tests (Condition1 and Condition2); and
Returns TRUE if a single argument (Condition1 or Condition2) is TRUE, and FALSE otherwise. The following truth table illustrates the possible results returned by the XOR function when working with 2 conditions:
Condition1
Condition2
XOR(Condition1,Condition2)
TRUE
TRUE
FALSE
TRUE
FALSE
TRUE
FALSE
TRUE
TRUE
FALSE
FALSE
FALSE
Item: Condition1 and Condition2
The Logical1 and Logical2 arguments of the XOR function (Condition1 and Condition2) are the conditions you want to test. The following are the main requirements that apply to these arguments:
The arguments may be logical values, expressions, arrays, or references.
The arguments must return a logical value (either TRUE or FALSE). When an argument of the XOR function is an array or reference, the applicable array or reference should contain the logical values or the expressions that are evaluated to logical values.
If you use an array or reference as argument(s), and it contains text or empty cells, those value are ignored.
If the arguments don't contain logical values, the XOR function returns an error (#VALUE!).
XOR with 2 conditions formula example
The worksheet formulas below return an Exclusive Or (XOR) of 2 conditions as follows:
Condition1: The logical value specified in column A (A6 to A9).
Condition2: The logical value specified in column B (B6 to B9).
No.
XOR formula
1
=XOR(A6:B6)
2
=XOR(A7:B7)
3
=XOR(A8:B8)
4
=XOR(A9:B9)
Effects of using XOR with 2 conditions formula example
The following image illustrates the results returns by the XOR formula when working with 2 conditions. As expected, the formulas (in cells C6 to C9) return the following logical values:
TRUE: If a single argument (Condition1 or Condition2) is TRUE (cells C7 and C8).
False: Otherwise, as follows:
If both arguments are TRUE (cell C6); or
If both arguments are FALSE (cell C9).
#2: XOR with more than 2 conditions
XOR with more than 2 conditions formula
To use the XOR function with more than 2 conditions in Excel, use a formula with the following structure:
=XOR(ConditionList)
XOR with more than 2 conditions process
To use the XOR function with more than 2 conditions in Excel, specify the conditions you want to consider (ConditionList).
XOR with more than 2 conditions formula explanation
Item: XOR
The XOR function returns an Exclusive Or of the conditions you pass as arguments (ConditionList). In other words, the XOR function does the following:
Carries out as many conditional tests as required (ConditionList); and
Returns TRUE or FALSE as follows:
TRUE: If an odd number of conditional tests return TRUE.
FALSE: If an even number of conditional tests return TRUE.
Item: ConditionList
The Logical1, Logical2, … Logical# arguments of the XOR function (ConditionList) are the conditions you want to test. The following are the main requirements that apply to these arguments:
The first argument (Logical1) is required. Subsequent arguments (Logical2, …, Logical#) are optional. XOR accepts a maximum of 254 arguments.
The arguments may be logical values, expressions, arrays, or references.
The arguments must return a logical value (either TRUE or FALSE). When an argument of the XOR function is an array or reference, the applicable array or reference should contain the logical values or the expressions that are evaluated to logical values.
If you use an array or reference as argument(s), and it contains text or empty cells, those value are ignored.
If the arguments don't contain logical values, the XOR function returns an error (#VALUE!).
XOR with more than 2 conditions formula example
The worksheet formulas below return an Exclusive Or (XOR) of 5 conditions as follows:
Condition1: The logical value specified in column A (A6 to A37).
Condition2: The logical value specified in column B (B6 to B37).
Condition3: The logical value specified in column C (C6 to C37).
Condition4: The logical value specified in column D (D6 to D37).
Condition5: The logical value specified in column E (E6 to E37).
No.
XOR formula
1
=XOR(A6:E6)
2
=XOR(A7:E7)
3
=XOR(A8:E8)
4
=XOR(A9:E9)
5
=XOR(A10:E10)
…
…
32
=XOR(A37:E37)
Effects of using XOR with more than 2 conditions formula example
The following image illustrates the results returns by the XOR formula when working with more than 2 conditions. As expected, the formulas (in cells F6 to F37) return the following logical values:
TRUE: If an odd number of arguments (columns A through E) is TRUE.
False: If an even number of arguments (columns A through E) is FALSE.
The image below contains a helper column (H) which displays the number of arguments that are TRUE in each row.
#3: XOR alternative to return TRUE when only 1 condition is TRUE
XOR alternative to return TRUE when only 1 condition is TRUE formula
Excel's XOR function returns TRUE if an odd number of conditional tests returns TRUE. In some contexts, however, XOR is defined as a function that returns TRUE if only 1 of its arguments returns TRUE.
To create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE (regardless of the number of conditions you test), use a formula with the following structure:
=COUNTIF(ConditionListRange,TRUE)=1
XOR alternative to return TRUE when only 1 condition is TRUE process
To create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE (regardless of the number of conditions you test), follow these steps:
Refer to the cell range containing the conditions you want to consider (ConditionListRange).
Count the number of cells that return TRUE (COUNTIF(ConditionListRange,TRUE)).
Test whether the number of conditions that return TRUE equals 1 (COUNTIF(…)=1)
XOR alternative to return TRUE when only 1 condition is TRUE formula explanation
Item: COUNTIF
The COUNTIF function counts the number of cells in a range that meet a single criterion.
Item: ConditionListRange
The Range argument of the COUNTIF function (ConditionListRange) is the cell range containing the conditions you want to test.
When you create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE, the cells in ConditionListRange should contain:
Logical values (either TRUE or FALSE); or
Expressions that evaluate to logical values.
Item: TRUE
The Criteria argument of the COUNTIF function (TRUE) is the condition that defines the cells in ConditionListRange that COUNTIF counts.
When you set the Criteria argument of the COUNTIF function to TRUE, COUNTIF returns the number of cells in ConditionListRange that return TRUE.
Item: =1
The equal to operator (=) does the following:
Compares 2 values; and
Returns TRUE or FALSE as follows:
TRUE: If the compared values are equal.
FALSE: If the compared values aren't equal.
When you create an alternative to the XOR function that returns TRUE when only 1 condition is TRUE, = compares the following 2 values:
The number of cells in ConditionListRange that return TRUE, as returned by the COUNTIF function (COUNTIF(ConditionListRange,TRUE)); and
The number 1.
Therefore, = returns TRUE or FALSE as follows:
TRUE: If only 1 cell in ConditionListRange returns TRUE.
FALSE: Otherwise. In other words, if either:
No cell in ConditionListRange returns TRUE; or
2 or more cells in ConditionListRange return TRUE.
XOR alternative to return TRUE when only 1 condition is TRUE formula example
The worksheet formulas below return TRUE when only 1 out of 5 conditions is TRUE as follows:
Condition1: The logical value specified in column A (A6 to A37).
Condition2: The logical value specified in column B (B6 to B37).
Condition3: The logical value specified in column C (C6 to C37).
Condition4: The logical value specified in column D (D6 to D37).
Condition5: The logical value specified in column E (E6 to E37).
No.
XOR alternative formula
1
=COUNTIF(A6:E6,TRUE)=1
2
=COUNTIF(A7:E7,TRUE)=1
3
=COUNTIF(A8:E8,TRUE)=1
4
=COUNTIF(A9:E9,TRUE)=1
5
=COUNTIF(A10:E10,TRUE)=1
…
…
32
=COUNTIF(A37:E37,TRUE)=1
Effects of using XOR alternative to return TRUE when only 1 condition is TRUE formula example
The following image illustrates the results returns by the alternative to the XOR formula that returns TRUE when only 1 condition is TRUE. As expected, the formulas (in cells I6 to I37) return the following logical values:
TRUE: If only 1 argument (columns A through E) is TRUE.
FALSE: Otherwise. In other words, if either:
No argument (columns A through E) is TRUE; or
2 or more arguments (columns A through E) are TRUE.
The image below contains a helper column (H) which displays the number of arguments that are TRUE in each row.
Learn more about working with the XOR function in Excel
Workbook example used in this Excel XOR Tutorial
You can get immediate free access to the example workbook that accompany this Excel XOR Tutorial by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.
References to constructs used in this Excel XOR Tutorial
Use the following links to visit the appropriate webpage in the Excel help center:
Count the number of cells in a cell range that return TRUE with the COUNTIF function.
Test whether the number of cells (that are TRUE) returned by the COUNTIF function is equal to 1 with the equal to (=) operator.
Books about Excel and the Excel XOR function
Use the following links to visit the book's webpage in Amazon. PowerSpreadsheets.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.
In this VBA Tutorial, you learn how to refer to, and work with, sheets and worksheets in macros. This includes:
How to refer to all sheets in a workbook.
How to refer to all worksheets in a workbook.
How to refer to the active sheet.
How to refer to a sheet by its index number.
How to refer to a worksheet by its index number.
How to refer to a sheet by its name.
How to refer to a worksheet by its name.
How to refer to a sheet by its code name.
How to refer to several sheets.
How to refer to several worksheets.
How to loop through all sheets in a workbook with the For Each… Next loop.
How to loop through all worksheets in a workbook with the For Each… Next loop.
How to loop through all sheets in a workbook with the For… Next loop.
How to loop through all worksheets in a workbook with the For… Next loop.
How to loop through all sheets in a workbook in reverse order.
How to loop through all worksheets in a workbook in reverse order.
This VBA Tutorial is accompanied by an Excel workbook containing the macros I use in the examples below. You can get immediate free access to this example workbook by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.
The following VBA and Macro Tutorials may help you better understand and implement the contents below:
You can find additional VBA and Macro Tutorials in the Archives.
#1: Refer to all sheets in workbook
VBA code to refer to all sheets in workbook
To refer to all sheets in a workbook with VBA, use an object reference with the following structure:
Workbook.Sheets
Process to refer to all sheets in workbook
To refer to all sheets in a workbook with VBA, follow these steps:
Identify the workbook containing the sheets (Workbook).
Refer to the Sheets collection representing all sheets in Workbook (Sheets).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the sheets you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
Application.ActiveWorkbook.
Application.ThisWorkbook.
Application.Workbooks.
Item: Sheets
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
Chart objects, where each Chart object represents an individual chart sheet; or
Worksheet objects, where each Worksheet object represents an individual worksheet.
Macro example to refer to all sheets in workbook
The following macro example displays a message box (MsgBox) with the number of sheets (Sheets.Count) in the workbook where the macro is stored (ThisWorkbook).
Sub referToSheetsCollection()
'source: https://powerspreadsheets.com/
'displays a message box with the number of sheets in this workbook
'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/
'display message box with number of sheets in this workbook
MsgBox ThisWorkbook.Sheets.Count
End Sub
Effects of executing macro example to refer to all sheets in workbook
The following GIF illustrates the results of executing the macro example. The workbook where the macro is stored contains 5 worksheets (Sheet1 through Sheet5) and 5 chart sheets (Chart1 through Chart5). Therefore, Excel displays a message box with the number 10.
#2: Refer to all worksheets in workbook
VBA code to refer to all worksheets in workbook
To refer to all worksheets in a workbook with VBA, use an object reference with the following structure:
Workbook.Worksheets
Process to refer to all worksheets in workbook
To refer to all worksheets in a workbook with VBA, follow these steps:
Identify the workbook containing the worksheets (Workbook).
Refer to the Sheets collection representing all worksheets in Workbook (Worksheets).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the worksheets you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
Application.ActiveWorkbook.
Application.ThisWorkbook.
Application.Workbooks.
Item: Worksheets
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
Macro example to refer to all worksheets in workbook
The following macro example displays a message box (MsgBox) with the number of worksheets (Worksheets.Count) in the workbook where the macro is stored (ThisWorkbook).
Sub referToWorksheetsCollection()
'source: https://powerspreadsheets.com/
'displays a message box with the number of worksheets in this workbook
'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/
'display message box with number of worksheets in this workbook
MsgBox ThisWorkbook.Worksheets.Count
End Sub
Effects of executing macro example to refer to all worksheets in workbook
The following GIF illustrates the results of executing the macro example. The workbook where the macro is stored contains 5 worksheets (Sheet1 through Sheet5). Therefore, Excel displays a message box with the number 5.
#3: Refer to active sheet
VBA code to refer to active sheet
To refer to the active sheet with VBA, use an object reference with the following structure:
Workbook.ActiveSheet
Process to refer to active sheet
To refer to the active sheet with VBA, follow these steps:
Identify the workbook containing the sheet (Workbook). If you don't identify Workbook, VBA works with the active workbook.
Refer to the active sheet in Workbook (ActiveSheet).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the active sheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
Application.ActiveWorkbook.
Application.ThisWorkbook.
Application.Workbooks.
If you don't specify Workbook when referring to the active sheet with ActiveSheet, VBA works with the active workbook (the workbook on top).
Item: ActiveSheet
The ActiveSheet returns an object representing the active sheet (the sheet on top) in Workbook, as follows:
If you specify Workbook, ActiveSheet returns an object representing the active sheet in Workbook.
If you don't specify Workbook, ActiveSheet returns an object representing the active sheet in the active workbook (the workbook on top).
Macro example to refer to active sheet
The following macro example displays a message box (MsgBox) with the name (Name) of the active sheet in the active workbook (ActiveSheet).
Sub referToActiveSheet()
'source: https://powerspreadsheets.com/
'displays a message box with the name of the active sheet
'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/
'display message box with name of active sheet
MsgBox ActiveSheet.Name
End Sub
Effects of executing macro example to refer to active sheet
The following GIF illustrates the results of executing the macro example. As expected, Excel displays a message box with the name of the active sheet (Sheet1).
#4: Refer to sheet by index number
VBA code to refer to sheet by index number
To refer to a sheet by its index number with VBA, use an object reference with the following structure:
Workbook.Sheets(SheetIndexNumber)
Process to refer to sheet by index number
To refer to a sheet by its index number with VBA, follow these steps:
Identify the workbook containing the sheet (Workbook).
Identify the sheet by its index number (Sheets(SheetIndexNumber)).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the sheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
Application.ActiveWorkbook.
Application.ThisWorkbook.
Application.Workbooks.
Item: Sheets(SheetIndexNumber)
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
Chart objects, where each Chart object represents an individual chart sheet; or
Worksheet objects, where each Worksheet object represents an individual worksheet.
SheetIndexNumber is the index number of the sheet you refer to. This index number represents the position of the sheet in the tab bar of Workbook, from left to right. For these purposes, the count usually includes:
Hidden sheets; and
Both chart sheets and worksheets.
Therefore, Sheets(SheetIndexNumber) usually returns an individual Chart or Worksheet object representing the chart sheet or worksheet whose index number is SheetIndexNumber.
Macro example to refer to sheet by index number
The following macro example activates (Activate) the fifth sheet (Sheets(5)) in the workbook where the macro is stored (ThisWorkbook).
Sub referToSheetIndex()
'source: https://powerspreadsheets.com/
'activates the fifth sheet in this workbook
'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/
'activate fifth sheet in this workbook
ThisWorkbook.Sheets(5).Activate
End Sub
Effects of executing macro example to refer to sheet by index number
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Sheet1. As expected, Excel activates the fifth sheet (Chart1).
#5: Refer to worksheet by index number
VBA code to refer to worksheet by index number
To refer to a worksheet by its index number with VBA, use an object reference with the following structure:
Workbook.Worksheets(WorksheetIndexNumber)
Process to refer to worksheet by index number
To refer to a worksheet by its index number with VBA, follow these steps:
Identify the workbook containing the worksheet (Workbook).
Identify the worksheet by its index number (Worksheets(WorksheetIndexNumber)).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the worksheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
Application.ActiveWorkbook.
Application.ThisWorkbook.
Application.Workbooks.
Item: Worksheets(WorksheetIndexNumber)
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
WorksheetIndexNumber is the index number of the worksheet you refer to. This index number represents the position of the worksheet in the tab bar of Workbook, from left to right. For these purposes, the count usually:
Includes hidden worksheets; but
Doesn't include chart sheets.
Therefore, Worksheets(WorksheetIndexNumber) returns an individual Worksheet object representing the worksheet whose index number is WorksheetIndexNumber.
Macro example to refer to worksheet by index number
The following macro example activates (Activate) the first worksheet (Worksheets(1)) in the workbook where the macro is stored (ThisWorkbook).
Sub referToWorksheetIndex()
'source: https://powerspreadsheets.com/
'activates the first worksheet in this workbook
'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/
'activate first worksheet in this workbook
ThisWorkbook.Worksheets(1).Activate
End Sub
Effects of executing macro example to refer to worksheet by index number
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Sheet5. As expected, Excel activates the first worksheet (Sheet1).
#6: Refer to sheet by name
VBA code to refer to sheet by name
To refer to a sheet by its name with VBA, use an object reference with the following structure:
Workbook.Sheets("SheetName")
Process to refer to sheet by name
To refer to a sheet by its name with VBA, follow these steps:
Identify the workbook containing the sheet (Workbook).
Identify the sheet by its name (Sheets(“SheetName”)).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the sheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
Application.ActiveWorkbook.
Application.ThisWorkbook.
Application.Workbooks.
Item: Sheets(“SheetName”)
The Workbook.Sheets property returns a Sheets collection representing all sheets in Workbook. The Sheets collection can, generally, contain both:
Chart objects, where each Chart object represents an individual chart sheet; or
Worksheet objects, where each Worksheet object represents an individual worksheet.
“SheetName” is a string representing the name of the sheet you refer to, as displayed in the sheet's tab. If you explicitly declare a variable to represent “SheetName”, you can usually declare it as of the String data type.
Therefore, Sheets(“SheetName”) usually returns an individual Chart or Worksheet object representing the chart sheet or worksheet whose name is SheetName.
Macro example to refer to sheet by name
The following macro example activates (Activate) the sheet named “Chart1” (Sheets(“Chart1”)) in the workbook where the macro is stored (ThisWorkbook).
Sub referToSheetName()
'source: https://powerspreadsheets.com/
'activates the sheet named "Chart1" in this workbook
'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/
'activate Chart1 in this workbook
ThisWorkbook.Sheets("Chart1").Activate
End Sub
Effects of executing macro example to refer to sheet by name
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Sheet1. As expected, Excel activates Chart1.
#7: Refer to worksheet by name
VBA code to refer to worksheet by name
To refer to a worksheet by its name with VBA, use an object reference with the following structure:
Workbook.Worksheets("WorksheetName")
Process to refer to worksheet by name
To refer to a worksheet by its name with VBA, follow these steps:
Identify the workbook containing the worksheet (Workbook).
Identify the worksheet by its name (Worksheets(“WorksheetName”)).
VBA statement explanation
Item: Workbook
Workbook object representing the Excel workbook containing the worksheet you refer to.
You can usually work with one of the following properties to refer to this Workbook object:
Application.ActiveWorkbook.
Application.ThisWorkbook.
Application.Workbooks.
Item: Worksheets(“WorksheetName”)
The Workbook.Worksheets property returns a Sheets collection representing all worksheets in Workbook.
“WorksheetName” is a string representing the name of the worksheet you refer to, as displayed in the worksheet's tab. If you explicitly declare a variable to represent “WorksheetName”, you can usually declare it as of the String data type.
Therefore, Worksheets(“WorksheetName”) returns an individual Worksheet object representing the worksheet whose name is WorksheetName.
Macro example to refer to worksheet by name
The following macro example activates (Activate) the worksheet named “Sheet1” (Worksheets(“Sheet1”)) in the workbook where the macro is stored (ThisWorkbook).
Sub referToWorksheetName()
'source: https://powerspreadsheets.com/
'activates the worksheet named "Sheet1" in this workbook
'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/
'activate Sheet1 in this workbook
ThisWorkbook.Worksheets("Sheet1").Activate
End Sub
Effects of executing macro example to refer to worksheet by name
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Chart1. As expected, Excel activates Sheet1.
#8: Refer to sheet by code name
VBA code to refer to sheet by code name
To refer to a sheet by its code name with VBA, use the sheet's code name:
SheetCodeName
Process to refer to sheet by code name
To refer to a sheet by its code name with VBA, use the sheet's code name.
VBA statement explanation
Item: SheetCodeName
SheetCodeName is the code name of the sheet you refer to.
You can use a sheet's code name instead of an object reference (such as the ones I explain in other sections of this VBA Tutorial) returning the Chart or Sheet object you refer to.
Macro example to refer to sheet by code name
The following macro example activates (Activate) the worksheet whose code name is Sheet1 (Sheet1).
Sub referToSheetCodeName()
'source: https://powerspreadsheets.com/
'activates Sheet1 in this workbook
'for further information: https://powerspreadsheets.com/excel-vba-sheets-worksheets/
'activate Sheet1 in this workbook
Sheet1.Activate
End Sub
Effects of executing macro example to refer to sheet by code name
The following GIF illustrates the results of executing the macro example.
When the macro is executed, the active sheet is Sheet5. As expected, Excel activates Sheet1 (both the name and code name are Sheet1).
In this Power Query (Get & Transform) Tutorial for beginners, you learn how to use Power Query (Get & Transform) to automate the process of importing and consolidating data in Excel.
The following are the main topics you learn about:
What Power Query is and how it can help you.
What data sources can Power Query import data from.
How to import data from 3 commonly-used data sources (Excel workbooks, CSV and text files) with Power Query, including the following:
How to import single or multiple data sources from an Excel workbook.
How to import data from CSV or text files.
How to edit a query during the process of importing data with Power Query.
Where and how to load the data you import with Power Query.
How to combine and consolidate the data you import from different files with Power Query.
This Power Query Tutorial is accompanied by several example files containing the data, queries and connections I use in the examples below. You can get immediate free access to these example files by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.
This Power Query Tutorial was most recently updated in March 2018 with Excel Version 1802 (Build 9029.2253 Click-to-Run). Microsoft is constantly updating/modifying Power Query. If you're working with a different version of Excel/Power Query, some details (particularly menus) are different. The principles and basic processes you learn in this Power Query Tutorial, are generally applicable.
Related Tutorials
The following Tutorials may help you better understand and implement the contents below:
Learn how to convert PDF files to Excel with VBA here.
You can find additional Tutorials in the Archives.
What Power Query (Get & Transform) is
One of the first steps in the process of analyzing data in Excel is importing it. You can achieve this in several ways. Common approaches involve manually importing data or using VBA to automate the process. These approaches may not be the most appropriate in several cases.
The process of manually importing data into Excel is generally slow, repetitive, tedious and error-prone.
Macros and VBA can help you automate a lot of your data importing processes. This approach, however, requires some programming knowledge and, depending on your situation, you may need to spend time maintaining the procedures you use.
Power Query (Get & Transform) allows you to import data into Excel. Power Query offers several advantages (vs. the common approaches I list above), including the following:
Like VBA, Power Query allows you to automate the process of importing data.
However, when compared with VBA, Power Query is generally easier to work with and maintain. Power Query also tends to result in better performance (vs. VBA).
Overall, you can think of Power Query as an ETL tool. In other words, Power Query allows you to:
Extract: Use Power Query to discover and connect to a variety of data sources.
Transform: Transform the extracted data by, for example, combining or refining it.
Load: Share the transformed data.
Data sources you can connect to with Power Query (Get & Transform)
You can connect Power Query to different data sources, including the following:
Files: Excel files, Text or CSV files, XML files, and JSON files.
Databases: Microsoft SQL Server, Microsoft Access, SQL Server Analysis Services.
Other example data sources: Microsoft Exchange, Facebook, SalesForce, Excel Tables or named ranges, web pages, and OData feeds.
From a broad perspective, the 2 data source types you're most likely to work with are:
Relational databases; and
Files.
This Power Query Tutorial focuses on working with the 3 following common file types:
Excel workbooks.
CSV (comma-separated value) files.
Text files.
As you learn in the following sections, Power Query usually imports the data within a CSV or text file as follows:
Each line within the file is a row of data.
Columns are determined by the appropriate delimiter (for example, comma in CSV files or tab in tab-delimited files).
File examples for this Power Query (Get & Transform) Tutorial
You can access the example files that accompany this Power Query Tutorial by entering your email address below. Alternatively, you can access all the files that accompany my Tutorials here.
There are 3 source file examples:
Excel workbook.
CSV file.
Text file.
These source file examples contain several thousand rows with the following (fictional) sales data:
Date: Between January 1 of 2018 and December 31 of 2019.
Item: 1 of the following Microsoft devices:
Surface Pro.
Surface Laptop.
Surface Book 2.
Surface Studio.
Xbox One X.
Store: I assume there's 1 store per city listed below. I divide the cities in 4 regions (Midwest, Northeast, South and West). The source files contain 20,000 rows of data for each region.
Midwest: Chicago, Detroit and Minneapolis.
Northeast: Boston, New York and Philadelphia.
South: Atlanta, Dallas, Houston, Miami, Tampa and Washington D.C.
West: Los Angeles, Phoenix, San Diego, San Francisco and Seattle.
Units sold: Between 1 and 5.
Unit price: I assume the price remains constant regardless of the store location and date of sale:
Total sales: The product of units sold times unit price (UnitsSold x UnitPrice).
The following are the differences between the different source file examples.
Workbook: The workbook example has 4 worksheets. Each worksheet corresponds to 1 of the regions I list above (Midwest, Northeast, South or East). Additionally:
The Midwest source data is formatted as an Excel Table (myTableSource).
The cell range containing the Northeast source data is a named cell range (myNamedRangeSource).
CSV: The CSV file examples only has data corresponding to the South region. This is the same data as that in the South worksheet of the example workbook.
Text: This is a tab-delimited text file containing (only) data corresponding to the West region. This is the same data as that in the West worksheet of the example workbook.
The source data isn't formatted. Further below, I explain how you can use Power Query to format the data.
If you use the file examples that accompany this Power Query Tutorial, you can't simply refresh the data extracted by Power Query from the main workbook (which has the queries and connections). The path of the source files you download will be different from the one I specified in the workbook example. The result is a data source error.
Therefore, I suggest you recreate the queries yourself by following the step-by-step processes I describe below.
Import data with Power Query (Get & Transform)
In the following sections, I explain the basic process you can follow to import data from an Excel workbook, CSV or text file using Power Query.
The essential steps are similar. Power Query has several options and features you can use while importing data. However, these topics exceed the scope of this Power Query Tutorial for beginners.
#1: Import a single data source from a workbook with Power Query
To import a single data source from an Excel workbook with Power Query, follow these 6 steps:
Go to Ribbon > Data > Get Data > From File > From Workbook.
Power Query displays the Import Data dialog box.
Identify the source workbook and double-click on it.
Power Query displays the Navigator dialog box.
Select the data source you want to work with.
Click Load.
Example of how to import a single data source from a workbook with Power Query
The following GIF illustrates the process to import a single data source from an Excel workbook with Power Query.
#2: Import data from multiple data sources in a workbook with Power Query
To import data from multiple data sources within a workbook with Power Query, follow these 7 steps:
Go to Ribbon > Data > Get Data > From File > From Workbook.
Power Query displays the Import Data dialog box.
Identify the source workbook and double-click on it.
Power Query displays the Navigator dialog box.
Select “Select multiple items”.
Select the data sources you want to work with.
Click Load.
Example of how to import data from multiple data sources in a workbook with Power Query
The following GIF illustrates the process to import data from multiple data sources in a workbook with Power Query.
#3: Import data from a CSV file with Power Query
To import data from a CSV file to Excel with Power Query, follow these 5 steps:
Go to Ribbon > Data > Get Data > From Text/CSV.
Power Query displays the Import Data dialog box.
Identify the source CSV file and double-click on it.
Power Query displays a dialog box named after the CSV file.
Click Load.
Example of how to import data from a CSV file with Power Query
The following GIF illustrates the process to import data from a CSV file with Power Query.
#4: Import data from a text file with Power Query
To import data from a text file to Excel with Power Query, follow these 5 steps:
Go to Ribbon > Data > Get Data > From Text/CSV.
Power Query displays the Import Data dialog box.
Identify the source text file and double-click on it.
Power Query displays a dialog box named after the text file.
Click Load.
Example of how to import data from a text file with Power Query
The following GIF illustrates the process to import data from a text file with Power Query.
General considerations about the process to import data with Power Query
Begin the process of importing data with Power Query
You begin the process of creating a query with Power Query as follows:
When the source data is in an Excel workbook:
Go to Ribbon > Data > Get Data > From File > From Workbook; or
Use the keyboard shortcut “Alt, A, PN, F, W”.
When the source data is in a text or CSV file:
Go to Ribbon > Data > Get Data > From Text/CSV; or
Use the keyboard shortcut “Alt, A, FT”.
Working with the Import Data dialog box
The Import Data dialog box is similar to other dialog boxes you work with when carrying out common Excel processes, such as opening or saving a workbook. Therefore, you can do the following:
Browse to the folder where the file (workbook, CSV or text) is saved.
Double-click on the appropriate file.
The file-filtering criteria applied by Excel depend on the type of file you're working with (Excel workbook vs. CSV or text). Therefore:
When you work with an Excel workbook:
The Import Data dialog box usually displays any type of Excel file. This includes, for example, regular Excel workbooks, templates and macro-enabled workbooks.
Regardless of the precise Excel file type you work with, Power Query limits itself to working with data in cells. Therefore, items such as PivotTables, charts or macros aren't imported.
When you work with a CSV file: The Import Data dialog box displays only CSV files.
When you work with a text file: The Import Data dialog box displays only text files.
Working with the Navigator dialog box
Power Query displays the Navigator dialog box when you work with an Excel workbook. The Navigator dialog box has 3 main sections:
Left: Available data sources.
Right: Preview.
Right bottom: Load, Edit and Cancel buttons.
Available data sources
On the left side of the Navigator dialog box, Power Query lists the data sources you can select inside the workbook you're working with (selected with the Import Data dialog box).
When working with an Excel workbook, these data sources are generally 1 of the following 3:
A worksheet.
An Excel Table.
A named range. It's possible to connect Power Query to, for example, dynamic named ranges. The process to work with this type of named range, however, differs from what I describe in this Power Query Tutorial.
You can distinguish these different data sources based on the icon displayed by Power Query next to the source.
If you want to select multiple data sources from a single workbook:
Mark the checkbox next to “Select multiple items” on the top left side of the Navigator dialog box prior to selecting the data sources.
Once you enable the option to select multiple items, Power Query displays checkboxes to the left of all data sources in the workbook. Use these checkboxes to select all the data sources you want to import.
Preview
Once you select a data source from the list of available data sources, Power Query displays a preview of the data.
Use this preview to confirm that the source data you chose is correct.
Load, Edit and Cancel buttons
Use these buttons to do any of the following:
Load: Load the data. You can specify how and where the data is loaded by following the process I describe further below.
Edit: Launch the Query Editor and edit your query. You learn the basics of working with the Query Editor further below.
Cancel: Close the dialog box and cancel the process of importing data with Power Query.
Working with the dialog box named after the source CSV or text file
Power Query displays a dialog box named after the source file when you work with CSV or text files. This dialog box has 3 main sections:
Top: Drop-down menus.
Middle: Preview.
Bottom: Load, Edit and Cancel buttons.
The main difference between this dialog box and the Navigator dialog box (in a previous section) is the fact that the Navigator dialog box allows you to choose from the available data sources within a workbook.
CSV and text files contain text data only. You don't have named ranges, Excel Tables, nor multiple worksheets to choose from. Therefore, when importing data from a CSV or text file, you don't select a data source within the file.
Results of importing data with Power Query
Results when you import a single data source from a workbook, or a CSV or text file with Power Query
The results of importing data with Power Query are similar when you import data from either of the following:
A single data source from a workbook.
A CSV file.
A text file.
After you complete the appropriate process (described in previous sections), Excel does the following:
Loads the imported data to an Excel Table in a new worksheet.
Displays the Queries & Connections task pane. This task pane includes the query you just created.
Results when you import data from multiple data sources in a workbook with Power Query
After you complete the process I describe above to import data from multiple data sources in a workbook, Excel does the following:
Loads the imported data to the Data Model.
Displays the Queries & Connections task pane. This task pane has separate queries for each data source you selected.
Edit a query with Power Query
Power Query has several features that allow you to edit queries. This section introduces the topic. However, covering all options exceeds the scope of this Power Query Tutorial.
The processes I explain below build on the basic procedures to import data (from workbooks, CSV and text files) I explain in previous sections. Please refer to those sections as needed.
#1: Edit a query when importing data from a workbook with Power Query
To edit a query and import data from a workbook with Power Query, follow these 9..