Excel As You Know It Is Just The Tip Of The Iceberg

Excel isn’t robust and is prone to mistakes

We can all agree that mistakes are bad. Mistakes waste precious time, we spend time looking for them and spend time fixing them, making any task we do indefinitely longer. The more time we waste, the less profit we make.

When it comes to entering data in Excel, sometimes information needs to be in a specific format, for example some columns need to contain numbers but others text. One little typo and this structure falls to pieces, none of your formulas work and now you’re searching for the mistake.

If Data Validation isn’t your best friend, it should be. You can find this in the Data tab and then in the Data Tools section. All you need to do is select the cell or area you want to have validation, select data validation, and set any criteria you like. The criteria set will restrict what information is allowed in the range you selected. You can even set a specific message that will appear when the value entered doesn’t match your rules. This is very useful as the user knows exactly what is going on.

My favourite part about data validation is that it allows you to create dropdowns within cells. Just select the List option and put the options you want in the source field separated by a comma, like this -> Yes,No.

An Excel spreadsheet is nothing more than some rows and columns

This is a very popular myth regarding excel. A spreadsheet can be simple, it can be complex, it can have functions all over the place, but it can also have complicated macros running in the background. A macro is defined as a set of actions stored in code that can be run at any time as many times as you want.

I used to think that functions like VLOOKUP, IF and SUM was all Excel had to offer. When I was building my model, I had so many things I wanted to add and improve upon, but time was holding me back. It was taking an age to add in the new features, constantly changing formulas to include the new information. Every time I added something, I would have to go through it all just to make sure my formulas were still correct, and it was working properly. I started to question if there is another way or is Excel just not good enough? Find out more about Excel Consultancy here.

Excel isn’t able to do what I need

Well, Excel isn’t perfect, I will admit that, but is anything?

When I realised the way I used formulas was extremely time consuming and inefficient, I felt like I was being held back and my model just wasn’t reaching its potential. So I began to explore.

I discovered Excel’s true capabilities and my faith in spreadsheets was restored. The issue wasn’t with Excel it was how I was using it, specifically how I integrated my processes into Excel. There isn’t much that Excel can’t do and you would be surprised to know how many manual processes can be automated in a spreadsheet, see some examples here.

When I was improving my model, I paid more attention to how I designed the processes and Excel became more of an ally than an enemy.

Excel isn’t just a spreadsheet, it should be a system

Sometimes we need to take a step back and reflect on what we are doing. Habits form a key part of our lives and they influence the way we act on a daily basis. Some of the tasks we do on a daily basis are purely habitual, therefore it is not a surprise that we have habits at work. We are oblivious to the time we waste repeating manual tasks because they become something we just do.

Spreadsheets should be a tool in your system, a tool that allows you to perform your business processes and procedures, a tool that makes your life easier so you can focus on growing your business. If we take a step back and think about how we use Excel, it is easy to identify the time-wasting culprits. Once you find the slow processes you can work out how to streamline it, make it simpler and make it more robust. You can then customise your Excel spreadsheets with your business processes in mind.

A small business owner who took a dive

Joe is a young business owner in the construction industry, he is doing extremely well but 6 months ago he reached his physical work limit but not his individual earning potential. He wanted to hire but he knew how much time he was wasting daily and didn’t want to bring people into a business built on inefficient foundations.

Joe was building reports in Excel, copy and pasting, formatting manually, typing formulas and categorising information manually, row by row, column by column. Any mistakes would take a keen eye to spot and would waste even more time. A typical report might take anywhere from 3 days to 5 days to create, leaving him stressed regularly trying to make deadlines and embarrassed if a client noticed mistakes in the report.

When I spoke to Joe and went over exactly what he was doing, I saw so much potential straight away. It turns out he was doing the exact same process for every single report he made, step by step, all identical.

The set categories of information he inputted manually became fixed tables that Excel can interact with automatically (the simplest way to do this is with VLOOKUPs).

The copy and pasting, formatting and formulas became a single button, this button was linked to a macro containing custom built code that replicated his process. Now all Joe needs to do is click a button and his data is imported and processed, into a version very close to the final report, within seconds.

The beauty of it is that the process is robust so there are no mistakes, giving you confidence in your work. The process that took 3-5 days now only takes a day at most, leaving him a lot of time to take on more clients and grow his business closer to its true potential.

Who am I?

My name is Giuseppe and I love all things Excel and efficiency and have the pleasure of working as an Excel Consultant for Effinovate. I love building automated processes and creating efficient spreadsheet systems. My journey started over 6 years ago, with a natural curiosity in football and probability. One day I realised I wanted to try and predict football scores. Fast forward a year and I had built an automated predictive model, as well as a solid knowledge foundation in Excel and VBA (the code that lives behind Excel).

Now I focus on building fully custom systems that integrate seamlessly with business processes, making work life easier. If you need help with your spreadsheets or need a custom solution tailored to your specific needs, feel free to get in touch. We are also building a set of free templates, go check them out and let us know what you think.

#vba?#excel?#automation?#spreadsheets?#everythingexcel