I often get people coming to visit me who have a spreadsheet they want to get more from. They either want to automate certain tasks, or create new sheets with aggregated data or share data with colleagues in new ways. The hope is that with a little bit of code, new vistas will open up.
Often the data is in a spreadsheet, it isn't clean enough to do anything useful with. If code is to stand a chance at making a spreadsheet more useful, then the data itself needs to be "code ready".
Below is an actual spreadsheet brought to me, with number of areas for that needed data cleaning.
As we worked together, we realised, a healthy spreadsheet isn't just about making sure your data is logical, there are also other factors that contribute to how easy your data will be to work with.
- Use formulas well - A few easy to learn formulas can significantly ramp up what you can do quickly with data. It is worth investing even just a few minutes learning new formulas and what they can do for you.
- Prevent errors - Make sure you validate data where you can, and help people not to make your data grubby.
- Improve the interface - With Google Spreadsheets you can add menus, actions and buttons and even sidebars that can turn a spreadsheet chore into a breeze.
- Use the charts and visualisations - Getting more out of your data can be as easy as creating a well designed dashboard using Google Spreadsheet's inbuilt charts.
Here is my list of suggestions for how to make this spreadsheet's data "code ready" in a Google Doc.
There are heaps of short videos on Google Gooru's YouTube page. In minutes you can be learning new features and taming those scary spreadsheets.