I have been putting off blogging about this topic because it’s unwieldy to explain and involves work done many years ago that I would do better now. I cringe a bit seeing an Offset formula now which I would only use these days as a last resort because it’s volatile. But let’s see if it […]
Using Excel Templates to upload to an accounting software – Part A The Cash Book Read More »
I have blogged before about how much I love INDEX (INDEX what’s the Big Deal ). It’s powerful, nimble (non volatile so easy on performance load) and just makes so much sense to me given we are working with a grid and INDEX essentially returns the contents from the co-ordinates of the grid reference entered.
Using INDEX to calculate a moving average dynamically Read More »
Transposing Data with INDEX / MATCH combo what’s this all about? OK imagine this – you have done the right thing as a budding data analyst and created your sales data down the page so that you can take advantage of Pivot Tables which likes dimensions of data going across in columns namely Date and
Transposing Data with INDEX / MATCH combo Read More »
Update 12/12/2021 Since writing this post in 2017, Excel now has new revolutionary dynamic array formulas that really has overtaken all the workarounds for better dropdown lists we used to do such as described in my post below. Now the new formula SORT() + UNIQUE() in Excel Office 365 (although not in earlier stand alone
No more gaps – a formula to remove spaces in lists or empty rows in ranges Read More »
Somewhere along the line, I realised I was better off using what I call the INDEX / MATCH combo in nearly all lookup situations and not the ever popular VLOOKUP. The INDEX /MATCH combo can look left and right (unlike VLOOKUP which can only look right), you don’t have to have sorted data to lookup
Why I like INDEX / MATCH combo (and not VLOOKUP) Read More »