# Data Groups & Formulas: Merges & Formulas

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

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.

Where you are:
JegsWorks > Lessons > Numbers

Project 2: Excel Basics

Project 5: Design

Search
Glossary

Appendix

## Step-by-Step: Merges & Formulas

 What you will learn: to repair formulas after merge to AutoFill repaired formula to repair sheet with linked cells

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

3. On the Alignment tab, set Horizontal alignment to Center, Vertical alignment to Top, and check the box to Merge cells. Click OK.

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. Repeat 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 the green check mark to enter the new formula.

The 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. With 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. Save as  trips28.xls
How to handle a full disk

5. Print the sheet Specials only.

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

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.

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. Save. [trips28.xls]
How to handle a full disk
10. Print active sheets to print the 4 active sheets.

11. After printing is finished, ungroup the sheets and close.