Excel’s Little-Known Cell Modes

No matter how experienced you are with Excel, there is always something you wish you knew earlier. There’s a good chance you weren’t already aware of the different cell modes that exist in Excel. However, it’s worth being aware of them because they can save you time in certain instances.

You will see the current cell mode at the bottom-left of your Excel window.

If this is not showing, ensure that Cell Mode is ticked when you right-click on the status bar.

Ready

Ready is the default mode and indicates that Excel is waiting for user input. It will continue to appear providing you are not editing a cell, defined name, conditional formatting formula or chart range.

Edit

Edit mode will be activated when any of the following events occur:

• double-clicking on a non-empty cell
• clicking in the formula bar—irrespective of whether the active cell is empty or non-empty
• pressing the F2 key in the active cell

You cannot navigate your way around the worksheet using your keyboard’s directional keys whilst in this mode.

Enter

Double-clicking on a blank cell or typing directly into a cell will activate Enter mode. However, if you click in the formula bar and start typing, Edit mode will be invoked instead.

Enter will also appear when working inside an argument of a function — if a cell reference has been not been selected.

Unlike with Edit mode, you can use your keys to select cell references, however, Excel will automatically jump to Point mode when doing so.

Point

Point mode is better translated to cell selection mode, as it activates whenever you select a cell in a formula. Remember: your formula must start off with either ‘=’, ‘+’ or ‘-’ to be read correctly.

It is not possible to switch to Point mode manually using F2 like with Edit and Enter. It is only through selecting a cell via those two modes that Point will activate.

These principles apply elsewhere, too

The examples shown are based on cells and the formula bar, however, the concept is the same elsewhere.

One of the biggest benefits of switching between cell modes is for defined names and conditional formatting.

Have you ever tried to edit a formula and attempted to move the text cursor across the formula, only to find it’s changing the cell reference instead?

This was a problem I ran into so many times since I started using Excel about two decades ago. I thought there wasn’t a workaround, so would reluctantly use my mouse to control the text cursor.

One of the problems is that the defined names and conditional formatting formula boxes are only really designed for short formulas. You can’t use line breaks (Alt + Return) in these formulas like you can in the regular formula bar. As soon as you start working with longer ones, they become difficult to navigate as they partially get cut-off—even after widening the window.

You are prevented from using your keyboard’s directional keys to move the text cursor around because the default behaviour is based on Point mode, so you can only select cell references.

What you end up doing is highlighting the visible part of the formula and dragging the cursor towards the left, just to get to the first part.

See the example below, which shows exactly what I mean…

It’s all rather unconventional — until you find out about cell modes!

By pressing F2 to go into Edit mode, you can hop about in the formula on your keyboard.

It surprises me a little that Microsoft hasn’t made Edit mode the default for the defined names and conditional formatting formula bars. I certainly don’t ever use the directional arrow keys on my keyboard to add and change cell references — I do that with my mouse.

In any case, hopefully now you will not have the same issue I had for so many years!