• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Curse You Excel Cursor

You are here: Home / Excel / Curse You Excel Cursor
Curse You Excel Cursor
January 30, 2014 by Mynda Treacy

The Scenario

You need to change the range referenced by your chart/conditional formatting/named range so you merrily open the Edit dialog box, like the one below for a chart, and start using your arrow keys to move the cursor into position, except all of a sudden the range referenced starts changing before your eyes.

Excel Cursor Focus when editing charts

 

What’s going on! All you want to do is move the cursor position within the existing reference but Excel appears to have a mind of its own and it doesn’t matter which arrow key you press it continues to change your cell references.

Aaargh, curse you cursor!

The Reason

The reason Excel does this is because the focus of your cursor is actually in the worksheet. You can see in the image below that even though the flashing cursor appears at the end of the series name field the active cell is actually B4.

Excel Cursor arrow keys not working

Pressing the arrow key once to the right will move the active cell to C4 and you'll see the reference in the Series Name field automatically update to =Sheet1’$C$4 like this:

Excel Cursor arrow keys changes reference

I don’t know about you but I find this about as helpful as a solar powered torch [that's a flashlight for those in the USA].

Don’t get me wrong, I understand the logic but most of the time I just want to type right over the reference myself. After all, I’ve opened the ‘edit’ dialog box, now let me edit it.

The Solution

Excel Cursor f2 to change focus

 

That’s it!

Ok, I’ll explain a bit more. The F2 key shifts the focus of the cursor to the ‘Edit’ dialog box.

After pressing F2 you can see below that the worksheet cell, C4, no longer has the marching ants around it:

Excel Cursor arrow keys working

This means the focus of my cursor is now in the ‘Edit’ dialog box.

Now you can merrily use your arrow keys to move the cursor to where you want without it wreaking havoc with your reference.

To shift the focus back to cell C4 simply press F2 again.

This also applies to other dialog boxes you find when working with:

  • Conditional Formatting
  • Name Manager
  • PivotTables and more.

[Update] Another clue as to what Excel is doing can be found in the Status Bar at the bottom left of the screen. You can use your arrow keys in the dialog box when it says 'Edit'.

Here are some screen shots of the different modes you might see:

Excel status messages

If you can't see the mode, right click on the status bar (bottom of your Excel window) and choose 'Cell Mode'.

Thanks, Jon and pmsocho who reminded me of this. [end update]

Curse You Excel Cursor
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Posts

8 Excel in-built AI Tools

Built-in Excel AI Tools

Clean, analyze and visualize data with these Official Excel AI tools by Microsoft. Including formula writing, PivotTables, Charts and more.
excel date and time

Excel Date and Time

Everything you need to know about Excel date and time. Includes comprehensive workbook with every date and time function, plus PDF version.
10 mistakes to avoid when using excel formatting

10 Mistakes to Avoid With Excel Formatting

These are the Excel formatting habits that drive me crazy and what you should do instead to ensure you use formatting effectively.
linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.


Category: Excel
Previous Post:Charting Disparate Data in Excel – 3 Solutions and 1 Crazy MessCharting Disparate Data in Excel – 3 Solutions and 1 Crazy Mess
Next Post:Convert Numbers (Currency) to Words With Excel VBAConvert Numbers (Currency) to Words With Excel VBA

Reader Interactions

Comments

  1. René

    July 24, 2015 at 12:17 am

    Hi Mynda,

    the same goes for editing a formula in a cell: pressing F2 switches from cell select mode to edit mode. I thought to remember that it was toggling, but once in edit mode, pressing F2 again, and then clicking in a different cell, or using the arrow keys, you are just selecting the other cell, and finishing the formula you were working on (maybe just an Excel 2007 issue?).

    Reply
    • Mynda Treacy

      July 24, 2015 at 10:41 am

      Hi René,

      Yes, F2 in the cell works slightly differently depending on whether you have a formula in the cell or not. It’s like that in all versions of Excel…as far as I know.

      Mynda

      Reply
    • Catalin Bombea

      July 24, 2015 at 4:28 pm

      Hi René,
      Excel works in misterious ways sometimes:

      In some specific cases, Excel will start operating directly in Point Mode, even if you click inside the formula, with the natural intention to manually edit the reference; if you’re not paying attention to the Input Mode shown in Status bar, if you use the arrow keys or you click on a cell, Excel will insert that reference in the middle of your reference text. And this can be very annoying…

      These cases, where Excel will start operating directly in Point Mode are: (may be more cases, if you have experienced these unusual behaviour in situations not listed here, please let us know)

      – When creating or editing a Defined Name, while working inside Refers To field,
      – When you create or edit a Conditional Formatting formula,
      – When you create or edit a chart series.

      If you are aware that Excel starts working directly in Point Mode in the 3 cases described above, and you intend to manually edit the formula or the chart series, all you have to do is hit F2 once, and Excel will listen to you like a well trained puppy…
      Keep in mind that Excel will switch to Point Mode only from Enter Mode, NOT from Edit Mode! There is no command to manually switch to Point Mode!

      Assuming that you worked with Excel in Point Mode, and you pressed F2 once to work in Edit Mode, if you want to operate again in Point Mode after editing the formula, with Excel in Edit Mode, you have to press F2 again to switch from Edit Mode to Enter Mode, then you will be able to use the arrow keys or mouse to select ranges for your formula.
      Switching between Input Modes:

      – From Point Mode, you can switch to Edit Mode by Pressing the F2 key once;
      – From Point Mode, you can switch to Enter Mode by Pressing the F2 key twice;
      – From Edit Mode, you can switch to Enter Mode by Pressing the F2 key once.

      Hope this will clarify some unexpected Excel behaviours.
      Cheers,
      Catalin

      Reply
      • René

        July 24, 2015 at 4:45 pm

        Hi Catalin,

        I had no idea there are three modes working in a cell. Thanks for the info, I am going to check it out to get an understanding of the differences between point and enter mode.

        kind regards,
        René

        Reply
  2. Shabbir

    February 4, 2014 at 2:44 pm

    Thanks for that simple tip which was tormenting me for years.

    Reply
    • Mynda Treacy

      February 4, 2014 at 2:52 pm

      🙂 You’re welcome, Shabbir.

      Reply
  3. pmsocho

    January 31, 2014 at 7:30 am

    That is bugging people all the time! It is one of my gold tricks during my Excel classes because almost no one knows about that 🙂

    Mynda, update your great article and add the info, that the you can always see the current mode (edit or point/enter) in the beginning of the status bar of an Excel window 🙂

    Reply
    • Mynda Treacy

      January 31, 2014 at 10:56 am

      @ Lynda, glad you liked it. Now you’ll be unstoppable 🙂

      @ Jon thanks for the reminder about the cell mode. I forgot about that…probably because I never use it! I’ve updated the post.

      @ pmsocho sometimes it’s the little tips that can make a big difference. I’ve added the update about the cell mode. Thanks for the reminder.

      Cheers,

      Mynda.

      Reply
  4. Jon Acampora

    January 31, 2014 at 3:09 am

    Hi Mynda,

    It might be worth noting that the Cell Mode (Enter/Edit) is displayed in the bottom left corner of the Excel application window in the status bar. When you press F2, the words “Enter” or “Edit” will be displayed there. This lets you know what mode you’re in. I also find it frustrating to do the accidental arrow key press and get unexpected results in the input box.

    If the Cell Mode is not displayed in your status bar, right click anywhere on the status bar and select Cell Mode from the drop-down.

    Thanks for sharing this great tip!

    Reply
  5. Lynda Maynard

    January 31, 2014 at 2:10 am

    Thank you thank you thank you!!! That has got to be one of the most useful tips ever. I do a lot of “copy chart & edit source data”, usually just changing the row numbers, and this has bugged me and slowed me down for years.

    And it works in the Conditional Formatting dialog box as well. Between those 2, I’ll be using this tip almost every day!

    Reply
  6. Douwe

    January 30, 2014 at 11:17 pm

    Awesome! I always have this problem with adjusting conditional formatting. Simple but great tip!

    Reply
    • Mynda Treacy

      January 31, 2014 at 7:55 am

      Cheers, Douwe 🙂

      Reply
  7. Bryan Metz

    January 30, 2014 at 9:54 pm

    Ugh, I have this problem all the time! I already knew about the F2 solution, but it’s still easy to forget if you are in “edit” mode or “annoying” mode. Heck, half the time when I intentionally put it into “annoying” mode it STILL doesn’t work like I want it to. “=Sheet1!A1:A4+Sheet1!B1:B4”? Uhhhh if you won’t take it as a valid reference then don’t automatically put it there for me.

    Reply
    • Mynda Treacy

      January 30, 2014 at 10:01 pm

      I hear you, Bryan. 🙂

      And if you’re in “annoying mode” and you don’t realise it the damage is best repaired with ESC. Then you have to start again.

      Reply
  8. MF

    January 30, 2014 at 6:31 pm

    Hi Mynda,
    This is simply wonderful! Never think of a solution as simple as this. Thanks for sharing!

    Reply
    • Mynda Treacy

      January 30, 2014 at 6:50 pm

      Thanks, MF. Glad you liked it.

      Reply
  9. Dave

    January 30, 2014 at 12:30 pm

    Mynda, that’s an awesome tip! That has bugged me for sooooo long! Not that I ever tried to find a solution or even try and understand what it was doing so I can’t complain too much! That’s a very simple explanation thank you!

    Reply
    • Mynda Treacy

      January 30, 2014 at 12:35 pm

      Me too, Dave. Me too!

      Glad you found it helpful 🙂

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

office scripts course

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x