Spring Forward, Fall Back—Time to Check Your Backup

Twice a year, we change our clocks. Safety experts tell us that these two occasions are also ideal times to check the batteries in our smoke detectors. Well, there is one more thing we need to check on a semiannual basis: our data backup systems.

Yes, I know. Computer backup systems are about as exciting as insurance policies. We set them up and then just assume they continue to work. Then one day, when we need them the most, we find out that they stopped working two years ago!

So, let’s take a quick look at your backup system. The exact procedure varies depending on which of the many backup programs you are using. But the general process is the same and it’s very simple:

How to Check Your Backup System

  1. Think of a file that you updated a few days ago.
  2. Open your backup program.
  3. Start the Restore process.
  4. Browse to the desired file.
  5. Restore it to a test folder on your desktop.
  6. Open the file to be sure it is intact.
  7. Then delete the file in the test folder.

That’s it. If everything works fine, you’re all set for the next 6 months. If not, well, you need to find out what’s wrong with your backup system and correct it as soon as possible.

By the way, if you are among the 75 percent of computer users who don’t back up their data at all, consider this a wakeup call. You are skating on thin ice, and it is only a matter of time before you lose irreplaceable photographs and other files. Start setting up a backup system right away.

 

Passwords Don’t Make Sense …

What Shakespeare Could Tell Us About Good Passwords

shakespeare-fr-wikipedia-250pxYes, passwords don’t make sense, or more accurately, they shouldn’t. What I mean is, if you have a password that is easy to remember, it is probably no good. If your password is a simple word or name, you might as well publish it on Facebook. Even if the password is combination of words, or maybe even has a number or two, you and your data are skating on thin ice.

Hackers are cobbling together super-powerful PCs from parts such as old video-game consoles. What do they use them for? Play games? Predict the weather? No. They are used to guess people’s passwords, at the rate of gazillions a minute. And they are getting faster and better every day.

Actually, in a sense they already probably know your password. Thanks to companies with lax security, millions of in-use passwords have been stolen and are freely available on the Internet. Many of our favorite passwords are no doubt in this collection. If your password is your child’s name followed by the year she was born, they probably already have that. If you cleverly switch the “a” with the symbol “@”, they probably have that as well. We are seeing, as security expert Steve Gibson termed it, “The Death of Clever.”

The best passwords are long strings of completely random characters, upper case, lower case, numerals, and symbols. And, we should have a different password for each of our accounts. But who can handle that? Well, there is a nice way of doing that – a password manager. I will mention more about that in another post. But right now, I’ll describe a handy way of creating pseudo-random passwords that are memorable.

Take one of your handy books, songs or poems. Look at the second line (the first may be too obvious). Take the first letter of each word. Capitalize the words that are already capitalized and any long word. Be sure to include punctuation marks. After the punctuation, include a numeral (say, the length of the word preceding the punctuation). Stop when you have at least 12 characters. (Experts now recommend at least 14 characters.)

So, for example, here is the second sentence from Shakespeare’s Sonnet 116:

Love is not love
Which alters when it alteration finds,
Or bends with the remover to remove.

The password might be:

LinlWawiAf,5

Now, don’t use this technique exactly as I described it. Customize it in your own way. Just be sure to remember how you did it.

 

3 Ways to Fix Mail Merge Number Formatting in Microsoft Word

[Originally posted June 2012. Updated May 2017.]

Sidebar for Mail Merge postThe Mail Merge feature of Microsoft Word is one of my favorite parts of the program. It is extremely powerful for creating labels and customized letters, emails, or reports. Once you learn how to use it, you can save countless hours of work. Unfortunately, the task of learning to master all of its idiosyncrasies can give you countless headaches.

One of the perennial annoyances of Mail Merge is its inability to format numbers from an Excel spreadsheet correctly. For example, a sales result in Excel appears as 100 but in Word it suddenly becomes 99.99999999996!

Fortunately, there is a way to fix this. In fact, there are three ways. We can modify the spreadsheet, modify the Mail-Merge document, or simply modify the way the two files connect to each other. Although this last approach is little known and no longer works for Word 2016, for many users it is certainly the best.

[Note for users of various versions of Microsoft Office: The steps presented here are for Office 2013, but the steps needed for older and newer versions of Word and Excel are very similar if not identical.]

1. Modify the Excel Spreadsheet

The first way is to avoid the problem: In the Excel worksheet, insert a column with a formula that converts the Excel numbers or dates into a text format that is exactly the same as what you would want to appear in your Mail Merge document.

To do this, you need to use Excel’s TEXT() function, which enables you to convert a number or date into its equivalent text formatted exactly the way you desire. The downside is that you need to know certain formatting codes. Although these codes are identical to those used in the Custom Number formatting feature of Excel, they are rarely seen by most Excel users.

For example, the formulas TEXT(B3, “$#,##0.00”) and TEXT(C5, “M/dd/yy”) convert number and date data into text data (in this case, “$12,345.67” and “12/01/10”).

(Tip: While you are in Excel, format the cells containing these formulas in a different style, say, italic, or some unusual color to remind you that these numbers are simply text. In older versions of Excel, these “numbers” cannot be used in calculations. But, in Excel 2016, they can.)

The advantage of formatting numbers and dates as text is that text is transported from Excel into a Word Mail Merge document unmolested. Well, almost unmolested. Formatting options such as font, size, and color do not make the trip.

Format Codes for Excel’s TEXT Function

Table of Excel TEXT function format codesA collection of the most commonly used codes is presented at the right. A key thing to remember is that “0” signifies a digit that will always appear even if zero, while “#” specifies a digit that will appear only if it is not a leading or trailing zero. Thus, the code “00000” will ensure that the leading zero is not truncated from New Jersey postal zip codes.

These codes for numbers, dates, and times can be used in the TEXT function or in Excel’s Custom number formats. As an extra bonus, they can also be used in the “Numeric Switches” in Word Mail-Merge Fields described in the next section.

Unfortunately, there is a slight difference between the way these codes work in Excel and Word in Microsoft Office 2013. In Excel, you can use either “M” or “m” for months or minutes. In almost all cases, Excel is smart enough to figure out which units you are talking about. But Word is not so smart, and you must use the capitalized letter to refer to months. Also, Excel has an additional code, “MMMMM”, which returns a single letter abbreviation for the month (e.g., “J, F, M, A, …”).

Supplementing a spreadsheet table with a few TEXT() functions is a simple and direct approach. If you are using more than one or two these functions, however, your spreadsheet soon becomes cluttered with seemingly redundant columns. Analyzing the spreadsheet becomes impossible, and there is great likelihood that you will mistakenly use a TEXT() function as input for a calculation, thereby yielding nonsense results. Our advice here is to put all your TEXT() functions on another worksheet, entitled something like “Data for Mail Merge.”

Handle with Care: The ROUND Function

Instead of the TEXT() function, some users prefer to use the ROUND() function to trim off extra decimal places. As its name implies, Excel’s ROUND() function will permanently round a number up or down to the number of decimal places you specify. The advantage is that the rounded number is not text and can still be used for further calculations. In many cases, the ROUND() function will work well with Mail Merge, but you may want to steer clear of it because of the following reasons:

  1. The ROUND function will not preserve the dollar sign or the thousands separator (comma)
  2. The ROUND function will truncate trailing zeros
  3. The ROUND function sometimes causes Mail Merge to display the wrong number of decimal places. For example, a Mail Merge document occasionally shows 4 decimal places when the ROUND function had specified 2 or 3.

In rare cases, the ROUND function causes Mail Merge to show a slightly different number. For example, instead of displaying 1.0014, Mail Merge showed 1.0013.

Okay. But let’s say that you don’t want to change your Excel spreadsheet. Well, there is something we can do in Word:

2. Use a “Numeric Switch” in Word

The second way to cajole Mail Merge into displaying numbers from Excel correctly is to modify the Word document. Specifically, this means applying the desired number format code to the Merge Fields in the Word document. (The merge fields basically tell the Word document which column in the Excel table has the desired data.) To apply a format to a field, you must include a numeric switch (formerly called a picture switch) in the field’s field code.

The first thing you have to do is to see the actual field code. Open the Mail Merge document and click the Mailings tab at the top of the window. Be sure the Preview Results button is toggled off so that you can see the Mail-Merge fields. Then right-click a Mail-Merge field (such as «Donation») and choose the Toggle Field Code option. You should now see the actual field code for that field, which is designated by curly braces as in { MERGEFIELD Donation }. Now edit the field code by simply inserting a numeric switch code to the end of the field code, as in

{ MERGEFIELD Donation \# $#,##0.00 }

There are many picture codes available. Here are four examples with their respective results:

{ MERGEFIELD Cost \# 00.00 }              01.00
{ MERGEFIELD Sales \# $### }               $ 15
{ MERGEFIELD Sales \# $#,##0.00 }    $ 1,500.00
{ MERGEFIELD Date \@ "MMMM d" }     November 26

As you can see, the numeric switch codes are identical to the Excel formatting codes except that they are preceded by “\#” for numbers and “\@” for dates. To see more of the codes available, refer to the table above, or see the online help for “numeric switch” in Microsoft Word.

Here are some examples of how the numeric field codes work with data in a column labeled Sales in an Excel spreadsheet.

Numeric Switches for Mail-Merge MergeFields

Table of Microsoft Word Mail Merge Numeric Switches
In light of the fact that Word and Excel use the same formatting codes, we could not help wondering why they don’t use the same function format. For example, why not have the MergeField function look something like:

{ MERGEFIELD(Donation,"$#,##0.00") }

Good question. You’ll have to ask Microsoft.

Note: Before you start cursing me out, try to remember that when you add or change a numeric switch, the effect is not shown immediately. You either have to update the field (right-click it and choose Update Field), or click the button Mailings > Preview Results 2 or 3 times, or if you are using the Mail-Merge Wizard, you will have to go back a step and return to see the effect of your changes. (I am not making this up. Remember, only geniuses work at Microsoft. We are just not smart enough to appreciate their brilliance.)

The numeric switches in merge fields work well, but I find the process very difficult to remember. (“Is it a forward slash or a back slash?”) Also, it is very easy to make a mistake. Fortunately, there is another way:

3. Use a DDE Link

The above approaches are relatively simple, but if you have more than a few fields that require formatting, they can drive you into early retirement. At the very least, they require you to remember format codes that, while similar, are used in very different ways.

A much more elegant and simple solution is to have Word link to the Excel workbook via a DDE (Dynamic Data Exchange) link rather than the usual, presumably non-dynamic, linking process. That sounds a little daunting, but if you are smart enough to do Mail Merge, then DDE is a piece of cake. It is a simple two-step process, and the first step — enabling Word to open a file via DDE — has to be done only once.

[Note for Excel/Word 2016 users: Microsoft appears to have pulled the plug on this relatively old DDE technology. Unfortunately, you will have to use one of the other two techniques.]

To set up Word 2013 for DDE links, do the following:

  • Click: File > Options.
  • Click the Advanced tab on the left and scroll down to the section General.
  • Check the box labeled Confirm file format conversion on open.

That’s all for the first step, and you never have to do it again. From now on, your copy of Word can open up many different types of files, and can open these by different avenues, including DDE. Microsoft Word Mail-Merge Confirm Data Source dialog box 1The only side effect of the above is that every time you open a non-Word file with Word, the program will give you a chance to change your mind. No problem.

The second and last step has to be done each time you select a data source for your Mail Merge operation (either in Step 3 of Word’s Mail Merge Wizard or after you press the Select Recipients button in the Mailings ribbon). Relax. It is just three additional mouse clicks:

  • After you have chosen the data file you would like to use, a new “Confirm Data Source” dialog box will appear.
  • The default type of link is by OLE, but that is not what you want.
  • Microsoft Word Mail Merge Confirm Data Source dialog box 2In the Confirm Data Source dialog box, click the check box to Show all.
  • In the expanded list of file types, choose MS Excel Worksheets via DDE (*.xls). (Choose this even if you are using the newer Excel file format: *.xlsx.)
  • If asked, confirm that you are selecting the Entire Spreadsheet.

If you have already selected a spreadsheet for your Word document, you may have to select it again, this time via a DDE link. That’s it! From now on, your Excel formatting will travel over to Word Mail Merge documents fairly intact. One huge caveat here: Make sure that the data you want to merge are in the first sheet of your Excel workbook. (It took us two hours to finally figure out that DDE does not see anything but the first Excel worksheet!) Sounds good. So, you ask, if this is so good, why didn’t the geniuses at Microsoft simply do this by default?

Another good question.

In previous versions of Word, there were a few minor downsides to this approach. But in my tests with Word 2013, these problems seem to have been fixed. But for how long? It is perhaps significant that the DDE link option refers only to the old “*.xls” file format rather than the new one used by more recent versions of Excel. Sure enough, Microsoft did deactivate DDE in the 2016 versions of Word and Excel. Let’s hope they come up with nice replacement.


If you had any questions or problems with the above, please let me know. Click here to post an anonymous comment.