Get started with formulas

In just a few steps, you’ll be up and running with building formulas and functions in Excel: the
world’s most powerful spreadsheet app.

Let’s go >
Go back to the top by pressing CTRL+HOME. To start the tour, press CTRL+PAGE DOWN.
=A1+B1,
constants
aren’t a good
practice. Why?
Because you
can’t easily see
the constant
without
selecting the
cell and
Basics:
looking
Formulas
That can
Basics: Doing maths with Excel
You canDoingfor
Add,
can
make
maths with
it. Subtract, Excel and Divide in Excel without using any built-in functions. You just need to use some basic operators: +, -, *, /. All formulas start with an equals (=) s
Multiply
Numbers to use: Operation:
To Add, cell
contain select cell F3, type =C3+C4, then press Enter.
it hard to 1 Addition (+)
references,
change later.
ranges
To
It’s of
Subtract, You
cell can
select Add,
cell Subtract,
F4, type Multiply
=C3-C4, then
much select cell F5, type =C3*C4, then press Enter. and
pressDivide
Enter.in Excel without using any built-in 2 Subtraction (-)
To Multiply,
references,
easier to put functions. You just need to use some basic operators: +, -, *, /. All formulas start Multiplication (*)
To Divide,
operators
your constantsselect cell F6, type =C3/C4, then press Enter.
and
constants. with
The an equals (=) sign. Division (/)
in cells where
following
Take can
they a look are
be at this: change the numbers in cells C3 and C4, and watch the formula results automatically change. Power (^)
all examples of
easily
EXTRA
formulas:
and
adjusted
CREDIT: 1 YouTo
canAdd,
raise select
a cell
value to F3,
a type
power =C3+C4,
by using then
the press
carat (^) Enter.
symbol, like =A1^A2. Enter it by pressing Shift+6. In cell F7, enter =C3^C4.
Dive down for more detail TAKE A LOOK AT THIS
referenced in Change the numbers here, and
=A1+B1
Next step
your formulas.
watch the formula results
=10+20
More
Excel isabout
=SUM(A1:A10)
2up ofToindividual
madeformulas, Subtract, select
cells andcells
ranges cellgrouped
that are F4, typeinto
=C3-C4, then
rows and press Enter.
columns. Rows are numbered, and columns are lettered. There are change.
automatically over 1 million rows and 16,000 columns, an
For example:
Select the
You’ll notice
yellow cell that in our third example above, we used the SUM function. A function is a pre-built command that takes a value or values, calculates them in a certain way and retu
with
Formulasbelow.
12 with3 functions
To Multiply,
start with select cell F5,
an equals type
sign, =C3*C4,
then then name
the function pressfollows
Enter. with its arguments (the values a function uses to calculate) wrapped in parentheses.
You’ll see we
used the SUM
function with a
Some
range formula
of cells.4 explanations
To Divide, select cell F6, type =C3/C4, then press Enter.
We
=10+20didn’tis atype
formula, where 10 and 20 are constants and the + sign is the operator.
“4” or “8”
=SUM(A1:A10) is a formula, where SUM is the function name, the opening and closing parentheses contain the formula arguments, and A1:A10 is the cell range for the function.
directly into
=SUM(A1:A10,C1:C10)
the formula. is a formula, where SUM is the function name, the opening and closing parentheses contain the formula arguments, and A1:A10,C1:C10 are the cell ranges
Dive down for more detail Next step
Previous
Next
More information on the web
More about formulas, cells and ranges
Use Excel as a calculator
Overview of formulas in Excel
Excel is made up of individual cells that are grouped into rows and columns. Rows
Excel functions (by category)
are numbered, and columns are lettered. There are over 1 million rows and 16,000
Operator Operator
columns,
Excel functions and you can put formulas in any of them.
(alphabetical)
Formulas can contain cell references, ranges of cell references, operators and
constants. The following are all examples of formulas: =A1+B1 =10+20
=A1+B1
Free ExcelFormulas
training online
can contain cell references, ranges of cell references, operators and
constants. The following are all examples of formulas: =A1+B1 =10+20
=A1+B1 Cell Cell
Constant
reference reference
=10+20
=SUM(A1:A10)
You’ll notice that in our third example above, we used the SUM function. A function
is a pre-built command that takes a value or values, calculates them in a certain way Function Argument
and returns a result. For instance, the SUM function takes the cell references or
ranges you specify and totals them. In this example it takes the cells A1 to A10, and
totals them. Excel has over 400 functions, which you can explore on the Formulas =SUM(A1:A10)
tab.
A range of cells has a starting cell, colon and an ending
Formulas with functions start with an equals sign, then the function name follows cell. When you select a range of cells for a formula,
with its arguments (the values a function uses to calculate) wrapped in parentheses. Excel will automatically add the colon.
You confirm a formula by pressing Enter. Once you do that, the formula will be
calculated and the result will be displayed in the cell. To see the formula itself, you can
look at the formula bar beneath the Ribbon, or press F2 to enter Edit mode, where
you’ll see the formula in the cell. Press Enter again to finalise the formula and Function Argument
calculate the result.

Previous Next
=SUM(A1:A10,C1
A range of cells

More information on the web Opening parenthesis

Use Excel as a calculator

Overview of formulas in Excel

Excel functions (by category)

Excel functions (by category)

Excel functions (alphabetical)

Free Excel training online

 Formulas: Answers:
3
-1
2
0.5
1

EXTRA CREDIT
You can raise a value to a
power by using the carat (^)
ws and 16,000 columns, and you symbol, like =C3^C4.
can put formulas Enter
in any it
of them.
by pressing Shift+6.

m in a certain way and returns a result. For instance, the SUM function takes the cell references or ranges you specify, and totals them. In this example it takes the cells A1 to A10, and totals t
n parentheses.

ell range for the function.

C1:C10 are the cell ranges for the function separated by a comma.

GOOD TO KNOW
Operator Constants are values that you enter in cells or formulas.
While =10+20 might calculate the same result as =A1+B1,

=10+20 constants aren’t a good practice. Why? Because you can’t

easily see the constant without selecting the cell and looking
for it. That can make it hard to change later. It’s much easier
to put your constants in cells where they can be easily
adjusted and referenced in your formulas.
 GOOD TO KNOW
Constants are values that you enter in cells or formulas.
While =10+20 might calculate the same result as =A1+B1,

=10+20 constants aren’t a good practice. Why? Because you can’t

easily see the constant without selecting the cell and looking
for it. That can make it hard to change later. It’s much easier
to put your constants in cells where they can be easily
adjusted and referenced in your formulas.
Constant Constant
For example: Select the yellow cell with 12 below. You’ll see
we used the SUM function with a range of cells. We didn’t
type “4” or “8” directly into the formula.

A10) Values
4
colon and an ending
cells for a formula, 8
d the colon. 12

Commas separate multiple arguments

Argument

A10,C1:C10)
e of cells Another range of cells

Closing parenthesis. Excel will usually add this for

you when you press Enter.
cells A1 to A10, and totals them. Excel has over 400 functions, which you can explore on the Formulas tab.
DETAIL
or type =SUM(,
Double-click
then select
this
thatcell.
range You’ll
notice
with the 100the
towards
mouse, and the
end.
pressAlthough
Enter.
it’s
Thispossible
will sumto
put
the values in in
numbers
acells
formula
D3, D4, like

Introduction to functions
this,
D5 we
and don’t
D6.
Introduction to functions
recommend
Your answer it
Functions
unless
should it’s be give
170. you the ability to do a variety of things, like perform mathematical operations, look up values or even c Fruit Amount
absolutely Apples 50 enter the formula for you
Now, let’s try
necessary. ThisAutoSum. Select the cell under the column for Meat (cell G7), then go to Formulas > AutoSum > select SUM. You’ll see Excel automatically
Go Functions
to theasFormulas give you the ability to do a variety of things, like perform mathematical
tab and browse through the Function Library, where functions are listed by category, like Text, Date & Time etc. Insert20
Oranges Function will let you searc
is known a
Here’s a handy
constant, operations,
and keyboard look up values
shortcut. or cell
Select even calculate
D15, dates
then press Altand times.
=, then Let’s
Enter. tryautomatically
This a few ways to enters SUM for you. Bananas 60
it’s
When
Dive easy
down toadd
you for up
start values
typing
more awith
detail the SUM
function name function.
after you press =, Excel will launch Intellisense, which will list all of the Lemons functions starting with the letters
40 you type. When you
EXTRA
forget thatCREDIT it’s
Try theWe
there. COUNT function using any of the methods you’ve already tried. The COUNT function counts the number of cellsSUM in a range
> that contain numbers.
170
Next step
recommend 1 Under the Amount column for Fruit (cell D7), enter =SUM(D3:D6), or type
referring
More about =SUM(, then select that range with the mouse and press Enter. This will sum
to functions Item Amount
another cell the values in cells D3, D4, D5 and D6. Your answer should be 170.
Now,
If the SUM
instead, let’s look at the
likefunction anatomy
could of a few
talk, it would say:functions.
“Return theThe
sumSUM function
of all the valuesis structured
in cells D38like this: and all of column H”. Bread
to D41, SUM is the function name, D38:D41 50 is the first range argum
cell F51. That2 Now, let’s try AutoSum. Select the yellow cell under the column for Meat Doughnuts 100
way,
The it’s easily
TODAY function returns today’s
TAKE
seen and A LOOKnot AT THIS(cell G7), then godate. It will automatically
to Formulas > AutoSum update whenSUM.
> select Excel You’ll
recalculates.
see Excel Cookies 40
Select
hiddenthese insidecells.
a Then in the bottom-right
automatically enter thecorner
formulaof the ExcelPress
for you. window, looktofor
Enter SUM: 170
confirm in the bottom bar. That’s called
it. The Cakesthe Status Bar, and it’s just50
another way to quickly find
formula. AutoSum feature has all of the most common functions. Pies 20
More information on the web SUM >
All about the3SUM function Here’s a handy keyboard shortcut. Select cell D15, then press Athen
lt =
Use AutoSum to sum Enter. This automatically enters SUM for you.
numbers
All about the COUNT function
Free Excel training online
Back to top Dive down for more detail Next step
Next step

More about functions

Go to the Formulas tab and browse through the Function Library, where functions are
listed by category, like Text, Date & Time etc. Insert Function will let you search for
functions by name, and launch a function wizard that can help you build your formula.

When you start typing a function name after you press =, Excel will launch Intellisense,
which will list all of the functions starting with the letters you type. When you find the
one you want, press Tab, and Excel will automatically finish the function name and enter
Go to the Formulas tab and browse through the Function Library, where functions are
listed by category, like Text, Date & Time etc. Insert Function will let you search for
functions by name, and launch a function wizard that can help you build your formula.

When you start typing a function name after you press =, Excel will launch Intellisense,
which will list all of the functions starting with the letters you type. When you find the
one you want, press Tab, and Excel will automatically finish the function name and enter
the opening parenthesis for you. It will also display the optional and required Fruit Amount
arguments. Apples 50
Oranges 20
Bananas 60
Lemons 40
Now, let’s look at the anatomy of a few functions. The SUM function is structured like SUM > 170
this:

The function The first Additional

name. argument. It’s arguments,
almost always separated by Item Amount
required. commas (,). Cars 20
Lorries 10
Bikes 10
Skates 40

=SUM(D38:D41,H:H) Total:
180
If the SUM function could talk, it would say: “Return the sum of all the values in cells
D38 to D41, and all of column H”.

Now, let’s try one that doesn’t require any arguments. IMPORTANT DETAIL
Double-click this cell. You’ll notice the 100 towards
end. Although it’s possible to put numbers in a form
The TODAY function returns today’s date. It will like this, we don’t recommend it unless it’s absolute
automatically update when Excel recalculates. necessary. This is known as a constant, and it’s easy
forget that it’s there. We recommend referring to
another cell instead, like cell F51. That way, it’s easi
seen and not hidden inside a formula.

=TODAY()
 =TODAY()

More information on the web

All about the SUM function

Use AutoSum to sum numbers

All about the COUNT function

Free Excel training online

Back to top Next step

Free Excel training online

Go back to the top by pressing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN.
Meat Amount
Beef 50
Chicken 30
Pork 10
Fish 50
SUM >

Item Amount
Bread 50
Doughnuts 100
Cookies 40
Cakes 50
Pies 20
COUNT >

EXTRA CREDIT
Try the COUNT function using any of
the methods you’ve already tried.
The COUNT function counts the
number of cells in a range that
contains numbers.
 TAKE A LOOK AT THIS
Select these cells. Then, in the bottom-
right corner of the Excel window, look for
this:

That’s called the Status Bar, and it’s just

another way to quickly find a total and
other details about a selected cell or range.

Additional Value New Total

100 180

DETAIL
cell. You’ll notice the 100 towards the
’s possible to put numbers in a formula
’t recommend it unless it’s absolutely
s known as a constant, and it’s easy to
here. We recommend referring to
ead, like cell F51. That way, it’s easily
dden inside a formula.
EXTRA CREDIT
Try using
TAKE A LOOK
MEDIAN or
AT
MODETHIS here.
AVERAGE function
AVERAGE
Select
functionany
range of gives
MEDIAN
Use thevalue
numbers,
you the AVERAGE
then function to get the average of numbers in a range of cells. Fruit Amount
Select
look
in Use
cell the AVERAGE
the D7, then use AutoSum
theinmiddle function
to to getanthe
add average function.
AVERAGE of numbers in a range of cells. Apples 50
Status
of
Now Bar
theselect for
data set,
cell G7, and enter an AVERAGE function by typing =AVERAGE(G3:G6). Oranges 20
an instant
while
In D15,1
cell gives
Average.
MODE Select
you can use cell D7,AutoSum,
either then use AutoSum
or type toto addanother
enter an AVERAGE
AVERAGEfunction.
function. Bananas 60
the one that Lemons 40
occurs
Activatethe
the previous sheet AVERAGE >
most
Go to the
frequently. 2sheetNow select cell G7, and enter an AVERAGE function by typing
next
=AVERAGE(G3:G6). Item Amount
Links for more information on the web Bread 50
3all about
Select to learn In cell D15, youfunction
the AVERAGE can useoneither AutoSum, or type to enter another AVERAGE
the web Doughnuts 100
function.
Select to learn all about the MEDIAN function on the web Cookies 40
Select to learn all about the MODE function on the web Cakes 50
Select to learn about free Excel training on the web Pies 20
Previous
Previous Next AVERAGE >

More information on the web

All about the AVERAGE function

All about the MEDIAN function

All about the MODE function

Free Excel training online

More about the SUM function
In some of the above tips, we taught you how to use the SU
Cells C37 to D41 contain data with two columns: Fruit and
The formula in cell D42: =SUM(D38:D41).
If the SUM function in cell D42 could talk, it would say this:
Here’s another way it can be used:
Cells C47 to D48 contain data with two columns: Item and

More information on the web

All about the SUM function
All about the SUMIF function
Use Excel as your calculator
Free Excel training online
Go back to the top by pressing CTRL+HOME. To proceed to
Meat Amount
Beef 50 TAKE A LOOK AT THIS
Select any range of numbers,
Chicken 30 then look in the Status Bar for an
Pork 10 instant Average.
Fish 50
AVERAGE >

Item Amount
Bread 50
Doughnuts 100
Cookies 40
Cakes 50
Pies 20

EXTRA CREDIT
Try using MEDIAN or MODE here.
MEDIAN gives you the value in the
middle of the data set, while
MODE gives the one that occurs
the most frequently.
ps, we taught you how to use the SUM function. Here are more details about it.
n data with two columns: Fruit and Amount.
=SUM(D38:D41).
ell D42 could talk, it would say this: Sum up the values in cells D38, D39, D40 and D41.

n data with two columns: Item and Amount.

essing CTRL+HOME. To proceed to the next step, press CTRL+PAGE DOWN.

GOOD TO
KNOW
You can use
either MIN or
MAX with
multiple ranges

MIN and MAX functions

or values to
Select
show cell D7,
the
MIN
In cell
then and
use MAX
D15, youfunctions
greater
Now orthe
select lesser
cell Fruit Amount
can
of use
the either
AutoSum
Use those MIN function
values, to get the minimum number in a range of cells.
G7,
the and enter
AutoSum a
Wizard to add
Use a the MIN function to getnumber
the smallest number
like
MAX
Use
Wizard,
MIN
function
the MAX
or type
function.
by
function to get the maximum in a range of cells. in a range of cells. Apples 50
=MIN(A1:A10,B1
typing Use the MAX function to get the largest number in a range of cells. Oranges 20
to enter
:B10) or a MIN
=MAX(D3:D6).
or MAX
=MAX(A1:A10,B
More
function.
Bananas 60
1), where B1on
information
the web a 1 Select cell D7, then use the AutoSum Wizard to add a MIN function.
contains
Lemons 40
threshold value, MIN >
like 10, in which
All
case about the MIN function
the formula
the 2
Now select cell G7, and enter a MAX function by typing =MAX(D3:D6).
would
All about never MAX function Item Amount
return a result Bread 50
Free Excel10.
less than training online
Doughnuts 100
3 In cell D15, you can use either the AutoSum Wizard, or type to enter a MIN
Cookies 40
or MAX function.
Cakes 50
Pies 20
MIN or MAX >
Previous Next

GOOD TO KNOW
You can use either MIN or MAX with m
More information on the web
You can use either MIN or MAX with multiple ranges or values to show the greater or lesser of those values, like =MIN(A1:A10,B1:B10) or =MAX(A1:A10,10).
or values to show the greater or lesser
values, like =MIN(A1:A10,B1:B10) or
More information on the web =MAX(A1:A10,B1), where B1 contains a
All about the MIN function value, like 10, in which case the formula
All about the MIN function never return a result less than 10.
All about the MAX function
All about the MAX function
Use Excel as your calculator
Free Excel training online
Free Excel training online
 Meat Amount
Beef 50
Chicken 30
Pork 10
Fish 50
MAX >

Item Amount
Bread 50
Doughnuts 100
Cookies 40
Cakes 50
Pies 20
10

OD TO KNOW
can use either MIN or MAX with multiple ranges
alues to show the greater or lesser of those
es, like =MIN(A1:A10,B1:B10) or
AX(A1:A10,B1), where B1 contains a threshold
e, like 10, in which case the formula would
er return a result less than 10.
start
display and a end
times,
negative then
subtracts
number the
time
because theyyou took
for lunch.
haven’t The
*24
enteredat theyourend
of the
birthday formula
yet,
In cell D28,
converts thean
you can
enter use
=NOW(),
fractional
IF function like
which
portion will
of give
the
this:
the
Date
day Date functions
current
functions
that
=IF(D7="","",D
time,
sees
Excel into
can
Excel
and give
will you the current date, based on your computer’s regional settings. You can also add and subtract Dates.
7-D6), which
update each
hours.
says, You’ll
“IF D7the TODAY function, which gives you Today’s date. These are live, or volatile functions, so when you open your workbook tomorrow, it will have tomorrow’s date. Ente
time
CheckExcel
need toout
format
equals
calculates. Excel can give you the current date, based on your computer’s regional settings. You
the cell asthen
Subtract
nothing, aIf – Enter your next birthday in DD/MM/YY format in cell D7, and watch Excel tell you how many days away it is by using =D7-D6 in cell D8.
Dates
you
Numberneed to can also add and subtract Dates.
show
Add Dates
change nothing,
the – Let’s say you want to know which date a bill is due on, or when you need to return a library book. You can add days to a date to find out. In cell D10, enter a random
though.
otherwise To do
Time format, Date functions
GOOD
that,
show
you
Excel
Home can
goTOto
D7go
keeps
>
KNOW
minus
to1 Take a look at the TODAY function, which gives you Today’s date. These are
dates and times based on the number of days starting from 1 January 1900. Times are kept in fractional portions
Today’s ofdate:
a day based on minutes. So 12:30 PM on 01/01/20
D6”.
Ctrl+1
Format>> Cells live, or volatile functions, so when you open your workbook tomorrow, it
Number Your birthday:
(Ctrl+1) >> will have tomorrow’s date. Enter =TODAY() in cell D6.
Time
Time > Select
functions Days until your
Number
Excel
the can>give
format you the current time, based on your computer’s regional settings. You can also add and subtract times. For instance, youbirthday:
might need to keep track of how many ho
Number > 2you
decimals. 2
want. Subtract Dates – Enter your next birthday in DD/MM/YY format in cell D7, and
Grace period days:
watch Excel tell you how many days away it is by using =D7-D6 in cell D8.
Bill due on:
If this formula could talk, it would say: “Take the Time Out and subtract it from the Time In, then subtract the Lunch Out/In Times, then multiply those by 24 to convert Excel’s fra
Previous 3 Add Dates – Let’s say you want to know which date a bill is due on, or when
Next you need to return a library book. You can add days to a date to find out. In
GOOD TO KNOW
More information on the webcell D10, enter a random number of days. In cell D11, we added =D6+D10 Excel keeps dates and times based on the number o
to calculate the due date from today. days starting from 1 January 1900. Times are kept i
All about the TODAY function fractional portions of a day based on minutes. So 1
All about the NOW function PM on 01/01/2017 is actually stored as 42736.5208
All about the DATE function the Time or Date show up as numbers like that, the
Free Excel training online you can press Ctrl+1 > Number > select a Date or T

Time functions
format.

Excel can give you the current time, based on your computer’s regional settings. You
can also add and subtract times. For instance, you might need to keep track of how
many hours an employee worked each week, and calculate their pay and overtime.

1 In cell D28, enter =NOW(), which will give the current time, and will update Time functions
each time Excel calculates. If you need to change the Time format, you can Current Time:
go to Ctrl+1 > Number > Time > Select the format you want.
 In cell D28, enter =NOW(), which will give the current time, and will update
each time Excel calculates. If you need to change the Time format, you can
go to Ctrl+1 > Number > Time > Select the format you want.

2 Add up hours between times – In cell D36 we’ve entered =((D35-D32)- Daily Hours Worked
(D34-D33))*24, which calculates someone’s start and end times, then Time In:
subtracts the time they took for lunch. The *24 at the end of the formula Lunch Out:
converts the fractional portion of the day that Excel sees into hours. You’ll Lunch In:
need to format the cell as a Number though. To do that, go to Home > Time Out:
Format > Cells (Ctrl+1) > Number > Number > 2 decimals. Total Hours:

3 If this formula could talk, it would say: “Take the Time Out and subtract it
from the Time In, then subtract the Lunch Out/In Times, then multiply
those by 24 to convert Excel’s fractional time to hours”, or =((Time In -
Time Out)-(Lunch In - Lunch Out))*24.
*24 to convert Excel’s
fraction of a day to
Time Out Lunch Out hours

Static Date & Time

Date:
=((D35-D32)-(D34-D33))*24 Time:

Time In Lunch In

GOOD TO KNOW
The inner parentheses () make sure Excel calculates those You can use keyboard shortcuts to enter Dates
parts of the formula by themselves. The outer and Times that won’t continuously change:
parentheses make sure Excel multiplies the final inner result by
24. Date – Ctrl+;
Time – Ctrl+Shift+:

Previous Next

More information on the web

More information on the web
All about the TODAY function

All about the NOW function

All about the DATE function

Free Excel training online

morrow, it will have tomorrow’s date. Enter =TODAY() in cell D6.
D7-D6 in cell D8.

Date functions

0 many hoursIMPORTANT
might need to keep track of how
DETAIL
Ifan employee
you worked
don’t want Exceleach week,aand
to display calculate
negative their pay and overtime.
number,
because you haven’t entered your birthday yet, you
can use an IF function like this: =IF(D7="","",D7-D6),
30/12/99 which says, “IF D7 equals nothing, then show nothing,
otherwise show D7 minus D6”.
multiply those by 24 to convert Excel’s fractional time to hours”, or =((Time In - Time Out)-(Lunch In - Lunch Out))*24.

TO KNOW
ps dates and times based on the number of
ting from 1 January 1900. Times are kept in
l portions of a day based on minutes. So 12:30
1/01/2017 is actually stored as 42736.5208. If
or Date show up as numbers like that, then
press Ctrl+1 > Number > select a Date or Time

Time functions
 ly Hours Worked
8:00 AM
12:00 PM
1:00 PM
5:00 PM
8

atic Date & Time

27/09/17
8:52 AM

OW
ard shortcuts to enter Dates
on’t continuously change:

Date – Ctrl+;
me – Ctrl+Shift+:
WORTH
EXPLORING
If you don’t
don’t know
TAKE Aformat
LOOK
which
Joining
AT THIS
There
code Joining text from different cells
text
are
In celltoC36,
Formulas,
fromtimes
many
use,
different cellswhen you want to join text that’s in different cells. This example is very common, where you have first names and surnames, and want to combine
in Excel
First Name Surname
In cell
you
entercan E3,use
=C28&"enter =D3&C3 to join the surnames and first names.
especially big
Ctrl+1 >
"&TEXT(D28,"Y
SmithNancy
ones, doesn’t look quite right though. We need to add a comma and a space. To do that, we’ll use quotes to create a new text string.Smith
cantoThere
Nancy This time, enter =D3&", "&C3. The &"
Number
YYY-MM-DD"). are many times in Excel when you want to join text that’s in different cells. This Andy North
To create
sometimes
format
YYYY-MM-DD the
cellfull name,
be
anyexample we’ll join first name and surname, but use a space without a comma. In F3, enter =C3&" "&D3.
hard to read, is very common, where you have first names and surnames, and want to Jan Kotas
the way
is the UKyou
but you
Dive down
want.
format combine
can
codefor more detail
Then them as first name, surname or full name. Fortunately, Excel lets us do that Maria Jones
break
select
for
Next up
Year-thetheir
with the Ampersand (&) sign, which you can enter with Shift+7. Steven Thorpe
parts
Custom with
Month-Day,
Using
spaces text
like and numbers together Michael Neipper
option. You
like 2017-09-
this:
Lookcopy
can
25. the1
at cells In cell E3, enter =D3&C3 to join the surnames and first names.
C28:D29. See how the date and times are in separate cells? You can join them together with the & symbolRobert like you’ll see in cellsZare
C32:C33, but that doesn’t look right,
format code Yvonne McKay
=C28
that’s& " " &
TEXT(D28,"YYY
displayed back
Y-MM-DD")
to your
2 SmithNancy doesn’t look quite right though. We need to add a comma and
a space. To do that, we’ll use quotes to create a new text string. This time,
formula.
enter =D3&", "&C3. The &", "& portion lets us join a comma and space
More information on the web
All about the TEXT with the text in the cells.
function
Combine text and numbers
3 To
Free Excel training create the full name, we’ll join first name and surname, but use a space
online
without a comma. In F3, enter =C3&" "&D3.

Dive down for more detail Next

Using text and numbers together

Using text & numbers
Today’s date: 08/08/24
Now, we’ll use the & to join text and numbers, not just text and text
Current time: 3:00 AM
Look at cells C28:D29. See how the date and times are in separate cells? You can join
them together with the & symbol like you’ll see in cells C32:C33, but that doesn’t look
right, does it? Unfortunately, Excel doesn’t know how you want to format the
numbers, so it breaks them down to their basest format, which is the Serial date in
this case. We need to explicitly tell Excel how to format the number portion of the
Now, we’ll use the & to join text and numbers, not just text and text

Look at cells C28:D29. See how the date and times are in separate cells? You can join
Joining text & numbers
them together with the & symbol like you’ll see in cells C32:C33, but that doesn’t look
Today’s date: 45512
right, does it? Unfortunately, Excel doesn’t know how you want to format the
Current time: 45512.1253841706
numbers, so it breaks them down to their basest format, which is the Serial date in
this case. We need to explicitly tell Excel how to format the number portion of the
Formatting text & numbers
formula, so it displays the way you want in the resulting text string. You can do that
Today’s date: 2024-08-08
with the TEXT function and a format code.
Current time: 03:00 AM

1 In cell C36, enter =C28&" "&TEXT(D28,"YYYY-MM-DD"). YYYY-MM-DD

is the UK format code for Year-Month-Day, like 2017-09-25.

2 In cell C37, enter =C29&" "&TEXT(D29,"HH:MM AM/PM"). HH:MM

AM/PM is the UK format code for Hours:Minutes AM or PM, like 1:30 PM.
WORTH EXPLORING
If you don’t don’t know which format code to use, you
can use Ctrl+1 > Number to format any cell the way you
want. Then select the Custom option. You can copy the
Previous Next format code that’s displayed back to your formula.

More information on the web

All about the TEXT function

Combine text and numbers

Free Excel training online

Surname, First Name Full Name
Smith, Nancy Nancy Smith
 TAKE A LOOK AT THIS
Formulas, especially big ones, can
sometimes be hard to read, but you can
break up their parts with spaces like this:

=C28 & " " & TEXT(D28,"YYYY-MM-DD")

G
hich format code to use, you
o format any cell the way you
tom option. You can copy the
ed back to your formula.
formula
you startin cell
D12.
typing, We’ve
Excel’s
In
gotcell
Next,you F33,
we’vestarted
auto-correct,
we’ve entered
with
added
should an
find IFit
=IF(E33="Yes",
=IF(C12<100,"L
statement to
for you. When
F31*VAT,0),
ess thanpress
calculate
it does,
Copy
where
deliveryD9 ifto
we’ve
100","Greater it’sit.
In
Tabcell
D10.
set up D9
toTheenter
VAT enter
as a
than or
required.
=IF(C9="Apple"
This equal
is aRange In
answer
Named
to 100").
EXPERT
cell here
TIP
F35,Range, What
you’ll
,TRUE,FALSE).
Named
should
with be
a value of
happens
Named
see if you
Ranges
IF statements
The
and
FALSE,
IF correct
we’ve
statements
0.0825. Our
enter
allow
answer
entered a isit to
you
=IF(E35="Yes",
because
formula
IF statements
number
define terms
SUM(D28:D29) anfrom
says Ifallow you to make logical comparisons between conditions. An IF statement generally says that if one condition is true, do something, otherwise do something else
TRUE.
Formulas
orange
cell E33 is >
not
equals
greater
or values
*1.25,0).
Define than
Name. in aor
This
an
Yes,apple.
equal
single
says then
“Ifto
place,100
cell in
Now, if you
multiply
cell C12? IF statements allow you to make logical comparisons between conditions. An IF
cell
and
E35
ever then
is Yes,
need reuse
to
F31
them
then
TRUE
by VAT,
take
and statement
the
FALSE generally
are unlike othersays that
words in ifExcel
oneformulas
condition
in is true,
that dodon’t
they something, otherwise
need to be doand Excel will automatically capitalise them. Numbers don’t need to be in quote
in quotes,
change
otherwise your
sum of thesomething
throughout a
=IF(C3="Apple","Yes","No")
VAT charges, else. The formulas can return text, values or even more calculations.
return
workbook.
Quantity a 0. You
you
Diveonlydown havefor more detail
can
to see
column
do it allone
in
in of
the
Try
Next
the
tablechanging
named
above IF statements
this1
place,
Yes to and
No inyou In cell D9 enter =IF(C9="Apple",TRUE,FALSE). The correct answer is TRUE.
ranges
and
IF
IF statementinthe
multiply
statements itwith another function Apple
can
cell use
E33
workbook
by 1.25, to by seecan also force additional calculations to be performed if a certain condition is met. Here, we’re going to evaluate a cell to see if VAT should be charged, and calculat
Delivery
the name
calculation Orange
going
otherwise
anywhere to in
change.
Formulas
return a 0”. >
the workbook.
Name 2 Copy D9 to D10. The answer here should be FALSE, because an orange is 50
Manager. Click not an apple.
GOOD
here toTO learn KNOW
When
more. you create a formula, Excel will automatically place coloured borders around any ranges referenced in the formula, and the corresponding ranges in the formula will be the
3 Try another example by looking at the formula in cell D12. We’ve got you IMPORTANT DETAIL
Previous started with =IF(C12<100,"Less than 100","Greater than or equal to TRUE and FALSE are unlike other words in Ex
Next that they don’t need to be in quotes, and Exc
100"). What happens if you enter a number greater than or equal to 100 in
automatically capitalise them. Numbers don’
More information on cellthe web
C12? in quotes either. Regular text, such as Yes or
All about the IF function to be in quotes like this:
All about the IFS function =IF(C3="Apple","Yes","No")
Advanced IF statements Dive down for more detail Next
Free Excel training online

IF statement with another function

Item
IF statements can also force additional calculations to be performed if a certain Widget
condition is met. Here, we’re going to evaluate a cell to see if VAT should be charged, Doohickey
and calculate it if the condition is true.
 1 In cell F33, we’ve entered =IF(E33="Yes",F31*VAT,0), where we’ve set up
VAT as a Named Range with a value of 0.0825. Our formula says If cell E33
equals Yes, then multiply cell F31 by VAT, otherwise return a 0.

Try changing Yes to No in cell E33 to see the calculation change.

2 Next, we’ve added an IF statement to calculate delivery if it’s required. In

cell F35, you’ll see =IF(E35="Yes",SUM(D28:D29)*1.25,0). This says “If cell
E35 is Yes, then take the sum of the Quantity column in the table above and
multiply it by 1.25, otherwise return a 0”.

3 Next, change the 1.25 in the formula in cell F35 to “Delivery”. As you start EXPERT TIP
Named Ranges allow you to define terms or values in a
typing, Excel’s auto-correct, should find it for you. When it does, press Tab single place, and then reuse them throughout a workbo
to enter it. This is a Named Range, and we’ve entered it from Formulas > You can see all of the named ranges in this workbook by
Define Name. Now, if you ever need to change your VAT charges, you only going to Formulas > Name Manager. Click here to learn
more.
have to do it in one place, and you can use the Delivery name anywhere in
the workbook.

Previous Next

More information on the web

All about the IF function

All about the IFS function

Advanced IF statements

Free Excel training online

Free Excel training online
 true, do something, otherwise do something else. The formulas can return text, values or even more calculations.

pitalise them. Numbers don’t need to be in quotes either. Regular text, like Yes or No, needs to be in quotes like this:

IF statements

Less than 100

he corresponding ranges in the formula will be the same colour. You can see this if you select cell F33 and press F2 to edit the formula.
IMPORTANT DETAIL
TRUE and FALSE are unlike other words in Excel formulas in
that they don’t need to be in quotes, and Excel will
automatically capitalise them. Numbers don’t need to be
in quotes either. Regular text, such as Yes or No, does need
to be in quotes like this:
=IF(C3="Apple","Yes","No")

Quantity Cost Total

2 £9.76 £19.52
3 £3.42 £10.26
 Sub-Total £13.18 £29.78

VAT? Yes £2.46 GOOD TO KNOW

When you create a formula, Excel will automatically
place coloured borders around any ranges
Delivery? Yes £6.25 referenced in the formula, and the corresponding
ranges in the formula will be the same colour. You
Total £38.48 can see this if you select cell F33 and press F2 to edit
the formula.

ERT TIP
d Ranges allow you to define terms or values in a
place, and then reuse them throughout a workbook.
n see all of the named ranges in this workbook by
to Formulas > Name Manager. Click here to learn
exists, but you
In
stillthis
want case,to
we’ll wrapthe
suppress our
In cell
existing D22,
#N/A
enter error,
VLOOKUP
you can use an
=VLOOKUP(C2
formula like
error handling
2,C17:D20,2,F
this
functionin cell D43:
called
ALSE). The
IFERROR
correct in
answer cell
=IF(C43="","",V
G43:
for Apples is
LOOKUP(C43,C
=IFERROR(VLO
VLOOKUP
50. VLOOKUP
37:D41,2,FALS
VLOOKUP
OKUP(F43,F37:
VLOOKUP
looked forforis one of the most widely used functions in Excel (and one of our favourites too!). VLOOKUP lets you look up a value in a column on the left, then returns information in
Now,
E)) try
G41,2,FALSE),"
Apples,
yourself found
in the
").
it, IFERROR
then went
=VLOOKUP(A1,B:C,2,FALSE)
Meat
This
says section,
says
that that
if the
over
in
if cell
cell one
G22.
C43 VLOOKUP is one of the most widely used functions in Excel (and one of our favourites
What
VLOOKUP
column do toyou thewant to look for?
You
equals
returns should
nothing
too!).
a valid VLOOKUP lets you look updoa value in a to
column on the left, then returns
If you
right
end
(""), up find
and
thenwith it, how many columns to the right you want get a value?
result, then information in another
returned dothe
Where nothing,
=VLOOKUP(F2
return you want to look for it? column to the right if it finds a match. VLOOKUP says:
display
amount. that,
2,F17:G20,2,FA
otherwise
Do you want an exact or approximate match?
otherwise,
LSE).
return What do you If you find it, how many
displaythe nothing
VLOOKUP’s
(""). We want to look columns to the right do
results.
EXPERIMENT
displayed Note for? you want to get a value?
the second
Try selecting
nothing here different items from the drop-down lists. You’ll see the result cells instantly update themselves with new values.
closing
(""), but youfor more detail
Dive down
parenthesis
can also use at
Next
the
numbersend of(0,1, the
formula.
VLOOKUP
2 etc.), or text,
Invariably,
closes
=VLOOKUP(A1,B:C,2,FALSE)
This
and #N/A
you’ll run into a situation where VLOOKUP can’t find what you asked it to, and it returns an error (#N/A). Sometimes, it’s because the lookup value simply doesn’t exist
such asthe IF
statement.
“Formula isn’tDETAIL
IMPORTANT
correct”. is what’s known as a blanket error handler, meaning it will suppress any error your formula might throw. This can cause problems if Excel is giving you a notification that
IFERROR
Where do Do you want Fruit Amount
you want to an exact or
A rule of thumb is to not add error handlers to your formulas until you’re absolutely certain they work properly. Apples 50
look for it? approximate
Previous match? Oranges 20
Next Bananas 60
More information on the web Lemons 40
All about the VLOOKUP function
1 In cell D22,
All about the INDEX/MATCH
enter =VLOOKUP(C22,C17:D20,2,FALSE). The correct answer
functions Apples
for Apples is 50. VLOOKUP looked for Apples, found it, then went over one
All about the IFERROR function
column to the right and returned the amount.
Use PivotTables to analyse worksheet data
Free Excel training 2 online
Now, try for yourself in the Meat section, in cell G22. You should end up EXPERIMENT
Try selecting different items from the drop-down lists. You’
with =VLOOKUP(F22,F17:G20,2,FALSE). the result cells instantly update themselves with new value

Dive down for more detail Next

 Dive down for more detail Next

VLOOKUP and #N/A If the SUM function in cell D42 could talk, it would say this: Sum up the values in
Item Amount
Invariably, you’ll run into a situation where VLOOKUP can’t find what you asked it to, Bread 50
and it returns an error (#N/A). Sometimes, it’s because the lookup value simply Doughnuts 100
doesn’t exist, or it can because the reference cell doesn’t have a value yet. Cookies 40
Cakes 50
1 If you know your lookup value exists, but want to hide the error if the lookup Pies 20
cell is blank, you can use an IF statement. In this case, we’ll wrap our existing
VLOOKUP formula like this in cell D43: #N/A

=IF(C43="","",VLOOKUP(C43,C37:D41,2,FALSE))

This says, “If cell C43 equals nothing (""), then return nothing, otherwise IMPORTANT DETAIL
return the VLOOKUP’s results”. Note the second closing parenthesis at the IFERROR is what’s known as
end of the formula. This closes the IF statement. will suppress any error your
cause problems if Excel is giv
formula has a legitimate erro
2 If you aren’t sure your lookup value exists, but you still want to suppress the
#N/A error, you can use an error handling function called IFERROR in cell A rule of thumb is to not add
until you’re absolutely certa
G43: =IFERROR(VLOOKUP(F43,F37:G41,2,FALSE),""). IFERROR says, “If
the VLOOKUP returns a valid result, then display that, otherwise, display
nothing ("")”. We displayed nothing here (""), but you can also use numbers
(0,1, 2 etc.), or text, such as “Formula isn’t correct”.

Previous Next

More information on the web

More information on the web
All about the VLOOKUP function

All about the INDEX/MATCH functions

All about the IFERROR function

Use PivotTables to analyse worksheet data

Free Excel training online

he left, then returns information in another column to the right if it finds a match. VLOOKUP says:

e lookup value simply doesn’t exist, or it can because the reference cell doesn’t have a value yet.

Meat Amount
Beef 50
Chicken 30
Pork 10
Fish 50

Pork

ems from the drop-down lists. You’ll see

update themselves with new values.
would say this: Sum up the values in cells D38, D39, D40 and D41.
Item Amount
Bread 50
Doughnuts 100
Cookies 40
Cakes 50
Pies 20

Pastry

IMPORTANT DETAIL
IFERROR is what’s known as a blanket error handler, meaning it
will suppress any error your formula might throw. This can
cause problems if Excel is giving you a notification that your
formula has a legitimate error that needs to be fixed.

A rule of thumb is to not add error handlers to your formulas

until you’re absolutely certain they work properly.
 Conditional functions – SUMIF
Conditional functions – SUMIF
Conditional functions let you sum, average, count or get the min. or max. of a range based on a given condition, or criteFruit Amount
SUMIF lets you sum in one range based on a specific criteria you look for in another range, like how many Apples you hApples 50
Conditional functions let you sum, average, count or get the min. or max. of a range
=SUMIF(C3:C14,C17,D3:D4) Oranges 20
based on a given condition, or criteria you specify. Such as: out of all the fruits in the
Which range do you want to look at? Bananas 60
list, how many are apples? Or, how many oranges are the Florida type?
What value
EXPERT TIP (text or number) do you want to look for? Lemons 40
Each
For one of
each match found, which range
SUMIFS
the Fruitisand the same as SUMIF, but itdo
letsyou
youwant
use to sum in?
multiple Applesof just by Fruit. Select 50
criteria. So in this example, you can look for Fruit and Type, instead cell H17 and type =SUMIFS(H3
Type cells has
1 SUMIF lets you sum in one range based on a specific criteria you look for in Oranges 20
drop-down another range, like how many Apples you have. Select cell D17 and type
a=SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17) Bananas 60
list where
COUNTIFS you
is =SUMIF(C3:C14,C17,D3:D14).
Which range do you want to sum? SUMIF is structured like this: Lemons 40
can
the
This select
same
is the as
This is thefruits.
different first range to look in for matches Apples 50
SUMIF,
second but it
range Which range do you For each match found, which
This
Try
lets is
it, the
and
youinuse
to look criteria
for for the first match Oranges 20
watch the want to look at? range do you want to sum in?
multiple
matches Bananas 60
formulasSo in
criteria.
This is the criteria for the second match
automatically Lemons 40
this example,
update.
you can look
for
DiveFruit
downand
Type, instead
=SUMIF(C3:C14,C17,D3:D4)
for more detail Fruit SUMIF
Next
of juststep
by Fruit. Apples
Conditional
Select cell H64functions – COUNTIF
and type
COUNTIF and COUNTIFS let you count values inWhat a range based
=COUNTIFS(F5 value (textonor anumber)
criterion you specify. They’re a bit different from the other IF and IFS functions, in that they only have a criteri
Select cell
0:F61,F64,G50: D64 and type =COUNTIF(C50:C61,C64). do COUNTIF
you want is
to structured
look for? like this:
=COUNTIF(C50:C61,C64)
G61,G64).
COUNTIFS
Which range is do you want to look at?
What value 2
structured like SUMIFS
this: (text or number)isdotheyou
same
wantastoSUMIF, but it lets you use multiple criteria. So in this
look for?
example, you can look for Fruit and Type, instead of just by Fruit. Select cell
H17 and type =SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17). SUMIFS is
=COUNTIFS(F50:F61,F64,G50:G61,G64)
structured like this:
This is the first range to count
This is the criteria for the firstWhichmatchrange This is the This is the
This is the second range to count do you want criteria for criteria for
This is criteria for the second matchto sum? the first the second
match match
SUMIF =SUMIF(C92:C103,C106,E92:E103)
Which range This is the This is the
SUMIFS =SUMIFS(E92:E103,C92:C103,C106,D92:D103,D106)
do you want criteria for criteria for
AVERAGEIF =AVERAGEIF(C92:C103,C106,E92:E103)
to sum? the first the second
AVERAGEIFS =AVERAGEIFS(E92:E103,C92:C103,C106,D92:D92,D106)
match match
Dive down for=COUNTIF(C92:C103,C106)
COUNTIF more detail
COUNTIFS
Next =COUNTIFS(C92:C103,C106,D92:D103,D106)
MAXIFS =MAXIFS(E92:E103,C92:C103,C10,D92:D103,D106)
More
MINIFSconditional functions
=MINIFS(E92:E103,C92:C103,C106,D92:D103,D106)
=SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17)
Dive down for more detail
Next
SUMIF with a value argument
This is the
Here’s an example of the SUMIF function using greater Thisgreater
than (>) to find all values is the than a given amount:
=SUMIF(D118:D122,">50") first range to second range
look in for to look in for
Sum up some values based on
....Look through these cells... this criterion: matches matches
...and if the value is greater than 50, sum it up.

NOTE: If you find you are making a lot of conditional formulas, you might find that a PivotTable is a better solution. See this PivotTable article for more information.
More information on thedown
Dive web for more detail Next
All about the SUMIF function
All about the SUMIFS function
All about the COUNTIF function
All about the COUNTIFS function
Conditional functions – COUNTIF
All about the AVERAGEIF function
All about the AVERAGEIFS function Fruit Amount
All about the MINIFS function Apples 50
COUNTIF and COUNTIFS let you count values in a range based on a criterion you
All about the MAXIFS function Oranges 20
specify. They’re a bit different from the other IF and IFS functions, in that they only
Create a drop-down list Bananas 60
have a criteria
Free Excel training online range and criteria. They don’t evaluate one range then look in another Lemons 40
to summarise.
Back to top Apples 50
Next Oranges 20
1 Select cell D64 and type =COUNTIF(C50:C61,C64). COUNTIF is structured Bananas 60
like this:
Which range do you Lemons 40
want to look at? Apples 50
Oranges 20
Bananas 60
Lemons 40
=COUNTIF(C50:C61,C64)
 =COUNTIF(C50:C61,C64)
Fruit COUNTIF
Apples 3
What value (text or number)
do you want to look for?

2 COUNTIFS is the same as SUMIF, but it lets you use multiple criteria. So in
this example, you can look for Fruit and Type, instead of just by Fruit. Select
cell H64 and type =COUNTIFS(F50:F61,F64,G50:G61,G64). COUNTIFS is
structured like this:
This is the first This is the second
range to count range to count

=COUNTIFS(F50:F61,F64,G50:G61,G64)

This is the criteria This is criteria for

for the first match the second match

Dive down for more detail Next

More conditional functions

Fruit Type Amount
Apples Fuji 50
You’ve already seen SUMIF, SUMIFS, COUNTIF and COUNTIFS. Now you can try on
Oranges Florida 20
your own with the other functions, such as AVERAGEIF/S, MAXIFS, MINIFS. They’re
Bananas Cavendish 60
all structured the same way, so once you get one formula written, you can just replace
Lemons Rough 40
the function name with the one you want. We’ve written all the functions you’ll need
for cell E106, so you can copy/paste these, or try to type them yourself for practice.

SUMIF =SUMIF(C92:C103,C106,E92:E103)
SUMIFS =SUMIFS(E92:E103,C92:C103,C106,D92:D103,D106)
You’ve already seen SUMIF, SUMIFS, COUNTIF and COUNTIFS. Now you can try on
your own with the other functions, such as AVERAGEIF/S, MAXIFS, MINIFS. They’re
all structured the same way, so once you get one formula written, you can just replace
the function name with the one you want. We’ve written all the functions you’ll need
Apples Honeycrisp 50
for cell E106, so you can copy/paste these, or try to type them yourself for practice.
Oranges Navel 20
Bananas Lady Finger 60
SUMIF =SUMIF(C92:C103,C106,E92:E103)
Lemons Eureka 40
SUMIFS =SUMIFS(E92:E103,C92:C103,C106,D92:D103,D106)
Apples Honeycrisp 50
AVERAGEIF =AVERAGEIF(C92:C103,C106,E92:E103)
Oranges Navel 20
AVERAGEIFS =AVERAGEIFS(E92:E103,C92:C103,C106,D92:D92,D106)
Bananas Cavendish 60
COUNTIF =COUNTIF(C92:C103,C106)
Lemons Eureka 40
COUNTIFS =COUNTIFS(C92:C103,C106,D92:D103,D106)
MAXIFS =MAXIFS(E92:E103,C92:C103,C106,D92:D103,D106)
Fruit Type Try it
MINIFS =MINIFS(E92:E103,C92:C103,C106,D92:D103,D106)
Lemons Eureka 80

Dive down for more detail Next

SUMIF with a value argument

Here’s an example of the SUMIF function using greater than (>) to find all values Item Amount
greater than a given amount: Bread 50
Doughnuts 100
Sum up some ....Look through ...and if the Cookies 40
values based these cells... value is
on this greater than Cakes 50
criterion: 50, sum it up. Pies 20
100

=SUMIF(D118:D122,">50")
NOTE: If you find you are making a lot of conditional formulas, you might find that a
PivotTable is a better solution. See this PivotTable article for more information.
NOTE: If you find you are making a lot of conditional formulas, you might find that a
PivotTable is a better solution. See this PivotTable article for more information.

More information on the web

All about the SUMIF function All about the SUMIFS function

All about the COUNTIF function All about the COUNTIFS function

All about the AVERAGEIF function All about the AVERAGEIFS function

All about the MINIFS function All about the MAXIFS function

Create a drop-down list Free Excel training online

Back to top Next step

 Fruit Type Amount
Apples Fuji 50
Oranges Florida 20
Bananas Cavendish 60
Lemons Rough 40
Apples Honeycrisp 50
Oranges Navel 20
Bananas Lady Finger 60
Lemons Eureka 40
Apples Honeycrisp 50
Oranges Navel 20
Bananas Cavendish 60
Lemons Eureka 40

Fruit Type SUMIFS

Oranges Florida

that they only have a criteria range and criteria. They don’t evaluate
EXPERT TIP one range then look in another to summarise.
Each one of the Fruit and Type cells has a
drop-down list where you can select
different fruits. Try it, and watch the
formulas automatically update.
e information.

Fruit Type Amount

Apples Fuji 50
Oranges Florida 20
Bananas Cavendish 60
Lemons Rough 40
Apples Honeycrisp 50
Oranges Navel 20
Bananas Lady Finger 60
Lemons Eureka 40
Apples Honeycrisp 50
Oranges Navel 20
Bananas Cavendish 60
Lemons Eureka 40
Fruit Type COUNTIFS
Oranges Florida 1
GOOD TO KNOW
Double-click this cell and you’ll see that the formula
is different. Specifically, the sum criteria is ">=50"
which means greater than or equal to 50. There are
other operators you can use like "<=50" which is less
than or equal to 50. And there’s "<>50" which is not
equal to 50.
asection,
functionthebox,
and
criteria forGO.
press
When you see
each argument
VLOOKUP
GOOD
is listedTOat the
highlighted,
KNOW
bottom of the
click
As
form.youOK at the
enter
Press OK
bottom.
each
when you’ve When
you selectand
argument’s
finished, a
function
section,
Excel in
the
will the
enter
TAKE
list, A LOOK
Excel will
Let
If
AT youthe
THIS
display
you.
You
syntax.
Let the Function Wizard guide you
argument’s
the formula
Function
know
its will
description
should end
for name
the Wizardofguide you you want, but you aren’t sure how to build it, you can use the Function Wizard to help you out.
the function
be
up displayed
with
towards
=VLOOKUP(C10the
bottom of
,C5:D8,2,FALSEIfthe
you know the name of the function you want, but you aren’t sure how to build it, Fruit Amount
form, above
)GOOD TOyou can use the Function Wizard to help you out. Apples 50
the Formula KNOW
You can
result. type cell and range references, or select them with your mouse. Oranges 20
1 Select cell D10, then go to Formulas > Insert Function > type VLOOKUP Bananas 60
Previous in the Search for a function box, and press GO. When you see VLOOKUP Lemons 40
Next highlighted, click OK at the bottom. When you select a function in the list,
More information on Excel
the will
webdisplay its syntax. Apples 50
Overview of formulas in Excel
Excel functions 2 (byNext, enter the function arguments in their respective text boxes. As you
category)
enter each one, Excel will evaluate it, and show you its result, with the final
Excel functions (alphabetical) TAKE A LOOK AT THIS
Free Excel training result
online at the bottom. Press OK when you’ve finished, and Excel will enter You should end up with
=VLOOKUP(C10,C5:D8,2,FALSE)
the formula for you.

GOOD TO KNOW
You can type cell and range references, or select them
with your mouse.

GOOD TO KNOW
As you enter each argument’s section, the argument’s
description will be displayed towards the bottom of the fo
above the Formula result.
 GOOD TO KNOW
As you enter each argument’s section, the argument’s
description will be displayed towards the bottom of the fo
above the Formula result.

Previous Next

More information on the web

Overview of formulas in Excel

Excel functions (by category)

Excel functions (alphabetical)

Free Excel training online

OK AT THIS
d up with
0,C5:D8,2,FALSE)

erences, or select them

ection, the argument’s

wards the bottom of the form,
ection, the argument’s
wards the bottom of the form,
GOOD TO
KNOW
Fixing
At some
Clicking Fixing formula errors
formula
point inerrors
time, you’ll run across a formula that has an error, which Excel will display with #ErrorName. Errors can be helpful, because they point out when something’s not
Error checking
Options will let– Go to Formulas > Error Checking. This will load a dialogue that will tell you the general cause for your specific error. In cell D9, the #N/A error is caused because t
you theHelp on this Error, a help topic specific to the error message will open. If you click Show Calculation Steps, anFruit
setclick
If you Evaluate Formula dialogue Amount
will load.
rules for At
when some point in time, you’ll run across a formula that has an error, which Excel will Apples
Each time you click Evaluate, Excel will step through the formula one section at a time. It won’t necessarily tell you why an error has occurred, but it will 50 point out where. From th
errors in Excel
display with #ErrorName. Errors can be helpful, because they point out when something’s Oranges 20
EXPERIMENT
are displayed
What’s not working
wrong
or ignored. correctly,
here? Hint: but they
We’re trying can be
to SUM upchallenging
all the items.to fix. Fortunately, there are several Bananas 60
options that can help you track down the source of the error and fix it. Lemons 40
Previous
Next 1 Error checking – Go to Formulas > Error Checking. This will load a dialogue Apple #N/A
that will tell you the general cause for your specific error. In cell D9, the #N/A
More information on the web
error is caused because there is no value matching “Apple”. You can fix this by
Detect errors in formulas
using a value that does exist, suppress the error with IFERROR or ignore it
How to avoid broken formulas
Evaluate a nested formulaand know it willatgo
one step away when you do use a value that exists.
a time
Free Excel training online

GOOD TO KNOW
2 If you click Help on this Error, a help topic specific to the error message Clicking Options will let you set the
will open. If you click Show Calculation Steps, an Evaluate Formula rules for when errors in Excel are
dialogue will load. displayed or ignored.

Fruit Amount
 Apples 50
Oranges 20
Bananas 60
Lemons 40

Total #NAME?

EXPERIMENT
What’s wrong here? Hint: We’re trying
up all the items.

3 Each time you click Evaluate, Excel will step through the formula one
section at a time. It won’t necessarily tell you why an error has occurred, but
it will point out where. From there, look at the help topic to deduce what
went wrong with your formula.

Previous Next

More information on the web

Detect errors in formulas

How to avoid broken formulas

Evaluate a nested formula one step at a time

Free Excel training online

out when something’s not working correctly, but they can be challenging to fix. Fortunately, there are several options that can help you track down the source of the error and fix it.

ou set the
xcel are
MENT
ng here? Hint: We’re trying to SUM
ems.
the error and fix it.
Have more questions about Excel?
Press ALT+Q and type what you want to know.
Have more questions about Excel?
Keep going. There is more to learn with Excel:

Community: Ask questions and connect with other Excel fans.

What else is new?Office 365 subscribers get continual updates and new features.
Give us feedback on this tour

Keep going. There is more to learn with Excel:

Community What else is new?

Ask questions and Office 365 subscribers
connect with other get continual updates
Excel fans. and new features.

Learn more Learn more