Please read these valuable tips on how to work with spreadsheets effectively.
Why are spreadsheets so hated and praised at the same time? Everybody loves spreadsheets for their flexibility. However, this flexibility can turn a spreadsheet that’s perfectly clear to you into something very difficult to query when it grows beyond the size acceptable for searching in it by human eyes and you need an analyst or programmer to help you. Programmers want to know whether values in a column are numbers, text, date, and so on, which is not clear with spreadsheets. Often, analysts need to import the data into some software other than Excel (which generally involves OLE DB or ODBC technologies), and requires a nicely formatted spreadsheet. Here are six critical issues to look out for.
- Folder, file, sheet, or column names contain non-alphanumeric characters
- Multiple tables on one sheet or one table split to multiple sheets
- Cell style indicates value
- Mixed data types in one column
- Merged cells and fancy headers
- Inconsistent identifiers
If you follow recommendations outlined in this post, you are on your way from being enslaved by your spreadsheets to let your spreadsheets work for you. However, there are many intricacies to storing data in spreadsheets and you may run into issues specific for your situation. My final recommendation is to adhere to one key principle: separate data from data representation.