PDA

View Full Version : Large Spreadsheet Calculations



markstro
10-08-2015, 07:29 AM
What is the limit on calculations performed by Excel 2010. I have a sheet with 43 columns, total rows are currently 58,638 and will grow to around 90,000 by the end of the year.

It takes forever to open and save the file, I am using numerous Vlookups, concatenates and calculations throughout the sheet.
I can't send the sheet as it contains proprietary pricing for a uniform service.

Just looking for any suggestions on how to improve the time to save and open the file, if there are any at all!

Aflatoon
10-08-2015, 07:44 AM
What kind of VLOOKUPs and do you use any volatile functions like OFFSET or INDIRECT?

markstro
10-08-2015, 08:02 AM
Not sure what kind of VLOOKUP you mean, pretty simple I guess. Nothing like OFFSET and INDIRECT at all.
Here's what I can tell you.
C=Date
D=Text(c:"YY")
E=Text(c:"mmyy")
F=Weeknum
J=IF($O4="u",IF($I4=$I5,0,1))
M=CONCATENATE K & L
O-R=VLOOKUP'S WITH DATA IN ANOTHER SHEET
S=CONCATENATE Q & R
W= VLOOKUP
X=CONCATENATE Y AA AE
AB=CONCATENATE AA AE
AF=CONCATENATE N AA AE W AL
AG=VLOOKUP
AS-AW= IF(AND) WITH A VLOOKUP AS WELL

Hope that helps, not sure if I have loaded this spreadsheet with too many things going on.

Aflatoon
10-08-2015, 08:44 AM
Do cols O-R look up the same information for each row (but return different columns)? If so, using MATCH in a new column to get the position of that item, and then 4 INDEX formulas will be much more efficient. Also, if you can sort the lookup table on the column that you are searching, you can use the binary search version of VLOOKUP which is much, much faster than the exact match version.

Paul_Hossler
10-08-2015, 08:47 AM
How many MB's is the file on disc?

If it takes too long (subjective for us impatient folk) the WB or one of more worksheets might be corrupted

You might have formatting that goes all the way to the right or botton

Re-build by starting a new WB and copy/paste just the cells in use and see if that helps

markstro
10-08-2015, 09:01 AM
Current file size is 101,937 Kb
O=Vlookup(N5,$CD$4:$CF$45,3)
P=VLOOKUP(A5,$BE$78:$BF:$93,2)
Q=VLOOKUP(P5,$BE$95:$BF$97,2)
R=VLOOKUP(N5,$BL$13:$BN$56,3)
These are in the same sheet as you can see, some of my other Vlookups are looking to another sheet.
I try to keep my formatting etc. just to the last row of data. I add around 1,500 lines a week, copy down all my formatting & formulas by double clicking the small + sign in the cell.

Paul_Hossler
10-08-2015, 09:42 AM
Current file size is 101,937 Kb

I'd say that a 100+ MB file should be re-created by copy/pasting just the data and formulas into a brand new workbook

That 100MB is the compressed size. The real spreadsheet is much larger

Excel has to uncompress when you open it and recompress when you save it; that all takes (a lot) of time

I strongly suspect that there's a lot on corruption (or orphaned cells) in there, possibly because it's been edited a lot

You can do a little detective work if you want by renaming it to .zip and opening it.

Look at the contents as see if there is a really large file inside the zip. That might give you an idea of where to look

markstro
10-08-2015, 09:50 AM
Not sure how to do that, I receive the invoice data for my uniform service in excel format, add it to the bottom of my spreadsheet and then copy down the formulas to the last row with data.

What would change if I copy the data and formulas?

Is there any way to indentify corrupted or orphaned cells?

markstro
10-08-2015, 10:40 AM
Saving the file to .zip compresses it to 7.8 MB, no other file visible in the .zip file.

Paul_Hossler
10-08-2015, 11:16 AM
1. The XLSX and XLSM formats are already zipped.

Just RENAME the file as ZIP and then open it (screen shot)


2. "I receive the invoice data for my uniform service in excel format, add it to the bottom of my spreadsheet"

I'm guessing that unless you PasteSpecial Values when you do that, you're creating a new (but identical) set of styles when you do that. That adds to WB bloat also. Happened to me is how I know.

Other possibility is that the 'excel format' is not really a real xlsx file. We had a SAP application that only called it an xlsx, but it was really a MIME wrapper around the xlsx and 10x as large


3. If you can run PKZIP on a 100MB XLSX file, and end up with 7.8MB file, then I'd say

a. create a new blank WB
b. insert and name sheets
c. copy the data columns from your old WB, and
d. paste special values into the appropriate sheet in the new WB
e. add formulas and format as required

markstro
10-08-2015, 11:41 AM
I use a macro to format the received data to copy to my main sheet.
I copy then paste, not using any paste special values.
When I open the .zip version The [Content_Types].xml file is only 2 kb compressed and only 16 kb actual size.
So, instead of Ctrl V to paste, use paste special : values then change my font size, formatting and copy down the formulas?

markstro
10-08-2015, 11:43 AM
What do you mean by a "new" but identical set of styles?

Paul_Hossler
10-08-2015, 04:05 PM
I've found that if I paste from another web page or spreadsheet, sometimes each paste adds another group of styles defined the same, but with a different name (e.g. Normal, and then Normal_1, etc.)

[Content_Types].xml is usually small, but what about the other folders in the ZIP?

\xl and \xl\worksheets etc.

markstro
10-08-2015, 04:43 PM
The other directories,
_rels shows file .rels at 1 kb,
docProps shows files app.xml & core.xml at 3 & 1 Kb
xl shows the largest file calcChain.xml at 30813 kb

Can't get the screen shot to import into this message.

Paul_Hossler
10-08-2015, 05:09 PM
In xl\ look into worksheets\ charts\ etc.

If you WB is 100MB on disk, there's got to be at least one large file somewhere in there

8ailey
10-08-2015, 11:39 PM
Hi markstro,

have you tried saving the workbook as a binary file? This will reduce the size which will open and close/(save) faster as it is much smaller than the XML format.

Go to SaveAs > Save as type - Select Excel Binary Workbook (*.xlsb) from the drop down

Aflatoon
10-09-2015, 12:52 AM
Is your calcchain really 30MB?

snb
10-09-2015, 01:05 AM
I fear the file contains a lot of invisible shapes:


Sub M_snb()
for each sh in sheets
y=shapes.count
msgbox y & " shapes"
x=x+y
next

msgbox x & " shapes in the file"
End Sub

Paul_Hossler
10-09-2015, 06:18 AM
snb has a good point

I've seen computer generated workbooks / worksheets that had a little icon (red ball, yellow ball, green ball, etc.) on them on every row. They're not invisible, but there could be lots of them

If you're pasted them in, your size could build up

SamT
10-09-2015, 08:14 AM
Create a new Workbook as Paul suggested in Post #5
Format the entire sheet as General
Format the headers as Bold, one size larger and bottom border.

Convert the workbook to Binary as Bailey suggested

Use VBA to add the new data Values and perform all the calculations to fill in the blanks. snb has some blindingly fast code to do this.

If and only if the values in the other sheet (VLookUp) are subject to change use cell formulas where needed. Consider using VBA on the other sheet to change the Looked up values on this sheet.

markstro
10-09-2015, 08:41 AM
Wow, thanks for all the suggestions, I'll try to work them in as I can today.
What tool can I use to insert screen shots in this forum, I'm new on this one, experienced in another.

SamT
10-09-2015, 08:46 AM
Use the "Go Advanced" button for that and to upload attachments.

markstro
10-09-2015, 08:54 AM
Yes my calcchain is that big.
The worksheet directory under the xl directory, sheet 1 is 32,139 compressed from 236,139 kb
Sheet 2 is 41,548 compressed from 325,085 kb

I've suspected for a while that I have way too much going on in this spreadsheet, one avenue I came up with but not tried yet is to copy just the data, keeping one row with formulas to copy down for any new data added.

That way I'm not running all those calcs every time I open or save the file. Would I be on the right track with this approach??

markstro
10-09-2015, 08:58 AM
Here's the best I can do, can't resize this from my screen shot tool, I'll try to copy the file structure into a spreadsheet and attach

14544

markstro
10-09-2015, 08:59 AM
Oh, it came out much larger after I sent the reply, cool

SamT
10-09-2015, 09:30 AM
BTW, you can rename those sheets to give them meaningful Names without affecting any formulas. Mind you, it will take some time for Excel to edit the formulas after each Name change.

Just so you know. If you decide to go VBA, Code is affected by Sheet Tab Name changes.

All sheets have two Names, The one you see on the Tab and an Object Name.

In the VBA editor, the Object Explorer shows both Names, he Object Name followed in Parentheses by the Tab Name... Sheet1(Sheet1)

In the Properties Window, that convention is reversed, (Name) | Sheet1 is the Tab Name and Name | Sheet1 is the Object Name.

If you change the Tab Name of sheet1 to "Main" you would see Sheet1(Main) in the Object Explorer and in the Properties window see (Name) | Main and Name | Sheet1.

OTH, if you then changed the Sheet's Object Name to "Primary" you would see Primary (Main) , (Name) | Main, and Name | Primary.

Using the Object Name in code is a bit faster and prevents any user changing the Tab Name from breaking the code.

Paul_Hossler
10-09-2015, 09:32 AM
Sheet 1 is 236MB and Sheet 2 is 325MB uncompressed

Sheet 1 is 32MB and Sheet 2 is 42MB compressed

That's 74 MB out of a 100MB file on disc

I'd really look at what's in those 2 sheets at least and if the data seems 'reasonable', they've probably been corrupted some how (lots of orphaned megabytes). Rebuild them and save the workbook

markstro
10-09-2015, 09:35 AM
As much as I know about Excel, not sure what you mean by "rebuild them"
How do I identify orphaned megabytes?

SamT
10-09-2015, 09:50 AM
See Post #'s 5 and 30

snb
10-09-2015, 10:06 AM
If you don't mind you can make this file available on www.wetransfer.com

markstro
10-09-2015, 11:42 AM
I would love to upload the file, however, there is proprietary pricing information I can't share.

markstro
10-09-2015, 11:48 AM
14545
after copying values only to a new worksheet then renaming to .zip, here is the xl\worksheets directory.

snb
10-09-2015, 12:42 PM
Did you run my macro ?

markstro
10-09-2015, 01:41 PM
I had to hit Ok on 10-15 MsgBox's saying 1 shape found

markstro
10-09-2015, 01:42 PM
I ran it on the .xlsb version by the way, I'll try the .xlsm version next

snb
10-09-2015, 01:43 PM
Is it correct that every sheet should contain 1 shape ? (a shape can be very large in size).

markstro
10-09-2015, 01:47 PM
snb, When I run your macro on the .xlsm and .xlsb in which I saved the values only I get 13 shapes

markstro
10-09-2015, 02:10 PM
Under find and select, selection pane I see 1 comment, can't find it in all the data, not sure how big or where it is.

markstro
10-09-2015, 02:13 PM
Found it, just a comment about not deleting a date a formula needs.

SamT
10-09-2015, 02:55 PM
I would love to upload the file, however, there is proprietary pricing information I can't share.
Sterilize the data. In row 2 for each textual propreitry column, place a random string of alpha characters. in each numerical column, enter a random string of numeric characters. In each pricing column enter 99.99 In each propietray column of alph and numeric characters, us random characters in the same pattern as the prop info,

Select Cell 2 in all the columns (hold the Ctrl key down). After all columns have been selected press Shift + Ctrl + Down Arrow, then Press Ctrl+D.

The data in the book has no relevance to the issue. Up load the sterilized book for us. As big as it is, you might have to upload one sheet at a time and even then Zip it first. The maximum upload size is 1MB.

markstro
10-09-2015, 03:08 PM
SamT, you are correct, I see that.
I'll try to get the sterilization done today, if not, Monday.

snb
10-10-2015, 03:20 AM
on wetransfer you can upload to 2 Gb.