This is the MyExcel Online Podcast, episode number 34. Welcome to the MyExcel Online Podcast, the need-to-know Excel insights, knowledge, and tips brought to you by the experts that know them best. Are you ready to explore your full potential and get better at Excel? It's time to stand out from the crowd with your host, John Mikoludis. Welcome to another year of the best Excel tips of 2024. It is great to have you here again. Now, 2024 was an amazing year filled with innovations and delights from the new Focus cell to the Regex functions and also to the general availability of Python in Excel and Copilot in Excel. Now, we've gathered 25 Excel MVPs and experts from around the world to share their best Excel tip of 2024.
Also, we have some awesome Excel newcomers in this podcast, so make sure to follow them and make sure to give some love to them as well. Now, let's get into it. Let's not waste any time. Oz, over to you. Welcome, Excel Warriors. This is Oz Du Soleil of the YouTube channel Excel on Fire and from the LinkedIn Learning Library. Now, let's start by centering ourselves by taking a deep breath in, hold it, and exhale. Over 20-24.
Excel has grown. We have gotten new features and function, and it seems like every day, and it can feel overwhelming. We've got group by, translate, Python, semi-joins, and it's tempting to feel like you have to learn them and use them right away. But true warriors know that victory isn't one with constant all-out attack. It's also one with calm, deliberate actions. You don't need to master everything. You look at what's available.
Use what you need for what you're trying to do and then strike with precision. And all of those new things, they will be ready when you are ready. So repeat this. I am in control. I go at my own pace. I conquer with clarity. Now let's take one more deep breath in. Hold it. Exhale.
Now go forth into 2025 with calm, deliberate ferociousness. And then the third little icon is called Import from Grid. You'll select your series of input cells, the formulas, and the final formula, including the headings, and click Import. And it will build a brand new Lambda for you that does all the logic of all of those subformulas. You just click Save, and that becomes a brand new function stored in the Name Manager on the Formulas tab. And you can just use that function with all of the logic embedded inside the Lambda. It's an amazing feature. Hey, everyone. This is Josh, your CFO guy, with my favorite 2024 Excel tip.
And that is to learn the difference between structured and unstructured data. See, structured data is data that comes in a tabular-like format where each and every column of the table serves a specific purpose. Generally, every cell is filled out and each row is filled out consistently. This structured table format is the easiest format for you to then manipulate, whether you need to run a simple lookup or a sum ifs. But what's even better about using structured data is the fact that you can then load it into a pivot table or power query or even power pivot. When in doubt, get your data in whatever format it is into the structured format and then pull it out into your dashboards and beautiful reporting. Hi, my name is Anne Walsh, also known as the Excel Lady, also the author of an Amazon bestseller called HXL.
Learn to love the software that can transform your confidence and career. And you can find more details at hatexl.com. My best office tip for 2024 is if you have a table of data, go to your home ribbon, search for analyze data. Unfortunately, this is only available for 365 subscriptions. And what you can do then is actually type in natural language questions. For example, what product gave me the highest sales? What customer gave me the most profit? And Excel will instantly generate a pivot table or chart for you. It can be a great way just to ask questions on the hop.
or you know spontaneous questions of your data however um i have noticed that if the data set goes beyond 100 000 rows and the wheels tend to fall off the wagon a little bit but i hope you find this useful and mind yourself and take care bye hello my name is chandu let me share two of my absolute favorite excel features that i really loved in 2024 i'm picking two because i couldn't really decide between either of them the first one is the newly added let function to excel i think it has been around for about a couple of years now but this year i really used let every time i got a chance to work with it and the results were phenomenal i went so far as to even create a fully functional interactive excel dashboard with just one let function i got a video of that on my youtube channel if you want to learn more about it but you don't need to go that far you can use let to speed up your formulas simplify the formula writing experience itself take out all the repetitive logic and put that into one variable with let and use that subsequently to build up your complex business rules so use let i have been using let in both my personal spreadsheets as well as when i'm teaching as well as when i work with clients and every time the results have been impressive the second one is the check performance button in the review ribbon of excel this is a godsend
this year alone when i was working with a client every time they sent me a file they do an extract from an accounting system and then send the file to me the files are usually quite large i mean they're not very very large but they're always about one megabyte or two megabytes like that but i know that there is not that much data in there and for a while i was surprised but i think halfway through the year every time i got the file and i opened the file excel would automatically launch this check performance feature and then say that there are a bunch of blank cells that don't have any data but just formatting applied to that i think this is what happens when you extract data from a system it generates data in a pre-configured format and that was adding all the million rows million rows and all these columns format to that before even putting the data there and that's why the file size was getting bloated and it became very slow but using the check performance tab it's just a simple matter of clicking a button and all this access formatting will be removed automatically and my files became small my files became much more responsive and i got to save time so check performance the button is in the review ribbon so there you go those are my two of the absolute favorite features of excel this year what about you what do you like let us all know by leaving a comment or writing a reply to john thanks john for this opportunity to share my favorite tip with your viewers and if you do need to find more about me check out my channel on youtube just go to youtube and search for chandu or go to my website chandu.org i wish you all a very pleasant and fun-filled holiday season and i hope to meet you sometime somewhere else again
Hello, this is Jeff with Excel University, and here's my Excel tip. I would check out the new checkbox control. Yes, checkboxes have been in Excel for decades, but the new one is absolutely insane. I kind of visualize it as a cell format that you apply to a cell, and it looks like a checkbox. And when you interact with the checkbox by checking it, the stored cell value is actually true or false if it's unchecked. And that means you can use these true or false values in your formulas. Plus, you can actually apply this checkbox format to a formula cell, and it will automatically check itself if that formula returns true. And if the formula returns false, the checkbox gets unchecked. You can also apply conditional...
formatting to these boxes. This is a relatively new feature at this point. So depending on your version of Excel and depending on when you're listening to this, you may or may not have it. But if you do have it, it is definitely worth checking out. No pun intended. If you'd like to see a demo, I did a YouTube video about it. So feel free to check it out. The link is in the show notes. Also, if you'd like to learn Excel free with me, subscribe to my weekly Excel tips newsletter. You can subscribe at excel-university.com slash subscribe. The link to that's in the show notes as well. I hope you have a wonderful and successful year ahead. This is Paul Barhurst, AKA the FP&A guy. And my tip for 2024, I thought a lot about this and this tip is going to feel...
old too many, but unfortunately I'm sharing it because there's still so many that need to learn it. My Excel tip is Power Query. Power Query has been around for a decade, but if you learn it, you know, if you haven't learned it, you don't know. It can save you hours and hours of time. It's one of the best things that was created by Microsoft Excel. It revolutionizes the way you can work in Excel. So my tip is take the time to learn Power Query. There are lots of resources out there. You know, one of the great ones is Ken Pulse, as John Ancampora once said.
Ken Pulse, this is Power Query Jesus. Or if you listen to Oz, Oz has great tips. So many out there do. So please, invest in Power Query in 2024, get rid of your crap data, and transform your processes. Wishing all of you a happy holiday season. Thank you. Hello, my name is Boriano Petrova from Bulgaria. My tip for 2024 is a Power Query, and I will try to explain you how to calculate percentage versus grand total. So first, connect to some Excel data with column that present amount, for example.
and rename last step my row data. Next, create custom column with the following calculation. Equal amount divided by 1 and rename this step my percentage. The next is select the previous step that is called my row data and find grand total of amount, selecting transform tab statistics and sum options. Rename this step grand total.
The last one is in the formula bar, replace grand total with my row data and one with the grand total. That's all, folks. You can send us a smile. We should Merry Christmas and Happy New Year. Hi, everyone. David here from the PiggyBank Accountant YouTube channel. My favorite Excel tip of 2024 is obviously AI in Excel. So if you have Copilot in Excel, you can type the prompt, add all insights to grid from a preselected table, and it's going to make you many insightful charts about your data. If you don't have Copilot in Excel, you can go to copilot.microsoft.com and ask AI how to do a specific formula in Excel. Just put in what you want to accomplish, copy and paste a few lines of your data, and here you go. I have lots of Copilot tutorials and courses on my YouTube channel, PiggyBank Accountant. Cheers.
okay hello everyone victor momo from the youtube channel excel moments and i'm here to share my best excel tip for 2024 and that has to be the trim range function how many times do you open a worksheet or a workbook and you see a formula that references an entire column or maybe an entire row or you see a formula that looks like some open bracket a1 column a hundred thousand and when you look at the actual data in there it's just 20 rows of data so why then does the formula extend to hundred thousand rows that's because the user emphasizes that the data would grow and he wants his formula to cater for the newly added data you could argue that he could use an excel table but that's a different conversation So where does the trim range function come in? What the trim range function does is that if you give it a range like A1 to A100,000, it's able to figure out the actual used range within that large range. So it looks like the last used cell, that's the last non-blank cell, and it can help you trim off excess columns or trim off excess rows. In the default case, it trims off both. So meaning that even if you give it a formula like A1 to A100,000, it may end up using A1 to A20 because that's really where you have data. This definitely optimizes your worksheet and your workbook.
performance. It's long overdue, but we're glad that it has been introduced. Even though it's still in beta, I expect it to make its way into the current channel very soon. Lastly, I would say thank you to John for the opportunity to share this tip with you wonderful people. And please do have a wonderful Christmas. Thank you. Hello there. It's Mark Proctor here from Excel Off The Grid. And my best tip of the year is to build your own library of reusable components. This could be in lots of different forms and it doesn't have to be difficult.
It could be as simple as saving chart templates for consistent style and formatting. It could also be workbook and worksheet templates, which already include layouts, styles, and the number formats that you use regularly. Or it could be something a little trickier, such as Lambda custom functions for common calculations or Power Query custom functions for handling tricky transformations. It could even be Office scripts or VBA. The key thing is that you have a library of reusable components that you can use over and over again, and you don't have to keep reinventing the wheel every time you start a new workbook.
Yes, you will need to test those components in various scenarios and try to make them bulletproof, but the benefits will significantly outweigh the time investment. It helps to reduce errors because you will be reusing something which already works. And this enables you to focus on solving new challenges rather than constantly solving old challenges that you've forgotten how you solved it last time. So that's my tip of the year, build a library of reusable components. And also, if you haven't done so yet, go check out the UnPivot podcast. It's Wynne Hopkins, Sue Bayes, Giles Mayall, and myself discussing all things data, financial modeling.
Excel and Power BI. Hey everybody, this is Melissa Compton from melcompton.com and my best Excel tip for 2024 are the functions to call and to row. Now what this allows us to do is take data that is in multiple columns and create a single list. So if you have employee information and column A has finance, column B has HR, column C has IT, and so on, you can use the to call function.
Tell it where to look and it will pull all of those employee names and put it into a single column. Two row works the same way. Let's just say row one has all of your IT employees. Row two has all of your HR employees and so on. You can use the two row function, tell it where to look, and it will create a single row of all of your employee names. And then you can use the two call function to look at your rows of data and put it all into a single column. So to me, it's one of my favorites and one of the most powerful tools that we have in Excel.
You can find out more about how this works on my website, melcompton.com, or my YouTube channel at melcompton. John, thank you so much for having me on. And everyone, I hope you have a happy holiday, Merry Christmas, Happy New Year, and I cannot wait to learn and grow with you in 2025. Hi, my name is George Watt from stringfestanalytics.com, and my top Excel tip for 2024 is this. Visualize your data with Python in Excel. If you follow me for a while, you know I'm a big advocate of Excel users learning a bit of Python. And now with Python in Excel, it's becoming harder and harder to avoid this powerful tool.
But if you're new to Python, you might wonder, where's the best place to start? My suggestion is data visualization, and here's why. First, Python in Excel unlocks a whole new set of tools for building data visualizations that are tough to create natively in Excel. Think of things like small multiples, jitter plots, and so much more. Second, data visualization is the great equalizer for understanding data. When worked with more complex data structures in Python, it's not always easy to display that information in an Excel workbook, but a good visualization can bring that data to life. This is especially true when working with unstructured data like text and performing analyses such as sentiment analysis that would have been challenging to achieve natively in Excel.
Python and Excel support several visualization packages, each with its own strengths and weaknesses. I encourage you to explore them and find out what works best for you. And as Python and Excel evolves, we're likely to see even more options for creating stunning charts and graphs. So thanks, John, for organizing this annual Tradition Podcast. I'm excited to see the incredible charts and visualizations that everyone creates with Python in the coming year. Everyone, this is Puneet from ExcelChamps.com, and my Excel tip for 2024 is to create a custom tab on your ribbon and add all the options that you use frequently to that one tab. So this is very easy. You don't need to use any code or anything. All you need to do is go to the File tab and then open the Excel options, and from there you need to go to Customize Ribbon, and in the Customize Ribbon,
At the bottom right, you have one button that says new tab. And when you click on it, it will add a new blank tab to your ribbon. Now, once you click on this new tab, you can move it to the first tab as a first tab. And then you can click on the rename button, which is just next to the new tab button. And then you can rename this. Then from the left side where you have all the popular commands, you can select.
all commands from the drop-down, and then you can add all the options that you use frequently every single day to this particular tab. And by doing this, you will not have to jump from one tab to another for using all the options which you use every single day or multiple times in a single day. I'm sure this will going to increase your productivity and help you to save a lot of time. That's it for my site, and I want to wish you a Happy New Year. Hello, people. My name is Bernard Obengbateng from Finance Skills Hub, Accra, Ghana. I'm super excited to be here to share my top tip in Excel in 2024.
I'm sure we've all found ourselves frustrated with those pesky empty rows clattering our data when we reference entire columns in our formulas. Sometimes we notice formulas churning through entire columns, slowing our formula processing down. Well, this year, Microsoft Excel has a new function called Trim Range, a sleek new function designed to streamline your workflow by removing empty rows from the edges of your data ranges. So imagine this, you have data in column A, but below where the data ends are lots of empty cells. If you are using a formula like equal to len, selecting the entire column A to A, you will typically process over a million cells, including those blank cells, leading to inefficiencies and unwanted zeros. With trim range, you can zero in on just the populated cells, making your calculations faster and cleaner. But guess what, that's not all. Alongside trim range, Excel now has trim references, a shorthand way to apply trim range directly within your range references. You just have to tweak the column operator. By doing that, you can trim leading,
trailing, or both sets of empty rows effortlessly. While these enhancements are especially beneficial for those working with dynamic arrays or crafting lambda functions, allowing for more precise range selections and improved performance. It's all about giving you, the user, more control and efficiency in your data manipulation task. If you don't have trim range, I'm sure it's coming your way soon. It has just been released, so expect it. Thank you, John, for putting this together, and hopefully we'll see you in the next episode. Have a good one.
Hello, this is Jackie Chiotti. You can find me on YouTube and I'd love to connect with you on LinkedIn. So one of my favorite recent additions to Excel is the addition of the navigation pane. It helps those of us who have workbooks that have multiple worksheets, multiple objects such as charts, tables, pivot tables, etc. You get to the navigation pane by clicking the view tab on your ribbon and clicking the navigation button in the show group. What that does, it opens the pane on the right hand side of your screen. It shows all of the sheets in your workbook as well as all of the ranges, tables, charts, pivot tables, etc.
In addition to which, it contains a search pane, so if you know the name of your sheet you're looking for, you can quickly type it in there to search and find your sheet or your table or other object. So you remember how your Excel trainer always tells you that it's a good practice to name your tables and name your pivot tables to give them meaningful names? Well, if you built that habit, it really comes in handy when you're using the navigation pane because now you can see all of your objects and you can tell by the name of your objects what they are. So I invite you to go ahead and open up one of your massive workloads, click the view tab on the ribbon, click the navigation button, and watch the magic happen. Hey, I'm so glad to participate this year to this amazing global podcast about Excel because this year I have discovered an amazing trick with the function mid. Yes, the function mid, this so common function in Excel. In fact, I was working on the exact color code of different color and I had a huge list. Of course, for me, hexadecimal is not something easy to read. So I was wondering if it was possible to split the hexadecimal code with six characters in group of two, the red.
then two, the green, and the last two, the blue. Okay, I can use a function left, mid, right to extract each part. But because the pattern of extraction was always the same, two character, two character, two character, I was thinking if it was possible to extract following a pattern, pattern of two characters. And this is why I have the idea to put as the second argument of the function mid, not a single value, but an array with the curly bracket. And in this situation, it's simple to say you want to extract the first value, comma, the third, and comma again, the fifth value.
And what is the length for each of these groups? It's two characters. And it works also perfectly in another situation. It's when your date is year, month, day, and you want to convert that as day, month, year, for instance. It's the same logic. You can just use one function mid with the position of your different group of characters you want to extract and also to position them in the third part of the mid function. You want to see that? No problem. Go on my blog. I'm Frederic Le Guin from France, but my blog is in English. It's excel-tutorial.com. And on the search box, you search for pattern and you will find this article. Thank you for following me.
Hello, it's Andrew Moss from the Global Excel Summit. My top Excel tip of 2024 is to use FocusCell. If you regularly lose track of where you are in the worksheet grid, this is for you. It is a long overdue feature that arrived recently, and it really does transform the user experience, especially when you're faced with a big, sprawling dataset that spans dozens of columns. If you work on an ultra-wide monitor like I do, you'll probably know how frustrating it is to look at the far right of a worksheet when the row numbers are a meter away on the far left, and the lower down you are, the worse it gets. FocusCell tackles this problem by highlighting the row and column that belongs to the...
active cell, letting you work more confidently, knowing you are inputting or editing something in the right place. It's easily found on the view tab. Just click the main part of the focus cell button to toggle it on and off. And if you want to change the color of the highlighting, select the drop-down arrow, hover over focus cell color, and you'll see a palette of 10 colors ranging from dark to light. No longer do we have to rely on janky VBA methods or buggy third-party add-ins to do this. I, for one, am very happy this feature has arrived. Wherever you are, have an excellent 2025.
Merry Christmas and a Happy New Year. My name is Wyn Hopkins from Access Analytic and my tip for 2024 is one shared with me by fellow MVP David Ben-Aim and it's about turning US dates or a mix of what looks like US dates and normal dates and changing them into proper non-US date format. You simply highlight this mismatch of dates that you may have imported. You go to the data tab, text to columns. You choose next on the first screen. Up pops to limited. You say next on the second screen. And then you choose from the dropdown box in the top left corner. You choose date format, M-D-Y. Click finish. And it fixes it all. I never knew this was a thing. It's amazing. And I hope that helps you out. Hello, this is David Benign from Excel Consulting and also from the YouTube channel under my name, David Benign. And my top Excel tip for 2024 is the new group by function. Type equals group by and you'll get a brand new function that essentially replaces pivot tables. You first have to specify the row fields.
Then you specify, select the column for the value fields. Then you specify the aggregation method. You click from a dropdown list. You've got some, you've got average. You've also got new stuff like array to text, which creates a comma separated list of the text values inside it. So for example, you can have a list of the countries in row fields, and then you can have a list of the cities in values fields. So France might have Paris, comma Marseille, comma Strasbourg. And then Spain might have Madrid, comma Barcelona, et cetera. I also love group by and pivot by because they allow you to create.
any kinds of charts. So pivot tables are limited. They can't use the new charts, whereas group by and pivot by can create map charts, can create tree maps, et cetera. And they also refresh automatically. So it's a nice thing that pivot tables cannot do. You should note that they are at the moment only available for people with Microsoft 365 on the current channel. So if you have Excel 2024, which came out this year or a previous version, they won't be available. And if you're on Microsoft 365, but are on a later release cycle, for example, the
monthly enterprise channel or the semi-annual channel, then they will come soon but are not available yet. And that's it. I do love equals group by and equals pivot by, which can take column fields as well as row fields to create a two-dimensional matrix. Hello, my name is Yolanda Cuesta from Excel Contabilidad eTIC, my website. My best Excel tip of 2024 is Python in Excel.
With this tool, we can type Python directly into a cell. The calculation rule in the cloud and the result are returning into our Excel worksheet, including plots and visualizations. I love this and I have to learn about in the next year. Merry Christmas and a happy and excellent 2025. Bye.
Hey, this is Gerhard Basar from AlbertExcel.com and here's my Excel tip for 2024. If you've ever built one of those monster formulas with rabbits flying everywhere, functions inside functions, where even you, the creator, have no idea what's going on anymore, then congratulations. You have mastered a massive mess of nested functions. We have all been there, right? But don't worry, Excel has a solution, the LED function.
L-E-T, or what I call the nested functions 2.0. It's been around for nearly four years and is here to simplify your formulas and your life, of course. With LED, you can basically slice your complex formulas into manageable chunks, no more messing around with parentheses, every part of your calculation gets its own variable name, and the last part of the LED function calculates your desired result using those predefined variables. In the past, you might have needed VBA or complex workarounds for similar tasks. Today, the LED function combines beautifully with powerful functions like VStack, Filter, Lambda, you name it, to tackle very advanced challenges. Of course, LED has its pros and cons. It takes some time to master and can be tricky for others to follow. But once you get the hang of it, it can be a game changer. So I encourage you to give it a try and see how LED can simplify your Excel life in 2025. Wishing you happy holidays and a fantastic new year. Guten Rutsch und Cheers.
Hey everyone, my name is Cristian Angel, a Microsoft MVP and founder of Romanian Power BI Modern Excel User Group. Thank you, John, for having me to present my favorite Excel tip for 2024, which is related to Power Query. As everyone knows, there are six standard join types when doing merges in Power Query, all available from the user interface, and each of them very useful when doing data transformations. My tip for 2024 is that starting this year, there are two additional special types of join in Power Query, left semi-join and right semi-join. They are not available in the UI but very easy to implement just by modifying the join kind parameter in the formula bar. These join types are most efficient when your data source supports query folding, pushing the heavy lifting back to the source system for faster performance. Let's get a brief description for each of them.
A left semi-join returns only the rows from the left table where there are one or more matching rows in the right table. It's like a quick filter. You get just the data from the left table that has a corresponding match on the right. A common use case might be identifying customers with recent purchases. Think about this scenario. We have a customer table, the left table, and the recent purchases table, the right table. Our goal? Get a marketing list of customers who made a purchase last month. We can easily perform a left semi-join on the customer ID column.
Power Query will filter the customer table, returning only those customers who appear in the recent Purchases table. This is perfect for quickly narrowing down your customer list and avoiding data that you don't need. The other function is Right Semi-Join. Right Semi-Join works the other way around. It returns only the rows from the right table that have matching rows in the left table. Let's see a scenario where this can be helpful, finding products that have been ordered, for example.
We have an orders table on the left and the product catalog table, the right table, and our goal is to identify which products have been ordered at least once. By doing a right semi join on the product ID, we'll get only those products in your catalog that are actually present in the orders table. As a summary, left semi returns row from the left table that match the right table, while the right semi returns row from the right table that match the left table. Use these joints whenever you want to filter your data and take advantage of query folding to offload the work for better performance. Just a short additional mention here, these two functions will not bring data from the other table in the new created column after the merge. So don't be surprised, the data in the nested tables is always null. And that's my favorite tip for 2024. If you found this helpful, give us a like and subscribe for more quick data tips. Now go on and try left semi and right semi joints in your next Power Query projects to streamline your data processing. Happy holidays, everyone.
Hi, this is Brian Hong from My Excel Online, and this is my top Excel tip for 2024. So it's all about co-pilot in Excel, so it's very easy nowadays to get AI assistance right inside your Excel software. So key features include data analysis where it can just analyze your data sets and provide insights, summaries, or even charts to help you make informed decisions fast. It also has formula generation, so if you're struggling with complex formulas, you can just type in your, like, a normal English query asking to provide a specific formula, and it will generate it for you. Another thing is also about data cleaning. So if you need to like clean out inconsistencies in your data, then it can also make suggestions for you. So check it out for Copilot. So it's really a big help for your Excel journey. We hope you enjoyed this episode of the My Excel Online podcast. Be sure to subscribe and share this with your colleagues so they can reach their full Excel potential. You can also visit myexcelonline.com slash podcast for show notes as well as John's online Excel courses and free tutorials to help you stand out from the crowd. Until next time, keep excelling.