Data Groups & Formulas:
Inserting & Formulas

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

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.

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

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & Formulas To subtopics
    SubtotalsTo subtopics
    Images & DrawingsTo subtopics
    Changes & Formulas To subtopics
    FootprintCopies & Formulas
    FootprintMerges & Formulas
    FootprintSorting & Formulas
    FootprintInserting & Formulas
    ExercisesTo subtopics

Project 5: DesignTo subtopics


Icon Step-by-Step

Step-by-Step: Inserting & Formulas

 Icon Step-by-Step

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: Class disk 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.

  1. On the sheet Specials select row 15.
  2. From the menu select  Insert  |  Row . 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.

    New row inserted and formulas recalculate

    What changed?
    • In the new row Total sale shows up automatically, $15,000.
      Icon: Excel 2002Icon: Excel 2003 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

    Insert data at border

    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

    Excel 2000 Icon: Excel 2002Icon: Excel 2003 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!

    Effects of inserting a row in Other sheet (Excel 2000)

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

    Excel 97 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.

    Effects of inserting a row in Other (Excel 97)

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

  6.  Class diskSave as  trips30.xls 
    Full floppy disk 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.

Excel 97 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.

    Formulas fixed in Excel 97 after inserting row

    Hand editing fixes the formulas

  4. Class diskSave. [trips30.xls]
    Full floppy disk 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!

     Inserted new row 5. Has same formatting as column labels.

  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]

    Formula not there after inserting row 5 and entering data.

    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.

    Formulas fixed to include row 5 data

  3. Class diskSave. [trips30.xls]
    Full floppy disk How to handle a full disk
  4. Select the upper table on the sheet Specials [A1:F28].
  5. Print 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.

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.