Microsoft optimize

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.

8 comments
glnz
glnz

Mark:  Susan's columns are very helpful.  But no one can overcome the awful, horrible underlying design of Word.  Styles and Section Breaks are the epitome of bad engineering - they should never have been incorporated into Word.  Absolutely nobody gets them right.  And as documents are emailed and revised by other people in other companies with other templates and other limited understandings of these complexities, the problems multiply.   (Every version of Word I have seen in 11 years is worse than WordPerfect in 1996.)

If Word were an automobile, thousands would have been injured in accidents and Microsoft would have been bankrupted by the liability.

Ok - more productively - HOW DOES ONE DELETE AN EXISTING SECTION BREAK WITHOUT SCREWING UP EVERYTHING IN THE PRECEDING AND FOLLOWING FOOTERS AND (SOMETIMES) SETUPS?  After 11 years of wrestling with Word, I still haven't figured out a way to do this and, after deleting a section break, always have to re-do the correct headers and footers in the surviving sections before and after.

Thanks.

chip_long
chip_long

I don't like the solution Susan's for keeping the paragraph at the top of the next page. With the use of templates and styles in the later versions of Word, I find changing things in the settings box can have unintended consequences for me. I find it much easier to just use a [ctrl]+[enter] to enter a forced page break.

rjdbnet
rjdbnet

When you select "Show/Hide ¶", Word 2003 displays a little black square in the margin to the left of a paragraph with "Page break before" set.  You have to unset "Page break before" to remove the setting; you cannot plant the cursor after the black square, and delete it.

Thanks for excellent, helpful articles!

Mark W. Kaelin
Mark W. Kaelin moderator

Do you have a particularly vexing problem with Microsoft Office? Are you having a difficult time figuring out how to accomplish a task? Perhaps Susan can solve it for you?


bkfriesen
bkfriesen

I still compare Word to WordPerfect 5.1. It was efficient, fast, and easy to understand and use.

The 'Microsoft Intelligence' features built into the current Word OFTEN are more of a hindrance than a help. I will say that when it does what you want it to, the conveniences built into the current Word are brilliant. (When it doesn't, however, there are few things more frustrating.)

ssharkins
ssharkins

@chip_long You should use the solution that works best for you. What users forget is that the hard break can cause problems down the road and sometimes those issues are hard to troubleshoot and trace back to a hard return. Nothing wrong with hard returns -- they're allowed! ;)