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:
| Type | Background | Font | Protection |
| Assumption or constant | White | Blue | No |
| Output | Grey | Black | Yes |
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:
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.
7 Responses to “Macros for Automatically Implementing Modeling Best Practices”
Hi!
Accordingly to modeling best practices if I give a look at the downloadable file, in worksheets Report, Workings and Drivers, I didn't expect to find a few little and elementary things as:
- titles unaligned (rows 6, 5, and 6/12)
- labels with leading spaces (Report!B22)
- labels not referenced (Report, column B)
Just at a first glance.
Regards!
SirJB7,
Thank you for your comments. Unfortunately I couldn’t follow all of your points, but with regard to the Report tab, this is a presentation sheet and therefore exempt from best practice formatting as it is intended to meet the design requirements of the user.
I'm one of these unfortunates that's unable to access videos online, so I can't see the demonstration of how to use. I made a copy of the "Report" tab, and tried the BPM_Auto_Format macro on it; bad move! Everything turned gray. I don't think that's a best practice. A little more explanation would be nice.
Hi jomili,
New to these tools as well, but I'll try to explain what I saw in the video. Looking at the first two buttons (constants and outputs), these two will let you pick the cells you want and apply formatting. White background with blue font for constants, gray background for outputs. Also, constants get unprotected and outputs get protected. The auto format first makes everything (the whole sheet) set as an "output" and then finds the inputs and changes those to white. While I can see that these clearly highlights what areas need to change, I agree with you that having everything be gray is a little much.
The "simple audit" tool is actually more what I was expecting. It colors cells based on their contents into these categeroies:
Text, number constants, formulas, errors.
I like the coloring scheme for this much better than the auto format. Hope that helps!
Thanks Luke. Your descriptions match what I was seeing. The simple audit macro is one I've already been using (Chandoo mentioned it a while back), but don't know that I can find much use for the other macros.
Isn't all of this and much more covered with the free version of bpmToolbox?
http://www.bestpracticemodelling.com/software/bpmtoolbox_highlights
i am using Pictures.Insert to insert pic into excel but not working in 2007,
even try to record macro but not able to record insert pic macro in 2007.
any alternate solution?