Basic Excel Formulas and Functions You NEED to KNOW!

Aug 14, 2023

Basic Excel Formulas and Functions You NEED to KNOW!

In this Excel formulas and functions tutorial you’ll learn important Excel functions. I’ll also show you an Excel trick that will make it easier for you to search for and properly use any Excel function.

You’ll learn simple formulas and functions in Excel. These Excel functions work for ALL versions of Excel (Excel 2007, Excel 2010 all the way to Excel for Office 365). The basic Excel functions we cover are: sum, count, counta, min, max and the average functions. We also cover autosum and the averageifs functions.

Time Stamps
00:00 How to Use Formula and Functions in Microsoft Excel
00:29 How to Write a Mathematical Formula in Excel
01:44 How to Sum Columns / Rows in Excel
03:24 How to Insert a Function in Excel
03:52 How to Use AutoSum in Excel
05:00 How to Calculate an Average in Excel
05:39 How to Count Numbers in Excel
06:29 How to Count Text in Excel
07:07 How to Calculate Min and Max Values in Excel
07:33 How to Use AVERAGEIFS in Excel
10:06 Wrap Up

⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-formula…

More Excel Tutorials for Beginners: • Excel Introduction - Learn Excel Basics

Intermediate Excel Functions (including the Excel IF and VLOOKUP functions): • Excel Intermediate Functions \u0026 Featur…

______________
My Excel Essentials Course: https://courses.xelplus.com/p/learn-e…

★ ALL my Excel Courses ► https://www.xelplus.com/courses/
_____________

✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/

EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/

Get Microsoft 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327…

GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347…
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U

More resources on my Amazon page: https://www.amazon.com/shop/leilagharani

Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#msexcel #excel

Content

0.22 -> When you use Excel

1.34 -> it's important to use formulas and functions correctly.

4.53 -> In this video, I'm going to cover common functions

7.42 -> that you're going to need.

8.5 -> It's great if you're in Excel beginner,

10.64 -> or if you generally aren't comfortable

12.75 -> using functions in Excel.

14.62 -> I'll show you a trick that makes it easier

17.26 -> to use any type of function.

19.33 -> Let's get to it.

20.235 -> (playful upbeat music)

24.31 -> We're going to be using this sample data set to practice on,

27.01 -> we have name, department and salary.

29.57 -> Now in Excel when you want to type in a formula,

31.87 -> you start with the equal sign.

33.86 -> Then using your mouse or the arrow keys,

36.86 -> you can move over to the cell you want to select.

39.73 -> So let's see, I just want to add two numbers,

42.47 -> I'm going to select this cell, type in a plus sign

45.21 -> then with my arrow keys on the keyboard, go

47.66 -> and select another cell

49.54 -> and then when I'm done, I can press enter.

51.567 -> Now you can use the typical mathematical operations

55.28 -> that you're used to.

56.22 -> So here I could go ahead

58.08 -> and add a minus and then go and select this cell

61.79 -> and when I press enter I get the final results.

64.63 -> And all of this is dynamic, so if something changes here

68.37 -> my result is going to update automatically.

70.94 -> I'm just going to press Control + Z to undo this.

74.14 -> Now, in addition to plus and minus,

76.31 -> you can also divide and multiply.

78.97 -> So let's say I want to remove this part of the formula

82.03 -> and then I just want to divide this cell with the other cell.

86.44 -> Type in a slash and press enter.

88.75 -> To multiply two values together,

91.3 -> you have to use the asterisk sign.

93.95 -> Now you can also use brackets.

96.13 -> So let's put these inside brackets

99.54 -> and then let's divide the results

101.69 -> with let's say this number and press enter.

104.65 -> Okay, so your typical mathematical operations work

108.48 -> in Excel formulas as well.

110.19 -> But if you wanted to add all of these numbers here,

113.69 -> you don't want to go in and do a plus for each number.

117.6 -> This is where Excel functions come into play.

121.04 -> So there are pre-programmed functions

123.85 -> that do the plus for you.

125.61 -> So for example, if I want to add all the salaries together,

129.81 -> I can use the sum function.

131.92 -> So just start typing sum

133.87 -> and you can see the list of functions popping up here.

136.92 -> You can select it with your mouse

138.54 -> or you can use the tab key to select the first one.

142.21 -> And then here you can add different cells

145.42 -> but in this case, we don't want to do different cells,

148.14 -> we want the entire range.

150.13 -> So with your mouse, you can select this

152.05 -> you can also go with the arrow keys

154.18 -> and hold on to shift key and select this range.

157.47 -> And then you close the bracket and when you press enter,

160.74 -> you have to sum of this entire range.

164.01 -> So functions in Excel are programmed machines

167.66 -> that do a certain task.

169.44 -> The sum function sums up different values,

172.75 -> then we have the count function

174.88 -> which is going to count different values.

176.92 -> Now we're going to see this in a second

178.94 -> but I want to show you one thing before we get there.

182.07 -> How do you know what a function is called?

184.65 -> If I'm new to Excel,

185.98 -> I'm not going to know about this function.

188.11 -> I might be typing in add and there is no add function

192.1 -> you can get help from the formulas tab.

195.22 -> Now talking about being new to Excel,

197.32 -> if you're also new to this channel welcome

200.17 -> and consider subscribing

201.49 -> so you can find these tutorials easier.

204.46 -> So under formulas tab, you have insert function,

208.94 -> this is something you can use to find a specific function.

212.8 -> So let's say I want to add values.

216.14 -> I'm going to press enter and I get suggestions here

219.257 -> and when I click on them, I can read about this function.

222.56 -> I see the SUM function here,

223.98 -> it adds all the numbers in a range of cells.

226.677 -> Now we're going to get back to insert function in a second,

229.74 -> I'll just press cancel on this now.

232.3 -> Let me show you another way of summing values

235.37 -> and that's using AutoSum.

237.1 -> When you click on this drop down,

238.92 -> you have the option to select the type of function you want.

241.8 -> So in this case, we want to sum

243.67 -> so I'm just going to select this

245.28 -> and it does the whole work for me.

246.88 -> All I have to do is check

248.61 -> whether the range it selected is what I want,

251.96 -> in this case it is, so all I need to do is press enter.

255.43 -> Now, there is also a shortcut key for this.

257.7 -> You might have caught it when I clicked on AutoSum

261.16 -> it's Alt and the equal sign.

263.6 -> So when you're here, just type in Alt and equal,

266.67 -> press enter and you have your sum.

269.71 -> Now, this also works horizontally.

271.72 -> So if I have a number here, I can go

274.04 -> and do Alt + Equals and it picks up the correct range,

278.03 -> and all I have to do is press enter.

280.04 -> Because summing values or getting to average of values is

284.38 -> such a common task.

286.17 -> You don't just find it under the formulas tab here,

289.13 -> you have AutoSum available in the home tab.

292.51 -> So directly from here on the editing side, you have AutoSum

297.61 -> and you can select the function that you need.

300.46 -> So now let's take a look

301.395 -> at getting the average of our values.

304.44 -> Now, on the side here, I'm just going to type in sum

307.5 -> so we know what each function represents.

310.29 -> Let's get the average here and now again I can start

314.62 -> by either typing in the function directly

317.01 -> and then selecting it with tab, highlighting my range,

320.89 -> closing the bracket and pressing enter.

323.35 -> Or I just go to the AutoSum options here

326.75 -> and select AVERAGE, but now take a look at this,

329.98 -> it picked up this value as well so I need to correct this.

333.48 -> So instead of C11, I want C10, I'm going to press enter.

338.95 -> Next let's count numbers, so I'm going to type in count,

342.64 -> on the side here I get some information

344.93 -> about what this function does.

346.58 -> So it counts the number of cells in a range

348.84 -> that contain numbers.

350.18 -> That's what I want so I'm going to open the bracket

352.84 -> or press tab and it opens the bracket for you

356.43 -> then select this range, close bracket, press enter

359.99 -> and that's the count of cells that have numbers.

363.92 -> So if one salary information is missing

367.1 -> my count is going to be reduced.

369.29 -> So I'm going to press Control + Z to go back,

371.72 -> now just to know what we're doing this was count salary.

376.08 -> Can I use this to count names?

379.68 -> Well, let's try it.

381.05 -> I'm going to use the COUNT function,

382.787 -> this time I'm going to press tab.

385.11 -> Let's select this, close bracket press enter and I get zero.

389.53 -> So the COUNT function only counts numbers,

392.16 -> it doesn't count texts.

393.93 -> If you want to count texts

395.72 -> you need to use a different function

398.54 -> and that's the COUNTa function.

401.34 -> This function counts the number of cells in a range

404.01 -> that are not empty.

405.71 -> So it doesn't care whether it's texts or it's a number,

409.8 -> it counts both of them.

411.4 -> Now I'm going to press enter and I get eight.

414.66 -> So if one of these happens to be a number

417.2 -> I'm going to put a zero, it's going to count it,

419.89 -> it only doesn't count it if it's empty,

423.44 -> just press Control + Z a few times to go back.

426.13 -> Another two super useful functions are

428.943 -> the MIN and the MAX functions.

432.35 -> So let's say I want to get the minimum salary in the range.

436.26 -> I need the MIN function, select the range,

439.58 -> close bracket, press enter

441.23 -> and it's going to give me the smallest number in that range.

444.88 -> In a similar way, we can get the maximum salary

448.63 -> from this range, close bracket, press enter

452.22 -> and that's our number.

453.44 -> Now, what if you wanted to do something more complex

456.29 -> like getting the average salary of people

460.35 -> who are in the sales department,

462.55 -> how would you go about this?

464.58 -> Well, we can start off by typing in average

468.38 -> to see the different options we have.

470.59 -> And then we can check this description

472.26 -> to see if it does what we want it to do.

475.15 -> So here we have AVERAGEIF which looks good

479.16 -> because it takes into account a given condition.

482.2 -> And we have something called AVERAGEIFS

485.31 -> which is a given set of conditions.

488.19 -> To be keen include more than one condition in this formula.

492.35 -> Now let's say I'm interested in this

494.25 -> because I might add in other columns here

497.26 -> and I might want to have different conditions.

500 -> So I'm going to double click this, or you can press tab

503.61 -> and then let's go to the formula bar and see what we need.

506.83 -> It says we need average range criteria range one,

509.83 -> criteria one.

511.19 -> Now all of this doesn't really mean

512.86 -> much to me if I'm new to Excel.

515.04 -> So here's what you can do.

516.75 -> Click on this button, that's the insert function button

520.44 -> and it takes you to this dialog box

523.01 -> which gives you a bit more information.

525.55 -> So for average range here this is the actual cells

529.53 -> to be used to find the average.

532.25 -> So this means these are my number cells

535.14 -> that's the range I need to select.

537.06 -> I can get a preview of what I selected right here

540.05 -> then what this criteria range one mean,

542.75 -> this is the range of cells you want evaluated

545.97 -> for the particular condition.

548.67 -> That's my range.

551.44 -> Next criteria one.

553.4 -> This is the condition or criteria

555.41 -> in the form of a number expression or texts

558.55 -> that defines which cells will be used to find the average.

562.69 -> So that's sales.

564.26 -> Now I can do a cell reference here

566.04 -> if I had sales sitting in a separate cell here

569.36 -> or I can just directly type it in.

572.18 -> And notice something the moment I click away,

575.01 -> it's going to put my texts in quotation marks,

577.47 -> because if you're using texts inside a formula,

581.33 -> you need to put quotation marks.

583.37 -> Now take a look at this,

584.58 -> I already see the answer here.

586.86 -> So I have a good idea that my function is working properly

590.82 -> and then all I have to do is press on okay

593.17 -> and the function is inserted here.

595.77 -> Okay, so this is a great feature to use

598.13 -> when you're new to Excel

599.89 -> and don't really understand what Excel wants from you

602.97 -> for each of these different requirements.

605.89 -> So that ends our introduction

607.44 -> to Excel formulas and functions.

609.47 -> I have many videos

610.87 -> on the channel covering different functions in Excel.

613.8 -> So make sure you check them out

615.7 -> if you ever need to write more complex functions

619.25 -> to get your analysis done.

621.08 -> If you like this video don't forget to hit that thumbs up

624.74 -> and do consider subscribing if you aren't a subscriber,

628.48 -> it would be great to have you as a part of our community.

632.48 -> Thank you for watching and I'll see you in the next video.

635.338 -> (upbeat music)

Source: https://www.youtube.com/watch?v=y1126PQ5zRU