This is a MyExcel Online Podcast, episode number 28, Christmas Special. 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 Mikalutis. Hello guys and girls, and welcome to our annual Christmas podcast special episode that we have for you. And it is going to be a gangbuster podcast. Now we do this special podcast every year, and each year it just keeps on getting better and better. In today's show, we have Excel experts from around the world. And we're starting all the way down from New Zealand to Asia, we're going to go through Europe to America and end up in Brazil. Now in this episode, you're going to find some awesome...
Excel tips and also some great productivity tips to help you become more efficient in your day-to-day work. And to get the show notes for this episode and get the links to each Excel expert and the tips, make sure to go to myexcel9.com forward slash podcast and go over to episode number 28. Now at the end of the show, I'm going to share my best Excel tip of 2020. So make sure you go all the way to the end so you can see this awesome tip. Now let's get into it.
Hey, this is Bill Jelen from MrExcel and the big news for 2020 coming right at the end, a hot new Lambda function. Put away those VBA user-defined functions. You now put a user-defined function right in the name manager. Pass a couple of parameters, do some calculations, return the answer to a cell. It's amazing. Hi, this is Jeff from Excel University. And my best tip for 2020 is this. If you have access to the filter function, use it. It is amazing. It competes head-to-head with and actually can do the things that we use VLOOKUP for, SUMIFs, INDEX MATCH,
and XLOOKUP. Oh, and more. It's a universal all-in-one function. Absolutely incredible. So if you have a version of Excel that has the filter function, you definitely want to check it out. Head to my blog. I have a video that talks all about it so you can check it out. Thanks and have a great year. Bye.
Hello, this is John Peltier of Peltier Technical Services at PeltierTech.com. I do a lot of work with Excel charts, including training and tutorials to help people with their own Excel charts. My favorite chart trick is this. It can be difficult to change the data in a chart. The dialogue is complicated, and editing the series formula is difficult. But when your data is in the same sheet as the chart, when you select the chart or select an individual chart series, the source data of the chart or selected series is highlighted with colorful rectangles. You can easily see where the data is, but you can also change the data using these highlighted outlines. You can move the data by dragging the highlighted outlines by the edges.
And you can resize the data, adding or removing points or series by dragging the corners of the highlighted rectangles. Now, isn't that easy? Here at PeltierTech.com, we have tons of blog posts with chart-related topics, and we sell add-ins that help you make charts that Excel doesn't know it can make, help you with chart data, help you with chart formatting, and more. Thank you. Hi there, this is Leila Garani from xdlplus.com. My favorite tip in 2020 is the import from PDF functionality in Power Query. This is available in Excel, the 365 version. With it, you can import a PDF document and convert everything into Excel data. All you need to do is go to the Data tab, Get Data, From File, From PDF. Then you just have to browse for your PDF file. You're going to see a list of tables followed by a list of pages. So if your data is recognized as a table by Power Query, it's best to select a table version instead of the Page option, because the Page option has everything else that's on the page, which you may not need.
Now, you can select one or multiple pages that you want to import. In case you need to transform the data, you can. For example, remove any information you don't need or add additional calculation columns. And then when you're done, just send the data to your Excel file. The great news is that you also have an automatic connection to the PDF. So in case the content of the PDF file is updated later, all you need to do is refresh your query and grab the latest content. On my blog, you're going to find more information about this. Just go to xdlplus.com slash tutorials. That's my tip for 2020. I wish you a happy new year.
Ozdu Soleil here from the YouTube channel Excel on Fire and thanks to John Michaelutis for asking me to include a tip in the yearly roundup. And here's my tip going into the year 2021 and beyond. The world of technology is making more use of artificial intelligence and machine learning and Excel is not exempt. We're getting great tools like flash fill, fuzzy matching, column by example, and ideas. But these tools come with an unspoken demand that we know our data and we have to anticipate where these tools might mess up and If we didn't write the formulas or generate the power query queries ourselves, we've got to check this work. Let's say we got a lot of transactions. One transaction has five items and the total transaction amount was $300. Another transaction has two items and the total is $1,000. And then we use ideas to ask which transaction had the most. Ideas is going to give you an answer, but it's going to be on you to go and look at the result. Did it give you the most items or did it give you the highest dollar amount? And is it the result that you wanted?
Or let's say we've got a bunch of names. The first name and last name are all in one column. And we use Flash Fill to dig out the first name. We've got to know our data. Do we have things like Mrs. Anderson or Mr. James Crawford? If we use Flash Fill, it's likely to dig out the salutations Mrs. and Mr., which are not first names. You've got to know the data to know that that's possible and then go back and check Flash Fill's results and maybe fix those manually. And this is very serious. If you get in trouble or embarrass your boss,
or a client or embarrass yourself, you're not going to be able to say, well, that's what Flash Fill gave me. I'm sorry. No, not going to work. So use the tools, but know your data, anticipate where these tools might make a mistake and check the results. Hey there, it's Jonathan Campora from ExcelCampus.com. With all the awesome new features in Excel, it's really tough to just share one tip. However, if you're someone that works with data frequently, especially messy, unorganized data from multiple data sources and databases, then my biggest recommendation from 2020 is to learn Power Query. Power Query is a data automation tool within Excel that helps us with importing data into Excel and cleaning it up at the same time. So if you're someone that spends time preparing data by deleting rows or columns, removing blanks, replacing values, doing lookups to other tables, converting text to dates, or even stacking or appending data tables together, then Power Query will be your new best friend, if it isn't already.
Power Query allows us to create automated processes for specific data cleansing tasks that can be repeated every time you get new data or need to run your reports. We've seen the members of our courses and training programs save a ton of time with their routine reporting tasks by learning and using Power Query. And this also helps them reduce errors with their work, eliminate boredom, and of course, impress their boss at the same time. So if you'd like to learn more about Power Query and how it fits in with other tools like PivotTables, PowerPivot, Power BI, and even macros and BBA, then check out our free training webinar called the Modern Excel Blueprint. You can get signed up at excelcampus.com slash webinar. Thanks again, John. I hope everyone has a Merry Christmas.
and happy new year. Hello, my name is Alan Murray from ComputerGarGar.com and this year has seen many updates and many improvements in Excel. One of the biggest was the explosion of dynamic arrays onto the Excel scene this year. Now that brought us some marvelous techniques for creating dynamic spreadsheets. And for my favorite tip of 2020, I would like to mention the sort functions that came as part of that. There were two functions, sort and sort by.
and both of them have their advantages. The sort function is very flexible and we can sort any column of a table or array using a column index number just like the index and vlookup functions request. This makes it great for use with the filter function or with match or count functions or even form controls because of their use of index values. There was also though the sort by function. Now this function allows us to specify or select a table column as the one to sort by, much like you would select a column in a sum or a countifs or an xlookup function. This function is also better at handling multiple sorting levels. So we can sort by two or three criteria. We can even sort an array using a column that is not part of the returned results. And that is pretty cool. And these two sorts functions make it easy now for us to dynamically order
the results in our Excel reports and our models. They are a brilliant addition. I would also like to mention that this year has seen a fantastic growth in Excel meetups. The demand for people to not only learn Excel, but to be able to meet up with others and engage in a community has seen lots of meetup groups get created, but also grow in that time. I run my own Excel meetup group, which is the London Excel meetup, and hopefully...
we will be back in person in London next year. But we have been operating online, much like many other Excel groups around the world, such as Toronto, Saudi Arabia, Berlin, Wellington. There's many of them. So I encourage you, because you're obviously an Excel fan, if you listen to this podcast, to engage in your local community. And you can attend and participate in your local, or even not local, as they're online at the moment, Excel events. So that's it from me, Alan at ComputerGaga.com. I wish you all the best for the rest of the year.
and for your continued Excel adventures in 2021. Hello, everyone. My name is Chandu. I run Chandu.org. And in this podcast, I'm going to share my favorite Excel tip. Now, many of the times when we create charts or graphs in Excel, the purpose of these is to go into a PowerPoint presentation or Word document. Do you know that you can just copy paste the chart into PowerPoint or Word directly? And that will give you an option to have the chart as well as the associated data.
in the final file itself for example let's say you have a presentation that you update every month that has certain graphs now what you could do is at the first month you can just copy paste all the charts into that presentation and then the next month onwards you can directly edit the charts inside powerpoint so you don't have to even go back to excel you can directly go to powerpoint select the chart and then it will show you all the options to both format the chart as well as edit the data when you edit the data it will open a small excel like window within powerpoint where you can update the data points and the graph will be updated This way, you don't need to go back to an Excel file, update the graphs and copy paste them into PowerPoint. The only note I have for you at this point is when you are doing this, keep in mind that Excel will copy all the associated data into PowerPoint. So anything that is sensitive that should not be copied over, it will also get copied. So you want to double check what goes into PowerPoint and or Word documents and then make sure that nothing personal or secretive is available there. So that is my tip. I hope you enjoyed this. I wish you all a very happy Christmas and a new year.
Hello, I'm Celia Alves from Solve and Excel Consulting at solveandexcel.ca. My Excel tip for the year 2020 is about an easy way to copy one Power Query query from one Excel workbook to another workbook. This is what you need to do. Open the two workbooks side by side. On each workbook, open the Queries and Connections pane. To open the Queries and Connections pane, just go to the Data tab and click on the button Queries and Connections, or the button Show Queries if you have an older version of Excel. Now, select the query that you want to copy on the first workbook, right-click on it, and select Copy.
Then go over to the second workbook, right-click on top of the Queries and Connections pane, and choose Paste. Just like that, your query will be available on the second workbook. Not only that, but if the query that you copied was referring to other queries in the first workbook, those queries will also be copied into the second workbook, and the dependencies between those queries are preserved. How about that? The final detail that I wanted to mention is that if you need to work on the M code of a query outside the Excel workbook where the query is, you can copy the query in a similar way into any text editor. This is what I mean.
Right-click on the query in the Queries and Connections pane, then go over to your notepad or another text editor and just paste. Just like that, you will have the M code available in your text file. Also, if the query that you copied was referring to other queries in the first workbook, the M code of those queries will also be brought over to your text editor. And that's it. I hope you found this tip useful. It sure can save you a lot of time if you need to replicate a query that you already built before on a different file. If you need more details about this method, please feel free to check my blog or my YouTube channel.
Thank you so much for listening to My Excel Online podcast and have a wonderful Christmas and a spectacular year of 2021. Hi, this is Brian Hong from MyExcelOnline.com and my best Excel tip for the year 2020 is to never stop. learning so whether it be you would be purchasing a brand new course about excel or reading a good book about excel just don't stop learning because in my case from my personal experience i recall the day when i first dabbled into power query when i started learning about that then it led me to power bi as well and i never looked back you just don't know what new opportunities would lead you right when it comes to the long road of learning and for me i'm the one teaching now about power query and power bi and it's been a fantastic experience okay so for the year 2020 and beyond never stop learning excel Hey everyone, it's Chris Newman coming to you from thespreadsheetguru.com. That's a site where I cover all the latest Excel features and also document creative ways I tackle problems I face at my day job in the corporate world. I love mixing my passion of design and analytics, so if that sounds like something you'd be interested in, feel free to stop on over. As I reflected back on my many uses of Excel this past year, I thought it might be interesting to share how I handle what I call check formulas in my monthly spreadsheet models. I had numerous projects this year where my spreadsheets were receiving data from other files or systems.
My spreadsheets were the final leg of the process, which meant the charts, tables, and dashboards I was creating were going to be presented on and needed to be perfect. Needless to say, I needed to scrutinize that data coming into the spreadsheets like my life depended on it. I know my time's short, so I'll just cover two techniques that I don't see people utilize when they create complex spreadsheet models. First, most people would check every row in a table by creating an additional column and having a formula check each row to see if it's valid. In its simplest form, think of an if statement that yields an okay or an issue found result. The problem with this is you can't typically see if there's a problem unless you look into your cell filters.
What I recommend doing is dedicating a cell at the top of your spreadsheet to perform a count if and look to see if any of your issue flags were generated. This gives you instant feedback on whether you even need to go into your filters and investigate. The second tip would be to create a dedicated checks tab. This would be a tab that you link all your flagged results into. Combining those with a little bit of conditional formatting to turn your flag values red or green will give you instant visual cues on whether there are any issues throughout your entire spreadsheet.
This will prevent you from accidentally missing a checkpoint in your spreadsheet and you will be much more efficient as you review your results. I even found myself including hyperlinks for each flag so I can instantly jump right to the problem within my spreadsheet. Thanks, John, for inviting me to the podcast. I hope to see new faces over at the Spreadsheet Guru blog in 2021. Hi, this is Wyn Hopkins. Check out xlookup.com.au. And my tip for 2020 is beg, steal, borrow, do whatever you can to get on to Office 365. That's where all the good Excel stuff is now coming. xlookup, dynamic arrays, the let function, all sorts.
Please persuade whoever needs persuading at your organization that it's worth upgrading to Office 365. Office 365, remember, gives you desktop as well as online. So some people think that Office 365 is purely online. No, it's the desktop version of Excel as well, and it gets regular monthly updates with all the new features. So if you want to get on board with the new stuff, the filter function, the unique function, all these sorts of good things, then you need Office 365. So that's my tip. Hope you have a better 2021 than 2020. Catch you next time.
This is my fifth year presenting a tip for the My Excel Online podcast, and this year's tip is a good one. My favorite Excel tip for 2020 helps you extend Excel beyond the Microsoft architecture. This year, I started using VBA to send emails through Gmail. It even works with Google's two-step verification. All you have to do is add a reference to the Microsoft CDO library from your VBA project and use it to connect to Gmail's SMTP server at smtp.gmail.com. Enter the right port, and you're good to go. You can fully customize your message subject, body, to and from fields, and you can even add attachments, just like you could if writing a macro to send emails through Outlook.
But instead of sending through Outlook, it actually sends directly from your Gmail account and your Gmail email address. Just Google VBA Gmail and click the link from wellzer.com for a complete tutorial. Hello, my name is Yolanda Cuesta from ExcelContabilidadEthic.com. And my best Excel tip of 2020 is ideas. Not only a tip, but also a new functionality in Microsoft Excel 365 and also in Excel Online.
You can find ideas in the home menu. You have an icon called ideas and it helps you to analyze your data in a simple way. Ideas shows you charts, trends and other analysis. Simply in a spreadsheet with data by clicking in the icon ideas. You can see the suggestions on the right and select your favorite. And that's my Excel tip of 2020.
Hey John, this is Sohail, and we're from Earned and Excel. I hope you and the family are doing well, and congratulations, by the way, on your Excel MVP award. It's well-deserved and long overdue, so good stuff there, mate. Now, the question you asked me is, what's my best Excel tip? Now, I always think in terms of how Excel can improve your career, and today's advice is no exception. You know, the question I...
meditate on a lot is how good do you need to be at Excel? And more recently, I've started to see how Excel has grown in terms of its features and functionality. And I think to myself, how much of these new features and functionality should I spend my time learning? And honestly, as somebody who's steadily worked in the corporate world for two decades, the difference between what I could do in 2010 compared to what I can do now is very little. It's just that organizations are producing more small data. So a lot of these new features and functions are, for my opinion, for my money, they're just not necessary for the average corporate professional. Now, I'm not saying that's the case for everybody else, but I am speaking in terms of people whose bums are on seats and they're sitting in front of a computer and chair.
at reports and analysis and so forth as part of their work. So I always think to myself, you know, why are some people more successful than I? And I've come to learn over the years that it really comes down to the decisions that they make and the actions they take on those decisions. Now, even more so than that, it's about the things they choose to obsess over and what they don't obsess over. And when I obsessed over Excel many years ago, I was getting really good at Excel and it was great. And I wanted to get more and more deeper. And I started learning peripheral things like shell scripting and C-sharp and things like that that would enhance my Excel game. And I quickly found I hit a ceiling. And it wasn't until I stopped obsessing over becoming an Excel expert, I started to go a bit more broader. I first became a better data analyst.
So I was getting my head around querying with SQL and some of the other tools. And then I eventually became a project manager and I learned. project management frameworks like Agile and Waterfall, and eventually I became a lot less of a technical person. And let me be very honest with you, the market for Excel experts is very small, and it's becoming more and more overshadowed by more relevant markets like project management, for example, and data analysis, for example, and many more things that are emerging. And we have to respond to that. Rather than just becoming Excel experts, it's a dangerous path to go down. You need to look at what are the careers that are emerging. You need to look at what technologies companies are using more and more, and make sure that you have a modicum of understanding and skill sets in those, rather than just obsessing about the next feature.
functionality that the Excel team decide to release. And a lot of that is just them releasing it out of fear at the various competitors nipping at the heels when it really shouldn't be the case because they are the £800 gorilla in the market and that's never going to change. So that's my tip. Think very carefully about the new Excel functionality and features you should learn. And I would think broader is going to be a better return for your career. Thanks.
and you can create some templates, then spend some time and create those templates the next time. You don't have to do it from scratch, and you can reuse some templates and save a lot of time. Similarly, if you're working with macros and VBA codes, then you can save those VBA macros or those custom functions that you create in a personal macro workbook, or you can create an Excel add-in outfit so that the next time you need these, this will be right there with you, and you can use it on all the Excel files on your system. This can save you a lot of time because then you won't have to write the code from scratch or look for the code where you used it the last time. Another example could be to learn Power Query and see how you can use it to combine multiple files. You can use Power Query to clean the data, but I use it a lot when I have to combine multiple worksheets or multiple different files, and that ends up saving me a huge amount of time. And finally, learn a couple of useful keyboard shortcuts that you can often use in your work, and that can make you a lot more efficient. So my Excel tip for you for 2020 would be to try and automate your work and be a lot more efficient in using Excel. Hey there, I'm Kat from Miss Excel, and this is my best Excel tip of 2020. The filter function, available on Microsoft 365,
can be used as a lookup tool to pull multiple items associated with the criteria you set instead of just one result like you would get from a lookup function. You can also easily extract the entire row of data associated with your results, making that filter function a great one to check out. Introducing the MyExcel Online Academy, a professional learning system proven to get you to your next Excel level fast, starting in either beginner, intermediate, or advanced courses based on our initial assessment. Each course level teaches and expands upon must-know Excel features like formulas, pivot tables, charts, power query, power pivot, macros, and BBA.
Upon completion of each level, you receive a certificate before progressing to the next course level. And each month, brand new Excel video tutorials and resources are released to keep increasing your skills. You can also access our member-only Excel resources and get support from our Microsoft-certified teachers. If you want to get better at Excel, become more productive and save yourself hours, days, and weeks of time each year, want to stand out from your colleagues by impressing your boss to generate greater opportunities like promotions and a higher salary, then the My Excel Online Academy is for you. So kickstart your personal and professional development and enroll today.
Hey, Boris from Presentation Agency 256 Labs here and the founder of the conference Present to Succeed. So my favorite Excel tip would be something that's related to presenting data and communicating data because we're a presentation agency. So one of my favorite key shortcuts and one of the favorite ones of our customers is related to when you have a line chart. And I would put it this way, a busy line chart, meaning that some of the points overlap themselves. And when you want to control or when you want to select each and every point, it's extremely useful to know that you can click on any point in any line chart and then on a PC...
just hit Ctrl and the right arrow to go to the next point, or Ctrl and the left arrow to go to the previous point. And thus, by clicking Ctrl, right, right, right, you can go through each and every point in the line chart, or Ctrl, left, left, left, go through each and every previous point in the line chart, and thus control them separately. Super useful, super easy to use, and extremely powerful key shortcut. Hello, I'm Christian Galvon from ExcelTurbo.com.br, and this is my best Excel tip of 2020. Unique function. This is one of the dynamic array functions available in the most recent versions of Excel from the Microsoft 365.
With unique, you can list all distinct items of a range or table column. All you need to do is type equal, unique, open parentheses, then select the list of data, enter. You don't need to worry about how many cells you need to count to fill down the formula. The results will automatically spill through the empty cells below the formula. By using the unique function combined with other Excel tools, including the other dynamic array functions, such as filter, sort, and sort by, you can create some of the most interesting solutions in Excel like responsive drop-down menus with data validation, summary sheets, ranking charts, and even more. Follow MyExcelOnline and Excel Turbo on social networks for more tips like this.
Just click on the cell that contains it, then in formula bar, highlight the part you want to evaluate, and press F9 on your keyboard. This will show you the result of that part of the formula, so you can have an inside look of the calculation. This is handy if you got an error, you can check where it happened, or if you just want to understand the steps and calculations of an advanced function or nested functions. Here is a simple example. If you have an average function within an if function, and you highlight the whole average function with the name, arguments, and the brackets, press F9. It will show the average value. Or you can highlight the logical test argument of the if function, and you can see if that is true or false.
When you use this, be careful and please remember, do not press enter to quit the cell because it will overwrite your function with these results and you do not want to do that. Use Ctrl-Z instead to undo this or escape button to exit the cell. Hope you can apply it in your daily work and never stop learning something new in Excel. I wish you happy holidays and good health and success in the coming year. Hi, my name is Georg Lazar from albatexcel.com and my Excel tip of 2020 for you is all about general problem solving in Excel. So what I'm going to share with you is not an Excel formula or a tool per se, it's rather a kind of approach or a mindset.
how to approach problem solving in Excel. Let's assume you are about to develop a calculator, let's say a marketing cost calculator, or a financial model, or a calculation for your next big party, whatever it is. Whenever you don't know how to start, then simply think about the outcome that you expect. This can be a target value, like for example, total expected costs of your next big party. From there, you simply go backwards, start with dummy values or hard-coded values, and start deriving formulas to calculate those afterwards, step by step, backwards. On that way, you will discover which other variables are needed for your calculation model, and magic happens, hopefully.
And you start building your own calculation tool in Excel. Once you start fine-tuning your calculations, you may tend to make things more complex. You may start to merge calculations into nested formulas and so on. Just as a side note, try to keep things simple so others can follow your calculation steps later on. And if all that does not help, then go back to the real basics. Grab a sheet of paper and a pen and try to scribble down what you want to achieve. You may lay out your sheet structure and how those are linked through cell references in your calculations. And that's it about my 2020 Excel tip for you. Stay healthy and enjoy the holiday season. And for all Germans out there,
Frohe Weihnachten und einen guten Rutsch. All the best from Berlin. Cheers, Georg. So let's practice with a data set of penguin measurements, of all things. This is included in the accompanying blog post for this tip, I'll share that. This is a CSV file from the web, so we can actually paste the URL right into the file explorer and import it into Power Query, which is pretty cool. I've got a screenshot of that in the blog post. We aren't going to actually transform this data, but if we tell Excel that we want to, that will bring us into the vaunted Power Query editor. From there, we'll go to a tab on the ribbon that you may not have spent a lot of time with, and that is the View tab. So if you're in that View tab, you're going to see in the middle a data preview group. Go ahead and start clicking on...
some of those radio boxes, and you'll start to see some useful info appear about our Penguin measurements. So you'll get a histogram of your different variables. You'll get a sense of what percent of those values are empty, have errors, and so forth. Now, this data preview is a little misleading because by default, Power Query is only going to profile your data set based on the first 1,000 rows. So to fix this setting, you'll want to navigate to the way, way bottom of your screen. There's a little status bar there that says column profiling based on top 1,000 rows. Change that to the entire data set. In the case of this Penguin demo, we're safe. It only contained 344 records, but it's definitely something to be aware of. Kind of a weird little glitch there, but that's my tip.
I hope it's something that you can get some use out of in 2021. Once again, I want to thank John for everything he does for the Excel community, including putting together this annual podcast. And I would like to thank you for listening. Take care. Okay, so my Excel tip is actually not an Excel tip per se, but it's something that you should all hear. Right now, Instagram is very fertile ground for Microsoft Excel. There's a lot of really cool people trying to do a lot of new things on it. So if you are not following people on Instagram who do Excel, this is the time. And same with...
TikTok. I mean, seriously, there's some really cool stuff being made, so check it out. Of course, this tip came from Jordan Goldmeier, Microsoft Excel MVP. Now, I don't have my new website up, but you can email me, jordan at anarchydata.com. Hi there. This is Caspar Langman from SpreadsheetNote.com, and it's time for my favorite Excel tip of 2020. Have you ever wanted to select all cells in a sheet that contain formulas or were blanks or containing conditional formatting or drop-down lists? I have.
Plenty of times. Instead of picking them manually, just bring up the Go To dialog box and press the special button in the lower left corner. And there you go. You bring up the Go To dialog box with the Control-G shortcut that works both on Windows and Mac. It's easy, it's effective, and it's fun. Well, I don't know about fun, but it's really awesome. You've been listening to Caspar Langman from Spreadsheeter.com, and this was my favorite Excel tip of 2020. Have a good one, everybody. Hey, everybody. It's Paula here from the Excel Club. 2020 is coming to a close, and we're starting 2021 with a lot of uncertainty.
But there is one thing I am certain about, and that is the Excel tip I'm going to share with you today. My best Excel tip for 2020 is to upgrade and get trained in Excel 365. Excel has come a long way in the last few years, and having the last edition means you have more features and functions. Features that will help you become more efficient and will reduce the scope for errors. Excel 365 has a new calculations engine. It's faster, and it thinks in a different way. We have dynamic arrays with a new filter, sort, sort by, and unique functions. We have XLOOKUP, stock history, and the left function. But it's not just all about functions.
Excel 365 has natural language help, rich data types and ideas, all harnessing the power of the cloud. There have been improvements to pivot tables, power pivot and power query. And without Excel 365, you are missing all of this. So that's my Excel tip of the year. I hope next year works out a little better than this year. And no matter what it holds, be safe and upgrade and upskill in Excel. Hi, it's Danielle Stein-Fairhurst from Plum Solutions in Sydney, Australia. Wow, what a year it's been. And my big tip for 2020 is to keep.
learning. So as a trainer, I used to do a lot of face-to-face training and I certainly miss the travel. I used to be able to do that all over the world and I have not been able to do that for a very long time, but we have still able to get together. Excel is basically made to be able to share screens, being able to see each other's screens. I am still able to collaborate with lots of people, being able to look at their screens and even being able to work together on an Excel model is still made possible via Zoom or via Teams.
Since we haven't been able to get together, I know that lots and lots of people have created some fantastic meetups and online conferences. And there's just been so many people that have created some fantastic online content. So there's been some great ways to learn and to continue to improve your Excel skills. Microsoft has brought out a couple of really exciting new features that have come out just in recent months. And there's lots and lots of people who have written about it and blogged about it and created YouTube videos that will help you to learn. So my big tip is to take advantage of perhaps not being able.
to travel or do what you used to be able to do and to make sure that you use this time to keep learning and to keep up to date with all of the fantastic new things that Excel will offer you so that you can continue to improve your Excel skills. And we are on to bigger and better things in 2021. Hello, my friends. This is Avi Singh, Power BI Pro. Thanks, John, for hosting us. I'm going to share something with you which isn't a technical tip, but it could prove really helpful for you in your Power BI journey. Now, I have trained and helped.
thousands of students through my program and probably a lot more through my YouTube channel. And there's one thing that I often hear people struggling with and who are starting off with Power BI or even after they've been around Power BI for a while, and it's a disease and it can be pretty fatal. And the disease is actually not good enoughness. and comparisonitis. They kind of go hand in hand. And usually what happens is that they compare themselves with somebody who they hold in high regard, and they look up to them, could be me, could be John, could be somebody else in the industry, and they go, whoa, man, they know so much, and I know so little. I need to get there. I need to work hard. I need to learn more. And only then will I be ready to help others. But what that does is that keeps them stuck in this, what I call the learning paradox. And the learning paradox is the more you learn,
the less you know. Because the more you learn, you realize more stuff that you don't know. And it never ends and it becomes a battle, a game you cannot win and you stay stuck. So what do you do if there's a game that you cannot win? Well, you got to change the game. And that is my number one advice to anybody who joins a program is to take the focus away from yourself. Stop playing that game. Am I good enough? Am I not? Am I good enough? Am I not? You can never win that game. So stop playing that game. Instead, shift the focus on others. In fact, not others, plural, but
one person. Just think to yourself, is there one person out there in this whole wide world whom you can help? just right now with the skills that you have. And I bet you can. Even if you learned, just discovered Power BI yesterday, guess what? Today, you can help somebody who doesn't yet know about Power BI, who hasn't discovered Power BI. And of course, if you're further on, I'm sure you can find at least one person you can help. And that is your job. That is your mission. If you would like to learn more about this, then search for the Pyramid Theory of Power BI video on my channel. And you'll get a slightly more detailed thought process behind this idea. So that's my tip for you. Hopefully, it helps you in your journey. Until then, follow on.
Hey, guys, it's Greg here at My Excel Online. And I wanted to talk to you briefly about debugging your VBA code. I know many of you are working with VBA and are starting to challenge yourselves a little bit more. And debugging is a big part of kind of getting past those initial errors. And don't be discouraged when you get errors, because even the best VBA programmers end up with errors in their code. And understanding how to identify those and fix those quickly is kind of that next step in your VBA journey.
So difficult over audio, but I want you to look at that debug menu in your VBA editor. Some of those key features that I use every day are breakpoints, the F8 key, which allows you to step into your code line at a time. that F5 key, which will run your code. And those are really key things. Now, if you've got a particular line that gives you an error, I always kind of look to the lines before it. And so I'll put a breakpoint a few lines before and I'll have the code stop. You can hold your mouse over those variables and see what the values are. And oftentimes you've got a variable declared wrong or you're making some manipulation to a variable that's not doing what you want. So I really encourage you to get in there, test out that debug menu.
and add some breakpoints, stop the code, and move it one line at a time and watch those variables and the values and your worksheet alterations happen as they go. Even practicing stepping through code that works already will help you understand all those tools that are there at your disposal. That's all I got. Keep on working on your VBA programming, and we'll talk to you soon. Hey, everyone. It's John here from howtoexcel.org. And my favorite Excel tip for 2020 is the new Lambda function. For me, this is the most exciting new feature to arrive in 2020. And in my opinion, this is now the most powerful function in Excel.
So what makes this the most powerful function in Excel now is that it allows you to build your own custom functions. So it allows you to define inputs for your new function and then define an output calculation based on existing Excel functions. So all those crazy long formulas that you've built in your Excel workbooks over the years, you can now turn into custom functions that you can reuse in your workbooks. So Excel has had the ability for a user to build their own custom functions using VBA coding language. But one of the problems is that those functions aren't available in Excel Online or on the mobile app. So anything that you build with those will never be able to be used in Excel Online. Whereas these Lambda functions are going to be available everywhere. So desktop, mobile, and web. And instead of coding with VBA or some other programming language, the coding is actually using existing Excel functions. So if you can perform a calculation with some Excel formula right now, you can now create a custom function based on that.
So this is one of the must-know features of Excel in 2020 and definitely something you need to check out. Hello, I'm Mark from ExcelOffTheGrid.com and my favourite tip of the year is this. If you're working in Excel and have to keep flipping back between two parts of the same workbook, maybe they're on different tabs or even different parts of the same worksheet, then stop. You don't need to do that. Instead, open up a new window to the same workbook. In the ribbon at the top, click View and then the New Window button. Excel creates a new window to the same workbook. Now this isn't a copy or a duplicate, it's the same workbook. You're just viewing it from a different angle. So any changes you make in one window are automatically reflected in the other window. Now this is great if you have multiple screens because you can show one window on one screen.
and one window on the other. Or even if you've only got one monitor, you can put one view on the left and one view on the right. If you know this tip already, fantastic news. If not, I've just let you into one of the best time savers there is, because there's no need to keep scrolling and flipping between two places in a workbook anymore. Well, that's it for me. I hope you can put this tip to use and start saving some time in 2021.
Now, wasn't that show just awesome? That was some of the best Excel and productivity tips that I've heard. And I'm going to say a big thank you to all the Excel experts who contributed in 2020. Now, once again, if you want to go and learn more about each of these tips, then go on to myexcelonline.com full slash podcast. And in episode number 28, I'm going to put every guest and a link to their website and their Excel tips. So make sure to go on there so you can learn and apply all these great tips that you've learned today.
Now, it's time for my best tip of 2020. Now, this tip here is going to take about 30 minutes to explain. So I'm not going to explain it now. But what I've done is I have created a link for you to sign up to a free webinar where I'm going to teach you the four-step framework to advance your Excel level within 30 days. Now, in this webinar, I'm going to teach you the top three mistakes that everybody makes when using Excel and why they are stopping you from advancing your Excel level.
I'm also going to reveal the number one secret that I used to quickly advance my Excel skills when I was working in the corporate environment. And finally, I'm going to share with you my four-step framework for advancing your Excel level and one that will make you stand out from the crowd and increase your chances of getting a new job, a promotion, or a pay rise. And that's why you are here today is because you want to improve your professional development. Now, this is a free webinar. It's less than one hour long. And all you're going to do is go on to myexcelonline.com.
forward slash advance. So that's MikeSunline.com forward slash advance, and then choose a session in your time. And I'm going to share with you this foster framework to advance your Excel level within 30 days. All right, that wraps it up for another year. I know it hasn't been the best year with COVID and everything happening around the world, but I'm sure in 2021, things can only improve. So keep the faith, be strong, be healthy, be happy, and I will see you next year. But before we go...
I really appreciate it if you go and give a rating and review on this podcast show. If you go over to your favorite platform, whether it is Apple Podcasts, Google Play, Amazon, iHeartRadio, Spotify, Stitcher, whatever you like, and just give an honest review about this podcast episode. And also go and check out our other podcast episodes that we've done in the past. There's a lot of great tips there. So have a Merry Christmas and a Happy New Year.
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.