The Holy Grail: Is Excel the best Data tool today?
“Excelling in Data Science can’t come without Excel. Finally! Someone said it.” (Okay, that’s not so bad!)
Launched in the 1980s, there’s probably no other tool that is so widely used across every industry in the world and almost every other work desk on the planet. Excel is the world’s most powerful spreadsheet software and you are about to find out just how undeniably awesome it can be to a data enthusiast. In my history of 2 years working at my company (you can find out more about me and my workplace on my LinkedIn), there’s probably never been a single day when I haven’t used Excel.
What are we hunting for in this article?
- What is Excel? (From Excel — For Excel — You will see what I mean)
- So what can Excel do? (The Can’s and the Can’ts)
- Where does it shine & backfire as a data analytics/data science tool for me? (Based on 2 years of daily treasure hunting)
- The must-know secret to master Excel (TOP SECRET)
- Other resources to head to — for learning Excel (Connect via LinkedIn for more)
IF EXCEL HAD AN ELEVATOR PITCH:
Right…..
.
.
“
Hi, I’m Excel. I can help you make tables, graphs, forms, etc. and I can store almost 1M rows and 16K columns of data in 255 different sheets! (A very huge amount of data I mean).
Can I pull data from Images, PDFs, SQL tables, etc.? YES! (I can see everything)
I can help you with data storage, manipulation, financials, statistical tests, and machine learning too (basically everything). Oh and sometimes if you’re tired, you can teach me something once and I can do it for you next time with Macros (Automation unlocked).
Yes, I am reliable and no I am not as fast as Python sometimes, but I can do everything and you should know I did Python’s job before it came around. I can run some Python commands nowadays too because you see I believe in inclusion. (Hence, I am not old school)
Regards,
Excel
”
Yep, pretty much it. Nice to meet you Excel!
So what can Excel do?
As mentioned in the elevator pitch, Excel can handle pretty much anything and you can make small to large & complex analyses in Excel in a simple way. The role it plays in the analyst’s daily life is where you can pull a little data to Excel, manipulate it in every possible way and you can drive into your boss’s cabin with an Excel sheet that glitters with numbers that serve a greater purpose.
(Be it Pivot tables, graphs, or just a simple table)
The bigger question now is — What can’t Excel do then?
The only catch is that the data is limited to 1M rows and with the onset of Big Data, data manipulation is the only savior for Excel to be relevant. Larger datasets are not made for Excel and that’s the reality.
Excel starts lagging at anywhere after 600K rows and 20 columns of data mixed with text and numbers even on one of the highest-end laptops and it loses its beauty when faced with larger datasets.
If Excel could do things with Big Data, that would be the annihilation of SQL and Visualisation tools. (Put ML in the picture and it's only a dream GUI for any data enthusiast)
Where does it shine & backfire as a data analytics/data science tool for me?
There is no doubt that Excel has always been one of those tools that each of us has started our data science journey with.
The GUI is very simple and has elements neatly put in place to manipulate data effectively. The sheets are simple and can be customized with color and grids so you can make pages that look like PowerPoint documents. If you can automate the data flow, you have a dynamic PPT that updates on your will by linking different sheets, workbooks, and data sources so you don’t have much to do after an analysis is prepared.
I like how you can make formulas with conditions that can pretty much be copied throughout pages without a lot of effort and can make your life easy. Another pro is the automated data filling for dates, numbers, etc.
Excel sheets can look like this:
Or like this…
The creativity is just left to you to work on. There are some nice templates to start with in case you need some guidance anyway.
However, it is a double-edged sword.
It is tough to debug Excel and moving one cell referencing here and there can completely change the numbers you see and take you down a completely different road.
Apart from the lags on a high quantity of data, it is a time-consuming process to type out formulas for complicated calculations where there might be a need for customized long formulas that might not be reproducible. On calculating too many things, it can break and you’re left trying to catch up on lost files and work or looking for the last file in the recovery.
Another downside is that the Replication of tables might be simple, but for graphs, it can take you a lot of time to do so. Changing the formatting, titles, and other column referencing, is a tough ask which a tool like Tableau could do in merely 10 seconds while looking prettier.
Otherwise, you have an amazing community that can answer any question you might have on an Excel calculation (since it has existed for almost 4 decades now)
We’ll discuss macros in another article outside this series, but let’s just say it's Excel’s way of saying no to manual repetition and embracing Automation in a very intricate yet simple way.
The must-know secret to Excel’s Mastery:
Find the odd one out:
SQL NINJA! PYTHON NINJA! DATA NINJA! EXCEL NINJA! ANALYTICS NINJA!
If you’re new to Excel, there’s a good chance you haven’t heard of Excel Ninjas. But there’s a reason, even if you have been close to Excel, you have never heard of SQL or Python Ninjas. Although they might exist, Excel Ninjas are real and there’s a very simple way they are blindingly fast with Excel functions.
- They don’t
- Use
- The
- — — MOUSE (or trackpad) — —
Are you going to be surprised if I tell you that there are two things you need to do to master Excel?
Use the Alt key on a Windows system in Excel and you’ll see what Excel can do with just the keyboard.
(Sorry Mac Users, we don’t get to become Excel Ninjas using the Keyboard)
For those who are on the Mac and are losing out on it:
The Alt command on the keyboard in an Excel workbook unlocks keyboard accessibility to the ribbons and functions offered by Excel (beyond the already existing super-cool shortcuts on the keyboard). This is how it looks and I think it is simply — STUNNING.
Try it out sometime. It’ll surprise you. If you practice enough, you’ll never use the mouse for Excel again.
Cheers to everyone who doubted Excel’s capability or importance in their data career. Hopefully, you have a different outlook on the tool now.
In the next article in this series, I’ll give you my perspective on the language used to query pretty much every company’s data today — SQL!
The series can be found here:
You might also want to read:
P.S. I don’t earn anything out of Medium since I write from India, but I’d be happier if you could add a few claps down here so it reaches a broader audience in case you find the series helpful. Happy learning!
Thank you for reading my article!
If you enjoyed it, please do leave a clap and I invite you to connect with me on LinkedIn for more insights and discussions!
LinkedIn: https://www.linkedin.com/in/yash-gupta-dss/
You can also follow my blog for updates and more articles on the series. Looking forward to connecting with you!
