Python in Excel live demo!

Key Takeaways

so hello everyone thank you so much for joining the booth theater session today on Python and Excel um a new feature that we announced last August 2023 so some of you may have already had the chance to try it out um but if not I'll I'll explain how you can get access to the feature today um so just to give a little bit more of a background my name is nant I'm currently a product manager at Microsoft um who's worked extensively on this feature over the last few years um join with Petra over here um she will be helping field questions um and hand out some fun prizes towards the end um as well as Ryan and Allison who are joining us from Anaconda um as this was a partnership between the two um of us to build this feature today so to get started we'll just kind of walk a little bit through about why Python and Excel um as many of you know there are lots of languages or um software that you can use to analyze data um and so we really wanted to give a little bit of background and context on why we specifically chose python um and so with that we did a lot of market research to kind of understand what the Fe what the languages users are using today to do analysis um and specifically for data analysis um this is one of the examples of that we did not just go to Bing chat and say which language should we pick of course um but if you do for example ask Bing chat to give you a list of the top tools you should learn for data analysis you'll notice it gives you a lot of responses back Excel python jupyter notebook um Power even powerbi towards the very end um and so what we wanted to point out here was it wasn't really surprising for us to see Excel and python um listed as the top tools that folks should use for analysis from an analysis from an analyst perspective um Excel is largely used across the board um and similarly python to be able to use libraries like Matt ptib seor and hand does to do data analysis on top of data frames um within different IDs that you choose um and so with that we we also saw an interesting narrative um around some of our market research where customers were saying they were in the process of of moving away from Excel into python um they were saying that they didn't really use Excel because they can't use Python with it um and even in the education space we saw where people were saying they knew Excel but they then wanted to learn python um and in a similar sort of similar vein the internet was actually creating guides and sharing material on how people can move away from Excel and into python um and so we saw this as an opportunity because we believe it's not really an or that you don't necessarily have to choose between the two tools but there is an opportunity to join them together um for an and and so we were able to bring the Two Worlds of data analytics together python loves Excel as you'll see some of us have those t-shirts on we have extras to give away towards the end um and so with that our mission is really to empower all Excel users to do more with their data analytics um between the partnership that I explained before Microsoft um along with Anaconda who helps us make sure we have the environment set up um in a reliable way and you can access all the libraries that you know and love um within the distribution that they provide and so we'll be doing this in two ways the first is with the actual feature Python and Excel that I've talked about um a bit in the beginning but also some of you have likely heard of the co-pilot World um and co-pilot is an interesting piece here because as with the AI boom um it really gives us an opportunity to tap into customers that may not be familiar with python or don't already know it um so you can use the chat pain to help you write python code explain that code and insert it into the grid um and conduct your analysis that way so there's also a learning opportunity and teaching opportunity here um for people who don't know python but also for people who do know python like myself it's great because I don't have to start off writing any code it can start it for me and then I can make my edits as I wish um and so with that I'll go into a live demo into Excel to show you how the feature works and then I'll come back into PowerPoint to show you a video on the co-pilot piece so you can also see how that works today okay so I'm going to excuse my like looking backwards um but I'm going to kind of zoom out here just to give you a sense of what we're looking at so what's great about this is Excel is a canvas right so I can design my spreadsheet any which way I want um and put the code basically anywhere I want that is in row major order is the way we execute um and so in this worksheet you'll see we have two tabs um the first is the data tab where the data lives that we'll be looking at and so this just looks at passengers um that traveled or over um a number of dates in the 1900s um a very simple data set with two columns um and then in the second spreadsheet we have a layout that we built before um writing writing the code to kind of layout step byep of how we want to do our analysis so we'll be doing an exponential smoothing forecasting um and I will zoom in so that everyone can see so the first thing we'll want to do here is load in our python libraries um if you go into the formulas tab you'll notice that there's a new chunk um within that chunk there's an insert python button a button to reset your runtime a button for Diagnostics and a button for initialization so there's a few ways you can invoke python mode or a python cell the first of which is to click this button the second is to type equals py so just like you would type equals sum equals if there is a python function um and so now once I do that you'll notice you get a green badge um and that badge will let you know that you are in Python mode here you cannot execute with enter like you do a regular formula because with python code you often have more than one line um and so the the cell will increase in height as you continue to go down to add more lines so let's start get started by importing some libraries so there are libraries that we initialize by default um I will not list all of them or share them all here but I'm happy to share um we'll be at the booth all day so can share that more detail um but to start we will import some libraries so let's import Panda Seaborn mot lib stats model um and for our exponential smoothing and at the last piece we'll include a string to let us know that the python libraries went in successfully so if I hit control enter you'll notice you'll get a pound busy and that's the code being run um in the Microsoft cloud and once it's returned successfully I know that because I received my string at the very end next we'll create our data frame that we'll actually do the analysis on so another way you can do a python um cell which is great for Excel lovers who love keyboard shortcuts is through a keyboard shortcut so if you hit Control Alt shift p um that will create a python cell as well now in order to import the data into Excel you can actually reference co uh cells that are in the Excel spreadsheet itself so you'll notice you'll see there is an Excel function um and all the code is passed through that function so I can again use my keyboard shortcuts to select all my data table two is the name of my table in Excel and I have headers so I'll keep that as true contrl enter and my data frame is created what's nice about this is this is also a data type within Excel so some of you may have previously used data types um so I can do things like if go over this um icon here I can show the card and that will give me a preview of the first five and the last five rows within my data set similarly if I want to know certain um things about the data frame there are Field properties here that you can also enter into the grid and that will give you more of an idea of what the data frame is and what it contains and what it looks like um next we'll go into plotting our actual value so it's nice to be able to see the first five and the last five but if I want to get a sense of kind of the trend here I can do that by again equals py so you just always want to remember that because if not you will it'll be a mess um and then I will run some code here that will create a plot for me to see um the actual values over time so once I commit that oh okay well I ran into an error for a few reasons the first is I did not label this data frame so you can actually name the data frame um so once I hit control enter this will run again and my code below it updated and it ran successfully so I got back an image data type this time so different than the data frame similarly um there are actions and Fields that I can get back from this so even things like the size of the image at the very bottom I can hover over here and get a preview of the image that will load here along with the size at the bottom um and if I want to see the liage image more enlarged I can rightclick and you'll notice there's a new button called display plot over cells if I click that a plot appears that I can put anywhere I want I can cut this and paste it into PowerPoint um you can leave it in Excel you can resize it and do things like that so for the sake of this I will just bring this up here um over to the side and so now we'll go into building um the actual forecasting model so I will run some code here that will do that while I do this you'll notice I'm taking in different assumptions that are above the code um this is we need that in order to reference the cells in the code itself and it actually references the exact cell that it lives in so C8 is where I have my periods C9 is the trend C10 is um the seasonality and then C11 is if I want box Cox supplyer not um and then the last line just fits that into um the model so once I hit control enter I will get back that result and then I can run the forecast on top of that so short line but it's great to be able to break everything up sell by sell so that I know that now I got my forecast back so again I can preview it here you'll see it is a uh series um and then similarly I have the same Field properties that I did previously um and now let's say I want to plot my forecast as well I'm able to write code to just do reference the cell previously plot control enter and this will return an image data type so we have different marshalling modes within Python and Excel that can be controlled a few ways but the first is right by the formula bar you'll notice this is a python object but if I click here I have two options a python object in an Excel value if I switch this to an Excel value I won't get a data type back but instead I'll get the actual value that that data type is directly into the cell um and similarly I'll I'll do the same here to show you how that would work with a series so I'd run the same code I'll get a series back and then I can switch it to an Excel value and it will spill onto my data set so I can see all of my values here and probably my favorite part I think of python and Excel is the ability to do this so if I want to look at different scenarios here not just the first that I built um I would have to copy paste the code change my inputs um and then rerun everything but with Excel functionality since this is already built in for Native formulas today I can highlight the cells that I'm interested in replicating the analysis for and just simply drag it to the right and you'll notice it is automatically updated and it references the scenario inputs that I would like right above right above it so you'll notice here if I double click into this you'll see it's referencing these cells right above um and similarly if I click right in here it also references scenario 3 above so this is great because it allows you to okay it's great because it allows you to test different scenarios compare them side by side um so I can look at how these different assumptions make a difference in what my forecast looks like over time so with that I will go back into my PowerPoint okay great so as you saw um when I was running the code you saw pound busy um and I mentioned that this code gets sent up to the Microsoft cloud um some of you may be wondering why Cloud why did we decide to have the code run there instead of locally um and there are four important reasons that I just wanted to walk through with everyone today the first is reproducibility um we with Excel the expectation is that you can open a workbook from over 30 years ago um and it will still work um and so we wanted that same thing to apply here right I can open a python spreadsheet that hopefully 30 years from now um I still have access to and I will still be able to run that without any issues the second is collaboration um the Excel team had a really big investment in co-authoring and having the ability for multiple users to be in the same workbook at the same time and do things in Excel reliably and so we wanted to make sure we saw that piece here um it's not always that you are the only one writing code or doing analyses and so with running in the cloud we're able to have you share your workbook with others they can not only see your results but also add to your analysis in the CES as well wrong side thirdly is es ofuse um I remember when I was first learning how to install python I was like wow that's a lot of steps um there are no steps here you just open Excel types equal py and you can write code right there you do not have to install anything and even if you do want to install anything but you share to someone who has no idea how to do that they don't have to worry about that um it's all set up for them and lastly is security um we know security is top of mind for everyone and we wanted to make sure we protect your organization's data um and so with that we run it in a secure sandbox um none of the data is contained in that a new container is opened every session um and it is only online so long as the workbook is open or until you hit a timeout um and then that container is destroyed and a new one is opened or started I should say um so with that just to kind of talk through how you can try it out if you haven't already um there's a lot of information here so the first most important thing is the first bullet um joining the Microsoft 365 insiders program we're currently still in preview so we're only available for the beta Channel um the build and version number is all there um but we are available to all users so Enterprise education family and personal users who run beta channel on Windows um and that's another important piece we're only available on the 132 platform um with plans to extend the platforms Beyond win32 but starting there for now um we also have a wait list I'll pull up a QR code towards the very end for you to join and that's where we'll let people know about future availability and things like that um and then lastly you can share your feedback so we have a GitHub page um and you can also do the same in the Excel help scen um in the Excel help feedback button um and then lastly we also have a python editor um if we have time towards the end not too many questions I will share this and show you show you how the python editor works it's the likes of a Jupiter notebook um and it's a pane on the right hand side where you can write write an edit code just like you would in a Jupiter notebook file sell by sell by sell um we have a number of resources because of course this is all new um I won't walk through everything here but there's a number of places you can go to get help if you need um as well as Anaconda they've put together a number of resources a number of places you can learn on courses um as well well as Community engagement towards the very bottom as well I let folks take a picture of that um and then again if you want to stay in touch we'll be here patreon myself Ryan Allison at this booth in Anaconda feel free to come up to us ask any questions give any feedback um and things like that we'll be happy to help and so with that I'll take any questions thank you and yes is it available on the online so not yet right now we're only on desktop um but we we do have plans to extend to other platforms um but the weight list would be the best place for you to know like once we are available on online yeah yeah so there are data limits on how much you can um run within python in Excel but I've heard people do and what we usually share is to try it on smaller pieces of data at first just to know exactly what you want to run um and then with a larger data set of course it would just take more time um but there there are data limits I can share with you exactly what those are I don't remember if the top of my head um coll sorry collab oh no so collab doesn't affect that at all yeah um it will just be like what we can actually send up to the cloud and return back back um but if there are specific like data sizes that you often work with I'd love to chat to kind of get an understanding of that anyone else no okay well if you have any questions we'll be here all day so feel free to stop by um thank you again so much for your time