fbpx
Search
Close this search box.

Macros for Automatically Implementing Modeling Best Practices

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

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