The official Microsoft Excel logo on a gray background

A lot of the tasks you’ll complete in Microsoft Excel are tedious. Luckily, Excel has several features that make this kind of spreadsheet work bearable. We’ll look at two of them: Flash Fill and Auto Fill.

How to Use Flash Fill in Excel

Flash Fill can automatically detect patterns in data and help you quickly fill cells.

For example, if we start with a list of full names (first and last), but then decide that we should have split them into separate columns, Flash Fill can automate a lot of the work.

To start, let’s assume that we have a list of names. In the column where you want the first names to go, type just the first name from the first cell.

first name only

Click the “Data” tab on the ribbon at the top of the Excel window.

data tab

Then, click the “Flash Fill” button in the Data Tools section.

flash fill

As you can see, Excel detected the pattern, and Flash Fill filled the rest of our cells in this column with only the first name.

filled first names

From here, now that Excel knows our pattern, it should show you a preview as you type. Try this: In the next cell over from where you typed in the first name, type in the corresponding last name.

add last name

If we click “Enter” on the keyboard, which moves us to the cell below, Excel now shows all of the last names in their proper places.

auto fill

Click “Enter” to accept, and Flash Fill will automatically complete the rest of the cells in this column.

auto fill last name

How to Use Auto Fill in Excel

Auto Fill works a little like Flash Fill, although it’s better suited for tasks that involve a lot of cells. It’s also better for cells that have an even more obvious pattern, such as numbers, for example. To see how it works, let’s type in a few numbers.

select cells

Click and drag to select both cells.

select cells

Find the square in the bottom right of the cell and drag it down. You can drag it as far as you’d like.

find box at lower right

Excel recognized the pattern and filled all of the cells below that you told it to.

But this doesn’t just work for numbers. Auto Fill is great for all sorts of patterns, like days and months, for example. Let’s type a few to get started.

adding months

Just like in the previous example, if we click the box at the bottom right and drag it down, Excel will fill all of the cells below using the Auto Fill feature.

more months

Using Flash Fill and Auto Fill are two easy ways to automate your work in Excel, so long as it’s an obvious pattern.

Although Excel sometimes surprises us with its ability to detect more complex patterns, it’s not something that you can count on. Stick to easy stuff for the best, most consistent results.

Profile Photo for Bryan Clark Bryan Clark
Bryan has worked in journalism and publishing for more than 15 years. For the last 10 years, he's covered the technology beat, including gadgets, social media, security, and web culture. Before working as a freelancer, Bryan was the Managing Editor for The Next Web. These days he spends his time at a number of publications, both online and off, including The New York Times, Popular Science, and The Next Web, among others.
Read Full Bio »