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?
CarenCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

excelismagicCommented:
What do you mean unmanageable ? Because of size ?

Can you try saving in binary format xlsb. Then the size will dramatically decrease

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CarenCAuthor Commented:
Wow! Thanks for such a fast answer!  I saved it as an .xlsb and it went to 8MB from 23MB!  I then removed all the connections and closed it. When I opened it again there was a message about unreadable content.  I closed it again and it opened fine so I'm not sure what that was all about.  

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?
excelismagicCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

excelismagicCommented:
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
excelismagicCommented:
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.
excelismagicCommented:
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.
CarenCAuthor Commented:
Sorry for delay in closing . . . through I closed it a few days after the replies.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.