Excel is an unbelievable tool to solve problems quickly, but as we scale the weakness of Excel becomes clear… It is very time consuming to aggregate data from hundreds of individual spreadsheets.
Here are 4 tips that can help you make more from your spreadsheets.
1. Standardize Your Excel Sheets
One of the biggest reasons companies struggle with managing their Excel related data is due to lack of standardization. If you setup an informal committee to oversee the creating and management of your Excel data you can help reduce one off templates that can be difficult to merge. This is certainly not always an easy thing to do, but most of the time one off templates can be avoided with a better thought out design.
2. Lockdown Your Spreadsheets
Users are typically always going to do what they “can”. This means that if we want the highest quality of data possible we need to guide our users through our process and limit what they “can” do. Or simply put, they will! As a best practice we like to see our customers use the Protect Sheet & Protect Workbook functions in Excel to disable fields that are calculated, use dropdowns and date pickers instead of free form text as much as possible, and use a color scheme to show the user the difference between an editable field (white), a required field (yellow), and a read only field (grey). The necessity for real-time data analysis means that ERP integrated solutions are critical for ensuring a prompt response. If you have all of your data in one place, you won’t have to spend extra time shuffling between different repositories until you find the right data. Therefore, you can significantly cut down on your response times, thus ensuring that your business manages its affairs in a dynamic fashion.
3. Make it easy to update dropdowns and dynamic data
If we accept #2 to be a useful tip, then we must also have a strategy for making it very easy to deploy new dropdown values and lookup tables to our spreadsheets. The approach we like to take is to separate the data into a completely different Excel file that can be deployed separate of the Excel where the users capture data. Or make all dropdowns work off of a hidden “Data” tab that can easily be replaced in it’s entirety. That way if you want to add a new project to your project dropdown you can easily deploy it to your end users without having to give them a new excel sheet and force them to re-enter their existing data. It also makes it much less confusing to your users.
4. Version your changes
Versioning is something we hear all the time in the IT world, but most people don’t think about it when they are creating Excel files. If you are ever going to be able to create reliable systems around your Excel data it is critical to know which version of a given template you are dealing with. The reason for this is for backwards compatibility and to be sure that your processes don’t break as you add fields or remove them from your Excel templates. If you track the version then your programs and apps that use Excel data can easily know which version of the file they are dealing with and apply the correct rules for that version. If you have ever deployed anything to field users you know that they will not switch over to the new version instantly, so there has to be thought behind a successful transition from one version of an Excel file to the next without breaking your systems.
Doc Infusion is a cloud based platform that can automatically accept your incoming Excel files and through machine learning recognize your Excel file, begin our document parsing process, and automatically enter it into your operational, reporting, or accounting systems to streamline your business processes.
If you have Excel data that you are struggling to key in manually, we can help you automate that.
Please reach out to us by clicking on contact us, we would love to hear about your project!