Microsoft

September Office tip mailbag: A little magic

Susan Sales Harkins answers Microsoft Office questions received from readers. This month the topics involve Excel and Word.

Mail-envelope.svg.png
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:

  1. Enter the first Monday, 9/2/2013, in the first cell of your date range, A1.
  2. Enter =A1+2 in A2.
  3. Enter =A2+5 in A3.
  4. Select A1:A3 and use the fill handle to complete the series. (The two formulas in column B are just for show.)

2013198.JPG

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

2013199.JPG

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.

2013200.JPG

There's an easier sequence than this though:

  1. Enter 9/2/2013 in C1.
  2. Enter =C1+2 in C2.
  3. Enter =C1+7 in C3.
  4. Select C3 and use the fill handle to complete the series.

2013201.JPG

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:

  1. Select the entire series.
  2. Right-click the selection's right border.
  3. Drag the selection to the adjacent column, and then back again (don't actually drop the values into the adjacent column).
  4. When Excel displays the context menu shown below, select Copy Here As Values Only to replace the formulas with literal date values.

2013202.JPG

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:

  1. 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.
  2. Choose Paragraph from the resulting submenu.
  3. Click the Line and Pages Breaks tab.
  4. Check the Page Break Before option.
  5. Click OK.

2013203.JPG

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.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks