Excel is an extremely smart application and can perform many functions automatically if you know how to use it the right way.
Ctrl+E or Flash Fill is one such feature of Excel, and the things you can do with Ctrl+E can blow your mind. With Ctrl+E you can combine and segment cells, retrieve information, and add text to cells. Let’s learn how to use Ctrl+E to automatize some very complex operations in Excel.
1) What does Ctrl+E do?
Ctrl+E is the keyboard shortcut for the Flash Fill feature. How does the Flash Fill feature work? It fills values automatically by detecting certain patterns. You can click on the Flash Fill icon, or press Ctrl+E on the keyboard to use this feature.
Flash Fill (Ctrl+E) expects one or more examples of a pattern, and then automatically fills in values for other cells or the rest of the column.
Even the formatting of the cell is copied with the “Flash Fill” feature.
Let’s first see what Ctrl+E is capable of, and what functions it can fulfill.
Ctrl+E (Flash Fill) can:
- Combine multiple cells, or concatenate text within a cell,
- Segment text or cells into its parts, add space between words/segments,
- Retrieve information, extract parts of a number or text,
- Add text to generate answers, questions, or sentences from cell values, and add brackets or punctuation to organize text.
Now that we know what Ctrl+E can do, we will see how to use it with practical examples.
2) How to Use Ctrl+E in Excel
a. Combine Cells
Ctrl+E (Flash Fill) can combine multiple cells.
In this example, there are hours, minutes, and seconds in different columns. Let’s use Ctrl+E to combine these values to generate the time.
Step 1: In column D, type the time with the hour (13), minute (34), and second (12) values: 13:34:12.
You can also convert decimals to hours and minutes for this purpose.
Step 2: While the example cell (D2) is selected, press Ctrl+E on your keyboard.
Flash Fill will automatically detect the pattern (HH:MM:SS) and replicate the pattern for the rest of the cells in column D:
Ctrl+E can also concatenate text within a cell. Let’s say that we have the full names of people:
We want to generate usernames for these people, by concatenating the first and last names in lowercase.
Step 1: First, type an example username in a new column by concatenating the first and last names.
Step 2: Select the example cell (B2), and press Ctrl+E.
Usernames will automatically be generated for every individual:
These are only two examples of how Ctrl+E can combine cells or text. Use your own imagination for other possibilities.
b. Segment Strings
Ctrl+E can segment text or information within a single cell, and place them in separate columns.
Below is a spreadsheet that contains information about people, such as name, surname, and age. We want to segment this information and place them in separate columns:
Step 1: For the information in cell A2, type the name in B2, surname in C2, and age in D2.
Step 2: Select cell B2, and press Ctrl+E. This will fill the Name column with the appropriate values. Do this operation also for cells C2 and D2.
With this method, you can segment sentences or information separated with spaces using the segmenting functionality of Flash Fill.
c. Retrieve Information
Ctrl+E can also retrieve or extract information from cells. This is especially useful if you have to extract information from fixed positions within numbers or text.
Below is an example of telephone numbers, and we need to extract area codes:
Step 1: Type an example area code in cell C2.
Step 2: While the example cell (C2) is selected, press Ctrl+E on your keyboard.
Flash Fill will automatically detect the area code pattern, retrieve this information from all phone numbers in column B, and place the area codes in column C:
Similarly, Ctrl+E can extract information from within the text and can perform this task so easily. Without this feature, you would probably have to manually retrieve the information, or write code in some programming language.
d. Add Text
One additional benefit of Ctrl+E is we can add text or characters to cell values. For example, we can add spaces or hyphens to a phone number. Let’s see how we can obtain a phone number with spaces.
Step 1: Write the first phone number with spaces in the desired locations in a new column.
Step 2: While this cell is selected, press Ctrl+E to use the Flash Fill tool.
With the help of Flash Fill, spaces are inserted between the phone number segments:
The add-text functionality of Ctrl+E is also helpful when creating automated text with varying text. Below are the names of people to whom we want to send an automated welcome message:
To generate an automated message with varying names, follow these steps:
Step 1: Type the automated text in a new column, containing the name from column A.
Step 2: While the cell with automated text is selected, press Ctrl+E.
Ctrl+E will automatically generate the text with each individual’s name:
We have seen many different applications of Ctrl+E (Flash Fill). This smart tool makes difficult tasks much easier. Have fun with it!
3) How to Fix When Ctrl+E is not Working
Sometimes, the Ctrl+E keyboard shortcut may not work. In order to fix Ctrl+E when it’s not working, you can follow different solutions.
Solution 1: Instead of using the keyboard shortcut, you can use the Flash Fill icon. Simply go to the Data tab in Excel. Under Data Tools, click on the Flash Fill icon on the upper right of Text to Columns.
Solution 2: Make sure that the correct cell is selected for Ctrl+E to work. Click on the cell that contains the example pattern, and then press Ctrl+E on your keyboard.
Solution 3: Make sure that the pattern is replicable. If the pattern is too complex, or it requires some reasoning other than combination, segmentation, or simple modifications to the text or number, the pattern may not be replicated by Ctrl+E. In that case, you may think of other ways to generate your data.
While you’re at it, also read about how to enable Ctrl + F if it’s not working.
4) How to Undo Ctrl+E in Excel
If you want to undo Ctrl+E and remove the outputs of Flash Fill, you can use the Undo shortcut or the Undo icon in Excel.
Undo Shortcut
The undo shortcut in Excel is Ctrl+Z. After you use Flash Fill with the Ctrl+E shortcut, you can press Ctrl+Z on your keyboard to undo the Flash Fill operation.
Undo Icon
The Undo icon in Excel is located in the Home tab, on the upper left:
You can press this icon until the undo operation is enough.
In this tutorial, we learned about an excellent feature of Excel: Flash Fill. We learned how to automatize many tasks using the Ctrl+E shortcut. Now, you can perform fancy operations that would rather require programming skills. Nice job!