How to Use Flash Fill and Recommended Charts [Solved]

How to Use Flash Fill and Recommended Charts

After much dragging of feet, I finally made the switch to Office 2013 on my workstation. Excel LogoThere have been some interface hurdles to overcome, but after only a few short weeks I’ve already come to prefer it. In this edition of our How-To series, I’ll explore a couple of Excel 2013 functions that have made my life and work that much easier.

First off – and this isn’t so much a feature or function so much as it is a change from the status quo – each workbook gets its own window. No longer will I have to resize spreadsheet sub-windows or open separate instances of Excel in order to work on two spreadsheets at once.

But let’s get to some features that will actively help you on a daily basis.

Data entry is a necessary evil in my day to day work. I work with a pretty big quantity of data on a daily basis, and it is very important that all the right letters and numbers get input correctly.

Here’s a simple example. Imagine a scenario where you have to take prospects’ names and get them into your CRM database. You pull a list of raw data – companies, names, positions, etc. – but you need to get the formatting right in order to get everything into your database correctly. Enter Flash Fill, a built in feature of Excel 2013.

First off, I don’t have to go about retyping the same thing for every field:




I can also just use the drag function at the bottom right corner to fill in a set of cells with the same text:



This isn’t any different from Excel 2010. Well, not yet anyway.

Let’s look at what happens with some slightly more complex data, like different names:


Let’s say that my CRM requires me to segment full names into first, middle, and last. Excel 2013 does this through something called Flash Fill. Simply start typing and Excel automatically recognizes a trend, shows you what it thinks you want, and you can choose whether or not to fill in the remaining cells according to Flash Fill:



Excel 2010 can't touch this...

It’s not an extra employee or an intern brought on to do data entry though – data has to be consistent across the board for it to recognize the pattern. For instance, if even one of those full names in the Employee column didn’t have a middle initial then Flash Fill wouldn’t work.

As long as you know how to use it though, the Flash Fill feature makes data entry very, very easy. This means I spend less time entering data like a drone and more time using the data to my advantage. This brings us to another Excel 2013 feature that I’ve fallen in love with, Recommended Charts.

Building visual representations of your data in previous editions of Excel was something of an arcane art, daunting to us layfolk at best, maddening on most days, and downright homicidal on others.

An Excel rage-induced nap. Another Excel 2010 Charts casualty…

But it doesn’t have to be. Excel 2013 includes a lovely little function called Recommended Charts. Let’s take a look.

First, I’ll enter some random data:


Then I select my data set and click on Recommended Charts from the Insert tab:


Now I’m given an option or options to represent the data in a visually appealing and easily understandable manner:


If the suggestions aren’t to your liking, just hop over to the All Charts tab and find one that does:


Hovering your mouse over the chart will blow it up for you:


Now I just click OK and I’ve inserted a nice little chart:


There are then all sorts of chart styles you can play with in the Design tab to further adjust the chart to your liking. Simply hover over one of these to see what a slightly different style would look like:


Getting this far in Excel 2010, 2007, or – heaven forbid – 2003 would have involved at least one sacrificial lamb. Just look at 2010:





So I’ve inserted a chart, but if I want to check out what other charts might look like I’d have to go to the Chart Layouts section, find the layout I think I want, and then click it:


Still no...


Fine. I give up.

The bottom line is that Excel 2013 has serious potential for business use. I’m still exploring its true functionality, but for now I’m elated. It’s the little things in life…