Top 20 Excel productivity points now on-line

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.

Continued...

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments
AccountingWEB's picture

BACK UP WORK

AccountingWEB | | Permalink

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?

Automated backup

cbales | | Permalink

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.

AccountingWEB's picture

Today's Date

AccountingWEB | | Permalink

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.

AccountingWEB's picture

Get Excel to work for you

AccountingWEB | | Permalink

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.

shurst's picture

Scenario identification

shurst | | Permalink

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

AccountingWEB's picture

Scenario Name Footers

AccountingWEB | | Permalink

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?

Re: Scenario name footers

cbales | | Permalink

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.

AccountingWEB's picture

Use the view menu

AccountingWEB | | Permalink

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.

AccountingWEB's picture

Chart Formatting

AccountingWEB | | Permalink

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?

Chart formats - set up your own custom template

cbales | | Permalink

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.

proof checks

AnonymousUser | | Permalink

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

shurst's picture

More on figure formats

shurst | | Permalink

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.

Re Celia's comments

cbales | | Permalink

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.

AccountingWEB's picture

Re : your Point 14.

AccountingWEB | | Permalink

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.

AccountingWEB's picture

Excel Tips

AccountingWEB | | Permalink

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

Couldn't mention everything Tony

cbales | | Permalink

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")

AccountingWEB's picture

Re : your Point 6.

AccountingWEB | | Permalink

You forgot to mention that Move Selection After Entry>Direction Down needs to be set first.