Data Groups & Formulas:
Merges & Formulas

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web
Tidy worksheets are easier to read and use. But, if you get the urge to be neat after you have done a lot of formulas and linking, you may have to do some repairs.

In this lesson you will merge some cells to make Specials easier to read and more logical. The merges affect formulas on the sheet and also cells linked to these cells. It's a cascade of unhappy changes.

Fortunately, you can fix these new problems.


Numbers Home

Project 1: Excel IntroTo subtopics

Project 2: BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics
   
Project 4: Groups & Formulas To subtopics
    SubtotalsTo subtopics
    Images & DrawingsTo subtopics
    Changes & Formulas To subtopics
    FootprintCopies
    FootprintLinks
    FootprintSorted
    FootprintInserted
    Summary
    Quiz
    ExercisesTo subtopics

Project 5: DesignTo subtopics

Search 
Glossary  
Appendix

Icon Step-by-Step

Step-by-Step: Merges & Formulas

  Icon Step-by-Step

What you will learn:

 to repair formulas after merge
to AutoFill repaired formula
to repair sheet with linked cells 

Start with: Class disk trips27.xls

The Tahiti trip cost is $1500. It seems silly to enter that on the Specials sheet for each Tahiti trip. You will merge the cells for Cost each. This will break the formulas that calculate the Total Sale.

Format Cells: Merge

  1. On sheet Specials select cells E5:E10, which all contain 1500, the cost of the Tahiti trip.
     
  2. Right click on the selection and from the popup menu select  Format Cells… 
     
  3. On the Alignment tab, set Horizontal alignment to Center, Vertical alignment to Top, and check the box to Merge cells. Click OK.Message: merging cells keeps contents of upper left cell only
     
  4. Click on OK in the message about losing contents of merged cells.

    All but the first of the cells that calculate Total sale for the Tahiti trips lose their values. Their formulas used Cost each from the merged cells. The Total at the bottom of the column is wrong, but the formula is not broken. It is adding up the column using zeros for the missing numbers.
     

  5. Cost each cells merged - formulas brokenRepeat the procedure for the New Zealand and World trips. (You can not do this for the group of Other trips because those trips do not all have the same value for Cost each.)

    [Select the cells for the trip and format the cells to Center, Top, and Merge.]
     

Formula: Repair with  Absolute Reference

The broken formulas for Total Sale use cells that vanished in the merge. You need to make the formulas refer to the remaining merged cell instead.

  1. Select cell F5, which has a formula that still works.  =D5*E5 

    You need to change E5 in the formula to $E$5, an absolute reference, so you can use AutoFill for the other Total Sale formulas. You could edit it directly, but there is a shortcut.
     

  2. In the Formula bar click next to E5 and press the function key F4, which is probably in the top row on your keyboard (above the number keys). E5 is changed to $E$5 so that the formula reads  =D5*$E$5 
     
  3. Click Button: Green checkmark the green check mark to enter the new formula.

    TipThe F4 key will cycle the cell reference next to the cursor through the 4 possibilities:

    • relative cell reference E5
    • absolute cell reference$E$5
    • mixed reference with column absolute $E5
    • mixed reference with row absolute E$5.

Formula: Repair with AutoFill

Now that F5 has a formula that uses an absolute reference, you can use AutoFill to copy it into the other Total Sale cells for Tahiti trips.

  1. Formulas using Cost each fixedWith cell F5 selected, drag by the fill handle with the left mouse button to cell F10.

    AutoFill enters new formulas. Success! These calculated values for Total sale are the same as before.
     

  2. Repeat the procedure for the New Zealand trips and the World trips.

    [Hint: Select the cell in Column F with a working formula. Edit it to use an absolute reference for the cell in column E. Drag by the fill handle to replace the other formulas for this agent. ]

     

  3. Center range E18:E23 to match the top of the column.
     
  4. Class disk Save as  trips28.xls 
    Full floppy disk How to handle a full disk
     
  5. Print Print the sheet Specials only.

Formula: Repair Linked Cells

Your changes on the sheet Specials also affected those sheets with cells linked to the cells you changed. You need to do some repair work.

  1. Cost each cells show zero.Select the Tahiti sheet.

    Except for the first one, the Cost each cells show a zero since they are linked to merged cells. Data was lost in the merge. Whoops.

    The easy solution is to merge these cells, too.

    The Total sale values are still correct because those cells are linked to the cells you just fixed on the sheet Specials. They are not using cells on the Tahiti sheet at all! The joy of linking!
     

  2. Select cells E5:E10 and right click on the selection. From the popup menu choose  Format Cells… 
     
  3. On the Alignment tab, set Horizontal alignment to Center, Vertical alignment to Center, and check the box to Merge cells.
     
  4. Repeat this procedure on the New Zealand and World sheets.
     
  5. Similarly, merge the Cost each cells on Formatted Groups and center range E18:E23.
     
  6. Group the four sheets - Formatted Groups, Tahiti, New Zealand, World.

    Repairs to linked sheets

    Linked sheets: Cost each cells repaired
     

  7. Open Page Setup and on the Margins tab select Center Horizontally.
     
  8. Check Print Preview for the correct centering, orientation, header, etc.
     
  9. Class disk Save. [trips28.xls]
    Full floppy disk How to handle a full disk
  10. Print Print active sheets to print the 4 active sheets.
     
  11. After printing is finished, ungroup the sheets and close.