Excel Tip: Top 20 Productivity Points
AccountingWEB's latest Excel guru Chris Bales has put together his Top 20 Excel Productivity Points - a must for all Excel users.
Useful tips include advice on hardware, software, worksheets, and troubleshooting. Print out this essential guide and you can make the most of Microsoft's popular spreadsheet tool.
Frequently overlooked, hardware setup can make a big difference, but the power of the computer is not that important for Excel in everyday use. A Pentium machine that will run the software is generally all that is needed unless you are going to get into detailed work with large files or with a large graphic content. Excel 2000 will run quite satisfactorily on a P100 for everyday use and Excel 97 will run satisfactorily on a 486. For practical purposes, 16 MB RAM is preferred but 8 MB RAM will suffice. Points that are relevant about the hardware are -
The size of the monitor does make a difference - 17" or above preferred. Operating system Windows 95 version 4.00.950a or above should allow you to run the screen resolution settings icon on the taskbar to enable easy monitor resolution changes at will. A 17" monitor at 1024 x 768 resolution will show you more than 90% of the total width of a landscape page onscreen. Larger monitors also allow more buttons to be included in the individual toolbars (see below). Anyone using a small screen portable for working on large spreadsheets would gain significant benefit from plugging in a 17" monitor when working in an office and they are no longer expensive.
A good pointing device does make a difference. A Microsoft Intellimouse or equivalent wheelmouse type device makes for easier and faster navigation within the page, enabling diagonal movements as well as avoiding the need to resort to the navigation keys and the scroll bars. We now regard this type of pointing device as essential for anyone using spreadsheets frequently for long periods.
Customize the toolbars to include the features that you use regularly. The various merge and unmerge buttons are a great help if you merge cells frequently. We run the drawing toolbar at the foot of the screen all the time, including buttons for several different styles and densities of "one click" cell treatment for presentation purposes. For those with smaller monitors, running the drawing toolbar onscreen means that you can delete the fill color and font color buttons from the formatting toolbar to free up some space for other buttons. To add the buttons, follow - View, Toolbars, Customize, Commands tab, find the button you want and drag it into the relevant toolbar where you want it to be positioned. To remove a button, right click the button, click on customize in the drop down menu, find the same button in the lists shown and drag the button from your toolbar back to the original in the customize menu.
For regular use spreadsheets, load a shortcut into favorites, onto your Windows desktop, quick launch taskbar area, or Microsoft Office shortcut bar if you use it. The latter two are very helpful as either is a single click launch direct into the specific spreadsheet. To set up either of the last two, set up the shortcut on your Windows desktop and just drag the shortcut icon to the desired position. To restrict access, set up an "open file password" for the workbook and the password request box will appear onscreen for completion before the file will open. If you have several such spreadsheets, you can set up a new toolbar in the Microsoft Office Shortcut Bar, and load the shortcuts into that new bar.
Using Workbooks and Worksheets
To name a worksheet in a workbook, click on the relevant sheet tab at the foot of the window to open up the relevant sheet, double click on the worksheet tab to highlight it in reverse video and overtype with the desired sheet name.
Standard Excel page navigation includes a useful shortcut facility for navigation to data entry points. Assuming data needs to be input for each entry in columns C, D and E on rows 5, 6 et seq., navigate to cell C5 using the navigation arrow keys or locate it by pointing with your mouse, enter in C5, tab to D5, tab to E5, then hit the enter key and cell C6 will automatically be selected as the first data entry point on the next row down.
There are usually at least two options for doing everything in Windows based software and Excel is no exception. While instructions tend to suggest you use the tools buttons on the toolbars, many of the standard commands are also available on the right mouse button which saves both time and hand movement. As the cut, copy and paste buttons are at the top of the right button menus, ensuring that the relevant cells are towards the top of the screen saves additional time as the menus then drop down whereas, from cells near the bottom of the screen, the menus roll up. A minor point maybe, but one that makes a lot of difference if you spend a lot of time working with spreadsheets.
Worksheet formatting and design
Plan a spreadsheet before you start to build it. Accountants are familiar with the concept of planning a job and time spent in planning a spreadsheet layout will more than likely save time in the long run. Start with the desired end result and draft out the layout and steps to get there on a sheet(s) of paper with a pencil. It's far easier to rub out pencil and modify a draft on paper than to back track through a half-built spreadsheet layout later to modify the design and sort out why the formulae do not still operate correctly.
When you've finished the layout and are satisfied that it all works correctly, save it in ".xlt" template format if you think that the layout might be useful again. Keep your draft layout notes and file them for future reference, not forgetting to write the Excel file name on the page(s).
Before you start to plan a layout, ask your colleagues if they have a suitable one already built - great time saver if they have! Do you have a central library of ready built layouts complete with an indexed file of supporting notes in your firm or office? If your firm is large enough to have an IT manager or IT librarian, this could come within their field. Notes can be scanned into computer files and shipped between offices together with format files as Email file attachments.
Format the whole page at one go by clicking on the tile at the top left intersection between the vertical row numbers and horizontal column alphabetical headers and making the basic page selections at one go (eg - font, font size, cell height, cell width, cell format, cell alignment, basic color scheme, text wrap and others).
Convert a number format cell into a text format cell by preceding the entry with an apostrophe instead of using the format cell routine - much quicker. Entry of say -loss in a cell formatted other than text will result in #NAME?, whereas '-loss will result in -loss.
It is frequently easier to achieve desired results in complicated calculations by using extra columns for intermediate calculations and then use the hide column routine on those intermediate columns so that they do not show when the final report is printed or viewed onscreen - eg useful when conditional formatting is used. The intermediate calculations can also be done in cells away from the section of the worksheet to be printed (use the "set print area" routine from the file menu) or on another page within the workbook.
Build formats in steps and prove that each works as you go. Put version control in your original plans so that you know where you are when recommencing work after a break. Well designed spreadsheets have built in proof checks - eg if column D is a subtotal of data entered into columns B & C, totals of columns B, C & D can be proof checked by a crosscast of the totals of columns B & C to ensure that the result is the same as the downcast total of column D and build in a warning message to display onscreen if it doesn't. Even better, format the warning message in a bright contrasting color so that it would be difficult to ignore.
There are differences between the way that numbers are shown by cells formatted as "number", "currency" and "accounting". The positioning in the cells differs between the formats. Also, a zero in a "number" formatted cell shows as 0 but is shown in a "currency" formatted cell as - which can very useful if you only want figures for non-zero balances to be displayed.
To quickly and easily convert a positive figure into a negative or vice versa, just multiply by -1. Eg -10*-1 will result as 10.
To quickly link a cell to another cell in the same worksheet, move to the destination cell, enter the equals sign, click on the relevant source cell and then hit the enter key. To link to another cell on a different worksheet, move to the destination cell, enter the equals sign, click on the relevant worksheet tab, click on the relevant source cell and then hit the enter key. To link to another cell in a different workbook, move to the destination cell, enter the equals sign, open the other workbook, click on the relevant worksheet tab, click on the relevant source cell and then hit the enter key.
To copy and paste a non-absolute formula from one cell to another without automatic change of the references for associated cells, navigate to the source cell, copy the formula from the display in the formula bar instead of from the cell itself, hit the keyboard tab key to exit that cell, navigate to the destination cell and paste into the cell direct or onto formula bar if preferred. Do not use "paste special" to paste direct into the destination cell. Paste into the formula bar if you are including the copied formula as part of a formula being constructed in the destination cell.
The different error messages in cells have different meanings and often point in the direction of what is wrong. It's all too easy just to return to the formula and spend time trying to work out what is wrong by looking at the formula bar, which may be fruitless as the error may be as a result of something in a different cell referred to by the formula. If you are not familiar with the meanings, print out the "error values in cells" explanation pages from the help menu and keep them somewhere where you can refer to them easily.
Charting (including graphs)
Always remember that the purpose of a chart or graph is to convey a message clearly and legibly and fancy designs can detract from that, so, unless you are setting out just to show off your skill, keep it simple!
Planning before you start is essential as it will help you decide which chart style variant to use because more complicated charts such as stacked sections analysis may well require data to be organized in specific ways.
Plan your chart color or shading schemes carefully as adjacent sections must contrast in order to avoid confusion and loss of impact. Color printing significantly improves the impact but select your color printer before you check the results in "Print View" as the display shown will be related to the currently selected printer. Remember that there are limitations in color reproduction when using tri-color inkjet cartridges - photographic cartridges will give you more accurate results.
Don't forget the cardinal rule of computer work - back up your work regularly. There is nothing more frustrating in computing than losing the lot because the system has gone down (and hard drives do fail). If it hasn't happened yet, it may well one day. Your work can be just as much at risk on a server hard drive as on a local machine.
© Chris Bales. This file is made available for posting on AccountingWEB and may be downloaded by members and visitors to the AccountingWEB site for internal use only. No unauthorized reproduction or redistribution in any form is permitted. Contact Chris Bales for authorization and training services.
BALES, Chartered Accountants
30 Goss Lane, Nailsea, Bristol BS48 2BD
Tel: 01275 854237 Fax: 01275 855058