Data validation is a feature that allows you to control the values in a cell. It is useful when providing templates to numerous users, when other cells refer to the value in a certain cell, and when you want to provide an easy way to enter data in a cell. This feature can automatically create a drop down box in the cell so the user can select a value from the list. For example, lets say that you want to restrict the value of a cell to either "Red" or "Blue". Select the cell, and choose "Validation" from the "Data" menu. Choose "List" from the "Allow" box. Enter "Red, Blue" in the "Source" box. Press OK. Now when you select the cell, notice that a drop down box appears, and you can choose one of the two values. You can also provide Input messages and Error messages which are very helpful when distributing your templates to numerous users.

Pivot tables are by far the most powerful data manager in Excel. They allow the user to organize raw data into any desired format, making it easy to convert the data into information you can use. They are perfect for interactive reports and times when you want to sort and view data in various ways. To create a pivot table, simply highlight your contiguous data range, and select "Pivot table report" from the "Data" menu. Drag the gray header boxes into the section of the report you want. When you click OK, the pivot table is created. You can drag the gray header boxes into other positions without going back to the Pivot table wizard. You can also double click on any value and Excel will pull the raw data that supports the value and paste it into a new worksheet. If you have multiple row sources, you can double click to expand or hide detail.

External data ranges are important when you want to use large amounts of data from other sources, including other Excel workbooks, databases or the internet. Once the external data range is created, it can be refreshed and if the information in the source has changed, the updated data will be brought into your workbook. Lets say you have a database with sales information. You can set up an external data range in your Excel workbook to pull down the sales rep and sales figures. Once the data is in Excel, you can perform calculations as necessary. Another example is setting up an external data range to query the internet and retrieve stock quotes. Once the price is in Excel, you can calculate your gain or loss based on the number of shares you own.

Subtotals are extremely useful when designing spreadsheets that require multiple totals on the same data. The "Subtotal()" function is much more flexible and useful than the "Sum()" function. The subtotal function can sum, count, average, and find the min or max of a selected data range. The nice attribute of the subtotal function is that it will not include in the total a cell that contains the subtotal function. For example,
Cell A1: 1
Cell A2: 4
Cell A3: 5 = subtotal(A1:A2)
Cell A4: 7
Cell A5: 12 = subtotal(A1:A4)
You see that the cell A5 refers to the entire range of data, but does not double count cell A3. This is convenient because it makes your formulas easier to construct and read.

Auto filters are a nice way to only show a portion of data that meet a certain criteria. It has the same effect as manually hiding selected rows. Lets say you had a table with 3 columns, sales rep name, region, and sales dollars. If you insert auto filters by selecting Autofilter from the Data/filter menu, Excel will create drop down boxes on each header. Thus, you could click the region header, and a drop down would allow you to specifiy which region to show. It would hide all rows that are not in the selected region. Additionally, if you wanted to total the sales dollars for all sales reps shown, you could use the "subtotal()" function as discussed above and the function will only include rows that are shown. The "sum()" function would include all rows, both hidden and unhidden, in the total.

Named ranges are the secret to linking workbooks and formula reference. Named ranges allow you to identify a cell or range of cells to refer to by name like "MyCell" instead of by absolute cell names like "A1". This is especially nice when linking workbooks. For example, lets say you have a value in cell B2 of the Source workbook that you want to use in a Destination workbook. First, you name cell B2, "MyValue" by selecting Insert, Name, Define. Now in your destination workbook, refer to "MyValue" instead of "B2". That way, you can change the structure of your Source workbook, like inserting a row above row 2, your value will move to row 3, thus occupying cell B3 and the Destination workbook can correctly find the value because it looks for "MyValue" and not cell B2. Besides using named ranges when linking workbooks, they are nice in formulas because they make your formulas easier to understand. For example, which is easier to understand, "=$A$1*C5" or "Commission_rate * January_sales"?

In my opinion, you will never become a master of Excel until you use keyboard shortcuts. This is because in my mind, a master performs tasks the most efficient way possible. Keyboard shortcuts are the most efficient way to interact with Excel. I would not recommend memorizing all of the keyboard shortcuts because there are too many. I would however, recommend the following:

1. Perform all menu commands by using the Alt key. To select a menu without using the mouse, simple hold down the Alt key and press the letter on the keyboard that is underlined in the menu name. This will pull down the menu. Again press the letter of the underlined letter for the desired menu command. You will find that you will quickly memorize frequently used menu commands and that your speed will dramatically improve.

2. Learn how to navigate within worksheets using keyboard shortcuts. The shortcuts that I like are: Control key navigation: As you know, when you press any of the arrow keys, the active cell moves one cell. For example, by pressing the right arrow, the active cell moves one cell to the right. Now, you can expand on this concept dramatically. When you hold down the control key and select any of the arrow keys, you will be moved over to the next cell that contains data. For example, if you are on cell A1, and there is no data in B1, C1 or D1, and you do have data in cell E1. By holding down the control key and pressing the right arrow, your active cell now becomes E1. This is an extremely fast way of moving around in worksheets. Also, if your active cell is currently in a range containing data and you hold down the control key and press an arrow key, your active cell will move to the last cell that has data. Shift key selecting: By holding down the shift key when pressing an arrow key, you will expand your selected range. For example, if you are in cell A1, and you hold down the shift key and press the right arrow, you will have a range selected from A1:B1. Now any commands you perform will happen to both cells. You can combine the shift key and control key to quickly move around and select cells within your worksheets. Quickly move up or down in a worksheet by using the Page Up and Page Down keys.

3. Learn how to edit text within cells using keyboard shortcuts. When you select a cell, you can press the "F2" function key and edit the formula. While you are editing the formula, you can press the right or left arrow key to move your cursor one character to the right or left. Now, if you hold the control key down and press the right or left arrow key, your cursor will move one word to the right or left. If you hold down the shift key while moving, you will extend your selected characters. To delete a character to the left of your cursor, use the backspace key. To delete a character to the right of your cursor, use the "Delete" key.

4. Learn how to move to a different worksheet within a workbook using keyboard shortcuts. If you are in a workbook with multiple worksheets, move one worksheet to the right by holding down the control key and pressing the page down key. You can move one worksheet to the left by holding down the control key and pressing the page up key.

In order to practice, force yourself to perform everything without the mouse. This will feel very akward at first, and you will have a strong desire to reach for the mouse. Resist the urge, and ask Cippit (hit the F1 key) for some keyboard shortcuts.

Hyperlinks are the key to creating a great user interface and to providing an easy way to open other workbooks. A hyperlink appears as blue underlined text (similar to internet hyperlinks) and when selected, a file is opened. Hyperlinks can be used to open other Excel workbooks, or other files like Microsoft Word or web pages (your computer will open the necessary application). Hyperlinks are ideal to use when you need to distribute information over a network. Using Excel hyperlinks, you set up an index.xls page that has the look and feel of an internet web page. The user can point and click to the reports and information that is needed. To insert a hyperlink, select "Hyperlink" from the "Insert" menu.

The "VLookup()" formula is nice to use when you need to pull a specific piece of information from a worksheet. The formula "=vlookup(MyValue, SourceData, 2)" tells Excel to look at the source data, find a row that is equal to MyValue, and return the value of the cell to the right of the matching data. This works well with data validation.

Conditional formatting tells Excel to format a cell depending on what the value of the cell is. For example, if a cell contains the value "OK", make the color of the text Green. If the value of the cell is "Error", make the color of the text Red and bold the font. Simply select "Conditional formatting" from the "Format" menu.