Quote of the Day: Copy from one, it's plagiarism; copy from two, it's research. --Wilson Mizner

I tried a little experiment yesterday and was pleasantly surprised by the results. A while back I had learned how to link a chart title to data stored in an Excel worksheet. That in itself is an enhancement to charting.

If you have done any charting (graphing) in Excel, you will know that most of the chart’s values are stored in a worksheet. Change the worksheet values, and the chart will automatically update. Here’s an example. Let’s say that I am using a worksheet to prepare a monthly sales presentation to management. Management want to see the relative contribution of each salesperson to year to date sales. So I have set up a simple pie chart to display the information. Obviously, I have much more to do in my job than just to prepare this once a month presentation so I want to produce this chart as efficiently as possible.

Now, when I created the chart, using the chart wizard, I had to ‘hard code’ the title. That means that each month after I have updated the underlying date, I have to manually edit the chart title so that it reflect the reporting month. That takes a minute or two and for me it’s easy to forget. Forgetting to change the title costs more time. I have to re-open the file and make the change. Notice how the chart title at the moment is simply, “YTD Sales,” not reflecting the fiscal period.

To keep management of this chart as simple and efficient as possible I want to enter data only into the five cells that are highlighted in yellow. Of course the sales figure cells could actually be getting their values from formulas referring to the raw data. Then all I would have to do is enter the period ending date (and even that cell could contain a formula referring to the raw data.)

Here I have made one small change. The textbox containing the chart title no longer contains literal text. I have replaced the literal text with a formula that refers to a cell  containing the title exactly as I would like to see it in the chart. Now, regardless of the reporting date, the chart will always indicate the correct date.

These examples show an embedded chart. Having a date specific title is not as critical in embedded charts as it is in charts that are rendered on separate chart sheets in the workbook. In those cases, you can’t just glance at the data to see the reporting date  applicable to the chart without moving to the other worksheet.

The formulas revealed

The next graphic exposes the formulas involved. I have switched the worksheet to display formulas rather than the values generated by those formulas using ctrl+`. For this example, the sales results are simply random numbers because I don’t have any real data that can be published. The chart title text box contains a formula as well but it is still displaying the value rather than the actual formula which, in this case, is =$D$4.

Cell D4 itself contains a formula that creates and formats the text I would like as the chart title.

Now you ask, why would I want to take the time to set this up when I could simply edit the title every month? For me it’s a no brainer. Yes, it took some time to set up – probably less than five minutes, to be honest with you. What is the payback on that investment of my time. Sure it is small but let’s say it takes me 1-1/2 to 2 minutes each month to edit the title. In the first three months I have more than recovered my time investment.Then there is the intangible benefit of saving me the embarrassment of forgetting to change the title or occasionally misspelling something in the title. Besides, I just think this is a cool possibility.

Remember One Trick

The is just one small thing to remember when you want to try this yourself. Ordinarily, when you want to enter a formula you select a cell and then type the formula in either the cell or the formula bar.  When you want to enter a formula in a chart title text box, select the textbox but do not type anything in it; when you have the textbox selected, type the formula in the formula bar.

This article, John R. Durant's WebLog : Why VBA Still Makes Sense , presents a solid case  for why VBA is the ‘next level’ when it comes to application development in Access and other members of the Office suite. John provides an excellent of VBA since its early days to the present and on into Office 2010 including his perspective on the ‘fit’ between .NET and VBA. John R. Durant's WebLog : Why VBA Still Makes Sense is definitely a worthwhile read.

If you are a Project user or are interested in the latest developments in Microsoft Project, check out this Office Team Blog overview. The new version looks exciting.

Quote of The Day Ask not what the world needs. Ask what makes you come alive... then go do it. Because what the world needs is people who have come alive. Howard Thurman

One day I was teaching an Introduction to Excel class when one of the participants questioned why it was important to ‘do everything quickly.’ I had discussing ways to efficiently create a workbook. I took the comment as coming from the perspective that many North Americans and Canadians, specifically, live life at too fast a pace. I happen to agree with that point of view but at the same time I prefer to get the drudgery of a task quickly out of the way so that I can dedicate more time to parts of the task that I find more interesting.

Autofill is an Excel feature that many self-taught users are not aware of. It’s easy to miss the tiny clue that the feature even exists. Autofill has two main purposes in Excel, extending a series, and copying formulas.

The Autofill handle

Unless you look very carefully, it’s easy to miss the autofill handle at the bottom right corner of the selection outline.

Once you have found the handle, you also need to watch the shape of your mouse pointer carefully:

When the mouse pointer resembles a black plus-sign, you are pointing at the autofill handle. You are ready to autofill. If the mouse pointer is any other shape, you are not  ready to autofill:

To perform the autofill, point your mouse at the autofill handle, then press and hold  the left mouse button. Then move your hand in the direction that you want to fill, either vertically or horizontally.

Extending Series

Excel recognizes certain data as belonging to one or other series. Weekday names, month names, expressions like ‘Qtr 1’ or ‘1st Quarter’, these are all series that autofill can automatically extend for you. Here is a brief video demonstration in which I create a series of month names for an income and expense worksheet. (I recorded

this demo using Excel 2007 so there are some screen features that you may not see if you are using a version of Excel earlier than 2007. The basic autofill techniques and results are the same however. In the demo, I started with the abbreviation for the month of January. If I had entered the full name, then autofill would have inserted the un-abbreviated name for each month into the cell. Note also that the case of the starting cell determines the case of all of the autofilled cells.

Copying Formulas

If the only thing autofill could do was to extend series, saving us from some typing drudgery, it would still be a useful tool that every Excel user should be aware of.

However, autofill can do more. Try autofilling a formula, instead of the first cell of a series. You will find that  autofilling may faster than copying and pasting formulas when you want to apply a formula across several rows or columns

Advanced Autofill Techniques

Remember that Autofill can extend any series as far as you need.  Now, here are some specialized techniques.

• create a series of sequential numbers
  • in Excel 2000, enter the beginning number in a cell and then hold the <Ctrl> key down while you drag the autofill handle.
  • in Excel XP (2002) and later, enter the beginning number in a cell, drag the autofill handle, then click the fill options button and select series.
  • if you want a sequential series to the right or left of a column of data, enter the starting value in the cell to the left or right of the column containing the data, point to the fill handle and double click.
• create an intermittent series
  • to create a series of even numbers, enter the first number of the series into one cell, the next number in the series in the next cell down or to the right (depending on the direction in which you want to fill), select both cells and then fill.
  • to create a series of five weekdays with no weekend days, create a series from the first day to the last day you want to include in the week, copy that last of days and paste it in the immediate next cell, then select all of the cells that have a day name in them and fill for as many weeks as you need.

This video demonstrates these techniques.

Perhaps not as sensually satisfying as raindrops on roses or whiskers on kittens, but when it comes to Excel, these are a few of my favourite things:

• autofill

Quickly copy formulas or create a series of months, days, quarters, or other custom series.

• cell and range names

Use cell or range names to make formulas easier to understand at a glance.

• templates

Stop re-inventing the wheel when you need new workbooks to handle data with tried and true formulas and layouts

• lookup functions

Reduce the amount of repetitive data in your workbook

• absolute references

Make it easier (and faster) to copy formulas that need to refer to the same value regardless of where you copy them to

• 3-D formulas

Create a worksheet to summarize data from detail worksheets using simple formulas.

• header and footer improvements (Excel 2007)

Custom headers and footers made a giant step forward with Excel 2007

• tables (Excel 2007)

Although the table concept is not new with Excel 2007, this version has added some amazing enhancements to tables.

• keyboard shortcuts

Keyboard shortcuts were absolutely essential in Dos days. They may not be essential in today’s mouse-based graphical user interface but they are as useful as ever.

• date and time
• copy cell data
• paste
• cut
• undo
• display formulas or their results
• draw borders (ctrl-Shift-7)

 

• custom sort order

For values that you want to put in order in a non-standard way

• custom number formats

For those numbers that are not numbers (you know – values like telephone or serial numbers that you would never ‘do’ math on.)

Watch for these and other articles in the coming weeks. If you have other favourite Excel things, drop me a note and tell me what they are and why you like them?

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.