Do you know how powerful CTRL E in Excel is? It’s time you learned this mighty shortcut with its application and take your Excel mastery to a higher level! There are hardly any other tools that will help you save time as this CTRL E will.
The best way to learn something is to learn it with examples, and we are definitely taking this route! As you go through the article, you will find that CTRL E does a better job than many other options and functions in Excel. Not only does it save time, but it also works on big datasets without any trouble.
We are going to show you 12 applications of CTRL E in Excel here. We have also included important bits and tips for this excellent tool. They will help you understand the use of this powerful option even further.
What Does CTRL E Do in Excel?
CTRL E is the shortcut for the Flash Fill tool in Excel. This option works by recognizing patterns from data and the inputs. Then it automatically predicts and fills data following the first one or two inputs. It was first introduced in Microsoft Excel 2013. Therefore, CTRL E does not work in any versions before Excel 13.
Now, for this CTRL E to work, you must always take the adjacent column to the right. It will not work if there are any empty columns between the one where your given data is and where you want to apply CTRL E. Flash Fill or CTRL E works from left to right column order.
If you still see that your CTRL E is not working, firstly, make sure that you have the right version of Excel. Afterward, ensure that your Fast Fill option is enabled in the Excel Options. We have shown you how to do it in the Tips section of this article.
12 Efficient Uses of CTRL E in Excel
There are hundreds of applications of CTRL E in Excel. We cannot possibly show them all in this article. Hence, we have chosen 12 excellent and effective uses of CTRL E to share with you which will give you a look into this tool.
These 12 uses will also give you a comprehensive idea of what this CTRL E can offer. If you can understand and apply these uses that we are showing you here, you will be able to do anything with CTRL E in Excel.
We are also sharing with you the Excel file so you can practice all these uses of CTRL E and do even more. Let’s start!
Use 1: Arranging Information
Arranging all the information in Excel is a must when undertaking a task. We have all faced a situation where columns have scrambled data and we needed to arrange them for a proper operation.
See the data above. Sometimes, you end up with thousands of cells in a column where you would have to separate information into groups. In the example data shown above, it would be grouped into Name, State, and Phone.
You can most probably use the Text to Columns option in Excel and delimit data by using the Space delimiter. However, they will not give you the right result if you are looking to extract names intact, in complete full names. Time to let the CTRL E option surprise you!
Follow the steps below to do it in seconds.
1. Write the full name in the adjacent cell to the right and hit Enter.
2. While you are in the cell right below, press CTRL+E on your keyboard, and see the magic!
3. You will find that the column has been filled with all the full names from the previous column. You can continue arranging. For example, to get the States name in the column, just take the next column to the right and click on the adjacent cell. From the first cell of data, input the State code (VA, in this case), and press the Enter button.
4. While the second cell in the column is activated, press the magical combination of buttons CTRL+E. You will see that the column has been filled with the States code.
5. That’s how easy it is! To complete arranging, in the same manner, take the next column to the right and click on the first cell in that column. Write the phone number from the first cell on the left and press Enter. Next, press the CTRL+E buttons to initiate the Flash Fill.
Now compare the column on the left (before) with the three columns on the right (after).
Use 2: Merging Information
In the previous use, we showed how to arrange data into separate columns. In this one, we are going to show you how you can use CTRL+E to combine data from two different columns.
Take a look at the picture above and you will get an idea. You may think that the CONCATENATE function is enough for this. However, the time it will take to write down the whole formula and then fill the columns will take longer than simply using the CTRL+E buttons to do it.
Now we demonstrate how you can merge Last Name and First Name data from two columns in a row and put them in the next column together, in a FirstName_LastName format. Follow the simple steps below to do it effortlessly.
1. Click on the first cell in the Full Name column. Write down the full name from the previous two cells on the left. Press Enter.
2. Your next cell is now selected and activated as shown in the picture above. While the selection is in place, press the CTRL+E buttons.
You will see the column becoming filled with all the names from the left, merging into the FirstName_LastName format in your new column.
See how easy it is? And we just got started. Keep on reading to find out the extraordinary things you can do with the simple yet powerful Flash Fill option through the use of CTRL+E buttons.
Use 3: Extracting Data
How many times has it happened that you were given an Excel sheet with data that looked similar to the picture below, and you were told to extract names and/or phone numbers from it?
Let us guess. Every single day? When thousands of cells contain data that look like this, you know you are in for a tough time. You cannot simply Text to Columns these data. If you like pain, you can type in everything manually. No? Is there then something that will save your day? You can bet CTRL+E will!
Just follow the steps below and watch the magic unfold before your eyes.
1. Let’s say you want to extract phone numbers from this mess. Take the next column, click on the first cell of the column, and then write the phone number from the adjacent cell on the left. When done, hit Enter.
2. Now, when the next cell down is selected and activated, hit CTRL+E.
3. See those phone numbers staring at you? How easy was it? In the same manner, follow the same steps for extracting names.
4. Do the same to extract State codes as well.
Now compare the utter mess on the left to the clean and organized extracted data on the right.
Use 4: Cleaning Up Data
While almost all the uses of CTRL+E or the Flash Fill tool are mostly about cleaning up data, some uses are more about cleaning up data than others. We are going with an apt example for this. Observe the data in the picture below.
When you are given something like this and are asked to sort them out in their names, states, and phone numbers, there are only a few methods you can use. Text to Columns may prove useful, but you are asked to write full names in a column without commas, state codes in another column, and then the phone numbers.
As always, CTRL E is here to make your work easier. Go through the steps below and be done with your work in under a minute!
1. First, take the next column, click on the first cell, and write the full name in the “first name first last name last” format. Then, press the Enter button.
2. Now, before doing anything else followed by hitting the Enter button, press CTRL+E and see it happen!
But wait a minute! What’s wrong? Why are the names in an erratic manner? Well, as we have told you in the intro, Flash Fill works by recognizing and following patterns. Notice the first name in the list. It has the letter “R” in both the first name and last name. Understandably, CTRL E could not catch the difference.
As we also mentioned earlier, this option works from left to right. Therefore, it took the first letter of last names in unorganized data to be the first letter of the first name in the Name column.
3. To remedy the problem, input the second name too in the second cell (Michael Smith, in our data) and then press Enter. And now press the CTRL+E buttons.
4. You’ll see that it worked this time!
Make sure you remember this lesson for when you see that the Flash Fill is not picking up the pattern and failing to repeat it. Just enter a few more cells and then you’ll notice that it has started working.
5. Following the same technique, clean up the remaining data by putting state codes and phone numbers in respective columns by using CTRL E effectively.
There you go! It’s done.
Use 5: Creating Template Sentences
Depending on the job, there are often requirements to create template sentences with clients’ names. For instance, you may be given a list of names. Then using the first names from the list, you are going to have to create sentences to greet your clients. They will be used later on by copy-pasting in, let’s say, text-based chats or e-mails.
For now, imagine that this is the list you are given. You now have to create sentences like “Hello FIRST_NAME! Welcome to our hotel.” Once again, you may either type all the sentences manually. You may also use the CONCATENATE function in Excel to create the sentences. But we suggest CTRL E for hassle-free work. Follow the steps below.
1. As always, take the next column, click on the first cell, and create your sentence as a pattern for Flash Fill to use. Then press Enter.
In this case, our sentence “Hello Richard! Welcome to our hotel.” has been written.
2. When the next cell down is selected, press the CTRL+E combo and let it do the work for you!
This is how flawless CTRL E is!
Use 6: Reversing Concatenation
Yup, you guessed right. We are going to show you how to do the opposite of CONCATENATION in Excel. It may seem familiar because there are indeed some similarities with Use 3: Extracting Data. However, this one here is different. We are not going to extract all the data and put them in columns. We are taking just what we need.
From the data shown in the picture above, we are going to take only the names, ID numbers, genders, SSNs, and countries.
1. First of all, take all the columns you need for your reverse concatenation.
2. Start with putting the names first in the first adjacent column. Then hit Enter and afterward, press CTRL+E to do the Flash Fill.
3. After you are done with the previous step, now fill the ID column following the same technique.
4. Keep on repeating this process for the respective information in the columns. When you are done, you will end up with Un-Concatenated data that would look like the picture below.
Simply magnificent, isn’t it?
Use 7: Reformatting Numbers
We will show you how to format and reformat numbers in Excel by using CTRL E. Let’s say you are given a list of phone numbers that you have to re-write in the standard format of “(Area Code) XXX-XXXX”. Sample data is shown in the picture below.
We are maybe showing you how to do it using CTRL E with a handful of data. The usual situation is much different and you have to deal with even thousands of cell data. To help you do it smartly, CTRL E is always there for you.
1. Take the adjacent column to the right and click on the first cell. Write down the phone number in the standard format. In this example, for the first cell, it would look like ‘(206) 451-2559”. Afterward, press the Enter button.
2. As always, before doing anything else, press the CTRL+E buttons while the next cell down is selected. See the column getting filled with reformatted phone numbers in milliseconds!
Instead of inputting data manually for hours, this is how you can do it instantly. Now, it does not matter how many phone numbers you have to do. Just do the first one or two, press the powerful combination of buttons CTRL + E, and be done with your reformatting!
Use 8: Rearranging Text
In this application of CTRL E in Excel, we show you how to rearrange texts in cells according to your need. Let’s take a look at the data we are going to work with for this one.
Information in this dataset is given in an order of Name, ID, Gender, Date of Birth, SSN, and Nationality. How would you rearrange this given data if you are required to put it in ID, Gender, Name, Date of Birth, Nationality, and SSN order like the picture below?
That’s right, CTRL E will have your data ready within seconds!
1. Start by taking the adjacent column to the right. Rearrange and put the first cell’s data in the order you were asked to. Then press the Enter button.
2. Right after hitting Enter, press the CTRL+E buttons. Your column will have all the data rearranged pretty quickly.
3. It works all the same regardless of whatever order you want the information to be in. For example, if you want to rearrange it into ID, Name, Gender, and so on, you can still follow the same steps and achieve the intended result. There is no way you can do it this quickly in any other way.
Who doesn’t love smart work, right?
Use 9: Formatting Text
Most of the time, formatting texts in Excel is a slippery slope. Take the example in the picture below.
Now consider that you are asked to capitalize the names and uppercase the State codes, keep the phone numbers, and separate them all with commas. You can think of doing it in at least three ways.
- First, you can simply do it manually. But that is not viable if you have to go through hundreds of cells and format them. It will take you hours or sometimes even days.
- Second, you can Text to Columns them, capitalize the first names and last names, uppercase the state codes, and then use CONCATENATE function to merge them again. But this will take time and a lot of effort too.
- Third, you can use the =PROPER() or =UPPER() formula to either capitalize or uppercase all the texts in a cell. It won’t work properly because only the names have to be capitalized (e.g. “john doe” becomes “John Doe”) and only the state codes have to be uppercased (e.g. “ny” becomes “NY”). Moreover, let’s not forget about separating them.
So how do you do it without spending the whole day or breaking all the data? Pretty simple, use CTRL E! Follow the steps shown below and be amazed by how fast it is going to format your text.
1. Take the next column for your formatted information.
2. Now click on the first cell in the newly taken column. Write information from the left cell but capitalize the names (i.e. Richard Robbins) and uppercase the state code (i.e. VA) for this example. Hit Enter.
3. Make sure that the cell right below the newly written formatted text is selected. Press CTRL+E and let the Flash Fill work its magic.
See the result for yourself! Is there any other way that could have fulfilled those criteria this fast? I bet you can’t find any!
Use 10: Removing Space & Characters
Now, this is a familiar situation for almost everybody. You are given a list of phone numbers where you need to change the formatting. You need to remove spaces and brackets from those numbers.
Take a look at this picture. If you are asked to remove those spaces and characters, are you going to do it manually? You probably will. But let’s change that today. Let the blessing of CTRL E in Excel illuminate your life!
1. Like always, take the next column and click on the first cell. Now write the phone number from the left cell but without any spaces or brackets. When done, press the Enter button.
2. Now press the CTRL+E buttons and let the Flash Fill do the work!
There you have it!
Use 11: Adding Area Code to Phone Numbers
Let’s say you are looking to add the area code to a list of numbers you have been given like the picture below. You have this list and, for instance, you have to add the area code 480 of Arizona to all of them.
Now, apart from doing it manually cell by cell, the most famous option in this scenario is to use the CONCATENATE function in Excel. However, we suggest you give CTRL+E a try!
1. Take the adjacent column to the right and click on the first cell. Now write the phone number from the left cell but add the area code of Arizona, 480, in this case. Press Enter when you are done.
2. If you are satisfied with your input, select the blank cell right below your input and hit the CTRL+E combo of buttons. See the column getting filled with those phone numbers but now, they have the area code in front of them!
3. If you want to format the phone numbers while you are at it, you can even do it at one go with CTRL E! Just write those phone numbers in the intended format which is (Area Code) XXX-XXXX in this case. Press Enter followed by CTRL+E. And you are done!
Isn’t this amazing?
Use 12: Combining & Creating New Text
In this one, we are going to show you how you can do more than just add area code as shown in the previous application of CTRL E.
You are given a set of data like in the picture. You need to put them on the FullName,_PhoneNumber basis for a specific use. You have to remove the spaces and brackets from those phone numbers too when you put them in the FullName,_PhoneNumber format. What to do? Time to welcome CTRL E in your life!
1. Click the first cell in the adjacent column to the right. Write your text and hit Enter.
As you can see, the FullName,_PhoneNumber format has been followed here.
2. While keeping the next cell down selected, press the CTRL+E buttons. It will fill the column with combining information from the other three columns on the left.
You can even do more customization for your text and they will still deliver! For instance, you can even add the country code +1 to the phone number and CTRL E will still do a flawless job.
You can even write them in the standard format for phone numbers but this time, with the country code added in the front.
You will get a perfect result every single time!
Important Tips for CTRL E in Excel
We have shown you 12 tremendous applications of CTRL E today. You can combine some applications or modify them any way you need them to be. But there are some things you need to know if you are completely a new CTRL E user.
- If your CTRL E does not seem to work, check if your Flash Fill is enabled. Go to Options from the File menu. In the newly opened Excel Options window, click on Advanced in the left column. Make sure that Enable AutoComplete for cell values and then Automatically Flash Fill are both checked on the right. Click OK to complete the process.
- You will always have to take the next empty column of where your data is. If there are any gaps, Flash Fill or CTRL E won’t work. Now, if there is any empty column between where you want to input your CTRL E data and your range of data but you cannot remove the column, simply put some characters in the empty column temporarily. It will make all of the columns touch each other and your CTRL E will work instantly! See the picture below.
- We have seen in Use 4: Cleaning Up Data how sometimes there may be problems for Flash Fill in recognizing patterns. If that happens, simply input data in a few more cells downward. Especially if you have a huge amount of data, you are suggested to put data in at least three cells for CTRL E to pick and predict the pattern. Then you will see it work smoothly!
- To let CTRL E work flawlessly, try to use it right after you have put data in the first cell of the column. CTRL E works by recognizing and repeating patterns as you already know. Doing anything else in-between inputting the first cell data and initiating CTRL E may break the pattern and give you wrong results.
CTRL E is one of the most powerful options in Excel. Knowing how to use it and where to use it will make you more efficient. We have shown you 12 amazing applications of CTRL E. You can even use CTRL E with other options and functions, and it will take your Excel expertise to the next level!
It is important to keep in mind that sometimes, you may have to arrange some of the data to help CTRL E recognize patterns more effectively. Therefore, when you combine a few more options with this Flash Fill option which is famously known as CTRL E due to its shortcut, your workflow becomes smoother and pressure-free.
Practice the application we have shown you a few times. And with the blessings of CTRL E, become an irresistible force in Excel in no time!