Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Macros for Automatically Implementing Modeling Best Practices

Posted on November 29th, 2012 in Financial Modeling , VBA Macros - 7 comments

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.

Best Practice Modeling using Excel - Make these 5 changes to your Excel models today

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:

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.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

7 Responses to “Macros for Automatically Implementing Modeling Best Practices”

  1. SirJB7 says:

    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!
     

    • Myles says:

      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.

  2. jomili says:

    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. 

    • Luke M says:

      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! 

      • jomili says:

        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. 

  3. James says:

    Isn’t all of this and much more covered with the free version of bpmToolbox?

    http://www.bestpracticemodelling.com/software/bpmtoolbox_highlights

  4. shantnu says:

    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?

Leave a Reply