SUM function and highlighted cells

Question

(Sorry that I can't post a screenshot with this problem.)

In a sheet, I have a SUM function that references a number of cells about thirty rows above the cell with SUM.

When I double-click the cell with the SUM function, the referenced cells get highlighted with a green border, as they should. If, however, I scroll the screen up, so that the SUM cell scrolls below the visible part of the sheet, the green border highlight disappears. This makes it very difficult to see what's referenced by the SUM function.

By the way, this problem is not present in Excel 2016 for Windows. It's been present in the Mac version for quite some time.

Both the Mac and Windows Excels are at the latest version.

Answer 1

Yes, this and other similar problems have been with mac Excel 2016 for some time (forever.) Use the smiley face in the upper right of Excel's menu bar to tell Microsoft of your concern. Keep watching future updates to see if the problem has been addressed.

Answer 2

Same here. Good to hear other people are having the same problem. The problem has been with Excel Mac for years. I always thought I'd maybe just me but I guess not.

Amongst the many bugs and quirks this is the one that drives me insane. Especially if you work with Spreadsheets that have long rows it becomes almost impossible to edit formulas without getting lost.

Answer 3

Thanks!! 15.35 does indeed solve this problem.

The disappointing thing is how months (years?) it took to fix this problem. And, this is a forum sponsored by Microsoft, no less.

Answer 4

Hi.  I saw that behavior just this morning.

I believe the "better" way to do this is to select your formula

and do:   Formula Tab / Trace Precedents.

You will see the cells referenced, even if you select another cell, and scroll away where the formula cell in no longer visible.  

Another advantage of this method is that your cell is not in "Edit Mode", where it could be changed while you are scrolling.

Now, having said that, the mac version just did an auto update to 15.35

The issue reported by the op appears to be fixed.

Answer 5

Try this as a workaround. (I assume you are using Autosum) 

1. Click on Formulas. (after you have entered your numbers.)
2. Click on the first Cell of the sequence, then click the shift key.
3. Now scroll down to the last item in the series, hold shift key down and click that cell
4. Now select the Autosum button.

The above suggestion is not a "Cure for the "bug" this simply a different way to accomplish what you need. 

To select a squence or series in other functions 

1. Type the function if enclosed in Parenenthis  type the () just after function name.
2. Nex click inside the ( )'s
3. Now go to first in the series and Click.

( That cell location should show up inside the Parenthesis (B1 ) then type colon beside thecell location 4. Now go to last cell in series and click in side that cell. 

(you should now see something like  =Function(B1:Z1) or Function(B1:Z1).

_________

Disclaimer:

The questions, discussions, opinions, replies & answers I create, are solely mine and mine alone and do not reflect upon my position as a Community Moderator.