OfficeTipsAndMethods

Posts Tagged ‘Productivity’

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) of 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 only 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.

Windows Live Sky Drive is about to get better!

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.

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.

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.

Advanced Autofill TechniquesRemember that Autofill can extend any series as far as you need.  Now, here are some specialized 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?