October’s the month of magic and while
neither of these solutions is magic, they might seem magical if you’re the user
struggling to accomplish the seemingly impossible!
Excel fill handle magic
Most users discover Excel’s fill handle quickly. It does a
great job of copying formulas and generating lists. It even handles custom
lists. Sometimes the list is more complex than the fill handle can interpret on
its own, but it can handle patterns, if you give it enough information.
And example Excel file is provided as an educational tool for this tip.
David needs just such a fill handle solution; he needs a
date series that skips some days. Specifically, he needs only Monday and
Wednesday of each week. My first solution was to follow the anchor date, a
Monday with formulas that add 2 and 5:
- Enter the first Monday, 9/2/2013,
in the first cell of your date range, A1.
- Enter =A1+2 in A2.
- Enter =A2+5 in A3.
- Select A1:A3 and use the
fill handle to complete the series. (The two formulas in column B are just
Oops! That didn’t work did it? Here’s why: the date in A1 is
a literal date. The fill handle will add one to it to create the new sequential
date, 9/3/2013, in A4. It can only work with the pattern you supply. That’s the
biggest mistakes most users make when trying to create a complex series. You
can see this at work by displaying formulas instead of values. To do so, press
[Ctrl]+` (look for the tilde character, ~, just above the Tab key).
To make this series work as expected, exclude A1 from the
original selection that the fill handle uses. Repeat steps 1 through 3 above.
Then, select A2:A3 and use the fill handle to complete the series. By exposing
the formulas again, you can see that the literal dates that the fill handle
copied before are now formulas and the series works as expected.
There’s an easier sequence than this though:
- Enter 9/2/2013 in C1.
- Enter =C1+2 in C2.
- Enter =C1+7 in C3.
- Select C3 and use the fill
handle to complete the series.
Once you have the first two dates, the single formula, which
adds 7, is all you need because you’re always adding a week, whether it’s to
the previous Monday or the previous Wednesday.
I recommend a pattern that reduces the fill handle selection
to a single cell whenever possible. For that reason, the second series solution
is superior to the first. Once the series is correct, you might want to replace
these formulas with their literal dates by copying the values:
- Select the entire series.
- Right-click the
selection’s right border.
- Drag the selection to the
adjacent column, and then back again (don’t actually drop the values into
the adjacent column).
- When Excel displays the
context menu shown below, select Copy Here As Values Only to replace the
formulas with literal date values.
Thanks to Alice Whalen and Stuart McLachlan for sharing
insight into this particular series solution.
Word property magic
Nino wants to remove content from a page without displacing
the content on the following pages. Of course, we all know that if you delete
content, Word will pull up the following content to fill the empty space you
would otherwise create. Doing so can displace the page break. Sometimes that
won’t matter, but sometimes it will.
Some users will resort to entering blank lines to push
content back to the top of the next page. Some might enter a page break. Both will
work, temporarily at least. Neither is a permanent solution and both have the
potential to mess things up.
The best solution is to tell Word to keep the paragraph at
the top of the current page, regardless of what you do to its preceding page as
- Right-click the paragraph
at the top of the page following the page you want to alter. In Word 2003,
choose Paragraph from the Format menu and skip to #3.
- Choose Paragraph from the
- Click the Line and Pages
- Check the Page Break
- Click OK.
Once you apply this property to the paragraph at the top of
the subsequent page, you can remove or add content to the preceding page
without displaying that page break. This property, if unknown to the user, can
be frustrating. If you have a paragraph at the top of the page that seems
“stuck” – check for this property setting. If set, simply remove it
to allow content to flow freely.