Statistical Functions in Microsoft Excel

As you read this text, pay attention to Table 1, which outlines the most common statistical functions in Microsoft Excel. You can apply these functions to large spreadsheet data sets. The rest of the section details how to use each type of function in a budget spreadsheet.

Copy and Paste Formulas (Pasting without Formats)

Data file: Continue with CH2 Personal Budget.

As shown in Figure 15, the COUNT, AVERAGE, MIN, and MAX functions summarize the data in the Annual Spend column. You will also notice that there is space to copy and paste these functions under the LY Spend column. This allows us to compare what we spent last year and what we plan to spend this year. Normally, we would simply copy and paste these functions into the range E13:E16.

However, you may have noticed the double-line style border used around the perimeter of the range B13:E16. If we used the regular Paste command, the double line on the right side of the range E13:E16 would be replaced with a single line. Therefore, we will use one of the Paste Special commands to paste only the functions without any formatting treatments. This is accomplished through the following steps:

  1. Highlight the range D13:D16 in the Budget Detail worksheet

  2. Click the Copy button in the Home tab of the Ribbon

  3. Click cell E13

  4. Click the down arrow below the Paste button in the Home tab of the Ribbon

  5. Click the Formulas option from the drop-down list of buttons (see Figure 16)


Figure 16 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 selecting them by dragging the mouse pointer over the options. As shown in the figure, when the mouse pointer is placed over the Formulas button, you can see how the functions appear before selecting. Notice that the double-line border does not change when this option is previewed. That is why this selection is made instead of the regular Paste option.

Press Ctrl + Alt + V for Paste Special menu, then F to select Functions, or R to select formulas and number functions. Prev

Figure 16 Paste Formulas Option


Skill Refresher

Paste Formulas
  1. Click a cell location containing a formula or function

  2. Click the Copy button in the Home tab of the Ribbon

  3. Click the cell location or cell range where the formula or function will be pasted

  4. Click the down arrow below the Paste button in the Home tab of the Ribbon

  5. Click the Formulas button under the Paste group of buttons