Excel cells: YOUR OBJECTIVE
In an Excel worksheet, you recorded your company’s sales figures from various locations. To obtain a clearer picture of the North American sales, you want to create a separate table on the same worksheet that displays the sales numbers from only the North American locations. You want this table always to display the current sales data from the original table, even if those numbers change. To accomplish this, excel cells reference must be used to link the cells in the new table to the cells in the source table
In this learning module, you will be shown how to creaate and use cell references to link to cells in one table to cells in another table, when both tables are located in the same worksheet .
Excel cells: HOW DOES IT WORK?
In Excel cells, you want to make use of the contents of another cell, located elsewhere in the same worksheet. Using a excel cell reference is the easiest way to accomplish that. Excel cell references offer the following advantages:
- It is certain that the contents of the new cell are always identical to the contents of the source cell.
- You are spared typing and therefore possible typing errors.
- If the value in the original cell changes, the contents of the linked cell will automatically update, too. You can therefore be certain that in all cells that refer to the source cell, you are always working with the most current data.
In our current example: to create a table for the North American market (Detroit, Boston), click in cell A14 where the first ice-cream flavor (this company’s product) is to be used as the row heading. Now, to begin entering a cell reference, type in an equal sign (=), and then click on cell A4…
Now, when you press [Enter], the excel cells reference is complete, and the content of cell A4 (“Chocolate”) appears in cell A14.
A cell reference may also be created by typing it directly into the relevant cell. In our example, you could have typed “=A4″ into the cell. This method, however, since it requires typing, is considerably more error prone.
Instead of manually entering excel cells references for the other ice cream flavors (as in our example), you might prefer to use the AutoFill function (see learning module: Formulas and functions). To do this, position the mouse pointer over the bottom-right corner of cell A14. It will change to a black cross.Now, while holding the mouse button depressed, pull the pointer down to cell A17. When you release the mouse button, the desired cell references, properly adjusted for position, will be inserted. The individual flavors and the word “Total” will appear in their proper places.
To create cell references that will transfer the Boston column into the new table, click cell B13 and then enter an equal sign (=). Now click cell C3.
The remaining excel cells references for the Boston column can be entered by using Excel’s AutoFill function , as described in the tip above.
Similarly, you can create the cell reference for the “Detroit” column by clicking on cell C13. Then type in an equal sign (=) and click on cell B3 . Complete this excel cells reference, too, by clicking [Enter]. As above, you can use AutoFill to fill in the rest of the column’s cell references.
Thanks to the use of excel cells references, any changes made to Boston or Detroit data in the source table will immediately be reflected in the new North American Market table.
To format the numbers in the new table as they apear in the source table, you must first select the relevant cells in the new table. Then, on the Home tab, in the Number group, click the arrow on the button. In the selection catalog that appears, click the Currency option. Finally, to remove two decimal positions to the right of the decimal point; in the same group, click the button twice.