Susan Sales Harkins answers Microsoft Office questions received from readers. This month the topics involve Excel and Word.
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.
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 for show.)
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 follows:
- 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 resulting submenu.
- Click the Line and Pages Breaks tab.
- Check the Page Break Before option.
- 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.