More Excel Tips!
Presented by: Gail Perry, CPA,
Managing Editor of AccountingWEB and author of Excel 2000 Answers!
January 12, 2001
Visit the AccountingWEB Workshop Calendar for upcoming workshop sessions.
Here are some of the quick tips discussed in Gail's Excel workshop on January 12, 2001. Look for more of these tips in the full transcript of the workshop. You can view a complete library of AccountingWEB's Excel tips at: AccountingWEB's ExcelZone
Quick Movement in Excel
Move quickly through blocks of cells on your spreadsheet without having to use the mouse, nor use the End+Arrow key combinations. Simply hold down the Ctrl key while you press right, left, up, and down arrow keys. It has the same effect as pressing End plus the arrow key, but cuts your time in half.
Another Quick Movement Technique
Using the mouse, you can move to the end of a contiguous block of cells by double-clicking on the cell edge.
For example, if you have filled in cells A1 through E5, and the cellpointer is in cell A1, double-clicking on the right side of the cell border at cell A1 will take you to cell E1 (the right side of the block of filled-in cells). At cell E1, double-clicking on the bottom of the active cell border will take you to cell E5. Now double-click on the left side of the active cell border and you're at cell A5. Double-click on the top of the active cell border and you're back to cell A1. This is a nice way to get to the last cell in a large block when you're not sure how far you would have to scroll down.
Taking Cell Movement One Step Further
If you hold down the Shift key while using the technique described either of the previous tips, you will select the cells. In the above example, start at cell A1, hold down Shift, and double-click on the right side of the cell. You will have selected from cell A1 to cell A5. Double click on the bottom of cell A5 and you will have selected the entire block of filled-in cells.
Stop Display of Zero Values
You can remove the display of zero values in a workbook by choosing Tools, Options, clicking the View tab, and unchecking the box for Zero values. Depending on the number format you have chosen for your cells, the zero value cells may appear blank, or may appear with a dash.
Quickly Move Among Various Sheets In Your Workbook
If you use a lot of sheets in a workbook, so many that the sheet tabs don't all appear at the bottom of the screen, here's a quick way to move from one sheet to the next.
Assign a name to cell A1 of each sheet in the workbook - ideally, make the cell name the same as the sheet name. Then, to move quickly to a sheet, click the drop-down arrow in the Name box at the top of the screen (just above the Column letter A), and the entire list of sheet names will appear. Click the name of the sheet you want to go to.
Moving From Chart To Chart
If you place several charts in your worksheet and find you tire of scrolling from one chart to the next or trying to find the chart you want, consider naming a cell in the worksheet that is adjacent to the location of each chart. Then you can use the Name Box to select the name of the chart you want to go to.
Font Size In Charts
Have you ever created a chart and found that the size of the category names is very large in proportion to the size of the chart information? You can easily change the font size in a single area of the chart (the X-axis titles, for example), by clicking once on the area and selecting a reduced font size from the formatting toolbar.
To change the font size of all text in the chart, click once on the background area of the chart to select the entire chart, then change the font size on the formatting toolbar.
Session sponsored by National Payment Corporation
Gail A. Perry
Managing Editor, AccountingWEB
Gail Perry is a CPA, tax specialist, author, speaker and instructor. Her weekly newspaper column, "Fun With Taxes," which appears in the Indianapolis Star, provides readers with practical tax insights and advice presented in plain English with a touch of humor.
Perry is a former senior tax consultant with the international firm of Deloitte and Touche, where she provided tax planning services and advice to individuals and small businesses. An accomplished free-lance writer, Perry is the author of over a dozen books, including "The Complete Idiot's Guide to Doing Your Income Taxes," "Using QuickBooks," "Excel 2000 Answers!" and "TurboTax for Dummies." In addition, she has written several computer training books and is co-author of a book for trainers, "The Computer Trainer's Personal Training Guide."
Perry is a regular guest on America Online's Money Whiz Q&A program, and she has been featured on Fox television's "Good Morning New York," Bloomberg Business News's "Bloomberg Forum," WOR Radio's syndicated program, "Manage Your Money with the Dolans," as well as dozens of radio shows across the country.