Statistical Functions in Microsoft Excel

As you read, review Table 1, which outlines the most common statistical functions in Microsoft Excel. You can apply these functions to a large set of data entered in a spreadsheet. The rest of this 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 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 LY 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 E13:E16.

However, you may have noticed the double-line style border that was 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 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:

  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 you make a selection 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 will appear before making a selection. 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. Preview of function output appears in range E13:E16 when selecting paste options from Home tab (F6, H, V), but not when accessing Paste Special directly.

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