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
Where you are:
Project 1: Excel Intro
Project 2: Excel Basics
Project 3: Format & Arrange
Project 4: Groups & Formulas
Images & Drawings
Changes & Formulas
Project 5: Design
Step-by-Step: Merges & Formulas
What you will learn:
|to repair formulas after merge
to AutoFill repaired formula
to repair sheet with linked cells
trips27.xls (saved in
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
Format Cells: Merge
- On sheet Specials select cells E5:E10, which all contain
1500, the cost of the Tahiti trip.
- Right click on the selection and from the popup menu select
- On the Alignment tab, set Horizontal alignment to Center,
Vertical alignment to Top, and check the box to Merge cells.
- Click on OK in the message about losing contents of
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.
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
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.
Select cell F5, which has a formula that still works.
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.
- 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
E5 is changed to $E$5 so that
the formula reads =D5*$E$5
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.
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.
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. ]
- Center range E18:E23 to match the top of the column.
Save as trips28.xls
How to handle a full disk
Print the sheet Specials
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.
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!
- Select cells E5:E10 and right click on
the selection. From the popup menu choose
- On the Alignment tab, set Horizontal alignment to Center, Vertical alignment to
Center, and check the box to Merge cells.
- Repeat this
procedure on the New Zealand and World sheets.
- Similarly, merge the Cost each cells on Formatted
Groups and center range E18:E23.
- Group the four
sheets - Formatted Groups, Tahiti, New Zealand, World.
Linked sheets: Cost each cells repaired
- Open Page Setup and on the Margins tab select Center Horizontally.
- Check Print Preview
for the correct centering, orientation, header, etc.
How to handle a full disk
Print active sheets to print the 4 active sheets.
- After printing is finished, ungroup the sheets and close.