Excel 365 Performance

[Ziggy]

Hi,

I've been using Excel 365, I recently had an issue where I started having an issue where in any Excel file ( a new one for example), if I type in a cell and hit Enter, it could take about 1-3 seconds to move to the next cell. There is no formula.

I have a new system with I7 @ 16 GB RAM

Resources seem fine... so I started looking at all my excel files. I noticed that 365 uses significantly more resources than Excel 2016... yest my system still has the power, so ultimately I traced it to 1 Excel file which caused the issue of lag whenever it was opened.

This Excel file was only 3MB, but has hundreds of formulas but no External links... so it seems that this file even if I don't edit it seems to get linked to calculation logic ( Auto Save is off).... does this make sense? I don't understand why it affects other Excel files. I do have automatic Calculation ON, but again that should not need to recalculate if another file which is not linked in any way is worked on? The file with the formulas is on One Drive, but as I said I only opened it... so the other odd thing is with the 2 files open, if I type in the NEW book and watch the file with the formulas I see a cell flash with the calculation on 1 cell.. that is sooo odd.... there is absolutely no link!!!

 

[Fluff]

Do you use any volatile functions such as Offset, Indirect, Today, Now?

 

[Ziggy]

Do you use any volatile functions such as Offset, Indirect, Today, Now?

Thanks, I thought of that and I removed TODAY, and relative formatting. I do see OFFSET used extensively ( I did not set this file up), but I can't remove that one.

 

[Fluff]

That's the problem. Volatile functions will recalculate whenever any cell in any open workbook is changed or recalculates.

 

[Ziggy]

That's the problem. Volatile functions will recalculate whenever any cell in any open workbook is changed or recalculates.

ok, but why does the Excel file ( with the formulas) flash one of the cells with a Date Value where there is no formatting or formula ( I Cleared all)

 

[Fluff]

I have no idea, especially as I cannot see what's happening.

 

[Ziggy]

I have no idea, especially as I cannot see what's happening.

thanks for your reply, you did answer the reason ( OFFSET is Volatile), I'm going to rework the formula to remove it. I will have to check how many changes I need to make. I'll let you know if that solves it.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Ziggy]

So I scrubbed all sheets and removed any Volotile functions. I searched for these:

Here is a list of Volatile functions

• CELL
• INDIRECT
• INFO
• NOW
• TODAY
• OFFSET
• RAND
• RANDARRAY
• RANDBETWEEN

I only had TODAY, OFFSET in the file. I also deleted conditional Formatting references with these functions.


Yest the problem still exists, so I had noted that I observed a cell flashing on the screen when typing in another book, first a different set of cells flashed after deleting formulas, and at the moment I do not see this happening, however I tested the following:

BOOKA = Blank book no formula , just a new book
BOOKB = work book with many formulas (Volatile removed)

**BOOKA remains open,

TEST1

- I close BOOKB and l reopen and leave it open for about a minute, and do not touch or change anything
- BOOKB is closed, no prompt to SAVE ( Expected).

TEST2

- I close BOOKB and l reopen and leave it open for about a minute, and do not touch or change anything
- I go to BOOKA and I type some random numbers/ letters
- I return to BOOKB and close it
- Excel now asks me to SAVE the file


so WTH is going on, why is another workbook affecting this file.

 

[Fluff]

Sounds like you might still have some volatile functions in bookb