Index
A
Absolute references
Additional functionality
Advanced Formula Environment (AFE)
add-in
formula creation
Home tab
LAMBDA functions
creation
import from GitHub Gist
management experience
named formulas
Name Manager in Excel
Sync names with Name Manager in Excel
AGGREGATE function
adding criteria
advantages
cell value
in Excel 2010
function_num
ignore error values in the range
ignore hidden rows
list options
sales data
syntax options
Ampersand character
AND and OR functions
AND function
Australian financial year
AVERAGEIFS function
B
BYCOL function
BYROW function
C
#CALC! error
Case of text
CEILING function
CEILING.MATH function
CEILING rounds
Cell F2
Cell references
absolute
definition
making absolute
mixed
relative reference
sheet
workbook
Chart data series
Chart labels
Charts
CHOOSECOLS function
absolute column selection
MATCH
Nth column
reordering columns
specific columns
CHOOSE function
cell value
controls
drop-down list
ID values
list
options buttons
random sample data
syntax
user selection
CHOOSEROWS function
every Nth row
every third row
top and bottom N values
Classic SUMPRODUCT
CLEAN function
COLUMN function
COLUMNS function
Column table references
CONCATENATE function
CHAR(10)
compatibility sign
CONCAT
example
CONCAT function
Conditional formatting rules
Conditional number formatting rule
COUNTA function
COUNT function
COUNTIFS function
comparing two lists
condition
conditional rankings
definition
examples
frequency distribution range
generating unique rankings
syntax
Currencies data type
Custom formatting
Custom number formatting
D
Data types in Excel
availability
currency conversion
Data tab
Excel Ribbon
formulas
aggregation function
automatic recognition of geography data type
capital city
FIELDVALUE function
input by insert data feature
insert data fields
UI of Excel
functions
conditional SUM function
create custom data type with Power Query
FILTER function
XLOOKUP
historical stock data with STOCKHISTORY
SeeHistorical stock data with STOCKHISTORY
Data Validation
list
rules
DATEDIF function
Date formats
DATE function
Dates in Excel
calculate the difference between two dates
calendar system
convert text to date
DATE function
DATEVALUE function
VALUE function
date difference, DAYS function
DATEDIF function
DAY function
EDATE and EOMONTH
SeeEDATE and EOMONTH functions
extract date from date-time stamp
DATEVALUE function
INT function
TRUNC function
#VALUE! error return by DATEVALUE function
extract time from date-time stamp
MOD function
subtract date element
ISOWEEKNUM function
MONTH function
NETWORKDAYS function
SeeNETWORKDAYS function
NOW function
regional settings
return the week number
serial numbers
TODAY function
WEEKDAY function
week number in a month
WEEKNUM function
WORKDAY function
YEARFRAC function
YEAR function
DATEVALUE function
Defining names
rules
prefixes
scope
underscore
Delimiter
Dependent drop-down list
Discount’s range
Distinct count formula
Drop-down list
DROP function
definition
formula
uses
Dynamic array formulas
array vs. dynamic arrays
availability
charts
Conditional Formatting
criteria value
Data Validation
example
Excel version
functions
multiple values
non-dynamic version
#operator
solution
#SPILL! error
spill range
table of scores
tables
within tables
Dynamic array functions
Dynamic Chart data
Dynamic named range
E
EDATE and EOMONTH functions
arguments
contract finish dates
first working day
last day of month
last working days
number of days
returning week numbers
EDATE function
Emoji keyboard
Emojis
EOMONTH function
EXACT function
Excel features
Conditional Formatting feature
identifying duplicates, conditional formatting
preventing duplicate entries
unmatched values, between two lists
Excel formula
automatic error checking
calculating percentages
decrease a value
definition
percentage change
percentage of a value
total
calculations set to manual
cell formatted as text
cell referencing
characters
definition
errors
functions
Function Wizard
order of calculation
mathematical operations
range operations
stored as text
switched on
Excel functions
Excel window
Extract characters
after a delimiter
FIND and SEARCH functions
LEFT and RIGHT functions
middle of a text string
Extraction tasks
F, G
FIELDVALUE function
FILTER function
add calculated columns to returned array
aggregation functions
availability
#CALC! error
combine results in one cell
compare lists
COUNTIFS
names, both lists
names, either list but not both
names, one list only
return all names from both lists
dynamic array formulas
filter tool in Excel
multiple conditions
AND logic
arrays
OR logic
NETWORKDAYS.INTL
other Excel features
dependent drop-down list
interactive chart
shrinking drop-down list
return non-adjacent columns
simple example
sort
syntax
UNIQUE ignoring blanks
FIND and SEARCH functions
combining LEFT and RIGHT
extract characters after a delimiter
extract characters before a delimiter
extracting text between two characters
extract number from a text string
FIND function
Fiscal quarters
Fiscal years
Australian financial year
Indian financial year
UK financial year
FLOOR.MATH function
Format Cells
Formatting
Formula errors
FREQUENCY function
FREQUENCY/TRANSPOSE functions
H
Handling formula errors
IFERROR function
IFNA function
Historical stock data with STOCKHISTORY
availability
example
insert a stock chart
London Stock Exchange
Open-High-Low-Close stock chart
properties
return historical exchange rate data
syntax
HLOOKUP function
HSTACK function
adding calculated columns
combines two array
I, J, K
IFERROR function
IF function
case-insensitive
date values, testing
empty/blank cell
EXACT function
logical test with text values
nested IF functions
numeric value, tests
with partial text match
purpose
SEARCH function
symbol/emoji
syntax
using emojis
value if true actions
IFNA function
IFS function
INDEX function
basics
example
format
frequently occurring value
last row/column, sum values
last value row/column
MATCH/XMATCH
moving average formula
power
returning all values
returning range
syntax
two-way lookup
wildcards
XMATCH
Indian financial year
INDIRECT function
arguments
data dependent
drop-down list
example
pivot style reports
R1C1 reference
referencing other worksheets
referencing table columns
sales data
sales rep/region labels
VLOOPUP
Interactive chart
INT function
ISBLANK function
ISNUMBER function
ISOMITTED function
ISOWEEKNUM function
ISTEXT function
L
LAMBDA functions
COUNTWORDS function in Excel
define name
Excel functions
formula
LET
phases
second LAMBDA
syntax
test
TEXTBETWEEN
LAMBDA helper functions
BYCOL
BYROW
ISOMITTED
MAKEARRAY
MAP
SeeMAP function
REDUCE
SCAN
LARGE function
Last character
LEN function
LET function
benefits
formulas
SEARCH functions, formulas
speed
Line chart
Logical functions
Logical functions with Excel features
Conditional Formatting rule
Conditional Formatting with charts
Data Validation rule
Dynamic Chart data with SWITCH
Logical tests
Lookup functions
M
MAKEARRAY function
Manipulating text
MAP function
BYROW
converting values from drop-down Value
LAMBDA function
mapping text values
multiple arrays
syntax
MATCH function
compare two lists
drop-down list
named formula
VLOOKUP
MAX function
MAXIFS and MINIFS functions
MEDIAN function
Mixed reference
MODE function
MODE.MULT function
MOD function
MONTH function
MROUND function
Multicell named range
Multiple column delimiters
Multiple column XLOOKUP
Multiple named ranges
N
Name Box
Named constant
Named formulas
Named ranges
for cell E3 with worksheet-level scope
and constants in Excel
default scope
for a drop-down list
in the formula
with multiple cells
with Name Box
scope option
using the Name Box
Name Manager
apply names to existing formulas
filtering name
name, editing
names in workbook
print names
Nested IF functions
NETWORKDAYS function
with additional non-working days
calculate working days difference between two dates
conditional holiday ranges
customized NETWORKDAYS.INTL function
different non-working days by location
FILTER function return non-working dates
NETWORKDAYS.INTL function
non-working days by location
number of Fridays between two dates
syntax
whole workdays between two dates
NETWORKDAYS.INTL function
Next-level aggregation functions
SeeAGGREGATE function; SUMPRODUCT function
Non-dynamic array method
NOT function
NOW function
NUMBERVALUE function
O
OFFSET function
cell values
COUNTIFS function
examples
Last X columns
sum values, Nth column
syntax
OR function
P, Q
Paste Name window
PivotTables
Power Query
PROPER function
R
R1C1 reference
RANDARRAY function
arguments
decimal values
list at random
shuffle a list of names
syntax
RANDBETWEEN function
RAND/RANDBETWEEN functions
RANK.EQ function
REDUCE function
Referencing table elements
#REF! error
Relative named ranges
REPLACE function
REPT function
RIGHT function
ROUND function
Rounding time values
MROUND, CEILING.MATH, and FLOOR.MATH functions
to nearest 15 minutes
to nearest hour
significance
ROUNDUP function
ROW function
S
SCAN function
SEARCH function
Second LAMBDA function
SEQUENCE function
all arguments
changing row/column order
last N months by country
negative step
number series across rows
pivot style report
rows/columns arguments
series of dates
sleeping
start/step value
sum of last N months
sum the last N days
sum the top N values
sum values from alternate rows/columns
uses
Simple text extractions
SMALL function
SORTBY function
advantages
column outside
examples
multiple columns
product name, total sales
returned array
syntax
table of product sales
SORT function
distinct values
example
multiple column
specific column from table
specified column
syntax
Spilled ranges
create top N list
extracting headers, table/range
non-adjacent columns
reference from list
rows/columns
#SPILL! error
Spill operator
SUBSTITUTE function
count the number of words in a cell
final instance of the delimiter
instance_num argument
simple SUBSTITUTE example
syntax
SUM function
SUMIFS function
definition
intellisense list
multiple criteria
numeric criteria
OR expression
sum range argument
sum values between two dates
syntax
text criteria
wildcards
SUMPRODUCT function
aggregating multiple columns
classic
count values
distinct count formula
sum every Nth cell
sum the top five values
sum values
sum values, specific weekday
weighted average
SUM v2.0
complex criteria
sum based on multiple columns
sum with arrays
Super easy referencing
SWITCH function
advantages
drop-down list
in Excel 2019
expression
matching value
syntax
TRUE evaluation
T
Table formatting, range as a table
Table column
Table elements
Table references
magic @ symbol
sheet column header changes
table column and cell references absolute
table elements
Tables
vs. cell ranges
changing/removing table styles
creation
with default style
dynamic charts
dynamic lists
Go To window
list of products
naming
range of data
style
uses
working with tables
TAKE function
definition
formula
uses
tblSales data
TEXTBEFORE and TEXTAFTER functions
extract text between two characters
instance number argument
match end and if not found arguments
syntaxes
text and delimiter
TEXTBETWEEN functions
TEXT function
ampersand/CONCAT function
currency format
Custom category
date formats
Format Cells window
format codes
positive and negative value formats
syntax
TEXTJOIN function
arrays
delimiters
empty values
TEXTSPLIT function
column/row delimiter
combine text from multiple ranges
functionality
handling missing data
simple example
syntax
using multiple column delimiters
Text strings
“Text that Contains” rule
Text values
The Go To window
Threshold value
Time difference
TIME function
arguments
decimal value
formula
LET function
minute argument
number of digits
time conversion
time format
Time in Excel
calculate the difference in two times
convert text to time
data validation rules
decimal value
format
highlight approaching due dates
highlight approaching due dates within the next month
negative time handling
regional settings
sum hours over 24 hours
time difference past midnight
TIMEVALUE function
argument
date-time value conversion
DATEVALUE function
formatting time
formula
mathematical calculation
outcomes
time conversion
Title and Error message
TODAY() function
Touch keyboard
TRANSPOSE function
Tricks
TRIM function
TRIMMEAN function
average of all values
column of totals
definition
TRUE or FALSE
TRUNC function
U
UK financial year
UNIQUE function
compare columns
distinct
distinct count formula
multiple columns
non-adjacent columns
SUMIFS
syntax
Unwanted characters
CLEAN function
TRIM function
UPPER function
V
VALUE function
VLOOKUP function
aggregate values
case-sensitive
column index number
COLUMN function
COLUMNS function
definition
index row
Conditional Formatting example
definition
dynamic chart range
fake errors
look to the left
multiple column
partial match
range lookup
reasons, VLOOKUP not working
data type
exact match not specified
first column
return Nth match
unique value
versions
VSTACK function
W
WEEKDAY function
arguments
index number
rate of pay
return type options
WEEKNUM function
ISO 8601 standard
return type
syntax
week numbering
Wingdings font
WORKDAY function
availability
calculate due date from starting date
calculate first working day of month
WORKDAY.INTL function
WORKDAY.INTL function
X
XLOOKUP
arguments
availability
definition
exact match
Excel features
dependent drop-down list
return range for chart
key developments
last match
lookup value in ranges
multiple column
quantity ranges and discounts
returning multiple values
SUMIFS, dynamic sum range
syntax
three-way lookup
three-way lookup with mixed match modes
two-way lookup
types of lookups
types of range lookup
users of Excel 2021
wildcards
XMATCH functions
exact match
last match
syntax
XOR function
Y, Z
YEARFRAC function
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.