problem in excel-PLEASE it very important

[zoharb]

Respected,
I have a big file of 400MB+ which has lot of functions.
Just 15 days ago I was able to copy a line of 50+functions to another place in just 2-3 seconds
but now it takes 2-3 minutes.
I have tried every thing like
(1)completely writing new formulas so that there might be some routine that was delaying but it is not working
(2)completely making new workbook from scratch.
(3)Has removed all conditional formating, unicode characters , iterations
But nothing is solving my problem
Can anyone please guide me
OR
someone please show some utility/application which points out particular formula which causes that lag
PLEASE it very important

 

[Yongle]

Which version of Excel are you using?

Your file is possibly a lot bigger that it should be - Excel remembers the last cell and that affects the memory
You need to find out which cell Excel thinks is your last cell

In Excel 2016
Home Tab \ find \ Go To Special \ Last Cell

If the last cell is outside the true data range
- select all columns to the right of your data range and delete those columns (the actual columns not the only values)
- select all rows below your data range and delete those rows (the actual rows not only the values)

GoTo Last Cell again - is it correct?
- if not then you probably have some formatting or conditional formatting rules that need to be amended (are you using comlete columns when you could specify a much smaller range etc?)

 

[zoharb]

Respected,
I have excel 2016 ----CPU is i3-8100---RAM is 8 GB DDR4
Has kept dual fan so that the system do not heat up.

Has removed all extra columns(2 columns was extra)
Still the same issue is there
Previously I was working on 800+ MB worksheet and it was working properly but when I modified some formula(calculating weekly data) , the problem started. So I had saved a version that was having that old formula.
That version was working fine initially.
Then I trimmed it by 400+MB so now it is at 400+ MB
but shows the problem.
I have to copy paste 223 lines of 113 functions daily and this is taking time . So needs solution
PLEASE HELP ME.
Zohar Batterywala

 

[Yongle]

Try this

set calculations to manual: File \ Options \ Formulas \ Calculation Options Manual

Paste all your data

Hit function key {F9} to recalculate

 

[zoharb]

Respected ,
Has changed the mode of formula calulation to manual from File \ Options \ Formulas
Then saved(it is getting saved in 1 minute as usual)
even then pasting of formulas take 2-3 minutes
Zohar Batterwala

 

[Yongle]

Does your workbook contain array formulas?

Does your workbook contain many sheets?

 

[Yongle]

Here is a few other things that can help speed up your workbook
(This list was found here)

Click "File" at the top of the screen and select "Options" to open the Options window. Click on "Formulas." Place a check mark next to "Enable iterative calculation" if your worksheet has intentional circular references, as this will allow Excel to stop endlessly calculating. Click "Advanced" on the left side of the Options window and scroll down to the "Formulas" section. Place a check mark next to "Enable multi-threaded calculation," and click the radio button next to "Use all processors on this computer." Click "OK" to close the window.

Move all of your data to a single worksheet, if possible. It takes Excel longer to calculate if it has to pull data from a different sheet. This solution may not be possible, or desirable, if the data is on a different worksheet for design or access reasons.

Check your worksheet's formulas for any repeated calculations that you can change to static references. For example, if you have thousands of formulas down column "C," and they all have "($A$1+$B$1)" as part of their formula, it means that Excel will make that calculation thousands of times whenever it calculates the worksheet. Find an empty cell on the worksheet and type that portion of the formula into the cell; then change the formulas to reference that particular cell instead of performing the calculation. You will now save valuable calculation time, as Excel will only need to make that calculation once.

Sort your columns of data, if possible. Excel functions that look up data run much faster on data that is already sorted. You can sort a column by selecting the column and then pressing the "Sort and Filter" button under the "Home" tab. Choose "Sort A to Z" or "Sort Z to A."

Remove volatile functions and replace them with non-volatile ones wherever feasible. In Excel, volatile functions are those that will always recalculate every time any part of the worksheet changes. The seven volatile functions are RAND, NOW, TODAY, OFFSET, CELL, INDIRECT and INFO. It's not always possible to find another way to recreate formulas without these functions, but if you can, it will certainly speed up Excel's calculations

 

[zoharb]

Respected,
I have cleared all doubts
(1)No iterative calculation
(2)NO extra datasheet
(3)there are static calculation in sheet but they are non-repetitive(means when there are static calculatins , I had ensured that other function will have changed reference cell(as that is the basic prerequisite to get correct output)
(4Sorting is not an issue
(5)has not used RAND, NOW, TODAY, OFFSET, CELL, INDIRECT and INFO functions
If you can please I can send you the complete file so you can do a quick diagnosis at your end.Part is not possible as with very less data, this delay is not seen AS I have seen that when I remade the sheet , initially the things was okay.
Zohar Batterywala

 

[Yongle]

How many rows of data?
how many columns in each row?
how many columns contain formula?
do any cells contain array formula?

 

[Smitty]

If you can please I can send you the complete file so you can do a quick diagnosis at your end.

You're welcome to post a copy of your workbook to a file sharing site, then post the link here. Just make sure to remove any sensitive personal information.

Regarding a 400+ Mb workbook, even if you've made a lot of accommodations to make Excel work well, I'd still be looking at what you're trying to do before wondering why Excel won't support it. I don't know your data situation, but have you looked at big data modeling options, like Power Pivot?