Excel: Favorite Tips and Your Questions Answered, with Gail Perry
Session Moderator: I would like to welcome Gail Perry; Gail is a CPA, tax specialist, author, speaker and instructor. Her column, "Fun With Taxes," 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," 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." Gail 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.
With that all that said, the floor is yours Gail.
Gail Perry: Thanks for the intro, Ryan. What Ryan didn't mention is that I am also the managing editor here at AccountingWEB, and I write the Friday Excel Tips that you get in your weekly newswire.
Thank you all for coming!
Today I've prepared 20 quick Excel tips that should provide you with shortcuts to make your work with Excel easier. If you have any questions as we go, please ask, or if you have had any problems with Excel, please share them!
Here's my first quick tip:
1. To close all open files, but leave Excel open - press Shift, then click the File menu. The "Close" command has changed to "Close All."
When you use this feature, you will be asked if you want to save each file, one at a time, so you won't lose any data. But after everything is closed, Excel will still be open, so you can continue working.
2. Pick up where you left off with Save Workspace command. All open files will be saved, as well as your cellpointer location - and all will be opened when you open the workspace.
This is a great tip if you want to save the layout of your screen when you're in the middle of working on something, but it's time to leave for the day. (Of course those of us who work at home really never leave for the day...)
Arlen Pecka: This is true!
Gail Perry: Another benefit of saving the workspace is if you want to set up several open files for another worker, you can simply have the other worker open the workspace and everything will be right where you want it. The workspace gets a separate file name, and you will see it when you select File, Open.
Arlen Pecka: When you then work in the workspace does that make the changes to the other files or just the workspace file?
Gail Perry: All the files are saved separately, the workspace just saves the file names. So all your changes will be saved in each individual file.
Arlen Pecka: Got it.
Daniel Anderson: In using the "save workspace", is that feature limited to one workspace, or can you have others as well?
Gail Perry: You can save as many workspaces as you like, and they can overlap in terms of the files contained in each workspace
Daniel Anderson: How do you retrieve the workspace?
Gail Perry: When you click File, Open, the workspace will appear along with the rest of your Excel filenames. It has a different extension (.xlw instead of .xls), but it is in the list with all the file names. The workspace remembers which files you had open and opens them all again, it remembers which sheet you were on and brings that sheet to the top of the screen, and it remembers where your cellpointer was and places it there again
3. Enter info across columns instead of down rows by pressing Tab instead of Enter after each entry. The Tab key takes you sideways across your worksheet.
And Shift+Tab takes you sideways, back to the left
Here's a quick way to get rid of files you don't need any more:
4. Delete a file by clicking the File menu and choosing Open. Right-click on the name of the file you wish to delete, and then choose Delete from the pop-up menu. You can also rename a file in this manner by choosing Rename from the pop-up menu. Click Cancel when you are finished, if you don't want to open a file.
This saves you from having to go to the Windows Explorer, or My Computer to take care of file management. You can also create new folders in the File Open window, and copy and move files from one folder to another. Also, you'll get a warning before you delete a file, so you don't have to worry (too much) about making a mistake here.
Here's a timesaver for data entry:
5. Enter data quickly into a block of cells by first selecting the block, then when you press the Enter key, your cellpointer stays within the selected block, snaking from one column to the next. You can work quickly from the number pad, without having to use the arrow keys to move from one column to the next.
This is particularly useful for people who are nimble with the number pad. The only drawback to this technique is that if you make a mistake and use your arrow key to get to another cell, the selected block is no longer selected.
Any questions so far?
Here's a tip for former Lotus users who remember the command to make global changes to a spreadsheet:
6. Make global changes to the entire worksheet by clicking the "Select All" box in the upper left corner of the worksheet - right above the row #1 and to the left of the column letter A. Any change you make when the entire worksheet is selected affects every cell in the worksheet. For example, you can set a default number format, change the font, change row height or column width, and so on.
The global change affects every cell in the worksheet. But not other worksheets, just the one you have open.
And here's another pointer for those of you who used to use Lotus:
7. Have you ever used the Lotus 1-2-3 help? If you're a former Lotus user, this is priceless. Choose Help, Lotus 1-2-3 Help, and the Lotus menus appear on the left side of the window. Double-click on a menu choice to go down a level in the menus. Keep going until you see the steps displayed in the middle of the window. These are the Excel steps that will help you execute the task. Here's a great tip: Press Enter, and the window will close, but a little yellow box will appear on your screen, displaying the steps, so you can follow along while you perform each of these steps. Pressing ESC will remove the box from your screen.
When I made the switch from Lotus to Excel, there were so many times I heard myself say "I know how to do this in Lotus, but how does it work in Excel?" This little help feature, especially with the yellow window that stays on your screen, gives you all the steps to help you make the transition. And I might add that there are some things that seemed quite simple and quick in Lotus, that aren't obvious at all in Excel.
If you're a keyboard user and prefer to type over using the mouse, here's a tip for selecting cells from the keyboard:
8. Keyboard selection - you can select cells from the keyboard, without using the mouse, by holding down the Shift key, then using the arrow keys to highlight a section of cells. Other keyboard shortcuts: Ctrl+Home to get you to cell A1, Alt+Page Down to move across columns, Alt+Page Up to move backwards across columns. And my favorite: End+Home to get to the last active cell in the worksheet.
If you've selected cells from the keyboard, and no longer want those cells selected, you can simply use your arrow to move the cellpointer off the selection.
Christa Janke: Another shortcut --Ctrl+Shift+8 highlights a contiguous range to the cell where your pointer is
Gail Perry: I like that!
Christa Janke: It's great when recording Macros
Gail Perry: All cells containing data will be selected. Assuming your cellpointer is in one of those
cells. Very nice!
Here's a variation on the Ctrl+Shift feature. You can fill a selected range with the same data by entering the data in one cell and pressing Ctrl+Shift+Enter
Do you find yourself constantly changing the folder name when you want to open or save an Excel file? The default location for saving Excel files is "My Documents" but you can change this to
whatever folder location you like.
9. Change default location where files are stored: Tools, Options, General - in the Default file location area change to a different folder.
Excel 2000 users: Do you like the new menus that show the frequently used menu choices at the top of the list, and you have to wait a few seconds for the bottom of the menu to appear?
Arlen Pecka: No.
Gail Perry: I don't like this feature either - my next tip tells you how to remove it.
Christa Janke: Depends if I want to use something I don't normally use.
Gail Perry: 10. You can turn back time and go to the traditional Excel menus by choosing Tools, Customize, and then clicking the Options tab. Uncheck the box that says, "Menus show recently used commands first."
This will apply to all future Excel sessions
11. Here's a quick shortcut for inserting a row or column: click on the column (or row number) where you want to insert. For example, if you want to insert a blank column between columns B and C, click on the letter C. Make sure you click right on the letter, to select the entire column. Then, hold down Ctrl and press the plus sign. Be sure to either use the plus sign out on your number pad, or hold down the Shift key as well if you want to use the plus sign that appears above the keyboard letters. Likewise, to get rid of a column (or row), click on the column letter (or row number), then hold down Ctrl and press the minus sign. Remember, if you delete a column or row by mistake, click the Undo button (or press Ctrl+Z) to go back one step.
You can use this technique for inserting partial rows or columns within spreadsheet material as well. Just select the area where you want to see blank spaces, then press Ctrl and the plus sign - you will be asked if you want to insert and move the other material aside, and just click Yes
Here are some shortcuts for entering formulas - these may be familiar to you...
12. When creating formulas, you don't have to type cell references. You can use the mouse to click on a cell, or you can use the arrow keys to move your cellpointer over to a cell. For example, to create the formula: =a5+a6 using your mouse as a pointer, enter the "=" sign (or click the "=" sign in the formula bar), then click on A5, enter the plus sign, click on A6, then press Enter (or click the green check mark in the formula bar). From the keyboard, enter the = sign, then arrow over to A5, enter the + sign, arrow over to A6, then press Enter.
Deborah Yanez: How do you get the formulas to continue adding the new rows that have been inserted?
Gail Perry: If you insert a new row in between the first and last row mentioned in a formula (for example if your formula is =sum(a1:A10) and you insert a row at row 8), the formula will be automatically revised (in this case it would change to =sum(a1:a11). If you insert a row at the beginning or end of a range, I think you have to rewrite the formula - I don't know of another way
Gail Perry: So if you inserted a row at the bottom of the list, you would have to redo the formula
Deborah Yanez: I'll try it that way. Thanks.
Arlen Pecka: Couldn't you select the cells and do a fill down?
Gail Perry: Yes, there are a few different ways you can change the formula.
13. Looking for a double underline? You can find it with the font tools. Click in a cell that should be double-underlined, then choose Format, Cells. Click the Font tab, then click the down arrow in the Underline area and choose a double-underline. The regular double underline hugs the cell contents and stretches only the width of the information you are underlining. The Double Accounting underline provides a bit of a gap between the cell contents and the underline, and stretches nearly the width of the cell.
I've added a button to my toolbar for the double underline, since I use it all the time. Do you all know how to add buttons to your toolbar?
Daniel Anderson: No.
Gail Perry: To add a button to your toolbar, click Tools, Customize, then click the Commands tab in the Customize window. Find the button you want (you can shop through several categories), then just drag the button right up to your toolbar. Close the Customize window and the button will be there forevermore.
Arlen Pecka: How can you get rid of it?
Gail Perry: Good question! To remove a button that you no longer want, click Tools, Customize. When the Customize window is open, drag an unwanted button from the toolbar down into your spreadsheet and just let go of the mouse button. The toolbar button will disappear from the toolbar.
14. Have you ever used the Merge & Center (or Center Over Columns, if you have an earlier version of Excel) feature to center a heading over your spreadsheet? It's a pain to turn off this feature if you change your mind. To get rid of the centering on your heading, click on the heading, then choose Format, Cells, and click the Alignment tab. Uncheck the Merge Cells box, and your heading will go back to the way it appeared before you centered it.
Especially in earlier versions of Excel, that center over columns feature wreaked havoc with other formatting on the spreadsheet.
15. You probably all know this one - to expand or shrink the width of a column, drag the small vertical line that separates the column letters. To get the best fit for the column width, double-click right on that line. To change the width of several columns at once, so either they'll all be the same width, or they'll all get the best fit, select the columns first. Don't select by dragging over cells in the columns, instead, select the columns by dragging over the column letters at the top. When all columns have been selected, you can change the width of one selected column and all the columns will follow along, getting the exact same width. Double-click on the column separator to give one of the selected columns a best fit, and all the rest of the selected columns will get the best fit as well. The best fit will vary for each column, depending on the contents of the columns.
16. Here's a quick way to hide information on a worksheet without having to actually hide entire columns or rows - select the information and change the color of the text to white!
The information will still appear in the formula bar if you click on the cell, but unless someone knows the cell contains data, it will look like the cell is blank.
Christa Janke: You can also add a custom format to the Number formats of 3 semi colons ;;;...this will hide anything in the cells you apply this format to.
Gail Perry: oooh! How neat! I'll have to try that!
17. New number formatting techniques in Excel 2000 (maybe in Excel 97, I can't remember): built-in formatting for zip codes, telephone numbers, and social security numbers. Select the cells you want to use for this type of data entry, then choose Format, Cells, Number. In the category list, select Special, then you can pick from the options at the right. When you assign this type of number format to a cell, you just have to enter the numbers, Excel will take care of entering dashes or parentheses in the correct spots.
These are a great timesaver if you use Excel to create employee lists or address lists.
If you use Excel for creating charts, this next tip might come in handy:
18. Change the default style of chart, so that if, for example, you always like to produce a line chart, that is the chart that will come up automatically each time you create a chart. That will save you the steps of going through the Chart Wizard. To set a default chart, first design a chart to appear the way you like it. Choose the chart style, select colors, background, grid display, etc. Then select the chart (click once on it so little handles appear on the sides), and go to the Chart menu (the Chart menu only appears on the menu bar when a chart is selected). Choose Chart Type from the Chart menu, and you will see a "Set as default chart" button in the window that appears. Click this button to apply your settings to the default chart style.
From this point forward, you can highlight data for a chart, click the Chart button on the toolbar, and click Finish in the Chart Wizard window, bypassing all the design steps.
Deborah Yanez: Can tabs be set up on the tool bar to set up single or double borders? We do a lot of number columns and we like the underlines to be the same size on each column, no mater how many numbers are in the cell.
Gail Perry: Yes - you can create border buttons - if you use the technique described above, you will see there are several border options in the Customize window, you can drag one of these buttons to the toolbar. There are double borders included here What isn't here are thick borders or colored borders. But you could create a macro for that, and add the macro to a toolbar button
Here's another tip for those of you who prefer using the keyboard to the mouse:
19. Use your keyboard to move around in dialog boxes. The Tab key moves you from one field to the next (Shift-Tab takes you backwards a field); Ctrl Page Up and Ctrl Page Down move you across the tabs that appear at the top of a dialog box. Pressing Enter is like clicking the OK button; pressing ESC is like clicking the Cancel button.
And here's the last tip today:
20. Get around quickly in a worksheet or workbook by assigning range names to cells or areas of the file. You can assign as many names as you like. All names must be unique, so you can't have the same name appearing on multiple sheets in the same workbook. To assign a name, click on the cell or range that you want to assign, then click in the Name box (left side of formula bar), and enter the name. To go to a named range, click the down-arrow in the name box and click on the name for the cell you want to go to.
Does anyone have any questions before we wrap up?
Arlen Pecka: What would you recommend as a comprehensive book on Excel and Access?
Gail Perry: My favorite Excel resource is "The Excel Bible" written by John Walkenbach.
Arlen Pecka: Thank You.
Gail Perry: He also wrote a book called "Excel Power Programming with VBA" which is exceptional if you work with macros in Excel
I really want to thank you all for coming! If you have Excel questions in the future, feel free to submit them to our Q&A forum.
Q&A is located at: http://www.accountingweb.com/help/anyanswers.html
Arlen Pecka: Thank you, Gail.
Session Moderator: We would like to thank Gail and all of you for joining us today!
Gail Perry: My pleasure - thanks again for coming, everyone!
Workshop sponsored by NetLedger: