Excel expert needed

[Ipwn 4]

Hi Guys,

I am look for an excel expert to assist with an issue we currently have:
a client of ours is using spreadsheets that are generated from dumps from their accounting system. These workbooks are all over 7MB in size and cause frequent crashes of Excel.

Most of what I can find suggests that the sheets are badly created but I honestly don't know enough about accounting to make sense of half of what they are trying to accomplish. We have tested on multiple systems which multiple OSs and versions of Excel, the only common denominator is the workbook itself.

I need someone who has experience dealing with sheets of this size and running Excel efficiently to take a look and either confirm our findings or find the actual problem. You will be paid for your time spent.

Drop me a PM if you know of someone, thanks in advance!

 

[LaraC]

It is not unusual for Excel to crash when you export more than 65000 records. If you say the file is 7MB it would reasonable to assume that you are working with large amounts of data.

 

[LaraC]

Make sure that the data you are exporting does not exceed limitation for Excel.

https://support.office.com/en-au/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f

 

[agentrfr]

Excel is carp at handling large amounts of data. Your accounting software should be able to dump the data in .csv files, from where you can manage it from a program like notepad++ (specifically built to handle large files, BUT IT DOES NOT DO PROCESSING - i.e. you can use it to look through a .csv file and pluck out the pieces you need to process in an excel sheet) or you can go the MS Access database route - while will require some sql management.

 

[MickeyD]

My one file was 75MB with over 700 000 records.

The problem came in when inserting formulas, formatting and macros. As a data file it was fine.

 

[Willy Strong]

Export to .csv and then import to Excel manually.

 

[LaraC]

My one file was 75MB with over 700 000 records.

The problem came in when inserting formulas, formatting and macros. As a data file it was fine.

It is only the latest versions of Excel using the *.xlsx extension that support a million rows. If the financial program exports to *.xls it is severely limited.

 

[MickeyD]

It is only the latest versions of Excel using the *.xlsx extension that support a million rows. If the financial exports to *.xls it is severely limited.

Correct.

 

[mak2000]

What version of Office is your client using. Also the spec of the machine makes a difference if you have complicated formulas and a lot of data. maybe try a higher specced PC.

 

[Ipwn 4]

Don't know how the data is getting into Excel but it is all dumped inside of a single sheet (thousands and thousands of rows and a few hundred columns) and from there every other sheet pulls data for its formulas. The accounting partner has explained to me that they do a dump from their software (a saas app) then add(not sure if this is a paste or import) into this sheet. Once inside this sheet, data is in number format with about 60% of the cells containing zero's (due to the layout of the data inside of that sheet).

 

[LaraC]

Don't know how the data is getting into Excel but it is all dumped inside of a single sheet (thousands and thousands of rows and a few hundred columns) and from there every other sheet pulls data for its formulas. The accounting partner has explained to me that they do a dump from their software (a saas app) then add(not sure if this is a paste or import) into this sheet. Once inside this sheet, data is in number format with about 60% of the cells containing zero's (due to the layout of the data inside of that sheet).

When you say it pulls data do you refer to hyperlinks?

 

[Ipwn 4]

What version of Office is your client using. Also the spec of the machine makes a difference if you have complicated formulas and a lot of data. maybe try a higher specced PC.

Performance has been ruled out, we have recreated the issue on various devices and the results remain consistent.
It also happens on any version of Windows (64 and 32) and also any version of office hence us now becoming desperate to find someone who can pin point the inefficient formulas.

 

[Ipwn 4]

When you say it pulls data do you refer to hyperlinks?

No simply references the values inside of this sheet inside of its own formulas.
Sorry if I used the wrong term.

 

[Electron1]

What are they doing with the data?
Maybe they would be better off generating reports using something like qlikview.

 

[LaraC]

Which error code is reported?
Office usually records what happens under applications and service logs within the Windows Event Viewer.

 

[Ipwn 4]

event logs are clean, just tested again. It's not a crash but more of a "hang" for about 30 seconds when closing the sheet. No changes have been made and nothing is being saved when the sheet closes, Excel just hangs.

I have been running process monitor to see if I can see anything there but Excel just seems to go quiet for 30 seconds then starts appearing again. During the quiet period Excel is hanging.

 

[Coasti]

Have you looked into using the Powerpivot extention for Excel? You can drop almost any data source into it and it can handle unlimited number of rows. It is a very powerful analytical tool.

 

[LaraC]

Too eliminate error handling as a possible reason is it possible for you to change excels formula options?
Deselect Recalculate Workbook before saving and all the error checking options.

 

[Ipwn 4]

Have you looked into using the Powerpivot extention for Excel? You can drop almost any data source into it and it can handle unlimited number of rows. It is a very powerful analytical tool.

I am trying to avoid rebuilding the sheet as far as possible as it is quite frankly, not my job. The sheet is rather complex and calculating finials which I don't fully understand so the time spent figuring out what is what, implementing a more efficient formula and then verifying the results is not feasible.

Too eliminate error handling as a possible reason is it possible for you to change excels formula options?
Deselect Recalculate Workbook before saving and all the error checking options.

Thanks for the suggestion, will look into this now.

 

[Ipwn 4]

Too eliminate error handling as a possible reason is it possible for you to change excels formula options?
Deselect Recalculate Workbook before saving and all the error checking options.

Done, no improvement.
What is interesting is that this only happens when Excel is already open.

If you open the workbook by clicking on it directly it closes instantly. If you open a blank sheet and then open the large sheet the large sheet it slow when closing.