We’re big fans of Excel data validation for controlling key inputs in financial modelling. Data validation will limit the range of inputs to those that the model is designed to cope with, plus make operating the Excel spreadsheet that much easier. The best place to find our more about all that is right here: data validation.

Data validation with uncertain lists

But sometimes the list of input data for your data validation could be uncertain. We recently had a delegate on a financial modelling course who had seen this before: a trick to set up data validation to handle a list of indeterminate length. It’s Jessica who gets the credit for bringing this up during the sesssion!

Embedding Offset inside the data validation list

If you want your data validation to handle a list of any length what you can do is put an Offset function inside the list input:

Structuring the Offset: how long should the list be?

In general we think it’s not best modelling practice to force Excel to work right to the bottom of a spreadsheet. So if the list could never go beyond 200 rows, we’d prefer cell C2 in the example above (an input for the Offset formula) to include something like (A1:A200) rather than (A:A).

Dealing with lists of data of indeterminate length in Excel

The point above applies generally to Excel. Sometimes we’re trying to construct a formula over lists of data that vary. Imagine that we import then present a set of data each month e.g. from management information systems. Each month when we dump out the list of data it has changed in length. Some months there are more rows of data than in other months, but we want to do something like sum all the data up. We’re never sure how long or deep the data’s going to be.

It’s easy to construct a Sum formula that will capture all the data

If we’ve got a list of data that varies each month, it’s super-easy to construct a formula that will capture all the data. In this case the formula would be something like =Sum(A:A). That formula is going to capture all the data in the column, no matter how many rows are involved.

Although it’s easy to build a formula that adds up all the data, it’s a pretty bad habit to get into. =Sum(A:A) is going to force Excel to work right to the end of the spreadsheet each time using a bit more processing power than it should. If your formulas get longer and more complicated and you’ve got lots of data, and lots of formulas, your spreadsheet is going to get large and start slowing down. So best-practice modelling would say “Don’t” here. Don’t get into the habit of building formulas over whole columns or rows. You’re asking for Excel spreadsheet size problems. A better solution would see you limiting the data processed by the formula to a realistic maximum. = Sum(A1:A200) is a lot better than = Sum(A:A).

Hang on a minute: I really truly don’t know how long my list of data will be

It’s all very well establishing best practice financial modelling guidelines but, in the real world, Excel spreadsheets regularly don’t comply with the cosy cotton-wool fantasy envisaged by our nice neat guidelines. We’ve had people on our financial modelling course training who have had models with 1,000s of rows of data. The guidelines should probably say “Don’t” to that too. Excel was never designed to cope with those volumes of data. You should be using a database and employing a programmer.

Unfortunately “Don’t” isn’t solving the problem for the financial modelling analyst who has been told they have to produce a result NOW (notice we SHOUTED there) and needs to get outputs without Excel falling over.

Uncertain/ dynamic data: could macros have a role?

Again, with macros, best practice financial modelling would say “Don’t” do that:

  • Macros are unauditable and unfathomable to most mortal Excel users;
  • Macros break easily;
  • Financial modelling’s not quite that clever. You should be able to solve any day to day modelling problem without having to resort to a macro.

We’re very happy to issue some severe health warnings around Excel VBA macros. By adding a macro you may be guaranteeing yourself a job for life as the only person who can get the Excel model to work but, assuming you may one day pass on and find another job, you haven’t done your firm any favours at all. You’ve successfully created a problem for your company: the Excel spreadsheets are full of macros only you know how to use.

Macros and real-world financial modelling

Sometimes though, in the real world, there really is a problem that we can’t solve using Excel’s regular formulas. And that’s where the macro could come into its own: as a last resort in the imperfect world when there really is no other solution provided via Excel’s standard functionality.

Macros and dynamic/ variable lists

So today we’re pretending that we’re living in a real and imperfect world where one moment data could be a few rows and one moment it could be 1,000s more than Excel should reasonably be asked to cope with. We already know it’s going to be a bad idea to ask Excel to work to the bottom of those lists all the time e.g. with =Sum(A:A)

How could a macro help?

Yes, as the last resort, a macro could potentially help. Instead of building a regular function that forces Excel to work right to the bottom of the row, what we could do is build and run a piece of macro code that will work to the bottom of the list. The good thing about the macro is that it takes the hard work “offline”. Instead of asking Excel to crunch through a huge list of live data, the macro will only start work when we say “go” and ask it to run a piece of specific VBA code.

To illutrate, here’s a tiny macro code example that totals any data in column A (no matter how deep it is) and plonks the result in cell B1:

Sub AddCells()
'This macro sums the data in column A and pastes it into cell B1
'The macro works no matter what the length of data in column A
'You could discover a key part of this code by starting the macro recorder
'And watching the code that results as you use the ctrl shift down keyboard shortcut
Range("B1") = WorksheetFunction.Sum(Range("A1", Range("A1").End(xlDown)))
End Sub

There’s only one line of code involved!

It’s not too hard to discover for yourself a really handy snippet of the code ("A1", Range("A1").End(xlDown)) that helps you automatically work to the bottom of a list. That’s if you’re able to:

Help, I’m lost with macros!

If you’re new to Excel VBA and you’d like to know more about how to implement the code we’re looking at here, sign up for our online macros training, or register your interest in our live taught vba macros course where you get to practise all of this stuff working through examples sitting side by side with your trainer.

Applying the macro to data validation

Do you remember:

  • We were looking at data validation with a dynamic/ changeable list of data that could vary in length/ depth?
  • We used Offset but decided that embedding something like (A:A) in the spreadsheet was probably a bad idea?

In this next example we:

  • Use the same kind of code to work to the bottom of a data validation list;
  • Assign a name to the list (see “naming cells” in Excel);
  • Put the name inside the data validation;
  • Add a button that will run the macro and regenerate the list, making sure all the data is picked up by the validation.

If you click on the image you can download the example:

Believe it or not, all that’s required is one line of macro code!

Names("ValidationList").RefersTo = Range("A1", Range("A1").End(xlDown))

Breaking the rules

Yes we’ve consciously broken a few of the usual “Don’ts” around macros but sometimes life just requires that!

Read more

You’re reading stories from the roughty toughty world of financial modelling,
That’s all.

Read more stories

About Financial Training Associates Ltd

At Financial Training Associates we’re Excel warriors. Excel runs through our veins. We’re lean and we’re mean. We live on the edge and sometimes we even break a few financial modelling rules (although we think really really hard before doing so).

Find out more about the Excel VBA macros course

If you fancy breaking the rules once in a while and maybe using macros to automate the most mundane of tasks in your Excel life (the ones that are just boring your stupid) you might want to check out our VBA course.

Go on. See whether you’re tough enough.

The VBA course: find out more...