Skip to content
026: The Best Microsoft Excel Tips & Tricks in 2019! episode artwork

Dec 19, 2019 30 min

026: The Best Microsoft Excel Tips & Tricks in 2019!

from Learn Microsoft Excel with MyExcelOnline

In this Christmas Special podcast episode, we round up the best Microsoft Excel experts & MVPs from around the world to share their best Microsoft Excel tips & tricks in 2019! These Microsoft Excel tips & tricks are the best that we have received to date and will make you a better and more efficient Excel user. What is your best Excel tip & trick in 2019? You can share it with us at the bottom of this page in the comments area, we would love to hear it! Get all the links to these Excel tips by clicking in the show notes here!

Episode details come from the podcast's RSS feed.

Embed this episode

Ready to play

026: The Best Microsoft Excel Tips & Tricks in 2019!

0:00 30:52
of matches

Transcript Auto-generated, may contain errors

This is the MyExcel Online Podcast, episode number 26, 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 Mikoludis. G'day guys and girls, and it's John Mikalutis here from MyExcelOnline.com with an awesome special podcast that we have for you. It is the best Excel tips of 2019, and we have gathered the most knowledgeable Excel experts and MVPs in the world to give you their best Excel tips of 2019. Now, this show here, we do.

each and every year at the end of each year. And you can go onto myxcelonline.com forward slash podcast and have a look at the previous shows to get more of these awesome Excel tips. These Excel tips that we've gathered today are probably the best that we have received. And I'm sure that you're gonna love and you're gonna apply to your work straight away. Now, we have show notes to every Excel tip that is given today. So all you need to do is go onto myxcelonline.com forward slash podcast and...

Click on episode number 26. It'll bring up the show notes. And in there, you can have a look at all our guests and all the links to their Excel tips in there. At the end of this podcast show, I have my Excel tip. So you're going to have to wait till the end of the show. And I'm going to give you my best Excel tip of 2019. And it is one that you're going to love. Now let's get into it with Excel tip number one.

Hello, my name is Jeff with Excel University, and my best tip isn't an Excel feature, function, or keyboard shortcut. My best tip is more of a mindset. It's a way of thinking. And in 2020, let's stop wasting time. And wasting time just means I'm spending more time than is necessary. And we Excel users, we do this without even realizing it. We do things manually that could be automated. We spend more time than is necessary. Once we figure out the right Excel features, functions, and techniques, we can really use Excel to streamline our work. And so what I would recommend is this. Ask yourself every time you do a manual task.

ask yourself, how can I get Excel to do this for me? And if you'd like to get started with this idea, I have a great free blog post series called Stop Wasting Time. So check it out and that'll get you pointed in the right direction. All right, hey, thanks and have a great year. Hey, this is Bill Challen from Mr. Excel. My favorite Excel tip of Excel 2019 is an awesome tip that you're never going to find. You have to be in Office 365 on the Home tab. Go over there on the far right-hand side to ideas, open ideas. And for those of you who are Office insiders, a brand new question at the top that says, ask me a question about your data. Rather than just relying on ideas to try and figure out what's interesting about your data, when you ask specific questions, they're going to generate a pivot table or a pivot chart to give you the answer. It uses natural language query, the thing that the folks in Power BI have had for a while, but now it came to Excel. Unfortunately, it came to a spot where no one will ever think to look.

Hello, this is Chandu from Chandu.org. Thanks for listening to my Excel Online podcast. I have some relationship advice for you. Well, it's not the relationship advice like you would expect in a magazine column, but this is about relationship advice for your data. Whenever you have multiple tables in your workbook, you can connect them with a table relationship and then use them in pivot tables to create multi-table pivot reports and pivot charts.

This feature has been around for more than six years now but it surprises me how few people are aware of this and how they use it. So I want you to walk away from 2019 with that tip in mind. You can collect tables and mix the data from multiple tables to create a beautiful powerful reports and analysis with pivot tables and pivot charts. Give it a try and if you have already been using once you create a relationship and correct tables you can also use the mythical data model feature of excel and with a data model you can essentially use power pivot dax language to write measures and calculate pretty much anything so your pivot tables are no longer simple pivot tables but they will be power pivot tables and there is nothing yet that you need to do to enable this feature all you have to do is link the tables and then you can use all the power pivot features so there you go i hope you enjoyed this i wish you a very happy holiday season and happy new year 2020 talk to you again some other time bye Hi, my name is Minda from my online training hub. My favorite tip for 2019 is that Excel will accept any number, positive or negative, other than zero, as an alternative for the boolean true value. On the other hand, the boolean false value will only accept zero as a numeric equivalent. For example, the following if formula equals if open parentheses minus 100 comma true comma false close parentheses will return true. This means that we can use expressions in the first argument of the if function that evaluate to any number as long as it's not zero and it will return the value of true argument from the formula.

Now this is also the case for conditional formatting and other Excel functions and tools that take Boolean, true and false values. I find this hack super useful for conditional formatting formulas. I hope you find some uses for it too. All the best for the rest of 2019 and I hope you have an excellent 2020. Hi, this is Kasper from Spreadsheeto.com, and this is my favorite Excel tip of 2019. Actually, this is not directly an Excel tip, and it's not even from 2019. This is the best productivity tip of all time across all software, but it's especially useful for working with spreadsheets. The tip is get a second monitor. If you have one monitor now, you'll get a 20 to 30% productivity boost from getting one extra monitor. Isn't that crazy?

Without lifting a finger, you can get 20% to 30% more done every day. Excel goes really well with two monitors because from the View tab of the ribbon, there's a button called New Window. Click it. Now you have two windows of the same Excel file. Drag one window to one monitor and the other window to the other monitor. You can now view different sheets at the same time in different monitors. And you can create formulas from one sheet to another sheet much easier than before. I hope you enjoy my Excel tip of 2019. Happy Holidays from Spreadsheeto.com and have a wonderful new year.

Hello, my name is Alan Murray from ComputerGaga.com and my Excel tip for 2019 is the filter function. Microsoft announced last month, November 2019, that the rollout to Office 365 subscriptions for dynamic arrays has begun. And the filter function is the cream of the crop of the dynamic array functions that are being released. So one of the most popular tools in Excel is to filter. So having a formula that can perform that job is surely going to be beneficial and pretty amazing to how we use Excel. So just think for a moment, why are you filtering in Excel? Well, you're filtering to find something.

Or maybe you're going to reduce the size of your list and then select some values and look at the totals in the bottom corner, the count, the average, the sum. Maybe you're finding the most recent value of something. Well, these are things that a filter function can now do for you, but they're going to be automated. So we can provide multiple criteria to this function and an or logic, and then we can embed it inside other functions, which I think is where the real strength is going to come. So when you think about a sum if function, summing on criteria, well, a filter is your criteria.

It's reducing the size of that list, bringing the range, and we can embed that function inside some average or even large, small, take your pick. It can do what vlookup does. It can do what count if does. It can even do what some product does. So dare I say it, I think this function will replace some product as my favorite function when it's released. It really is a game changer. So if you're not familiar with this function yet, go and Google, find some articles, look it up, ready for when it comes out so that you can hit the ground running. So that is my Excel tip.

All the best for 2020, everyone. Hey there, this is John Alcampura from Excel Campus, and I want to share two of my favorite tips from 2019. The first is the new filter function, which is part of the new dynamic array formulas. There are a lot of uses for the filter function, but essentially it allows you to do a lookup based on one or more criteria and then return multiple results. So if you've ever tried to do this with a VLOOKUP to return multiple matches, it can obviously be very challenging and you have to use a very ugly array formula, but the filter function makes this very easy, and it's also now generally available for Office 365 subscribers. Now, if you don't have Office 365 yet, then I do have another tip for you, which is a new keyboard shortcut I learned to read.

for filtering pivot tables. So let's say you have a pivot table with a list of items in the rows area and you want to filter out or exclude an item like blank cells. You simply just select a cell or cells that contain the items you want to filter out. Then press control minus. Just hold down the control key and press the minus key. And this will exclude the selected items from the filter. Just filter them out. It's an awesome little shortcut. I'll be using it all the time. So to learn more about these tips, you can go to excelcampus.com slash DA. And that's for an article on the new dynamic arrays, including that filter function. And then you can also go to excelcampus.com slash 19.

the number 19 for the keyboard shortcut. There I have a video and an article with 19 advanced Excel shortcuts, and I'm sure you'll learn a few new ones there. So thanks again. I hope everyone has a Merry Christmas and Happy New Year. Hi, this is Danielle Stein, Fairhurst from plumsolutions.com.au in Sydney, Australia. My big Excel tip for 2019 is to keep things simple. If you're listening to this podcast, you're probably into Excel and you're probably very good at Excel. And that means that you can create formulas that are really long and complicated. But my recommendation would be to try not to create long and complex formulas. So I am a financial modeler. And what I often find is that I have to take apart other people's models that have been created in Excel. And some people that are very good at Excel have.

created formulas that are so difficult to follow and so difficult to understand that their colleagues or somebody else just can't understand what the formulas are doing. I do find that sometimes people try to show off as to how good their Excel skills are. But you have to remember that when you're building a financial model, you're not building it for yourself. You're building it for someone else to use. And for somebody else to use it, they're not going to trust the model unless they can understand and they can follow the formulas and see what the model's doing or how the formulas have been created. So make sure that you build the model in such a way that other people can follow.

other people can understand. So rather than creating something that's really long, complicated, create something that's simple, straightforward, clear and easy to audit, easy to validate, easy to check and easy to understand. And that's really difficult. It's much easier to create something that's complicated and it's harder to create something that's functional and yet simple. So we need to weigh up between functionality and simplicity. We need the formula to do what you need it to do, but be simple and clear for someone else to understand. So my big tip for Excel for 2019 is to keep things simple. So have a great year.

Hi, I'm Brian Hong. I'm an Excel book author and contributor at MyExcelOnline.com. This is my favorite Excel tip of 2019, which is using the XLOOKUP function. It's because the XLOOKUP function performs lookups vertically or horizontally. I love the flexibility of it, and it is way easier to use when compared to its older siblings like VLOOKUP and HLOOKUP. It is mind-blowing on what it can do. It can return values from more than one column. It can look up data from the right or left of lookup values. It can default to an exact match, and so much more. XLOOKUP is definitely my top pick when it comes to learning Excel in 2019, and I'm sure a lot will agree.

What is a VLOOKUP formula? Powerful liquid solution? Hmm. What does VBA stand for? Very big application? Hmm. What do you believe is your Excel level? Advanced? Hmm. Introducing the MyExcel Online Academy, a professional learning system proving 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 VBA.

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.

Oz Du Soleil here from the YouTube channel Excel on Fire. Thanks to John McAludes for asking me to contribute a tip. And here is my tip. Think beyond the tips. Think about the people you serve who want their paychecks right, their inventory, and their performance evaluations right. They don't care if you use DAX, pivot tables, Power Query, or whatever. And another Excel expert will always have something different, something better as a solution. But when you're asked to do something with Excel,

You are doing it in service of somebody else who has to live with those consequences and is not another Excel expert. So yeah, use tips. Always learn to use Excel better, but primarily be the hero to those people who have to live with what you do with Excel. Thanks again and happy holidays. Hi, I'm Ryan Wells from wellsr.com. My favorite Excel tip for 2019 is a VBA tip.

This year, I learned how to create custom button labels for message boxes in VBA. By interrupting the process of creating the dialog box, you can change the labels before it's activated in Windows. This process is called hooking. I use hooking for a lot of my advanced user forms, but I never thought about using it to modify the button text on a message box. It's a great trick. It means you're no longer restricted to the seven or so default button labels. You can display whatever you want, and it's a whole lot easier than creating a custom user form from scratch. Just Google custom message box buttons and click the link from wellzer.com for more details.

My top tip for the year is this. Use the F2 key. After you've selected any cell in the Excel window, you can press F2 to toggle in and out of edit mode. It operates like double-clicking on a cell so you can edit the contents. Now, this in itself might be useful, but we're so used to double-clicking that it probably serves little benefit. But have you ever felt the frustration when entering or editing a formula inside a named range, conditional format, chart source, or data validation? That's where F2 becomes really useful. Because in that scenario, if you use the left or right arrow keys in the box,

It doesn't move the cursor left and right as you'd expect it to. Instead, it moves the cell on the worksheet behind the dialog box and changes the formula in the box to include the reference to the selected cell. How annoying is that? But wait, save your frustration. Instead, click on the formula in the dialog box and press F2. In the bottom left of the Excel window, the status will change to Edit. You're now in Edit mode, which means you can move the cursor within the formula box just by using the arrow keys. Press F2 again to leave Edit mode and the functionality will revert back to how it was before.

Such a simple thing, but it saves so much frustration. That's all from me. Have a great 2020. Sahil here. Let's do some walking wisdom. So today I want to tell you why keyboard shortcuts in Excel are good for your job, but they're bad for your career. Let me repeat that again. Keyboard shortcuts are good for your job.

but bad for your career. Now, what do I mean by this? You have a certain workflow. You come in, you do some reporting, you do some analysis, you do some work. You need to do a lot of copying and pasting. And keyboard shortcuts can speed that up. They can really speed that up. And there's all kinds of different keyboard shortcuts. There's hundreds of keyboard shortcuts. And they can speed things up for you. But what I persistently see in professionals is that by speeding up their work with keyboard shortcuts, it satisfies them because they think, oh, I can get through this in half the time. The problem is, is that...

You'll find yourself year after year after year just doing the same thing and you're doing it in half the time. Well, how about thinking a little bit bigger? How about thinking of doing it in a fraction of the time? So I never really bothered too much with keyboard shortcuts. I focused my attention on automation. And when I got my skills around VBA and macros, I wasn't able to save half the time. I was able to save 99% of the time. I was able to save a huge amount. Something that would take me an hour would get done in about 10 seconds. Really, what happens, it blinkers a lot of questions because I think there's a false economy there. You're thinking, oh, I'm getting things done quickly because I've got keyboard shortcuts. But actually, in the grand scheme of things...

You're not. If you took a step back and sure, use the keyboard shortcuts. But what you really want to do is to learn some VBA and Excel skills, advanced skills. You want to learn how to use macros. You want to learn how to do VBA scripting. And those things are going to automate your processes. Those are going to boil your work down to one click. And you're going to be saving hours upon hours. I mean, there are situations I've worked in on projects where I've managed to save up to a week.

A week, a month, just through automation. No amount of keyboard shortcuts are going to save that much labor. And the idea is that what they do is that they remove the mechanical work. Whereas keyboard shortcuts just make you better at being a manual laborer. You're just faster. You've just got bigger muscles. Imagine you're shoveling coal or you're digging up ground. You're just better at doing that. But you don't want to be that. You want to be the site manager. You want to be the foreman. You want to be the person who's directing 100 or so workers and laborers and so forth. So look beyond keyboard shortcuts. The big win is in VBA. It's in the ability to automate your work.

Hello, this is Randy, the founder of Excel for Freelancers, and I'm so happy to share my best tips of 2019. So thanks so much for the opportunity, John. One of the biggest challenges in Excel has been with the ability to share and sync your Excel data with anyone else in the world. While this newest Excel versions have this feature, it's only available to those people who have the newest version. Today, I'm going to share a tip with you that will allow you to share and sync your Excel data with anyone in the world using any version of Excel with just a shared folder. The secret lies in just a bit of VBA code along with the shared folder capabilities.

First, you'll want to set up your shared folder such as Dropbox, Google Drive, or Microsoft OneDrive, and make sure to create one shared folder with all the people that you want to share the data with. Within this folder, each user will have their own shared folder based on their name. Secondly, on a sheet or sheets you want to share the data and sync, write just a few lines of code on a change event that creates a small text file that includes the value of that change cell. The name of this text file will be based on both the sheet name and the cell address. Another small piece of code will then read any text files with changes.

by other users within their own shared folder and update the local workbook. Next, in a hidden sheet, create a list of names you want to share the file with. Once this is complete, you can then duplicate this workbook and give each person their own copy of the Excel workbook. Now, each time a user makes a change, they will be saved in all the other users' folders. The application will then be read by any changes by other users using these text files in their folder and apply those changes to their own workbook. This allows for real-time syncing and sharing of data in any workbook using any version of Excel, anyone around the world.

I have a video describing this exact process on YouTube and a free download. So just check out my channel, Excel for freelancers and do a search for share and sync. I've got a video describing every part of the process. Thank you so much. I hope you've enjoyed this tip for 2019 and happy holidays. Hi, this is Wynne Hopkins from accessanalytic.com.au or you can find us at xlookup.rocks and my Excel tip for 2019.

isn't to do with Excel itself, but it's to do with a couple of websites. One is Excel User Voice. This is where you go to get your ideas heard, your suggestions about how to improve Excel. You can put your own suggestions there. You can go and vote for other people's ideas and get new features and enhanced improvements to Excel implemented. The Excel team are listening. So go to Excel User Voice. Just search it. You'll find it. The other one is Excel Tech Community. If you want help with Excel questions, Google Excel Tech Community and you'll find it.

post your questions, you'll get somebody answering, and it's a really helpful collaborative environment. So check it out. That's Excel User Voice and Excel Tech Community. Hello all, it's George from georgejmount.com, and I want to focus on a feature that is really awesome, but we often take for granted, and that's the status bar. So if you've ever hovered over a few cells to get something like the average or the sum, there's this tiny bar way at the bottom of your workbook, and this is called the status bar. So take a look what happens when you go down to that bar and right-click. You're going to get a pretty lengthy menu called the customized status bar menu. This has a lot of options to customize that bar. There are some ways to get additional data analysis, such as the min and the max and the count, just through this menu.

But what you can also do, and I wrote a post about this, which I'll link to in the comments, is to customize this even further, including things like standard deviation, skewness, and kurtosis if you want to do more advanced data analysis. So this ability to just hover over some cells and already get started with data analysis is what makes Excel so awesome. And even a simple tool like this, like the status bar, has all these features and possibilities just waiting to be explored. So thanks, everybody, for listening. I hope you're ready for 2020 with all these new Excel tips and tricks. Thank you, John, for hosting. And here's to the new year to good friends and good spreadsheets.

This is Jordan Goldmeier with Excel TV. I have a tip for you. It really is two tips. The first tip is, even though you've been doing this for all these years, at least I have, it's a tip to myself, there's always something you learn, something you forget. So here is the genesis of this tip. Someone asked on one of these Facebook forums, how do I take a word in NBBA? I'm going to repeat it a certain amount of times. It was for a business project they were working on. So, I said, look, there's two different ways to do it. One way is you could do a for loop. You would do for i equals one to n amount of times, and then you would take that string, so whatever your result is, result equals result,

and you can catenate whatever your repeating item is. So you could do that three times. So I said that was one way to do it. Another way to do it is I said, well, you can use application.worksheetfunction.rept. So that's the repeat function. And you would access it via VBA using application.worksheetfunction. So I said, oh, those are the two ways to do it. But someone pointed out, actually, there's a third way. There is a third way. The REPT function in Excel, like an Excel function in VBA, is string. So it's string, open parenthesis. First parameter is your word. Second parameter is how many times you want to repeat it.

close parenthesis, exactly like REPT. I totally forgot about that. If I ever knew it at all, I think it's poorly named, but there you go. Three different ways to do the same thing. This is Jordan Goldmeier with Excel TV. Thanks everyone and have a great new year. And finally, this is John Mikalutas with my best Excel tip of 2019. And it is one that is happening in April, 2020 in London, England. It is the Global Excel Summit. Now, this is the first summit where a lot of Excel experts get together to share.

their Excel knowledge to the rest of the world. And it is one that most of our guests in our podcast are going to be there and they're going to be teaching a lot of their Excel tips and knowledge. And also I'm going to be there as well. And it is one that you should definitely check out because it is one place where all the Excel experts are going to be together and you can ask lots of questions and get a lot of Excel knowledge from all these experts. And it's a great place to meet your Excel idols and also your peers as well. So this is happening in...

London on the 21st and the 22nd of April, 2020. To check out the details, go on globalexcelsummit.com. And you can have a look there. There's going to be masterclasses from various Excel MVPs. So if you want to meet all these awesome Excel experts that were on the show today, plus meet me in person, then go on globalexcelsummit.com and buy your tickets. And I can't wait to meet each and every one of you in person.

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

When was this Learn Microsoft Excel with MyExcelOnline episode published?

This episode was published on December 19, 2019.

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.