A useful hack – Excel Flash Fill

This isn’t really related to dividends or FI, but it’s a really useful (and I think little known) feature in Excel 2013 that I’ve been using a lot when working with the UK Dividend List. The feature is called Flash Fill and if you don’t know about it already and use Excel frequently for work or personal use, then please take a look below.

Excel Flash Fill

The following video explains the feature (though you may have to skip an advertisement):
 

I’ve used this feature in a couple of different ways:

Transposing UK dates

I had some dividend data with UK date format which was pasted in as text to Excel. I know I could have messed with the regional formats to convert to a US date, but it’s also simple to use the flash fill feature to convert the data. The screenshot below shows the before and after…I selected the green cell, pressed Control + E and boom!

ExcelHack1

You can see that I used it to strip out the “dividend” from “H1 dividend” too…I could have searched for and replaced ” dividend” with “” but it was just easier type “H1” and then do the Control + E thing.

I also used it to generate only the year of the ex-dividend payment for each line; I was too lazy to look up the display formatting or required cell formula to do it. Typing “2014” then pressing Control + E was much easier! 🙂

Generating website URLS

Here’s another example of generating website URLs for a stock ticker based on the symbol. After typing in the first URL, it automatically became a hyperlink. Then I just selected the green cell, pressed Control + E again and the remaining URLs were generated.

ExcelHack2

Anyway, I hope you’ll find this tip useful – it’s always useful to add another tool to your toolbox!

0 thoughts on “A useful hack – Excel Flash Fill”

Leave a Reply

Your email address will not be published. Required fields are marked *