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.
Step-by-Step: Merges & Formulas
|What you will learn:||to merge cells
to repair formulas with absolute reference
to repair formulas with AutoFill
to repair formulas on sheets with linked cells
Start with: trips26-Lastname-Firstname.xlsx (saved in previous lesson)
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. BUT... this will break the formulas that calculate the Total Sale. So you will have to repair those formulas afterwards.
Click on OK in the message.
Wipe out! A lot of cells lose their values.
Effect 1: The formulas for Total sale use 'Cost each' from the cells that are now merged. Excel thinks that the merged cells are empty, except for the first one. You will fix this shortly.
Effect 2: The Total at the bottom of the column is wrong, but the formula is not
broken. It is adding up the column which now has a lot of zeros because you merged cells.
Repeat the procedure to merge the 'Cost each' cells 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.)
looking good except for the background colors of the merged cells. They are
not alternating like the rest of the table. The merged cells take the formatting of the upper left cell before the merge.
Click in the cell containing 3000 and from the Home ribbon tab, change the Fill Color to White.
That one was easy because it is clear that the background was white. But in other situations Paste Formatting is better because you don't have to know what the colors are.
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.
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.
The F4 key will cycle the cell reference next to the cursor through the 4 possibilities:
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.
AutoFill enters new formulas. These calculated values for Total sale are
the same as before.
But the background color from the first cell is copied down the column also.
[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. Fill without formatting.]
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 on several sheets. Did you think about this effect?
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!
Check Print Preview for the correct centering, orientation, header, borders, etc. Make corrections if needed.
If a sheet does not fit on one page, change the page break in Page
Break Preview. Formatted Groups is probably putting the arrow in column G on a sheet