2024 Excel Latest Upgrades in Office 365: New Features Explained

[Music] [Applause] [Music] okay let's check those new features in Excel number one if you head over to the insert tab on the ribbon and you choose pictures then you can recognize immediately that there is a new feature inserting pictures images into a cell directly from different sources but it's not the end because this new feature works 100% dynamically with the functions built into Excel let me show you I have a simple Excel table with my favorite Marvel heroes and I'm going to add one more column and I'm going to name it as image I select the cell I go to the insert tab hit pictures place in cell my image is stored on my device then I simply just browse to the folder where I have my images the first one is the item man in the first row I hit insert here we go the image got inserted and I just simply repeat the process until I inserted all the images I want then I can select the range go to the Home tab and I can align the images with the default functions in Excel the images will react dynamically to the cell changes if you change any dimension of the cell but let me show you the coolest thing about this feature I am going to create a dynamic drop down so I go to the data tab hit data validation select list and start typing equal in direct since I want to create a dynamic list double quotes my table name and the column I want to use in my list that's going to be the hero name column from my table close double close brackets hit enter I created a dynamic drop down list with the table so once I add the new value to my table that's going to be updated on my drop down list as well but that's not the trick let me show you I'm going to use a simple xook up function to pull those images into another cell so the look look up value is going to be from the drop- down value lookup array is my hero name column return array so the result I want to get back is the image I close the brackets I hit enter it shows an error right now because there is no value in my drop down but once I select a value from my drop down it will show me the image immediately and I can modify the dimensions of the image by changing the size of the cell so the best thing about this image function that it will d dynamically work with our formulas and arrays we create on a sheet there is another formula built into Excel which is called the image and that can return back images in a Cell based on a URL address let me show you quickly I simply head over to Google to look up for some online images I found this one I click on the image it will drive me to an article I right click on the image I open in a new tab and I simply just copy the URL of the image I go back to my Excel file let's open up a new sheet I select the cell equal sign entering image Open brackets double quotes paste the URL double quotes again so the URL must be between double quotes I close the brackets and I hit enter and voila the image formula pulls the image directly to a cell by using a URL address and why is it awesome because you can use your one drive to pull images as well if you want to know how to work with one drve URLs with the image function please check out my next video number two in the new Excel if you go to the view tab on the ribbon you will see a new button called navigation and it will open up a true navigation pane which will help you to navigate between the objects you created in your Excel the principle is exactly the same what you can see in powerbi but it's going to be available in the Excel as well if you look at this navigation pane then you have a searching engine every object is being grouped by the sheet names so if you created anything on a sheet it will find it and it will name the reference on the sheet as well and if you click on the reference then it will drive you directly to the cell reference it's 100% dynamic as you see if I add the new sheet to my workbook then it will appear on my navigation pane automatically and once I enter any value in any cell that's going to be updated on the navigation pain immediately number three I simply go to the formulas Tab and you can see that the python is available in the Excel for data analysis I can either start typing py which will open up the python code editor as you see you have to use the control enter to close the code itself because if you hit simply the enter then it will open up just a new line in the code editor or I can just simply select the cell and hit the python icon and it will open up the code editor it's very Dynamic you have to just select your table and as you see it recognize automatically that this is a table and has headers Etc and if I hit control enter then it will create my Digital Data frame and the data frame is not more than your table created in a virtual way there is no any command added to the Cod line so nothing happens here but if I select the cell where I have my python code then I go to the formula bar and I select Excel value that it will extract the data I have in that data frame so nothing happened to our table because there is no code command added to our python line but let's use a common standard one which can create you a quick analysis from your table first I select the source cell then I go back to the formula bar and I return this back to python object so you can quickly switch and change between the Excel and the python object then I go back to the code editor and I hit dot describe Open brackets close brackets contrl enter and here we go our data frame has been recalculated now I can return back as an Excel value and voila the python code created me a standard quick analysis about my table number four I simply select this range then I go to the insert tab I go to the right hand side and we finally got the checkbox as a feature built into a cell and it works dynamically with all the formulas you can find in Excel and if you look at the checkbox itself when I select the cell it says true when it's checked when it's blank it says false so the way it works is very simple it returns back only two values either true or false how can we use that we have many different ways let me show you a quick one I go to the cell I start entering count ifs I select my range and my criteria is the true I close the brackets I hit enter and it's counting how many box have been checked in my column number five the next new feature is not 100% complete by Microsoft I believe because it doesn't really work well but let's give it a try if you go to the data tab on the ribbon you see that there's a new option to import data from images I select the picture from file I browse for the image I have one image with a hand made drawing with a simple table I'm going to insert and the cloud service will work on my data and here we go as you see it didn't really work well because the handmade drawing is not that characterized like a digital printed data on a paper and if I hit insert data then it will paste me some dummy values but let's see what happens when I try to insert something digitally printed so I select picture from file and as you see I just simply took a photo about the digital printed paper with my cell phone and I'm going to insert this one this time the cloud service is working but it seems like Microsoft still needs to work on this new feature number six Microsoft finally released the emulated pivot table by a formula it's called the pivot by function the first argument we need to enter is the row field so what you would put under the row section when you use a normal pivot table so I simply select this time let's say the hero name column from my table the next argument is the column Fields like in a normal pivot now I'm going to select the gender column the next argument is the values so basically the column that includes the values I want to work with the next argument is the actual calculation function and if you look at this awesome list you have multiple ways to create calculation within the pivot by function like in a normal pivot table I'm going to use the sum and I'm going to set one more argument which is an option to show the headers I select number three yes I want to show and maybe one more you can choose whether you want to show only the grand totals Grand and sub totals together let's say I'm choose number one this time I close the brackets and I hit enter and voila it created a dynamic array acting like a pivot table and the last new feature I want to introduce to you is the group buy function which is similar to the pivot by but it's different I would say this is the Sami ifs new version but it's a dynamic array let's see how does it work the first argument is the row Fields I'm going to use the gender column this time values let's say strength score the functions are exactly the same what you can use in the pivot by I'm going to use the sum just for the Simplicity you can set the headers yes show the headers and also play with the grand totals I'm going to choose number one I close the brackets I hit enter it created me a dynamic make array by grouping values and using an aggregation and in this case it was a sum function I hope you enjoyed this video and you consider to subscribe to my channel where I can show you everything I know about Excel data porbi and the new techniques

Channel: Office Universe

Views: 25,188

Keywords: excel new features, microsoft excel tutorial, excel 365, PowerPoint, Excel beginners, Advanced Excel, Excel Tricks, Excel Data Visualization, Excel Charts, Excel Dashboards, Excel Basics, Excel Array Formulas, Excel Formulas, Excel Functions, Excel tutorials, Excel for analysts, Excel VBA, Microsoft Excel tutorials, Office 365, Excel for Office 365, Microsoft 365, Power Query, Pivot Table, Power Pivot, 2024, new, new features in excel, excel for web

Id: X0GE1sXFWKg

Length: 10min 14sec (614 seconds)

Published: Wed Dec 13 2023