Updated: Sep 15
How many different spreadsheets do you regularly use to monitor business activity and manage your business? If you use more than one spreadsheet, then how do you validate the integrity of the data? If you are duplicating the source data set across multiple spreadsheets you risk making critical decisions based on faulty data.
You must be absolutely certain that, at any given moment, every single spreadsheet has the exact same set of data. Even data sets that are generated only minutes apart can have vastly different, and conflicting information. It is only a matter of time that you use the wrong information to make a decision, or that you find yourself unable to reconcile important financial transactions.
In one of my previous engagements, I was asked to streamline the reporting process for a client. They were required to produce a monthly report on the processing activity they did for one of their largest customers. The problem was that it took a day, sometimes a bit longer, to produce the monthly report.
Data was stored in an Access database and exported to Excel, where a clerk would manually manipulate the data to create the report. There were three data sets that were used to create the report, all of which were extracted from the single Access database. The process was to extract the three data sets, one right after another. It took about 3 minutes to extract each data set. However, the transaction volume was so high that even in this short amount of time, the data in each set was different - so much so that it was impossible to reconcile the three data sets even though they all came from the same source!
The solution was to first combine all tables in Access, then export the combined table to Excel to be certain that all data had been created at the same exact moment. This made it possible to use Excel's standard features to create pivot table reports, which reduced the need to manually recreate the customer report each month. It also meant that every month we just had to replace the existing data set - an Excel table in one of the workbooks worksheet tabs - with the new data set created for the current month.
So take time to audit your critical spreadsheets and examine the data source for each. If you find that more than one spreadsheet relies on the same data source, but you have duplicated the data in each spreadsheet, it is time to redesign your spreadsheets to take advantage of the software's sophisticated data management features and prevent any unnecessary critical errors from happening.