Automatically Update Excel Pivot Tables

Excel's Pivot Tables are fantastic for data analysis, but they have one small flaw - they require manual updates when the source data changes. If forgotten, this can have pretty catastrophic effects; I've known it to cause a few red faces!

There's a very simple solution for this, a small snippet of VBA code can be used to automatically update your Excel Pivot Tables. Simply run the below code using a convenient event trigger (for instance, by using the Worksheet_Activate() event trigger when your summary Dashboard is activated).

To learn how to use VBA, see our beginner's VBA tutorial here.

Sub updateAllPivots()

    Dim pvt As PivotTable

    Dim ws As Worksheet

    Dim counter As Integer

    Dim plural As String

    

    Application.ScreenUpdating = False

    

    For Each ws In ThisWorkbook.Worksheets

        For Each pvt In ws.PivotTables

            counter = counter + 1

            pvt.RefreshTable

        Next pvt

    Next ws

    If counter > 1 Then

        plural = "s"

    Else

        plural = ""

    End If

    MsgBox counter & " pivot tables" & plural & " updated."

End Sub

FMDA Consultancy provide training and consultancy services for Financial Modelling and Data Analysis. Contact us at fmdaconsultancy@gmail.com.

Excel VBA: The Basics - Tutorial 1

Adding VBA to your Excel spreadsheet can massively improve your efficiency especially if it is used for a lot of repetitive and labourious tasks. In this first VBA tutorial you'll learn the basics of how to create macros.

VBA is often a great addition to a spreadsheet and one that will leave your colleagues and customers stunned at your abilities. 

What is VBA?

Visual Basic for Applications (VBA) is a programming language used across a whole host of applications including Excel, Word, Powerpoint and much more. The fact that it's a "programming language" often puts a lot of people off learning, but you don't have to be a programmer to achieve some incredible things in Excel VBA. In fact, you can even get Excel to write the code for you by recording macros.

What is a Macro?

A macro is a predetermined instruction to your computer to carry out a certain task. In Excel VBA, these can either be written by you from scratch in the Visual Basic Environment (VBE) or simply recorded.

How to Record a Macro

Recording a macro is very simple. Follow these steps to get started:

1. Open up a fresh spreadsheet in Excel

2. Open up 'Excel Options'

    In Excel 2010 or 2013 click File >> Options

    In earlier versions click Tools >> Excel Options

3. Tick the Developer Tab option

     In Excel 2010 or 2013 click Customize Ribbon >> Developer

     In Excel 2007 click Popular >> Show Developer Tab in Ribbon

4. You will notice a new Tab in the Ribbon called "Developer". Click Developer >> Record Macro

5. You will be asked to name your macro, name it "myFirstMacro" and press OK.

You're now recording your first macro!

Almost anything you do from this point will be recorded and can be called upon for use later in an instance.

6. Click in Range A1 and enter the value "Hello World" and press enter.

7. Now go back to the developer tab and click Stop Recording.

8. Now delete the value in Range A1.

9. Click on the Macros button in the developer tab and select "MyFirstMacro" from the menu that appears then click Run.

The "Hello World" value reappears instantly! Now, this isn't exactly world changing stuff at the moment, but you should be able to see just how much potential VBA has for automating your processes in the future.

The best way to learn is a bit of trial and error, try recording a few more macros and see how they play out. If you have any questions, tweet us @FMDAConsultancy

In the next tutorial we'll look at the Visual Basic Environment and writing your first macro from scratch.

FMDA Consultancy provide training and consultancy services for Financial Modelling and Data Analysis. Contact us at fmdaconsultancy@gmail.com.

Analysis using Waterfall Charts in Microsoft Excel

When analysing two or more scenarios in a financial model, it is often important to distinguish which variables have caused the most significant changes to financial outputs. However, complex financial models can often conceal the finer movements within the model.

Using 'Waterfall Charts' an analyst can determine exactly how a variable's movement has impacted upon financial outputs regardless of the model's complexity and the quantity of variables that have changed between scenarios.

The 'Waterfall Chart' can be altered for use in complex financial models where multiple revenue & cost streams require separation and graphical illustration to demonstrate their relative impact on the overall financials.

FMDA's free Waterfall Chart Template can be downloaded here.

Dynamic, Dependent, Validated Lists in Microsoft Excel

FMDA's free 'validated lists' template spreadsheet demonstrates how to drive lists in a dynamic fashion. Not only that, but the template demonstrates how lists can be populated dependent upon input information. 

Validated lists are an absolute must when developing a spreadsheet intended for third-party use. This functionality blocks a user from entering incorrect information in an input form, removing the possibility of input-driven errors.

However, developers are often unaware as to just how flexible these lists can be - partly because it takes a bit of tinkering to get it right!

The first lesson to learn is how to create 'dynamic named ranges'. These ranges grow larger or smaller in their row-column dimensions dependent upon input information. These are demonstrated in the free template.

The second lesson to learn is slightly less intuitive; by using the 'sumproduct()' function in a rather odd fashion validated lists become a whole lot more flexible! Again, these techniques are demonstrated in our free template. You can download the free template here.

FMDA Consultancy provide training and consultancy services for Financial Modelling and Data Analysis. Contact us at fmdaconsultancy@gmail.com.

Spreadsheet Structure and Formatting with Cell Styles

Creating an efficient, transparent and consistent spreadsheet structure and format is crucial when delivering a professional spreadsheet. These attributes provide managers and clients with the reassurance that work is of the highest quality and ensures that users of these spreadsheets quickly understand, gain confidence in, and ultimately edit or add additional layers to the work with ease.

The easiest and most efficient way to achieve this in Microsoft Excel is to develop structures and formats using 'Cell Styles' before you begin developing your spreadsheet. But don't worry, if you have already created your spreadsheet, these structures and formats can be added retrospectively.

At FMDA we use a very similar structure and format with all of our Financial Models and Data Analysis spreadsheets. You can download a free example of this structure here.

FMDA Consultancy provide training and consultancy services for Financial Modelling and Data Analysis. Contact us at fmdaconsultancy@gmail.com.