# Data Groups & Formulas: Inserting & Formulas

Did you want Working with Numbers: 2007,2010,2013,2016  or español

Sometimes inserting rows and columns causes formula problems and sometimes it does not. What makes the difference is where you are inserting and what the formula range includes.

### Where you insert makes a difference:

• Inside the range: All is well. Excel understands that the new cells inside a formula's range are supposed to be included in the results. Excel adjusts the formula for you.

• Completely outside the range: Of course you don't expect the formula to include this data, and it doesn't!

• At the edge of the range:  The formula may not use those cells, especially Excel 97.

To have Excel 97 automatically include in a range any new cells at the right edge or bottom edge of the formulas range, you must have blank cells there that are already included in the formulas range.

Where you are:
JegsWorks > Lessons > Numbers

Project 2: Excel Basics

Project 5: Design

Search
Glossary

Appendix

## Step-by-Step: Inserting & Formulas

 What you will learn: to insert row without breaking formula to insert row that breaks formula to fix a formula broken when cells were inserted

After you have gotten your worksheets all fixed up and formatted, you find that you have omitted some trips. Whoops! You need to insert some more trips on the sheet Specials to check out the various possibilities. Since rows and columns behave the same, you can test the variations with rows:

• inside range
• at the bottom border of range with blank row
• at bottom border of range without a blank row
• at top border

### Insert Row: Inside Range

The first new trip to add is to New Zealand, sold by Gardner to Midland Associates for 5 people. (He must be a good salesman to sell them a second trip so soon!)

You need to put this information in the middle of the table between rows 14 and 15.

1. On the sheet Specials select row 15.

2. From the menu select | . A new row appears above the selected row.

3. Copy the information in the first 3 cells of the row 14 into the new blank row. [Hint: use the key combo CTRL + '.]

4. In the Number of People column enter the number 5.

What changed?
• In the new row Total sale shows up automatically, \$15,000.
In Excel 2002/2003, if the formula does NOT automatically appear, copy the formula from another cell into F15.

• The Totals for the table, cell D26 and F26, now includes the new values in their columns. The ranges of the formulas changed to include the inserted row and plus all the original rows. The new row was inside the range of the original formula: =SUM(F5:F24). Cool!

### Insert Row: At Bottom Edge of Range (Inside Range)

The second trip to be added is in the Other category

1. Select cell F26 and inspect its formula =SUM(F5:F25).
This formula includes the blank row below the last Total sale value.

2. Select row 25 and insert a row.

3. In the new row 25 enter the following:
Trip: Other
Travel Agent: Chavez
Customer: Rachel Williams & Associates
Number of People: 15
Cost each: \$2500

Again, the Total sale formula automatically calculates and the Totals for the table includes the new values.  The new row wound up inside the formula's range for both Totals. This is working out pretty easy!

### Insert Row: At Bottom Edge of Range (Outside Range)

You will add the third trip to the Other category also, but outside the range of the Total for the whole table. This row is between the data and the cell with the formula and there is no blank row.

1. Delete the blank row 26.

2. Inspect the formula in F26, which now shows =SUM(F5:F25). There is no blank row.

3. Select a cell in row 26 (the Totals row) and insert a new row. It appears above the selected row and has the formatting of the row above it.

4. Inspect the formula in F27, which shows=SUM(F6:F25). It does not include the row you inserted at the bottom edge of the formula's range.

5. Add the following information to row 26:
Trip: Other

Travel Agent: Heinz

Customer: Smith, Thomas

Number of People: 2

Cost each: \$3575

In Excel 2000/2002/2003 - This row is included in the calculations just like before. Excel guesses that you want this data included and changes the formula after you add data. What a good guess!

In Excel 2000 - cells at bottom border of range ARE included in formulas

In Excel 97 - This row is not used by the totals formulas! The Total Sale column remains empty. You have 3 formulas to straighten out.

In Excel 97 - cells at bottom border of range are NOT included in formulas

6.  Save as  trips30.xls
How to handle a full disk

### Fix Broken Formulas

If inserting cells breaks a formula, you must edit the formula to include the new cells.

In Excel 97 --

1. Total Sale:  Select cell F25 and AutoFill down to F26.

2. Total of Total Sale column: Select cell F27, the Total for the table, and edit the formula's range to include F26  [ =SUM(F5:F26)]

3. Total of Number of People: Select cell D27 and edit it similarly.

Hand editing fixes the formulas

4. Save. [trips30.xls]
How to handle a full disk

### Insert Row At Top Edge of Range (Outside Range)

1. Select row 5 and insert a new row. It appears above the selected row but with the formatting of the columns labels. Not a good guess by Excel!

2. Reformat row 5: Merge E5 with the merged cell E6. Use Format Painter to copy the formatting of the other cells in row 6 onto row 5.

3. Add the following information to row 5:
Trip = Tahiti
Travel Agent = Heinz
Customer = 1st Bank
Number of People = 2
[Cost each is already in the merged cell]

The Total sale is not calculated and the totals in row 28 do not change. The formulas did not adjust to include your new row. It's back to the repair shop for the totals formulas.

### Fix Broken Formulas

1. AutoFill from cell F6 to F5. Now the Total sale is calculated, but the Totals at the bottom in row 28 still do not include row 5.

2. Use AutoSum to revise the formulas in cells D28 and F28.

3. Save. [trips30.xls]
How to handle a full disk

4. Select the upper table on the sheet Specials [A1:F28].

5. Print the selection.

### Check Links on Other Sheets

You added four rows to the original data for 1st Bank, Midland Associates, Rachel Williams & Associates, and Thomas Smith. Did any of your sheets with linked cells pick up the new data?

1. Select each of the other sheets in turn and look for changes.

There aren't any! Linking cells does not link ranges, just individual cells. The new rows do not show up in the ranges. You would have Paste Link all over again to update the other sheets. Since this is not the "real world", we can just let those sheets stay as they are.