Python In Excel: Microsoft Changed EVERYTHING
Вставка
- Опубліковано 15 лип 2024
- 👨🏼💻 ChatGPT Tutorial 👉 • ChatGPT for Data Analy...
🆇 X/Twitter 👉 / lukebarousse
🔗 LinkedIn 👉 / luke-b
Courses for Data Nerds
==================================
🤓 Google Data Analytics Certificate 👉🏼 lukeb.co/GoogleCert
🐍 Python for Everybody 👉🏼 lukeb.co/PythonForEverybody
💿 SQL for Data Science 👉🏼 lukeb.co/SQLdataScience
🧾 Excel Skills for Business 👉🏼 lukeb.co/ExcelBusinessAnalyst
📈 PowerBI for Data Viz 👉🏼 lukeb.co/PowerBI
📊 Tableau for Data Viz 👉🏼 lukeb.co/Tableau_UCDavis
🏴☠️ Data Science: Foundations using R 👉🏼 lukeb.co/RforDataScienceJH
➕ Coursera Plus Subscription (7-day free trial) 👉🏼 lukeb.co/CourseraPlus
👨🏼🏫 All courses 👉🏼 kit.co/lukebarousse/data-anal...
Books for Data Nerds
==================================
📚 Books I’ve read 👉🏼 kit.co/lukebarousse/book-reco...
📗 Data Analyst Must Read 👉🏼 geni.us/StorytellingWithData
Tech for Data Nerds
==================================
⚙️ Tech I use 👉🏼 kit.co/lukebarousse/computer-...
🪟Windows Virtual Machine for Mac (Parallels) 👉🏼 lukeb.co/ParallelsFreeTrial
Social Media / Contact Me
======================
👾 r/DataNerd 👉🏼 / datanerd
🌄 Instagram: / lukebarousse
⏰ TikTok: / lukebarousse
📘 Facebook: / datavizbyluke
🙋🏼♂️Newsletter: www.lukebarousse.com/
💬 Prompt in ChatGPT for Python in Excel
=======================
🤓 You are a distinguished data nerd that is an expert in both Python and Excel.
🐍 Excel recently released a feature allowing the use of Python within it's program.
💿 I have provided you an Excel spreadsheet familiarize yourself with the contents.
💬 When I provide you a prompt there is no need to ask if I'm ready. Just Do It.
🧑💻 I will be asking you questions and if it refers to python code I want you to provide me the results in the following format:
1. The expected results or visualizations
2. The full code snippet needed to insert into Excel
2a. When providing code snippets the last line of code in Python MUST be the output of the cell (whether a variable, or a chart)
2b. This last line does not require the use of the print() statement (a special feature of Python in Excel)
2c. The data has been loaded in the excel spreadsheet under the name 'jobs_df'
2d. The following is already imported in the environment (so don't re-import):
import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import statsmodels as sm
import seaborn as sns
import excel
As an Amazon, Coursera, and Parallels Affiliate Programs member, I earn a commission from qualifying purchases on the links above. It costs you nothing but helps me with content creation.
#datanerd #dataanalyst #datascience - Наука та технологія
Aaaaaand you were right! ua-cam.com/video/vGI6VLr8L5w/v-deo.html
🤣 🤣 🤣 SO CRAZY!! Thanks for noticing this!
Husband and i just found your videos!!!! Im getting ready for a job interview as Data Analyst so using all your tips!!! thanks for all the knowledge shared!!! :D
I'm an Excel "expert" that has struggled to learn python. This gives people like me the perfect comfort zone environment to learn python as opposed to something like vscode
100% agree 🙌🏼 I think it will be a great environment for this. We'll eventually have some Python/Excel experts because of this tool!
Jupyter Notebooks are a way more approachable way of learning python imo. Give it a shot if you want
I understand where you're coming from. However, you may be surprised how easy it is to get started with VSCode. There is a very popular Python extension, developed by Microsoft, that makes writing basic Python scripts a breeze. Going further, it's not too hard to hook an Excel spreadsheet up to Python with the Shell object in VBA.
I really recommend VsCode, super easy to set up and with an extension you can have it show you the syntax of it so you never have to guess when learning how to use them :)
Vscode is just a simple editor. This is using what looks like python with the pandas and matplotlib modules. It's not that steep of a learning curve. Dump excel all together. Create proper SQL databases and interact with that. If you have to visualize it in software programs like tableau are ok I guess. With those skills you can interact with data lakes, etc, and can stop making Excel coloring books.
First time making a comment to Luke:
This implementation is amazing! The allowance of python in excel built that bridge, so each expert from one or the other can have some sort of connection, Also thank you Luke for making briliant videos on all sorts of newest tech skills that was introduce. I am a master student in the final year in Data Analytic. Hope to see so much more from you!
I already use pyexcel, a python module to edit and use date from a excel sheet, and it's amazing. Knowing that python is implement by default will clearly replace visual basic after
Man the quality of the video is on another level 🔥🔥 i really appreciate the amazing efforts you're giving to this. Thank you luke
Glad you enjoyed it!
with this feature i could really see some guys working as controllers getting into programming
Absolutely! I'm going to be working as a Controller and would Like to Go into the field of Data Analytics and Programming so I think that's perfect
You're so good at conveying the content in an informative and entertaining way. Keep it up👍
I hope your channel gets more recognition. Keep up the good work!
Luke you absolutely crush your videos, extremely well done. Honestly you are a major reason we started a channel, only hope to one day be on your level of quality and value add. Having started I am not sure how many people know what level of effort you put in to make these and how long it took get this level of quality.
Heck yeah!! Thanks for the kind words my dude. Just checked out your channel 🙌🏼
Thank you, really was not expecting any response, so really appreciate it. Would love any insights(pun intended) you have. Always looking for constructive criticism.@@LukeBarousse
One of the best Python in Excel analysis that I've seen. Well done Luke. keep it going.
Heck yeah! Thanks for the motiviation!
Good video. Although I don't actually use Excel or Python for much, it's still interesting to see what can be done. I only use Excel to create some quick and dirty data sets for testing. It's convenient to generate insert statements when you need to populate a table with test data.
Tks Luke!. Crystal explanations!!.
Thanks Luke for sharing and bringing your entire personality into this one lol. You made it informational & enjoyable.
Glad you enjoyed it!
Wow, this video is awesome! I learned a lot of new things about Python and Excel. You have a great way of teaching and engaging with your audience. I can’t wait to see more of your videos. YOU ARE THE BEST.
Thank you!!!
Also Excel is the statistical software with the biggest user base by far for at least since 1990. I am sure Microsoft wants to hold on to that lead. VBA was good in the past to allow scripting and UI form creation. Since Python is the new scripting king, it makes sense to build in Python scripting into Excel. Plug in makers and 3rd party integration is also a factor. Bloomberg terminal has plug in for Excel, but any user code needs VBA. Python will be a much better choice.
Totally agree about VBA over Python
Amazing review, thank you Luke!
I'm curious about the data security implications of providing your organization's data to chatgpt via copy and paste... Most of my jobs have had heavy data security policies
Yeah if you're unsure then I wouldn't risk it
This. I’m not uploading any file with company data into ChatGPT nor sharing it with a python cloud server even if it’s Microsoft.
I love Chat GPT. I'm learning database management and I use Chat when I can't figure out what code I need or how to write it. I love that it explains everything in plain English better than the instructors for some of the courses I'm taking. I know Chat is going to be one of the best tools in my data arsenal. Keep the great videos coming, Luke!
Heck yeah! Awesome to hear that it's helping with your studies; I wish I would have had this tool when I was learning how to code 🙌🏼
Yeah I love the ability to get it to reword things or explain it from different angles. Really helps to solidify learning
Wow, such fun and informative too! The chart example A+
I just love to watch your videos luke I love python !!
I'm a marketing operations analyst and only use Excel. The nice thing about marketing analytics, is the data is always provided to you by BI groups or CRM/GA4 reports. No data science or mining is required, at least in my position.
I think Luke hits it on the head with the experience excel users, and that you can pretty much do everything in excel if you know all the formulas, so I don't think python in its beta state will do much except for maybe a few edge cases that might be cool.
And to expand on that a little excel has pretty much all those functions that you can do in python already, including the graphs. You can do heatmaps, histograms, and even do linear regression, etc in excel and you don't need boiler plate code to do it or even have to use chatgpt as mentioned for noncoders/lazy coders. I personally find seaborn graphs to be terrible looking (almost all of them, a few look good). The dashboard shown in the video looks clean though, so that looks intriguing, but I would be remise to say that you can do a version of that probably with a pivot table (maybe?) and an advance/expert excel users I work with usually make pretty clean looking excel files that do a bunch of fancy things. That dashboard did look really clean though, but I don't know how much of that you can replicate just through excel because I know filters work basically the same way but just look a little uglier.
So, I'm not convinced right out of the gate that anyone would be able to flex their python skills to all those excel users at work because the graphs are already done and most of the "analysis" is also already been done in excel so anything brought to the table via python is usually not something that anyone would notice (simply because excel can do most of what python can do anyway). I don't know how much machine learning you could reasonable do in excel that, 1, your job would need if they are using excel to begin with, but 2 size limits as mention would limit machine learning to a small degree, but mostly machine learning is at least advance machine learning is a little too complicated to use chatgpt.
The main reason I use python with small office data is primary to automate reports and connect it to other things like outlook for automated emails etc.
Honestly, I think its just better to do python in an installed environment/ide like vscode or jupyter notebook so you can use other modules.
I appreciate this honest review! I think you make some solid points for those "hard-core" excel users and I can't argue with 'em! 🙌🏼
Yeah expericed excel users will never have a reason to use this and experienced python users will never feel the need to use excel because of the limitations it has compared to using python standalone. Still very cool and obviously it will be used by someone who is for instance a python programmer but is momentarily forced to use excel in his job.
Great video! I don't know which was better, the technical insight or the humor and drama.
🔥🔥🔥
Thanks Luke Best I've seen on intro Python in Excel PS: Co-pilot sidebar showed up on screen this morning dec 11
This video is top notch! Coming from somone who is currently in the bottom right of the quadrant :) Would you consider doing a video on xlcalculator 0.5.0 ? I see that its a python library that can read excel files, would love to hear your take on it!
Yo Luke rules as always..Great learning even the panda in video is happy..😊
Luke, which resource would you recommend for dirty data set to practice data cleaning? Thank you
Very good video and informative. Keep it up!
Excellent editing Luke. Had to pause less than 2 minutes into the video to let you know.
I appreciate you noticing!
This is so obvious, yet brilliant.
Every organisation needs to be on top of this
I need to take some time out to explore this. My retirement hobby is software, but I got into it as a financial consultant in the 1980s by knowing “everything” a spreadsheet of the time could do. Now I do Python - a no-brainier. Something that connects the spreadsheet world to the Python world seems to be another no-brainier. The database universe is funnest part - and it’s not clear that this place is catered for.
Correct me if I’m wrong, the TRIM function in Excel is better in removing the excess spaces (2+) between words compare to the methods available in Python (standard or pandas) or SQL, possible to do, but not as straight forward.
It’s only small issue I guess, especially with the ChatGPT now (I found how to do it in SQL awhile back from Stack Overflow while practicing Data Cleaning with SQL).
Python has an extensive amount of NLP libraries that I feel are far superior to Excel...
Now are those libraries in this environment 🤷🏼♂️
Thanks for the insight shared, good info to know. When I said better, it’s in term of simpler to use (saves importing extra libraires or few extra lines of code if only want to remove the inconsistent excess spaces), not other terms like performance over large data set (one of main augments python is better than Excel for data analysis).
pandas strip is like trim but 10x better.
I as an engineer we’re always stressed, using Excel sometimes. There had not been time for learning more sophisticated functions, because one week later we need to serve in totally different systems, functions and jobs. Even when using Powerpoint every two or three years, I have forgotten nearly all helpful functions and we are three version ahead. It is so frustrating, if not specializing in some tools, cause our automotive development business does not giving room for learning and training this.😢😢
Your video is good on many levels😂 I had to move it to a bigger theater because it's so intriguing
Ha! Glad you enjoyed it!
that was so funny, entertaining and educative! kudos to you and keep up the effort!
🙌
I want to learn that so bad! I just like your train of thought you have !!! Wow 😂 I have basic excell / power point/ PDF acrobat- from the 90's' in combination of Business analysis from the arcades! 😅 i need to label up "I know that I know nothing" Socrates. ! Thank you so much for make this accessible to people like me ! Gracias mijito!
I have been trying out the case studies and assignments from my MIT No Code AI ML course in ChatGPT's Advanced Data Analytics and it's been really fascinating to see how far it can go. Typically it does EDA and outlining the approach for models very well. It usually fails at the point where it is missing the libraries it tries to access or if the dataset is too large.
yeah large datasets will do that
Please can you share with us some project to practice data analysis
Love your videos I’m going to become a data analyst very soon
You got this!!
Love it! I’m listening.
Quality, story telling 👌🏿 🔥
Brilliant video. Thank you
Bro... your videos are next level 🔥
I have to use excel everyday and when I saw the addons I was really impressed.
Love this video
looking forward to use this with chat gpt
4:07 ... I've been listening Luke!
Yes you have!! One of the few 🙌🏼🙌🏼
Really, really informative!
what is the best way for a newbie to start practicing the combination of these skills?
An issue I've had with using Power Query which has pushed me to basically using purely Python was the maintainability of the queries and the file itself. As I scaled up what I was doing in a single file it became unmanageable whilst with just python scripts I have Git and Jupyter Labs to hold my hand with documenting and version control.
Having Python script living inside excel cell's sounds about as manageable so I foresee it becoming problematic if you have too much Python in a cell or too many cells with it built in.
I'm curious where you would draw the line and what mitigation methods you could take! Because sharing an excel file in terms of portability and required technical skill is just miles ahead of trying to get the layman to set themselves up in Python so I'd love for this Python in excel formulas to be a winner!
Yeah while Python in excel is awesome, it doesn't solve the problems that I have with excel. It just makes the good parts of excel even better. For instance I had 300-500 thousand lines of data, and the entire excel sheet started behaving slow. Now imagine I worked with 2 files simultaneously. Now I work purely in python, and after creating the dataframe is save it as a pickle.
Haha, actually I can tell you that the upper right quadrant exists. I have used Excel for over 7 years to create complex spreadsheets for different individuals, I have also used Python for about 4 years (though more for web and APIs) but I do use it for data analysis.
Still, I'm trying to figure out how I can be more productive with these tools combined. 😅
Your videos are just 🔥🔥🔥🔥🔥🔥
I have access to LinkedIn Learning. Is there a course you recommend instead of from Coursera? And if so, which is better and looks better in a resume?
Luke, you are awesome, thanks for sharing
Aw thank you!!
Hi Luke, I'm not being able to enable this feature. I have an enterprise account with the license to use it but couldn't activate it. I tried on my M2 MBP and in windows on parallels... couldn't make it. Could you make a video on activating this?
9:50 can you make a more detailed video on it how to do it... this is so amazing...
but can't i just do it with xlookup, average and pivot tables?
Let me see what I can do on this!
Not having an option to run the python code locally makes this feature borderline unusable IMO.
Having the cloud computing sandbox environment is a neat idea to make spreadsheets that use the python features more portable so you don't need to worry about configuration on various systems that might be used to view the spreadsheet. But I haven't heard anything about the cost of running this on their servers. It seems free for now while it's new and you're paying for a microsoft office account, but the only way it's going to stay that way is if they are harvasting some data from you that they can sell or they'll charge other cloud computing costs. The latter could be a problem if you opened a document that ran some python that was running some expensive stuff then it could be a pain.
Yeah, I'm also curious about the cost as well... I've heard that the CoPilot models will cost money
Although regarding selling your data; companies already store their data using Microsoft services already... so they could have done this already (or worse are already doing it 😱)
It's not about that. I'm sure they're probably also harvesting the data, but more so allowing python to run on your local machine is a security risk. If I share an excel file with you and I put arbitrary python in there that runs on your machine I can hide malicious code. (i.e embed a keylogger, open a reverse shell, etc...). This is already possible with VBA macros, but they're heavily locked down and require a user to enable execution. Plus, Windows Defender has a feature called AMSI that scans those scripts looking for malware. Even with all of this, malicious excel macros still slip by. This is too new for it to be allowed to run on your local machine. It probably will go that way eventually, but I doubt they have the security posture in place at this point. Additionally, Python isn't maintained by Microsoft, and doesn't come with Windows by default. They would need to change that, or they'd need to bundle the install with excel. This would also require companies to allow python to execute on company devices - many IT administrators try to block this by default (especially on Windows) because of the security concerns. It's not as simple as it sounds.
I don't speak English, I had to watch the video with a plugin that gives you audio of the subtitle text. I loved the video, although you don't explain anything in depth, you do say everything you need to know about python and excel and all this with an excellent sense of humor. I have subscribed to your channel.
I have 12 years in excel, 3 years chopping code with vba, 3 years in dax and power query, 9 months with the new Lambda functions and 3 months in python.
I started without knowing that it would be in Excel. I think this new functionality will give me the additional motivation I needed to learn to program in Python, I hope that my second language is typescript so I can take advantage of it in Excel.
I am new to a lot of this stuff and trying to get better. Where did you get the data? Where do I get data like that?
Lukeb.co/freedata
@Luke Barousse, thank you for your awesome discription. AI is falling and rising with the right Data. Where do you have the Data in your mentoined example from? Can I download it somewere to try your example?
Here's the kaggle data: lukeb.co/freedata
First glance at the thumbnail I thought I saw Hugh Laurie with an excel spreadsheet xD
Hello. Could you please share the template of your personal portfolio website. It's really awesome !. Please create a guide for how to build one ?. Using static templates or Wordpress etc ?. It would be really helpful. Please !
The reason for still using excel, the place where I work we do almost everything or in Excel or we can export to excel.
So people who don’t understand computers can still make these graphs and stuff. And the ones who do don’t have to ask chatgpt all the time for the answers.
please luke,help me to connect MS SQL to STREAMLIT
Thx again Mr. Beast for this clear insight. :D
😜
Earned my sub at 4:20
Is this possible in google sheets too? 😮
Love this video…any suggestions if you’re using private data/can’t exactly paste it into chatgpt? Maybe using a dummy dataset instead w same field titles?
Yeah you could maybe look in to anonymizing the dataset to make in non confidential, but this is still risky
Learn python lol. Codecademy has free courses. It's really not that complicated if you put in a few hours every day.
i am wondering how strategic this move is between MS and Python. Is it reacting to the ongoing honeymoon between R and Google or is there something more to expect? Microsoft wanted so bad to buy SAS 7/8 years ago so it is probably throwing its excess of love on the only partner left on the market? or not?
Highly Strategic! I mean look what they've done with OpenAi and now with the Python community. Probably no coincidence they hired Guido van Rossum. They are developing better tools nonetheless... so can't really complain if i'm being honest
Great and quite informative video. Can you share the demo Excel data sheet link please ?
Here is a sample of the dataset
www.kaggle.com/datasets/lukebarousse/data-analyst-job-postings-google-search
Hey data nerds!! I guess now I'll have to hit my Phyton skills 😢
How to use PY() and The LET Excel function : example "=PY(2+3)" works but "=LET(x,PY(2+3),x)" can't be interpreted. Thanks
I was on board until the Python running in the cloud and then they send the results back to you. This is a no way in hell.
Interesting, i didn't really have a problem with this, except for the latency issues.
Just curious, what is the concern for you?
offline mkde.ecurity.banned for use by law in companies obliged to protect their customers sensitive data. Loke banks.
@@LukeBarousse
@@LukeBarousse As you said the latency problem may be an issue but my main concerns are security and complexity. Like a lot of people we are running a hybrid cloud environment with some processing being done locally, some applications running on AWS and other applications running on GCS. Which platform we use depends on the application.
Now here comes Python in Excel that looks like a pretty trivial application that could easily run locally on a developer's workstation but instead we would have to add yet another cloud vendor (Azure) and all the costs associated with that. So now we have a security problem with more data and results traversing the Internet when it doesn't really need to and a trivial development tool that has turned into a complex distributed application. In my opinion its just too much trouble for what its worth.
Yeah for someone working with confidential customer financial data that would be a hard sell to the EVP to approve sending data to the cloud. Big privacy issues. Plus, why bypass the compute on my local machine? My CPU is fine for Excel but not a Python plugin? Seems like an unnecessary, unwanted bottleneck and smells like a subscription "feature."
@@lashlarue59 Well said. This feels very money-grabby. I'd bet they'll charge a nice subscription fee for it in the final release.
Amazing !
Can it run any python script?
Quick Question: I am looking at switching from a windows laptop to a MacBook and wondering if there are any features of Excel that you miss?
yeah quite a bit, I find it quite limiting and so I use a VM to run the microsoft version of excel
lukeb.co/parallels
chat GPT is the new stack overflow copy pasta.
Hey Luke, thanks for your video. But I can't enable it on my Excel. Did you get it as part of Microsoft Beta Insider program?
Yes I did
@@LukeBarousse thank you for your reply
Is this Python feature already available worldwide Luke? I can't seem to find it in my current updated version of Excel
You need to be signed up for The Windows 365 Insiders program specifically their beta program. They are slowly releasing it to these users
@@LukeBarousse oh nice, didn't know it had to be through there. Thanks Luke!
Would this new feature change how someone new to the data analyst career track would need to choose what master? I’ve only just started learning SQL via Datacamp.
I think my advice still applies from the end of the video. Use ChatGPT to generate Python code when you get to learning Excel. It will help you start getting familiar with programming while you are still learning it.
@@LukeBarousse Awesome! Thank you so much for your help 😊
Is the size limit issue not solved by the data model in PQ?
Good question, just tried to load a million rows (imported through a data model in PQ) and still got an error message.
I think the cloud environment is just too small to handle it
@@LukeBarousse thanks Luke!
Have you done a video on what makes a good Data Analyst Resume's?
Yeah ua-cam.com/video/MfUzKeEKtr8/v-deo.htmlsi=nN4_GU9qUGJRXe3q
@@LukeBarousse Thanks Chief!
Who knows how to install the Python libraries which are missing in the Python for Excel?
Great video. I'm an expert in VBA and been avoiding Python up till now... Goddammit, not another programming language!
Ha! Well with ChatGPT I'm telling you it's a breeze... so I wouldn't stress it!
One thing is there is still a limited number of rows in excel right? Only works for less than 1 million rows i guess
Yes, but Power Query can hold more than 1M rows and I'm expecting that once Python gets a bigger sandbox enviornment it'll be able to handle larger datasets like this
HTML IS a programming language!!!!
I knew there would be keyboard warriors here... but I didn't expect it to be YOU!!! 🤬🤬🤬
@@LukeBarousse Oh it’s me alright. HTML!!!
😂😂😂
where can i find data source like this for making projects?
I have an open source data set here on data analyst job postings in the US: www.kaggle.com/datasets/lukebarousse/data-analyst-job-postings-google-search
How have I not seen this yet?
I'd rather have the possibility for a local instance, for example in a docker container.
Can SO tell me which laptop is he using? Thank youuu
MacBook Pro 14 “ M1 Max with a VM running windows. This is the vm I use lukeb.co/parallels
Mind blowing
Maybe I'm missing something here. I can't seem to be able to add it to the ribbon bar. I've updated, searched, I've gone through the program and through online 365. Is it just available for certain users right now?
Microsoft 365 insider program it's avaialble; you have to be signed up for the Beta program
Adoes Phyton in Excel work on MacOS or is exclusive of Windows (like some functions of Power Query)?
Yeah only in Windows for RN... also it's still in Beta so it's slowly getting released
Thumbnail and intro shot fooled me… 👍🏻
@@maikvanrossum Yeah... i'm using a Virtual Machine on my mac to run Windows (and thus excel and Power BI). Mac's versions don't have what I need for my job
Here's what I use: lukeb.co/parallels
Can you code a game in excel with this?
after the first sentence in the video I thought: couldn't this be exploited like the whole macro story in google office products?
if yes we will see a whole new wave of security problems comming to us
That's why they run it in the cloud and not on your local system. If they ever open it up to allow for local system execution than yes... you are essentially allowing arbitrary code execution. But you can already do this with vba macros. They can literally just import windows DLLs, get function addresses, and call them. That's why opening a document with macros requires you to enable them as opposed to just running them by default. If they ever allow python access to system they would probably do something similar, and I wouldn't be surprised if there is some kind of local sandbox that has a popup if the python tries to run certain functionality (i.e networking, os stuff, etc..)
Does it work with an offline version of Microsoft office?
No have to have internet access
Do you have to have Python installed to use this feature?
Nope! It is running Python in the cloud, so no need to install anything... this of course comes at the cost of latency
Does it mean we can use python code in VBA editor...if so this is great
not from what I saw. meant for use in the spreadsheet
The ens result of this feels a lot like BI software like Tableau, PowerBI or Qlik Sense.