Of all the hundreds of formulas & thousands of features in Excel, INDEX() would rank somewhere in the top 5 for me. It is a versatile, powerful, simple & smart formula. Although it looks plain, it can make huge changes to the way you analyze data, calculate numbers and present them.

7 reasons why INDEX is an awesome companion:

• Get nth item from a list.
• Get the value at intersection of given row & column.
• Get entire row or column from a table.
• Use it to lookup left.
• Create dynamic ranges.
• Get any 1 range from a list of ranges.
• INDEX can process arrays.

It's pretty much impossible to use Excel and not notice the Name Box, which appears just above the upper-left-hand corner of the worksheet frame. Most users know this as the space in Excel where you can determine the address of the currently selected cell. A smaller subset of users relies on the Name Box as a navigation aid. However, that unobtrusive rectangle belies a dizzying array of functionality in Microsoft Excel.

Having spent a lot of time creating the perfect workbook, setting it just how you need it ready to do other important work, you send it out to people around the office or business, or perhaps you place it on a shared drive for everyone to access, only to find that within days, or even hours, some bright spark doesn’t like your layout and changes it around to suit them regardless of the fact that it was set up in a particular way for a specific reason.

This is where we need PROTECTION.

To reduce errors in Excel reports, you should foot and cross-foot them. But Excel's floating-point arithmetic gets in the way. Here's how to get around this problem.

When you create a pivot table from your source data, it only shows the items that have data. For example, if you put Customers and Products in the Row area, it only lists the items that each customer has bought.

In this article we'll see how to show items with no data in some records, show zeros in empty cells, and show items that have no data at all.

I use Regular Expressions (or RegEx) for data validation in Excel 2010. It is extremely fast, very flexible and powerful in the sense that its logic will only ALLOW the exact data you want to be processed - everything else will be validated as invalid.

Using Regular Expressions negate the use of multiple If, Then, Else or Case statements. This article will cover validating different types of data using a simple Regular Expression Subroutine. I will also provide an IsValid Regular Expression Function and some common Regular Expression patterns for you to try out.

When cells are not visible on a worksheet, you can watch those cells and their formulas in the Watch Window. The Watch Window makes it convenient to inspect, audit, or confirm formula calculations and results in large worksheets. By using the Watch Window, you don't need to repeatedly scroll or go to different parts of your worksheet.

If a workbook contains many sheets you can create a table of contents to make navigating to the sheets easier. This is a fantastic idea when producing a final version of a report in Excel for a customer.

Excel does not yet contain a feature that produces a table of contents, but you can create a macro to get the job done.