Control E in Excel – What Does it Do?

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). 

Dataset with ISBN number

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.

Dataset with ISBN number

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
Enter the expected result in cell

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.

  1. Select cell C3 and press Ctrl + E.

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

Control E Result

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. 

Flash Fill gives wrong result

When we press Enter, Flash Fill gets everything right:

Flash Fill gives right result
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.

Full name dataset

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:

Add two columns for first name and last name

We proceed as follows:

  1. Select cell B2 and enter the first name “James”:
enter first name in cell B2
  1. Select cell B3 and press Ctrl + E.

The rest of the first names are filled in automatically:

Control E gives result for first name
  1. Select cell C2 and enter the name last name “Smith”:
Enter last name in cell C2
  1. Select cell C3 and press Ctrl + E.

The rest of the second names are filled in automatically:

Control E gives result for last name

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. 

Dataset

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.”
Enter result in cell B2
  1. Select cell B3 and press Ctrl + E.

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

Control E gives result
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:

Full name dataset

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:

Dataset to rearrange name using Control E

We use the below steps:

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

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

names rearranged using control E
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. 

names in Lowercase Dataset

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:

add additional column to use control E

We proceed as follows:

  1. Select cell B2 and enter the function displayed in cell A2 but in all uppercase, “UPPERCASE.”
enter the first expected result in Cell B2
  1. Select cell B3 and press Ctrl + E. 

All the functions are converted to all uppercase.

use central E to fill the entire column using flash fill
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.

full name in lower case data set

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.”
enter name in uppercase in Cell B2
  1. Select cell B3 and press Ctrl + E.

All the full names are converted to a proper case. 

use control E to change the case of the text
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).”
enter the data in expected format in cell B2
  1. Select cell B3 and press Ctrl + E. 

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

use control E to change the format of the text
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:

employee name Dataset

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

enter the email address to use in flash fill
  1. Select cell B3 and press Ctrl + E.

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

use control E to get the result

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. 

date in Julian format

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).
enter the year value from the Julian date in Cell B2
  1. Select cell B3 and press Ctrl + E.

The years are promptly retrieved from the Julian dates:

control E to extract year value from 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. 

day month and year values in separate cells

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.
enter the combined date in cell D2
  1. 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.

use control E to get all the dates in the right format

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:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

2 thoughts on “Control E in Excel – What Does it Do?”

  1. I made a list of all my checking transactions at my JPM Chase account and copied into an Excel spreadsheet… unfortunately all of the entries were in all caps. I wanted to change the Description column only into sentence case. As you say, Excel is unable to change this into sentence case as in MS Word. So I copied the list of (300) lines of the column in question into a Word file; it copied into Word in table format. I then was able to highlight all of the rows of this table and then cut and paste again back into the Excel spreadsheet column in question – which it overwrote with the now sentence case format as desired. Voila.

    Reply

Leave a Comment