• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint



The Annoyance:

The cell text in Excel X looks odd-kind of jaggy-not smooth like other applications. It's like this on both my Macs. My friend has the new Excel 2004, and to my surprise, the text in his version Excel looks smooth. Do I need to upgrade to Excel 2004 in order to get text that's easier on the eyes?

The Fix:

No, you need only to turn on font smoothing. Both versions of Excel have an odd "feature" that lets you set font smoothing separately from that of Mac OS X's System Preferences. (Why Excel needs it own smoothing control is known only to Microsoft.) The feature is nearly identical in the two versions of Excel, except that in Excel X, font smoothing is turned off by default (smoothing is turned on by default in Excel 2004).

To set smoothing in either version of Excel, go to Excel's preferences (Excel → Preferences, or -,) and click View. To turn on font smoothing, click on the checkbox next to "Enable Quartz text smoothing," and you're all set.


The Annoyance:

Doh! I just realized that this monster of a spreadsheet I created would work better if the rows were columns and the columns were rows. Copying and pasting each individual cell would waste a whole afternoon. Is there a cure for row-and-column dyslexia?

The Fix:

Indeed there is-a slick feature called Transpose. This is my favorite feature of Excel, one that has been around for a long time. Transpose is easy to use-it's just a slight variation on copy and paste. The top of Figure 4-14 shows a spreadsheet stretching out to the right, off of your screen, and into the wild blue yonder-a good candidate for transposing. The 12 months serve as column titles along the top, and city names appear in the first cell of the rows on the left. By reversing the months and cities, you can fit the entire table on your screen.

Here's how to transpose these cells:

  1. Select the cells that you want to transpose.

  2. Copy the table (-C).

  3. Click on the cell to designate it as the top-left cell of the new table.

  4. Select Paste Special from the contextual menu (Control-click or right-click a cell).

  5. Select Transpose in the Paste Special dialog.

  6. Click OK.

Figure 4-14. The original table (top) runs off the screen to the right. After transposing the rows and column with Paste Special, the table fits in the space (bottom).


The Annoyance:

I hate it when Excel second-guesses me. For instance, I have a spreadsheet that includes product codes that contain slashes (/) in one of the columns. Excel insists on converting the slashes to dates. I know that I can open the Format Cells dialog, click the Numbers tab, and set the cell to good old text, but this is an awful lot of rigamarole to go through. Is there a way to stop Excel from trying to read my mind?

The Fix:

There's no need to consult the Format Cells dialog just to change a bunch of numbers or fix some text formatting. You can use certain characters at the beginning of your text string to tell Excel how you want the cell to be formatted. When you hit Return (to go down) or Tab (to got to the cell to the right), the special character disappears, leaving your text or number exactly as you typed it. Here are the more common formatting characters you can use to tell Excel who's boss:

  • Need to format something as text? Insert an apostrophe ('). When you use an apostrophe at the beginning of your text string, Excel won't convert it to a number format, even if the number string contains slashes. When you hit Return (or Tab), the apostrophe disappears, and what you type is what you get.

  • Need to format something as a fraction? Insert a zero (0) followed by a space ( ) before the fraction. Excel even has the ability to do math on fractions instead of decimals. You don't need to use a 0-space in an equation, however. When the first character in a cell is an equal sign (=), Excel won't try to convert the fractions to dates.


The Annoyance:

Like many people, I use Excel X to keep lists and tables. Sometimes I'll use the @ sign to signify a meeting location, such as "Bob @ office." Of course, Excel X automatically converts this to an email link, just as it converted my product numbers with slashes into dates (see the previous annoyance Reformat Numbers and Text). This is quite annoying, because every time I click on the cell, my email program launches with a blank email message window. A similar thing happens if I type in an actual web URL beginning with "http." I can't find any setting to turn this off or to convert the live link to plain text.

The Fix:

There isn't any way to turn off this "feature" of Excel X, but there are several ways to work around this annoyance.

  • Use an apostrophe before the text as you type it. As described previously, this tells Excel to format the cell as text. Unfortunately, you can't go back and add an apostrophe to get rid of the link.

  • Hit -Z to undo the link. You should use -Z immediately after Excel converts the text to a live link. If you start typing elsewhere, you'll have to keep hitting -Z and undo everything you typed after the link.


    When you create a new Excel spreadsheet, do you ever notice how it always pops up with three sheets (Sheet 1, Sheet 2, and Sheet 3)? While these are fine names for an empty spreadsheet, you might want to rename them to something more descriptive after you've filled in all the blanks.

    To rename a sheet, just double-click on one of the tabs and type in something more descriptive.

    If you don't need all three sheets in your Excel file, you can delete the ones you don't want by clicking on the unneeded sheet's tab and selecting Delete Sheet from the Edit menu.

    This makes it easier to find the sheet you need, and removing empty sheets also makes the Excel file a wee bit smaller in size.

  • Convert the link to text with the contextual menu. This is difficult to do, because clicking a cell with a hyperlink launches your email program or web browser. To avoid this, click the cell next to the hyperlink and use the arrow keys to navigate to the linked cell. Press the Control key and select Hyperlink from the contextual menu. In the dialog that appears, click the Remove Link button in the lower left.

Excel 2004 doesn't have this problem, as it takes a more practical approach. Instead of assuming that anything that looks like an Internet address needs to be a hyperlink, Excel assumes that it is just text. If you want a real hyperlink in Excel 2004, enclose the link in quotes and parentheses, and precede it with "=HYPERLINK" all in caps. For instance:


When you hit Return (or Tab), the web or email address you've entered becomes an active hyperlink in the Excel spreadsheet. When you click on that link, your default web browser or email program opens up.


The Annoyance:

I need to type the date into quite a few cells in many different spreadsheets several times a day, every single day. Is there any way to automate this tedious task? Can I create an AppleScript or some kind of macro to do this for me?

The Fix:

There's no need to resort to programming AppleScripts. Excel has a built-in key command to enter the current date in the selected cell: Control-; (semicolon). Add the Shift key (Control-Shift-;), and Excel inserts the current time.

Excel gets the current date and time from Mac OS X, so your Mac needs to be set to the correct date and time in the Date & Time preference panel (System Preferences → Date & Time).


The Annoyance:

Excel's split pane feature lets me keep the row and column headings visible while scrolling the main table. Unfortunately, the scroll bar at the top can get out of whack, moving the column heads out of view (as in the top of Figure 4-15).


Remember our discussion about discontinuous selecting text in Word? You can do the same in Excel. While holding the key, you can select cells, rows, and columns that aren't next each other. You can then apply a formatting change on all of the selections at the same time.

The Fix:

Split panes have been in Excel for so long that they're almost a historical feature. More recently, Microsoft added the Freeze Panes (called "Freeze Pane" in Excel 2004) menu command that accomplishes the same goal, but much more elegantly.

Figure 4-15. When you use the split panes feature, scrolling in the top pane can easily get out of whack.

You can see in Figure 4-16 that the Freeze Panes feature doesn't let you scroll the column headings. Instead, Freeze Panes locks the column heads (and rows) in place, while still letting you scroll through the contents of the main table. To use Freeze Panes, make sure the spreadsheet is in Normal view and select Window → Freeze Panes. To unfreeze the panes, click on a cell that will be the top left cell of the main table, and choose Window → Unfreeze Panes from the menu bar.

Figure 4-16. The cleaner Freeze Pane keeps the column headings fixed.


The Annoyance:

It's such a pain to get data and charts from an Excel spreadsheet into my Keynote presentations. I like Keynote, but I'm afraid I'll need to go back to PowerPoint because of this.

The Fix:

Let's not be hasty, now. While there's no denying that the integration between Excel and PowerPoint is much better than between Excel and Keynote, there's no need to dump Keynote. Instead, you can use a handy little utility called XL2K, a free download from http://homepage.mac.com/imaxinc/index.html. XL2K moves Excel tables into standard Keynote tables, and moves charts from Excel into standard Keynote Charts. Just select the data, launch XL2K, and choose Table, Chart, or Both. XL2K also lets you choose the chart type. Seconds later, you're looking at a Keynote slide with your Excel numbers and charts.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint