Often you can use a PivotTable to do the same thing as a whole bunch of functions like SUMIF, COUNTIF, SUMPRODUCT et cetera. No-one seriously hurt apart from severely injured pride and a marked increase in insurance premiums the following year.) (The image above comes from this New York Times article detailing a spectacular traffic pileup in Japan in 2011 that left a highway strewn with the smashed wreckage of eight Ferrari’s, a Lamborghini and three Mercedes sports cars. Tens of thousands of them are just going to crash in to each other: A few hundred SUMIFS on the same stretch are still going to whiz by pretty fast. One SUMIF is going to travel very fast down the highway. That’s because these functions are like Ferrari’s…very powerful, but very expensive. But 60,000 resource-intensive number-crunching functions such as SUMIF, SUMPRODUCT, COUNTIF etc pointed at very large ranges will cause Excel to flinch, if not shut it’s eyes completely for large periods of time.
You can throw 60,000 VLOOKUPS or IF statements or other run-of-the-mill functions at Excel and it won’t even blink. It’s really easy to see just how big a problem you might have, simply by doing a Find All for the name of the particular function you’re after: And each one of these formulas referenced two entire columns, rather than just the 2500 rows that actually contained data. The second problem is that each file contained something like 60,000 SUMIF formulas in them. To see what to do with this code, read What would James Bond have in his Personal Macro Workbook.
#REMOVE SORT IN EXCEL AFTER SAVING FOR 2011 FOR MAC CODE#
I’d suggest putting the below code into your Personal Macro Workbook, for times like this:įor Each sht In ActiveWorkbook.Worksheets In this case, a bit of VBA will usually suffice. S ometimes this doesn’t fix the problem, and you still find yourself well below your data. When you’ve done this, then push + again and see where you end up – hopefully at the bottom right corner of your data. In fact, this is often why the used range is wrong…it still reflects some data that used to be in the sheet, but that the user subsequently deleted using the keyboard. Pushing that Delete key does not reset the used range. Note that you’ve got to use the Right-Click>DELETE option, NOT the Delete key on the keyboard. To do this, select the entire row immediately below your data, then press + to extend the selection right to the bottom of the sheet, then right click and select Delete: Often you can reset the Used Range simply by selecting all the the empty rows under your data, and then deleting them. After we reset the used range, the filesize plummeted from 35MB to around 2MB. This is exactly what had happened in the case of the spreadsheet concerned.
If the used range includes millions of cells that aren’t even used, then the information that Excel saves regarding these cells can really blow out the file size. Why? Because when Excel saves a file, it includes information about things such as what type of Cell Formatting is used within the used range. Maybe all the way to the very bottom of the grid:
Hopefully it will take you to the bottom-most, right-most cell that you’ve actually used in the sheet:īut occasionally, you’ll see that it might take you far, far below that cell. You can find out what this is for each spreadsheet by pushing +, and seeing what cell this takes you to. The Conf used rangeįirst, there was a problem with the Used Range – the area within a worksheet that Excel thinks contains all your workings and data. It turns out there were two problems with her files that were easy to resolve. This analyst had a file with only 6000 rows of data in it, but the file size was something like 35MB, and after each and every change she had to wait at least a minute for the file to recalculate before she could do something else. This prompted one of the participants to come to me for advise regarding restructuring a spreadsheet with that very problem. I recently gave a presentation on Excel efficiency to a bunch of analysts, in which – among other things – I’d pointed out that if you ever find yourself having to switch calculation to Manual, there’s probably something wrong with your spreadsheet.
0 kommentar(er)
