Working with formulas in tables
Video: Working with formulas in tablesIf you worked with formulas in tables, there is a distinct advantage to the fact that Excel copies formulas down the column instantly, it's also a slight downside as we shall see. In this particular worksheet which has been converted into a table, we want to put in New Salaries in column K. And everybody in this list is going to get an increase of 2.91%, that's the value that's in cell M1. Existing salaries are in column I. So I'm going to write a formula for the very first person here.
- Next steps
Viewers: in countries Watching now:
In this course, author Dennis Taylor shows how to use Excel for creating a simple database. The course explains the limits of Excel as a data management tool and spells out the design considerations for creating a database. It also demonstrates using tables to simplify database creation as well as the Form and Data Validation tools to manage data.
- Understanding database design concepts
- Using the Text to Columns feature
- Creating fields
- Using tables
- Adding total fields
- Formatting tables with the Design tab
- Using the Form tool to build and view data
- Using data validation to restrict data entries
Working with formulas in tables
If you worked with formulas in tables, there is a distinct advantage to the fact that Excel copies formulas down the column instantly, it's also a slight downside as we shall see. In this particular worksheet which has been converted into a table, we want to put in New Salaries in column K. And everybody in this list is going to get an increase of 2.91%, that's the value that's in cell M1. Existing salaries are in column I. So I'm going to write a formula for the very first person here.
Equal and I want to click on the cell I2, it's got the salary for the first person. And when I do, what do we see? An unusual kind of construction here refers to this as a salary. And what do we want to do with this salary? We want to multiply it, there's an asterisk, times this cell M1, and as a click on M1, look what pops up on the screen. Do we've to worry about this being an absolute address, we're going to copy it down the column, no, we don't have to worry about that either. What does this formula do as of now? It's going to be calculating the salary times the percent.
So if we were to press Enter, we would only have the amount of the increase. So let's add on to this the existing salary plus, and once again I'll click on I2. So that's a pretty long formula, and it may not be as clear as you want. But look what happens when I press Enter. It's entered into every single cell here, and every one of these, for example, this one or anyone, if you double- click it, you'll see what it's doing, it's consistently referring to the percent in cell M1; 2.91%. We actually see it here as well too.
If you go to the very bottom, we see how far this got copied. In this list of some 741 names, it got copied all the way to the bottom. Now you might find those formulas on wieldy, they certainly don't jump out and say exactly what's going on. I'm going to press Ctrl+Z, press Ctrl+ Z again, and rewrite the formula using actual cell addresses. And although I'm not a fan of typing I think many people might prefer this approach to using formulas in tables, because it does retain the copying aspect of it that's so powerful.
Equal and this time I'm going to type I2, that's the first-person in the list that has a salary. I2 times and the salary increase percent is in cell M1, let's type that. We do need to make this absolute by pressing F4; That'll take care of that. And then let's add on this the existing salary I2, and as I press Enter, again, the powerful part of this is the formula gets copied all the way down the column, so everyone of these entries here is accurate and sure enough.
Press Ctrl+Down Arrow, we go all the way to the bottom. So writing formulas in tables can be very efficient and you may or may not want to go with the standard technique of clicking on the cells, or you might do as I did in the example here, type in the actual addresses. And I think for many people this latter approach is better, because it does retain that familiar look of formulas.
There are currently no FAQs about Setting Up a Database in Excel 2010.