Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Top 20 Excel productivity points now on-line

by
9th Mar 2005
Save content
Have you found this content useful? Use the button above to save it to your profile.

AccountingWEB's latest Excel guru Chris Bales has put together his Top 20 Excel Productivity Points - a must for all Excel users.

Workshop visitors will be well aware of the invaluable knowledge Chris Bales has to share with Excel users - as will users of his demonstration on charting using the software.

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.

Chris Bales' Top 20 Excel Productivity Points
(Plus a reminder at the end)

Hardware

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 -

1. 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 an A4 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 large UK High Street and mail order retailer is currently marketing a 17" monitor at 129GBpounds + VAT.

2. 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.

Software setup

3. Customise 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 colour and font colour buttons from the formatting toolbar to free up some space for other buttons. To add the buttons, follow - View, Toolbars, Customise, 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 customise 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 customise menu.

4. 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

5. 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.

6. 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.

7. There are usually at least two options for doing everything in Windows based software and Excel is no exception. Whilst 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

8. 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 draught 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).

9. 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.

10. 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 colour scheme, text wrap and others).

11. 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.

12. 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.

13. 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 inbuilt 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 colour so that it would be difficult to ignore.

14. 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.

Formulae

15. 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.

16. 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.

17. 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.

Formula troubleshooting

18. 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)

19. 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 organised in specific ways

20. Plan your chart colour or shading schemes carefully as adjacent sections must contrast in order to avoid confusion and loss of impact. Colour printing significantly improves the impact but select your colour 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 colour reproduction when using tri-colour inkjet cartridges - photographic cartridges will give you more accurate results.

Finally

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 unauthorised reproduction or redistribution in any form is permitted. Contact Chris Bales for authorisation and training services.

BALES, Chartered Accountants
30 Goss Lane, Nailsea, Bristol BS48 2BD
Tel: 01275 854237 Fax: 01275 855058
e-mail: [email protected]
http://www.bales.uk.com

Tags:

Replies (17)

Please login or register to join the discussion.

aw_logo_2019
By Accounting WEB
26th Mar 2002 18:57

BACK UP WORK
You did a great job, Chris. I was wondering, though, you mentioned backing up your work; however, you did not mention setting up auto-save or setting up for back-up option. Does EXCEL not have those options?

Thanks (0)
avatar
By cbales
26th Mar 2002 21:13

Automated backup
Thanks for your comments Lisa.

Provided the "Autosave" add-in has been loaded, Excel can be set-up to save automatically. To find out whether its allready loaded or not, look at the "Tools" menu in Excel to see if its listed. If not, it can be loaded from the original program CD or from a web download. To set it up, type "Autosave" into the help menu search box and read how to set it up.

As a matter of policy, I still suggest to users that they backup onto removable media as selections such as autosave have to be specifically set up and few users are aware of its existance or, if they are, can be bothered to sort it out. Backing up to the same hard drive has its limitations.

When I wrote this article we were also backing up daily onto a digital tape streamer. Since then we have moved on to backing up onto a duplicate removable hard drive unit and software is available to enable the backup to be done automatically in real time.

Thanks (0)
aw_logo_2019
By Accounting WEB
25th Mar 2000 22:11

Today's Date
For those who need to enter data in spreadsheets manually, hit ctrl ; to enter today's date. BTW this will work anywhere in MS Office.

Thanks (0)
aw_logo_2019
By Accounting WEB
25th Mar 2000 22:35

Get Excel to work for you
Why manually enter data in Excel at all? The data you're trying to analyse must exist in a database somewhere - accounting system, flatfile, Oracle, web page etc. Get Excel to fetch it with Data | Get External Data.

Thanks (0)
Simon Hurst
By Simon Hurst
21st Mar 2000 08:31

Scenario identification
You could just include the cell with the title that identifies the scenario as one of the changing cells, and then just enter appropriate text for each scenario...

Thanks (0)
aw_logo_2019
By Accounting WEB
17th Mar 2000 14:13

Scenario Name Footers
I like to use the Scenario Manager when doing sensitivity analyses, as I only need one workbook, and the client can easily toggle between the scenarios. However, I cannot work out how to identify on the printed output which scenario we are in. There does not appear to be a function for popping it into a cell, or (even better) the footer of the page. Are good old-fashioned print macros, typing the name in really the only way to identify the scenario?

Thanks (0)
avatar
By cbales
18th Mar 2000 10:21

Re: Scenario name footers
Jill and I have discussed this one and two solutions emerged to solve it for her -
1) Where results are shown on different worksheets, replace the sheet numbers with meaningful names (see 5 above). Then format the individual results worksheet footers (or headers if preferred) to include the worksheet name tab. To do this, select the worksheet then follow "view" from the menu bar, "header & footer", "custom footer" (or custom header), click on whichever of the three position options available and then select the "name tab" button from the options provided (far right button of the seven on Excel 97 and 2000). This will show the worksheet name at the foot (or head) of the page in "Print Preview" and on printouts.
2) Where results are shown on one worksheet and are dependent on which scenario is selected, use a conditional formula on the results worksheet to select the scenario title to show within the worksheet depending on an entry in a specified cell - eg: if cell A1 is the specified cell, the "if" function could be used showing results of "scenario A" if digit 1 is entered in cell A1, "scenario B" if digit 2 is entered in cell A1 etc. but do remember that you are limited to 7 "if's" in a nested formula. This method could also be used to select all the source information for each scenario, just based on a single keyed in selection entry. If there are more than 7 different scenarios, consider using the "choose" function instead to increase the choice from 7 to 29 maximum.

Thanks (0)
aw_logo_2019
By Accounting WEB
15th Mar 2000 10:52

Use the view menu
As an IT trainer I was surprised to find that accountants are not always familiar with the Group and Outline facilities (under the data menu) and the full power of Custom views (under view) Using these properly can save a huge amount of time and are well worth getting to grips with.

Thanks (0)
aw_logo_2019
By Accounting WEB
15th Mar 2000 16:36

Chart Formatting
I find that the past format button on the excel toolbar is an invaluable tool for my every day work. Does Chris, or any one else know if the same facility is available to copy chart formats from one chart to another, without having to adjust all the default colours etc mauually?

Thanks (0)
avatar
By cbales
15th Mar 2000 21:33

Chart formats - set up your own custom template
Gill
Graphing in Microsoft Word, Excel and Powerpoint is done by Microsoft Graph, which is a separate application. Anyone can set up their own user defined custom chart types as templates, specifying colours, layout etc.
To find out more about this, open up a blank page in Excel then, from the menu line, select Insert, Objects and page down in the "Create new" box to Microsoft Graph. Double click on the heading to open the application and click on Help, Contents & Index, enter "Chart types", "customizing" (American spelling!) and follow "Save a custom chart to apply to other charts".
Hope this helps.

Thanks (0)
avatar
By AnonymousUser
14th Mar 2000 17:20

proof checks
Chris's point 13 about proof checks is good - but they work best if they zero out - ie if zero means they are ok and any amounts mean errors. for example if you deduct the "total row" total from the "total column" total the result will be zero if it casts and cross casts

Thanks (0)
Simon Hurst
By Simon Hurst
14th Mar 2000 08:32

More on figure formats
You can also allow for the width of the right hand bracket in custom number formats to make sure all the figures line up properly eg:

#,##0_);(#,##0);-

The underscore leaves a space equal to the width of the character that follows it.

Thanks (0)
avatar
By cbales
12th Mar 2000 19:57

Re Celia's comments
Point 6 -
As I have described is the standard setup for both Excel 97 and Excel 2000 and, from memory for earlier versions (but its around two years since I last worked on versions earlier than Excel 97). Unless software settings have been altered, no changes should be needed for this facility to work.

Point 14 -
OOPS! It's ACCOUNTING cell formatting that automatically produces a - for 0 (or 0.00) NOT currency for both Excel 97 and 2000. Celia's comment prompted me to read the paragraph again. Whilst there are other ways of achieving the desired result, for most people the easiest and quickest route is to make use of standard settings available.

Thanks (0)
aw_logo_2019
By Accounting WEB
12th Mar 2000 06:37

Re : your Point 14.
A zero in a “number” formatted cell will only show as 0 if the default format (which is “0”) or formats such as #,##0;(#,##0) are used.

The cell does NOT have to be formatted as “currency” to avoid the 0.

The “number” formatted cell will show nothing when a zero is entered if the cell is formatted as 0;(0);”” or as #,##0;(#,##0);””.

If a dash is required instead of nothing, the formats would be :
0;(0);”-“ or #,##0;(#,##0);”-”.

The above applies also to decimal formats.

Thanks (0)
aw_logo_2019
By Accounting WEB
10th Mar 2000 16:00

Excel Tips
I'm surprised that Chris did not mention keyboard shortcuts in his top 20 tips.

For me using these is the quickest way of creating and manipulating spreadsheets, whether it's simply copying (ctrl v) and pasting (ctrl v) or sorting data (Alt d s)etc.

If you look at the menu items you will invariably see either 'ctrl x' (where x is a letter associated with the item). Press this combination and you perform that function.

Alternatively you will see menu items that have a letter underscored. Press the Alt key with these letters and you will open that menu. Find the function or submenu that you want use and press the relevant letter to perform the function that you want and so on.

Once you get used to using the keyboard I promise you that you will not go back to using a mouse!

Tony

Thanks (0)
avatar
By cbales
10th Mar 2000 16:50

Couldn't mention everything Tony
Those who want to learn the shortcut keys can find the details by looking up Contents and Index in the "Help" drop down menu. Type in "shortcut keys" and select "use shortcut keys in Microsoft Excel". There is a submenu of around 13 different categories to view and print out.
A modern programable multifunction mouse (especially cordless version) is as far from an ordinary basic mouse as a Rolls Royce is from a Mini and anyone who has not tried one just won't believe the difference it makes.
Whether you use the mouse or the keyboard is a matter of preference and everyone should find out what suits them best.
Could't mention everything Tony in a list limited to twenty.
(ps - the keyboard copy command is "ctrl c")

Thanks (0)
aw_logo_2019
By Accounting WEB
12th Mar 2000 05:46

Re : your Point 6.
You forgot to mention that Move Selection After Entry>Direction Down needs to be set first.

Thanks (0)