- Use Flash Fill.
Like many modern software programs, Excel is designed to recognize certain patterns. For example, perhaps you are creating a table that lists the last and first names of attendees at a company training session. After all the names have been entered into two separate columns, you realize you would like a single column to correctly display the full name. An easy way to achieve this without having to manually retype the entire list is to use Flash Fill.
- Create a new column for the combined information you wish to display.
- In the first cell, type the name as you wish it to display. In our screenshots, this would be “John Smith.”
- Begin typing the next piece of data in the next cell. Excel should automatically suggest a Flash Fill option.
- If the Flash Fill suggestion matches how you would like the information displayed, simply hit the Enter key and the rest of your column should fill in automatically.
Flash Fill is especially helpful if your data is initially in different forms but you want the final information to display in the same fashion. For example, in our attendee list, some of the names were capitalized, in all caps, or had no capitalization. Sometimes you may need to manually update more than one option but Excel will detect your pattern.
Flash Fill should automatically be turned on in Excel but if it is not, you can turn it on using the File>Options>Advanced menus. You can also turn Flash Fill on or off using the shortcut Ctrl+E. Be aware that the Mac version of Excel does not have Flash Fill.