|
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 |
Start with:
trips29.xls
(saved in previous lesson)
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.
- On the sheet Specials select row 15.
- From the menu select | . A new row appears above
the selected row.
- Copy the information in the first 3 cells of the row 14 into the
new blank row. [Hint: use the key combo CTRL + '.]
- 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
- Select cell F26 and inspect its formula =SUM(F5:F25).
This formula includes the blank row below the last Total sale value.
- Select row 25 and insert a row.
- 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.
- Delete the blank row 26.
- Inspect the formula in F26, which now shows =SUM(F5:F25).
There is no blank row.
- 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.
- 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.
- 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
-
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 --
- Total Sale: Select cell F25 and AutoFill down to F26.
- 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)]
- Total of Number of People: Select cell D27 and edit it similarly.
Hand
editing fixes the formulas
-
Save. [trips30.xls]
How to handle a full disk
Insert Row At Top Edge of Range (Outside Range)
- 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!
- 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.
- 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
- 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.
-
Use AutoSum to revise the formulas in cells D28 and F28.
- Save.
[trips30.xls]
How to handle a full disk
- Select the upper table on the sheet Specials [A1:F28].
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?
- 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.
Conclusions about Inserting
- Inserting rows and columns inside a formula's range updates
the formula automatically.
- Inserting a row or column at the bottom edge of a range may not
update the formula. You must check to see if it did.
- Inserting a row or column at the top edge of a range will not
update the formula.
- Inserting rows and columns does not update other sheets with
linked cells.
