Skip to content
033: The Best Microsoft Excel Tips & Tricks in 2023 episode artwork

Mar 15, 2024 26 min

033: The Best Microsoft Excel Tips & Tricks in 2023

from Learn Microsoft Excel with MyExcelOnline

We have rounded up the best Microsoft Excel experts & MVPs from around the world to share their best Microsoft Excel tips & tricks in 2023! These Microsoft Excel tips & tricks will save you time and make you a more efficient Excel user. Make sure to learn and apply at least 1 tip from today's podcast show. Each tip is covered in our latest Excel E-Book which you can download for FREE for a limited time only: Click here to download our FREE 101 Excel Tips & Trick E-Book! Show Notes: MyExcelOnline

Episode details come from the podcast's RSS feed.

Embed this episode

Ready to play

033: The Best Microsoft Excel Tips & Tricks in 2023

0:00 26:04
of matches

Transcript Auto-generated, may contain errors

This is the MyExcel Online Podcast, episode number 33. 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 awesome Excel podcast show where we gather the best Excel experts and MVPs from around the world to share their best Excel tip in 2023.

Now, first of all, I've got to thank everyone who's contributed to this amazing podcast. Every year, we have all the Excel experts come and join us and give us a tip. And from the bottom of my heart, I've got to thank each and every one of them for giving us the tips and sharing their knowledge. And also, on a sad note, one of our... past Excel experts, Yvette Zabados. She's from Hungary. Her website is axelnaked.hu. I sent an email out to her so she can give me a tip for this year. And her husband replied back to me with some sad news that she passed away. So yes, it was really sad news to hear that. And yeah, I just got to give my condolences out to her family. And so this year, my Excel tip isn't about Excel. It's about life, just to live life to your fullest and just love everyone. So with that, let's get into these awesome Excel tips. Hey, John, it's Bill Jelen from MrExcel.

I think my favorite tip in 2023 is the brand new group by function. oh, this just came into beta maybe a few weeks ago, but I am using it all the time. I'll have a quick little database and I just want to know how many records are in each category or something like that. Of course, easy to do with a pivot table, easy to do with some if and unique, but just so much easier to do a group by and say this is the column we want to analyze and either count or sum. It even adds a grand total at the bottom. You can sort high to low. It's just great for me working on a project knowing how many items I have left to process and in each category. It's a great addition to Excel. I can't wait until it comes out.

so everyone can try it. Hello, this is Danielle Stein-Fairhurst from Plum Solutions here in Sydney, Australia. My top Excel tip for 2023 is the use of dynamic arrays. Now, I know they've been around for quite a while, but I know that a lot of people are still not always using them. So that is my top tip for this year is to get to grips with them and start using them in your Excel models. So as a financial modeler, one of the points of best practice in financial modeling is always to make sure that you have consistency of formulas as much as possible. So when you have those calculation blocks having...

one single formula that goes all the way across and all the way down just makes your model not only easier for you to build, but also much, much easier for others to audit and to check and to test your calculations in the future. And that is something that is so important in financial models. And if you're using dynamic arrays and you are basically forced to have a consistency or formulas. And that's why I think dynamic arrays is such an important topic. In fact, I have chosen to speak about that topic at the global Excel summit coming up next month in London. So I hope to see some of you there either in person or online. So thank you everyone. Have a wonderful new year and I hope to see you all soon. Hey everyone. It's Ken Pulse with you from xlguru.ca and skillwave.training. I also happen to be the brains behind the monkey tools Excel add-in and I'm here to share my favorite tip of 2020.

with you, which is actually not specifically an Excel tip, but rather a Windows tip that I think will increase your productivity. And it has to do with the ability to turn on the Windows clipboard to save multiple things for pasting. So the way you do this is you type your Windows key on your keyboard and you type in clipboard and you want to go to clipboard settings. When you're in there, turn on clipboard history. And what that means is now when you go control C.

You can still do a control V to paste, but if you press Windows V, you actually get a list of multiple items there. So this is going to make your copy and paste experience a lot easier if you're actually copying and pasting, you know, two things, you need to paste them in different places, a lot less flipping back and forth. You can even turn this on to sync between multiple computers, which just makes your life a lot easier if you're trying to work across multiple different desktops as well. So hopefully you'll find that helps increase your productivity for 2024. Ozdu Soleil here from the YouTube channel Excel on Fire. My tip for you.

Please learn how to cultivate fake data, whether you keep a workbook like I do that has a lot of fake data or you use a large language model or something else. But fake data is vital. Two reasons. First of all, in 2023, we heard so much about privacy and large language models and people uploading sensitive data into large language models. And it's just irresponsible and unnecessary. You need fake data.

so that you can represent the problem that you have, where you need help. Do you need help with analysis or are you trying to email data to somebody like me to get help? I don't want 500 rows of kidney dialysis patient data. Second reason, you need fake data that you can play with because there are so many features coming out in Excel. Now there's Python that you can do in Excel. There are new functions like group by pivot by and to call.

get some fake data that you can play with. And for example, make up 50 high school names. They're mascots. The number of seniors, juniors, freshmen, and sophomores. When the school opened, do they have a swimming pool or not? If the school closed, what year did it close? All kinds of data there. And you can play with to call, group by, et cetera. And you can email that data to somebody like me to get help. And I won't feel funny about it.

cultivate fake data, and practice being a prudent steward of data. This is Jeff with Excel University, and my favorite tip this year is dynamic array functions. If you haven't explored some of the dynamic array functions, it's time. It's time to learn them and begin to incorporate them into your formulas. They create new opportunities and totally new approaches, and they enable fully dynamic worksheets. If you're looking for a few to get started, check out Group By, Pivot By, Filter, Unique, and Sequence. I cover these on my YouTube channel, so definitely stop by. Also, I'd like to invite you to my free Excel Speed Challenge. It's extremely condensed so that within 45 minutes of training, you can save an hour per week going forward. ExcelUniversity.com slash challenge.

My name is Kat Norton from Miss Excel, and this is my favorite Excel tip of 2023. Did you know that you can pull multiple consecutive columns of results using an X lookup function? First, specify your lookup value, which is where we ask ourselves, what are we looking up? Next, specify your lookup array, which is the column or row where Excel can find that match to your lookup value. And when it comes to the return array, this is where it gets interesting. Instead of just highlighting one column to return, we can highlight any consecutive columns you want to pull in as your results. And Excel will spill those results out into the adjacent cells in seconds.

Hey, it's John Ockampora from ExcelCampus.com. So there were a ton of new features released for Excel in 2023, but one of my favorites is the new checkboxes in cells. We can now place checkboxes inside of a cell. It's a really simple feature, but the nice part is that the checkboxes can make our spreadsheets interactive and easier for your coworkers to use. And this is especially true when you combine them with other Excel features like formulas and conditional formatting. I have an entire blog post and YouTube video where I share several examples of how you can use the new checkboxes in your Excel files. Everything from simple checklists to attendance trackers and even an interactive calendar chart for project management. And I also share a hidden feature that I think makes them better than the

checkboxes in Google Sheets. You can check out that post and video at excelcampus.com slash box. That's excelcampus.com slash B-O-X. Thanks again for including me on the podcast, John. I hope everyone has a happy holidays. Hello, this is Alan Murray from Computer Gaga, and my top tip of 2023, unsurprisingly, is to use AI for Excel. If you have not been living under a rock this year, you may have noticed that AI is suddenly more popular than smashed avocado or sloths. Well, maybe not the latter.

And it's important that you get in the game or you will be left behind. Now, the easiest way to get started with AI for Excel is to go to ChatGPT. So if you navigate on a browser to chat.openai.com and start speaking with that, asking for Excel questions, asking for formulas, asking for advice. Looping back to Excel, there's an add-in called Excel Labs. And this is also a great way to integrate it with Excel and start testing what AI can do for you.

get access to the add-in from the far right of your home tab and see how the advanced formula environment can help format and troubleshoot your formulas. And the function called labs.generativeAI can be used to send custom prompts to the AI model right from the grid. So that's my tip. I wish you the best for this year and get stuck into AI in 2024. Hello, my name is Paul Barnhurst, aka the FP&A guy from the fpnaguy.com. And this is my favorite Excel tip of 2023, probably the same as it is of 2022. And that tip would be to learn dynamic arrays. They're a game changer. They will change the way you work in Excel. And one of the best parts of dynamic arrays, if you're on Excel 365, is they're constantly updating them and adding new functions. From unique, to quickly remove duplicates and get a unique list, to xlookup, to some of the newer ones, text split, text before.

Text after in many places, they take away that need for mid, left, right with these new functions. There's other ones to call, wrap rows, take, drop, sequence. The list goes on and on. What's awesome about these functions is they're dynamic by nature. So if your list changes, the unique list automatically updates to add those new elements in your list. Or, you know, you have other cases where maybe you're doing an X lookup and the list change and you can have it dynamically add the new elements to the list.

I think they are going to revolutionize the way we work in Excel. For many, they already have. And so if I was to give one tip for 2023, if you're not currently using dynamic arrays, I recommend you start using them and learn about them and understand how they can change the way you work in Excel. And finally, I'll just wish everybody a Merry Christmas, Happy Holidays, Happy New Year. And again, let's all enjoy the 2024 journey and improve our Excel skills. Hi, this is George Mott from StringFest Analytics, and my tip for this year is the Unpivot Other Columns feature in Power Query.

Now, there are a couple reasons why I recommend this one. First, let's address the elephant in the room, AI. Yes, AI offers many exciting possibilities, but the old saying garbage in, garbage out still holds true. If your data is in a pivoted format, you will not achieve the same depth of insights from AI as you would with properly formatted data. Thankfully, Power Query Big Surprise has simplified this process of unpivoting a data set, a task that used to be way more complicated in Excel. However, I recommend a slight variation in this process. We're going to select the columns you do not want to unpivot first and then use the unpivot other columns feature. So why am I suggesting this indirect approach?

First, if your dataset contains many columns that need unpivoting, this method is way more efficient because it requires fewer clicks and less manual selection. More importantly, though, if your dataset regularly updates with new columns that require unpivoting as well, this approach will ensure that all of those newly added columns are automatically included in the unpivot, minimizing the need for ongoing adjustments, and that is the whole dream of Power Query. So try that out and use it to your advantage. Thanks again for listening. I'm thrilled to see how our community advances Excel in 2024 with so, so many innovative breakthroughs. However, remember...

Everything we do starts with clean, properly formatted data, which usually means it's unpivoted. Hello there. My name is Celia Aldes from SolventXL.ca, and this is my favorite Excel tip of 2023. It contains three parts, actually. Part one, if you haven't yet, start to use dynamic array functions. They are fun to use, very versatile, and Microsoft just puts another two new functions in the oven, GroupBy and PivotBy. These functions are currently available in the beta channel. Part two, as you play more with these dynamic array functions and get excited about let and lambda, for example, you'll need to write longer formulas and therefore to expand your formula bar.

The shortcut Ctrl Shift U will toggle between expanding and contracting the formula bar. Part three, to edit your long functions like a pro and also save your custom functions, get the Microsoft's add-in Excel Labs and create your formulas in the advanced formula environment. There, you have room and functionality that allows you to write your formulas in multiple rows, identified as needed and color-coded for easy reading and interpretation. And it recently got a very fancy formula debugger. If you often write long formulas, you'll love it.

you can get Excel Labs by clicking on the button Add-ins that you can find in the Home tab and searching by Excel Labs. Have a wonderful 2024, everyone. Merry Christmas. Hey, John. Thanks for having me on your show. It's Georg Lazar from albatexcel.com, and here's my tip of 2023. Let's talk about error values in Excel. You may have noticed in your daily work a new kind of error value when applying certain functions in Office 365. I'm talking about the so-called spill error. And in German, it's called Überlauf, and this just looks strange on your spreadsheets.

This error appears whenever you use so-called dynamic array functions. And you may have used a very popular one without knowing. I am referring to XLOOKUP, the successor of VLOOKUP with more functionality. Those kind of functions can take up different kind of space where results are written into. And whenever there's something in their way, meaning they cannot spit out results because other values are in their way, then you have successfully created a spill error. Just one example. By the way, my favorite is the filter function, which I personally find very powerful. In the end, in Excel, it always comes down to have a clean, well-crafted database to run your analysis on.

Dynamic arrays are just one of the many options in Excel to support this endeavor. So keep watching out for more functions like this in 2024. And I want you to remember one thing. You can not order drinks at the formula bar. And if so, don't spill them. This is Georg from Berlin, wishing everyone nice holly days. Bye-bye.

Hi, I'm Cristiano Galvão, Microsoft MVP and content creator on the Excel Turbo channel. My best Excel tip for 2023 is follow all these guys here and watch the Excel events that the community is doing around the globe. There are online events, but also in-person events, such as Excel Weekend, Bulgaria Excel Days, Global Excel Summit. And you have the Excel Virtually Global Online and also the Excel Championship. It's amazing. Follow the guys and participate in some events because the possibility to create a strong network is huge. And you will meet in person the Excel Masters that you love. Hi, my name is Richard Nero from DevelopX. My Excel tip is probably going to be a common one, but it's all about the power of Power Query. Imagine a world where Dataprep isn't a daily chore. It's a one-time setup that simplifies your life.

That's what Power Query offers. The major benefit of Power Query is it's set-once-use-forever structure. You go through the data prep process just once, and after that, it's just simply a matter of refreshing your queries to update your data. Right-click that pivot table, pivot chart, left-click, left-click, refresh, and boom, you are done. Data is up to date. No more repetitive tasks every time your data source changes. It's a game changer for efficiency. Power Query connects directly to data sources.

Oracle, SAP, Databases, SharePoint, D365, Salesforce, ODBC, Connections, Hadoop, all that lovely stuff on Azure, whether it's blob storage, data lakes, or signups, analytics, and APIs, and I could go on and on. And because it connects directly, this eliminates any copying and pasting data into your spreadsheets. If you're copy-pasting data into spreadsheets, then stop. Please stop. Go learn Power Query. By connecting directly to databases and online services, you're ensuring your data is always up-to-date and accurate. The true superpower with Power Query is the ability to write SQL scripts and utilize stored procedures and views. By using code stored in the SQL box, you centralize maintenance and development that not only streamlines your workflow, but also enhances collaboration and consistency across your team or organization. So if you're new to Power Query...

Check out Master Your Data by Kempos. And if you're already comfortable with Power Crew and want to go to the next level, check out Gil Revy's book, Collect, Combine and Transform Data. Power Query transforms the way you handle data in Excel. It saves time, reduces errors, and allows for more sophisticated data handling techniques. So if you haven't started using Power Query, now is the time to dive in. Hello, it's Andrew Moss from the Global Excel Summit. It's been another cracking year for new features, functions, and improvements. Therefore, my tip of the year is to stay up to date. This is more important than ever. 2023 has been a breakthrough year for AI, and it's already having a huge impact on our daily lives. And it's not going away whether you like it or not, so you might as well embrace it.

Excel is being transformed from a mere data analysis tool to a turbocharged data science juggernaut. This year we've seen Python arrive, we've seen co-pilots arrive, we've seen checkboxes arrive, we've seen a raft of new functions arrive, including image, group by, and pivot by. The list goes on and on and on. They're not gimmicks, they're game changers. Granted, some of these are only available to Microsoft 365 insiders at the time of speaking. However, we can look forward to their mass rollouts in 2024. If you want to save time, be more efficient, more productive, and build bulletproof workbooks, you need to tap into modern Excel. Get ahead of the curve now with new stuff.

coming thick and fast. There's only going to be more to learn next year. And if your company still insists on using Excel 2013, do everything in your power to get them to upgrade to Microsoft 365. They don't know what they're missing out on. Hello, my name is Yolanda Cuesta from ExcelContabilidadCity.com. And my favorite Excel tip of 2023 is Translate with all the AI power into Excel.

You can try it from Reviuta. And now I wish you a Merry Christmas and a happy and excellent new year. Did you know that if you name a rectangular range, give it any name at all, if it's more than one cell long, then if you use the zoom slider or view zoom and go to 39% or less, then that name will show up in the worksheet as if we were doing a page one print preview kind of thing. It's a great tool for examining workbooks that you get to see how people define their names and if they're consistent. Thanks. Bye.

Hi, this is Brian Hong of MyExcelOnline.com and this is my top Excel tip for 2023. So it's all about using ChatGPT to simplify your Excel process. If ever you have a complex formula or if you're working with complex VBA code that you're not sure on what it really does, then all you need to do is to copy this formula or code straight into ChatGPT and then tell it to explain it to you. like in layman's terms. And the really cool thing with this one is it's able to give like a step-by-step breakdown so that you can really drill down and reverse engineer on what this code or formula is doing. Another quick tip as well is you can just tell ChatGPT to explain it to a five-year-old, for example, to just really make it like really simple terms so that something that's really long or really complex for you that you have no background off, right? You can quickly start learning from that.

Hi, my name is Frederic Le Guin from the website excel-tutorial.com. In this video, I'm going to show you my best tip for the year 2023. And what is this tip? It's how to find my drop-down list. Because in Excel, to find a drop-down list, the only way is to select many cells till you find the cell with the arrows indicating that you have a drop-down list. But of course, it's not a convenient solution. My solution is to go in the menu Home, Editing, Find and Select, and then you have Data Validation. And just like that, automatically Excel highlights all the cells with a data validation. And then you just change the color.

And here we go. We have all the different drop-down lists in my worksheet. 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.

Frequently asked questions

How long is this episode of Learn Microsoft Excel with MyExcelOnline?

This episode is 26 minutes long.

When was this Learn Microsoft Excel with MyExcelOnline episode published?

This episode was published on March 15, 2024.

Is there a transcript available for this episode?

Yes, a full transcript is available for this episode. You can read the complete transcript on the episode page.

Can I download this Learn Microsoft Excel with MyExcelOnline episode?

Yes. Use the download control on the episode player to save the publisher-provided media file.