Excel is great 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.
- [Instructor] Excel is great 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 operation. In this movie I will show you three different ways to create formulas. My sample file is the Create workbook, which you can find in the Chapter 5 folder of the exercise files collection. This workbook contains two worksheets, and I'm working on the Summary sheet. This worksheet contains a list of inquiries, counted by month, for a particular year.
And let's say that I want to create a formula that finds the sum of all those inquiries. There are number ways I can do that. The first is to type and equals sign, and then if I know the name of the function I want to use, I can just start typing it. In this case it would be sum, S-U-M. You see that the formula auto complete list pops up, and sum is at the top of most recently used list, that's because it is the most commonly use function. And then under the general functions list you'll also see it at the top, because it occurs there alphabetically based on what I've typed in.
I can continue creating my formula by typing a left parentheses, and then selecting the cells that I want to add. That would be B4 through B15. Type a right parentheses to close everything out, and return, and I get the sum of those values. Another way that you can create a formula is by using the formula builder. So, I will press the up arrow key to select cell D3 again, and press delete to get rid of the existing formula.
I will go up to the formula bar here, and you see that there is an insert function button. It looks like an F of X, which is math speak for a function. I'll click that. I get an equals sign, and then I get the formula builder. I can look in the most recently used list, and I see here that I have sum, or I could also search. So if I do add, that I get address and look up reference, so I know that's not right.
So if I type sum, then I get most recently used and so on. So, I will make sure that sum is highlighted, and click insert function. Now, within the formula builder I can enter in the numbers that I want to use. In this case I want to use a range of numbers from B4 to B15, like I did before. So with the cursor flashing in the Number1 box, I will select cells B4 through B15, and you can see the cell range being filled in here, in cell D3, and also here in the formula builder.
Everything looks good. I will click done, and my formula goes in to cell D3. To close the formula builder, just click its close button at the top right corner. Another way to identify available formulas or functions is to go to the formulas tab of the ribbon, and then look amongst the various categories. So financial, logical text. If I wanted to do math and trig, I could click that, and then scroll down until I see sum, which is a math and trigonometric function.
And I'll click away. One final technique I'd like to show you is how to use a cell on another worksheet, and this also applies to cells in other workbooks. I'll click cell D6, then type and equals sign. Now let's say that I want to calculate the cost to replace 10 percent of the inquiries if I lose them. So in other words I have a known acquisition cost, which is on my other worksheet, and I want to multiply 10 percent of the value in D3, the number of inquiries I had with the cost to replace 10 percent of those.
First thing you need to do is get 10 percent of the value in D3. So after the equals sign I'll type a left parentheses, and that will be D3, I'll just type the value straight in, multiplied by 10 percent, and then a right parentheses to close out, and then I'll multiply that by the acquisition cost. The acquisition cost is on the Acquisition Cost worksheet, so I'll click it, and I will click cell B1, which is where the cost is acquired.
I have the formula that I'm supposed to get here, in cell C8, you can see it here in the formula bar as well. Press return, and I go back and I see the cost to replace a 10 percent loss. You can use the same technique to create formulas using cells and other workbooks. Just make sure the other workbook is open, and then start creating your formula, and move to that workbook, worksheet, and cell, just like I did with the Acquisitions Cost worksheet here.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks