Online Investing Hacks

Flexible Techniques for Referencing Data

A number of Excel functions can help you summarize data or retrieve specific values to use in a formula.

Excel is a natural choice for processing financial data; not only does it calculate numeric results from values in cells, but it also acts as a lightweight database management system that sorts, filters, and validates those same cell values. However, when you store a great deal of data in Excel, cell addresses might be hard to handle. If you move data around, formulas that use cell addresses might stop working properly. Worksheets are more flexible when you retrieve data using Excel functions that search for labels associated with the data you want. In addition, formulas are hard to understand and even harder to troubleshoot, because cell addresses don’t convey their purpose. You can make formulas easier to understand by using data retrieval functions to pull values into a summary worksheet with named cells that identify the financial measures that they represent.

Essentially, Excel worksheets are tables with cells at the intersection of each column and row. Typically, when you store data in a range of cells, the first column on the left side of the range includes labels that describe the rows, and the first row of the cell range includes labels that describe the columns. You can use Excel functions to search these labels for text and retrieve data in nearby cells.

Searching Labels in a Column

Suppose you want to use the five-year average ...