Category Archives: Ask-An-Instructor

Excel 2010 – Fixing Mail Merge Data – When Dates Show Up as Numbers

“The dates from my mail merge from Excel into Word are showing up as numbers instead of dates – how do I fix this?”

Issue: Recently, we had someone write to us because he was having trouble getting his dates to format correctly in his mail merge document. Some of his dates, derived from an Excel spreadsheet, were appearing as numbers in his Word document:

Mail merge date formatting

Solution:  While we were not able to look directly at the file, I came to the conclusion that the problem did not lie within the Word mail merge document, but with the formatting in the Excel spreadsheet itself. What probably happened was that some of the dates were actually formatted as numbers instead of dates.

Here’s a bit of background to put this into context:

Any date you enter into an Excel spreadsheet actually gets stored behind the scenes as a numbers. This is called the serial date. The number represents how many days have passed between January 0, 1900 – yes, I did say January 0, 1900 – and the date you’ve input. One thing to note – the number 1 represents January 1, 1900 BUT the number 0 does not represent January 31, 1899. So, now that we know all of this, how can we use this information to fix the problem?

Well, the solution is fairly simple. All we have to do is reformat the spreadsheet so that our dates are formatted as such. Here’s how:

1. Clear the formatting from the cells you are working with. To do this, select the cells you are changing. Then, in the Editing group on the Home tab, click on the drop down arrow beside the Clear command (white eraser icon):

Clear button

2. Select Clear Formats. Now, take a look in at what’s happened to your formatting. All of your selected data will now appear as numbers. Further to that, if you look in the Number group on the Home tab, you’ll see that all of your data has now switched to “General” formatting:

General formatting

3. All that’s left to do is format your data as dates. In the Number group on the Home tab, click on the drop down arrow next to where it says “General”. This will give you a list of several formatting options available. If you want to see more options, you can either click on More Number Formats or you can get out of the drop down menu and then click on the Dialogue Box Launcher. That’s the little box in the right-hand corner with an arrow pointing South-East. In this case, however, all you need to do is select either date option (Short Date or Long Date). Unless you want your date to appear a certain way in your Excel spreadsheet, it really doesn’t matter how it looks as long as it’s formatted specifically as a date:
Date reformatted

4. You’re now ready to use in your Word mail merge document! Your dates should now appear as you want them to:

Mail merge final

-Katie Caplan
Client Service Coordinator,
Sector Learning Solutions

Excel 2010 – Sorting Multiple Worksheets Alphabetically

“In Excel 2010, is there a way to sort multiple worksheets all at once within one workbook without running a macro?”

Issue: An Excel spreadsheet with 25 Excel worksheets that needed to be sorted alphabetically. The client was using a macro, but thought there ought to be an easier way.

Solution:  According to Sector instructor, Margo Almond, “running a macro is the simplest, safest way to sort multiple worksheets alphabetically.” Alternative workarounds do exist, but are more complicated and run the risk of messing up your data.

What is a Macro?
Macros record a sequence of actions (such as mouse clicks and keystrokes) and play those actions back in the exact same order. Macros are created to automate a series of steps requierd to perform a certain task – reducing multiple steps into one – saving you time and the boredom of repetion.

excel-2010-macro

Created with content provided by Katie Caplan, Client Service Coordinator, and Margo Almond, Instructor.

-Kelly Marshall,
Director of Communications,
Sector Learning Solutions

Excel 2010: Consistent Case Formatting

“In Excel, is there an easy way to change text with different case formatting to all have consistent case formatting – such as all upper case, lower case or have the first letter in each word capitalized?”

aai-excel-case

Issue: An Excel spreadsheet containing a list of registrant names that had been entered inconsistently – some were all lower case, others were all upper case and some were a mix. The client was not looking forward to having to correct each of these manually. A time-saving solution to the tedious task was required.

Solution: Excel can actually make this quick, easy and (relatively) painless. The program comes with built-in functions to change the case to UPPER, LOWER, or PROPER. “PROPER” case has the first letter in each word capitalized.

Step-by-Step Instructions: The following step-by-step solutions show how to use this function to convert data to the “PROPER” case.

1. Once you have the names in your spreadsheet, insert a column after the registrant column. To do this, simply right click on the column to the RIGHT of where you plan on inserting the new column and select Insert. In the example below, I need to insert a column between Registrant Name and Phone, so I will need to right click on my Phone column.

Note: If the registrant name column is divided into two columns (i.e. first and last name), you will need to insert two columns: one in between first and last name and then one after last name.

aai-excel-case01

2. In the new column, select the cell to the right of  the first name you wish to change and enter the formula =PROPER(cell reference).

In my example, the first name I wish to change is Jane Smith. The cell reference for her name is A2. My formula, therefore, will be “=PROPER(A2)” and it will be entered in cell B2.

aai-excel-case02

3. Copy that formula down.

To copy the formula down, drag the small black square in the lower right-hand corner, double-click it, or, my personal favourite, hold down the SHIFT key while pressing the down arrow to select all the cells that I want my formula to be entered in. Then press CTRL and D.

Note: If your data is in a table, the data will automatically populate in the cells you require after entering it.

aai-excel-case03

4. Copy the highlighted cells: Press CTRL and C or right click and select Copy. Then, in cell A2, right click, select Paste and then select Values.

aai-excel-case04

Alternatively, you can click the dropdown arrow under Paste in the Clipboard group (Home tab) and then select Paste Values. This will allow you to only copy the names and not the underlying formulas. If you try to copy the formulas, you will get an invalid cell reference error; remember, your original formula referenced cell A2 which you are now trying to paste into.  Then, remove column B by right clicking and selecting Delete.

aai-excel-case05
5. The following will be the final result:

aai-excel-case06

If you need to, you can interchange the PROPER function with LOWER or UPPER; all of the above steps will remain the same.

-Katie Caplan,
Client Service Coordinator,
Sector Learning Solutions

Word 2010: More Mail Merge Tips – A Follow-Up

Some of you may recall our May 2012 blog post: Word 2010 Mail Merge: Changing the Date Format about changing the date format of a mail merge field in Microsoft Word. This sparked a number of positive responses from our readers and raised more questions about mail merge. I thought I’d address a couple of these questions today.

Question #1

“If I want the day of the week included in the date – how do I do that?”

-Holly

Answer

This is a great question with a very simple solution – all you have to do is adjust the date format to include the day of the week. The date format can be adjusted to whatever you’d like it to be.

In the example I gave, the format was “MMMM d, yyyy”; as you might expect, the date would end up looking like “January 3, 2013”. If I wanted that to include the day of the week to look like “Thursday, January 3, 2013”, I would simply adjust the format in the merge field to “dddd, MMMM, d, yyyy”. With the field coding visible (see original blog post for instructions on how to do this), your merge field should appear as follows:

«Date»

Your date format should change from this:

mail-merge-january-18-2013

To this:

mail-merge-friday-january-18-2013

Thanks for the question, Holly!

Question #2

David Rubin of BMO Nesbitt Burns Financial Services Inc. came up with another excellent question:

“What would be the entries for $000,000 and 0.00%? I can get the date to work, but am having trouble with the numeric [formatting].”

In other words, how do I set up a mail merge in Word to work with Excel data that has numeric formatting such as dollar amounts or percentages?

Answer

There are a few ways to accomplish this.

One way is to use the Dynamic Data Exchange (DDE). It is nowhere near as complicated as it may sound. After testing this out, I’ve found that this works best overall, particularly if you’re merging from a spreadsheet that already exists and contains a lot of numbers. I’ve outlined the steps for this approach below*:

*Please note:

  • These steps are for Word 2007/2010. If you are using an earlier version, please send us an email; we will be happy to send you the appropriate instructions for your version.
  • These steps are written under the assumption that you already know how mail merges works and how to perform one. If you need assistance with this, please let us know and someone can walk you through the process.

1. If it already exists, open your mail merge document. Otherwise, you can simply open a blank document.

2. Go into the Options dialogue box. If using 2007, this will be behind the Office button. In 2010, it’s under the File tab.

3. On the Advanced tab, scroll down to the General section (almost at the bottom):

mail-merge-screen

4. Check the box next to “Confirm file format conversion on open” and then click OK.

5. Begin your mail merge; you can do this through the Mail Merge wizard, but I prefer to skip that and go straight to Select Recipients. Whichever way you choose to get there, once you’ve selected your data source you will see the Confirm Data Source dialogue box.

6. Check the “Show all” box and then select “MS Excel Worksheets via DDE” and click OK:

mail-merge-confirm-data-source

7. In the Microsoft Excel dialogue box, under Named or cell range, select the data range or worksheet that contains that data you wish to use:

mail-merge-entire-spreadsheet

8. Now preview your results – all of your numeric values should appear as they were formatted in Excel!

mail-merge-end-screen

Another method is to work with the field codes directly in Word; this is what we did when we changed the date format. This really only works well when the Excel worksheet from which you’re merging only has one or two number columns. The third way, and probably least effective, is to format the Excel worksheet cells as text. This really only works well if you’re creating a new worksheet directly from the mail merge and if you’re not performing any calculations in the worksheet.

Thank you, David, for another great question!

We’d love to hear from you! If you have any tips or tricks related to any Microsoft Office program, please feel free to send us an email.

-Katie Caplan,
Client Service Coordinator,
Sector Learning Solutions

Word 2010 Mail Merge Using Excel 2010 Data with Numeric Formatting

David Rubin of BMO Nesbitt Burns Financial Services Inc. came up with another excellent question:

Question:

“What would be the entries for $000,000 and 0.00%? I can get the date to work, but am having trouble with the numeric [formatting].”

In other words, how do I set up a mail merge in Word to work with Excel data that has numeric formatting such as dollar amounts or percentages?

Answer:

There are a few ways to accomplish this.

One way is to use the Dynamic Data Exchange (DDE). It is nowhere near as complicated as it may sound. After testing this out, I’ve found that this works best overall, particularly if you’re merging from a spreadsheet that already exists and contains a lot of numbers. I’ve outlined the steps for this approach below*:

*Please note:

  • These steps are for Word 2007/2010. If you are using an earlier version, please send us an email; we will be happy to send you the appropriate instructions for your version.
  • These steps are written under the assumption that you already know how mail merges works and how to perform one. If you need assistance with this, please let us know and someone can walk you through the process.

1. If it already exists, open your mail merge document. Otherwise, you can simply open a blank document.

2. Go into the Options dialogue box. If using 2007, this will be behind the Office button. In 2010, it’s under the File tab.

3. On the Advanced tab, scroll down to the General section (almost at the bottom):

mail-merge-screen

4. Check the box next to “Confirm file format conversion on open” and then click OK.

5. Begin your mail merge; you can do this through the Mail Merge wizard, but I prefer to skip that and go straight to Select Recipients. Whichever way you choose to get there, once you’ve selected your data source you will see the Confirm Data Source dialogue box.

6. Check the “Show all” box and then select “MS Excel Worksheets via DDE” and click OK:

mail-merge-confirm-data-source

7. In the Microsoft Excel dialogue box, under Named or cell range, select the data range or worksheet that contains that data you wish to use:

mail-merge-entire-spreadsheet

8. Now preview your results – all of your numeric values should appear as they were formatted in Excel!

mail-merge-end-screen

Another method is to work with the field codes directly in Word; this is what we did when we changed the date format. This really only works well when the Excel worksheet from which you’re merging only has one or two number columns. The third way, and probably least effective, is to format the Excel worksheet cells as text. This really only works well if you’re creating a new worksheet directly from the mail merge and if you’re not performing any calculations in the worksheet.

Thank you, David, for another great question!

-Katie Caplan,
Client Service Coordinator,
Sector Learning Solutions

Word 2010 Mail Merge: Add Day of the Week to Date Format

Some of you may recall our May 2012 blog post: Word 2010 Mail Merge: Changing the Date Format about changing the date format of a mail merge field in Microsoft Word. This sparked a number of positive responses from our readers and raised more questions about mail merge. I thought I’d address a couple of these questions today.

Question:

“If I want the day of the week included in the date – how do I do that?” -Holly

Answer:

This is a great question with a very simple solution – all you have to do is adjust the date format to include the day of the week. The date format can be adjusted to whatever you’d like it to be.

In the example I gave, the format was “MMMM d, yyyy”; as you might expect, the date would end up looking like “January 3, 2013”. If I wanted that to include the day of the week to look like “Thursday, January 3, 2013”, I would simply adjust the format in the merge field to “dddd, MMMM, d, yyyy”. With the field coding visible (see original blog post for instructions on how to do this), your merge field should appear as follows:

«Date»

Your date format should change from this:

mail-merge-january-18-2013

To this:

mail-merge-friday-january-18-2013

Thanks for the question, Holly!

-Katie Caplan,
Client Service Coordinator,
Sector Learning Solutions

How to Find and Delete the Same Data from Multiple Cells in Excel 2010

Have you ever needed to delete all instances of a word or phrase in Microsoft Excel without replacing that word/phrase with something else?

Recently, one of our past students used our Ask-an-Instructor Forum to ask us how to go about doing this in Excel 2010. This client maintains a work schedule for a number of staff in her office. The basic schedule covers the entire span of a year (January to December), with changes being made here and there throughout.  One of the staff members moved on into a different role and so our student no longer needed to schedule this particular person. She was looking for a way to easily remove this staff member using Find and Replace.

While it is not exactly made very obvious, in the end the solution is very simple:

Excel Vocabulary: “Data” entered into Excel can be text data (a “text string” such as a word or a phrase) or number data (a number).

Data is stored in a “cell” (the rectangular box that is the intersection point of a column and a row).

When filtering data,  the term “value” is used to describe the condition that data must meet in order to either remain in the data table or be filtered out.

1. Click on the Home Tab.

2. Locate the “Find and Select” button in the Editing group (far right).
Click on the button to activate the drop-down menu. Select “Find” (binoculars icon):


3. The Find and Replace dialogue box opens. Make sure the Find tab is selected. Type in the word or phrase you would like to delete in the “Find what:”  field:

 4. If the arrows on the Options button are pointing to the left (<<), skip ahead to step 5. If the arrows on the Options button are pointing to the right (>>), click the Options button.

5. Click Find All. Depending on how many instances of the data value there are in your spreadsheet, you may not be able to see them all immediately. To solve this, look at the bottom right corner of the dialogue box. Move your mouse over the corner until it turns into a two-headed arrow on a diagonal that looks like this:

Click and drag to expand the box until you can see all instances (cells) found:

6. Select all of the cells listed. You can do this by selecting one of the cells and then pressing Ctrl + A. Alternatively, you can click the first cell in the list, press Shift, and then click the last cell in the list:

7. Close the Find and Replace dialogue box. You should now have every cell populated with the data value you want to delete selected:

8. Press Delete. Voila! You have now successfully deleted all instances of the data value you wanted to remove.

One thing to keep in mind is that although all the steps listed above are for Microsoft Excel 2010, they also apply to Microsoft Excel 2007.

Don’t forget! If you have taken any classes with us in the past, take advantage of your unlimited access to our Ask-An-Instructor Forum. We’re all here to help! We will do our best to address your question within 2-3 business days.

Katie Caplan,
Client Service Coordinator,
Excel 2007 or 2010 Training @
Sector Learning Solutions