Excel Nightmares


Excel Nightmares

Microsoft Excel is the second-most widely used productivity app in the world (only Word beats it). Many of us use Excel every day and know how good it is at handling information, performing calculations and laying out information with charts and graphs. In fact, as excel offers such a free form structure for your information many people store almost everything in Excel – and here in lies the topic for this newsletter.

Sure you can use excel for your business sales tracking and invoicing. Many people store huge lists of information in Excel. And if users are careful this can be an adequate system for a while.

Some Pitfalls of Excel

  • When you need to locate detailed information -the basic search is not up to matching across many columns at once!
  • Your calculation has the dreaded #ERROR where your total should be, simply because someone entered text in a column that was intended for numbers!
  • There is no easy method to locate a Workbook from the past.

What’s the Answer?

Yes, I can feel your frustration! The answer is a Database! More specifically a Relational Database Management System. Databases are designed and optimised to store and retrieve your data in the most efficient way. Information is stored in a structured way that will enforce data types – preventing you from entering text in that number column. Additionally, as a database expects to grow in size significantly, indexes are used behind the scenes to massively speed up your searches and summaries. What’s more, with good design the structures are “normalised” to ensure existing data is not repeated but stored in a parallel structure as a “Relationship”.  This can significantly ease your workload as instead of having to re-key that long address every time it is simply populated for you from an existing structure.

Because of their design optimisation, Databases can efficiently store almost unimaginable volumes of information in a relatively small file that is readily accessible by many users at once and can hold your complete business history for many-many years that would take rooms full of filing cabinets to match.

Nightmare Examples!

A few years back a client was running out of storage on their network. All Jobs were priced and repriced in a separate Excel Workbook. Each Workbook needed a huge Parts-List of around 10,000 rows to allow it to calculate the final price. The result was their servers hard drive became clogged with the data from these. Simply moving the Parts-List to a Database and using this one central reference dropped their storage needs to around 10% of the previous demand.

Another Business proudly showed me their file storage “Folder Tree.” Each folder holding another folder for the Clients of a Sales Person. Each Client folder had another folder for each Year and each Year had another folder for the Month! In this folder was an Excel Workbook for each Job done in that Month!

I asked how long it took to do an analysis of the monthly sales and he proudly answered they had just employed someone to do just that! A Database of course would have delivered a prebuild report at the click of a button!

We don’t just build You a Database!

No, the key to a well-structured Database is analysing your Information Requirements, Business Processes and Rules. Once we know this, we can apply our knowledge of Databases and Business to craft a Database System optimised for your Business. On top of this we then build a “User Interface” –the screens and forms to allow you to enter and manage your information, but that’s another story…

Can ProcessIT solve your Excel Nightmare?

Don’t be shy –Tell us about your nightmare! Call me now at: 06 876 0312 or reply to this email for your free initial consultation to discuss how best to optimise your systems…


Tech Tip:

Do you struggle to convey your computer problems to others; “The top thingee isn’t showing the normal watsit when I click on the whodacky.” ??? Very few tech support people will understand what you are on about! Well there is a solution and it’s been around since windows 7 and is included in windows 10 –it’s called the “Steps Recorder”. No, it doesn’t count how many paces you do while waiting for the Tech Support to answer your call! –It takes Screen-Shots and documents your problems for you to save and share.

Steps Recorder

Click on the Windows Search (Magnifying Glass) on the task bar then type in Steps Recorder, it will appear on the list. Open it. A small Popup window will appear: Go to your misbehaving application, then click on the “Start Record” button and continue using your misbehaving application until you get the error. You can add a comment anytime by clicking “Add Comment” and optionally selection any area of the screen or button to highlight. This and your comment are noted. When you have finished click “Stop Record”, Review your recording and Save it to a convenient place, to then share or email to your support team… Easy!


Brian Henderson