Transcription
Microsoft released a ton of new features for Excel in 2024. So, if you recently upgraded to Microsoft 365 or you feel a bit behind with all these updates, well, here are 25 of my favorite new features that will save you time and make Excel easier to use.
Oh, and I included a few that you might not have seen yet, so let's get to it.
The new high contrast only feature in the Color Picker will tell us which font color we should use based on the cell's fill color. This is telling us that a darker font color will be the most easy to read on this blue background.
The new checkbox feature can be used for everything from a simple checklist, where we can check off items like this, to more advanced solutions where we can show details from other sources.
The new Focus cell feature helps us focus on the selected cell. You can change the color here, and you can also quickly turn it off. When you use CRLF or the Find window, Focus cell will be turned on automatically.
The new theme for Office finally rolled out to general availability last year, and I call this one the purple monster because if you previously used this blue color often, the new theme changes that to purple. But overall, I do like the new font and color palette.
The GroupBy and PivotBy functions allow us to create summary reports with one single formula, and these can be used as an alternative to Pivot tables.
The new Trim Range function allows us to use whole column references in our formulas, and it will trim off any blank unused cells. This improves calculation speed, and when we add values to the end of the range, they will automatically be included in the results.
So, some of these features are still in beta at the time of this recording, and I'll note that with this icon throughout the video.
The Compatibility Versions feature allows Microsoft to publish updates to existing functions. For example, the Lin function used to return A2 for emojis, which was incorrect. But if we go to the Formulas tab, Calculation Options, Compatibility Version, and change to the latest version, this will now use the latest version of the function, which returns the correct result.
The new Translate and Detect Language functions allow us to translate any cell's value into any language. We can also use these new functions to create interactive multilingual reports.
Three new Rex functions allow us to find and extract complex patterns from text strings. Here, I'm using Rex Extract to extract a date from the cell that contains both text and dates, and here I'm using a similar formula to extract all of the phone numbers from each of these cells.
X Lookup and X Match were also updated with Rex functionality. Here, we can set the match mode argument to three for a Rex match, and this is going to use these tokens and the phone numbers here for this pattern sequence to look up the phone number in this list over here and return the company name.
If you want to learn more about the new functions and formulas in Excel, we have an online training course called Modern Formulas. This course covers spill ranges, dynamic array formulas, error handling, and a lot more. I'll put a link in the description where you can learn more and join us.
The web version of Excel received over 10 new features with the modernized grid update. Probably the most notable is the new way to insert rows and columns.
The web version also received the ability to export CSV files. This means you can use the free version of Excel to create CSV files.
A date picker has been added to the web version of Excel. By simply double-clicking any cell that contains a date, you'll see this nice month view date picker where you can quickly select a date and input that into the cell. I'm really hoping this feature comes to the desktop version of Excel.
The web version also received several copy and paste improvements, including the ability to drag and drop rows and columns, additional paste options, and autofill options, which are helpful when filling columns with numbers or dates.
The Chart Data task pane in the web version received several enhancements that make it easier to select your data and also see how the data is displayed when using sheet views.
You can now right-click anywhere in the table and choose "Copy Link to Sheet View," and this will create a link directly to this filtered view. This is great if you want to share this workbook with your co-workers and you want them to open the workbook in this exact view.
Microsoft Forms can now be created directly from the desktop version of Excel. Here, I'm using Co-Pilot to create a quick survey for my team, and when my team members fill out the form, the results will be updated in real time in the spreadsheet.
The Mac version of Excel received a new KeyTips update, which allows us to create keyboard shortcuts for buttons in the ribbon using an ALT or Option key sequence.
The Mac version also received an update for Power Query, allowing us to refresh data from a folder, which is great for combining Excel or CSV files.
Co-Pilot for Excel was made generally available and has a ton of features to help us analyze data, write formulas, apply conditional formatting, and learn more about our spreadsheets.
Python in Excel was made generally available, allowing us to analyze data and create charts in ways that weren't previously possible in Excel. A new Python editor was also released, which makes it easier to write and debug code and also see the order that it's calculating.
Microsoft also announced Co-Pilot in Excel with Python, and this uses the advanced analysis feature of Co-Pilot to help you write Python code, fix errors, and analyze your data.
Excel for iPad got several updates, including the ability to apply sheet protection and the ability to create and edit Pivot tables. Many people use an iPad as their main computer now, so it's great to see some updates coming to the iPad version of Excel.
So, I'm curious to know which feature is your favorite or which one you'll be checking out first. Leave a comment and let us know.
I'll put links in the description to more detailed tutorials on some of these new features. If you want to save even more time with Excel, then check out this video next.
Thanks again for watching. Have a great day, and I'll see you in the next video.