Excel dragging formulas function keeps defaulting to dragging values

Question

I use Excel for Mac on my Mac mini. Software is current and up to date both in Excel and Apple Operating System..

Whenever I try to drag a formula the program keeps defaulting to show the value not the formula.

All preferences should enable the formulas to show:

Calculation is automatic.

Fill handle and dragging and dropping is on. (It always allows dragging but then shows values not formulae).

I usually have to manually enter the formula in the new cell and then it starts to show the formulae from then on. UNTIL NEXT TIME I GO INTO EXCEL.

Some of the spreadsheets were created in earlier versions of Excel but have all been updated ("saved as") in the current format with the current extensions.

Can't see any other answers for this specific set of problems. Sorry if it's there and I have mixed it.

The problem is not improving or going away.

Answer 1

I'm sorry Colin but I cannot reproduce what I understand you trying to describe regardless of the steps I take. Neither have you provided access to the workbook nor the other requested information.

Regrettably, I don't see how I can be of any help. All I can suggest is that you use a different method. Simultaneous actions on multiple sheets [?] often yields unexpected results.

Answer 2

What you describe sounds perfectly normal. When dragging a cell containing a formula or using the Fill Handle the result of the formula is shown in the destination cell. The formula, itself, displays in the Formula Bar. The difference between the two methods is that dragging the cell retains the same formula regardless of reference type but use of the Fill Handle causes the formula to update based on its new location unless it contains Absolute References.

The exception would be if Show Formulas is selected on the Formulas tab. That device toggles the Formulas check box in Excel> Preferences - View.

If I've misunderstood please provide a more detailed description supported by screen shots. Also, always include specific macOS & Office Version Numbers when posting — No reflection on you, its just that the way both Apple & Microsoft are doing things. Currently there are 3 supported editions of macOS & at least 6 supported editions of Mac Office, not to mention the number of unsupported editions of each still in use.

Answer 3

Hi Bob.

Thanks for your reply.

I think I may have been unclear in what I wrote.

An simple example of what I meant to describe.

1. Cell A1 - value of 1.
2. Cell A2 - formula of A1 plus 1
3. Cells 3, 4, etc - value of previous cell plus 1, so that the cells show 2, 3, 4, 5, etc. DISPLAYING THE VALUE NOT THE FORMULA.
4. What IS happening is that cells 2, 3, 4 , 5, etc show the value of 1 (as in cell 1) not the value of the formulae.

I hope that has clarified things.

And all.the boxes are ticked (unless there's a box I don't know about - and I can be a techno-idiot at times) to calculate immediately and show the value of the formula (not show the formula itself as I seem to have indicated).

Again, thanks for your help.

Answer 4

I really don't understand — the description isn't clear as it provides no specific examples of what is being entered. I'm sure it's clear to you but I can't look over your shoulder 🙂

What is displayed in the Formula Bar when you select cell A3, A4, etc.?Please provide all of the information originally requested.

It would be best if you save the file to OneDrive & paste a share link into a reply here. There are instructions here if you need them: Share OneDrive files and folders

I cannot reproduce the result you are describing. Starting with 1 in cell A1 & using the Fill Handle I get the following:

	Entry	Result in Cell
A1	1	1
A2	=A1+1	2
A3	=A2+1	3
A4	=A3+1	4
A5	=A4+1	5

I get the same result when manually entering the formulae rather than using the Fill Handle

Answer 5

Hi again. Don't know why I hadn't notices it before but I've just picked up that the problem only occurs when I am dragging a formula on 2 or more worksheets at once.

I have a couple of workbooks with information on 2 and 5 pages each of which I sort into different orders for various reasons.

The issue has only developed pretty recently (noticed it about a month ago) so SOMETHING HAS CHANGED. Probably after I.upgraded from an ancient Imac to a Mac Mini (much newer operating system of course).

But the fact remains that this problem did NOT exist previously and SHOULD NOT exist now.

The problem, to recap is:

CELL. ENTRY. RESULT IN CELL

A1. 1. 1

A2. =(A1+1) 1

A3. =(A2+1) 1

A4. =(A3+1) 1

and so on (but it looks like only when work on more than.one worksheet at a time).

I hope this helps clarify