Why I Don't Use Excel Tables
Why I Don't Use Excel Tables
Perhaps it’s a personal thing, but I’ve always found Excel tables difficult to work with. I don’t like the strange formula references tables use. I find how they automatically expand annoying – it feels like they take control away from me. And, I don’t like how a new named reference is created for each table, it detracts from my (ideally, anyway) super lean Excel development approach!
This seems to be the no.1 argument for Excel tables. If we’re adding data to the bottom of a dataset, the only way to guarantee analysis will automatically update is to use a table, right? And an awkward table reference such as [column1], or similar?!
The fact is, I always work with datasets that change in size and rarely, if ever, use Excel tables.
I have a more dynamic solution that beats even a pivot table, and in this video I show you how to put it together, step-by-step.
Excel’s INDIRECT formula divides opinion. To fans like me, it’s a unique facilitator of cool dynamic functions in Excel. To others, it’s a covert source of spreadsheet inefficiency.
There’s no doubt Excel’s INDIRECT formula must be used with care. But, some of the claims about INDIRECT – that’s it’s somehow a ‘dangerous’ approach – are simply not true. There are many riskier things going on in most spreadsheets! You shouldn’t use hundreds of INDIRECT formula in a file because file calculation will slow down. A few formulae, however, make no discernable difference. And, if you can use it judiciously, it opens up a world of possibilities.
The term ‘INDIRECT’ refers, I think, to how the formula ‘reads’ information in a cell as a cell reference. This is a ‘meta’ idea that I found very difficult to get my head round. And, it’s even more difficult to explain to others. Once the penny drops, however, the fun can really begin. The best way to learn? Look at plenty of examples, starting with the one in this video. As usual, the download file is available for you to play with.
The dynamic solution I propose in this video requires plenty of ‘setup’. I take you through it step-by-step and, don’t worry, I usually get this wrong somewhere. It takes trial-and-error to get everything working. If you can, however, It’s totally worth the effort: this kind of mechanism can transform your data analysis, moving you away from Excel’s more clunky features and towards elegant solutions that does exactly what you need, and no more!
So, did you get it working, and are you moving away from Excel tables? Let me know in the YouTube comments.