OfficeTipsAndMethods

Archive for the ‘Excel 2010’ Category

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.

Quote of the day To become aware of the possibility of the search is to be onto something. -Walker Percy

Natasha wanted to graph photocopier downtimes to show the duration of each downtime in minutes with data points labeled with the date of the down time incident. Her data list included the date of the copier needed service and the number of minutes it remained out of order. This seems simple enough but when she tried to generate a graph for the year, every day of the year appeared in the graph. That wasn’t quite what Natasha was looking for. She wanted to show only dates when there was copier downtime.

Graphing, it seems, assumes that if a series consists of date values then you want a date series showing every date between the first and last dates. Here you see the problem in the graph on the left, and a solution in the graph on the right. In the original data, the first entry is February 12 and the last, December 14. Notice how the graph is display dates from February 12 to December 12.

Because of the size of the graph, the labels display only one date for each of the eleven months.

The graph on the right, on the other hand, has data points only for each of the dates in its data series. (The size of the graph may cause alternate data points to be un-labeled, so the graph might need to be widened somewhat so that each data point has a label. You may also have to format the axis.) Because the X-Axis data is self-explanatory, I have removed the legend from both graphs.

The secret, to paraphrase Fried Green Tomatoes, is in the formula. Actually, it is in a function that the formula uses. TEXT() is a very versatile function, well worth adding to your Excel arsenal

In this example, I have used TEXT() to convert the date values in column B to text values that include the name of the weekday on which the date occurs.

The TEXT() function converts numeric values to text . It takes two arguments, a reference to a cell containing a numeric value and a string to indicate how the text should be formatted.

For example, the formula in cell J3 is:

=TEXT(B3, “ddd mmm dd”)

The format string here specifies that the text should begin with the three character abbreviation for the day name followed by a space, the three character abbreviation for the month name, another space, and finally a two digit number for the day.

There are many variations on the format string. Search the Excel help files for Text Functions or point your browser to Office On-line. The help article gives full details of how to specify the format string (the second argument of the function) depending on the numeric value you are trying to convert to text.

The point in this example is that by converting the dates in column B to text, and using the data in column J for the series labels, we can fool Excel into displaying date without having it automatically treating the dates a continuous series from the earliest date to the latest date.

Thanks Natasha for raising the question.

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?