Excel is becoming an ever more powerful tool for analytics, and data visualisation has come a long way from the humble bar chart. As one of the most popular software programs out there, it has the advantage of being an option that doesn’t require a lot of time, training or investment, and its capabilities are rapidly overtaking tools that used to cost thousands in the early days of data. As with any tool, though, the results it provides can vary widely according to the skill of the person using it.
My attitude to software is that anything that can be done automatically, should. Not just to free up time, but to standardise the result and minimise the chance of error. Not incidentally, this lets you get the best results from both your tech investments and your people, since they are freed up to do the things that add value, things that people do best. Assessments, judgement calls, decisions, fed by data the software has processed to save the time that manual handling steals. Pushing the boundaries of what Excel was designed to do, to get around limitations in the resources available within a public service department, I learnt hundreds of tips and tricks to make Excel do more of the work for me. I can write formulas to create reports that handle just about anything, and I’ve developed skills in the back end programming language (Excel VBA) that lets me design spreadsheets that work like custom programs, specific to your needs but without any extra add-on software costs.
Here’s a tip for you. One of the lesser-known but most useful tricks in Excel when you’re entering a batch of data, is the combination of Ctrl-D. That duplicates the value in the cell above it, no matter how long or awkward it is, so you don’t have to enter it again. If it’s a formula, it copies it but moves the references down one row (unless you’ve written the formula to prevent that and ‘lock’ the references). Using tab to go from field to field, or to select if the cell has suggested something based on what you’ve started typing, and Ctrl-D to copy down wherever you can, a lot of data can be entered very quickly.
If you’ve got a spreadsheet that you regularly run reports from, consider getting a dashboard designed. Adding one extra sheet to the workbook, it can read the data as it’s updated, so you never need to re-run anything, just pull it up and it’s ready to go, telling you exactly what you need to know about the data as it is now, in an easy visual format, printable any time you need it.