Create and Edit Formulas
Create, Edit, and Delete Named Ranges
Summarize the Values in Groups of Cells
Create Conditional Functions
Microsoft Excel workbooks allow you to do much more than simply store and organize your data. One important task you can perform in Excel is to summarize the values in related cells. Whether those cells represent the sales for a day at your store, the returns from your personal investments, or your times in bicycle races, you can find the total or average of the values, identify the minimum or maximum value in a group, or perform dozens of other calculations on your data. Many times you can’t access the information you want without referencing more than one cell, and it’s also often true you’ll use the data in the same group of cells in more than one calculation. Excel makes it easy to reference a number of cells at once, letting you build your calculations quickly.
After you’ve added your data to a worksheet, you can summarize the data by creating formulas. A formula is an expression that performs calculations on your data. For example, in a worksheet that lists hourly sales for a day in a single row of cells, you can build a formula in the last cell in that row to find the total of all sales for the day. You can also build the formula to calculate the average or find the lowest or highest hourly value.
When you build a formula, you need to identify the worksheet cells that will provide the values for the formula and the operations you want performed on those values. To identify a cell, you give its cell reference. The first cell in the first column is cell A1, meaning column A, row 1. If you examine a formula, you will sometimes see a cell reference written as $A$1, rather than just A1. The difference is that cell references written with the dollar signs are absolute references, meaning the reference won’t change when the formula is copied to another cell, and cell references written without the dollar signs are relative references, which will change when the formula with the references is copied to another cell.
The benefit of relative references is that you can write a formula once, copy it to as many other cells as you like, and have Excel update the formulas to reflect their new cells. As an example, consider the worksheet in the figure, which tracks the number of hourly package pickups for a month.
The cells in column P contain formulas that calculate the sum of the hourly pickup values in column C through column O. The formula in cell P5, =SUM(C5:O5), finds the sum of cells in row 5, corresponding to October 1. When you copy the formula from cell P5 to cell P6, the formula changes to =SUM(C6: O6). Excel notices that you copied the formula to a new row and assumes that you want the formula to work on that data. Had you written the formula as =SUM($C$5:$O$5), however, Excel would notice that the formula used absolute references and would copy the formula as =SUM($C$5:$O$5).
If you want to reference a value from a cell in another workbook, you can do it. Excel uses 3D references, which means that any cell in any workbook can be described by three pieces of information:
The name of the workbook
The name of the worksheet
The cell reference
Here’s the reference for cell Q38 on the January worksheet in the Y2007ByMonth workbook:
[Y2007ByMonth. xlsx]January!$Q$38
The good news is that you don’t need to remember how to create these references yourself. If you want to use a cell from another workbook in a formula, all you need to do is click the cell where you’ll use the value, start the formula, and click the cell in the other workbook. Excel will fill in the reference for you.