CarenC
asked on
Excel data 22 MB - PowerPivot and Freezing Data
My data is pulled into the spreadsheet using SQL and is 22 MB. If I add pivot tables or calculations then it's unmanageable. I've tried PowerPivot and that works fine but I'm stuck on how I can freeze the data without losing the raw SQL data. If I delete the PowerPivot connection then I lose the raw data. What are my options?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ok
here are the only disatvatages of binary format
A) No Ribbon modification allowed for XLSB formats. You must convert back to XLSM, make your Ribbon changes, and then back to XLSB
B) A potential lack of interoperability with OpenOffice
C) Not compatible with Excel 2003 and previous versions
here are the only disatvatages of binary format
A) No Ribbon modification allowed for XLSB formats. You must convert back to XLSM, make your Ribbon changes, and then back to XLSB
B) A potential lack of interoperability with OpenOffice
C) Not compatible with Excel 2003 and previous versions
A lot of users are unaware of the many benefits and advantages of .xlsb Excel Binary Workbook Format.
there will be no affect on charts of graphs or formulas. only if your users have 2003 excel is it is not compatible.
.xlsx loads 4 times longer than .xlsb and saves 2 times slower and has 1.5 times a bigger file. Reportedly tested this on a generated worksheet with 10’000 rows * 1’000 columns = 10’000’000 (10^7) cells of simple chained =…+1 formulas:
.xlsx .xlsb
loading time │ 165s │ 43s
saving time │ 115s │ 61s
file size │ 91 MB │ 65 MB
In addition from a security point of view, since data is in binary format and not in XML, .xlsb files will display unreadable contents if a user tries to change the extension of the file to .zip.
Other advantages of .xlsb file format include:
– Saving in binary allows formulas to be saved properly if they are longer than the 8192 character limit
– Macros, VBA code is fully supported
there will be no affect on charts of graphs or formulas. only if your users have 2003 excel is it is not compatible.
.xlsx loads 4 times longer than .xlsb and saves 2 times slower and has 1.5 times a bigger file. Reportedly tested this on a generated worksheet with 10’000 rows * 1’000 columns = 10’000’000 (10^7) cells of simple chained =…+1 formulas:
.xlsx .xlsb
loading time │ 165s │ 43s
saving time │ 115s │ 61s
file size │ 91 MB │ 65 MB
In addition from a security point of view, since data is in binary format and not in XML, .xlsb files will display unreadable contents if a user tries to change the extension of the file to .zip.
Other advantages of .xlsb file format include:
– Saving in binary allows formulas to be saved properly if they are longer than the 8192 character limit
– Macros, VBA code is fully supported
Wikipedia source
Excel Binary Workbook
.xlsb
As Excel Macro-enabled Workbook, but storing information in binary form rather than XML documents for opening and saving documents more quickly and efficiently. Intended especially for very large documents with tens of thousands of rows, and/or several hundreds of columns.
Excel Binary Workbook
.xlsb
As Excel Macro-enabled Workbook, but storing information in binary form rather than XML documents for opening and saving documents more quickly and efficiently. Intended especially for very large documents with tens of thousands of rows, and/or several hundreds of columns.
personally, by default. i always use binary format xlsb
becuase, it is fast, macro supported, not any different than nomral xlsx other than being faster than it.
all other users that i share my workbooks have excel 2007 or above. so i do not have concern of excel 2003.
becuase, it is fast, macro supported, not any different than nomral xlsx other than being faster than it.
all other users that i share my workbooks have excel 2007 or above. so i do not have concern of excel 2003.
ASKER
Sorry for delay in closing . . . through I closed it a few days after the replies.
ASKER
Some questions . . .
1. What is binary format? I read the MS Knowledge Base article, https://msdn.microsoft.com/en-us/library/office/gg615597(v=office.14).aspx, but didn't understand it very well. Streams, substreams . . . didn't get it all. Can you help me understand?
2. Will it affect users being able to open it?
3. Will it affect graphs and charts?