Excel "best practices" ..design, standards, practices, auditing, controls, documentation, support

[Brian.Crawford]

I am trying to assist a client in introducing some standardization and organization to their end users in the development and use of their Excel Spreadsheets. This would include design, auditing, controls, documentation, supporting, etc. The users would be considered "average" (some "above"), macros are used minimally and VBA directly ..not at all (except for work I've done). They have about 250,000 WBs today, most fundamentally financial in nature.
I want to be able to advise them (a freebie) on what is available in the way of "best practices"; tools (ex software) such as for control & auditing, design technigues (to improve transference & supportability), design & development ...educational material, etc.

I have located a few web sites and associated material but would really like some feedback from experienced users and developers on what some of the best products and/or services to consider might be. This is something the client would "prefer" to implement on their own but could be flexible.

Appreciate any suggestions
Brian

 

[shawnhet]

Ozgrid has some good suggestions vis a vis Excel usage on the link below. I am not sure exactly what you are looking for beyond something like this though.Excel Best Practices. Efficient Excel Spreadsheet Designs in Spreadsheets

Cheers,

 

[Brian.Crawford]

Thanks, I'll take a look at that.
I guess I might describe it a "tool set" to better manage their Spreadsheet environment, probably "auditing" (insuring accuracy & consistancy) both at the design/development stage and methods to verify results later is there biggest concern.

Thanks
Brian

 

[obiron]

In terms of VBA make sure that maximum use is made of objects.

Don't do

dim myworksheet at string

myworksheet = "SummarySheet"

worksheet(MyWorksheet).activate

instead do

dim myWorksheet as worksheet

set myWorksheet = worksheets("SummarySheet")

myworksheet.activate

The advantage of the second method is that myWorksheet now inherits all the methods and properties of the worksheet object which will significantly shorten the code later.

When macros have been recorded remove all the unnecessary .Selects and .Scroll commands. you don't need to select a range to reference it or modify its contents.

.Range("A4").Select
.Range("A4").Font.Bold
.Range("A5").Select
.Range("A5").Font.Bold

can be reduced to

.Range("A4").Font.Bold
.Range("A5").Font.Bold

make sure when referencing ranges and cells that the full reference is used.

make use of named ranges where ever possible and manage the range on the worksheet_onChange() event

Turn off Screenupdating and use the Application.statusbar to keep users informed about what is happening

Make sure that all procedures have a proper on-error handler that will fail gracefully (e.g. turn on screen updating and restore the previous calculation state)

I'm sure I will be able to think of more.....

 

[xenou]

They have about 250,000 WBs today, most fundamentally financial in nature.
I want to be able to advise them (a freebie) on what is available in the way of "best practices"; tools (ex software) such as for control & auditing, design technigues (to improve transference & supportability), design & development ...educational material, etc.

IMHO teaching a company with hundreds of thousands of files how to use, design, control, audit, test, and support their Excel documents would be a full time job - you may be overly optimistic thinking you can give some free advice and call it a day.

 

[xenou]

This article seems similar to one I read several years ago (might actually be the same one). Sums up key items I would consider "best practices" for excel worksheets. Mostly to do with how to design a spreadsheet properly so that it's easy to use, understand, and maintain. There are probably a lot more topics to consider based on your post but I guess I already said that:

Best Practices:
http://homepages.wmich.edu/~balik/AFS06Balik.pdf
Also good tips, more concise:
Financial Modeling: Excel Best Practices | The Strategy Blog

 

[willdearman]

Hi Brian - I'm the guy that wrote the Excel Best Practices post Xenou linked to. This may or may not be applicable, but in financial modeling at least there are three general standards that I have listed below. One approach would be to put the best of each into a document for your client as their internal standard; I do agree that teaching and auditing that internal standard would be a more than full time job.

FAST Standard - The FAST Standard Organisation |  The FAST Standard
Operis (I don't see their standards document online)
SSRB - Standards | Spreadsheet Standards Review Board

* For a comparison, there is a great academic paper by Grossman and Özlük called "Spreadsheets Grow Up: Three Spreadsheet Engineering Methodologies for Large Financial Planning Models."

I'm interested to hear how it all turns out. Feel free to get in touch if I can off help will [at] thestrategyblog.com

Cheers,
Will

 

[LockPicker]

In terms of VBA make sure that maximum use is made of objects.

Don't do

dim myworksheet at string

myworksheet = "SummarySheet"

worksheet(MyWorksheet).activate

instead do

dim myWorksheet as worksheet

set myWorksheet = worksheets("SummarySheet")

myworksheet.activate

The advantage of the second method is that myWorksheet now inherits all the methods and properties of the worksheet object which will significantly shorten the code later.

When macros have been recorded remove all the unnecessary .Selects and .Scroll commands. you don't need to select a range to reference it or modify its contents.

.Range("A4").Select
.Range("A4").Font.Bold
.Range("A5").Select
.Range("A5").Font.Bold

can be reduced to

.Range("A4").Font.Bold
.Range("A5").Font.Bold

make sure when referencing ranges and cells that the full reference is used.

make use of named ranges where ever possible and manage the range on the worksheet_onChange() event

Turn off Screenupdating and use the Application.statusbar to keep users informed about what is happening

Make sure that all procedures have a proper on-error handler that will fail gracefully (e.g. turn on screen updating and restore the previous calculation state)

I'm sure I will be able to think of more.....

Mind if I use your "Why do developers get Xmas and Halloween mixed up? Because OCT31 = DEC25"?