In this video, learn how to insert a built-in function using the inspector, the Insert menu, and the Insert button, and use data in the function from other sheets in the file.
- [Instructor] Sometimes when working with lots of data here in Numbers, a formula just won't do. If you need to total up many, many rows of values, using a formula can be very tedious and sometimes you need to complete more complex functions that would take a lot of know-how and could be time-consuming and difficult to create. That's why there are a number of built-in functions available here Numbers and we're going to explore one now. Let's go to our Annual Budget Overview table here in our Leaf & Mortar budget file, 0502 if you're catching up, where we see a number of values showing up and we used formulas to calculate the differences.
If we go to cell, for example, B3, just click right here on the Estimated, Total income, and look down at the formula bar, you can see, it's just a number that was entered. But this actually comes from the Income Details, and if we were to use the numbers on the Income Details sheet, you can see we have Estimated and Actual. Adding a formula that takes D2 plus D3 plus D4, all the way down to row 37, would be very tedious to type in a formula.
That's why we're going to use a function, a popular one, that will sum them up. Let's go back to Profit-Loss Summary. We need to remove all of these numbers here in these four cells. I'm going to click and drag from D3 across and down to C4, so all four cells are selected. Hit your Delete key to remove the contents. You can see the Difference column and row is updated with zeroes until we get actual values in here, but we're going to go get the values from another sheet. Let's start with Estimated, Total income here in D3.
Now, to insert a function, we can go up to the Insert menu, go down to Formula, and you'll see a number of very popular functions, like Sum, Average, we want to count the number of values in a selection of rows or columns, you can use Count. lots of built-in functions available here. Let's click in the background. Another option is to go to the Insert button. You'll see some of them here listed as well, along with New Formula.
I'll click in the background. You might like to start formulas with the equal sign. That goes for functions as well. Hit the equal sign and take a look over here at the Inspector. It's now showing Functions, and with All selected, you're going to see there's a long list of built-in functions, very powerful functions, some more simple than others, such as our Sum function. We could scroll down till we find it, there it is, and select it. With SUM selected, you can see down below, we'll get a definition.
It's actually going to return the sum of a collection of numbers. That's exactly what we want. We can use the search field to search for functions too. If we know we want to sum them up, you can see all of the functions that start with sum here at the top. Some of them do different things. SUM is what we want, so all we have to do is select it and click Insert Function. It's the same as doing it from the Insert button or the Insert menu. Now you can see it's waiting for a value. The value is in gray. Click it, and you can see it's highlighted.
Now we can change the value by going to another sheet and selecting that range of cells for our estimated total income. Click Income Details, go to the Estimated column at the top, in cell D2, click and drag all the way down to the last row of data, which is going to be row 37. And with that selected, you can see this is what we could have typed out, SUM Income Details, that's the name of the sheet, Table 1, that's the table we're looking at, and there's the range, D2 to D37.
It's much easier to simply click and drag over those cells, click the green circle check mark to get the value showing up from another sheet. Excellent. Now, the actual total income will come from the same sheet. Let's try it this way. We'll go to the Insert button and choose Sum. Now, Numbers is going to try and look at the table and find values and sum them up for you. That's exactly what's happening. Click in cell C3, and you can see it's just trying to take the estimated total income and add it up.
Well, we actually want to click there to select it and change it. So by clicking it, we can now go to our Income Details. This time, go up to the Actual column, that's cell E2, click and drag down, you can see we're actually replacing what Numbers tried to add for us with our own range. With that selected, click the green circle check mark, and there's the new value and there's the new difference. Let's do the same now for estimated total expenses. In this case, we'll go to the Insert menu.
Let's go down to Formula and choose Sum. Again, Numbers is going to try and figure out where there are numbers and total them up for you. It's trying to take the numbers above and add them up. That's why we see the same value. By clicking cell B4, there it is, Estimated Total Income, click to select it so we can change it. It's the Estimated Total Expenses that we want, so we'll go to the Expense Details sheet this time and the Estimated column is column E starting at row two. So, E2, click and drag all the way down.
This is a longer table, many more values. Let's go all the way down to row 74. Next row is just getting started here. And we'll click our green circle check mark. For the actual total expenses, I'm going to go up here, click the dropdown, choose Sum. Again, it's going to try and use some other values in this table. Click the cell, click Actual Total Income. It's Actual Total Expenses we want from Expense Details. And we'll scroll up.
There's the Actual there, column F. F2, click and drag all the way down to the bottom to row 74, and the green circle check. There we go. Click in the background to see those values. Now, if anything changes in the Expense Details sheet or the Income Details, we'll see them reflected here in these cells and our totals. That's the beauty of using functions. Now, what if we wanted to add another column here and calculate an exchange rate on the difference? Let's do that.
We'll click anywhere in this table, go up here to the top right handle, next to column D, click and drag that out until we see a new column up here, column E. We'll click in cell E2 and type in Difference in $CDN, Canadian dollars. Well, in that case, we're going to want to take the difference value and multiply it by an exchange rate. That's going to require us, if we're going to copy this formula, to create what's called absolute cell addressing, and we'll talk about that next.
LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Recall how to customize the user interface.
- Explain how to save a spreadsheet.
- Describe how to customize table cells and borders.
- Cite the steps for adding an image to a cell.
- Distinguish between different functions and explain how to use them.
- Explain how to format general chart attributes.
- Describe how to use interactive charts.
- Cite how to insert hyperlinks into a document.
- Recall how to add and arrange images.
- Summarize how to collaborate with iCloud.