Maria’s Guides

“Working with Excel Files” goes online.

Just a quick note to let Excel 2007 users out there know that Informit has published an excerpt from my Excel 2007 Visual QuickStart Guide on its Web site. The piece is titled “Working with Excel Files.” Access to the site is free.

I’m pretty sure the book is out, but I haven’t had an author copy in my hot little hands yet.

I think my recent article about using WordPress with Google sitemaps will be online there next week. Stay tuned for more info.

Information from the Microsoft Web site.

This update contains several improvements to enhance security and stability, including fixes for vulnerabilities that an attacker can use to overwrite the contents of your computer’s memory with malicious code. In addition, this update includes all the improvements released in all previous Office 2004 updates.

System requirements

To install this update, your computer must meet the following minimum requirements:

Operating system: Mac OS X 10.2.8 (Jaguar) or a later version of Mac OS

Improvements for all Office 2004 applications

• Security is improved
  This update fixes vulnerabilities in Office 2004 that an attacker can use to overwrite the contents of your computer’s memory with malicious code.
• Performance is improved when dragging objects to application windows
  This update fixes an issue that causes an increase in utilization of system resources when you drag a file or object to an Office application window.

Improvements for Word 2004

• Version numbers display correctly
  This update fixes an issue that prevents Word from displaying its complete version number in the About Word dialog (for example, 11.3.5).

Improvements for Excel 2004

• Reliability is improved
  This update fixes one issue that, when you open Excel, causes the application to unexpectedly open workbooks or other files.

Improvements for PowerPoint 2004

• Stability is improved
  This update fixes an issue that causes PowerPoint to quit unexpectedly when opening presentations.

Improvements for Entourage 2004

• Performance is improved
  This update fixes an issue that caused Entourage to stop responding for several minutes during ordinary use.

More information

If you have technical questions or problems downloading or using this update, visit the Microsoft Support page at www.microsoft.com/mac/support to learn about the support options that are available to you.

That’s book number 68.

I put the finishing touches on Microsoft Office Excel 2007: Visual QuickStart Guide. It’s my 68th book (I just counted) and right now, I feel as if I wrote them all yesterday.

Okay, so not that tired.

I had some trouble with this book. First, there was the beta software situation. Not only did I have to work with the Office 2007 beta, but I had to run it on the Vista beta. Double Microsoft Windows betas for a person who usually works on a Mac! You can imagine my concern.

But everything went pretty smoothly with that and I’ve been using release versions since January, so I know everything in the book is based on the final software.

Motivation slowed me down a bit in the middle of the project. I think I really need an editor cracking a whip over my head to get me to work at my old pace. These days, I’d rather fly than write about Excel. (Can you imagine?) The thing that snapped me out of it was money. If I don’t make milestones, my publisher does not send checks. Although Flying M Air is now paying all of its own bills — thank heaven; you should see some of those bills! — it’s not paying my bills. If I don’t write, I don’t eat. And since I like to eat, I became motivated.

Of course, the killer was my February hard disk crash and the two weeks it took me to get everything back to normal here. What a productivity killer! But it taught me a new valuable lesson about backups — you think I would have learned the last two times — and my old dual G5 is still running, now with a new hard disk to go with last year’s new motherboard. Sheesh. (Now you know why I bought AppleCare for my MacBook Pro.)

I churned through the last few chapters relatively quickly, anxious to meet deadlines tied to promotional opportunities. (I’m not sure of those promos really exist or if my editor has learned to tell me about fantasy promos to get me to work faster. I wouldn’t blame her if she made it up.) I had first pass files done last week and spent the past few days finalizing files based on edits. Today, after fooling around a bit — I’m the queen of procrastination — I laid out the index, created an ad for the book’s companion Web site, and turned it all in. The e-mail message I sent to my editor said:

I think I’m done. Can you ask them to send that final check? (Still waiting for the last one, too.)

The book weighs in at 360 pages, which is about the same as the last edition. It’s got the new VQS cover design. It lists for $21.99, but you can buy it from Amazon.com for $14.95 right now, which is 32% off. (Not a bad deal.) It should be in stores by April 20 or thereabouts.

Meanwhile, life goes on.

Tomorrow, I have to take my helicopter in to the avionics shop in Mesa to see if they can figure out why my radio isn’t working right. I have a meeting with a marketing guy down there at 10 AM. Then a tour of Phoenix for a man and his daughter at 2. Somewhere in between, I’ll have lunch with Mike, who has been away for the past few days. Then a flight home.

Friday I get started on my next book. Those of you who know me should know what that is.

A how-to for Excel users.

My Visual QuickStart Guide books for Excel — from the edition for Excel 95 for Windows through the edition for Excel X for Macintosh — have always included an Excel function reference. In an effort to make pages available for other content, however, we’ve cut that appendix from the Excel 2007 edition of the book. I don’t feel badly about the cut, since this information is readily available in Excel Help and on the Web.

Here’s how you can find a function reference for your version of Excel and print it for your own hard-copy reference guide.

In Excel 2007 for Windows

1. Click the Help button in the upper-right corner of the Excel window to display the Excel Help window.
2. Enter function list in the search box and press Enter.
3. Among the search results that appear, you should see an item titled “List of worksheet functions (by category).” Click its link.
   
4. A help document titled “List of worksheet functions (by category) appears in the help window. It includes a complete list with brief descriptions of all Excel 2007 functions. You can read through this document and click links within it to learn more about specific functions.
   
5. To print the reference sheet, click the Print button in the Excel Help window’s toolbar. Use the Print dialog that appears to set printing options and click the Print button.

Note that you may need a connection to the Internet to access the Function List from within Excel Help. And remember that you can always resize the Excel Help window to better read what’s inside it.

Get this Information Online

This reference information is also available online for some versions of Excel. Click this one of these links:

• Excel 2007 List of Worksheet Functions (by category) - Microsoft has added dozens of new functions to Excel 2007.
• Excel 2003 List of Worksheet functions (by category) - These functions also apply to Excel 2000 (for Windows), Excel 2001 (for Macintosh), Excel 2002 (for Windows), Excel X (for Macintosh), and Excel 2004 (for Macintosh).

These pages contain clickable links to details about specific functions. They can also be printed from within your Web browser; use the Print command.

Informit releases a chapter of my Peachpit Press book on its Web site.

Every once in a while, Informit.com, the online publishing arm of Pearson Education (parent company of Peachpit Press) releases a sample chapter of a book on its Web site. It apparently has done that with a chapter of my Excel 2007 Visual QuickProject Guide.

You can find the sample chapter at http://www.informit.com/articles/article.asp?p=696192&rl=1.

Don’t expect fancy formatting — the site uses plain old HTML-based code to present the material, with lots of clicks to get from one page to the next.

If you’re interested in seeing a sample chapter the way it looks in the book, download the Excerpt from Chapter 3, which is available on the book’s companion Web site, http://www.marialanger.com/excelquickproject/

For perfectionists.

One of the things that always bothered me about Excel was the way it handled right alignment in cells with certain types of number formatting applied.

The Problem

For example, in the following illustration you’ll see that some cells containing values have currency formatting and other cells containing values have comma formatting applied. The line up nicely in a column. But when right-align the column headings as I’ve done here, the headings don’t line up with the numbers:

This is the case in all recent versions of Excel that I’ve worked with, including Excel 2004 for Macintosh and Excel 2007 and Excel 2003 for Windows. (The illustrations here are from Excel 2007 running on Windows Vista.)

Why It Happens

Before we can fix this problem, we should have a good understanding of why it occurs.

You may have noticed that when you apply currency or comma formatting (for example) the numbers shift to the left. Ever wonder why?

Well, you can thank Excel for thinking ahead about something that you might not have in a column of numbers: negative values. As shown here, certain number formatting options place parentheses around negative numbers. To make sure all the numbers in a column line up, positive numbers are shifted by the width of a parentheses character to the left. So every number formatting with currency or comma formatting actually has a tiny bit of space after it.

The Solution

Since you don’t normally apply currency or comma formatting to text, cells containing text are truly aligned against the right side of the cell when you use the right alignment formatting option.

But guess what? You can apply currency or comma formatting to a cell containing text. And when you do, the contents of the cell will shift to the left by the width of one parentheses character. The result: everything is nicely aligned: