Download "How to Use Excel Formulas"
%3Aformat(webp)%2Fi.ytimg.com%252Fvi%252F59_SrhR9yro%252Fmaxresdefault.jpg&w=1139&q=75)
Table of contents
Similar videos from our catalog
%3Aformat(webp)%2Fi.ytimg.com%252Fvi_webp%252FQcW2lf56zm8%252Fmaxresdefault.webp&w=1139&q=75)
%3Aformat(webp)%2Fi.ytimg.com%252Fvi_webp%252FSfhV6Adxqmg%252Fmaxresdefault.webp&w=1139&q=75)
%3Aformat(webp)%2Fi.ytimg.com%252Fvi%252FTl2LIMbSoOo%252Fmaxresdefault.jpg&w=1139&q=75)
%3Aformat(webp)%2Fi.ytimg.com%252Fvi_webp%252F_NEvWYp33Eg%252Fmaxresdefault.webp&w=1139&q=75)
%3Aformat(webp)%2Fi.ytimg.com%252Fvi_webp%252FyPWNOItfAI4%252Fmaxresdefault.webp&w=1139&q=75)
Video tags
Subtitles
00:00:00
how to use excel formulas00:00:02
if you need to use excel for work or a00:00:05
new student of excel or just need a00:00:07
refresher on several common excel00:00:09
functions then this video is for you00:00:13
[Music]00:00:17
excel is much more than just a list of00:00:20
values stored in a table you can also do00:00:22
formulas so let's take a look at how00:00:24
formulas work starting with some basics00:00:27
like addition subtraction multiplication00:00:29
and division00:00:30
remember first that all formulas start00:00:32
with an equal sign so you have to type00:00:34
equals in any cell to begin your formula00:00:37
we can do things like 2 plus 2 which is00:00:40
just a basic math calculation and it00:00:42
gives us the result in that same cell of00:00:45
four now you can see up here in the00:00:47
formula bar that the original formula is00:00:50
shown here and we can edit it make00:00:52
changes to it if we want to and hit00:00:54
enter and it'll update that cell00:00:57
you can do regular math like the two00:00:59
plus two you can also use multiplication00:01:01
by using an asterisk00:01:03
and division by using a forward slash00:01:06
and you can even raise this to a power00:01:09
by using the carrot symbol00:01:12
in addition to entering numbers directly00:01:14
into a formula you can also use cell00:01:16
references00:01:18
for example let's do equals and we'll00:01:20
select this salary value on row 2.00:01:24
it fills in f2 as the cell reference and00:01:27
we can add that00:01:29
to f3 to get the total of those two00:01:32
values combined cell referencing is00:01:34
fantastic because even if you insert00:01:37
columns and rows your cell references00:01:41
still stay the same and reference the00:01:43
original locations that you selected and00:01:46
even if you were to move one of your00:01:48
values to a new location00:01:50
it updates the cell reference to reflect00:01:53
the new location this functionality00:01:55
makes it wonderful for modifying and00:01:57
growing your spreadsheets over time but00:02:00
still maintaining the accuracy of your00:02:02
formulas00:02:03
formulas can also contain functions for00:02:06
example sum is a very common function00:02:08
and you type in equal sum and a left00:02:11
parenthesis followed by numbers that you00:02:13
want to sum together so we can select00:02:16
this one00:02:17
put a comma in select this one add the00:02:20
in parentheses and hit enter and you00:02:22
have now a sum of those two salary00:02:24
amounts most functions also allow you to00:02:27
use a range so we can type in some00:02:30
left parentheses and instead of entering00:02:33
numbers separated by commas we can00:02:34
select an entire section of numbers00:02:37
and it puts in f2 through f7 separated00:02:40
by a colon defining that range of00:02:42
numbers00:02:43
now that we put the in parenthesis and00:02:45
hit enter it shows the total of that00:02:47
entire column00:02:49
sum is such a common function that00:02:51
microsoft has added an autosum on the00:02:54
menu under the home tab00:02:56
and if you click below a column that you00:02:58
want to total you can just click the00:03:00
autosum and it'll fill in that sum with00:03:03
that range of numbers automatically hit00:03:05
enter and you have your total00:03:07
microsoft has provided many other00:03:09
functions to use in formulas as well for00:03:11
example if you want the average00:03:14
of the values in that list00:03:17
use the average function00:03:19
if you want to find the minimum value00:03:23
use the minimum function and likewise00:03:26
you can find the maximum value as well00:03:28
in fact excel has hundreds of functions00:03:31
that you can use inside your formulas if00:03:33
you want to see a list of them click00:03:35
this fx option right here00:03:38
that brings up the insert function and00:03:40
you can type a brief description of what00:03:42
you want to look for so for example we00:03:44
just looked at the average function00:03:46
so i type in average hit go and you can00:03:48
see there's a variety of different00:03:50
functions related to averaging00:03:53
there are a list of functions broken00:03:55
down into different categories financial00:03:58
date and time math statistical lookup00:04:01
database text logical so let's take a00:04:04
look at some examples in a few of these00:04:06
categories00:04:07
we've seen functions working with00:04:09
numbers but did you know you can use00:04:11
text related functions in a formula as00:04:14
well for example a common thing we may want00:04:16
to do is combine the first and last name00:04:19
from these text fields over here so00:04:21
we're going to create an equal concat00:04:23
function00:04:25
where we select the first name00:04:28
and the last name00:04:30
in parentheses and we've just combined00:04:32
those two together into one now i don't00:04:34
like the fact that there's a space00:04:35
missing00:04:36
in between those names so i'm going to00:04:38
go up here and edit that formula00:04:40
and we'll add a space00:04:43
and a comma00:04:44
and now we've got our space in between00:04:46
concatenate is very similar to the00:04:48
ampersand00:04:49
i can just do equal00:04:51
first name ampersand and a space00:04:55
another ampersand00:04:56
and the last name00:04:58
let's combine the two together with the00:05:00
space in between using the ampersand it00:05:03
works similar to plus with numbers00:05:06
other common functions for manipulating00:05:07
text are lower where i can take the00:05:10
lower case of all of that text upper00:05:15
to do the upper case00:05:17
i can even do proper case00:05:22
which does the upper case of the first00:05:23
letter of each word and lowercase for00:05:25
the remainder let's say i want the left00:05:28
four characters of the full name well we00:05:30
can do left00:05:32
of that comma four characters00:05:35
we can do right00:05:38
four characters00:05:40
one great thing about excel formulas is00:05:42
you can combine multiple functions into00:05:45
one formula so for example let's go00:05:47
ahead and take our john smith that we00:05:50
created right here and we'll wrap around00:05:52
that the uppercase00:05:55
of all of that text00:05:58
and we'll edit that again and this time00:06:00
we're just going to take the left00:06:03
four characters of that00:06:05
you can see how you can combine one00:06:07
function together with another or00:06:08
multiple functions in one formula to get00:06:11
what you want and that's just a handful00:06:13
of some of the text functions but there00:06:14
are many more to choose from00:06:17
you use logical functions to determine00:06:19
if something is true or false and it00:06:21
will return that value for example if i00:06:24
just type in equals00:06:26
the age here is greater than 40 and i00:06:29
hit enter it's going to tell me whether00:06:31
that's true or false let's copy this00:06:33
down00:06:34
and you can see that it's false for some00:06:37
and true for others depending on the age00:06:40
now i can reverse the true false00:06:43
statement by putting a not function in00:06:45
here00:06:46
so i'm going to wrap that00:06:48
in parentheses and now it reverses the00:06:51
true to false and the false to true00:06:54
you can also do an or function00:06:57
so let's say we want to determine when00:06:59
the age is greater than 40 or for the00:07:02
second expression00:07:04
the salary is greater than 75 thousand00:07:09
and it's going to tell me when that's00:07:11
true or false00:07:13
that one being the only one that's false00:07:15
now the most common logical function00:07:17
you're going to see is the if statement00:07:19
how it works is you do equals if and it00:07:22
wants a logical test so again we'll pick00:07:25
the age greater than 4000:07:27
and then you can choose what to do if00:07:29
that value is true so if it is greater00:07:32
than 40 let's go ahead and show what the00:07:34
salary is00:07:35
comma and if the value is false we'll go00:07:38
ahead and put in zero so what this is00:07:40
going to do is if the age is greater00:07:42
than 40 it's going to show the salary00:07:44
and otherwise it's going to show zero00:07:46
i'll hit enter let's copy this down00:07:50
and you can see for all the ones where00:07:51
the age is below or equal to 40 it's00:07:55
going to show zero otherwise it shows00:07:57
the salary so come down here and we'll00:08:00
do an auto sum00:08:02
and you can see that this is the total00:08:05
amount of salary for people that are00:08:07
greater than 40 years old now you can00:08:10
even do nested if statements one within00:08:12
another if you want to get really00:08:14
complicated but i won't show that in00:08:16
this video just be aware anytime you00:08:19
want to do a logical test of values in00:08:22
order to determine what to do with them00:08:24
that's where you would use a logical00:08:25
function like the if statement00:08:28
lookup functions are useful to find and00:08:31
return data from tables of values00:08:34
so for example let's say we typed in the00:08:36
name smith00:08:37
and we want to look up smith to see if00:08:39
it's in this list of last names we can00:08:41
use the match function00:08:44
and we can look up the name smith00:08:47
in this array of last names00:08:50
and we're going to make sure it's an00:08:51
exact match00:08:55
and it tells us that smith is found in00:08:58
position number one of the list that we00:09:00
selected00:09:01
now if i change this name to miller00:09:04
you can see it found it in position00:09:06
number three if i chose a name that's00:09:09
not on the list00:09:11
it gives us an error00:09:12
so let's go back to smith00:09:15
what if we wanted to not only find the00:09:18
position where smith is in this list but00:09:20
we wanted to return the age for that00:09:23
person00:09:24
well we'll leave our smith here and this00:09:26
is where the vlookup function comes in00:09:29
now we're going to look up smith00:09:32
and the table array that we're going to00:09:34
choose is going to be the last name and00:09:36
the age for this entire list00:09:39
the column index number that we want to00:09:41
return the value from is going to be00:09:44
column number 2 of this list that we00:09:46
selected00:09:48
and again we'll make sure that it's an00:09:50
exact match00:09:54
this shows that smith returns the age 4900:09:58
and if we change this to miller00:10:01
it looks up and determines miller's age00:10:03
is 39.00:10:05
vlookup stands for vertical lookup00:10:07
because we are searching for data in a00:10:09
vertical list but you can also do00:10:12
horizontal with hlookup00:10:14
so for example let's say we wanted to00:10:17
find over here which column we want to00:10:19
choose and then drop down and get data00:10:22
from the row below it well we can put in00:10:24
here the age00:10:26
because that's the column that we want00:10:28
to search for00:10:29
we'll do00:10:30
hlookup we're going to search for age00:10:34
we're going to search for age in this00:10:36
list00:10:38
and we're going to return the row 1 2 300:10:43
to get this number right here and again00:10:46
we're picking00:10:48
exact match00:10:50
and it found 21 in the list of that00:10:53
array00:10:54
now anytime we talk about vlookup and00:10:56
hlookup we should also refer to x lookup00:10:59
that's a brand new function that00:11:01
microsoft created in the past year let's00:11:03
go back to our name smith00:11:06
and this time we're going to use xlookup00:11:09
to look up that name smith00:11:12
in this array of last names but the00:11:14
return array rather than an index column00:11:17
number we can actually pick whatever we00:11:19
want so let's just pick this list over00:11:21
here of salaries00:11:23
hit enter and it returns the salary for00:11:26
smith we change this to miller and it returns00:11:29
the salary for miller now one of the00:11:31
benefits of x lookup is it allows you to00:11:34
do an array of data as well so let's00:11:36
edit this formula over here and we'll00:11:39
change the return array rather than just00:11:41
salary let's go ahead and select this00:11:43
entire list of first last name and age00:11:49
and you can see it returned all three of00:11:52
those columns together00:11:53
change it to00:11:54
smith and it returns the three columns00:11:58
for smith xlicup is a very powerful00:12:01
function and i have an entire video00:12:03
dedicated to how you can use it take a00:12:05
look at the link in the description of00:12:07
this video00:12:09
now this is just a sample of some of the00:12:11
functions that you can use inside excel00:12:14
formulas make sure to check out my 2000:12:16
essential excel functions with00:12:17
downloadable reference guide if you want00:12:19
to look at more in detail and if you're00:12:21
new to excel make sure to look at the00:12:23
introduction to excel video i'll put00:12:26
links to both of these videos in the00:12:28
description below thanks for watching00:12:30
sealy training hey if you want to see00:12:32
more videos like this one please00:12:34
subscribe and if you've enjoyed this00:12:36
video be sure to click the thumbs up and00:12:38
leave a comment i really do appreciate00:12:40
your supportDescription:
How to use Excel formulas. If you need to use Excel for work, are a new student of Excel, or just need a refresher on several common Excel functions, then this video is for you. Support me with your Amazon purchases, click here: https://www.amazon.com?ie=UTF8&language=en_US 20 Essential Excel Functions Video: https://www.youtube.com/watch?v=8P4-jOoYBjo 10 Advanced Excel Functions Video: https://www.youtube.com/watch?v=cnmPSlajJVM Introduction to Excel Video: https://www.youtube.com/watch?v=c2dcRy1X9AA 50 Ultimate Excel Tips and Tricks: https://www.youtube.com/watch?v=FXs3WG7M-qk Contents 0:00 How Formulas Work 0:17 Functions in Formulas 2:03 Text Functions 4:07 Logical Function 6:16 Lookup Functions 8:28
Preparing download options
* — If the video is playing in a new tab, go to it, then right-click on the video and select "Save video as..."
** — Link intended for online playback in specialized players
Questions about downloading video
How can I download "How to Use Excel Formulas" video?
http://unidownloader.com/ website is the best way to download a video or a separate audio track if you want to do without installing programs and extensions.
The UDL Helper extension is a convenient button that is seamlessly integrated into YouTube, Instagram and OK.ru sites for fast content download.
UDL Client program (for Windows) is the most powerful solution that supports more than 900 websites, social networks and video hosting sites, as well as any video quality that is available in the source.
UDL Lite is a really convenient way to access a website from your mobile device. With its help, you can easily download videos directly to your smartphone.
Which format of "How to Use Excel Formulas" video should I choose?
The best quality formats are FullHD (1080p), 2K (1440p), 4K (2160p) and 8K (4320p). The higher the resolution of your screen, the higher the video quality should be. However, there are other factors to consider: download speed, amount of free space, and device performance during playback.
Why does my computer freeze when loading a "How to Use Excel Formulas" video?
The browser/computer should not freeze completely! If this happens, please report it with a link to the video. Sometimes videos cannot be downloaded directly in a suitable format, so we have added the ability to convert the file to the desired format. In some cases, this process may actively use computer resources.
How can I download "How to Use Excel Formulas" video to my phone?
You can download a video to your smartphone using the website or the PWA application UDL Lite. It is also possible to send a download link via QR code using the UDL Helper extension.
How can I download an audio track (music) to MP3 "How to Use Excel Formulas"?
The most convenient way is to use the UDL Client program, which supports converting video to MP3 format. In some cases, MP3 can also be downloaded through the UDL Helper extension.
How can I save a frame from a video "How to Use Excel Formulas"?
This feature is available in the UDL Helper extension. Make sure that "Show the video snapshot button" is checked in the settings. A camera icon should appear in the lower right corner of the player to the left of the "Settings" icon. When you click on it, the current frame from the video will be saved to your computer in JPEG format.
What's the price of all this stuff?
It costs nothing. Our services are absolutely free for all users. There are no PRO subscriptions, no restrictions on the number or maximum length of downloaded videos.