In this article, you’ll learn how to edit a formula using keyboard in Microsoft Excel. Sometimes, you will find yourself clicking back and forth between the cell and the formula bar while entering an editing formula. The following are some tricks that you can use to edit a cell without touching your mouse:-
- Use the arrow keys to move to the cell.
- Press the F2 key to enter edit mode.
- While in edit mode, use these tips to make changes
Let’s take an example and understand:-
We have a score sheet, in which we have calculated total score, average, maximum and minimum score.
- Use the arrow keys if you need to select cells to use in your formula. When selecting cells, remember to press F2 to toggle between edit mode and select mode.
- Press the HOME key to move the cursor to the beginning of the formula.
- Press the END key to move the cursor to the end of the formula.
- Hold down the CTRL key plus the left or right arrow to move quickly through the formula.
- Hold down the SHIFT key and use the left or right key to highlight a part of your formula.
- Press the END key and then CTRL+SHIFT+HOME to select the whole formula.
- If you need to, you can copy (CTRL+C) and paste (CTRL+V) parts of your formula.
- When you’ve finished making changes to your formula, press ENTER to accept the changes or press the ESC key to revert changes in case you have made a mistake.
These are the ways we can edit the formula by using the keyboard’s keys.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at
I have a formula in a cell which counts the values in 2 other cells. Normally, after entering in the values in these 2 other cells, the formula cell updates automatically when Enter is pressed or when the cells are clicked out of.
However, for some reason, the value calculated by the formula does not update automatically. I am having to click on the formula bar, then I have to click enter for the formula cell to do its thing.
Why is this happening?
asked Jul 8, 2011 at 9:03
oshirowanenoshirowanen
2,13014 gold badges60 silver badges78 bronze badges
A likely cause is that Calculation is set to manual. To change this to automatic in the various versions of Excel:
2003: Tools > Options > Calculation > Calculation > Automatic.
2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.
2010 and newer: File > Options > Formulas > Workbook Calculation > Automatic.
On Mac:
- 2008: Excel Preferences > Calculation > Automatically
In some cases you may prefer to keep it set to manual, for example if there are many heavy calculations to perform. In such cases, you can simply press F9 when you want the calculations to update.
TylerH
5077 silver badges20 bronze badges
answered Jul 8, 2011 at 9:08
6
Confirm with Excel 2007: Office button > Excel options > Formulas > Workbook Calculation > Automatic.
Short key to refresh
Ctrl+F9
answered Sep 15, 2013 at 8:24
2
I had a case of this just now on Excel 2010: a particular spreadsheet that would not auto-recalculate. I changed the setting as indicated above; but the auto-recalculate still did not work, and upon rechecking the "Calculation" option, found it had reset itself back to "Manual" all by itself. Three attempts later and it was still adamant it wanted to be "Manual" and nothing else.
So here is my solution to the problem:
Copy the contents of the spreadsheet to a new one, and deleted the old one (it wasn't a particularly important spreadsheet, thankfully), and everything was fine. I can only assume that somehow the file had become corrupted.
A good reason to keep backups.
answered Dec 22, 2014 at 15:21
PuffinPuffin
811 silver badge1 bronze badge
1
Reposting this snippet that user RFB had (inappropriately) attempted to edit into my answer:
A possible cause is that the Office Prefs file is corrupted. In OSX this can be found in:
Library/Group containers/*.office/com.microsoft.officeprefs.plist.Delete this file and restart the OS. A new plist file will be created when Office is restarted. Formulas recalculated again perfectly.
I also have had this problem with a very large spreadsheet that just stopped updating itself over the weekend, but having checked the above solutions, setting were already set to automatic, and sheet is too big to rebuild, so I was at a loss.
My solution was to find an old version of the same excel spreadsheet which was not having thses issuesbefore I archived it, which is of similar size and complexity to the current version.
I opened this spreadsheet, checked that this was still working and autoupdating with current installation of MS Excel and any new automatic office updates (which it did work with), and then just reopened the original spreadsheet. "Hey presto" it was working again.
answered Feb 16, 2015 at 10:47
In my instance I was using a particular add-in called PI Datalink. Somehow PI's Calculate method was no longer running during the normal workbook recalculate. In Settings I had to change Automatic Update command to Full Calculate and then back again. Once the original setting had been restored, the add-in ran as normal.
answered Jun 12, 2015 at 15:26
Steve TaylorSteve Taylor
5342 gold badges5 silver badges25 bronze badges
I ran into an issue where some cells weren't calculating. I checked all the normal things, like cell type, auto calculation, etc...
What I found to be the problem was the formula and the fact that I surrounded numbers with quotes. That probably turned the cell into a text format cell, even though technically the cell was still a number and listed as one under the formats.
I stripped the quotes and the cells calculated as normal.
answered Feb 24, 2015 at 0:06
2