Dumb Excel Questions

Looks right
:grimacing:

usually I have more than a dozen calculated fields to the right of the data set, so just rearranging the columns is the easiest

also, I copy and paste-by-value the data into my workbook

Sometimes I think it’s better to have several smaller formulas rather than one big one. Maybe a tab with nothing but match statements that looks up the column number of the key and anything else you’ll be using.

If you like named cells (which I mostly do not) then this would be a good use for them.

Then your index formulas won’t be so messy. Just a thought. If you have lots of index formulas referring to the same match results then this will have the added benefit of making the file smaller, since formulas take up more space.

2 Likes

These are certainly far easier to review (and process if you’re tracking the data-flow).

1 Like

excel has 16K columns. You can afford to split up some formulas in extra columns

3 Likes

I have a spreadsheet with multiple tabs and lots of calculations - 43k mg

I changed all the formulas to hard code. I deleted a few tabs that were only look ups.
I went to every tab, and deleted all below and right of the data. - still 43k mg

How do I reduce the size?

Then, I realized I missed on formula. I added it to the original, still 43k
I added as a hardcode to the other sheet and now 105k

HELP

Is it still open? Maybe try saving it or if that doesn’t work, close & reopen.

Sometimes it’s saving all the changes so that you can [CNTL] Z everything.

Oh, and you could see it prior versions are being saved as part of the same document. I’d have to Google how to delete prior versions.

If you’ve done that already then I got nothing.

Links hiding out in named ranges?

“43 thousand megabytes”??

Hmm…

  1. Check for hidden tabs.
  2. Check for pivot tables, and something called a “pivot cache”. I don’t use Pivot tables so that is something you have to figure out.
  3. Check for conditional formats.
  4. Check for images (?).
  5. Check for links.

Otherwise, I’d completely recreate the file.

Some stupid guesses:
Along with conditional formats, sometimes a file accumulates lots of styles (count of styles is limited to 65K or something like that). I don’t know how to manually remove a style, but I have an .xlam that adds a button to the ribbon to do it. They aren’t huge space gobblers, but if you try to format something when you are at the limit it sometimes balks.

Check to see if there are any data connections. If you aren’t using them, delete all of them.

Save as .xlsb, check file size.

copy each sheet to a new file and save. See if one of them is bigger than it should be.

If it is really big (43GB is huge to me for an Excel file, even 43MB can be unwieldly) are you using Office 365 or an older version of Excel? Office 365 has some improved memory handling from what I have been told.

  • Clear out excess formatting: On my Excel I can go to File → Options → Ad-Ins → select COM Ad-ins in the drop down, and check the box for “Inquire”. This adds a tab to my workbook that has a button for “Clean Excess Cell Formatting” I don’t remember I how I got this, before that was there I used to just have a macro that I googled that did the same thing. Essentially this will go through and get rid of cells with formatting that are outside of where your data is. Things like when someone highlights an entire row/column and formats it. This can help quite a bit.
  • Hidden named ranges - you can use VBA to delete hidden named ranges. Be careful though, some of those hidden names are vital to Excel working, so definitely do this on a copy of your workbook first
  • Are you using pivot tables? Like DTNF said, pivot tables can take up a lot of data. One trick is that if you copy/paste your pivot tables and then make changes the copies will use the same cache as the original, so they take up less data.
  • I 100% agree with the suggestions to use .xlsb file extension.
  • Do you have any volatile functions (does Excel ask you to save after you open/close the workbook without making any changes)? Those can slow you down, though I don’t know that those would increase the memory a lot.

This. My laptop gets wonky at 30GB. PZ is off by a factor.

Even “=NOW()” will cause this.

Oh, and pivot tables suck.

I like this suggestion.

:face_with_raised_eyebrow:

You choose to have those annoying advertisements show up in your Excel files?

:man_shrugging: I don’t like any function that updates automatically like that, so I just treat them all as volatile functions.

I don’t use pivot tables a ton, but they are nice when you’re trying to do something quick.

PZ, is this a file you inherited from someone else, or is this your own zombie creation?

my own creation, updated each year from prior year. 43k is standard with all formulas. no pivots.

i ended up copying tab by tab without formulas into v2. saving, checking size and copying into final version.

never found where corrupt, but got a hardcode version at 10k, i could email

4 Likes

Is there a way to put an OR function in a SUMIFS?

Like this…
=SUMIFS(A1:A10,B1:B10,“ABC”,C1:C10,OR(“XYZ”,“123”))

What I do now is this…
=SUMIFS(A1:A10,B1:B10,“ABC”,C1:C10,“XYZ”)
+SUMIFS(A1:A10,B1:B10,“ABC”,C1:C10,“123”)

…maybe some kind of array fn.

I’ve often wondered this also. But the two sumifs has always been easier than figuring it out, so I never looked into it.

3 Likes