Dennis demonstrates how to use Range Names for more readable formulas and better worksheet documentation.
- [Instructor] You can save time, provide documentation,…as well as reduce errors in formulas,…if you use what's called a range name.…Now in this worksheet called range names,…there's a formula in cell I2, I'll double click it.…It's simply using the data in column G and column K…and nothing wrong with those addresses,…and by the way if I want to copy this down the column…and make sure it works properly,…I need to make sure that K1 is an absolute address…so I click behind the one here press the function key F4…that's needed if we want to copy this formula…down the column and I'll do it by way of double clicking…so each, one of those refers to that cell properly.…
Now, if that cell had a name,…this formula might look a little clearer.…You can name a single cell or a whole range of cells,…and for a variety of reasons.…How do we give a cell a name?…Couple different ways,…One way simply click the cell…in the case of a range, highlight the range…and then to the left of the formula bar,…there's a white panel that's called the name box,…
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