Howdy folks. Jeff here. 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. Here’s the slide:
This prompted one of the participants to come to me for advise regarding restructuring a spreadsheet with that very problem. 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.
It turns out there were two problems with her files that were easy to resolve.
The Confused range
First, there was a problem with the Used Range – the area within a worksheet that Excel thinks contains all your workings and data. You can find out what this is for each spreadsheet by pushing [Ctrl] + [End], and seeing what cell this takes you to. Hopefully it will take you to the bottom-most, right-most cell that you’ve actually used in the sheet:
But occasionally, you’ll see that it might take you far, far below that cell. Maybe all the way to the very bottom of the grid:
This is bad. 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. 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. This is exactly what had happened in the case of the spreadsheet concerned. After we reset the used range, the filesize plummeted from 35MB to around 2MB.
Often you can reset the Used Range simply by selecting all the the empty rows under your data, and then deleting them. To do this, select the entire row immediately below your data, then press [Ctrl] + [Down Arrow] to extend the selection right to the bottom of the sheet, then right click and select Delete:
Note that you’ve got to use the Right-Click>DELETE option, NOT the Delete key on the keyboard. Pushing that Delete key does not reset the used range. 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.
When you’ve done this, then push [Ctrl] + [End] again and see where you end up – hopefully at the bottom right corner of your data.
Sometimes this doesn’t fix the problem, and you still find yourself well below your data. In this case, a bit of VBA will usually suffice. I’d suggest putting the below code into your Personal Macro Workbook, for times like this:
Sub ResetUsedRange()
Dim sht As Worksheet
Dim lng As Long
For Each sht In ActiveWorkbook.Worksheets
lng = sht.UsedRange.Rows.Count
Next
End Sub
To see what to do with this code, read What would James Bond have in his Personal Macro Workbook.
Too much SUMIF
The second problem is that each file contained something like 60,000 SUMIF formulas in them. And each one of these formulas referenced two entire columns, rather than just the 2500 rows that actually contained data. 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:
You can throw 60,000 VLOOKUPS or IF statements or other run-of-the-mill functions at Excel and it won’t even blink. 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.
That’s because these functions are like Ferrari’s…very powerful, but very expensive. One SUMIF is going to travel very fast down the highway. A few hundred SUMIFS on the same stretch are still going to whiz by pretty fast. Tens of thousands of them are just going to crash in to each other:
(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. No-one seriously hurt apart from severely injured pride and a marked increase in insurance premiums the following year.)
Often you can use a PivotTable to do the same thing as a whole bunch of functions like SUMIF, COUNTIF, SUMPRODUCT et cetera. PivotTables are natural aggregation and filtering tools. In this case I could use just one PivotTable to replace those 60,000 SUMIFs, and recalculation time dropped from minutes to milliseconds. Now, reporting on this business process is effortless.
One spreadsheet, two morals
I’ve got two morals to share regarding this.
The first is to keep your eyes peeled for signs of trouble in your spreadsheets. Think of FileSize and Recalculation Time as the rev-counter of your car…if it’s getting further and further into the red, then pull over, and check under the hood.
The second – and I can’t underscore this enough – is the importance to organizations of educating all users on how to recognize symptoms of inefficiency. They don’t all have to know how to treat it (although that would be good), but just how to diagnose it. Because if it goes undiagnosed, avoidable inefficiency imposes significant, on-going, and very real opportunity cost. A real dollar amount.
Raising awareness of danger signs is possibly the biggest efficiency gain and risk-reducing opportunity that any training initiative can offer, at the least cost. It’s a game-changer.
Two morals, multiple remedies.
Over at the Daily Dose of Excel blog, I recently posted a mock business case centered around corporate investment in Excel training programme. There’s much more food for thought there, and even more in the comments, so go take a look, and please do leave a comment there with your own thoughts.
While this business case revolves around an internal corporate training programme, another great way of reducing this opportunity cost is through courses such as Chandoo.org’s own Excel School, VBA Classes, and other Chandoo courses.
Not to mention other fantastic courses that you’ll find advertised on the web if you look.
And yet another is though interactions in places like the Chandoo Forum, where you’ll find an army of ninjas with more collective experience than the Borg from Star Trek. The hive mind that is a forum knows no equal.
And of course, you’ll find a wealth of information on this very blog, in articles like I said your spreadsheet is really FAT, not real PHAT!
About the Author.
Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this:
=NOT(EVEN(PROPER(OR(RIGHT(TODAY())))))
That’s right, pure #VALUE!
Find out more at http:www.heavydutydecisions.co.nz
30 Responses to “Rescue oddly shaped data – Battle between Formulas, VBA and Power Query”
Nice use of Power Query! Power Query is simply awesome! But somehow a lot of people are punishing themselves by not using it (not learning it).
An imperfect 4th approach for consideration... no codes at all...
Select myrange.
Go to Special --> Blank
Delete Cell --> Shift cell left
90% done... now we just need to move the data of 2nd column to the bottom of 1st column
Of course... Power Query is the best.
Cheers,
There is another way but it involves multiple steps:
Copy the values in column E, move the cursor to F5, Paste Special with Skip Blanks, OK
Copy the values in column D, move the cursor to F8, Paste Special with Skip Blanks, OK
And so on.
This works perfectly, albeit a little clumsily apart from the values in B17 and C16, which can be moved with simple copy and paste
Power Query Forever! I do not know how I survived for so long without knowing and using this tool, I can not recommend it to my colleagues, but by the way they prefer to suffer to learn.
My congratulations here from Brazil.
I rolled my eyes when I saw that data
Using decimal places is a nice trick to order data, thanks for that
And tweaking the first formula a bit, you can use OFFSET instead of INDIRECT
=OFFSET($A$1, MIN(IF(myrange, ROW(myrange)), ROWS(A$1:A1))-1, RIGHT(TEXT(MIN(IF(myrange, ROW(myrange) + COLUMN(myrange)*0.00001), ROWS(A$1:A1)), ".00000"), 5)-1)
Tried the above formula with the downloaded oddly shaped data file and I could not get it to work. I get #value without ctrl+shift+enter, and #ref with ctrl+shift+enter.
Sorry, it was SMALL, not MIN.
Add with CTRL+SHIFT+ENTER.
Thank you for your formula. Like the indirect formula I tested this one in older versions of EXCEL and it worked without ALTERATION in EXCEL 95. Very impressive.
Too complicated
Use =Sum to summarize all the sells to the left and Bobs Your Uncle
@Bertie... I am afraid that won't work when you have more than one value in a row.
I tested this formula in versions of Excel all the way back to Excel 95
=IF(ISERROR(INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(MyRange"",ROW(MyRange)+COLUMN(MyRange)*0.00001),ROWS(A$1:A9)),"00000.00000"),".","C"),FALSE)),"",(INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(MyRange"",ROW(MyRange)+COLUMN(MyRange)*0.00001),ROWS(A$1:A9)),"00000.00000"),".","C"),FALSE)))
So there are multiple ways of cleaning up messy data by formulas.
Wow.. Excel 95. Who knew people still use that. But as you have shown, Excel has all these beautiful and powerful functions for 23 years. It has data sciency stuff before DS was even a thing.
I had a problem with pasting the formula in the original post.
Formula should be: =IF(ISERROR(INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(myrange"",ROW(myrange)+COLUMN(myrange)*0.00001),ROWS(A$1:A1)),"00000.00000"),".","C"),FALSE)),"",(INDIRECT("R"&SUBSTITUTE(TEXT(SMALL(IF(myrange"",ROW(myrange)+COLUMN(myrange)*0.00001),ROWS(A$1:A1)),"00000.00000"),".","C"),FALSE)))
EXCEL even in a 16 bit version, is a very robust and capable program.
I don't like the VBA code. If you have a blank row in MyRange, the last entry in the range is doubled up in the paste.here range.
Not really. The macro is writing one cell at a time from paste.here. You have to clean the range before, which I was too lazy to write. But a line like Range(range("paste.here"), range("paste.here").end(xldown)).clearcontents should do the trick.
Adding Range(range("paste.here"), range("paste.here").end(xldown)).clearcontents fixed the problem.
for step split column by delimiter i am not getting option of split into rows or columns. Can you help me in this
Thanks Chandoo for promoting Power Query.
To simplify further, you can "Unpivot Columns" instead of right click on the newly created column and split it by comma in to rows in step 3 of Power Query.
i used
=LOOKUP(10000,B5:F5)
and got the answers. I just plagiarized this formula somewhere and use it, maybe you can explain why it works.
Regards
@Johan... I am not sure if the formula works correctly. When I tested it with the sample data in this post, it showed #N/As in two cells. Essentially, it will only give first value in each row. So if a row has multiple values, then subsequent values are missed. LOOKUP() function goes thru a list and finds the first value that is less than or equal to the input - in this case 10000 in B5:F5.
I have the need to convert pdf's to excel on occasion and they often come out a mess like this. I have used:
Cell G2 =COUNT(myrange)
Cell G3 =IFERROR(IF(G2-1<1,"",G2-1),"") copied down to G100
Cell H2 =IFERROR(LARGE(myrange,G2),"") copied down to H100
Waouw...
=IFERROR(INDIRECT("R" & SUBSTITUTE(TEXT(SMALL(IF(myrange "", ROW(myrange) + COLUMN(myrange)*0.00001),
ROWS(A$1:A1)), "00000.00000"), ".", "C"), FALSE), "")
but CTRL Shift Enter with {} before and after 🙂 😀
Here's a way with pivot table
https://www.bookkempt.com/2018/02/aligning-non-contiguous-data.html
This is brilliant. Bookmarked 🙂
Another possibility.
This assumes that you have a row index 'k' to use in the SMALL function and a column index 'h' to identify the columns of 'myRange'.
If you define 'coord' to refer to
=k+h/10 [assuming h<10]
then it will be possible to recover values later based upon location within 'myRange'. The formula 'nb' that identifies non-blanks by coordinates is given by
= SMALL( IF(myRange"", coord), k )
Finally, to unpick the pieces
= INDEX( myRange, INT(nb), 10*MOD(nb, 1) )
Whilst I am here and making trouble the PQ solution is also a tad over-complicated. All that is needed is to unpivot the entire table and remove the Attribute column.
The advanced editor would show
let
Source = Excel.CurrentWorkbook(){[Name="myRange"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})
in
#"Removed Columns"
1.fill the blank cells with 0
2.the requested column value=sum of those mess number column
but this can be used in only one column has value
Chandoo
And if we use the formula SEARCH (100000000, B5: F5)
JC
Another approach with Power Query, it will still work if the number of columns changed:
let
Source = Excel.CurrentWorkbook(){[Name="myrange"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "List", each Record.ToList(_)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"List"}),
#"Expanded LIst" = Table.ExpandListColumn(#"Removed Other Columns", "List"),
#"Filtered Rows" = Table.SelectRows(#"Expanded LIst", each ([List] null))
in
#"Filtered Rows"
Cool idea to use Record.ToList as added column. Thanks for sharing this.
Nowadays, you can just use TOCOL on Excel 2024, MS 365, and Web Excel. It has a parameter to ignore blanks/errors/both.