This article illustrates how to use the Excel tool, Flash Fill, for the purposes of financial modeling and business planning. Excel Flash will be demonstrated across a number of instances, and highlight how it can be an incredibly powerful and time-saving tool in financial modelling. This is especially so with large data sets.

 

Strategize Financial Modelling – Flash Fill.xlsb

What is Excel Flash Fill?

Here is a quick explanation of Excel Flash Fill, which is a:

Time-saving feature (in Excel 2013 and beyond) that fills out data based on patterns of data – thus, it can flash fill once a pattern is recognised;

Tool that automatically fills data when it recognises a pattern;

Feature that works best when data is consistent in form and type; and 

Can be executed on the basis of solely one cell – assuming the data array is consistent

 

Example 1 – Extracting GL Numbers

Excel Flash Fill can be applied to the list of GL accounting codes, in order to extract the integer part of the code. If we type 456 in cell C9, then Excel Flash Fill is able to fill the range and reflect this pattern – that is extrapolate just the integer for each GL code.

 

Example 2 – Combining of Columns

Excel Flash Fill can be deployed to combine the “Type” with a dash, with the “Serial Number”; which is an alternative to utilising the Excel CONCATENATE function. All it takes is one example of this version, and Excel Flash Fill can effortlessly fill down the data range. 

Additionally, Excel Flash Fill can readily be rewound; if you are not happy with the pattern fill. Otherwise, we can accept the Flash Fill.

 

Example 3 – Partial Combining of Columns

Excel Flash Fill can facilitate a need to partly combine the Business Unit name with a corresponding transaction number. Additionally, Excel Flash Fill will house the abbreviated Business Unit name inside square brackets. 

Once more, Flash Fill identifies the pattern and instantly applies it to the other line items. Clearly, an alternative solution to Excel Flash Fill, would be to apply Excel functions to derive the same output.

 

Example 4 – Splitting out bank & account numbers

Once again, Excel Flash Fill will perform a fill down based on the pattern from one cell; so as to split out a bank and account number. As along as the serial numbers are consistent, which they are; then Excel Flash Fill can perform this repetitive exercise in seconds.

 

Example 5 – Combining & Adjusting of Columns

In this final example, we want to combine and adjust the format of the following items using Excel Flash Fill – which pertain to bonus amount by employee. 

We want to add a dollar sign to the bonus amount, then a dash, the employee’s surname, a comma and then the initial of the employee’s first name.

 

Final remarks on Excel Flash Fill

What these examples have demonstrated is the immense value of Excel Flash Fill – particularly in financial modelling.

Assuming our data is consistent, Excel is able to recognise a pattern by leveraging simply one input cell – to fill down an array of data.