EXCEL TIPS AND TRICKS 1

Paste into Non-sequential Cells in Excel

You can use the CTRL key for multiple selections. Sometimes, you want to copy a formula or piece of data into a series of non-sequential cells in Microsoft Office Excel. You can do this quickly without having to paste into each cell individually.

1. Copy the data from the source cell.

2. Hold down the CTRL key as you click to select each destination cell.

3. After all the cells are highlighted, paste the data by pressing CTRL+V. You have to paste only once.

Similarly, you can type data into a series of cells simultaneously.1. While holding down the CTRL key, click all the cells that you want to type the same text (or value) into.2. Type the entry, and then press CTRL+ENTER. The text will be added to all the selected cells.

Excel’s Counting and Summing Functions

Function Description


* These are new functions, available only in Excel 2007.

COUNT
Returns the number of cells that contain a numeric value

COUNTA
Returns the number of nonblank cells

COUNTBLANK
Returns the number of blank cells

COUNTIF
Returns the number of cells that meet a specified criterion

COUNTIFS*
Returns the number of cells that meet multiple criteria

DCOUNT
Counts the number of records that meet specified criteria; used with a worksheet database.

DCOUNTA
Counts the number of nonblank records that meet specified criteria; used with a worksheet
database.

DEVSQ
Returns the sum of squares of deviations of data points from the sample mean; used primarily
in statistical formulas

DSUM
Returns the sum of a column of values that meet specified criteria; used with a worksheet
database.
FREQUENCY
Calculates how often values occur within a range of values and returns a vertical array of
numbers. Used only in a multicell array formula,

SUBTOTAL
When used with a first argument of 2, 3, 102, or 103, returns a count of cells that comprise a
subtotal; when used with a first argument of 9 or 109, returns the sum of cells that comprise
a subtotal

SUM
Returns the sum of its arguments

SUMIF
Returns the sum of cells that meet a specified criterion
SUMIFS*
Returns the sum of cells that meet multiple criteria