This video will illustrate how to use the Excel tool – called Flash Fill.
We will demonstrate across a number of instances, how Flash Fill can be an incredibly
powerful and time-consuming tool in financial modelling.
This is especially so with large data sets.
Before we start, here is a quick explanation of what Flash Fill is.
Flash Fill is a new feature in Excel 2013 and beyond, which can fill out based on data patterns.
Thus, it can Flash Fill once a pattern is recognised.
Remember it is best when the data array is consistent in form and type
Finally, Flash Fill can be executed on the basis of solely one cell
– assuming the data array is consistent.
Looking at our first example, 0:01:00.066,0:01:00.033
which entails GL accounting codes; 0:01:03.066,0:01:03.033
we want to apply Flash Fill, in order to extract the integer part of the code.
If we type 456
in cell C9,
then Flash Fill is able to fill the range and reflect this pattern
– that is extrapolate just the integer for each GL code.
In Example 2, Combining of Columns,
we want 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 Flash Fill can effortlessly fill down the data range.
Additionally, Flash Fill can readily be rewound;
if you are not happy with the pattern fill.
Or we can accept the Flash Fill.
Example 3, Partial Combining of Columns,
we want to partly combine the Business Unit name
with the corresponding transaction number.
Additionally, we 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.
Evidently, an alternative solution could apply Excel functions to derive the same output.
In Example 4,
we throw up the challenge of separating bank account
and account numbers.
Once again, Flash Fill will perform a fill down based on the pattern from one cell.
As along as the serial numbers are consistent, which they are; 0:03:51.066,0:03:51.033
then Flash Fill can perform this repetitive exercise in seconds.
In this final example, we want to combine and adjust the format of the items.
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.
What these examples have demonstrated
is the immense value of 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.