Spreadsheets are amazing and can be really useful. They can speed up boring, repetitive tasks and reduce a big chunk of human error. They can also be stressful and frustrating. Spreadsheets are mainly stressful when used wrong. So when should you use spreadsheets?
Managing accounts For small businesses, or small departments within a business, spreadsheets are a nice way to keep track of accounts without paying for, or learning how to use, accounting software. Accounts in spreadsheets can be made much more simple with the ability to download bank account transactions as CSV files which can be imported. Features such as conditional formatting and functions such as SUM and AVERAGE really help make accounts meaningful. I kept Taught by Humans books solely in a spreadsheet for three years, until I began using software provided by my accountant. One thing I liked was the ability to define the accounts my way (e.g. unofficial business departments, customer payment method), so much so I still have an ongoing accounts spreadsheets.
Admin (both business and personal) Spreadsheets are amazing for admin. Want to keep track of some mundane task (e.g. a work rota or cleaning schedule), they are an easy go to. For budgeting, both personal and work related, they provide a quick and easy way to do calculations and can be formatted to look slick. Yes, I do have a regularly updated personal budget Google Sheet, I am that cool. As a personal finance stickler, I find this useful for deciding how much to save and how to plan big expenses.
Quick data tasks Let me begin by caveating this — I do not think all data tasks should be done in a spreadsheet. I also do not think it is necessarily quicker to do things in a spreadsheet. I do, however, know that someone who is not familiar with databases, SQL and Python is not going to have an easier, quicker time completing a task not in a spreadsheet. I am familiar with all those things, and I often carry out tasks in spreadsheets. (I also really like to look at my data which isn’t always possible, particularly with Python, but that is a tale for another day). Here are some tasks I regularly do in a spreadsheet: - Opening files to check them - Cleaning and sorting - Calculations - Joining two small datasets - Comparing two datasets - Summarising Again with a caveat, I am very comfortable with data management, logic and writing spreadsheet formulas. This makes carrying out these tasks significantly easier. But I also very much believe (and have proof to back me up) anyone can learn these skills.
Tables and charts While there are amazing, beautiful visualisation tools out there, spreadsheets provide a nice, accessible way for charts and tables to be created in little time. Although I do admit to finding the limited capabilities frustrating at times (why can I not make the bars thicker in Google Sheets bar graphs?), but I will also admit to being a perfectionist when it comes to data visualisation.
Analyse data Spreadsheets are amazing calculators. Need to do some quick sums or maybe some more complex statistical or accountancy calculations — spreadsheets are definitely a good option.
Forecasts Business, sales, house prices, savings all things which need forecasts. Both Excel and Sheets have user-friendly FORECAST functions.
Storage Sometimes storing data in a spreadsheet makes a lot of sense. Got a list of phone numbers or address, you really don’t need to load that into a database. Playing around with some data before it’s really to be used, spreadsheets could be perfect for that.
Exporting and sharing Spreadsheets are great for getting data ready to share with others. Not everyone will have Tableau, SQL or other ways of storing and visualising data. However, Excel formats (.xslx) are widely used and can be opened in all spreadsheets (including free version of Excel and Google Sheets, as well as the open sources, e.g. Open Office Calc). Exporting as a CSV file means programmes other than spreadsheets (even pre-installed notepad programmes on most computers) can open the data. It might not be quite as readable as a spreadsheet. These files can also be easily imported to databases and used by programming languages.
There are also some not so-good but necessary reasons to use spreadsheets:
When other users won’t be able to use something else This is similar to the Exporting and sharing reason above. Sometimes you have other ways of storing, viewing or analysing data, but your customers, colleagues or whoever you need to share it with does not. (Of course, a lot of platforms have options to allow this, including web-hosted dashboards) This is where our trusty spreadsheets shine — most people (and businesses) have a Google or Microsoft account (or can use free, open-source software) so can access spreadsheets.
Budget Often spreadsheets are free, or are included in a package already paid for (e.g. GSuite or Office365). Therefore, spreadsheets often feel like a budget-friendly way to store and analyse data. I understand this can work for small, non-sensitive data or it may be a company’s only available option — but spreadsheets are not databases.
Okay, I have realised I could go on and on about how useful spreadsheets are (have I mentioned I love them?). My caveat holds — I love spreadsheets because I am comfortable using them. This isn’t magic, it’s a learnt skill and over at Taught by Humans we want to teach everyone this skill. Spreadsheets shouldn’t be scary, they should be time-saving and useful for you and your job.