One of the first things I learnt when helping people with Excel problems was the necessity of spending enough time making sure they have described the problem properly, before actually trying to come up with a solution. ‘My decimal point key has stopped working’ or ‘the SUM() function is broken’ might be symptoms of completely different issues, many of which can lie hidden within Excel options.
Introduction
If Excel suddenly starts doing strange things, as well as blaming the latest update, it’s also worth checking Excel options. More than once, I have traced seemingly insoluble Excel issues to an unanticipated change to an Excel option. Excel Options can be found from the File Ribbon tab, Options command which displays the Excel Options dialog. Within the dialog, the options are arranged in several different categories:
We are going to pick out five Excel options that can lead to keyboard-thumping frustration if you don’t realise that they have been changed.
What’s happened to my decimal points?
Problem:
Excel starts automatically changing the numbers you enter so that the last two digits come after a decimal point that you never typed in. You type in 120, Excel displays 1.2.
Option:
Advanced, Editing options section, Automatically insert a decimal point:
If turned on, this option will assume that all amounts are entered including the chosen number of decimal places, without the need to type in any decimal points.
I can’t edit my cell contents anymore
Problem:
When you double-click on a cell, nothing happens so you can’t see any formula within the cell or change the contents in the cell, or another cell or cell range is selected.
Option:
Advanced, Editing options section, Allow editing directly in cells:
If this option is turned off, you will only be able to edit cell contents using the formula bar. Where a cell refers to one or more other cells or cell ranges, double-clicking on the cell will select the cells referred to.
Problem:
The fill handle has stopped working.
Option:
Advanced, Editing options section, Enable fill handle and cell drag-and-drop
If this option is turned off, the fill handle will still be visible at the bottom right-hand corner of a selected range, but when you hover the cursor over the fill handle it will just stay as a cell selection cursor rather than a sold black plus sign. Trying to drag the fill handle will just change the selection rather than filling the range. Also, it is not possible to drag a cell or cell range to a new location.
Problem:
All my cell references have changed to Rs and Cs
or:
All my column headings have changed from letters to numbers
Option:
Formulas, Working with formulas section, R1C1 reference style
When turned on, R1C1 reference style will change all cell references to show the relative position of the cell referenced from the cell containing the reference. If cell D5 contains a reference to A1, the formula will not show as =A1 but rather as =R[-4]C[-3]. The numbers in the square brackets represent the number of rows or columns to move. Positive numbers move down for rows and right for columns, negative numbers move up for rows and left for columns, so R[-4] is four rows up and C[-3] is three columns left.
Switching to R1C1 reference style also causes the cell headings to switch to numbers making it easier to confirm that an Excel worksheet really does have 16,384 columns.
Problem:
My SUM() functions have stopped working (where SUM can be replaced by the name of any other Excel function or calculation operation).
Option:
Formulas, Calculation options section, Manual
This is a very common problem, made more frequent because setting the calculation mode to manual in one workbook causes other workbooks to switch to manual mode. Not only will other open workbooks be switched to manual, but new workbooks and other workbooks you open in the same session will also be switched to manual. Also, saving a workbook while the calculation mode is set as manual will cause the calculation mode to be remembered, so that when that workbook is next opened it will open in manual calculation mode and will change the calculation mode for other workbooks opened in the same session.
As if all that wasn’t bad enough, it’s quite easy to change to calculation mode. Most of the other options we have considered require you to go to File Options and select and change the appropriate setting. However, the Calculation mode can be changed directly from the Calculation Options dropdown in the Calculation group of the Formulas Ribbon tab.
Often when this problem occurs, because people have changed a value and noticed that a particular formula doesn’t reflect the change, they assume that it is just that calculation or function that has stopped working, rather than realising that all calculations have become manual.
Links
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.