Formulas are used to perform calculations that are required to facilitate the understanding of data.A formula begins with an equal to (=) sign and is followed by syntax that lets you do the required calculations.In Part 2 of this series we’ll look at Excel lists, drop downs and data validation.These are very important areas of Excel that you should master if you want to take your Excel skills to the next level.
I’ll also show you a trick for quickly defining multiple Named Ranges very quickly, and explain how Data Validation in Excel can be easily bypassed – and what you can do about it.
Launch Excel 2010, open a datasheet on which you want to apply data validation rules.
For instance: In grading data sheet, we need to apply rule that marks must be between 0-100, and in an event of any incompatible input value, errors dialog appears.
On that worksheet, make a list (with a heading) of the values you want in your dropdown: You may wind up with multiple lists on this sheet. Then, for built-in documentation purposes, give the table a name by clicking on Design under Table Tools and entering a name: I like to prepend these tables with some sort of consistent prefix – “tbl_,” “lookup_,” or even simply “t_.” That way , if I use a lot of named ranges, all of my lookup tables will show up in one group in the Name Manager.
Ultimately, we want the dropdown list to be “the first column in this (simple, 1-column) table.” But, the specific syntax for referencing table components can get a little confusing, so we can cheat to figure out exactly how to reference the cells.