7 uses of Ctrl E – Best Excel shortcut Ever

There are so many useful shortcuts in Excel. Making use of these shortcuts, we become more productive when using Excel.

If I am allowed to use one shortcut only, I will definitely pick “Ctrl E”.

Many of you have heard of Ctrl C or Ctrl X but not many people are aware of the power of “Ctrl E”

In this article, I am going to show you 7 ways of using Ctrl E in Excel. I am sure you will be the most productive employee in the office after watching this.

General use of Ctrl E

Speaking of Ctrl E, most people may realise that it is a shortcut to access the Flash fill feature. However, most people don’t realise how powerful Flash fill feature is. If there is one single most useful shortcut in Excel, I will put my vote to Ctrl E.

Ctrl E activates Flash fill feature. Let me briefly explain what Flash fill is. When we input something, Flash Fill reads our input pattern and generates inputs that are similar to our input. In other words, it guesses what we would like to input based on what we already input.

Isn’t it amazing? As if you have got a little helper who can read your mind.

I have seen many people using Ctrl E at work but not many of them (close to none) are aware that Ctrl E could have done so much more.

That’s why I put together the usages of Ctrl E in this article. I assure you that you will be able to use Ctrl E to the most if you read through this.

What are you waiting for? Let’s begin!

7 usages of Ctrl E in a table

Things to be aware of when using Ctrl E

1. The data format should be standardized.
2. The cell you are going to perform Ctrl E should be directly next to the cell that contains the raw data.
3. You have to perform Ctrl E several times if there is more than one column.

You might also be interested in How to Sum and Count Cells by Color in Excel?

1. Combine cells to create a date

Here we have the year, month, and day information in columns A, B, and C respectively.

What we want to do is to put them together and form a date.

With Ctrl E, you will no longer need to input those manually. You don’t even need to apply a formula.

Steps

1. You will need to input a date manually into the first cell in the date column. By the first cell, I mean cell D2 (the cell below the header).
2. You will need to press Ctrl E.
3. Bang! Now you have got other cells populated as well.

If you are not satisfied with the date format, you can go back to the previous state by pressing Ctrl Z. There you can change the first cell of the column.

Then you can perform the Ctrl E trick again.

2. Unconcatenate

Another usage of Ctrl E is to unconcatenate.

From time to time, we will encounter cells containing more than one set of information.

In the below example, cell A2 contains four different sets of information. They are “Name”, “Sex”, “Age” and “Date” respectively.

To keep the data simple, containing more than one set of data may not be the best practice.

Therefore, we will have to separate them, in Excel’s terms, “unconcatenate” them.

With Ctrl E, you don’t need to go through all those hassles of writing a long and complicated formula.

“Unconcatenation” is in a few clicks.

Steps

1. Enter the data in the first row —> Enter “Ada” in cell B2, “F” in cell C2, “23” in cell D2, “Marketing” in cell E2. This is to tell Excel how you want your cell to be split.
2. Select B2 and press Ctrl E and do this to the remaining columns.

You might also be interested in 5 Types of Graphs to Create with REPT function

3. Retrieve data from cells

Sometimes cells contain more than one set of data and we only want a certain part of it.

With Ctrl E, you can retrieve certain texts or numbers or anything you want in no time.

Steps

1. Enter “2020” in cell B2.
2. Press Ctrl E.

4. Add text to cells

Add text to cells is one of the most frequently performed actions in Excel.

What we usually do is to use the concatenate function or use an ampersand to stick the cells together. This usually means creating columns and inserting cells.

However, if the Excel sheet is a one-off. You don’t actually need to use formula and perfect everything.

Instead, you can try Ctrl E out.

With Ctrl E, you don’t need to add any columns or copy the phrases down.

Steps

1. Enter the sentence (or prefix/suffix) in cell B2.
2. Press Ctrl E.

You might also be interested in How to Sum Intersections of Multiple Ranges (Excel)

5. Add space

Using a formula to add space is not so complicated but with Ctrl E it gets even easier.

It is not just spaces you can add. You can also try ampersand or any other symbols.

Steps

1. Enter the data with space in cell B2.
2. Press Ctrl E.

6. Add bracket

Steps

1. Enter the data with bracket in cell B2.
2. Press Ctrl E.

You might also be interested in Look Up the Last Value in Column/Row in Excel.

7. Re-order

Trust me! This is the simplest way you can ever find when it comes to re-order.

The traditional way of doing this could have been

1. Separate the first name and last name using text-to-columns
2. Remove the trailing space using TRIM function
3. Use CONCATENATE function to join the text strings together in the inverted order

Steps

1. Enter the data with the correct order in cell B2.
2. Press Ctrl E.

Ctrl E has saved you at least 3 minutes on this.

Bonus: Mixed usage

Here I want to show you how powerful Ctrl E can be.

It does more than one thing at a time.

Here I have input cell B2 to cell E2. Then I pressed Ctrl E.

Cell B2 to cell E5 is populated in one second. Amazing!

Can you think of another interesting use of Ctrl E? Share with us in the comment section below.

Hungry for more useful Excel tips like this? Subscribe to our newsletter to make sure you won’t miss out on any of our posts and get exclusive Excel tips!