Macros for Automatically Implementing Modeling Best Practices

This article is written by Myles Arnott from Excel Audit

In the first part on our Modeling Best Practices series, we learned 5 best practices to follow. This article shows how to automatically implement the best practices using macros.

Quick Re-cap on Modeling Best Practices

Make cell content and cell purpose visually identifiable at all times

In the first article I highlighted the fact that the content and purpose of every cell should be easily identifiable to the user at all times.

At a basic level we can identify two basic cell types:

Best Practice formatting made easy

In order to make the application of Best Practice formatting quicker and easier I have created three simple macros. These macros use Excel’s Go To Special function and then some simple formatting to the active sheet.

Demo of the macros

Please watch this 5 minute demo to understand how the macros work.

[Click here to watch the video]

Overview of best practice macros

Auto_Format:  automatically formats cells depending on their type:

• Number constants (i.e. input cells) are white background, blue font & unprotected
• Non number constants (e.g. formulae) are grey background, black text & protected

Constants_Format:  formats selected cells as white background, blue text & unprotected

Formula_Format: formats selected cells as grey background, black text & protected

And a couple of extras:

Simple_Audit:  A Simple Audit Macro that uses the go to special function to select and highlight specific cell types. This is the macro from the Managing Spreadsheet Risk article.

Clear_format: formats all cells as white background, black text & protected

A word of warning: These macros apply formatting to your spreadsheets. This formatting cannot be undone.

File to download

Since formatting steps vary for Excel 2003 & 2007, we have 2 versions of the files. Please download the appropriate file below:

Excel 2007 and above version

Excel 2003 & below version

These files have the macros embedded in them. You will need to move these macros into your personal workbook. Help on this.

Once in your personal workbook you can then add these to your QAT, or Ribbon.

Conclusion

Have a play with the macros on the example workbook and then, once you are happy with how to use them, you can start applying best practice formatting at the click of a button.

Let us know how you are implementing these best practices and your suggestions using comments.

Thanks to Myles

Many thanks to Myles for compiling all the tips & sharing this with us. If you have enjoyed this article, please say thanks to Myles. You can also reach him at Excel Audit or his linkedin profile.