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:
|Assumption or constant||White||Blue||No|
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.
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:
These files have the macros embedded in them. You will need to move these macros into your personal workbook. Help on this.
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
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Extract data using Advanced Filter and VBA||Chandoo.org Holiday SALE, Starts on Monday – 3rd December! »|