Excel is great when you're working with large amounts of data and need to keep things organized. 3 Beginner Tips for Managing Excel Spreadsheets 3 Beginner Tips for Managing Excel Worksheets Worksheets are the essence of Excel. Here are some tips to help you stay organized and productive. Read more . However, getting that data into your document and formatting it correctly can often be time consuming.
Fortunately, Microsoft designed Excel with this type of task in mind, and there are several features you can use to speed up the process. By taking advantage of auto fill and flash fill, you can make sure you don't waste time. 14 Tips to Save Time in Microsoft Excel 14 Tips to Save Time in Microsoft Excel If Microsoft Excel has taken up a lot of your time in the past, let us show you how to get some of it back. These simple tips are easy to remember. Read more about work that could be automated.
Here's how to get the most out of Auto Fill and Flash Fill.
Before we jump into Flash Fill, it's worth making sure we know how to get the most out of Auto Fill. I'm getting an early start on my Christmas cards, so I've set up an Excel document to help me organize my recipient list.
I want to number my list, but I don't want to write each number individually. Instead, I'll start my list with the number one in cell A2, then drag the bottom right corner of the cell to automatically fill the rest of the column.
Unfortunately, doing this just fills the list with ones, which isn't very useful. We need to use highlighting. Auto Fill Options button to specify what we are looking for. Click the button and select Fill Series . A quicker way to access these options is by clicking and dragging with the right mouse button, which automatically opens the context menu when you release it.
As you can see, our numbers progress sequentially. This is about the simplest possible use for Auto Fill. It really becomes powerful when you can make assumptions about your data How to Convert Delimited Text Files to Excel Spreadsheets How to Convert Delimited Text Files to Excel Spreadsheets Use Microsoft Excel to import data from a text file to a spreadsheet. The Text Import Wizard helps you import delimited text data and organize it neatly. Read more.
For example, below you can see how autofill works with dates. Dragging up automatically fills in dates in the past, while dragging down automatically fills in dates in the future. This works exactly the same way when you drag left and right, respectively.
The more information you provide to Auto Fill, the better it will perform. You can see below that I entered 10:00 AM in a cell, before specifying that it should be treated as a time in the Number House section ribbon tab.
If we drag down, auto fill fills the selected cells with sequential times like one hour intervals.
That's useful enough, but Auto Fill can do more than just hourly increments. If we manually enter 10:30 into the cell below 10:00, we can select both cells and then drag, resulting in half hour increments.
So how does this help our Christmas card list? I'm going to use Auto Fill to determine which card design each person gets.
The three types of cards that I have available are already present in the Design column. By selecting all three cells, then using the fill handle to expand our selection, we can quickly fill this entire column.
You can also use Auto Fill to add days of the week or months of the year. All Excel needs the first day or month and will automatically fill in the rest.
Flash fill is a close relative of auto fill, but it performs a slightly different function. Instead of continuing with a series of data, you can pull information from other cells and present it in the desired format. 9 Tips for Formatting an Excel Chart in Microsoft Office 9 Tips for Formatting an Excel Chart in Microsoft Office First impressions are important. Don't let an ugly Excel chart scare off your audience. Here's everything you need to know to make your charts beautiful and engaging in Excel 2016. Read More.
For Flash Fill to work properly, we need to use cells that are adjacent to the data we're trying to format. It also helps alter the format of your headers, so Excel doesn't assume they're part of your dataset.
Above, I added two more columns to my spreadsheet, the first with each individual's address and the second listed to receive their zip code. I also added the first zip code manually, to help Excel understand what I'm looking for.
Next, select all the cells you want to fill. From here, open the Data tab on the ribbon and select Flash Fill from the Data Tools tongue. (We could also complete this process using the fill handler, as we did before with autofill.)
This will ask Excel to find all the information that corresponds to what we manually transferred from cell D2 to cell E2. The software will find each zip code and copy that information into the selected cell.
This can be a powerful time saving tool. For example, suppose you are preparing these addresses for a mail merge and want to reconfigure the way the information is organized and formatted. Flash Fill allows you to experiment with one cell and then automatically fill in the same results for the rest of the dataset.
Above, you can see that I've reordered the information and renamed the city name with Flash Fill. This functionality cannot read your mind, but it can follow your lead if you provide the correct data. Remember:a second sample cell can often produce more accurate results, especially if its formatting varies between different rows.
Now you know how to automatically create lists of any kind with Auto Fill or let Excel pull information from one cell and add it to another with Flash Fill. If you ever need a paper copy of your data, make sure your spreadsheet is properly formatted for printing. 7 Excel Printing Tips:How to Print Your Spreadsheet Step by Step 7 Excel Printing Tips:How to Print Your Spreadsheet Step by Step Step Having trouble printing from Microsoft Excel? This guide will show you how to print an Excel spreadsheet perfectly. Read more.
Need more help coping with Auto Fill or Flash Fill? Or do you have a tip you want to share with other readers? Either way, why not join in sharing your thoughts in the comments section below?
