Analyzing Your Excel 2013 Data

Understanding Excel formulas and functions

Excel 2013 is a powerful worksheet program that gives you all kinds of tools for performing sophisticated calculations and data analyses. Depending on your needs, you might never need to do many high-end tasks in Excel, but having a working knowledge of formulas, functions, and cell referencing is an important part of getting Excel to perform even fairly simple calculations for you. This section introduces you to some of the basics you’ll need as you use functions and cell references in the formulas you create.

Learning about cell references

Each cell in your Excel worksheet has its own unique address that represents the intersection of the row and column where the cell is located; for example, C5 is an individual cell that falls at the intersection of column C, row 5. K23 is another, located in column K, row 23. You can easily determine the address of the cell by clicking in the cell. The Name box in the upper-left corner of the Excel window shows the cell reference of the selected cell.

When you select a range of cells, Excel describes that range by separating the individual cell references with a colon; for example, B4:B20 describes a range of cells that begins with cell B4 and extends to cell B20, which happens to define a group of cells that are all in one column.

When you create formulas, the formula will behave differently depending on whether the cell has an absolute or relative reference. A formula with a relative cell address uses the data in the cells specified, relative to its position on the worksheet. For example, with relative cell references, if you are summing the data in cells A1 to D1 and showing the total in E1, when you copy the formula in E1 to E2, the formula recalculates the data based on cells B1 to D2. If the cells in A1 to D1 are absolute references, the formula you copy to cell B2 will continue to reference the cells in A1 to D1. By default, cells use relative references, but if you want to make the references for cells absolute, select the cells you want to change in the formula bar and press F4 to switch from relative to absolute referencing. Excel adds a dollar sign ($) to the beginning of any reference that is treated as absolute in a calculation.

Understanding formulas and functions

You’ll need to understand cell references to use them properly in formulas. Formulas instruct Excel as to what calculations to perform on your worksheet. A formula might be very simple; for example =3*5+2. (Note that the equal sign [=] informs Excel that what follows is a formula or function and should be calculated as such, not treated as worksheet data.) A more common formula uses cell references to indicate to Excel where to find the data for the calculation, such as =D4+D5.

Click to view larger image

You use functions to perform common calculations on the cells you select in your worksheet. You include functions in your worksheet formulas. Some of the functions in Excel are very common and you’ll be at home using them right off the bat. Using functions such as SUM, AVERAGE, and COUNT, you can total values, find an average value, and display the number of elements in a data range. You insert a function in a formula and specify to the function which cells you want to include in the calculation, as in the following example:

=SUM(B3:E3)

In this formula, SUM is the function. The cell reference (B3:E3) points Excel to the values you want to use to produce the formula result.

You’ll use the Formula bar and the Insert Function tool to create and modify formulas in Excel. The Formulas tab also includes the tools you need to insert, manipulate, and modify formulas and functions on your worksheet.