Dennis demonstrates how to use Row/Column references in formulas to reduce complexity and enhance accuracy.
- [Instructor] When writing certain formulas in Excel…you can save time, increase accuracy…and eliminate certain errors by using…an entire column reference and in some cases,…although rarely, entire row references.…In this worksheet here we're about to come up with a…tax rate in column I based on this table of data over here.…Now, you don't necessarily have to know a whole lot…about the vlookup function, that's one we're about to use…here, but if we were using this,…in the standard kind of way that most people do,…we'd be looking at this value in cell G2…and then highlighting these cells.…
If we know we're going to be copying this formula…down the column, we will want to make sure…that's an absolute reference and press the function key F4,…comma and then put in the number two to mean…we will get our answers out of the second column.…So as we press Enter, we will get an answer.…So, it's preformatted, we see 2.5%.…We can copy it down the column, provided we've…got the absolute reference.…But as we look at formulas and later we make changes…
Dennis begins with the basics—how to display data so that errors can be easily spotted. Next, he offers handy tips to ensure data is entered correctly the first time, using the AutoFill feature and using AutoCorrect shortcut codes for frequently used entries. Dennis provides easy ways to validate your data, which is particularly helpful when multiple team members are contributing to the same spreadsheet. This includes restricting the data that can appear in a spreadsheet by setting value limits, pre-populating data with drop-down lists, and other methods. He also shows how to avoid mistakes in formulas, how to hide data that doesn't need to be seen, and how to use workbook protection to prevent errors, and more.
- Streamlining data entry steps
- Using Excel shortcuts and automation features
- Protecting worksheets and workbooks
- Validating data
- Basing entries on formulas
- Detecting errors in formulas
- Using Range Names
- Finding mistakes in large, complex spreadsheets