Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Excel is a wonderful tool for storing data about your business. What's even better is that you can create formulas to summarize that data and gain insights into your operations. The most straightforward way to create a formula is to type an Equals sign, followed by the formula. So, for example, if I wanted to find the sum of all the values from cell B3 to B10, I can type equal and then sum. Now note, when I type an S, Excel uses what's called formula Auto Complete to display all of the functions that start with the letter S.
SUM appears in my Most Recently Used list, but you'll see here, under Functions, that there are quite a few other ones that start with S as well. If I type a U, the list is reduced substantially. If you want to select a function name from the list, from the formula Auto Complete list, you can use the down arrow key to highlight the function you want, or you can use your mouse pointer to click the one that you want. When you do, Excel creates your function and gives you a set of open and close parentheses. To create this formula, I can just type B3: - again, I'm talking about a cell range - to B10, and that indicates that I want to summarize, or find the sum of, the data in cells B3 through B10.
Everything looks right. I can press Return, and Excel creates my Formula. But let's say that you don't know which function you want to use. You're not sure which one makes the calculation you want, so you need to get some help. There are several ways to get help on using formulas and functions in Excel. One of the best is the Formula Builder, and that is on the Formulas tab, and you can click Formula Builder button to display the Formula Builder page of the toolbox. Now let's say that instead of finding the average or the sum of the values from B3 to B10, I wanted to count the number of values from B3 to B10.
I'm not worried about the total; all I want to know are how many there are. So let's say that I want to search for a function, and I'll just type in Count Cells. When I press Return, Excel displays a list of functions that are likely. First, we see the Most Recently Used list, and in there, we have the COUNT function. So I'll click that, and when I do, in the Description panel, I see that the COUNT function counts the number of cells in a range that contain numbers. I also see that Excel displays the syntax, which is how I need to type the formula in.
So I have the syntax; it's COUNT. So I can close the Formula Builder, and in this cell, I can type equal - I already have the Equal sign - and then type COUNT, which appears here in the Auto Complete list, and the cells that I want to count are from B3 through B10, not B11. That's not an actual value. That's the sum of all the other values. So I'm counting the number of cells from B3 to B10 that contain a number. I can press Return, and I see that there are eight.
You can also get help on functions by clicking the Reference button, which is also on the Formulas tab of the Ribbon. It's here. When you do, Excel opens the Excel Help dialog, and you can look at the different categories of functions to see which ones you want to use. So let's say that you wanted to do some math, such as finding a sum, finding an average, and so on. To do that, you would click Math and trigonometry functions, and when you do, Excel displays all the functions that are available: Absolute value, and so on, all the way down.
To close the Excel Help dialog, just click the close button at the top-left corner. You can also list functions by category by clicking the Insert button. Again, we're still on the Formulas tab. But if you click the Insert button, Excel displays a menu of the different categories of functions you can create. So if you hover over Date and Time, you see that you can find an Hour, a Minute of a particular day or time, and so on. Right after data entry, creating formulas is the most valuable skill when working in Excel. If you spend some time browsing through the functions available to you, you'll become more familiar with the building blocks you'll use to create your formulas.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98765 Viewers
80 Video lessons · 141635 Viewers
59 Video lessons · 59975 Viewers
52 Video lessons · 73138 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.