to www.officetipsandmethods.com

A little over a year ago I started two blogs, Argee's Office Help Blog and Argee's Access Blog. As the titles suggest, the first focuses on entire Microsoft Office applications except for Microsoft Access. The second is aimed strictly at Microsoft Access related topics. I found a number of free blog hosts and started my first OfficeHelp, a Windows Live Site. After publishing a few articles there, I wanted to be able to separate Access oriented articles from those related to the rest of the Office suite. Windows Live blogs are great for personal/social blogs but I couldn’t find a way to classify or categorize articles with more that a single category/tag for each article.

Visiting a few blogs by my Access colleagues led me to consider the Blogger site which is hosted by Google. I decided to locate my Access site there. Each article can have multiple tags. That makes it easier to specifically classify articles so that a reader can find specific topics more quickly.

Recently, I have come to realize that I really wanted an even more sophisticated classification system than what is possible on the Blogger site. Part of this need stems from the significant change that Microsoft introduced into user interfaces with Office 2010. These changes were particularly significant in Access, my main area of interest. A little Internet searching led me to the WordPress publishing platform.

Will WordPress solve my classification problem? It certainly seems so, and then some. I found that I can set up custom categories and sub-categories. That means that I can flag an article as relating to Access generally, to Access 2007, specifically, and that the article is part of a tutorial. I can also tag each article with one or more tags that will aid in subject oriented searching. Best of all, I found I can include a category search capability in a side bar that is always there at the top of the blog page.

WordPress may not be the last word in meeting my blogging needs but so far I like the look and feel. Now it’s time to get back to writing. For the time being I will mirror articles that I publish at OfficeTipsAndMethods on my OfficeHelp and Access blogs so if you have subscribed to one or both of those feeds you will still be notified of new articles in those blogs. But if you want a “one feed serves all” feed, visit

www.officetipsandmethods.com

and subscribe to the rss feed there.

Presentation design experts suggest that simplicity is a must if a PowerPoint presentation is to be effective. The risk when you ignore presentation style guidelines is that your message will be lost in a sea of visual changes. PowerPoint uses design masters to control slide layouts and font sizes, colours, and typefaces.

You may have a presentation (or perhaps several) where the person creating the presentation has decided to apply individual font settings to some of the slides (perhaps most of them.) Now you want to reset all text back to the standard settings of a presentation template.

Here is a particularly bad example of the problem:

Notice how the presentation has a mixture of font sizes and colours as you go from slide to slide. Some of the text is almost unreadable and the size of title text changes from slide to slide. It wouldn’t take an audience long to write off the presentation as extremely amateurish and to question whether anything the presenter had anything worthwhile to say.

Fixing the background is fairly easily handled by simply applying a design template to the presentation and resetting slide  backgrounds to automatic for any that have a customized background. When you select Slide Design from the Format menu, the task pane will open on the right side of the screen, displaying the available design  templates.

If you move your mouse over one of the design thumbnails, a dropdown arrow will appear on the right side of the thumbnail. Click the apply to All Slides option. In the example presentation, some slides have custom backgrounds.

At this stage, only slides that do not have custom backgrounds will be have their backgrounds reset to match that of the design master you selected. At this point you should also apply the design template to the presentation master so that PowerPoint will automatically apply it to any slides you add to the presentation.

Reset slide backgrounds by selecting all slides and then right-clicking one of them. Select “Automatic” from the Background dialogue and click Apply to All.

 

	Design Template Applied		Automatic Background Applied

So far so good but the fonts in the presentation are still quite inconsistent. Fixing that problem requires switching the Task Pane to Slide Layout. You can do that by clicking on the title bar of the Task Pane or selecting Slide Layout from the Format menu. The is a two step process. First you apply the layout to all selected slides. Then, with all slides still selected, you click “Reapply Layout.”

Here are before and after views of one of the slides from the sample presentation:

	Slide With Custom Background Colour and Formatted Text		Slide With Theme and Slide Layout Re-Applied

Note: Resetting a presentation’s appearance in this way will affect only placeholder text fonts. If the presentation includes additional textboxes, you will need to deal with these individually. You may also need to set individual slide layouts for some slides. I have applied the Title and Text layout to make the general change to all slides. To correct the first slide of the presentation, I had to select it and then apply and re-apply the Title and 2-Column Text layout.

Thanks to Noreen for raising the question and to Cynthia, Katherine, and Echo for helping with the solution.

Wondering what’s coming in the next release of Office? Check out the Office Team Blogs. Start with the Microsoft Office 2010 Engineering blog to get an idea of features the various Office applications share in common and how they will look in 2010. One particularly interesting new development is the Backstage. This article tells you what has happened to file operations and other features found on the Office Button Menu in ‘07 and on the File Menu in ‘97 – ‘03. The article not only illustrates the Backstage but also explains the rationale that led the development team to this approach.

While you are on the Engineering blog site, look for team blog links for each of the Office Application team on the right side of your screen.

I recently came across a link to Peter Noneley's site . Peter has developed an excellent reference tool for serious Excel users. He calls it Excel Function Dictionary and it is available as a free download from his site. Here is a peak inside and a brief overview of the tool. When you open the workbook, the first thing you see is a brief menu with three choices, Function Dictionary, Function Examples, and Function Categories.

Function Dictionary

The choices are hyperlinks that lead to more and more detail. For example, if you click Function Dictionary, you will see a list of the more than 150 functions that Peter has chose to provide help on. Each item in the Function Dictionary list is itself a hyperlink to a detailed explanation and example of the function in use.

Peter has done a great job of maintaining a consistent ‘look and feel’ throughout the help topics.

 

 

 

Function Examples

Peter has thoughtfully included working examples of a number of functions. Clicking the link in the Reference column of the menu causes a list of examples to display in the Examples column. Each example is thoroughly explained

Function Categories

If you click function categories you will see the standard Excel list of function categories

 

 

 

 

 

Click one of the category links to see a display of names of the functions that belong in that category. Once again each of the names is a link, this time leading to a detailed explanation of how to use the function.

In the last graphic you will see an example of Peter’s explanations. The tool is available free for download form Peter’s site: http://www.xlfdic.com/

 

 

 

 

 

Excel has so many features that it is more difficult to know exactly what you don’t know vs. what you do know in depth. You may be a long time Excel user who has used many advanced features and consider your self to be an Excel guru. If you’d like to measure just how extensively you really know Excel, check out this article in MVP Nate Oliver’s blog. You may be surprised.

Standard postal mail has all but been eliminated from my day to day 'vocabulary.' Email serves me quite well for virtually all of my communication needs.

Occasionally however I have to submit documentation, such as medical insurance claims, by regular 'snail' mail. Typically, this kind of mail results in money being returned to me. It is critical the the address be readable so that the letter gets to the correct destination with a minimum of fuss.

Word has an envelope feature on the Mailings 2007. However, the only option there seems to be to add the envelope to the main document. Since I just need the addressed envelope, this isn't an ideal solution because I have to specify just to print the envelope and not the blank main document.

Here's the solution I came up with:

First create a new blank document.

On the Page Layout tab click Margins and choose Custom Margins at the bottom of the menu:

On the Paper tab select the paper size for the envelope you will be using. If you don't see your envelope in the list of available sizes, you can choose custom size and enter the dimensions of the envelope you want to use.

Click OK to close the Page Setup dialogue. Then, on the Page Setup tab, set the orientation to Landscape.

Select the Insert tab and insert two text boxes on the document. Use one of the corner sizing handles to resize the text boxes. The one at the upper left should be large enough to accomodate your return address.

Make the other text box large enough to accomodate the destination addresses you expect to be using.

Enter your return address in the upper left text box. If you expect only need the envelope from time to time only for one particular address, then enter that address in the text box in the middle of the envelope.

You will also want to take time to format the font size for each text box and also to make the margins transparent.

You can do that by right clicking on the text box and choosing Format Text Box ... from the menu. Be sure to point your mouse exactly on the border around the text box before clicking your right mouse button. Otherwise you won't see the Format Text Box option.

 

 

On the Colors and Lines tab of the Format Text Box dialogue, click the Line Color: dropdown and select No Color. That will make the border invisible.

 

Your finished product will look something like the envelope below

 

 

 

 

 

 

 

 

 

 

 

 

 

Use the Print dialogue from the office menu to select the printer and paper tray for the envelope. Specific instructions depend on what printer you choose to do the printing.

Saving the Envelope for Future Mailings.

Of course, this would be a lot of work to go to if you had to set up the envelope each time you needed one. However, if you save the document then printing the envelope each time you need one, then all you will have to do is re-load your envelope document and print.

When it comes to saving the envelope, you have two choices. If you will always be mailing to the same address then you can save the envelope as a standard Word document.

However, if you expect to be periodically mailing to several different addresses, you might find it worthwhile to save the envelope as a template. In that case, don't type in the destination address when you create the envelope. Just leave that text box empty. That way, when you create a new document from your template, you can type in the destination address you need at the time.

 

 

 

Michele has details for a number of contacts in an Excel worksheet. She would like to import the details rather than re-typing everything. Well, Michele, you are on the right track. I believe that whenever you have information in an electronic format, re-keying that information should be your last resort. Except when the amount of information is trivial, transferring it electronically should be the first approach you investigate.

CAUTION: Before you actually import any contacts, I suggest that you carefully read through these instructions.

The basic steps for the import are:

A. Protect and setup the Excel data

1. backup the Excel workbook in Excel 97-2003 format
2. review the data to make sure there are no blank rows or columns
3. create a header row

B. Import the contact information into Outlook

Protect and setup the Excel Data

Transferring data electronically may require some changes to your source data  so that the import will go more smoothly. Therefore, the first step is to backup (duplicate) your Excel workbook. Keep in mind that the Outlook import process cannot read Excel 2007 (.xlsx) files. So if your workbook is a 2007 file, save a copy as an Excel 97-2003 workbook.

Close your original file and open the duplicate you just created. It is important that you have the data to be imported set up according to some simple rules.

First of all there should be no blank columns and no blank rows. The import process will interpret blank rows and columns as the boundaries of the data. Data to the right of a blank column or below a blank row will not be imported.

The first row of the data should be a header row. This header row should contain names for each column of data (field names), rather than contact information. Although you can use almost any names for the header row, you will save yourself a little work by using the names that Outlook itself uses to describe the data. Here is a list of field names used by Outlook:

 

Title	FirstName	MiddleName	LastName
Suffix	Company	Department	JobTitle
BusinessStreet	BusinessStreet2	BusinessStreet3	BusinessCity
BusinessState	BusinessPostalCode	BusinessCountryRegion	HomeStreet
HomeStreet2	HomeStreet3	HomeCity	HomeState
HomePostalCode	HomeCountryRegion	OtherStreet	OtherStreet2
OtherStreet3	OtherCity	OtherState	OtherPostalCode
OtherCountryRegion	AssistantsPhone	BusinessFax	BusinessPhone
BusinessPhone2	Callback	CarPhone	CompanyMainPhone
HomeFax	HomePhone	HomePhone2	ISDN
MobilePhone	OtherFax	OtherPhone	Pager
PrimaryPhone	RadioPhone	TTYTDDPhone	Telex
Account	Anniversary	AssistantsName	BillingInformation
Birthday	BusinessAddressPOBox	Categories	Children
DirectoryServer	EmailAddress	EmailType	EmailDisplayName
Email2Address	Email2Type	Email2DisplayName	Email3Address
Email3Type	Email3DisplayName	Gender	GovernmentIDNumber
Hobby	HomeAddressPOBox	Initials	InternetFreeBusy
Keywords	Language1	Location	ManagersName
Mileage	Notes	OfficeLocation	OrganizationalIDNumber
OtherAddressPOBox	Priority	Private	Profession
ReferredBy	Sensitivity	Spouse	User1
User2	User3	User4	WebPage

Don't be daunted by the number (92) of field names. You need only the field names that are appropriate for your data, just as long as you have one column for each field of your data. Let's say that you have only first and last name, company, and email address then you will have only four columns in your worksheet. The first row (header) would have FirstName, LastName, Company, and EmailAddress in the first, second, third and fourth column, respectively.

This is a short list that could be imported into Outlook quite easily. I obtained the list of field names by doing a test export to Excel from my Outlook contact list. Notice in particular that the field names have no spaces.

Import the contact information into Outlook

Outlook provides an Import/Export wizard to help set up the process. Before you start the wizard, you will need to decide whether you want the new contact list to be included in your current contact list or if you would like to have a separate custom contact folder for the import. Now is the time to create a custom folder if you need one.

Start the wizard from the Outlook File menu by selecting Import and Export...

  

 

 

 

 

 

 

 

 

 

 

 

 

 

Then select Import from another program or file and click next.

Select Microsoft Excel 97-2003 from the file type list.

 

 

 

 

 

 

 

 

 

 

 

 

 

Click the browse button and use the window that opens to navigate to and select the Excel file that contains your list of contacts to be imported.

 

 

 

 

 

 

 

 

 

 

 

 

Then select the Outlook folder into which you want to import the new contacts. If you prefer not to mingle the new contacts with your existing contacts, you could create a new contact type folder within your contacts folder. That will isolate the additions and allow you to delete them en masse if you need to.

 

 

 

 

 

 

 

 

 

 

Before clicking Finish, review the action to be performed. I chose not to use Outlook's field names when you were setting up your Excel file, you can use the Map Custom Fields to indicate which Outlook fields should be matched to each of your fields.

When you click finish, your contact list will be imported into the Outlook folder you have specified.

Once again, thanks Michele for raising another excellent question.

The problem:

One of my clients presented the following problem. She is required to transcribe meeting minutes in a format that uses a Word table. Word was inserting page breaks whenever there was not enough room at the bottom of a page for the full text of one of the columns for the next item in the minutes. She wanted the page break to occur only when the page was 'full.'

The solution:

Start by selecting the entire table. This is best done by clicking anywhere in the table and then eithe4 click the handle that appears by the top left corner of the table or presseing <ctrl>-A.

 

Next, select the Table Tools Layout Table and click the Properties shortcut.

Use the Table Properties Row Page to select the "Allow row to break across pages" option.

Each row of the table will then continue to the bottom of the page and continue at the top of the next page if necessary. Thanks Michele for raising the question.

Data validation lists help restrict data entry to values that you have included in a list of valid/acceptable values. For example, you can use a date validation list to prevent 'creative' spelling differences for expense categories. The feature adds a 'drop-down' to the cells to which you have applied data validation.

I have long had a problem with one aspect of the feature however because of this error message:

One standard I try to maintain in my workbooks and encourage my trainees to use is that each data table should be on its own worksheet. According to the error message, however, you must store a data validation list on the same worksheet as the data table for which you want have some cells whose values come from a lookup list. Keeping the lookup list on a separate worksheet would keep its details 'out of site, out of mind,' only to be seen when the user is accessing one of the cells that has data validation applied. The error message implies that I cannot enforce my design standard if I want to use a data validation list.

Thanks to Monique, a trainee with whom I was working last week, I learned that the error message does not tell the whole truth. It is indeed possible to have a lookup list on a different worksheet. This means that I can manage my data in the way I want to manage it; I can have the lookup list for a data table on a separate worksheet. It also means that I can more easily maintain lookup lists by keeping them all on a single worksheet dedicated to lookup lists.

How you go about it depends on the Excel version you are working with. For Excel 2000, XP (2002), and 2003, you will need to create a range name for each lookup list. In Excel 2007 you can use range names or type the lookup reference in the Sheet Name!Range format.

Try it out. In a new blank workbook, name one of the sheets Lookups, and another, Data. On the Lookups sheet type a list of country names. In this example, 'Canada' is in C1, and 'USA' is in C2. Obviously, you might have a much longer list of country names. Name the range 'Countries'. Be sure to include the full list of countries when you are naming the range.

Now switch to the Data worksheet. Select the column (or cells) to which you want to apply the Data Validation List. In Excel 2000 - 2003, Select Validation from the Data menu. In Excel 2007 you will find Data Validation on the Data tab of the ribbon. In all versions, this will open the Data Validation dialogue.

Use the Data Validation dialogue Settings tab to Allow List. In the Source text box enter "=Country" (without the quotation marks.)

Although I don't recommend it, in Excel 2007, you can refer to the list using Sheet Cell reference by typing the reference into the Source text box ("=Sheet!Range" format.) If the sheet name includes a space you will need to enclose it in single quotes. Because the dialogue box is open you will not be able to select the sheet and range using your mouse. For that reason alone, I prefer to use a range name for the data list. Use F3 to be able to select the relevant range name.

"Where can I find a complete list of Excel keyboard shortcut key?' This question comes up in my classes from time to time. You can find a full list on the Office On-Line web site. You may find the list just a little overwhelming, given that one or more shortcuts are associated with most or your keyboard keys. So, here a few of what I consider to be the more useful shortcut combinations.

 

Shortcut	Description	Comment
CTRL + ;	Enter the current date	unchanging date stamp
CTRL +  :	Enter the current time	unchanging time stamp
CTRL + `	Toggle display of cell values and formulas on the worksheet	this is the key on the left side of top row of standard keys (beside the '1' key
CTRL + '	Copies the formula from the cell above
CTRL  + SHIFT + "	Copies the value from the cell above
CTRL + SHIFT + *	Selects cells around the active cell bounded by blank rows and columns	this is the shifted 8 key in the top row of standard keys
CTRL + HOME	Moves to the beginning of the worksheet	Usually moves to cell A1 unless Freeze Panes is in effect then CTRL + HOME will move to the Top Left Cell of the Bottom Right Quadrant
CTRL + SHIFT + HOME	Extends the selection from the current cell to the beginning of the worksheet	If Freeze Panes is in effect, extends the selection from the current cell to the Top Left Cell of the Bottom Right Quadrant (if the Active Cell is in the Bottom Right Quadrant). If the Active Cell is not in the Bottom Right Quadrant, extends the selection to include the Top Left Cell of the Bottom Right Quadrant.
CTRL + 9	Hide the selected rows
CTRL + 0	Hide the selected columns
CTRL + SHIFT + (	Unhide rows within the selection
CTRL + SHIFT + )	Unhide columns within the selection
CTRL + C	Copy	Repeat CTRL + C to open the Office Clipboard
CTRL + X	Cut
CTRL + V	Paste the last item cut or copied	If the marquee is active
CTRL + Z	Delete the last entry you typed or reverse the last command you executed
CTRL + Y	Repeat the last command or action if possible	(Re-does the last undo)
F3	Opens the Insert Name dialogue	useful to ensure you don't misspell user defined names in formulas
F4	Creates absolute reference	while entering a formula

 

This is just a small selection of the keyboard shortcuts available in Excel. Check the Office Online link at the beginning of this article for more that may be useful to you.

Most newcomers to Project Management have little trouble understanding three of the four task dependency types. Finish to Start (FS), Start to Start (SS) and Finish to Finish (FF) all are fairly intuitive. It is quite easy to point out real world analogies.

On the other hand, the fourth type, Start to Finish (SF) is anything but intuitive. The average person in the street most likely will never have heard of the kind of special (and rare) circumstances to which a Start to Finish dependency applies. Here's an illustration that may help clarify the concept.

In this example, framing materials are required on site so that the framing task can be executed. However, the project's financial constraints require that the materials be ordered for 'just in time' delivery. In other words, we only want to incur responsibility for payment when we start to use the materials. Other considerations for 'just in time' delivery might be risk management in order to limit the possibility of theft (and reduce the cost additional security while the materials are waiting to be used.)

In this example, there is an estimated transit time of one week between the supplier and the construction site. The project schedule illustrates two basic scenarios using  a Start to Finish dependency and a 'standard' Finish to Start dependency. Compare tasks 4 and 5 to tasks 12 and 13. Task 5 can't finish until its predecessor, Task 4, begins because of the Start to Finish dependency. In the Finish to Start version, shipping is geared to pouring of the foundation, rather than the scheduled start of Framing.

The scheduling impact of SF over FS because clearer when you look at the second set of comparisons (SF - tasks 17-20, FS - tasks 25-29). Land clearing has taken an extra week. The delay in task 17 has delayed the start of the sequence of dependant tasks following it, resulting in a one week delay in starting Framing (task 20.) So it is the delay in framing that delays the start of the Purchase and Delivery of Framing Materials. Arguably, using SF dependency between Framing and Purchase and Delivery of Framing Material provides a more clear model of the real world circumstances than would have been the case with the dependency being FS.

Click to see enlarged image of project plan

 

Thanks to Clayton Barranger-Mitchell, who recently attended one of my Excel classes, for this solution.

Scenarios are a particularly useful Excel feature when you are budgeting or forecasting and calculating results based on certain assumptions. The feature makes it easy to experiment with various what-if options in the data and yet not lose track of the various sets of values when you want to try out new variations.

Here is a simple example:

Original estimates and assumptions:

Variable Cost Rate	30% of Gross Sales
Gross Sales	75,000.00
Variable Costs	22,500.00
Fixed Costs	50,000.00
Estimated Profit	2,500.00

My one complaint with the feature has been that the manager and reports both display absolute references to the relevant cells rather than what one might refer to as 'meaningful' names. I have always found it easier to think of data in terms of what it is rather than where it is located. So I might refer to 'salaries' rather than $C$36, for example.

Here's what the Scenario Values dialogue looks like if you use only cell references for the changing cells of the scenario:

The Scenario Values dialogue labels its entry boxes with standard absolute cell names. The corresponding Summary Report has similar reference and would have to be edited before you publish or present it at a meeting.

Fortunately, the problem has a simple solution. Simply create a name for each of the cells to which the scenario refers.

With meaningful names it is easier to work with the Scenario Values dialogue and the Summary Report is more readable without your having to modify the cell names in the report. Underscores in the names are a byproduct of Excel's requirement that range names may not have spaces.

Thanks to Héctor Miguel Orozco Díaz for the newsgroup posting where I first learned of this technique.

You may find it necessary at some time to prevent  the insertion of one or more columns in a worksheet. For example, consider this table:

For the sake of the demonstration, let's assume that you do not want columns to be inserted between columns C and D.

• First identify two cells, one in each of column C and D, that you do not want to use for any other purpose. In this example I will use cells C1 and D1.
• Select both cells and type the formula

=""

• after typing the formula press

Shift-Ctrl-Enter

(press and hold the shift and ctrl keys and press Enter.)

If you inspect the two cells now you will see that they each contain a formula that looks like this {=""}. This is known as an array formula. Having an array formula will prevent operation, such as column insertion, that would affect the layout of the array.

This method is not fool proof but it does provide at least some protection against inappropriate column insertion. Combining this technique with hiding the row containing the array formulas and worksheet protection adds a little bit of security to prevent the accidental deletion of the array formula. If you do not want to hide the relevant row or your version of Excel does not allow you to prevent users from selecting locked cells, enable worksheet protection format the cells' protection as locked and hidden before you enable worksheet protection.

A video demonstration of this technique is available here.

You may handle whatever file management (creating folders and renaming files, for example) you need to do by using My Computer. Did you know that you can carry out the same tasks right from within an Office Application.

Let's say, for example that you are working on a Word document that you know will be the first of a number of documents relating to a new project you will be working on. To make all of the documents for the project easy to find, you want to put them into a new folder. Of course you could simply save the file and close Word, then create your new folder and move the document you just saved into it. That would certainly work but it is time-consuming.

Creating a new folder

Start to save the file as you normally would. Then when the Save (or Save As) dialogue opens, change the Save in folder to the folder where you want the new folder to 'live.' Then click the create new folder button and type a name for the new folder and click OK. Your new folder will open ready to receive your file.

Renaming a file or folder

Now, for the sake of argument, let's say you called your new folder 'My New Project' and you realize that you will have other projects to work on from time to time. The project you are working on won't be your 'New Project' forever. So you decide to give the folder a name that will help you recognize the projects whose files it contains, the 'Manhattan Project.'

Even though you may not have a new file to save, start by closing any open files that are in the folder you want to rename. Then open the Save As dialogue. Use Save in ... or the navigation icons at the left of the dialogue to navigate to the folder containing the folder you want to rename. For this example, I named the folder 'My New Project' and it is located in my My Documents folder. Point to the folder you want to rename and click your right mouse button. Select Rename from the shortcut menu.

Type the new name for your folder and press Enter. You can then click the Cancel button to close the Save As dialogue.

In addition to creating a new folder and re-naming existing files, you can also use a similar approach to delete one or more files. One caution however, be sure to close any open file before trying to change its name or modify/delete its parent folder.

Obviously Word is a word processor and not a report writer. Perhaps not as obviously, Excel is not a data manager, it is a 'number processor.' Nevertheless I regularly work with people who, either by choice or not, use Excel to keep work-related lists, and Word to address labels and envelopes and create form letters using mail merge.

Recently, one of my trainees brought up a need she has to make custom lists from a master list stored in an Excel worksheet. She didn't want to print labels or envelopes or custom form letters. She simply wanted lists of selected clients for whom certain conditions were true. Her list had a information she wants to use to extract only certain clients.

This is an example (using fictitious data, of course) of the kind of list she is storing in an Excel worksheet.

She wanted to use this list as the source for reports that might look like this example, using Word mail merge features:

Notice that the report only includes a few of the records in the original data because it has been filtered to select only those people who have 'accounting' in their job title.

When I started to work out a solution that would work for my client, I found that applying the mail merge features to the problem would provide a satisfactory solution but the methodology to do the job wasn't completely intuitive.

If you are interested in doing something similar and don't mind a bit of a read, I have posted the tutorial I developed downloadable pdf.

Today is an especially happy day for me. Of course, I am happy because today in Canada's official birthday. But I am doubly happy because, I have just received word that Microsoft have recognized me as a Most Valuable Professional for another year. This is my second MVP award. You can view my MVP Profile here and see a summary of the program here.

The blog has been a little quiet for the past few days and I have fallen behind my self-imposed target of two to three articles a week. I am presently writing a fairly comprehensive tutorial on using Word's merge tools to produce list style reports. As soon as it is ready, I will be posting a summary with links to the tutorial for you to be able download the full piece.

In my last entry, I discussed choosing the right tool for the job, including some links to pages that will help you decide whether Excel or Access should be your tool of choice for data management. If you have chosen Excel because

1. you don't have a lot of data,
2. you think you don't need related data, and/or
3. Excel seems to be less dependant on rules for how the data should be stored

(not necessarily in order of priority), you may plunge in to creating the list only to find yourself with a significant problem once you have all the information recorded in the list.

One common mistake in setting up your list is to include all name information in one column. After all, what could be more natural that to have a cell display John Smith's name just as you would think about it in real life. It may seem silly, even cumbersome to separate the first name from the last name, and if you have middle initials for some or all people, to put that information in yet another cell. So you create your list and now have several hundred or several thousand rows of data, all with each person's full name in a single cell.

Looks nice, doesn't it? Well now, let's put the list to work. I would like to see the list sorted into alphabetical order based on the person's last name. If you entered the names in the form of "first name, last name" you will be unable to perform the sort. On the other hand, if you have entered the names in the form or "last name, first name" you will be able to sort the list into alphabetical order by last name. So far so good.

But now I notice that there are several people who have the same first name. Now I want to see them sorted in order so that John Smith comes before Mary Smith. Later when I add Edward Smith to the list, I want to see his name before John's. This sort may still be possible but if you have paid close attention to how you have entered each name, you may never be able to sort the list exactly as you want. For example when you are entering data into the list, you may forget to type the comma after every name. On a bad day, you may even reverse the order and type "first name last name" for some people.

List management should be about saving time. However, unless you put some thought and planning it how you create the list, instead of saving time, you will have created more work for yourself, rather than less.

The preferred solution to the name problem I have described is to separate each name into the parts that make it up. Each part should be in its own cell. That means not one cell to hold the entire name but at least two(last name, first name) and possible three, if you have middle names or initials to keep track of.

"Now you tell me," you say. "I already have my list with names all in the same column. Do I have to re-type all that information?" The answer is, it depends. Most likely you will have to re-type some of the information but if you have been consistent with how you entered most of the names, there is a tool that can cut down your work.

Beware of relying too much on this tool, however. You will need to ensure that you have enough blank columns immediately to the right of the column you want to process (it's called parsing.) You will have to determine what character separates the name. (Remember that <space> is also a character.) Above all, make sure to backup the file before you start. You don't want to lose data because you didn't set things up correctly.

In Excel 2000, XP, and 2003 look for the Text to columns... command on the Data menu.

 

 

 

 

In Excel 2007, you will need the Data Tab of the Ribbon. Look for the Text to columns feature.

I frequently hear of situations where a company or a person working within that company has chosen to solve a problem with entirely the wrong application. Often their choice favors the tool they are most familiar with. Psychologist Abraham Maslow put it this way, "When the only tool you have is a hammer, everything looks like a nail."

For example a person familiar only with word processors may well choose to manage tabular data in a Word table. For a trivial small amount of information the solution might work. The problem is that requirements put on the solution tend to grow over time. Using the tool to get the results you want becomes more and more difficult and time consuming.

Choosing whether to use Excel or Access to manage data involves some important decision making. Far too frequently,the choice defaults to the more familiar application, Excel, rather than to the right tool for the job. Far too often, users will simply jump into putting together a solution before they fully understand the problem.

Help in making the choice between Access and Excel is as near as the Internet. The short training presentation clearly discusses some of the major issues you should consider before opting for either Access or Excel. This Quick Reference Card presents a brief summary of the training topic.

If you prefer a short article the see this one on Office On-line.

Regardless of the version of office you are using, you should apply the same principles in deciding whether Access or Excel should be your tool of choice.

I came across this interesting movie clip last week. If you are new to Office 2007, having used earlier versions to the point where the transition to the Word, Powerpoint, or Excel 2007 Ribbon Interface is almost traumatic, this presentation may help you understand "what on earth were they thinking." The presentation takes you through a brief history of the Office interface from the late 80's/early 90's to the interface release last year, Office 2007. In addition, Jensen Harris, who is the Group Program Manager of the Microsoft Office User Experience Team, reviews some statistics about what people use and do not use, and the evolution of the Ribbon interface from concept to release last year.

Check out the blog entry and the clip "The Story of the Ribbon."