Stuck waiting on Microsoft to grant you access to Python in Excel? Don't wait! Check out my video and start building skills NOW: ua-cam.com/video/0iV4FtpSriY/v-deo.html
Well spoken David. I grew up with Excel’s own automation language in the 90s - it could do a lot but was a pain to use. I was pleased when Excel VBA came in as I had some familiarity with it in MS Access. I have always maintained that a major problem “power” Excel users have is trying to use Excel as a database application. Although it has improved over the years it still does very little on its own to enforce data and calculation integrity as many companies and individuals (including my late brother) found out to their chagrin and $ loss.
@@DaveOnData Power Pivot is definitely not a database substitute, but rather a data manipulator/analyser very much dependent on an external database or similar.
I am stopping half way through your video to tell you that the way you communicate this information in a conversational manner is PURE GOLD. Now onto the rest of this video and others that you have produced 😇
Nice! I'd never heard of Python In Excel and you pretty well told me what I need to know. I've written some VBA for Excel and for me it was all about writing scripts that automated long sequences of steps I'd been doing by hand and finding tedious. It's an annoying programming language if you come from any of the "real" programming languages but usable if you need it.
Thanks for this! I appreciate how you broke this down. My main uses for VBA have been automation. I've never done any serious analytics. It also helps to know that Python runs in the cloud. That raises questions about being able to use a workbook that has Python while I'm on a plane or some other place with no internet.
You are most welcome! Regarding the Internet aspect, an option for some will be to run Python locally. Easily 99% of the Python code is the same whether you use Excel or another tool (e.g., Jupyter Notebooks). If needed, you can write the Pythom code on the airplane using a local tool and paste it into the workbook once you land.
Dave your T-shirts are great - I also love Iron Maiden….but recently listing Saxon.. My experience with vba is, that this is great tool for connecting with data bases and providing data from there. Ofcourse with little help of SQL. Now PQ cope with that but pure sql is more efficient (you can adjust your sql query using date, or other parameters from spreadsheet - in PQ is more difficult cause of safety issues). Regards and waiting for a new episode 😊
Saxon! I haven't listened to that band in a very long time. I had a copy of "Crusader" on tape in the 1980s. 😁 My next video, out next Wednesday, will explore the tradeoffs between pure Power Query and pure SQL for Python in Excel.
Best to use Python to externally perform data analysis and to then create spreadsheets. From within python, you can run macros from within Excel. You can also pass formulas to Excel as well. VBA is best used when you are already in Excel and want to run automation.
Many professionals don't have the option of running a local Python install. Python in Excel can be an easy way for these professionals to unlock advanced analytics.
Python in Excel as implemented by Microsoft does not satisfy data privacy requirements in Europe as required by GDPR regulations. As shown in this video, Microsoft only promises "not to persist data in the Microsoft Cloud". That does not preclude MS from copying your data or metadata to one of their non-cloud servers, etc. Not surprising, MS has a reputation for dragging its feet when it comes to complying with (data) privacy regulations instead of toeing the line as they should.
@@DaveOnData Meanwhile All of us could store/share our most valuable resource, private and/or company data stored in our Excel workbooks and databases over Power Query connections with Microsoft over Microsoft Cloud.
@@DaveOnData Depends on the exact use and the nature of data stored in the external cloud on the one hand, and how well the cloud service provider complies with GDPR regulations on the other hand.
@@DaveOnData Sometimes some companies intentionaly for some of their data use local/private datastores like local databases and/or local excels and/or send part of their data/knowledge in form of excels exclusivly to well known parties.
Most important I learned where Python runs! What was missing is that VBA is a cross Office tool. For example I wrote a tool which read email then worked on the excel attachment and then sent out emails. You can also access word and powerpoint with VBA.
TBH, you shouldn't use Excel as a database! That was declared years ago by the VBA project manager, Joel Spolsky. (VBA was introduced to Excel - before being migrated to other Office apps - in 1993. Blimey, that dates me!) Nothing stopping you using Excel as a front end to large data. It's only worthwhile to use Python if you've got a large volume of data and that should naturally reside in separate DBMS, so you would be writing dedicated Python apps against THAT data. Sounds like a 'Me too' token gesture. Anyone done a latency test against, say, the max rowcount of a wide worksheet uploaded to Python?
Thanks for the comment! I would humbly offer the following for consideration: 1 - I 100% agree that Microsoft Excel shouldn't be used as a database. However, I've been in tech for a long time and know that Excel will always be used in suboptimal ways. It's not that Excel is an inherently bad tool. It's just that Excel is very flexible and ubiquitous, making it ripe to be the proverbial hammer in search of a nail. 2 - Performing advanced analytics on "Excel-sized" data is nothing new. For example, the books of Dr. Wayne Winston has taught Excel users for years how to conduct logistic regression and market basket analyses using Solver. Python in Excel is arguably a logical addition to Excel to allow users a better alternative to hand-rolling analyses using Solver. Check out my UA-cam video comparing Solver to Python in Excel for logistic regression: ua-cam.com/video/ekT4Dx0D0qY/v-deo.html 3 - While it doesn't use a wide dataset, I have a UA-cam video showing the performance of Python in Excel using 330,000 rows of data that might interest you: ua-cam.com/video/0ICD9zMMzZ4/v-deo.html
Good video. As a CPA in Industry, the big thing is automation of mundane processes which is common in accounting. Most accountants don't want to spend extra time outside work learning python, since there is no time during work because of continuing deadlines required by mangagement. However, most IT departments don't want employees to use VBA macros in their workbooks for automation because of the concern of the macros being exposed to viruses. That leaves 3rd party packages like Alteryx that makes data transformation and automated communication with financial ERPs intuitively easy to implement. However, packages like Alteryx is cost prohibitive at $5,000 per individual license annually with a minimum three license purchase, which some companies don't want to invest in.
Many of my clients are interested in Python in Excel for the IT reasons you cite. These clients typically already use Azure, so the cloud-based aspect of Python in Excel appeals to their IT department.
Uh no, VBA is absolutely allowed as it already comes with Excel. Never heard of a company blocking VBA access as that would tremendously reduce work efficiency. It's downloading macro enabled files that is the issue which they have resolved by auto disabling it when downloaded.
Before jumping into Python in Excel, Power BI shoild be used by the more advance users who need to do lower level data analytics. Power BI's AI will detect patterns for you and make suggestions right off the bat with the data present to it.
Power BI is most definitely a powerful tool. However, I commonly see that professionals don't have access to Power BI, while they do have ready access to Excel. Also, with Copilot in Excel, you get similar AI capabilities.
Learn VBA! This language has been developed specifically for Office application development, while Python has only recently been "forced" into Office because this seems to be a new advertising strategy of the Microsoft masterminds. Python is not more powerful than VBA in anything, apart from the function libraries (written in other languages) that Python accesses; if NumPy or Panda were made directly available for Office, there would simply be no reason to use Python in Office. Learning VBA is also a good idea because VB (even if no longer developed by MS) is an excellent bridge language to Xojo, B4X and Mercury - an investment in the future.
Excellent. Thank you for the explanation. I have one more question. Is it possible to insert or execute Python formulas in a cell using VBA? If this is possible, it seems like additional automation could be achieved.
I checked with my contact at Microsoft and here's the response: Yup, you just write out the formula using range.formula = “=PY(, )” In beta, you currently have to have users click the try preview first otherwise it might throw an exception
VBA was what made me want to start learning programming… I think VBA is more useful on Access than on Excel. Sadly, most of the content that we find online is teaching how to use it on Excel.
Great video, I have just subscribed your channel. Thank you for expanding the differences between Python run in Excel and on PC. I wanted to use Python on my company PC for some automations, however I don't feel comfortable with it having access to everything (as a beginner I might make some mistakes resulting in unexpected outcome). Therefore I will limit its use to safe boundaries within Excel and try to look for automations in Power Automate/Apps (not big fan of VBA).
When Python for Excel came out, I looked into it and just didnt see the use of it. I use Python for Data Analytics using the pandas library and tend to only use Excel just for data output for people who would never understand any programming, they just want to see the end result. So I fail to see Python for Excel as a use to anyone, Especially if for example you extract data from SQL, are you wanting to upload your servers credentials to Microsoft ??? If you can use Python your just use Python otherwise use power query within Excel, Python for Excel just doesnt fit in any situation.
If I may be so bold, I would offer the following for consideration: 1 - Python in Excel uses a Power Query connection as an external data source. The authentication to the data source happens locally. 2 - Excel users have been conducting advanced analytics for many years. For example, using the Solver to implement logistic regression. Python in Excel offers a better way for these users to conduct data analyses. I have a video comparing Solver vs statsmodels for linear regression: ua-cam.com/video/ekT4Dx0D0qY/v-deo.html 3 - Python in Excel offers a very low barrier to entry for Excel users. Regarding #3, I have clients that are highly interested in Python in Excel due to their IT departments preventing local Python installations. However, these clients are already using Azure and they see this as very easy way to unlock advanced analytics in their work. It is worthy to note that these clients are not IT folks (e.g., developers), but are data-savvy business professionals.
Hi David, thank you for this. I have a different question for you. Between using Python in Excel and Power Query, which one would you recommend? If possible, would you create a video explaining the differences and benefits of each option? Thank you in advance.
Python for the data analysis, VBA for controlling getting input data in environments that don't connect Data Verse or other outside data sources. (Think ETL actions across SharePoint/OneDrive locations.) _WS
@@DaveOnData Sure, but the issue is not a lot of businesses open up DataVerse or DataLake to operational level psudo-developers so you're stuck back into the VBA work-around for automation as the odds are power automate isn't connected either and you will not have Pro license.
It does, indeed! Although "fast" is a relative term. You may find my video interesting. It demonstrates importing 330,000 rows of data into Python in Excel: ua-cam.com/video/0ICD9zMMzZ4/v-deo.html
Wow! Thank you for taking the time to write these words - they are much appreciated. As I will discuss in my next video, I'm a huge fan of PQ in the right situations. In others, technologies like SQL and Python in Excel are the ticket.
@@DaveOnData, for me PQ has one advantage of being available going back to Excel 2010. Also a lot of hospitals don't allow Macros. But following you for Python and dipping my toes further into SQL. One of the hospitals here in Chicago I work the most with has Office 2016, very limited access to SQL and PBI. They allow one Director one folder so I can do somethings in VBA for her. But I agree with you.
Very insightful! Can tell that you're definitely smart. Wish you would make more videos about data analytics. Will start binge watching your videos soon 😂
But… I thought I had recently read that Microsoft is no longer supporting VBA. And are there not existing Python libraries that can “read” or “write to” Excel files? (Kinda a noob here, but wanting to add to my skill set and seeing how Python might help - my college “programming” was in MATLAB and Fortran77 (digital compiler), and I took a bit of Python3 during the early months of the pandemic. I essentially work as a mechanical product engineer.)
Great questions! First, regardless of Microsoft trying to migrate folks off VBA (e.g., to Office Script), there is a huge installed base of VBA code and VBA developers. It's not going anywhere anytime soon. Second, programming languages like R and Python have libraries for reading in, manipulating, and writing out Microsoft Excel files. However, this isn't always an option for many Excel users for two primary reasons: 1- They don't want to install and maintain a programming language on their laptop. Python, in particular, is a pain in this regard. 2- If they wanted to do this, they couldn't because of restrictions put in place by their IT department. Many of my clients are excited by Python in Excel because it provides a quick and easy path to advanced analytics within Excel.
@jasongins - In general, I'm a big fan of Anaconda. For example, I use Anaconda in my Python courses at TDWI conferences. That being said, I'm not super familiar with Anaconda Cloud. If I put on my former Enterprise Architect hat, the things I would be curious about the costs and security.
It is worthless trying to use Python in my company as they would not allow IT any IDE to be installed due to compliance reasons (they block every, everything). So I got into VBA long time ago.
This is precisely why many of my clients are excited by Python in Excel - no local installation is required. Additionally, many of their IT departments are already using Azure, making Python in Excel a very attractive option for them.
This is my opinion. Please take it as one perspective among many. I'm going to make an assumption that Microsoft Excel is your data analysis tool of choice. As a novice Excel Data Analyst, it is unlikely that you will need VBA anytime soon - maybe never. Python in Excel is really for more advanced analytics, so I would first focus on the fundamentals using out-of-the-box features. For example, my exploratory data analysis (EDA) with Excel tutorial series: ua-cam.com/play/PLTJTBoU5HOCRFQhfU1gg2ciNpS_evWKR7.html
As I'm not familiar with AP/AR tasks, I wouldn't be able to comment. That being said, I know that Finance professionals often use VBA to help automated their Excel-centric processes.
Office Scripts is where Microsoft wants people to go. That being said, there is a large installed base of VBA code/users. In my experience, VBA isn't going anywhere anytime soon.
It's a glorified version of the code recorder, which has been in Office products for years. The recording facility was removed from Powerpoint some years ago because processing the actions became too difficult to code! So maybe that's MS' thinking about future Office products. Nothing you can't do in VBA - even making calls to the .Net and Windows libraries if you need lower level control.
I respectfully disagree with this. Python in Excel does not compete with VBA or Office Scripts in any way. It is designed specifically to enable data analyses that are difficult or impossible to do with out-of-the-box Excel. For example, you could code up a random forest algorithm in VBA, but why would you do that when Python in Excel gives you access with just a few lines of code? Also, Python in Excel addresses a huge shortcoming in doing analytics with Excel - having to hand-roll worksheet templates using Solver. I have a video that discusses this: ua-cam.com/video/ekT4Dx0D0qY/v-deo.html
Hello! Could Excel 2023 with the addition of Python and Copilot recognize the data type and approximate it correctly, and then find the transition points from one type of pattern to another?
Deep neural networks can learn very complex relationships from data. The trick is that they need a lot of examples from which to learn. So it is possible in theory.
I have a challenge now trying to get one of my macro sheets to run on an older windows 10 system with intel core duo processor, it works but a little slow, any tips?
@@DaveOnData Update- It wasn’t an issue with the VBA script, the solution was to not do this A:A in the formulas of my sheet but limit it e.g A1:A500, got a lot of automation going on
This is a complaint I frequently hear, and I understand it. There are also many organizations invested in Azure and they see the ease of use as a big win over maintaining local Python installations.
@@DaveOnData yes - I totally understand this is for the Azure users. It basically integrates with Azure, so from that point of view it makes perfect sense. However most users won't use Azure. And since most users also won't be into Data Engineering/Analysis, it does not make sense for them. Still - the news titles that "Python is available in Excel" come misleading, as people understand that it would be available as VBA and would do similar things and they think they could use it to a degree. Also providing a full-blown Python in Excel would be a total security compromise, but hey isn't VBA a security risk too? I remember the 2000s era when you would get each week at least one email with attached word/excel file with some VBA malware. So what. I guess solution is to still - limit the Python, therefore maybe provide a Python fork, with MS Office-specific functions, but all of it to function locally. Then MS would get more people into MS Office customizations. If anyone really needs it. Maybe the Libre Office folks would get this idea faster than MS guys and provide this for us, as sometimes it would be significantly faster to provide a Spreadsheet file + attached script for a task, instead to code a GUI from scratch and provide custom functionality... No idea how many would need it, but guess there is a market for such things.
I would offer this for consideration. In my experience, most Excel users do not immediately associate Python in Excel with VBA/Excel automation. In fact, my experience has been most Excel users don't really know what the point of Python in Excel is. 🤣 The best way I've found to explain Python in Excel is that it allows for advanced analytics that used to be hand-rolled using Solver or just weren't possible unless you purchased an Excel Add-in.
the thing is that microsoft does NOT control python. imagine large companies starts using it and in 10 years there is ok we have python 4 or something which is NOT compatible with python 2 (like python 2-3) for me i’m ok but imagine billion dollars banks…. microsoft must control the programming language -> office script but well it’s fat far far behind VBA honestly. with vba alone you can do many things but office script you may need to use power automate aswell which makes everything a big mess
That is always a consideration with Excel - make sure it will scale to the size of your data. One great thing about Python in Excel is that it provides a smooth path to scale. For example, moving to Jupyter Notebooks if needed.
VBA is also used for interactive controls, for instance form buttons to add an entry to a worksheet. Unfortunately, Microsoft does not support VBA for iOS devices, which means a lot of Office automation is not available on that platform. iPhones and iPads have grown in the business world. So people are starting to abandon Microsoft as their goto one-stop-shop.
Interesting perspective! Personally, I'm not seeing this as all of my clients are Microsoft shops. In particular, my SMB clients are heavy Microsoft Excel users on Windows.
Python in Excel is more like Excel in Python. Which, of course, really doesn't work. Good advanced geeky stuff, but useless in 99% of most Excel environments. As David does acknowledge. If you're serious about Excel, don't waste time on Python. For advanced Excel, that really works, Power Query is the way to go! Otherwise, you are coding Python, not Excel...
I'd like to offer this up for consideration. For years, Dr. Wayne Winston has authored books on performing advanced analytics in Excel (e.g., using the Solver). Python in Excel is a much better way to do these things.
Ah, C++ is my favorite software engineering language! To be honest, as much as I love C++, the speed of languages like R and Python really doesn't matter for analytics.
Stuck waiting on Microsoft to grant you access to Python in Excel? Don't wait! Check out my video and start building skills NOW: ua-cam.com/video/0iV4FtpSriY/v-deo.html
I like this video ❤❤❤
Well spoken David. I grew up with Excel’s own automation language in the 90s - it could do a lot but was a pain to use. I was pleased when Excel VBA came in as I had some familiarity with it in MS Access. I have always maintained that a major problem “power” Excel users have is trying to use Excel as a database application. Although it has improved over the years it still does very little on its own to enforce data and calculation integrity as many companies and individuals (including my late brother) found out to their chagrin and $ loss.
Thank you for taking the time to share your experiences. Any thoughts on Power Pivot as a database substitute?
@@DaveOnData Power Pivot is definitely not a database substitute, but rather a data manipulator/analyser very much dependent on an external database or similar.
I am stopping half way through your video to tell you that the way you communicate this information in a conversational manner is PURE GOLD. Now onto the rest of this video and others that you have produced 😇
Wow! Thank you so much for the kind words. I am happy to hear that you are enjoying the content.
Nice! I'd never heard of Python In Excel and you pretty well told me what I need to know. I've written some VBA for Excel and for me it was all about writing scripts that automated long sequences of steps I'd been doing by hand and finding tedious. It's an annoying programming language if you come from any of the "real" programming languages but usable if you need it.
Excellent! Glad that you found the video's content useful.
Chat GPT can write VBA no problem
Thanks for this! I appreciate how you broke this down.
My main uses for VBA have been automation. I've never done any serious analytics.
It also helps to know that Python runs in the cloud. That raises questions about being able to use a workbook that has Python while I'm on a plane or some other place with no internet.
You are most welcome!
Regarding the Internet aspect, an option for some will be to run Python locally.
Easily 99% of the Python code is the same whether you use Excel or another tool (e.g., Jupyter Notebooks).
If needed, you can write the Pythom code on the airplane using a local tool and paste it into the workbook once you land.
so... the proper question is...
Office Script vs VBA
M Code vs Python Excel
DAX vs Python Excel
Agreed! I would also add (which will be the subject of my next video):
SQL vs Power Query/M
Oh, and here are a couple more:
Solver vs Python in Excel
Analysis ToolPak vs Python in Excel
Dave your T-shirts are great - I also love Iron Maiden….but recently listing Saxon.. My experience with vba is, that this is great tool for connecting with data bases and providing data from there. Ofcourse with little help of SQL. Now PQ cope with that but pure sql is more efficient (you can adjust your sql query using date, or other parameters from spreadsheet - in PQ is more difficult cause of safety issues). Regards and waiting for a new episode 😊
Saxon! I haven't listened to that band in a very long time. I had a copy of "Crusader" on tape in the 1980s. 😁
My next video, out next Wednesday, will explore the tradeoffs between pure Power Query and pure SQL for Python in Excel.
@@DaveOnData Interesting video considering MS access comes with a 365 subscription and easily interfacing with each other.
Best to use Python to externally perform data analysis and to then create spreadsheets. From within python, you can run macros from within Excel. You can also pass formulas to Excel as well. VBA is best used when you are already in Excel and want to run automation.
Many professionals don't have the option of running a local Python install. Python in Excel can be an easy way for these professionals to unlock advanced analytics.
Python in Excel as implemented by Microsoft does not satisfy data privacy requirements in Europe as required by GDPR regulations. As shown in this video, Microsoft only promises "not to persist data in the Microsoft Cloud". That does not preclude MS from copying your data or metadata to one of their non-cloud servers, etc. Not surprising, MS has a reputation for dragging its feet when it comes to complying with (data) privacy regulations instead of toeing the line as they should.
A fair criticism! We shall see how Microsoft will address (or not) what you mention here.
@@DaveOnData Meanwhile All of us could store/share our most valuable resource, private and/or company data stored in our Excel workbooks and databases over Power Query connections with Microsoft over Microsoft Cloud.
@RedShiftGalaxy - Do you mean to say that organizations should never use cloud providers?
@@DaveOnData Depends on the exact use and the nature of data stored in the external cloud on the one hand, and how well the cloud service provider complies with GDPR regulations on the other hand.
@@DaveOnData Sometimes some companies intentionaly for some of their data use local/private datastores like local databases and/or local excels and/or send part of their data/knowledge in form of excels exclusivly to well known parties.
Thanks David. Been wondering about this question for a while. I now know the answer.
My pleasure! I hope you found the content useful.
Ok...this is the first video on python that made any sense to me! Well done on your style of teaching.
Thank you for the comment! I'm glad you found the video useful.
Most important I learned where Python runs! What was missing is that VBA is a cross Office tool. For example I wrote a tool which read email then worked on the excel attachment and then sent out emails. You can also access word and powerpoint with VBA.
Glad you found some aspect of the video useful!
TBH, you shouldn't use Excel as a database! That was declared years ago by the VBA project manager, Joel Spolsky. (VBA was introduced to Excel - before being migrated to other Office apps - in 1993. Blimey, that dates me!) Nothing stopping you using Excel as a front end to large data. It's only worthwhile to use Python if you've got a large volume of data and that should naturally reside in separate DBMS, so you would be writing dedicated Python apps against THAT data. Sounds like a 'Me too' token gesture. Anyone done a latency test against, say, the max rowcount of a wide worksheet uploaded to Python?
Thanks for the comment! I would humbly offer the following for consideration:
1 - I 100% agree that Microsoft Excel shouldn't be used as a database. However, I've been in tech for a long time and know that Excel will always be used in suboptimal ways.
It's not that Excel is an inherently bad tool. It's just that Excel is very flexible and ubiquitous, making it ripe to be the proverbial hammer in search of a nail.
2 - Performing advanced analytics on "Excel-sized" data is nothing new. For example, the books of Dr. Wayne Winston has taught Excel users for years how to conduct logistic regression and market basket analyses using Solver.
Python in Excel is arguably a logical addition to Excel to allow users a better alternative to hand-rolling analyses using Solver. Check out my UA-cam video comparing Solver to Python in Excel for logistic regression: ua-cam.com/video/ekT4Dx0D0qY/v-deo.html
3 - While it doesn't use a wide dataset, I have a UA-cam video showing the performance of Python in Excel using 330,000 rows of data that might interest you: ua-cam.com/video/0ICD9zMMzZ4/v-deo.html
Can't wait for more. Excellent again, so glad I found you 😀
Thank you!
Good video. As a CPA in Industry, the big thing is automation of mundane processes which is common in accounting. Most accountants don't want to spend extra time outside work learning python, since there is no time during work because of continuing deadlines required by mangagement. However, most IT departments don't want employees to use VBA macros in their workbooks for automation because of the concern of the macros being exposed to viruses. That leaves 3rd party packages like Alteryx that makes data transformation and automated communication with financial ERPs intuitively easy to implement. However, packages like Alteryx is cost prohibitive at $5,000 per individual license annually with a minimum three license purchase, which some companies don't want to invest in.
Many of my clients are interested in Python in Excel for the IT reasons you cite. These clients typically already use Azure, so the cloud-based aspect of Python in Excel appeals to their IT department.
Uh no, VBA is absolutely allowed as it already comes with Excel. Never heard of a company blocking VBA access as that would tremendously reduce work efficiency. It's downloading macro enabled files that is the issue which they have resolved by auto disabling it when downloaded.
Very good.
Your "700 million users..
" etc etc., sum the question in a nutshell.
Cheers and thanks
Thank you! I am glad you enjoyed the video.
Before jumping into Python in Excel, Power BI shoild be used by the more advance users who need to do lower level data analytics.
Power BI's AI will detect patterns for you and make suggestions right off the bat with the data present to it.
Power BI is most definitely a powerful tool. However, I commonly see that professionals don't have access to Power BI, while they do have ready access to Excel. Also, with Copilot in Excel, you get similar AI capabilities.
@DaveOnData Ah! I didn't know about Copilot in Excel thx. I'm a bit rusty - was in the Google ecosystem for a while. I'll look into it!
Learn VBA! This language has been developed specifically for Office application development, while Python has only recently been "forced" into Office because this seems to be a new advertising strategy of the Microsoft masterminds. Python is not more powerful than VBA in anything, apart from the function libraries (written in other languages) that Python accesses; if NumPy or Panda were made directly available for Office, there would simply be no reason to use Python in Office. Learning VBA is also a good idea because VB (even if no longer developed by MS) is an excellent bridge language to Xojo, B4X and Mercury - an investment in the future.
Thank you for offering your perspective! What do you think about Python in Excel for helping users perform analytics?
NumPy and Panda are Python, you can't say there is no reason to use Python in Office xD
Yea it’s a lot easier to access API data from Python than VBA
Excellent. Thank you for the explanation. I have one more question. Is it possible to insert or execute Python formulas in a cell using VBA? If this is possible, it seems like additional automation could be achieved.
I checked with my contact at Microsoft and here's the response:
Yup, you just write out the formula using range.formula = “=PY(, )”
In beta, you currently have to have users click the try preview first otherwise it might throw an exception
VBA was what made me want to start learning programming…
I think VBA is more useful on Access than on Excel. Sadly, most of the content that we find online is teaching how to use it on Excel.
Curious - Why VBA with Access? Are you using it to build applications?
Excellent! Very clearly explained. Thanks
Thank you! I appreciate the kind words and I am glad you found the video useful.
Great video, I have just subscribed your channel. Thank you for expanding the differences between Python run in Excel and on PC. I wanted to use Python on my company PC for some automations, however I don't feel comfortable with it having access to everything (as a beginner I might make some mistakes resulting in unexpected outcome). Therefore I will limit its use to safe boundaries within Excel and try to look for automations in Power Automate/Apps (not big fan of VBA).
@tomoleusz - Thank you for taking the time to leave a comment and I am glad to hear you have found my content useful!
When Python for Excel came out, I looked into it and just didnt see the use of it. I use Python for Data Analytics using the pandas library and tend to only use Excel just for data output for people who would never understand any programming, they just want to see the end result.
So I fail to see Python for Excel as a use to anyone, Especially if for example you extract data from SQL, are you wanting to upload your servers credentials to Microsoft ???
If you can use Python your just use Python otherwise use power query within Excel, Python for Excel just doesnt fit in any situation.
If I may be so bold, I would offer the following for consideration:
1 - Python in Excel uses a Power Query connection as an external data source. The authentication to the data source happens locally.
2 - Excel users have been conducting advanced analytics for many years. For example, using the Solver to implement logistic regression. Python in Excel offers a better way for these users to conduct data analyses. I have a video comparing Solver vs statsmodels for linear regression: ua-cam.com/video/ekT4Dx0D0qY/v-deo.html
3 - Python in Excel offers a very low barrier to entry for Excel users.
Regarding #3, I have clients that are highly interested in Python in Excel due to their IT departments preventing local Python installations. However, these clients are already using Azure and they see this as very easy way to unlock advanced analytics in their work. It is worthy to note that these clients are not IT folks (e.g., developers), but are data-savvy business professionals.
Hi David, thank you for this. I have a different question for you. Between using Python in Excel and Power Query, which one would you recommend? If possible, would you create a video explaining the differences and benefits of each option? Thank you in advance.
@mcwahaab - I cover your question in this video: ua-cam.com/video/rMjtcsmHf9g/v-deo.html
@@DaveOnData - Thanks a lot. Appreciate
Great video! 10 mins really insightful for someone like me, who doesn't knows what's what. Thanks!
You are welcome! Glad to hear you found the content useful.
Python for the data analysis, VBA for controlling getting input data in environments that don't connect Data Verse or other outside data sources. (Think ETL actions across SharePoint/OneDrive locations.) _WS
@whosestone - What are your thoughts on Power Query for ETL?
@@DaveOnData Sure, but the issue is not a lot of businesses open up DataVerse or DataLake to operational level psudo-developers so you're stuck back into the VBA work-around for automation as the odds are power automate isn't connected either and you will not have Pro license.
Sir, 🙏🏼 i dont know much but i think python helps to process data very fast✨
It does, indeed! Although "fast" is a relative term. You may find my video interesting. It demonstrates importing 330,000 rows of data into Python in Excel: ua-cam.com/video/0ICD9zMMzZ4/v-deo.html
@@DaveOnData ✨🙏🏼✨
Nice insight. I'm heavily in the PQ house. But watching your videos on Python has opened up some possibilities in my little brain.
Thanks as always.
Wow! Thank you for taking the time to write these words - they are much appreciated. As I will discuss in my next video, I'm a huge fan of PQ in the right situations. In others, technologies like SQL and Python in Excel are the ticket.
@@DaveOnData, for me PQ has one advantage of being available going back to Excel 2010. Also a lot of hospitals don't allow Macros. But following you for Python and dipping my toes further into SQL.
One of the hospitals here in Chicago I work the most with has Office 2016, very limited access to SQL and PBI. They allow one Director one folder so I can do somethings in VBA for her. But I agree with you.
Python, VBA, M, SQL, Dax, even Excel worksheet functions - all great tools to me.
@rogeryang18 - Indeed! Python in Excel is another tool specifically designed for analytics.
Very insightful! Can tell that you're definitely smart.
Wish you would make more videos about data analytics. Will start binge watching your videos soon 😂
Good news - I have many videos on data analytics topics, including machine learning. 😀
But… I thought I had recently read that Microsoft is no longer supporting VBA. And are there not existing Python libraries that can “read” or “write to” Excel files? (Kinda a noob here, but wanting to add to my skill set and seeing how Python might help - my college “programming” was in MATLAB and Fortran77 (digital compiler), and I took a bit of Python3 during the early months of the pandemic. I essentially work as a mechanical product engineer.)
Great questions!
First, regardless of Microsoft trying to migrate folks off VBA (e.g., to Office Script), there is a huge installed base of VBA code and VBA developers. It's not going anywhere anytime soon.
Second, programming languages like R and Python have libraries for reading in, manipulating, and writing out Microsoft Excel files. However, this isn't always an option for many Excel users for two primary reasons:
1- They don't want to install and maintain a programming language on their laptop. Python, in particular, is a pain in this regard.
2- If they wanted to do this, they couldn't because of restrictions put in place by their IT department.
Many of my clients are excited by Python in Excel because it provides a quick and easy path to advanced analytics within Excel.
@DaveOnData what's your take on anaconda cloud notebook as an option for people with IT department restrictions on a local install?
@jasongins - In general, I'm a big fan of Anaconda. For example, I use Anaconda in my Python courses at TDWI conferences. That being said, I'm not super familiar with Anaconda Cloud. If I put on my former Enterprise Architect hat, the things I would be curious about the costs and security.
It is worthless trying to use Python in my company as they would not allow IT any IDE to be installed due to compliance reasons (they block every, everything). So I got into VBA long time ago.
This is precisely why many of my clients are excited by Python in Excel - no local installation is required. Additionally, many of their IT departments are already using Azure, making Python in Excel a very attractive option for them.
@Daveondata for a novice data analyst, do you recommend learning VBA or Python+Excel given the other tools available?
This is my opinion. Please take it as one perspective among many.
I'm going to make an assumption that Microsoft Excel is your data analysis tool of choice. As a novice Excel Data Analyst, it is unlikely that you will need VBA anytime soon - maybe never. Python in Excel is really for more advanced analytics, so I would first focus on the fundamentals using out-of-the-box features. For example, my exploratory data analysis (EDA) with Excel tutorial series: ua-cam.com/play/PLTJTBoU5HOCRFQhfU1gg2ciNpS_evWKR7.html
Sounds cool, thanks. How could this help with ap/ar tasks automation?
As I'm not familiar with AP/AR tasks, I wouldn't be able to comment. That being said, I know that Finance professionals often use VBA to help automated their Excel-centric processes.
What about Office Scripts? I think that’s the new VBA that Microsoft is pushing users towards.
Office Scripts is where Microsoft wants people to go. That being said, there is a large installed base of VBA code/users.
In my experience, VBA isn't going anywhere anytime soon.
@@DaveOnData Being just 365 hurts it as well.
It's a glorified version of the code recorder, which has been in Office products for years. The recording facility was removed from Powerpoint some years ago because processing the actions became too difficult to code! So maybe that's MS' thinking about future Office products. Nothing you can't do in VBA - even making calls to the .Net and Windows libraries if you need lower level control.
I respectfully disagree with this. Python in Excel does not compete with VBA or Office Scripts in any way. It is designed specifically to enable data analyses that are difficult or impossible to do with out-of-the-box Excel. For example, you could code up a random forest algorithm in VBA, but why would you do that when Python in Excel gives you access with just a few lines of code?
Also, Python in Excel addresses a huge shortcoming in doing analytics with Excel - having to hand-roll worksheet templates using Solver. I have a video that discusses this: ua-cam.com/video/ekT4Dx0D0qY/v-deo.html
Great video. Very informative to me with no coding/programming experience.
@KeithJBrett - Thank you for this feedback! I'm glad you found the video useful.
Hello! Could Excel 2023 with the addition of Python and Copilot recognize the data type and approximate it correctly, and then find the transition points from one type of pattern to another?
Based on my experience, this is possible. The caveat would be that crafting the right prompt(s) can be tricky depending on the data.
@@DaveOnData I have several formulas that I use, can neural network combine them into one and output them as one formula?
Deep neural networks can learn very complex relationships from data. The trick is that they need a lot of examples from which to learn.
So it is possible in theory.
My 1st ver. was ver. 5 (1993) which included VBA for 1st time. It came on 11 floppy discs.
Yes! I remember the days when I would install Office using 30+ 3.5" floppy disks. 🤣
Really great explanation, I think VBA saves a hell of alot of time
When it comes to automating Excel-based processes, it is the de facto standard!
I have a challenge now trying to get one of my macro sheets to run on an older windows 10 system with intel core duo processor, it works but a little slow, any tips?
Unfortunately, I haven't used VBA in a really long time. I wouldn't know where to begin.
@@DaveOnData Update- It wasn’t an issue with the VBA script, the solution was to not do this A:A in the formulas of my sheet but limit it e.g A1:A500, got a lot of automation going on
Thank you for that information- excellent!
You are welcome. I hope you find my future videos useful as well.
Great video! Thank you! Subbed! :)
You are welcome! Glad you enjoyed the video.
Thank you for the explanation. I don't like they that the data flies to the cloud. It should stay local. Anyway. I'm not gonna use it in Excel.
This is a complaint I frequently hear, and I understand it. There are also many organizations invested in Azure and they see the ease of use as a big win over maintaining local Python installations.
@@DaveOnData yes - I totally understand this is for the Azure users. It basically integrates with Azure, so from that point of view it makes perfect sense. However most users won't use Azure. And since most users also won't be into Data Engineering/Analysis, it does not make sense for them. Still - the news titles that "Python is available in Excel" come misleading, as people understand that it would be available as VBA and would do similar things and they think they could use it to a degree. Also providing a full-blown Python in Excel would be a total security compromise, but hey isn't VBA a security risk too? I remember the 2000s era when you would get each week at least one email with attached word/excel file with some VBA malware. So what. I guess solution is to still - limit the Python, therefore maybe provide a Python fork, with MS Office-specific functions, but all of it to function locally. Then MS would get more people into MS Office customizations. If anyone really needs it. Maybe the Libre Office folks would get this idea faster than MS guys and provide this for us, as sometimes it would be significantly faster to provide a Spreadsheet file + attached script for a task, instead to code a GUI from scratch and provide custom functionality... No idea how many would need it, but guess there is a market for such things.
I would offer this for consideration. In my experience, most Excel users do not immediately associate Python in Excel with VBA/Excel automation. In fact, my experience has been most Excel users don't really know what the point of Python in Excel is. 🤣
The best way I've found to explain Python in Excel is that it allows for advanced analytics that used to be hand-rolled using Solver or just weren't possible unless you purchased an Excel Add-in.
I can think of nothing more annoying than having a msgbox that pops up telling me to rename every new sheet
Agreed! However, the annoyance aligns with the hypothetical example.
@@DaveOnData true. The video was great!
Excellent explanation. Thank you!
You are welcome! Glad you found the video useful.
Very well explained.
Thank you for the feedback, it is greatly appreciated!
Thanks for the info
You are welcome! Unfortunately, Microsoft hasn't done a good job of making it clear how Python in Excel adds value to Microsoft Excel.
just use python since you can always do more if you have to
This can be problematic for many professionals. For example, many cannot install Python locally due to IT restrictions.
Thank you Dave for the this great video with clear explanation. Love the shirt!
Glad you liked the video and the t-shirt! 😁
Thanks David
You are welcome! I hope you found the video useful.
the thing is that microsoft does NOT control python. imagine large companies starts using it and in 10 years there is ok we have python 4 or something which is NOT compatible with python 2 (like python 2-3)
for me i’m ok but imagine billion dollars banks….
microsoft must control the programming language -> office script
but well it’s fat far far behind VBA honestly. with vba alone you can do many things but office script you may need to use power automate aswell which makes everything a big mess
Wouldn't any organization that uses Python for data analysis (e.g., Jupyter Notebooks) face the same risk?
vba on large dataset is too slow
That is always a consideration with Excel - make sure it will scale to the size of your data.
One great thing about Python in Excel is that it provides a smooth path to scale.
For example, moving to Jupyter Notebooks if needed.
thanks very much!
You are welcome! Glad you enjoyed the video.
VBA is also used for interactive controls, for instance form buttons to add an entry to a worksheet.
Unfortunately, Microsoft does not support VBA for iOS devices, which means a lot of Office automation is not available on that platform.
iPhones and iPads have grown in the business world. So people are starting to abandon Microsoft as their goto one-stop-shop.
Interesting perspective! Personally, I'm not seeing this as all of my clients are Microsoft shops.
In particular, my SMB clients are heavy Microsoft Excel users on Windows.
The python works on excel sheet, how to work
I'm not tracking your question. Can you elaborate?
Any halfway decent language replaces VBA.
Any suggestions for automating within Excel that IT will commonly allow? Office Script?
wishing for local python. MS is getting access to all that yummy scripts that users develop for them.
Arguably, they've been getting that for years now with Azure. 🤣
The answer is definitely C# 😁
Ha! Written a fair amount of C# code in my day. It's my 2nd favorite language for software engineering behind C++.
Teach me everything you know.
I've got many tutorials on my UA-cam channel. Check them out!
Walker Laura Jones Margaret Anderson Patricia
Thank you for suggesting the video! Much appreciated.
Python in Excel is more like Excel in Python. Which, of course, really doesn't work. Good advanced geeky stuff, but useless in 99% of most Excel environments. As David does acknowledge. If you're serious about Excel, don't waste time on Python. For advanced Excel, that really works, Power Query is the way to go! Otherwise, you are coding Python, not Excel...
I'd like to offer this up for consideration. For years, Dr. Wayne Winston has authored books on performing advanced analytics in Excel (e.g., using the Solver). Python in Excel is a much better way to do these things.
Jackson Angela Brown Kimberly Brown Thomas
Be hornest. Python performance is bad.
Compared to what? Performance is always relative
Compared to C++
Ah, C++ is my favorite software engineering language! To be honest, as much as I love C++, the speed of languages like R and Python really doesn't matter for analytics.