Why is my Excel file so big even though I uploaded the data using Power Pivot/Power Query?

[Olivia Ludwig]

Hi Excel-community,

a friend and I are currently working on a Dashboard. The data set is very large which is why she uploaded the data using Power Query. If I understand correctly, by doing so, the data set is comprimised (please correct me if I am wrong) and it should not be a problem to build a Dashboard with about one million rows.

When I started building the Dashboard (which mainly includes making simple pivot tables in a backlog and based on those using data slicers to create diagrams in a different sheet), the file started to become slower and slower. I just checked how large the file is (184MB) which I think is too big. My concrete questions are the following:

Is it normal that a file with 184MB is so slow (slow=selecting a different filter takes 40 seconds)?
Is it possible that it became so large because of all the filters I inserted or should that not be a problem? (the data set is just one table and not connected with other add-ins)?
What is the most problematic: Creating more and more Pivot tables (using the same set of data), using data sclicers (=filters) or including diagrams based on the pivot tables and slicers?
How comprimised should the data be after uploading it? (Original data: 110MB)?
What would you suggest I should do as a next step?

I would be very greatful for any tips & tricks, please don't hesitate to ask questions if I haven't explained well enough.

Best regards Olivia

This Dashboard has already been built for parts of the data, I wanted to build one big Dashboard including all of the data. Since we uploaded it with power pivot, we expected it to be faster than before (previously with the other Dashboards we did not use Power pivot/Query), but it is as slow as before.

[romperstomper]

184MB is pretty big considering that's a compressed folder.

I'd suggest you make a copy of the file, add a blank sheet, then delete all the other sheets one by one, saving after each one, to see if the file size changes significantly. I'm assuming you don't know how big the file was originally with just the data query and no pivot tables/charts (is that what you mean by diagrams?) built yet?

[Logit]

Sometimes Excel will create thousands of lines of non-descript data that cannot be seen but is located in all the blank rows after the last visible row on each sheet in the workbook.

You can manually go to each sheet highlight all those blank rows and press DELETE or you can run the following macro to automate the process for you.

RUN THIS MACRO ON A COPY OF YOUR WORKBOOK FIRST, THEN CHECK THE FILE SIZE. IF THE SIZE HAS BEEN REDUCED AND THE WORKBOOK STILL FUNCTIONS AS DESIGNED,
RUN THE MACRO ON YOUR ORIGINAL WORKBOOK.

Please Login or Register  to view this content.

[Olivia Ludwig]

Hello Logit,

thank you for this hint. I deleted all irrelevant rows as you suggested manually (because there are only three sheets in my file), but the file size hasn't changed.

[Olivia Ludwig]

Thank you for the suggestion Rorya. I do not know how big the file was with just the data query, but I regenerated it. Like I said, the original data is 110MB, but when I imported it using data query it was still about 90MB big. Does that already sound weird to you?

Also, I tried deleting sheet after sheet, and after deleting the sheets with the charts (yes, that's what I meant by diagrams), the file was 10MB bigger than before! How is that possible? Something is really weird here.

Best regards
Olivia

[Olivia Ludwig]

Actually, I think I might know what went wrong with the upload (which still does not explain why it became so big with the Dashboard): I used Power query, but not "connection only" to import the data. I think I had to do that, correct?

The problem is, I am using a Mac & can't find the box where I can choose "connection only" while importing data using power query. Do you know if it is possible, or is there a problem with Mac again?

Best regards and thanks in advance
Olivia

[romperstomper]

If you are on a Mac, you are not using Power Pivot, because you can't use the data model. You also can't set up a connection only query, so all your data is in a table which would explain the file size issues.

[Olivia Ludwig]

Thank you, that is it. But since someone else uploaded it using Power Query with "connection only" (and using a Windows), did I technically "brake" the file by opening it with my mac? Is that possible? Because apparently, before I opened it, the data was imported correctly without seeing a table. But when I opened the file with my mac, I was able to see the table with the data.

[romperstomper]

I couldn't say whether that is normal as I've never tested it. I will try and do so later to see if the table appears when refreshed on the Mac.

[Olivia Ludwig]

Thank you! That would be great to know.

[romperstomper]

I did a quick test and the data did not get loaded to a table when opening the workbook, or on refresh.

[Olivia Ludwig]

Thank you for testing it, the problem is solved, thanks!

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.