Manual Excel reports could cost you upward of R81 thousand a year.
Here are 4 things you can do to reduce that cost.
I use these 4 techniques when designing reports that help reduce some of the common mistakes we see when working with manual Excel
1. PivotTable
This feature has so much available to users and the advantage of cross-platform usage (Windows and Mac) that it amazes me how many times I see people NOT using it. It supports all the common aggregation functions such as SUM with contextual awareness – like SUBTOTAL.
PivotTables really is Excel superpower.
1. Structuring Data
This is a problem that I see all too often. Most users design a report for what they expect the end result to be. This makes sense when starting out but as the data and the demand on the report increases. Bad design really starts to impact on your ability to change a report or introduce new analytics. It also prevents you from doing any ad-hoc discovery as the complexity of the report makes it harder and harder to make changes.
This is the highest contributor to why reports take hours or even days to complete.
3. Use Tables rather than a range of data.
A Table is more than just a named range. Tables have specific features that allow you to reference a column name, like “mytable[Sales]” rather than a column/row intersection “G34”. This way, when a table moves or there is a new column added, then your formulas aren’t affected as easily as it would be should you use standard ranges.
Tables also have the added benefit of growing dynamically and gives you features like slicers and total rows to make it easier to work with.
It’s also great when using a table as a source to your PivotTable, that way always staying in sync and not needing to update the range (A1:AG5678)
4. PowerQuery
If you add formulas to your imported data and have to copy these formulas down (filldown) whenever the data is updated then using PowerQuery in it’s simplest form may be of benefit. If you use any of the lookup functions, such as VLOOKUP or HLOOKUP then I strongly recommend that you replace those with the use of PowerQuery. While it may be new and seem confusing, just a few hours with PQ and you’ll soon become a master 😉