Data Groups & Formulas:
Copies & Formulas

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

The sheet Formatted Groups was created with the Move or Copy... command. All the cells are just copies of cells on Specials. They do not change when the original cells change.

If you find  out later that some of the data is wrong on Specials, you have to fix it in both sheets. If the cells on Formatted Groups are linked to Specials, you can fix both sheets by fixing Specials. Much better!

In this lesson you will work with Formatted Groups to link all the cells to Specials. It cannot be done in just one step. You merged some cells on Formatted Groups. Those merged cells can be linked, but they create a small complication for using AutoFill on the rest of the table.

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
    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: Copies & Formula

 Icon Step-by-Step

What you will learn:

to find out if a cell is a copy or if it is linked
to change a copy to a link

Start with: Class disk trips26.xls - Formatted Groups sheet (saved in previous lesson)

Copied or Linked?

  1. Select sheet Specials and change the number of people that AAA Sales bought trips for to 10 in cell D5. Click Button: Green checkmark the green check mark to enter the new value so your selection will not move.
  2. Select the sheet Agents Totals and expand the data. Look at the value showing for AAA Sales. It is still 7. The sheet was not updated because it was a simple copy of the values.
  3. Select sheet Tahiti and look in cell D5 at the number of people that AAA Sales bought trips for. This cell has been updated with the new number you entered. The data cells on this sheet are linked to the original data.
  4. Undo your change. (You don't have to switch sheets to Undo. There is only one list of actions.) You should be moved back to the sheet Specials with 7 back in cell D5.
  5. Select sheet Specials and change the value of cell E29 to 12. Click Button: Green Checkmark the green check mark to enter the new value.
  6. Select sheet Tickets Sold Chart and look at the first bar. It has been updated with the new value for the sales in the first week. Chart values are linked to the original data.
  7. Undo your change.
  8. Experiment with other changes, if you like.

    Do you remember which cells are copies and which are linked? You don't have to remember! Look at the Formula bar. When you select a cell that is linked, the Formula bar will show a cell reference, like =Specials!C10. Don't forget to undo each of your changes to the data.

    Tip If you are not sure you got all the changes reversed, close the file without saving it and then reopen it. You will be back to the last saved version.

Change Copies to Links: Formatted Groups

The sheet Formatted Groups is a copy. What would it take to link these cells? There are a couple of issues to deal with but fixing the sheet Formatted Groups is not so bad.

You will have to handle the merged cells in column A separately.

Column labels

  1. On the sheet Formatted Groups, select cell A4, which contains the word Trip.
  2. Link this cell to A4 on the sheet Specials. [Hint: type  =  , switch to sheet Specials, click in cell A4 and press ENTER.] You are switched back to Formatted Groups.
  3. Right drag A4 on Formatted Groups to the right to F4. From the popup menu select Icon: Excel 97Icon: Excel 2000 Fill Values  or Icon: Excel 2003 Copy Cells .
    The row loses its height.
  4. Resize row 4 by double-clicking the bottom edge of the row heading.
  5. Check each cell in the row. What does the Formula bar show? You should have cell references, like =Specials!F4

Data cells

  1. Message: This operation requires that merged cells be identically sizedSelect range A4:F4 and AutoFill the whole table. [Hint: drag the selection down to row 25.]
    A message appears. You can't do this because of the merged cells. Rats!

  2. Select instead range B4:F4 and AutoFill by dragging to row 25.

    AutoFill copies formatting too!

    Whoops! You did not want the formatting copied!

  3. Undo!
  4. Select range B4:F4.
  5. Icon: Excel 97Icon: Excel 2000 Right drag down to row 25. From the popup menu select  Fill Values .
    Icon: Excel 2002Icon: Excel 2003 Drag down to row 25. From the AutoFill Options, select  Fill without formatting .

    Extra zeros and changed height after AutoFill

    Better! The only changes are the zeros that appear in some blank spaces and row 25 changes height.

  6. Remove the unnecessary zeros from rows 24 and 25 and, if necessary, resize row 25 with AutoFit.

Merged cells

  1. On the sheet Formatted Groups, select cell A5, which contains the word Tahiti.
  2. To link this cell, type  =  , switch to sheet Specials, click in cell A5, and press ENTER. The cells are linked.
  3. Repeat to link cells A11, A16, and A18 on Formatted Groups to the matching cells on sheet Specials.
  4. Inspect all the cells in the top table that aren't empty. Are they all linked now? Only the title cells should be unlinked.

    If you link the title cells, you will lose the separate formatting for the word Specials. We'll leave well enough alone in this case!

  5. Class disk Save as  trips27.xls .
    Full floppy disk How to handle a full disk