O'Reilly logo

Excel 2010: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Performing Table Calculations

Excel tables have several nifty features that help you out when you're performing calculations on the data inside a table.

One example's the way that automatic expansion works when you're adding new columns that contain calculations. For example, say you've got a table with three columns: Product ID, Model Name, and Price. If you want to add a new column that tracks the discounted price of each product (say, 90 percent of the regular price), then a table's a great timesaver. Once you've created the new discount price column for one row (see Figure 14-21), Excel fills the same calculation into every other row (Figure 14-22). You wind up with a table that shows the discounted price of every product. No copying and pasting required.

This worksheet shows a new column with a formula that's in the process of being entered. See Figure 14-22 for what happens next.

Figure 14-21. This worksheet shows a new column with a formula that's in the process of being entered. See Figure 14-22 for what happens next.

Once you finish the formula shown in Figure 14-21, and then hit Enter, Excel expands the table to include the new column, and then fills the formula down to include every row. And if you scroll to the bottom of the table and start adding a new row, Excel is intelligent enough to automatically copy this new formula into the new row. In other words, once you add a formula to a column, that formula is sticky.

Figure 14-22. Once you finish the formula shown in Figure 14-21, and then hit Enter, Excel expands the table to include the new column, and then fills the formula down to include every row. And if you scroll to the bottom of the table and start adding a new row, Excel is intelligent enough to automatically copy this new formula into the new row. In other words, once you add a formula to a column, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required