Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
Article Categories

Posts Tagged ‘Productivity’

Five Essential Keyboard Shortcuts

ShortcutTrying to learn keyboard shortcuts can be intimidating; there are just so many. Like most problems, however, you can overcome the problem by breaking it down to bite-sized chunks. So if you haven’t been using keyboard shortcuts, start by learning and using just these five. Chances once you have used them only a few times, they will become second nature. Remember that some keyboard shortcuts require a combination of keys. That means you press and hold the first (second, and third, in some cases) and tap the last. As you will see, frequently the key to use makes logical sense but sometimes it doesn’t.

<ctrl>A Select the entire document
<ctrl>X Cut the selected content or object(s)
<ctrl>C Copy the selected content or object(s)
<ctrl>V Paste what you have cut or copied
<ctrl>Z Undo the last action you did

These particular keyboard shortcuts (and many more, actually) have a consistent Beachmeaning throughout the Windows world. Whether you are working with a Word Document, an Excel Workbook, a PowerPoint Presentation, any other Office document, or Windows (File) Explorer, you use the same keyboard shortcuts.

Why bother learning these or other keyboard shortcuts? It’s all about time. To be sure, the time you save by using a keyboard shortcut once is insignificantly brief. But these shortcuts do things that you do frequently and repeatedly whenever you are working on your computer. Think of those tiny slivers of time as grains of sand on a beach. Save enough of them any you will have time for a beach vacation.

Links for the technically inclined:

Announcing the Biggest VM Sizes Available in the Cloud: New Azure GS-VM Series

Important announcement in Azure space


Announcing VP9 support coming to Microsoft Edge

New feature for Microsoft Edge


Unity 5.2 and Visual Studio Tools for Unity 2.1

Visual Studio is the new default Unity scripting editor on Windows


Announcing the Microsoft Azure Tour

The Microsoft Azure Tour is a free one day technical training event for developers and IT professionals to help you achieve more success with Azure.


Visual Studio Code and Visual Studio Online

Blog on the integration with Git


See How One Note Can Enhance Your Productivity


This Blog article contains a laundry list of how one person make the most of OneNote in his business and personal life.

Manage Application Licences With OneNote

imageWhether you purchase applications on-line and download the installation files or install new software from installing disks, keeping a central record of Application Licences and Activation Codes can greatly simplify managing the applications you have and having the information you need (User Name, associated email, licence number and activation code).  OneNote provides a simple solution for individuals and small businesses who do not have ready access to Information Technology experts who specialize in such management.



Keeping the information organized simplifies the process of

  • upgrading applications
  • re-installing applications
  • transferring applications to a new computer system.

There are, of course, a number of different ways you could organize this information in OneNote. My notebook has just two sections.


The contents section has only one page which serves as a table of contents for the notebook and as a quick list of all applications. Each entry on this page is a hyperlink to the main page for each application


I have organized the Applications Section by having a Page for each application. If I have received the main information by email then I simply send the email to OneNote using the link on my Outlook Ribbon. Once I have created this page, I copy the link to it and insert it as a hyperlink on the Contents page.

Occasionally, additional information about the application may come up. For example, I called support for one application. To keep a record of the discussion and its outcome I created a subpage for the application, titling the page Support followed by the date of the discussion. Having a separate subpage for each support call, if there is more than one, makes it easy to track down and review the applications support call history

An alternative approach to organizing application information might be to create a separate OneNote section for each application with as many detail pages as necessary.

However you organize the pages, a summary of key application information at the top of the main application page can be helpful. Include in this summary as much key information as you have used in obtaining the application in the first place. For example most of the applications I have installed have the following types of information:

  • Developer/Provider Website
  • User Name
  • User Email
  • Serial Number
  • Activation Code
  • Sales Contact
  • Support Number/Address
  • Version
  • Installation Date
  • Upgrade Versions and Installation Dates


If I have created an account with the Provider that I purchased the application from, the one piece of information that I do not store in OneNote, is my password. Similarly, if the application itself requires a log-in password, for security reasons I do not store the password in the OneNote workbook.

OneNote makes it quick and easy to store and update Application “Vital Statistics.” Investing a few minutes to record this information when you install a new application can save you hours of frustration in the future trying to remember where you put that scrap of paper that scribbled the details on.

Data Validation Using Multi-tiered Lists

I came across this video today which is an excellent tutorial of advanced Excel techniques for data validation. The example uses the Indirect() function as the source for the lists that are used as choices in a ‘drop-down’. I will let the video speak for itself.


Creating multitiered lists

One thing that the video doesn’t mention, is that because the lists validation works with are named ranges, they can actually be on a different sheet than the one that the data validation is used on. Ordinarily, data validation lists must be on the same sheet as the cells with the validation rules.

Have You Pinned Lately

Excel, PowerPoint, and Word 2007 all have Recent Documents lists on the Office Menu. You can set an option for the number (up to 50) OddGiftof document titles to be displayed on this list. It’s a handy way of getting back to documents that you have recently worked on. You don’t need to remember where the document is. Just click the name  in the Recent Documents list.

Quote of the Day

Ultimately, the only power to which man should aspire is that which he exercises over himself.

– Elie Wiesel

Even with the option set to retain 50 documents on the list, however, a document that you use only occasionally may be forced off the list if you work with a large number of different documents. Tracking down files that you use Office Menuonly once in a while can be challenging, to say the least.  Did you know that you can mark a document name so that it always stays on the list?

To the right of each document name, there is a grey pushpin icon. If you click that icon, the icon will change so that it looks like it is pushed into a corkboard and the colour will change to green. A document name with the green pushpin will always stay on the Recent Documents list, regardless of how many other documents you open and close or how long since you have opened the document.

The name will not always be on the top but it will be in the Recent Documents list. So, if you only open that Greeting Card list once a year and want to find it easily next year, click its pushpin icon on the Office Menu. The next time you go to open it, that once a year document will probably be at the bottom of the list but it will still be there waiting for you to click its name so you can get ready to send next years cards.

Play Nicely with Others

Windows Live Sky Drive is about to get better!Sailor2 picture

Talk about coincidence. A few days ago I needed to set up an Excel workbook to collect some basic information from several people scattered around the globe. We are working on a project involving the transfer and update of several hundred small documents from one World Wide Web site to a related but different site.  Because we need to make small changes to the documents in the process, they need us to work on them one at a time. The problem some of us saw, of course, was how to keep each other informed about which documents we were individually working on at the moment, which were completed, and which remained to be worked on and moved.

The tracking method had to be simple and accessible to all of us regardless of where we live in the world. Our timing is just a little off it seems because Office 2010 is in the wings, about to be released and it seems to me that and Access 2010 Web Application would be an excellent solution.

Two things prevented me from trying that approach, however. First, we needed to start the project sooner rather than later. Second, I am not up to speed with Access Web Applications yet. Fortunately, an alternate solution that we can apply in the meantime does exist: an Excel workbook hosted in Office Live Workspace.

Quote of the Day

Education is when you read the fine print. Experience is what you get if you don’t.

-Pete Seeger

Office Live has been available for several years. This service allows you to  store and share documents online. So once I had set up a workbook with the document lists for our team all I had to do was save the file to my Office Live account in a folder for the project. Then I used the sharing feature to add each of the team members as an editor for the project folder.

We have been using the workbook for several days now. To each of us, it appears that it ‘lives’ on our local system and that’s because we are doing the actual editing in our respective local Excel installations. Here’s how it works. John decides to work on document A. He goes into Excel and opens the Office Live workbook. If he isn’t logged into the Office Live account, he will have to enter his user name and password. Then except for a brief pause while the file downloads, the workbook opens in John’s copy of Excel.

He then finds the title of the document he intends to work with, marks an X in the In Progress column and closes the workbook. Of course he answers yes to the Save dialogue to save the document.

A few minutes later, as luck would have it, Mary decides to work on the same document that John is now working on. When Mary opens the Office Live workbook and finds the document title, she will see John’s X in the In Progress column. Knowing that someone else is working on the document, she can choose a different one and record that she is now working on that one.

Now, the coincidence that I mentioned at the beginning of this article isn’t about John and Mary deciding to work on the same piece at the same time. That’s a coincidence all right but an even more significant one happened for me this morning. I had already decided to write this article. Then in my email this morning I receive an RSS copy of this blog article from the Microsoft Office 2010 Engineering team, Accessing your Office files from any computer with Windows Live SkyDrive. This article links to a Excel oriented article, Collaborative Editing Using Excel Web App.

So it seems that the kind of document collaboration using Office Live Workspace that I had set up is about to get even simpler. I should explain that the Web App approach will be using SkyDrive, a Windows Live Service. The system I described uses Office Live Workspace. I have had both Office Live Workspace and SkyDrive accounts for some time but until now, I used the SkyDrive account simply for files that I wanted to make available for others to download. Soon, I will be able to use my SkyDrive for collaborative documents as well.

New at the Access Wiki

Access Wiki moderators are hard at work moving UA Forums FAQ and code archive content to the wiki. This will add many new articles to the Wiki’s growing content. There are some 850 code archive items alone. Since UA started in 2002 there have been more than 1,940,000 view and download of code archive content.

Project 2010 Preview

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.

Streamline Workbook Creation with Autofill

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:

imageTo 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 TechniquesRemember 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.




A few of my favourite (Excel) things

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?