2.3 Copy and Paste Formulas, and Absolute Cell References
2.3 Copy and Paste Formulas, and Absolute Cell References
Learning Objectives
- Learn how to copy and paste formulas without formats applied to a cell location.
- Use absolute references to calculate percent of totals.
Copy and Paste Formulas (Pasting without Formats)
As shown in Figure 2.24, the COUNT, AVERAGE, MIN, and MAX functions are summarizing the data in the Annual Spend column. You will also notice that there is space to copy and paste these functions under the Last Year Spend column. This allows us to compare what we spent last year and what we are planning to spend this year. Normally, we would simply copy and paste these functions into the range E14:E16. However, you may have noticed the thicker style border that was used around the perimeter of the range D13:E16. If we used the regular Paste command, the thick line on the right side of the range D13:E16 would be replaced with a single line. Therefore, we are going to use one of the Paste Special commands to paste only the functions without any of the formatting treatments. This is accomplished through the following steps:
- Highlight the range D14:D16 in the Budget Detail worksheet.
- Click the Copy button in the Home tab of the Ribbon.
- Click cell E14.
- Click the down arrow below the Paste button in the Home tab of the Ribbon.
- Click the Formulas option from the drop-down list of buttons (see Figure 2.25).
Figure 2.25 shows the list of buttons that appear when you click the down arrow below the Paste button in the Home tab of the Ribbon. One thing to note about these options is that you can preview them before you make a selection by dragging the mouse pointer over the options. When the mouse pointer is placed over the Formulas button, you can see how the functions will appear before making a selection. Notice that the thick line border does not change when this option is previewed. That is why this selection is made instead of the regular Paste option.
Skill Refresher
Paste Formulas without formatting
- Click a cell location containing a formula or function.
- Click the Copy button in the Home tab of the Ribbon.
- Click the cell location or cell range where the formula or function will be pasted.
- Click the down arrow below the Paste button in the Home tab of the Ribbon.
- Click the Formulas button under the Paste group of buttons.
Absolute References (Calculating Percent of Totals)
To further analyze your budget, you want to see what percentage of your total monthly spending is spent in each category. Since totals were added to row 12 of the Budget Detail worksheet, a percent of total calculation can be added to Column C beginning in cell C3. The percent of total calculation shows the percentage for each value in the Monthly Spend column with respect to the total in cell B12. However, after the formula is created, it will be necessary to turn off Excel’s relative referencing feature before copying and pasting the formula to the rest of the cell locations in the column. Turning