Set up a base template in Excel and re-use your precious Styles!

When you create a new Workbook in Excel you get the standard three worksheets and the standard (useless) Excel Styles. This means that every time you want to start preparing a workbook, you need to do one of the following to improve the presentation:

  • Import Styles from another workbook
  • Manually update the presentation with shades, fonts and borders

Setting up a base template with a good suite of Styles can significantly improve the efficiency of financial modelling and is strongly recommended even if you are only working part time in Excel.

If you are a full-time Excel professional and are not yet using Styles then get on it today as it will change your life forever.

Standard Excel Styles and Worksheets

template1

template 2

Tailored Excel Template with Styles and Coloured Worksheet tabs

template3

template4

Set up your own starting template in Excel – it is easy!

To set up your own starting template in Excel you simply work through these steps:

  1. Make sure you can view hidden folders. This can be done by:
    My Computer -> Tools -> Folder Options -> View -> Show Hidden Files and Folders
  2. Create a workbook that contains the Styles you want and the Worksheet structure you prefer. I recommend not to include any other content (headings etc) in this workbook.
  3. Save the file as Book.xlt (the extension “xlt” means that it is a template rather than a standard workbook) in the following location:C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

Now once you open up a new workbook all the Styles can be accessed the usual way (ALT + ‘).

Why should you want to have a standard library of Styles in Excel?

The answer is simple – it makes creating perfectly consistent Excel workbooks really quick and easy. The following example takes only seconds to create with perfect consistency and no manual adjustments of the formatting.

If you don’t find this convincing enough then just give it a go anyways – I promise that it is worth it!

Recent posts by Rickard Wärnelid

Tags: , , ,

Comments for “Set up a base template in Excel and re-use your precious Styles!”

  1. Nick Crawley says:

    Hi Rickard,

    In my view “Styles” is one of the key differentiator of a profesisonal modelling product for sure and commercially one needs to use Styles to work fast enough to be profitable (!)

    Excel 2007 has had an overhaul on Styles, sadly not in a good way, but do you think your approach works for Excel 2007 templates as well?

    Nick

  2. I haven’t actually tried this in Excel 2007 as I am doing everything I can to stay clear of it as it doesn’t add any value to a professional financial modeller. I upgraded when it was first released but have now gone back to a double installation of 2003 (+compatibility pack) AND 2007 with Excel 2003 as the default version for .xls files.

    In theory you should be able to modify the Excel start template in the same way but the location of these files will be slightly different depending on the version of Excel:

    Excel 2000 – C:\Program Files\Microsoft Office\Office\XLStart\

    Excel XP – C:\Program Files\Microsoft Office\Office 10\XLStart\

    Excel 2003 – C:\Program Files\Microsoft Office\Office 11\XLStart\

    Excel 2007 – C:\Program Files\Microsoft Office\Office 12\XLStart\

    If you decide to give this a go, please let us know the results.

  3. Jon von der Heyden says:

    Why are you staying clear of 2007? Surely there are plenty of customers who need to have models in 2007. The compatibilty pack will not solve all your issues… Compatibility is a real issue and the pack doesn’t solve all.

    Anyway beside that I would suggest creating styles in a sheet template (sheet.xlt) because it offers the benefit of loading styles into existing spreadsheets simply by adding a new sheet to it.

    Book.xlt / sheet.xlt is also very valuable for storing common names and even functions. The problem with adddins is they need to be installed on the machine (or shared directory) so using UDF’s from an addin often returns #NAME? error when distributed to other users. Storing your UDF’s in book.xlt will ensure that they are always attached to the workbook.

    This article supports the need to use Styles: http://support.microsoft.com/kb/213904

  4. Jon,

    I like the idea of inserting sheets and will have a play with that. I can see that being a very valuable functionality for internal use. In my day to day work at Corality we always work on financial models for external clients which means that 100% compatibility is critical.

    The reason we are avoiding new developments in Excel 2007 is not because of compatibility issues between Corality and the clients (since that has already been solved by installations of Excel 2003 and 2007) but rather compatibility between our clients and their clients (i.e. banks and investors).

    In a more general Excel context than financial modelling for cashflow analysis I am sure it makes sense to investigate and use all the new functionality of Excel 2007, but in the financial modelling world structure, simplicity and transparency is far more important than technical sophistication.

    I agree with your point of Add-ins, they simply create too many issues to be worth it.

  5. Jon von der Heyden says:

    I suppose it also depends alot on the complexity of the models that you build. The models I build typically require alot of VBA development. I build alot of models that write and read data from external tables (Access for instance). I also build interfaces with VBA (toolbars etc…). My models tend to check the version that the client / user is on and reacts accordingly (i.e. toolbar or riboon approach). Because of the complexity of my models I also tend to include a few ‘settings’ sheets in my book.xlt. One which manages the properties that belongs to each sheet, but others also to store tables for table driven interfaces for instance. A good default workbook set-up can save alot of development time but it does demand a consistent approach to model and data structures.

  6. Every specific field of financial modelling requires its own set of tools. Fimodo is generally presenting tips and tricks for people mainly developing cashflow analysis models where transparency is extremely important.

    By the sounds of it you are from a different background and it would be interesting to know more about the business use of the models that you are developing. In the area where I perform most of my work (Project finance modelling and corporate finance) using Access databases is a guaranteed way of annoying your banks which will result in a very painful capital raising process…..

    The most common action when a banker receives a financial model developed with Access components is to say ‘thank you but no thank you, please re-build your financial model in Excel and come back to us when you are done.’ Same thing goes with Excessive VBA as most bankers don’t know/trust VBA enough to use it efficiently in the credit analysis situation. If they did they would not manually be running simple tasks such as debt-sculpting using VBA and Goal SEEK.

    http://blog.corality.com/2009/09/debt-sculpting-using-vba-and-goal-seek/

    Jon, I didn’t mean to sounds negative – it’s just that you are too technically too advanced for ‘our’ financial modelling sector….. Looking forward to hearing more about the area you work in to see if I am right…!?

  7. Jon von der Heyden says:

    Hi Rickard

    [QUOTE]
    it’s just that you are too technically too advanced…
    [/QUOTE]

    In my experience and opinion I believe that there are not enough solutions providers out there who possess sufficient technical skills. They are either too ‘technical’ or too ‘financial’. I don’t mean to cause offence with this statement – I am by no means implying that the solutions that your establishment provides are inadequate, merely that I believe that more technical knowledge offers more alternatives and more methods to improve your already great models. :)

    I like the way the authors of Professional Excel Development 2nd Edition (PED) categorise excel developers. They describe VBA developers as believing that every problem must have a VBA solution. This is only too true in my experience and there are too many VBA developers in the market place; and it is because of them that clients are adverse to allowing any VBA employed within their solutions.

    The authors go on to describe professional excel developers as people who can develop excel-based applications and utilities that are robust, fast, easy to use, maintainable and secure. I believe that with the right application VBA can be used to ensure that all of these aspects are true in a model.

    Of course we may want to differentiate models from applications, but in my view the two are the same. Both collect and store data. The data is aggregated to provide outputs and the outputs are presented in various different ways to facilitate interpretation. Some projects may be complex, involve copious amounts of data and even require tapping in directly into the ERP. Others may rely on a simple report download or two and then loaded into the model.

    Employing VBA in models doesn’t have to be cumbersome. Using VBA doesn’t have to make a model more difficult to maintain or detract from the confidence in the results. I use VBA to improve the efficiency, to improve the user interface and make for a more pleasant user experience. I use VBA to ensure absolute integrity in both data results and also workbook/sheet/data structures. I refer to native methods for the real powerful stuff such a pivot tables, charts, validation etc… Most flash/summary reports in my models either employ pivot tables or they are aggregated using formulae.

    Another bonus for using VBA is that using ADO within VBA for instance often allows us to skip the laborious process of downloading reports from the ERP first. This could allow us to ‘tap’ into the ERP and collect the data directly. Of course many BI solutions solve this need for us; and I am a huge fan of BI.

    It is very difficult for me to describe how exactly I apply VBA in my models because I employ VBA for so many different requirements. But certainly VBA does have its’ place often even in the smaller projects, simply because of its’ ability to ensure integrity and to improve the user interface.

    With regards to using MS Access (or any SQL back-end), again there are so many advantages. To list a few:
    1. Use where records exceed what Excel is capable of handling (note the term handling rather than storing). Using ADO to read/write data to and from Access is often much more efficient than housing in the same workbook.
    2. Use to facilitate simultaneous update / access. Excel is rubbish when it comes to ‘sharing’!
    3. Data that has been aggregated with SQL can be trusted, and I would even say that I would have more confidence in SQL aggregated data than formula aggregated data.

    I think that a chapter in the PED called ‘Application Architectures – Concepts’ best describes the need for VBA in models and the risk of developing Codeless Applications. Maybe you can get a sneak preview into the chapter by visiting it on Google books here: http://books.google.co.uk/books?id=VnegO0pMYlIC&pg=PT1&dq=professional+excel+development+second+edition#v=onepage&q=professional%20excel%20development%20second%20edition&f=false

    This chapter clearly outlines a number of valid points into why codeless applications are often a bad idea and how VBA can be used to enhance models/applications. The reason why so many clients are so adverse to VBA in their solutions is largely due to how many developers there are out there who are not yet proficient enough to make VBA work! But a codeless application can never compare to a well constructed model built by a proficient developer.

    As for my background, I have been developing financial models for nearly the last 10 years. I specialise in management accounting functions and I have a background in telecoms and industrial. I couldn’t begin to even list the models that I have built but safe to say I have built many. I love Excel and everything that can be done with the Excel Object Model.

    Regards
    Jon

  8. Hi Jon,

    Thanks again for your comments – I really think it is great for Fimodo’s readers to see the different views on VBA which proves that it is quite a controversial topic.

    I will definitely not argue with you over the benefits of VBA/SQL/Databases as I started out in my career in this field. I was developing banking software with Excel front-end for portfolio optimisation of fixed income instruments, equity trading simulation tools, fund performance reporting most of which were based on a VB6/Microsoft SQL/Excel and in these cases VBA certainly adds a lot of analytical power as it is in pretty much all ways superior to pure Excel stuff. When presenting Corality’s VBA for cashflow modelling course (http://corality.com/training/courses/vba-macro-cashflow-models) I have had to simplify all of the concepts from a ‘real programmers perspective’ to fit in to the target audience of financial analysts, bankers and corporate financial controllers etc.

    I think that the benefits of using VBA really comes back to the individual definition of a ‘financial model’. There are numerous definitions and to make things clear my personal definition would be something like ‘bottom-up modelling of periodic forecasted cashflows generating aggregated information used for analysis.’

  9. @ Rick:

    “I think that the benefits of using VBA really comes back to the individual definition of a ‘financial model’.”

    Here you really hit the nail right on the head Nick. During my career I’ve seen the term “financial model” applied to just about anything that makes a calculation denominated in a currency.

    In most cases, the term is semantically correct. After all, an SAS or SPSS credit rating model also fits the definition, technically.

    It’s important that all of us in the profession do our part to avoid confusion among ourselves, for clients and the general public.

  10. The question of whether or not to include VBA in a financial model is a tough one. Basically when developing models for clients, avoid it if possible, because some smaller companies may not have the expertise to make changes if required (which means they have to keep calling me back in – which not exactly what we want!) However, if the model functionality requires VBA, I build it in if the benefits outweigh the disadvantages.
    It’s the same with array formulas – do we avoid them because there is limited general knowledge so users often can’t take them apart?

    @Rickard I agree with your views on Excel 2007 and having all files compatibile, as well as simplicity and transparency being more important than sophistication. However, I must say that I do at times get seduced by the functionality of some of the new tools and functions. Only good of course if all users are in 2007!

    Danielle Stein Fairhurst
    Plum Solutions.

  11. @Julius – Maybe we should introduce a rule that all comments on Fimodo have to be signed off with the person’s name and their definition of a ‘financial model’!

    @Danielle – Of course I would like the world to update to the newest version of all available software – not just for financial modelling in Excel but also for other things. Replacing Microsoft work with Mindmapping software like FreeMind (http://freemind.sourceforge.net) changes word processing from sequential to multi-tiered and can drastically improve efficiencies in communcations (for example when scoping a financial model based on a number of categories).

  12. Julius Powell says:

    @ Rick:
    Hooray for FreeMind! In my opinion, mindmaps are THE best tools for scoping a model. If regularly updated, it makes a handy reference when looking at a model again after a break between, for example, bid phases. A toned-down version of the mindmap is a great supplement to model documentation.

    FreeMind obviously has the major advantage of being open source.

  13. Hi Julius,

    That is the first time I have heard of anyone external being passionate about mindmaps for financial modelling and it makes me very happy! It makes communications so much quicker and avoids the old static word docs that never get updated after initial preparation.

  14. Bash B says:

    Nick Crawley introduced my colleagues and I to the world of styles in Excel, and life has not been the same ever since! It is so simple and yet so useful and time saving, no need to worry about everyone in the team getting the colours and formatting right anymore.

    Is there a way to, in Excel 2007, edit/delete several styles concurrently without having to go back to the styles dropdown each time?

  15. Chris McNeill says:

    @ Bash B

    If you are up for some VBA, then create a Userform and put a listbox and 2 buttons on it. Then copy the code in below:

    Attach it to your Personal workbook, and a commandbar button and you can use it anytime, any workbook.

    ‘//////
    Option Explicit

    Private Sub UserForm_Initialize()
    Dim sStyle As Style
    With Me.ListBox1
    For Each sStyle In ActiveWorkbook.Styles
    .AddItem sStyle.Name
    Next
    .MultiSelect = fmMultiSelectExtended
    End With
    End Sub

    Private Sub cbCancel_Click()
    Unload Me
    End Sub

    Private Sub cbDeleteStyles_Click()
    Dim i As Long

    On Error Resume Next
    For i = 0 To Me.ListBox1.ListCount – 1
    If Me.ListBox1.Selected(i) = True Then
    ActiveWorkbook.Styles(Me.ListBox1.List(i)).Delete
    End If
    Next i
    Unload Me
    End Sub

    Chris

Comment on this Article