Excel. It is EVERYWHERE

November 25, 2018

When you start going out and speaking with businesses especially in finance and insurance you find something really interesting:

 

The business world runs on Excel

 

 

 

Yet, few people know how to use it defensively. This makes Excel probably the most dangerous software on the planet.

 

There is a site devoted to Excel horror stories: 

EuSpRIG Horror Stories

 

You can see how in some cases simple spreadsheet errors caused millions of dollars in loss.

 

 

 

The opportunity for getting basic reporting right before doing data science and AI

 

I consider myself very fortunate to be able to go to many companies, chat and see where they are at on their analytics journey.

 

In a single day I could have a conversation about the implementation of deep learning models, computer vision problems, but being based in regional Australia some of the conversations start like this:

 

“We spend days and days getting people to do exactly the same reporting each month in Excel, there has to be a better way”

 

“We have Power BI, we know it is good but nobody here knows how to use it and nobody wants to learn”

 

So while the rest of the world is talking about Data Science and Deep Learning there is a huge number of companies battling to get reliable numbers out of their business.

 

Why do I mention this? Well it is a huge opportunity for beginning data scientists and data analysts to get their foot in the door. It is also a great opportunity for companies just beginning their journey into data and analytics to get a taste of what is possible. I think it would be entirely possible to set up a consulting company helping people with Excel, corner those small to mid-sized companies who are just starting out and do very well indeed.

 

And yep there we go, I just did a search and there is a company here in Australia just doing this and doing very well. It’s a niche, but it is a sizeable niche and it is probably the same all over the world.

 

Excel Consulting Solutions

 

 

 

The foot in the door potential for Excel Consulting

 

A longer term play for an analytics consulting company is to offer solutions for stuff like Excel to mid-sized companies looking to increase their data analytics capabilities.

 

It’s a long term play, but one I could see leading to Power BI work, then to R scripting in Power BI, then to predictive modelling in R etc. In other words a consulting company starting at the very beginning could take a company all the way from Excel to AI over time.

 

One of my buddies recently said to me:

 

“You honestly think that finance and accounting people care at all about DAX and Excel macros, anything that looks like programming is going to be met with a wall of hate. They would rather just pay for it to go away rather than learn to do it themselves.”

 

So, I guess if what he says is true then their resistance could become your opportunity, even as a student and even with fairly limited experience you could do very well by offering these services, or even working while you are studying. If you are a finance professional updating your Excel skills could help you to stand out from the crowd.

 

 

 

 

Excel’s major problems and solutions you could offer

 

Actually after spending a bit of time looking at it the developers at Excel have good solutions to most of Excel’s major problems. It is really just that most people using Excel don’t know about these solutions. Therefore they don’t use Excel properly. Again this is your opportunity.

 

Problem 1: Excel formula hides errors in cells and object references

 

Solution: Use Excel Tables

 

 

 

 

The benefits are:

  • Rather than using cell references Excel tables allow you to reduce the chance of errors.

  • The column automatically fills down

  • The Excel table is added to the data model

 

Here is an overview of Excel Tables

 

 

Problem 2: Copy/ Paste Errors

 

Solution: Import Data Using Power Query

 

 

 

The benefits are:

  • Apply the import transformations once and never have to do it again

  • Automatically refreshes the data

  • Makes Excel reproducible

Here is a Power Query 101 guide

 

 

Problem 3: Analysis not repeatable, same manual process and calculations each time

 

Solution: Measures and Calculated Columns can be written once and used in multiple places

 

 

 

 

The benefits are:

  • Calculated columns are baked into the table, whereas measures have a filter context, so automatically update depending on what is selected in the pivot table or pivot chart.

  • Excel formula need not be repeated all over the spreadsheet, the code is written once and managed in one place

 

Here is a beginner guide to DAX

 

 

Problem 4: Joining data is painful, often a complex series of VLOOKUPs

 

The tables in the Excel Data Model can be managed almost like SQL tables using a GUI.

 

 

 

The benefits are:

  • No more complicated and bizarre VLOOKUPs

  • Drag and drop functionality to join tables, then you can access fields across multiple tables

  • Better understanding of table relationships

 

Here’s a guide to joining tables in Power Pivot

 

 

Conclusion

 

In the excitement around Data Science and AI I think we have forgotten about the majority of companies who are still wrangling Excel. This represents a huge opportunity for students, career transitioners and companies.

 

The same ideas above pop up in Power BI which may be a way to get some organisations started on their data journey.


 

Resources:

 

If you are using Excel and want to get across this functionality and more check out:

https://www.edx.org/xseries/microsoft-excel-data-analyst

 

   

 

 

Please reload

Recent Posts

Please reload

Archive

Please reload

Tags