Excel

What is Excel?

Excel is perhaps the best known spreadsheet software to ever grace our screens. Developed by Microsoft, it was first unleashed back in 1985 and is still widely used around the globe today. It forms part of the Microsoft Office family alongside Word, PowerPoint and Outlook and can be used to organise everything from simple numbers to structured lists and plans and everything in between.

Why might you use Excel?

Chances are, you already do in some capacity. For many businesses, Excel is still the go to for working with tables of data which could be anything from budgets and forecasts to stock lists and schedules. It’s popular because it’s flexible, familiar and most of all - user friendly. Here are some common tasks where you might use Excel:

• Organising your data - Excel provides you with a structured grid interface where you can store, organise, manipulate and manage your data into rows and columns - simple but effective.

• Number crunching - Excel offers powerful mathematical, statistical and financial functions that allows you to perform complex (or not) calculations and analysis on your data. You can create formulas to automate calculations so those all important budget numbers are never far away.

• Data visualisation - For the visually inclined users and data consumers within your team, Excel includes a variety of charting and graphing tools that allows them to create visual representations of their data such as bar charts, line graphs, pie charts and scatter plots. 

• Financial modelling and budgeting - Another reason you might use Excel is for financial modelling, budgeting and forecasting. It lets you build dynamic models, run “what if” scenario analysis and keep a close eye on both expenditure and revenue streams.

• Creating reports - Excel allows your users to create (somewhat) professional looking reports and presentations by formatting data, adding charts and graphs and customising the layout and design of their spreadsheets. Reports can be easily shared with others or exported to other formats.

• Automation - If you have tech wizards in your team, they can use features such as macros and Visual Basic for Applications (VBA) to automate boring repetitive tasks and workflows. However, this side of Excel is not typically accessible to everyone and requires a certain level of know how.

• Sharing is caring - Thanks to OneDrive and Excel Online you can now collaborate, share and avoid emailing out 17 versions called “Final_v17_REALLYFINAL.xlsx”.

What are the drawbacks?

As great as Excel is, it does have limitations:

• Limited data capacity - Excel has limits on the amount of data it can process efficiently. Large datasets can dramatically slow down performance or cause the application to crash altogether which is never fun. You may also encounter issues when processing complex calculations. 

• Version 'control' - Collaborating on Excel files can be challenging, especially if you have multiple users making changes simultaneously. Although this has been somewhat negated with the introduction of Excel Online and OneDrive, keeping track of who changed what and when can still be difficult. 

• Mistakes are easily made - Unfortunately, Excel lacks built in mechanisms for ensuring data integrity and consistency. One wrong key stroke from your users has the potential to compromise a significant amount of data or indeed, the whole spreadsheet. Without proper validation and auditing processes in place, data accuracy is far from guaranteed.

• Visuals can be basic (yes, we did that on purpose) - Excel comes with basic charting and graphing tools which are great for simple visuals. But if you’re after advanced, highly customised data visualisations Excel will probably fall short. This means you might need additional software to get the results you require.

• Security risks - Excel files are vulnerable to common security risks including unauthorised access, data breaches and malware attacks. Password protection and encryption features are available but they won't necessarily protect you from determined attackers nor stand up to modern compliance and security standards set out by regulators.

• It doesn't like unstructured data - A creature of rigidness, Excel has been primarily designed for structured data stored in rows and columns. When faced with unstructured data such as text documents or multimedia files it hasn't really got a clue and well, you're best looking elsewhere.

• Dependency on individual expertise - Not everyone speaks fluent Excel and relying on a single individual with the relevant expertise can open your business up to all sorts of issues. If they go on holiday or leave the company altogether, what is your plan B?

What are the alternatives?

Whilst Excel might begin as a convenient tool to help you manage your data, as your business grows, your workbooks tend to grow in tandem getting bigger, more complex and harder to manage. This is particularly risky if you start relying on them as core systems or for critical operational processes.

An alternative approach is to replicate and transform your Excel based processes into a dedicated software solution. This involves identifying the key functions, calculations, data flows and automation currently done within Excel and rebuilding them in a more flexible and structured environment - often as a web based or desktop application.

For example:

• Data input forms in Excel can be recreated as user friendly interfaces with built in validation to reduce errors.

• Macros and formulas can be translated into backend logic or automated workflows using appropriate programming languages.

• Reports and charts can be turned into dynamic dashboards that update in real time.

• Manual processes such as data copy pasting or version tracking can be automated and centralised.

By doing this, you're able to maintain the logic and structure you've already built in Excel whilst gaining the benefits of a more robust, secure and scalable system. Winners.

Best projects for excel

Whilst it's easier than ever to find alternatives to Excel for almost any type of project, there are still some strong use cases - especially if you're a small businesses - where Excel remains a practical, cost effective choice:

Budget trackers - Providing your budgets and/or forecasts are relatively straight forward, Excel remains a simple but effective way to monitor income, expenses and savings keeping you in the loop about how you're performing.

Invoicing - If you're old school and prefer to remain that way, Excel can create professional invoices with automated calculations that are perfectly adequate for your clients (if they're old school too).

Inventory management - Is it ideal? No. Can it work? Yes. Small companies might find that they can track and manage their stock levels sufficiently using Excel.

Shift scheduler - If you've only got a few staff, Excel can be an efficient tool for building their rotas with availability, shift times and even conflict checks.

Mail merge data source - Keeping with the old school theme, Excel can be used as the foundation for managing email campaigns and bulk communications via Mail Merge. If you're still into that stuff.

Data analysis - Of course no list covering use cases for Excel would be complete without data analysis. Tried and tested, Excel can still be your go to for sorting, filtering and visualising data quickly and effectively. 

How can we help?

Our developers have worked with Microsoft Excel for many, many (many, many, many) years. From recreating Excel based databases in more modern and flexible systems to replicating and enhancing the automation you’ve built with macros, we can review how you’re currently using Excel, recommend better alternatives where needed and guide you through the transition.

FAQs

Q) Can existing Excel VBA macros and scripts be converted into clean web application code?

Yes. Existing VBA macros and scripts can be analysed and rebuilt using modern web technologies, retaining the required functionality whilst creating cleaner, more secure and maintainable code.

Q) How do developers clean up duplicate or bad data when migrating from Excel?

It depends on the developer but at Cool Code Company, we use data validation and cleansing processes to identify duplicate records, inconsistent formats, missing values and incorrect data. We may also use automated rules that can fix or standardise suitable records, whilst anything that can't be safely corrected is flagged for manual review.

Q) What tech stack is best for converting a desktop Excel system into a cloud application?

There’s no single best stack, the best choice depends on the complexity, integrations, security requirements and future scalability of the system along with your existing environment.

Q) Can a new web app still export reports back into Excel format for finance teams?

Yes. Web applications and indeed other software systems can export reports and data directly into Excel compatible formats allowing finance teams (and others) to continue using familiar spreadsheets for analysis, reporting and sharing.

Q) How do you handle complex financial calculations when moving from Excel to a custom app?

Existing formulas, rules and dependencies are mapped, tested and rebuilt in code with validation against the original Excel outputs to make sure the calculations remain accurate.

Further reading

Excel migration