Linkedin excel: avoiding common mistakes office 365/excel 2022 videos

“

- [Instructor] If you write formulas in Excel that deal with a list that might grow or shrink, using entire column references is going to make sense. It will save you time, you'll have cleaner looking formulas and you won't have to rewrite the formulas as the list grows or shrinks. I've got the sheet called column references, we also have another sheet we'll see in a bit called row references. Here's a worksheet here that includes salary, that we're trying to come up with a tax rate here in column I. As I scroll to the right, you'll see this list over in columns K and L. There's nothing else in those columns, except what we're seeing. Now, we might be using the new function called X lookup, but if you're using that or V lookup, you'll have to refer to these cells. But do we refer to them by selecting them, or do we refer to them by columns? And I'll show you the variation on how this would work. X lookup is what I'll…

“

- [Instructor] If you work with Excel formulas, you need to be familiar with what are called error checking rules. And you won't typically see the box that we're seeing on the screen here to the right, but that's a list of them. We first want to show you how you might encounter a rule. On this worksheet, you'll see some green triangles in some of the cells. I'm about to click on cell E2. As I do click, what do we see to the left? I slide leftward, and there's a drop arrow, first of all, but even before that, a pop up indicator. This cell contains a formula that is not locked to protect it from being changed inadvertently. If you're familiar with that feature, I think you know that this isn't exactly an error, but it is a worthwhile bit of information. If others are using this, they can change the formula here. We've got the indicator to the left, click the exclamation point, we see other potential errors that could have…

“

- [Instructor] We're looking at a worksheet called Protect Sheet in our chapter two file. It's over 700 rows of HR type data and multiple people have access to this file. But I'm in charge and I don't want others to make changes where there shouldn't be changes. Column E has formulas. I'll double click on cell E2, we see a formula there. Now if data has to be adjusted in column D, the hire date, I do want the formula to do its job and do it correctly but I don't want somebody typing here. Imagine where someone's looking at a sheet of paper and says this hire date should have been 2015, so I'll just type a four here 'cause that's what it will be if this is 2015. Can I type a four here right now? I can and what am I about to do? To wipe out the formula. I'm not going to do that, I'll press escape. I don't want anybody altering data in column E. I want to allow changes in column D. I certainly don't want them…

“

- [Instructor] Excel has any number of different techniques for reducing the amount of time required for data entry. I've got a sheet of paper in front of me with some city names here, also some sales, eventually we'll put this on a different worksheet. It turns out that the city names are recurring. I need to put in Boston in cells A2 and A3, also in A6, A7, A10 and we'll have other repeating cities as well. Rather than typing this once and copying it multiples times, we can select the cells ahead of time and simply type the entry once. I've highlighted these two cells, I've let go of the left mouse button, with the Control key now, I'll highlight cells A6 and A7, once again let go of that left mouse button, Control key is held down, I'll click on cell A10. And as I type Boston, it looks as if this entry is only going into that cell, but I won't press enter, I'll press Control + Enter. Same entry goes in all of those. We…

“

- [Instructor] You can cut down on a lot of typing by using a feature called auto correct, that's the name of the worksheet we're viewing here on our chapter one file. And there's some other techniques as well in this movie that we're going to show you that will reduce that data entry time. I'm about to type company for a column heading, but I mistype it, C-O-M-A-N-Y, I press enter, and Excel automatically corrects the spelling. And, built in are quite a few other corrections of this type. Let's take a look at the file tab in the ribbon and then go down to options, under proofing, you'll see a feature called auto correct. And what we see here is a huge list of entries. Now some of them are for special symbols, but if you type A-B-O-T-U, you probably meant to spell about, and Excel will automatically correct that. And here's a huge list of entries. Those are automatic, but a different way to use this is to say I type…

“

- [Dennis] Whether you're new to Excel or use it regularly, avoiding mistakes can reduce the time it takes to fix them. In this course, I'll show you tips and techniques to minimize the possibility of errors as you work with Excel. I'll show you how to use AutoCorrect and AutoFill to make data entry faster and less error-prone, and accelerate date-time entries. Then, I'll show you how to use worksheet and workbook protection schemes, as well as powerful data validation rules to control the range of data being entered. And set up pick lists so they'll reduce errors and save time. I'll also show you how to write formulas more efficiently and use Error Checking features. I'm Dennis Taylor. I've been working with Excel for years. And we've got a lot to cover, so let's get started.