Is Excel and VBA Dead? Why Data Analyst are leaving Excel for R and Python

How Martin transitioned from Philosophy to Data Analyst and Strategy Consulting

Learn to work more effectively with data. Free training

[JonathanNg]

This is an important but difficult topic to discuss. Improper use of Excel is probably the number one cause of data quality issues and time-consuming data work.

In many instances, R, Python or even the Power Query function within Excel can de-risk and automate traditional Excel solutions.


Excel still solves a number of challenges very effectively though.


So the real question becomes how do we use Excel properly in the right situations and limit its use where other tools would be far more appropriate?

[accountingsapayag]

The reality is that MS Excel has something R and Python don't have: The Sheet. I've been an accountant and a data analyst for like decades now(going 2). And when you have a raw "trash" file where you need to understand, Python and R won't do the job because it doesn't have the sheet. Python and R are only good when you already know the "landscape/geography" of the file. In Excel, you can immediately get a few data for testing manually. Whereas in Python and R, you need to run codes all the time and although it is fast, it's so hard to quickly browse the result sets. unlike in Excel. All the dirty work really goes through Excel VBA. ANd if one knows SQL, It can match the speed Python has to offer. The other problem with Python and R is that not all are coders. SO Excel can't be dead on that account.

[giuliko]

I work with VBA and R every single day. As an engineer and data analyst, I can say that depend on the project to be developed, I choose one of them. For example, if I need a tool that we could import some workbooks, make some calculations and why not plot some charts or a final report into the company, for third people to use, I go for vba. Not everybody has admin access to their machines to be able to install R and run an app. But if I'm developing for my personal analysis or online Dashboards or even more complex things such as machine Learning, I will go with R for sure. About speed, for sure R is faster but if we compare speed of things that we can write in vba and r, vba is not so low, definitely. Using arrays in vba you can be able to put your data in excel memory and work like R, in terms of processing data in memory. Once you do all your things, just drop that array in a sheet at once, with one line of vba code. So, my point is, it's possible to be really fast in vba also using not so many lines of code doing great things in memory with arrays. But in R you can go way crazier than vba in general terms. The language is also easier to learn than vba. Both of them worth learning.

[elmaestroco]

Discussion at 21:20 Not if you put a bit of work when you prepare the file. You can lock some cells, you can condition the data entry on the type of data/info to be filled in, you can add comments, reminders, etc. There are plenty of things that you can do beforehand to ensure that the file comes back to you the way you wanted.

[jamescrumpler]

Thank you for sharing! I work in public administration (Performance Auditing) at the state level, and recently started to introduce R into the workflow. Excel was the only tool that was used for a long time, but now datasets provided by state agencies are exceeding the capacity of Excel to handle. For this reason alone we needed something different. Especially with the Tidyverse, it is easier to get up and running with R, and teach the process to others who may not be familiar with R. Am already planning the second training for next month :)

[PaulRoneClarke]

Excel is a great introduction to data manipulation. At some point, if your role demands it, you may go “ I need more than this” and break open R or Python and start learning. But without Excel that journey would never have started for many.
Most analysis I know started with Excel and moved to programming later. Without Excel as their introduction, they would not be analysts today.

[malcorub]

Business users, especially in Finance, are not letting go of Excel anytime soon. Non technical users still heavily exchange detailed data in Excel files (as opposed to TXT and CSV) for upload to other programs.
I do think Microsoft Access is on a downward slope with less and less users.

[lawrencedoliveiro]

6:25 Best to think of VBA as primarily a tool for writing GUI automation macros. That kind of idea has the well-known pitfall that it rapidly gets unwieldy and unmaintainable, not to mention slow and frequently unreliable. R is a domain-specific language (DSL) for statistics. Python is a general-purpose programming language with a very versatile core that makes it conducive to building DSLs for many application areas. Hence and all those associated data-handling toolkits that are designed to work with Python.

[UncleLoren]

I enjoyed this informative discussion about the changing climate in data analysis. While I'm sure VBA isn't going away anytime soon, I'd strongly encourage people to pick up R and Python(Pandas). They have made me much more productive - and I wish I made the jump sooner. I would offer one note below about VBA:

As a veteran VBA/Access user -- who came up in the Accounting world -- I'd say that environment DOES overcome the issue mentioned here with Excel, where VBA's object model can't leverage the concept of a table (or essentially, a dataframe). Access is a great tool (and underused in many situations, b/c everyone loves to make Excel do things it shouldn't!). Nevertheless, for all of its merits as a small-user db, complete with reporting/query/GUI, R and Python have become my go to tool for many transformations/data munging processes.

I will be sure to check out the channel, and also check out the story of Martin's career transition. Great video, guys - thanks for sharing your views.

[johncardona]

Excel with M and DAX language is a TOP 3 best data analysis tool

[Spida]

Also, with Salesforce acquiring Tableau, the rise of PowerBI, Sensei and other BI tools, a video of one or more of those BI tools and explaining how python/r fit in with BI would be a cool topic. I use PowerBI a lot and know that you can create python/r visualizations. Python/R customized visuals are default options for PowerBI now, so it’s really exciting to see how everything comes together and the value that each tool brings to the table cohesively.

[stevennye]

Nice conversation, I am probably over committed to so hearing a completely different perspective is always good. The advantage of Excel will always be that it is ubiquitous, it may be an imperfect tool, but you don't have to justify a purchase to get someone started.

[mandelbro]

> VBA has no way of working with tables
Actually, it does, you just create Functions that can point to array variables. Any native feature of R can actually be coded for in VBA if you're willing to construct and test a function fit for purpose.
VBA is actually super fast with a good compiler, IF you know how to send sheet data directly to two dimensional data arrays, and then parse all your data directly from the arrays instead of referencing the worksheets which slows down analysis over 1000 times. You can also build your own custom functions to do custom statistical analysis, so it's not natively supported like in R, but to say you "can't do this with Excel" is quite incorrect. You can quite literally do any complex mathematics you want in Excel, and quickly if you understand how your CPU is processing information through its pipeline and how the VBA compiler works with arrays in rows more efficiently than columns. One big issue I have with Excel VBA though, is the difficulty of implementing parallel computing to take advantage of multi-core processors when you have repetitive calcs not bound by any dependency. Breaking away from single-thread operation is quite difficult, especially when in some applications, you might be able to get a 10 fold increase in calculation speed depending on how many CPU cores you have available.

[eriol]

I don't think so, excel and R and python could coexist with each other. Excel is useful to prototype everything thanks to the GUI, after that anyone could just replicate whatever they want to do in R or python.

excel + onedrive with power query is also an inexpensive solution for those who want to make an automated dashboard if they don't want to get power BI.

But VBA is ugh, I wish they enable other language to work in excel.

[idivergentes]

Resume:Excel for cell to cell calculations, Python/R for authentication and research.










You are welcome ;)

[Phoenixspin]

I'm happy to hear that Excel is not dead and that it has a use case in accounting. (I can vouch for that.) However, it seems to me that R would be a good tool to help accountants detect fraud.

[tomharrington]

The sweater game is strong with both of them.

[rrrprogram]

In my view... Its not possible to replace excel with any other tool... Microsoft has been working relentlessly to keep up with new tools... Human error will always be there... Be it excel or R or Python..

[shamusenright]

Great discussion fellas. I have used excel for many years entering and analysing survey data. I usually setup a spreadsheet whereby each row is a case and each column a different variable. I then use a combination of formulas and pivot tables to analyse the data. But as the databases have become larger, I realise the data analysis method is time consuming and inefficient. Would you suggest learning VBA or R in order to speed up the data analysis?

[roywilson]

Very interesting video.. Couldn't agree more about the merge and centre in excel. Have been using excel for several years along with the VBA and recently have started to learn Python. I am finding that using Python and packages like Pandas is so much more intuitive than using Excel VBA and so much quicker. Would like to also look seriously at R as a tool.