Whether you’re using Microsoft Excel 2008 ($549.95 in Canada as part of Office 2008 for Mac – Standard Edition) or 2004, you need some tricks up your sleeve for easing day-to-day tasks, such as entering data or custom sorting, as well as for special situations, such as using colours not included on the Standard palette.
Here are my five favorite Excel tips for everyday use and beyond.
1. Customize sort orders
When it comes to sorting data, Excel provides several standard sort options for numbers, dates, text, and so on. But sometimes you need something beyond the built-in choices. For instance, you may prefer to organize your expenditures by season, with summer listed first, instead of the alphabetical fall, spring, summer, winter. You can do this by creating a custom sort list.
Enter the items in contiguous cells in the order you want, and select those cells. Choose Excel -> Preferences and go to Custom Lists; click on Import and then click on OK. (This is much quicker than creating a list from within the Custom List preference pane.) To use your custom sort order, select the column or area you want sorted and choose Data -> Sort. Click on Options in the Sort dialog box, and select your custom list from the pull-down menu.
If you’re using an Excel 2008 AutoFilter on the column (the double arrows next to the column header), you won’t find your custom sort included in its menu. However, once you’ve applied a custom sort to a column, selecting either the Sort Ascending or Sort Descending command in the AutoFilter menu does invoke the custom list.
See related stories
10 tips for creating an awesome PowerPoint presentation
15 great tools that optimize Microsoft Office
Why Microsoft Excel just won’t get it done anymore
2. Constrain keyboard cell selection
When you’re entering data, speed is everything. Say you’re typing information into a three-by-ten block of cells. Going from the last cell in one row or column to the first cell of the next usually requires selecting that cell with your mouse, or using several key presses to navigate to it.
Here’s a simple time-saver: Select the block of cells by dragging across them before you start entering data. Use the Tab key to move across a row or the Return key to move down a column; when you reach the edge of the block, press that key again to jump to the beginning of the next row or column. You can also use Shift-Tab and Shift-Return to move backward and jump to the previous row or column when you reach the edge of the selection.
3. Force a line break in a cell
Ever have a string of text spill out of one cell into the next? You already know that you can prevent that by formatting the cell for text wrapping (choose Format -> Cells, click on the Alignment tab, and check the Wrap Text box). But that doesn’t mean your text will break where you want it to: Goals for 1st Quarter might end up as Goals for 1st / Quarter instead of the preferable Goals for / 1st Quarter. The solution is simple: To force text to break at the spot you want–whether or not you’ve enabled text wrap–click where you want to split the line and press Control-Option-Return.
4. Replace standard colours
You can immediately add clarity or visual interest to a spreadsheet using coloured text or cells. Unfortunately, the Standard palette’s selection of garish colours leaves much to be desired. However, you can change its choices to ones that are more useable. And if you’re running Excel 2008, you can even put the more attractive and complementary Document Theme colours (available only for graphics) into the palette.
To use theme colours, open the Formatting Palette and expand the Document Theme panel. Find a theme with colours you like, and leave them showing in the panel. To alter a Standard palette colour, open Excel -> Preferences and click on colour; select any hue you want to replace (hot pink?), and click on Modify to open the colours palette. Choose a new shade from the colour wheel; or, if you want a theme colour, select the magnifying glass and click on the colour that’s showing in the Document Theme panel. Click on OK in the colours palette to put the new hue in the Standard palette.
Note that any colours you use from the Standard palette are “wired” to the palette: Modify a palette colour, and it changes throughout the worksheet wherever it’s been used. To import a custom palette from one document into another, open both the source and target documents. With the target document active, choose Excel -> Preferences and click on colour. In the Copy colours From menu, select the name of the document containing the palette you want to copy.
5. Create alternating row colours
If you want stripes of alternating colours in your worksheet, you’ll have to do it yourself. The AutoFormat feature for tables lets you add shaded lines, but if you delete a row or sort your table, you end up with a patchwork of shaded and plain cells. Excel 2008’s ledger sheet templates aren’t a solution either–they come in only green and white, affect the entire worksheet, and include built-in calculations.
The usual approach to colouring alternating rows that won’t explode when you move cells around is to use conditional formatting (which we covered years ago in Macworld Hints). This method uses the MOD function, which calculates whether a cell is in an even- or odd-numbered row and shades only the even rows. With this method, you can add or delete rows, and copy and paste cells, with impunity. There’s just one problem: Every other row ends up au naturel.
By using two-condition formatting, you can create tables with stripes of alternating colours. Select the area you want to format and choose Format -> Conditional Formatting. Select Formula Is from the pop-up menu and type =MOD(ROW(),2)=0 (this identifies all even-numbered rows). Click on the Format button; under the Patterns tab, select a colour for the background, and click on OK.
To create the second condition, click on the Add>> button in the Conditional Formatting dialog box. Repeat the above formatting procedure, but use =MOD(ROW(),2)=1 (to identify odd-numbered rows), and choose a contrasting pattern colour.
Since Excel’s built-in Standard palette doesn’t offer two shades of subtle, coordinating colours, use the previous tip to create colours that will work well together without overwhelming your table data.
Sharon Zardetto is a long-time Mac writer. Her latest ebook is Take Control of Safari 4.
Source: Macworld.com