Skip to content
032: The Best Microsoft Excel Tips & Tricks in 2022 episode artwork

Feb 9, 2023 28 min

032: The Best Microsoft Excel Tips & Tricks in 2022

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 2022! 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.com

Episode details come from the podcast's RSS feed.

Embed this episode

Ready to play

032: The Best Microsoft Excel Tips & Tricks in 2022

0:00 28:16
of matches

Transcript Auto-generated, may contain errors

This is a MyExcel Online Podcast, episode number 32. 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. G'day guys and girls and welcome to another year of the best Excel tips of 2022. We've collated some of the best tips from the Excel experts and MVPs from all around the world to share with you today. And my tip to you is just to learn one of the tips from the following tips you're going to listen to. Learn one, spend one hour learning it, mastering it. And if you can do that, you're going to become better at Excel. It only takes one tip and one hour. That's my tip.

Now let's get into these awesome tips of the best Excel tips of 2022. For the show notes, go to myexcelonline.com full slash podcast and click on episode number 32 to see all the tips. Ozzy Soleil here from the YouTube channel Excel on Fire. And here is my tip for you going into the year 2023. Now I'm going to go in a different direction here because I know that my colleagues are going to share a lot of really cool hands-on tips.

But my tip is to encourage you, give you permission to not listen to the people who tell you Excel is dead or question your use of Excel. No, they are a gaggle of miscreants, nincompoops, and nerdy whales, or they are trying to sell you something that might cost as much as $3,000 a month. And guess what? Those people fall into the category of miscreant. Don't listen to them. Excel continues to expand. We've got the dynamic array functions. We've got...

Power Query continuing to get new features constantly. There's Power BI, Power Pivot, so many things, and it doesn't cost you $3,000 a month. Excel is built to get you what you got to get. So when you hear these miscreants, nigger poops, and nerdwells, don't engage them in their fiddle faddle. You just remind them that the time is ticking. They still have the opportunity to get on Santa's nice list. Joining you on Santa's nice list.

Getting off of the naughty list for 2023. Hey there, my name is George from albertaxle.com and I'm really excited to share with you my favorite Excel tip of 2022. Actually, it's something more for 2023. It's about Flash Fill. Nah, not really. But let me tell you, this tip is not just about the Flash Fill feature in Excel. It's about something even more revolutionary in my opinion. Have you heard about the ChatGPT tool from OpenAI? I think this is a game changer. And if this is good or bad news, I totally leave up to you. With ChatGPT, you can simply type out what you need in normal human language and the AI will do the rest for you. Want an explanation for an Excel function?

just ask the AI bot. Or you need some WBA code written, no problem, the accuracy of this tool is really, really surprising. But as always, be careful when working with AI. It's both fascinating and scary at the same time. I asked the AI to write me a description of the Flashful feature in Excel. So I told the AI to make it energetic, fun, and to use powerful words. I then ran the output through an AI voice processor and added some AI-generated background music to add some more drama to the whole thing. So here to yourself, I wish you all happy holidays and a fantastic 2023. AI, now it's your turn.

Thanks George for opening the stage for me, ladies and gentlemen and dear Excel lovers. This is your friendly AI assistant here to tell you about the most amazing feature in Excel, the Flash Fill. Now I know what you're thinking, an AI talking about Excel? That's just crazy talk. But hear me out because Flash Fill is seriously impressive. Flash Fill is a feature that automatically fills in cells based on patterns it detects in your data. It's like having a personal data genie at your fingertips. And let me tell you, it's a total game changer. Here's how it works. Let's say you have a column of first names and a column of last names, and you want to create a new column that combines the first and last names into a full name.

With Flash Fill all you have to do is type the first few full names into the new column, making sure to match the pattern of the data in the other columns. And then, drum roll please, Excel will automatically fill in the rest of the column for you. It's really like magic, magic as I am. But wait, here's more. Flash Fill isn't just for combining data, it can also help you reformat data quickly and easily. Let's say you have a column of dates in the format month slash day slash in year, and you want to convert them to a more readable date format. With Flash Fill, you can simply type a few dates in the new format, and Excel will do the rest. It's like having a personal data wizard in your computer.

So next time you're feeling overwhelmed by a large dataset in Excel, don't stress out. Just use flash fill and let it do the heavy lifting for you, or you just ask me. And remember, this part of this podcast was written, spoken, and musically accompanied by an AI, so you know it's got to be really, really good. Thanks for listening, and happy Ixling in 2023. Bye-bye.

Hey, this is Bill Jelen from MrExcel. My favorite new feature in Excel for 2022 is the brand new image function. Equal image, open parenthesis, put a URL to any image and it will bring that image back to that cell. It fits right in the cell. So if you want a larger image, make the cell a little bit larger. What a great thing for dashboards. You can even hook it up to slicers and a pivot table. So you can get, you know, all of the books that are about Excel or all the books that are about Power Query or all the books that are about Power BI.

Hey there, my name is Kat Norton from Miss Excel, and this is my favorite Excel tip of 2022. I'm a huge fan of the new text functions that were recently rolled out and looking at fun ways to combine them together. My current favorite is a sort, unique, and to call combination. This is great if you have an array of data that you want to combine into a single unique list that is also sorted. Now, since it is a function inside of a function inside of yet another function, I like to lay it out as a sort function first.

Then for the array, I layer in a unique function followed by the to call function, short for two columns, which is one of the new text functions. Now for the to call function, this is where we specify the array by simply highlighting the area of data you want to include in your list. Then we close the parentheses a couple of times until you are back out in your sort function, which is where I like to specify my sort order argument as the number one to sort my list from A to Z. So zooming out at the syntax here, just a quick pretend again, we start off with equal sort and an open parentheses. Then you place your unique function.

with another open parentheses. And that is where we input the to call function where you specify your array. And remember to add that one when you're back out in the sort function to sort the data in ascending order. And there you have it, a sorted unique list from an array of data in seconds using three functions. Hi, this is Jeff with Excel University, and my tip is about getting data into Excel. And yes, we have Power Query, and that works really well when the data is in a format that Power Query works with. In other words, some type of digital format like a CSV file or Excel file or database or something along those lines. But the tip comes into play when the data that we need is in an image or a picture file. And back in the old days, what that looked like for me was looking at the picture and then...

hand keying in the values into Excel, right? But now we have a new option and it's called import data from picture and it can pull in data from an image file or the clipboard. And while it's not perfect because it has to do a lot of optical character recognition and interpretation, it certainly is faster than typing in the values and then you can always correct them if there are errors. So anyways, check it out, import data from picture. Hopefully this will help and have a great year ahead. Hey everyone, this is David from xlformulabot.com, the site where you can translate your text instructions into Excel formulas in seconds with the help of AI. My best tip.

of 2022 is to not discount learning excel it is an imperative skill for any occupation that works with data whether you're an entry-level data analyst or a cfo or ceo even the most advanced data science models oftentimes start and end in excel as someone that has hired several hundred analysts over the years the number one skill i look for still is how deep can someone go in excel there's so many resources out there to deepen your excel skills so what are you waiting for Hello, my name is Celia Alves from Solventexcel.ca, and in 2022, I fell in love with the Excel dynamic array functions. These functions can return multiple values all at once into the neighboring cells of the cell where we write the formula. This behavior is called spilling. The great news is that all the other functions that we have been using for many years, like VLOOKUP, for example, can also take advantage of this new technology. So if we enter an array as the lookup value in a VLOOKUP formula, we will get an array of results, one per each value in that initial array.

Anyways, with the spilled array behavior, solving problems in Excel became an even bigger delight than before because the dynamic array functionality allows for much simpler, lighter, and more fluid solutions. It's simply beautiful and mind-blowing. The last batch of dynamic array functions included 14 new functions, 14 in one go. To play with them on Excel desktop, you need a Microsoft 365 subscription, but you can also use them in Excel for the web with a free Microsoft account. I wish you all a wonderful 2023 with a lot of joy at work using Excel.

Hey there, it's John O'Connor with ExcelCampus.com, and I want to share a tip about leaving your legacy with your Excel files. To explain what I mean, I'll share a quick story. So my wife and I bought a home last year, and after we moved in, we quickly realized that the previous owner had done a lot of work to make the home easier for us to live in and maintain. They had not only done a lot of upgrades to the systems, but also kept everything very well organized. There were maps and guides for the circuit breakers and sprinklers and security systems. Filters had been marked with installation dates, and they even organized appliance manuals and receipts. And not only were we impressed with this, but it also saved us a lot of time and money.

And even though the previous owners only kind of managed or lived in the house temporarily, they left a nice legacy that really made it easier on us. And I'm now thinking of ways to continue and improve this practice for future homeowners. And I believe we should do this same practice with our Excel files. Excel is almost 40 years old now, and it's not uncommon to work with a file that's been around for over 20 years. This is especially true when you work for larger organizations, as processes tend to be a bit slower to change. So whether you're creating a new file from scratch or you're inheriting the management of an existing file, it's important to leave your legacy on the file.

And you can do this by simply making the file easier to use. Make it more approachable instead of more complex. This will have future owners of the file kind of singing your praises instead of cursing you for making their life more difficult. I don't know about you, but I've experienced this several times in my career where I took over a role with some complex spreadsheets that were developed by someone else that came before me. And I spent a lot of time and energy just trying to figure out how to update and use the files. I also believe I've made this mistake in my career a few times as I've created files with complex formulas and macros that weren't really well documented. And this likely made it more difficult for others to inherit and use.

files and i don't want you to make these same mistakes so i put together a free checklist with 19 items for creating excel files that your boss and co-workers will love to use you can grab the free checklist at excelcampus.com legacy and the checklist includes simple tips for formatting and documenting formulas also adding a table of contents for easier navigation of your files it also includes more advanced techniques for automation data entry data validation making reports more interactive and a whole lot more the tips will not only help you leave a great legacy with your excel files for future owners but they will also help you take what i call a stress-free vacation where you don't get emails while you're on vacation with questions on updating your files. So again, you can download the checklist at excelcampus.com slash legacy. Thanks again, John, for including me on the podcast. I wish everyone all the best in 2023 and hope you have a happy new year. Hey Excelers, I've never created an offer as good as the one I'm going to give you. That's right. We're giving you 30 days full access to all our Excel and Office courses for only a dollar. And the best part, I have five smoking bonuses that I'm giving for free to the first 100 people to sign up today.

You'll get access to these five bonuses that are valued at over $6,000 for free. How awesome is that? You'll save hundreds of dollars by just making one little choice right now. And before you think this deal will come around a different time, think again, my friend. I have never done this before and I have no clue if I will ever do it again. So you definitely want to snag this dollar trial for 30 days today before it goes away. Now, I can't wait to see you in the dollar trial during the next 30 days so we can make you better excel in office and you can realize...

your dream of saving hours from your workday, becoming more efficient, more skilled, which will lead to more recognition, promotions, pay raises, or a better paying job. How good is that? Now you'll save hundreds of dollars, but just making one little choice right now. So click the link and I will see you inside the course for only a dollar, which is less than a cup of coffee. Go to myacassonline.com and redeem your one dollar trial today.

Hey everyone, it's Ken Pulse from xlguru.ca and skillwave.training, and today I want to show you my favorite tip of 2022. It is granular field layouts in pivot tables. This is such a favorite thing that it was actually like the second episode of my Monkey Shorts channel on my Skillwave YouTube channel, and you can find a link to that right up top here. So here's the issue. I've got this great pivot table layout, but I really want a blank line after each change in date and a blank line after each change in journal entry number. The problem is, if I go and insert blank rows after every item, I also get blank lines after every account, and I don't want that at all. So I'm going to uncheck this and remove these, and here's how I'm going to make this happen.

A lot of people don't know that you have the ability to control this on a field-by-field basis. So I'm going to go right-click, and way down the end, I'm going to go to Field Settings. And on my Field Settings here, I'm going to go to Layout and Print, and I'm going to choose to insert a blank line after each item label. You'll also notice we can control it. We're in tabular form right now, but I can make this next column outline form or repeat item labels just for this individual field. If I go and say OK, notice I just get my blank line after this item. So that's cool. I'm now going to go and say right-click on this guy here.

Field settings again. We'll go into layout imprint. And here we go. We're going to insert a blank line after each item label in the reference column as well. And say OK. And boom, it gives me that little bit of breathing room that I'm looking for.

Now, the last thing I'm going to do, even though you don't see it on this particular screen right here, I can end up with accounts that have multiple descriptions posted to them and that would leave a blank line here. So I want to fill my item labels down in case that ever happens. No big deal. Right click. We can go to field settings, layout and print. And here we go. I can control repeat item labels just for this one specific column without worrying about it applying all over the place there. So there you go. That's my favorite Excel tip for 2022 is granular layouts inside pivot tables. Hello, my name is Yolanda Cuesta from ExcelContabilidadCity.com.

And this is my favorite Excel tip of 2022. Image function. Now your images are part of Excel and they don't are floating. Besides, you can move short and work with them easily. Now, I wish you a Merry Christmas and an excellent New Year 2023, in which we will have many interesting Excel news, I'm sure. Hey everyone, Jordan Goldmeier here of advancedxl.com. That's advanced X as in the letter, L as in the letter dot com. Here's my tip for you. Now, many of you might know of the new function called IFS, IFS, which allows you to chain together multiple IF conditions so you no longer need a nested IF. My tip to you is that the final condition of an IFS, if instead of you...

putting in a condition rather you just write a true as the last parameter it's always going to be an if else if else if else if and then the final one is always going to be an else that will always happen because if you put a true in that final condition if everything else isn't qualified then you basically have the final condition so if you put a true down there at the last parameter whatever happens if the other conditions aren't satisfied this one will always be satisfied so that's my tip make sure to use a true at the end if you want to chain together an if else and with the final one to always happen if the other ones do not hit all right thanks everyone Hello, my name is Ivette Savados from ExcelNacad.hu. My favorite Excel tip of 2022 would have been a huge help for me for years at work. Currently, this is in beta version, but I hope that this will be available soon for all of us. I'm sure you know some workarounds to keep leading zeros for numbers, but this will change soon. You can keep these leading zeros just by one click. Go to options, advanced, then in the automatic data conversion section, you can uncheck the box that says remove leading zeros and convert to a number. And that's it. So simple and saves so much energy and time. I hope you can use it soon. I wish you happy holidays and happy new year.

Wow, what a year. My name is Wyn Hopkins and I'm an MVP and author of the book Power BI for the Excel Analyst. It's been an amazing year. Things have gone from strength to strength in Excel and my tip is that you must keep checking out the Excel blog. The only way to keep up to date with everything is follow the blog. Search for Excel Tech Community blog and all the new features, whether it's text join or searchable data validation boxes or new connectors for Power BI, it's summarized there.

Obviously, check out the Access Analytic YouTube channel as well for all sorts of features. But my main recommendation and tip for this year is the Excel blog is the place to go. Hi, I'm Cristiano Galvão from Excel Turbo, and this is my best Excel tip of 2022 for you. Learn how to use the advanced formula environment along with the Lambda function. This is a new way to write formulas and transform them into your own custom functions. This is a game changer in Microsoft Excel, and it will be a big plus in your skills.

To start exploring this new universe, open your Excel from Microsoft 365, go to the Insert tab and press the Get Addings button. There, search for the word formulas and install the advanced formula environment. The tool will offer you a tour so that you can take the first steps without need for outside help. About the Lambda function, you can find more information on the Excel Turbo channel on YouTube. Hi, my name is Anne Walsh, also known as the Excel Lady. My website is www.theexcelexpert.com, with dashes between the Excel and the Expert. My favorite tip of 2022 is the Analyze Data feature on the Home ribbon. This is only available in Office 365, but it's a great way to interrogate your data and get some interesting questions posed about it. Your data needs to be in a table format.

But if you just click on it, once you have your data sorted out, go to Home, Analyse Later and see what different questions it pops up. And then it actually generates some suggestions to you. If you decide to make a pivot table of any of them, it actually gives some of those ones to you. All right. I hope you find this useful and mind yourself and take care. Bye.

Hi all, this is George Mount from stringfestanalytics.com, and my tip for the year is to read data organization in spreadsheets. This is a 2018 article from the American Statistician, written by Carl Broman and Kara Wu. You can get it online for free, and I will link to it. Now, this is not going to give you the hottest tips and tricks in Excel. What it will give you is a really thorough philosophy for how to store your data in the first place, because a lot of the problems that we have with data really come down to it just not being stored well. So check this article out. Some of the tips may not be as pertinent now that Power Query is a thing. I'll let you read the article and see what you think, but again...

as far as really kind of changing your mindset and getting you to think differently, I think this article is great. So take a look at that. Let me know what you think. I hope you have another great year in spreadsheets. Want to thank John and everybody else for putting this together. Looking forward to seeing you in 2023.

hi this is brian hong and this is my top excel tip so if you want to supercharge your excel automations it's best to use chat gpt for that because with chat gpt you can just simply ask it to write a macro or a vba code for you like a code snippet that you can simply use right away in your excel spreadsheet so what it can do is it will write the code for you based on your requirements and then you can even ask it to make modifications for you and then it will provide documentation as well on how to best use that code for you Hello, my name is Maria Braga from MBI Accelerate Your Business, and this is my favorite Excel tip of 2022. I love to use the importing data from PDF within folders with Power Query. This is an incredible way to transform your complex task of extracting data from several PDFs into a single Excel table really quickly. If you don't know Power Query...

I recommend you to explore it. It will save you a lot of time. I also used a lot of dynamic array functions like filter, sort, and unique, and all of them combined to have a kind of pivot table with formulas. They are amazing. Hi, this is Casper from Spreadsheeto.com, and this is my favorite Excel tip of 2022.

Wow, what a year we've had. ChatGPT blew the Dorian this November, and the Excel world might be changing forever because of it. So what's something you can do right now to take advantage of this new AI? Well, if you're pretty good at Excel already, ChatGPT can write VBA code for you. It may not be perfect, but it's pretty good. Try go to chat.openai.com and write, send me VBA code that combines data from each sheet in the workbook into one sheet. And ChatGPT writes the actual VBA code for you.

It's pretty amazing. Try it out. It's very cool. Now imagine what else you can do with this powerful chatbot. I'm Caspar Langman, and this was by far my favorite Excel tip of 2022, maybe even my favorite Excel tip of the last 10 years. Happy New Year from all of us here at spreadsheet.com.

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 28 minutes long.

When was this Learn Microsoft Excel with MyExcelOnline episode published?

This episode was published on February 9, 2023.

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.