Control E in Excel – What Does it Do? (10 Hacks) (2024)

The shortcut Control E triggers the Flash Fill feature in Excel.

It ranks at the top of the most valuable shortcuts in Excel and can be considered Excel’s superpower.

This tutorial shows you ten amazing time-saving hacks one can pull off in Excel using the shortcut Ctrl + E.

What is Flash Fill in Excel?

Excel’s Flash Fill feature automatically fills in data when it detects a pattern. Unfortunately, it is only available in Excel 2013 and later.

Note that the column on which you’re using the Ctrl + E shortcut should be adjacent to the raw data columns; otherwise, it won’t work. This is because it can only identify the pattern from the adjacent column.

So if you have a data set with full names in column A comma, then you need to enter the couple of expected results in column B and then use Control E to use Flash FIll to fill column B with results (where it will try and identify the pattern based on the first manual entry that you do).

Additionally, the results of Flash Fill are not dynamic, so if there is a change in the raw data, you will need to repeat the Flash Fill procedure to reflect the change in data.

The keyboard shortcut to use Flash Fill is the same in windows as well as in Mac

Now let’s dive into the tutorial and discover the fantastic stuff we can do with the Ctrl + E shortcut.

Also read: Useful Excel Shortcuts

Control E Hack #1: Insert Spaces or Special Characters into the Data

When we insert spaces or special characters, such as hyphens, into the data, such as phone numbers, we make the data more readable.

For example, consider the following dataset containing book titles and their International Standard Book Numbers (ISBN).

The ISBNs are challenging to read because they do not have hyphens or spaces separating the numbers into groups.

Inserting spaces or hyphens manually in the data will be tedious and time-consuming, especially if the dataset is huge.

We can use the shortcut Ctrl + E to insert spaces or hyphens in the data to make it easy to read.

We use the following dataset to show how we can insert hyphens in the ISBNs to make them easy to read.

We use the below steps:

1. Select cell C2 and type in the ISBN displayed in cell B2, placing hyphens in different positions:

978-92-95055-02-5

Note: By entering the first ISBN with hyphens, we are training the Flash Fill feature and telling it to fill in the rest of the data following the pattern we have shown.

2. Select cell C3 and press Ctrl + E.

Flash Fill fills in the rest of the data following the pattern we showed:

Notice that Flash Fill has gotten only the first two numbers correct.

It has incorrectly generated the other three numbers because Flash Fill does not always get it right the first time, and we will need to train it further so that it gets everything right.

In this case, we train it further by selecting cell C4 (where it first got it wrong), pressing F2 to enter edit mode, and changing the last number of the ISBN to 4.

When we press Enter, Flash Fill gets everything right:

Also read: How to Extract Text After Space Character in Excel?

Control E Hack #2: Unconcatenate Data

When we concatenate data, we join data from several columns into one column.

Sometimes we may need to do a reverse process where we split (unconcatenate) the data in one column into several columns.

Let’s consider the following dataset with the first and last names in one column.

We need to split the full name in column A into first name and last name.

We can do this easily using Ctrl + E.

We use the following dataset to show how we can use Ctrl E to unconcatenate the full name in column A into first name and last name in columns B and C, respectively:

We proceed as follows:

1. Select cell B2 and enter the first name “James”:

2. Select cell B3 and press Ctrl + E.

The rest of the first names are filled in automatically:

3. Select cell C2 and enter the name last name “Smith”:

4. Select cell C3 and press Ctrl + E.

The rest of the second names are filled in automatically:

We have unconcatenated the full names into first and last names almost effortlessly and within no time, thanks to the powerful Ctrl + E shortcut.

Also read: How to Combine Two Columns in Excel (with Space/Comma)

Control E Hack #3: Add Text To Cells

Suppose we have the following dataset of people who have signed up for our Excel tutorials on Flash Fill.

We want to write them a standard but customized thank you note in column B.

Of course, we can do this manually, but it will be a tiresome and time-consuming process, mainly if we deal with a large dataset.

We can use Ctrl + E to generate column B’s customized thank you notes instantaneously.

We use the following steps:

1. Select cell B2 and enter the note, “Hello Edith, thank you for signing up for our exciting Flash Fill tutorials.”

2. Select cell B3 and press Ctrl + E.

Personalized thank-you notes for the people who signed up for the tutorials are generated spontaneously:

Also read: How to Add Text to the Beginning or End of all Cells in Excel

Control E Hack #4: Re-order Data

Sometimes we may want the data in our Excel files to be displayed in a different order.

Let’s consider the following dataset showing full names in the order of the first name and then last name:

We want the data displayed in a different order of the last name and then the first name. This re-ordering can be done with ease using Ctrl + E.

Let’s use the following dataset to show how this can be done:

We use the below steps:

1. Select cell B2 and Enter the name displayed in cell A2 but in the following order, “Smith, James.”

2. Select cell B3 and press Ctrl + E.

The names are instantaneously re-ordered as though by magic!

Also read: How to Rearrange Rows In Excel (4 Easy Ways)

Control E Hack #5: Convert Data to UPPERCASE

We may want to convert text strings to all uppercase letters. This conversion can be done very fast using the magical Ctrl + E.

For this tutorial, let’s consider the following dataset showing some of Excel’s built-in functions in lowercase.

The functions are usually written in UPPERCASE. We use the following dataset to show how Ctrl + E can be used to convert the functions to all uppercase:

We proceed as follows:

1. Select cell B2 and enter the function displayed in cell A2 but in all uppercase, “UPPERCASE.”

2. Select cell B3 and press Ctrl + E.

All the functions are converted to all uppercase.

Also read: What Does F2 Do in Excel?

Control E Hack #6: Convert Data to the Proper Case

We can use Ctrl + E to convert text strings to a proper case, where the first letter in each word is in upper case, and the rest are in lower case.

Let’s look at the following dates showing full names in all lowercase:

Names usually are written in the proper case, so we need to convert the data into the appropriate case. Again the magical Ctrl + E will save the day.

We use the following dataset to show how Ctrl + E can convert text strings into a proper case.

We follow the steps below:

1. Select cell B2 and enter the full name displayed in cell A2 but in the proper case, “James, Smith.”

2. Select cell B3 and press Ctrl + E.

All the full names are converted to a proper case.

Also read: Alt + Enter Not Working in Excel

Control E Hack #7: Add Brackets to Data

We may want to highlight some aspects of our data by enclosing them in brackets or parentheses.

Let’s look at the following dataset showing the names of countries and their capital cities in one column.

We want to highlight the countries by enclosing them in brackets.

We use the following dataset to show how this hack can be pulled off using Ctrl + E.

We use the below steps:

1. Select cell B2 and enter the data displayed in cell A2 enclosing the country in brackets, “Nairobi (Kenya).”

2. Select cell B3 and press Ctrl + E.

All the countries in the dataset are immediately enclosed in brackets.

Also read: Show Negative Numbers in Parentheses/Brackets in Excel

Control E Hack #8: Generate Custom Domain Email Addresses

We can create custom domain email addresses using Ctrl + E.

Suppose our organization’s domain name is flashfill.com, and we want to generate custom email addresses for the following employees:

We can use Ctrl + E to generate the email addresses using the following steps quickly:

1. Select cell B2 and enter the following custom email address for the first employee on the list:

richard@flashfill.com

2. Select cell B3 and press Ctrl + E.

The custom domain email addresses for all the employees are suddenly generated.

Control E Hack #9: Extract Year From Date

Sometimes we may want to extract pieces of data from other data. For example, we can extract the years from the Julian dates in the following dataset.

Note: A Julian date is a date format that combines the current year and the number of days that have elapsed since the start of the year. For example, January 15, 2015, is represented as 2015015.

We can easily extract the years from the dataset using the thrilling Ctrl + E.

We use the below steps:

1. Select cell B2 and enter the year 2010 (the first four numbers of the value displayed in cell A2).

2. Select cell B3 and press Ctrl + E.

The years are promptly retrieved from the Julian dates:

Also read: How to Remove Year from Date in Excel?

Control E Hack #10: Join Values from Cells to Create Dates

We can use Control E to combine data from cells to create dates in the format we want.

Let’s consider the following dataset showing the day, month, and year in columns A, B, and C, respectively.

We want to combine the values in columns A, B, and C in column D in the date format of yyyy-mm-dd. We can achieve this by using the fantastic Ctrl + E shortcut.

We use the following steps:

1. Select cell D2 and enter the date 2022-12-11.

2. Select cell D3 and press Ctrl + E.

The values in cells A, B, and C are combined into dates in column D in the date format yyyy-mm-dd.

This tutorial has shown ten amazing hacks we can pull off in Excel using the magical Ctrl + E flash fill shortcut. We hope that you found the tutorial helpful.

Other Excel articles you may also like:

• How to Combine Two Columns in Excel (with Space/Comma)
• Excel Shortcuts Not Working – Possible Reasons + How to Fix?
• How To Go to the Top of the Page in Excel (Shortcuts)
• How to Paste without Formatting in Excel (Shortcuts)
• ALT + F4 in Excel
• What Does the Exclamation Point Mean in Excel?
• What Does Control + R Do in Excel?