Dr K V SUBBA REDDY MBA-MCA

INSTITUTIONS

THIRD SEMESTER
M.B.A. – MASTER OF BUSINESS
ADMINISTRATION

BUSINESS SIMULATION LAB

MANUAL
 1. Excel Lessons

Customize the Quick Access Toolbar

The Quick Access Toolbar is a customizable toolbar that contains a set of commands that are

independent of the tab that is currently displayed. You can add buttons that represent commands to

the Quick Access Toolbar, and you can move the Quick Access Toolbar from one of the two possible

locations.

What do you want to do?

Move the Quick Access Toolbar

Add a command to the Quick Access Toolbar by using the Program Name Options dialog box

Add a command to the Quick Access Toolbar directly from the Ribbon

Move the Quick Access Toolbar

The Quick Access Toolbar can be located in one of two places:

Upper-left corner next to the Microsoft Office Button (default location)

Below the Ribbon

If you don't want the Quick Access Toolbar to be displayed in its current location, you can move it to

the other location. If you find that the default location next to the Microsoft Office Button is too

far from your work area to be convenient, you may want to move it closer to your work area. The

location below the Ribbon encroaches on the work area. Therefore, if you want to maximize the work

area, you may want to keep the Quick Access Toolbar in its default location.
1. Click Customize Quick Access Toolbar .

2. In the list, click Show Below the Ribbon.

Top of Page

Add a command to the Quick Access Toolbar by

using the Program Name Options dialog box

You can add a command to the Quick Access Toolbar from a list of commands in the Program Name

Options dialog box, where Program Name is the name of the program you are in, for example, Word

Options

1. Do one of the following:

 Use the Microsoft Office Button

1. Click the Microsoft Office Button , and then click Program Name Options,

where Program Name is the name of the program you are in, for example, Word

Options.

2. Click Customize.

 Use the Quick Access Toolbar

1. Click Customize Quick Access Toolbar .

2. In the list, click More Commands.

2. In the Program Name Options dialog box, in the Choose commands from list

, click the command category that you want.

3. In the list of commands in the selected category, click the command that you want to add to

the Quick Access Toolbar, and then click Add.

4. After you finish adding any other command that you want, click OK.

Top of Page
Add a command to the Quick Access Toolbar
directly from the Ribbon

You can also add a command to the Quick Access Toolbar directly from commands that are displayed

on the Ribbon.

1. On the Ribbon, click the appropriate tab or group to display the command that you want to

add to the Quick Access Toolbar.

2. Right-click the command, and then click Add to Quick Access Toolbar on the shortcut

menu.

NOTE Only commands can be added to the Quick Access Toolbar. The contents of most lists, such as

indent and spacing values and individual styles, which also appear on the Ribbon, cannot be added to

the Quick Access Toolbar.

Enter numbers or text

1. On the worksheet, click a cell.

2. Type the numbers or text that you want, and then press ENTER or TAB.

TIP To start data on a new line within a cell, enter a line break by pressing ALT+ENTER.

NOTES

By default, pressing ENTER moves the selection down one cell, and pressing TAB moves the

selection one cell to the right. You cannot change the direction of the move for the TAB key,

but you can specify a different direction for the ENTER key.

How to change the direction for the ENTER key

1. Click the Microsoft Office Button , and then click Excel Options.

2. In the Advanced category, under Edit, select the After pressing Enter, move

selection check box, and then click the direction that you want in the Direction box.

When you press TAB to enter data in several cells in a row and then press ENTER at the end of

that row, the selection moves to the beginning of the next row.
 A cell may display ##### when it contains data that has a number format that is wider than

the column width. To see all text, you must increase the width of the column.

How to change the column width

1. Click the cell for which you want to change the column width.

2. On the Home tab, in the Cells group, click Format.

3. Under Cell Size, do one of the following:

To fit all text in the cell, click AutoFit Column Width.

To specify a larger column width, click Column Width, and then type the width

that you want in the Column width box.

You can display multiple lines of text inside a cell by wrapping the text.

How to wrap text in a cell

1. Click the cell in which you want to wrap the text.

2. On the Home tab, in the Alignment group, click Wrap Text.

NOTE If the text is a single long word, the characters won't wrap; you can widen the

column or decrease the font size to see all of the text. If not all of the text is visible after

you wrap the text, you may need to adjust the height of the row. On the Home tab, in

the Cells group, click Format, and then under Cell Size click AutoFit Row.

In Microsoft Office Excel, the appearance of a number in a cell is separate from the number

that is stored in the cell. When a number that you enter is rounded, in most circumstances,
 only the displayed number is rounded. Calculations use the actual number stored in the cell,

not the number that is displayed.

After you type numbers in a cell, you can change the format in which they are displayed.

How to change the number format

1. Click the cell that contains the numbers that you want to format.

2. On the Home tab, in the Number group, point to General, and then click the format

that you want.

TIP To select a number format from the list of available formats, click More, and then

click the format that you want to use in the Category list.

For numbers that do not need to be calculated in Excel, such as phone numbers, you can

format them as text by applying the Text format to empty cells before typing the numbers.

How to format numbers as text

1. Select an empty cell.

2. On the Home tab, in the Number group, point to General, and then click Text.

3. Type the numbers that you want in the formatted cell.

NOTE Numbers that you typed before you applied the Text format to the cells need to

be reentered in the formatted cells. To quickly reenter numbers as text, select each cell,

press F2, and then press ENTER.

Top of Page
Enter numbers with a fixed decimal point

1. Click the Microsoft Office Button , and then click Excel Options.

2. Click Advanced, and then under Editing options, select the Automatically insert a

decimal point check box.

3. In the Places box, enter a positive number for digits to the right of the decimal point or a

negative number for digits to the left of the decimal point.

For example, if you enter 3 in the Places box and then type 2834 in a cell, the value will be

2.834. If you enter -3 in the Places box and then type 283, the value will be 283000.

4. On the worksheet, click a cell, and then enter the number that you want.

NOTE Data that you typed in cells before selecting the Fixed decimal option is not

affected.

TIP To temporarily override the Fixed decimal option, type a decimal point when you enter

the number.

Top of Page

Enter dates or times

1. On the worksheet, click a cell.

2. Type a date or time as follows:

 For a date, use a slash mark or a hyphen to separate the parts of a date; for example,

type 9/5/2002 or 5-Sep-2002.

TIP To enter the current date, press CTRL+; (semicolon).

 For a time that is based on the 12-hour clock, type a space, and then type a or p after

the time; for example, 9:00 p. Otherwise, Excel enters the time as AM.

TIP To enter the current time, press CTRL+SHIFT+; (semicolon).

NOTES

To enter a date or time that stays current when you reopen a worksheet, you can use the

TODAY and NOW functions.

When a date or time is typed in a cell, it appears either in a default date or time format or in a

format that was applied to the cell before the date or time was entered. The default date or

time format is based on the date and time settings in the Regional and Language Options

dialog box (Control Panel). If these date and time settings have changed, any existing dates

or times in your workbooks that have not been formatted by using the Format Cells

command also change.

If you want to use the default date or time format, click the cell that contains the date or time,

and then press CTRL+SHIFT+# or CTRL+SHIFT+@.

Top of Page

Enter the same data into several cells at once

1. Select the cells into which you want to enter the same data. The cells do not have to be

adjacent.

How to select cells, ranges, rows, or columns

To select Do this

A single cell Click the cell, or press the arrow keys to move to the cell.

A range of cells Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow
keys to extend the selection.

You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow
keys. To stop extending the selection, press F8 again.

A large range of cells Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can
scroll to make the last cell visible.

All cells on a worksheet Click the Select All button.

 To select the entire worksheet, you can also press CTRL+A.

NOTE If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second
time selects the entire worksheet.

Nonadjacent cells or cell Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
ranges
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent
cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.

NOTE You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without
canceling the entire selection.

An entire row or column Click the row or column heading.

Row heading

Column heading

You can also select cells in a row or column by selecting the first cell and then pressing
CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for
columns).

NOTE If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the
last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.

Adjacent rows or columns Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you
select the last row or column.

Nonadjacent rows or Click the column or row heading of the first row or column in your selection; then hold down CTRL while
columns you click the column or row headings of other rows or columns that you want to add to the selection.

The first or last cell in a Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for
row or column rows, UP ARROW or DOWN ARROW for columns).

The first or last cell on a Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
worksheet or in a
Microsoft Office Excel
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or
table
formatting.

Cells to the last used cell Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell
on the worksheet (lower- on the worksheet (lower-right corner).
right corner)

Cells to the beginning of Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of
the worksheet the worksheet.

More or fewer cells than Hold down SHIFT while you click the last cell that you want to include in the new selection. The rectangular
the active selection range between the active cell (active cell: The selected cell in which data is entered when you begin
typing. Only one cell is active at a time. The active cell is bounded by a heavy border.) and the cell that
you click becomes the new selection.
 In the active cell, type the data, and then press CTRL+ENTER.

TIP You can also enter the same data into several cells by using the fill handle (fill handle:

The small black square in the lower-right corner of the selection. When you point to the fill

handle, the pointer changes to a black cross.) to automatically fill data in worksheet

cells. For more information, see Fill data in worksheet cells.

Top of Page

Enter the same data on other worksheets

If you already entered data on one worksheet, you can quickly fill this data into corresponding cells on

other worksheets.

1. Click the tab of the worksheet that contains the data. Then hold down CTRL while you click the

tabs of the worksheets into which you want to fill the data.

NOTE If you don't see the tab that you want, click the tab scrolling buttons to display the

tab, and then click the tab.

2. In the worksheet, select the cells that contain the data that you entered.

3. On the Home tab, in the Editing group, click Fill, and then click Across Worksheets.

4. Under Fill, select the option that you want.

NOTES

Data that you type appears in all selected worksheets, and may inadvertently replace existing

data. To avoid replacing existing data, view worksheets at the same time.

How to view worksheets at the same time

 1. On the View tab, in the Window group, click New Window.

2. Switch to the new window, and then click the sheet tab of the worksheet that you want

to view.

Repeat steps 1 and 2 for each worksheet that you want to view.

3. On the View tab, in the Window group, click Arrange All.

To cancel a selection of multiple worksheets, click any unselected worksheet. If an unselected

worksheet is not visible, right-click the sheet tab of a selected worksheet, and then click

Ungroup Sheets.

Edit cell contents

Show All

You can edit the contents of a cell directly in the cell. You can also edit the contents of a cell in the

formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or

formulas in cells or charts. Displays the constant value or formula stored in the active cell.).

NOTE When you are in editing mode, many Ribbon commands will be inactive, and you will not be

able to use them.

1. To place the contents of a cell in editing mode, do one of the following:

 Double-click the cell that contains the data that you want to edit.

 Click the cell that contains the data that you want to edit, and then click anywhere in the

formula bar.

This positions the insertion point in the cell or formula bar.

2. Tip To move the insertion point to the end of the cell contents, click the cell and press F2.

3. To edit the cell contents, do any one of the following:

  To delete characters, click where you want to delete them, and then press BACKSPACE,

or select them, and then press DELETE.

 To insert characters, click where you want to insert them, and then type the new

characters.

 To replace specific characters, select them, and then type the new characters.

 To turn on Overtype mode so that existing characters are replaced by new characters

while you type, press INSERT.

NOTE Overtype mode can be turned on or off only when you are in editing mode.

When Overtype mode is turned on, the character to the right of the insertion point is

highlighted in the formula bar, and it will be overwritten when you type.

 To start a new line of text at a specific point in a cell, click where you want to break the

line, and then press ALT+ENTER.

4. To enter your changes, press ENTER.

Tip Before you press ENTER, you can cancel any changes that you made by pressing ESC.

After you press ENTER, you can cancel any changes that you made by clicking Undo on the

Quick Access Toolbar.

NOTES

A cell may display ##### when it contains data that has a number format and that is wider

than the column width. To see all text, you must increase the width of the column.

How to change the column width

1. Click the cell for which you want to change the column width.

2. On the Home tab, in the Cells group, click Format.

 3. Under Cell Size, do one of the following:

 To fit all text in the cell, click AutoFit Column Width.

 To specify a larger column width, click Column Width, and then type the width

that you want in the Column width box.

You can display multiple lines of text inside a cell by wrapping the text.

How to wrap text in a cell

1. Click the cell in which you want to wrap the text.

2. On the Home tab, in the Alignment group, click Wrap Text.

NOTE If the text is a single long word, the characters won't wrap; you can widen the

column or decrease the font size to see all the text. If not all text is visible after you wrap

the text, you may need to adjust the height of the row. On the Home tab, in the Cells

group, click Format, and then under Cell Size click AutoFit Row.

Move or copy cells and cell contents

Show All

Hide All

Using the Cut, Copy, and Paste commands in Microsoft Office Excel, you can move
or copy entire cells or their contents. You can also copy specific contents or
attributes from the cells. For example, you can copy the resulting value of a formula
without copying the formula itself, or you can copy only the formula.

Note Excel displays an animated moving border around cells that have been cut
or copied. To cancel a moving border, press ESC.

What do you want to do?

Move or copy entire cells

Move or copy entire cells by using the mouse

Insert moved or copied cells between existing cells

Copy visible cells only

Prevent copied blank cells from replacing data

Move or copy the contents of a cell

Copy cell values, cell formats, or formulas only

Move or copy entire cells

When you move or copy a cell, Excel moves or copies the entire cell, including
formulas and their resulting values, cell formats, and comments.

Select the cells that you want to move or copy.

How to select cells or ranges

To select Do this

A single cell Click the cell, or press the arrow keys to move to the cell.

A range of cells Click the first cell in the range, and then drag to the last cell, or hold
down SHIFT while you press the arrow keys to extend the selection.

You can also select the first cell in the range, and then press F8 to
extend the selection by using the arrow keys. To stop extending the
selection, press F8 again.

A large range of cells Click the first cell in the range, and then hold down SHIFT while you
click the last cell in the range. You can scroll to make the last cell
visible.

All cells on a Click the Select All button.

worksheet

To select the entire worksheet, you can also press CTRL+A.

Note If the worksheet contains data, CTRL+A selects the current

region. Pressing CTRL+A a second time selects the entire worksheet.
Nonadjacent cells or Select the first cell or range of cells, and then hold down CTRL while
cell ranges you select the other cells or ranges.

You can also select the first cell or range of cells, and then press
SHIFT+F8 to add another nonadjacent cell or range to the selection.
To stop adding cells or ranges to the selection, press SHIFT+F8 again.

Note You cannot cancel the selection of a cell or range of cells in a

nonadjacent selection without canceling the entire selection.

An entire row or Click the row or column heading.

column

Row heading

Column heading

You can also select cells in a row or column by selecting the first cell
and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT
ARROW for rows, UP ARROW or DOWN ARROW for columns).

Note If the row or column contains data, CTRL+SHIFT+ARROW key

selects the row or column to the last used cell. Pressing
CTRL+SHIFT+ARROW key a second time selects the entire row or
column.

Adjacent rows or Drag across the row or column headings. Or select the first row or
columns column; then hold down SHIFT while you select the last row or
column.

Nonadjacent rows or Click the column or row heading of the first row or column in your
columns selection; then hold down CTRL while you click the column or row
headings of other rows or columns that you want to add to the
selection.

The first or last cell in Select a cell in the row or column, and then press CTRL+ARROW key
a row or column (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN
ARROW for columns).

The first or last cell on Press CTRL+HOME to select the first cell on the worksheet or in an
a worksheet or in a Excel list.
Microsoft Office Excel
table Press CTRL+END to select the last cell on the worksheet or in an
 Excel list that contains data or formatting.

Cells to the last used Select the first cell, and then press CTRL+SHIFT+END to extend the
cell on the worksheet selection of cells to the last used cell on the worksheet (lower-right
(lower-right corner) corner).

Cells to the beginning Select the first cell, and then press CTRL+SHIFT+HOME to extend the
of the worksheet selection of cells to the beginning of the worksheet.

More or fewer cells Hold down SHIFT while you click the last cell that you want to include
than the active in the new selection. The rectangular range between the active
selection cell (active cell: The selected cell in which data is entered when you
begin typing. Only one cell is active at a time. The active cell is
bounded by a heavy border.) and the cell that you click becomes the
new selection.

Tip To cancel a selection of cells, click any cell on the worksheet.

On the Home tab, in the Clipboard group, do one of the following:

To move cells, click Cut .

Keyboard shortcut You can also press CTRL+X.

To copy cells, click Copy .

Keyboard shortcut You can also press CTRL+C.

Select the upper-left cell of the paste area.

Tip To move or copy a selection to a different worksheet or workbook, click another

worksheet tab or switch to another workbook, and then select the upper-left cell of
the paste area.

On the Home tab, in the Clipboard group, click Paste .

Keyboard shortcut You can also press CTRL+V.

Notes

To choose specific options when you paste cells, you can click the arrow below
Paste , and then click the option that you want. For example, you can click Paste
Special or Paste As Picture.
By default, Excel displays the Paste Options button on the worksheet to provide you
with special options when you paste cells, such as Keep Source Formatting and
Match Destination Formatting. If you don't want to display this button every time
that you paste cells, you can turn this option off. Click the Microsoft Office Button

, and then click Excel Options. In the Advanced category, under Cut, Copy, and
Paste, clear the Show Paste Options buttons check box.

Excel replaces existing data in the paste area when you cut and paste cells to move
them.

When you copy cells, cell references are automatically adjusted. When you move
cells, however, cell references are not adjusted, and the contents of those cells and
of any cells that point to them may be displayed as reference errors. In this case,
you will need to adjust the references manually.

If the selected copy area includes hidden cells, Excel also copies the hidden cells.
You may need to temporarily unhide cells that you don't want to include when you
copy information.

If the paste area contains hidden rows or columns, you might need to unhide the
paste area to see all of the copied cells.

Top of Page

Move or copy entire cells by using the mouse

By default, drag-and-drop editing is turned on so that you can use the mouse to
move and copy cells.

Select the cells or range (range: Two or more cells on a sheet. The cells in a range
can be adjacent or nonadjacent.) of cells that you want to move or copy.

How to select cells or ranges

To select Do this

A single cell Click the cell, or press the arrow keys to move to the cell.

A range of cells Click the first cell in the range, and then drag to the last cell, or hold
down SHIFT while you press the arrow keys to extend the selection.

You can also select the first cell in the range, and then press F8 to
extend the selection by using the arrow keys. To stop extending the
selection, press F8 again.

A large range of cells Click the first cell in the range, and then hold down SHIFT while you
click the last cell in the range. You can scroll to make the last cell
 visible.

All cells on a Click the Select All button.

worksheet

To select the entire worksheet, you can also press CTRL+A.

Note If the worksheet contains data, CTRL+A selects the current

region. Pressing CTRL+A a second time selects the entire worksheet.

Nonadjacent cells or Select the first cell or range of cells, and then hold down CTRL while
cell ranges you select the other cells or ranges.

You can also select the first cell or range of cells, and then press
SHIFT+F8 to add another nonadjacent cell or range to the selection.
To stop adding cells or ranges to the selection, press SHIFT+F8 again.

Note You cannot cancel the selection of a cell or range of cells in a

nonadjacent selection without canceling the entire selection.

An entire row or Click the row or column heading.

column

Row heading

Column heading

You can also select cells in a row or column by selecting the first cell
and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT
ARROW for rows, UP ARROW or DOWN ARROW for columns).

Note If the row or column contains data, CTRL+SHIFT+ARROW key

selects the row or column to the last used cell. Pressing
CTRL+SHIFT+ARROW key a second time selects the entire row or
column.

Adjacent rows or Drag across the row or column headings. Or select the first row or
columns column; then hold down SHIFT while you select the last row or
column.

Nonadjacent rows or Click the column or row heading of the first row or column in your
 columns selection; then hold down CTRL while you click the column or row
headings of other rows or columns that you want to add to the
selection.

The first or last cell in Select a cell in the row or column, and then press CTRL+ARROW key
a row or column (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN
ARROW for columns).

The first or last cell on Press CTRL+HOME to select the first cell on the worksheet or in an
a worksheet or in a Excel list.
Microsoft Office Excel
table Press CTRL+END to select the last cell on the worksheet or in an
Excel list that contains data or formatting.

Cells to the last used Select the first cell, and then press CTRL+SHIFT+END to extend the
cell on the worksheet selection of cells to the last used cell on the worksheet (lower-right
(lower-right corner) corner).

Cells to the beginning Select the first cell, and then press CTRL+SHIFT+HOME to extend the
of the worksheet selection of cells to the beginning of the worksheet.

More or fewer cells Hold down SHIFT while you click the last cell that you want to include
than the active in the new selection. The rectangular range between the active
selection cell (active cell: The selected cell in which data is entered when you
begin typing. Only one cell is active at a time. The active cell is
bounded by a heavy border.) and the cell that you click becomes the
new selection.

Tip To cancel a selection of cells, click any cell on the worksheet.

Do one of the following:

To move a cell or range of cells, point to the border of the selection. When the

pointer becomes a move pointer , drag the cell or range of cells to another
location.

To copy a cell or range of cells, hold down CTRL while you point to the border of the

selection. When the pointer becomes a copy pointer , drag the cell or range of
cells to another location.

Notes

Excel replaces existing data in the paste area when you move cells.

When you copy cells, cell references are automatically adjusted. When you move
cells, however, cell references are not adjusted, and the contents of those cells and
of any cells that point to them may be displayed as reference errors. In this case,
you will need to adjust the references manually.

If the selected copy area includes hidden cells, Excel also copies the hidden cells.
You may need to temporarily unhide cells that you don't want to include when you
copy information.

If the paste area contains hidden rows or columns, you might need to unhide the
paste area to see all of the copied cells.

Top of Page

Insert moved or copied cells between existing cells

Select the cell or range (range: Two or more cells on a sheet. The cells in a range
can be adjacent or nonadjacent.) of cells that contains the data that you want to
move or copy.

How to select cells or ranges

To select Do this

A single cell Click the cell, or press the arrow keys to move to the cell.

A range of cells Click the first cell in the range, and then drag to the last cell, or hold
down SHIFT while you press the arrow keys to extend the selection.

You can also select the first cell in the range, and then press F8 to
extend the selection by using the arrow keys. To stop extending the
selection, press F8 again.

A large range of cells Click the first cell in the range, and then hold down SHIFT while you
click the last cell in the range. You can scroll to make the last cell
visible.

All cells on a Click the Select All button.

worksheet

To select the entire worksheet, you can also press CTRL+A.

Note If the worksheet contains data, CTRL+A selects the current

region. Pressing CTRL+A a second time selects the entire worksheet.

Nonadjacent cells or Select the first cell or range of cells, and then hold down CTRL while
cell ranges
 you select the other cells or ranges.

You can also select the first cell or range of cells, and then press
SHIFT+F8 to add another nonadjacent cell or range to the selection.
To stop adding cells or ranges to the selection, press SHIFT+F8 again.

Note You cannot cancel the selection of a cell or range of cells in a

nonadjacent selection without canceling the entire selection.

An entire row or Click the row or column heading.

column

Row heading

Column heading

You can also select cells in a row or column by selecting the first cell
and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT
ARROW for rows, UP ARROW or DOWN ARROW for columns).

Note If the row or column contains data, CTRL+SHIFT+ARROW key

selects the row or column to the last used cell. Pressing
CTRL+SHIFT+ARROW key a second time selects the entire row or
column.

Adjacent rows or Drag across the row or column headings. Or select the first row or
columns column; then hold down SHIFT while you select the last row or
column.

Nonadjacent rows or Click the column or row heading of the first row or column in your
columns selection; then hold down CTRL while you click the column or row
headings of other rows or columns that you want to add to the
selection.

The first or last cell in Select a cell in the row or column, and then press CTRL+ARROW key
a row or column (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN
ARROW for columns).

The first or last cell on Press CTRL+HOME to select the first cell on the worksheet or in an
a worksheet or in a Excel list.
Microsoft Office Excel
table Press CTRL+END to select the last cell on the worksheet or in an
Excel list that contains data or formatting.
 Cells to the last used Select the first cell, and then press CTRL+SHIFT+END to extend the
cell on the worksheet selection of cells to the last used cell on the worksheet (lower-right
(lower-right corner) corner).

Cells to the beginning Select the first cell, and then press CTRL+SHIFT+HOME to extend the
of the worksheet selection of cells to the beginning of the worksheet.

More or fewer cells Hold down SHIFT while you click the last cell that you want to include
than the active in the new selection. The rectangular range between the active
selection cell (active cell: The selected cell in which data is entered when you
begin typing. Only one cell is active at a time. The active cell is
bounded by a heavy border.) and the cell that you click becomes the
new selection.

Tip To cancel a selection of cells, click any cell on the worksheet.

On the Home tab, in the Clipboard group, do one of the following:

To move the selection, click Cut .

Keyboard shortcut You can also press CTRL+X.

To copy the selection, click Copy .

Keyboard shortcut You can also press CTRL+C.

Right-click the upper-left cell of the paste area, and then click Insert Cut Cells or
Insert Copied Cells on the shortcut menu.

Tip To move or copy a selection to a different worksheet or workbook, click another

worksheet tab or switch to another workbook, and then select the upper-left cell of
the paste area.

In the Insert Paste dialog box, click the direction in which you want to shift the
surrounding cells.

Note If you insert entire rows or columns, the surrounding rows and columns are
shifted down and to the left.

Top of Page

Copy visible cells only

If some cells, rows, or columns on your worksheet are not displayed, you have the
option of copying all cells or only the visible cells. For example, you can choose to
copy only the displayed summary data on an outlined (outline: Worksheet data in
which rows or columns of detail data are grouped so that you can create summary
reports. The outline can summarize either an entire worksheet or a selected portion
of it.) worksheet.

Select the cells that you want to copy.

How to select cells or ranges

To select Do this

A single cell Click the cell, or press the arrow keys to move to the cell.

A range of cells Click the first cell in the range, and then drag to the last cell, or hold
down SHIFT while you press the arrow keys to extend the selection.

You can also select the first cell in the range, and then press F8 to
extend the selection by using the arrow keys. To stop extending the
selection, press F8 again.

A large range of cells Click the first cell in the range, and then hold down SHIFT while you
click the last cell in the range. You can scroll to make the last cell
visible.

All cells on a Click the Select All button.

worksheet

To select the entire worksheet, you can also press CTRL+A.

Note If the worksheet contains data, CTRL+A selects the current

region. Pressing CTRL+A a second time selects the entire worksheet.

Nonadjacent cells or Select the first cell or range of cells, and then hold down CTRL while
cell ranges you select the other cells or ranges.

You can also select the first cell or range of cells, and then press
SHIFT+F8 to add another nonadjacent cell or range to the selection.
To stop adding cells or ranges to the selection, press SHIFT+F8 again.

Note You cannot cancel the selection of a cell or range of cells in a

nonadjacent selection without canceling the entire selection.
An entire row or Click the row or column heading.
column

Row heading

Column heading

You can also select cells in a row or column by selecting the first cell
and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT
ARROW for rows, UP ARROW or DOWN ARROW for columns).

Note If the row or column contains data, CTRL+SHIFT+ARROW key

selects the row or column to the last used cell. Pressing
CTRL+SHIFT+ARROW key a second time selects the entire row or
column.

Adjacent rows or Drag across the row or column headings. Or select the first row or
columns column; then hold down SHIFT while you select the last row or
column.

Nonadjacent rows or Click the column or row heading of the first row or column in your
columns selection; then hold down CTRL while you click the column or row
headings of other rows or columns that you want to add to the
selection.

The first or last cell in Select a cell in the row or column, and then press CTRL+ARROW key
a row or column (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN
ARROW for columns).

The first or last cell on Press CTRL+HOME to select the first cell on the worksheet or in an
a worksheet or in a Excel list.
Microsoft Office Excel
table Press CTRL+END to select the last cell on the worksheet or in an
Excel list that contains data or formatting.

Cells to the last used Select the first cell, and then press CTRL+SHIFT+END to extend the
cell on the worksheet selection of cells to the last used cell on the worksheet (lower-right
(lower-right corner) corner).

Cells to the beginning Select the first cell, and then press CTRL+SHIFT+HOME to extend the
of the worksheet selection of cells to the beginning of the worksheet.

More or fewer cells Hold down SHIFT while you click the last cell that you want to include
 than the active in the new selection. The rectangular range between the active
selection cell (active cell: The selected cell in which data is entered when you
begin typing. Only one cell is active at a time. The active cell is
bounded by a heavy border.) and the cell that you click becomes the
new selection.

Tip To cancel a selection of cells, click any cell on the worksheet.

On the Home tab, in the Editing group, click Find & Select, and then click Go To.

In the Go To dialog box, click Special.

Under Select, click Visible cells only, and then click OK.

On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut You can also press CTRL+C.

Select the upper-left cell of the paste area.

Tip To move or copy a selection to a different worksheet or workbook, click another

worksheet tab or switch to another workbook, and then select the upper-left cell of
the paste area.

On the Home tab, in the Clipboard group, click Paste .

Keyboard shortcut You can also press CTRL+V.

Notes

Excel pastes the copied data into consecutive rows or columns. If the paste area
contains hidden rows or columns, you might need to unhide the paste area to see
all of the copied cells.

If you click the arrow below Paste , you can choose from several paste options to
apply to your selection.

When you copy or paste hidden or filtered (filter: To display only the rows in a list
that satisfy the conditions you specify. You use the AutoFilter command to display
rows that match one or more specific values, calculated values, or conditions.) data
to another application or another instance of Excel, only visible cells are copied.

Top of Page

Prevent copied blank cells from replacing data

Select the range (range: Two or more cells on a sheet. The cells in a range can be
adjacent or nonadjacent.) of cells that contains blank cells.

How to select cells or ranges

To select Do this

A single cell Click the cell, or press the arrow keys to move to the cell.

A range of cells Click the first cell in the range, and then drag to the last cell, or hold
down SHIFT while you press the arrow keys to extend the selection.

You can also select the first cell in the range, and then press F8 to
extend the selection by using the arrow keys. To stop extending the
selection, press F8 again.

A large range of cells Click the first cell in the range, and then hold down SHIFT while you
click the last cell in the range. You can scroll to make the last cell
visible.

All cells on a Click the Select All button.

worksheet

To select the entire worksheet, you can also press CTRL+A.

Note If the worksheet contains data, CTRL+A selects the current

region. Pressing CTRL+A a second time selects the entire worksheet.

Nonadjacent cells or Select the first cell or range of cells, and then hold down CTRL while
cell ranges you select the other cells or ranges.

You can also select the first cell or range of cells, and then press
SHIFT+F8 to add another nonadjacent cell or range to the selection.
To stop adding cells or ranges to the selection, press SHIFT+F8 again.

Note You cannot cancel the selection of a cell or range of cells in a

nonadjacent selection without canceling the entire selection.
An entire row or Click the row or column heading.
column

Row heading

Column heading

You can also select cells in a row or column by selecting the first cell
and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT
ARROW for rows, UP ARROW or DOWN ARROW for columns).

Note If the row or column contains data, CTRL+SHIFT+ARROW key

selects the row or column to the last used cell. Pressing
CTRL+SHIFT+ARROW key a second time selects the entire row or
column.

Adjacent rows or Drag across the row or column headings. Or select the first row or
columns column; then hold down SHIFT while you select the last row or
column.

Nonadjacent rows or Click the column or row heading of the first row or column in your
columns selection; then hold down CTRL while you click the column or row
headings of other rows or columns that you want to add to the
selection.

The first or last cell in Select a cell in the row or column, and then press CTRL+ARROW key
a row or column (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN
ARROW for columns).

The first or last cell on Press CTRL+HOME to select the first cell on the worksheet or in an
a worksheet or in a Excel list.
Microsoft Office Excel
table Press CTRL+END to select the last cell on the worksheet or in an
Excel list that contains data or formatting.

Cells to the last used Select the first cell, and then press CTRL+SHIFT+END to extend the
cell on the worksheet selection of cells to the last used cell on the worksheet (lower-right
(lower-right corner) corner).

Cells to the beginning Select the first cell, and then press CTRL+SHIFT+HOME to extend the
of the worksheet selection of cells to the beginning of the worksheet.

More or fewer cells Hold down SHIFT while you click the last cell that you want to include
 than the active in the new selection. The rectangular range between the active
selection cell (active cell: The selected cell in which data is entered when you
begin typing. Only one cell is active at a time. The active cell is
bounded by a heavy border.) and the cell that you click becomes the
new selection.

Tip To cancel a selection of cells, click any cell on the worksheet.

On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut You can also press CTRL+C.

Select the upper-left cell of the paste area.

On the Home tab, in the Clipboard group, click the arrow below Paste , and then
click Paste Special.

Select the Skip blanks check box.

Top of Page

Move or copy the contents of a cell

Double-click the cell that contains the data that you want to move or copy.

Note By default, you can edit and select cell data directly in the cell by double-
clicking it, but you can also edit and select cell data in the formula bar (formula bar:
A bar at the top of the Excel window that you use to enter or edit values or formulas
in cells or charts. Displays the constant value or formula stored in the active cell.).

In the cell, select the characters that you want to move or copy.

How to select characters in a cell

To select the contents of a

cell Do this

In the cell Double-click the cell, and then drag across the contents of the
cell that you want to select.

In the formula bar (formula Click the cell, and then drag across the contents of the cell that
bar: A bar at the top of the you want to select in the formula bar.
Excel window that you use
 to enter or edit values or
formulas in cells or charts.
Displays the constant value
or formula stored in the
active cell.)

By using the keyboard Press F2 to edit the cell, use the arrow keys to position the
insertion point, and then press SHIFT+ARROW key to select the
contents.

On the Home tab, in the Clipboard group, do one of the following:

To move the selection, click Cut .

Keyboard shortcut You can also press CTRL+X.

To copy the selection, click Copy .

Keyboard shortcut You can also press CTRL+C.

In the cell, click where you want to paste the characters, or double-click another cell
to move or copy the data.

On the Home tab, in the Clipboard group, click Paste .

Keyboard shortcut You can also press CTRL+V.

Press ENTER.

Note When you double-click a cell or press F2 to edit the active cell, the arrow
keys work only within that cell. To use the arrow keys to move to another cell, first
press ENTER to complete your editing changes to the active cell.

Top of Page

Copy cell values, cell formats, or formulas only

When you paste copied data, you can do any of the following:

Convert any formulas in the cell to the calculated values without overwriting the
existing formatting.

Paste only the cell formatting, such as font color or fill color (and not the contents of
the cells).
Paste only the formulas (and not the calculated values).

Select the cell or range of cells that contains the values, cell formats, or formulas
that you want to copy.

How to select cells or ranges

To select Do this

A single cell Click the cell, or press the arrow keys to move to the cell.

A range of cells Click the first cell in the range, and then drag to the last cell, or hold
down SHIFT while you press the arrow keys to extend the selection.

You can also select the first cell in the range, and then press F8 to
extend the selection by using the arrow keys. To stop extending the
selection, press F8 again.

A large range of cells Click the first cell in the range, and then hold down SHIFT while you
click the last cell in the range. You can scroll to make the last cell
visible.

All cells on a Click the Select All button.

worksheet

To select the entire worksheet, you can also press CTRL+A.

Note If the worksheet contains data, CTRL+A selects the current

region. Pressing CTRL+A a second time selects the entire worksheet.

Nonadjacent cells or Select the first cell or range of cells, and then hold down CTRL while
cell ranges you select the other cells or ranges.

You can also select the first cell or range of cells, and then press
SHIFT+F8 to add another nonadjacent cell or range to the selection.
To stop adding cells or ranges to the selection, press SHIFT+F8 again.

Note You cannot cancel the selection of a cell or range of cells in a

nonadjacent selection without canceling the entire selection.

An entire row or Click the row or column heading.

column
 Row heading

Column heading

You can also select cells in a row or column by selecting the first cell
and then pressing CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT
ARROW for rows, UP ARROW or DOWN ARROW for columns).

Note If the row or column contains data, CTRL+SHIFT+ARROW key

selects the row or column to the last used cell. Pressing
CTRL+SHIFT+ARROW key a second time selects the entire row or
column.

Adjacent rows or Drag across the row or column headings. Or select the first row or
columns column; then hold down SHIFT while you select the last row or
column.

Nonadjacent rows or Click the column or row heading of the first row or column in your
columns selection; then hold down CTRL while you click the column or row
headings of other rows or columns that you want to add to the
selection.

The first or last cell in Select a cell in the row or column, and then press CTRL+ARROW key
a row or column (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN
ARROW for columns).

The first or last cell on Press CTRL+HOME to select the first cell on the worksheet or in an
a worksheet or in a Excel list.
Microsoft Office Excel
table Press CTRL+END to select the last cell on the worksheet or in an
Excel list that contains data or formatting.

Cells to the last used Select the first cell, and then press CTRL+SHIFT+END to extend the
cell on the worksheet selection of cells to the last used cell on the worksheet (lower-right
(lower-right corner) corner).

Cells to the beginning Select the first cell, and then press CTRL+SHIFT+HOME to extend the
of the worksheet selection of cells to the beginning of the worksheet.

More or fewer cells Hold down SHIFT while you click the last cell that you want to include
than the active in the new selection. The rectangular range between the active
selection cell (active cell: The selected cell in which data is entered when you
 begin typing. Only one cell is active at a time. The active cell is
bounded by a heavy border.) and the cell that you click becomes the
new selection.

Tip To cancel a selection of cells, click any cell on the worksheet.

On the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut You can also press CTRL+C.

Select the upper-left cell of the paste area or the cell where you want to paste the
value, cell format, or formula.

On the Home tab, in the Clipboard group, click the arrow below Paste , and then
do one of the following:

To paste values only, click Paste Values.

To paste cell formats only, click Paste Special, and then click Formats under Paste.

To paste formulas only, click Formulas.

Note If the copied formulas contain relative cell references, Excel adjusts the
references (and the relative parts of mixed cell references) in the duplicate
formulas. For example, suppose that cell B8 contains the formula =SUM(B1:B7). If
you copy the formula to cell C8, the duplicate formula refers to the corresponding
cells in that column: =SUM(C1:C7). If the copied formulas contain absolute cell
references (absolute cell reference: In a formula, the exact address of a cell,
regardless of the position of the cell that contains the formula. An absolute cell
reference takes the form $A$1.), the references in the duplicate formulas are not
changed. If you don't get the results that you want, you can also change the
references in the original formulas to either relative or absolute cell references and
then recopy the cells.

Move or copy rows and columns

1. Select the row or column that you want to move or copy.

How to select cells, ranges, rows, or columns

To select Do this
A single cell Click the cell, or press the arrow keys to move to the cell.

A range of cells Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow
keys to extend the selection.

You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow
keys. To stop extending the selection, press F8 again.

A large range of cells Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can
scroll to make the last cell visible.

All cells on a worksheet Click the Select All button.

To select the entire worksheet, you can also press CTRL+A.

NOTE If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second
time selects the entire worksheet.

Nonadjacent cells or cell Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
ranges
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent
cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.

NOTE You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without
canceling the entire selection.

An entire row or column Click the row or column heading.

Row heading

Column heading

You can also select cells in a row or column by selecting the first cell and then pressing
CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for
columns).

NOTE If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the
last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.

Adjacent rows or columns Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you
select the last row or column.

Nonadjacent rows or Click the column or row heading of the first row or column in your selection; then hold down CTRL while
columns you click the column or row headings of other rows or columns that you want to add to the selection.
 The first or last cell in a Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for
row or column rows, UP ARROW or DOWN ARROW for columns).

The first or last cell on a Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
worksheet or in a
Microsoft Office Excel
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or
table
formatting.

Cells to the last used cell Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell
on the worksheet (lower- on the worksheet (lower-right corner).
right corner)

Cells to the beginning of Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of
the worksheet the worksheet.

More or fewer cells than Hold down SHIFT while you click the last cell that you want to include in the new selection. The rectangular
the active selection range between the active cell (active cell: The selected cell in which data is entered when you begin
typing. Only one cell is active at a time. The active cell is bounded by a heavy border.) and the cell that
you click becomes the new selection.

Do one of the following:

 To move rows or columns, on the Home tab, in the Clipboard group, click Cut .

Keyboard shortcut You can also press CTRL+X.

 To copy rows or columns, on the Home tab, in the Clipboard group, click Copy .

Keyboard shortcut You can also press CTRL+C.

2. Right-click a row or column below or to the right of where you want to move or copy your

selection, and then do one of the following:

 When you are moving rows or columns, click Insert Cut Cells on the shortcut menu.

 When you are copying rows or columns, click Insert Copied Cells on the shortcut

menu.

NOTE If you click Paste on the Home tab, in the Clipboard group (or press CTRL+V)

instead of clicking a command on the shortcut menu, you will replace any contents of the

destination cells.
 Top of Page

Move or copy rows and columns by using the

mouse

1. Select the row or column that you want to move or copy.

How to select cells, ranges, rows, or columns

To select Do this

A single cell Click the cell, or press the arrow keys to move to the cell.

A range of cells Click the first cell in the range, and then drag to the last cell, or hold down SHIFT while you press the arrow
keys to extend the selection.

You can also select the first cell in the range, and then press F8 to extend the selection by using the arrow
keys. To stop extending the selection, press F8 again.

A large range of cells Click the first cell in the range, and then hold down SHIFT while you click the last cell in the range. You can
scroll to make the last cell visible.

All cells on a worksheet Click the Select All button.

To select the entire worksheet, you can also press CTRL+A.

NOTE If the worksheet contains data, CTRL+A selects the current region. Pressing CTRL+A a second
time selects the entire worksheet.

Nonadjacent cells or cell Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges.
ranges
You can also select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent
cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.

NOTE You cannot cancel the selection of a cell or range of cells in a nonadjacent selection without
canceling the entire selection.

An entire row or column Click the row or column heading.

Row heading
 Column heading

You can also select cells in a row or column by selecting the first cell and then pressing
CTRL+SHIFT+ARROW key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for
columns).

NOTE If the row or column contains data, CTRL+SHIFT+ARROW key selects the row or column to the
last used cell. Pressing CTRL+SHIFT+ARROW key a second time selects the entire row or column.

Adjacent rows or columns Drag across the row or column headings. Or select the first row or column; then hold down SHIFT while you
select the last row or column.

Nonadjacent rows or Click the column or row heading of the first row or column in your selection; then hold down CTRL while
columns you click the column or row headings of other rows or columns that you want to add to the selection.

The first or last cell in a Select a cell in the row or column, and then press CTRL+ARROW key (RIGHT ARROW or LEFT ARROW for
row or column rows, UP ARROW or DOWN ARROW for columns).

The first or last cell on a Press CTRL+HOME to select the first cell on the worksheet or in an Excel list.
worksheet or in a
Microsoft Office Excel
Press CTRL+END to select the last cell on the worksheet or in an Excel list that contains data or
table
formatting.

Cells to the last used cell Select the first cell, and then press CTRL+SHIFT+END to extend the selection of cells to the last used cell
on the worksheet (lower- on the worksheet (lower-right corner).
right corner)

Cells to the beginning of Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of
the worksheet the worksheet.

More or fewer cells than Hold down SHIFT while you click the last cell that you want to include in the new selection. The rectangular
the active selection range between the active cell (active cell: The selected cell in which data is entered when you begin
typing. Only one cell is active at a time. The active cell is bounded by a heavy border.) and the cell that
you click becomes the new selection.

Do one of the following:

 To move rows or columns, point to the border of the selection. When the pointer

becomes a move pointer , drag the rows or columns to another location.

 To copy rows or columns, hold down CTRL while you point to the border of the selection.

When the pointer becomes a copy pointer , drag the rows or columns to another

location.

IMPORTANT Make sure that you hold down CTRL during the entire drag-and-drop

operation. If you release CTRL before you release the mouse button, you will move the

rows or columns instead of copying them.

NOTES
When you use the mouse to insert copied or cut columns or rows, the existing contents of the

destination cells is replaced. To insert copied or cut rows and columns without replacing the

existing contents, you should right-click the row or column below or to the right of where you

want to move or copy your selection, and then click Insert Cut Cells or Insert Copied Cells

on the shortcut menu.

You cannot move or copy nonadjacent rows and columns by using the mouse.

Paste Special when copying from Excel

Use the Paste Special dialog to copy complex items from a Microsoft Office Excel worksheet

and paste them into the same worksheet or another Excel worksheet using only specific

attributes of the copied data, or a mathematical operation that you want to apply to the

copied data.

In this article

Paste

Operation

Paste
All Pastes all cell contents and formatting of the copied data.

Formulas Pastes only the formulas of the copied data as entered in the formula bar.

Values Pastes only the values of the copied data as displayed in the cells.

Formats Pastes only cell formatting of the copied data.

Comments Pastes only comments attached to the copied cell.

Validation Pastes data validation rules for the copied cells to the paste area.

All using Source theme Pastes all cell contents in the document theme formatting that is

applied to the copied data.

All except borders Pastes all cell contents and formatting applied to the copied cell except

borders.

Column widths Pastes the width of one copied column or range of columns to another

column or range of columns.

Formulas and number formats Pastes only formulas and all number formatting options

from the copied cells.

 Values and number formats Pastes only values and all number formatting options from

the copied cells.

Top of Page
Operation
Specify which mathematical operation, if any, that you want to apply to the copied data.

None Specifies that no mathematical operation will be applied to the copied data.

Add Specifies that the copied data will be added to the data in the destination cell or range

of cells.

Subtract Specifies that the copied data will be subtracted from the data in the destination

cell or range of cells.

Multiply Specifies that the copied data will be multiplied with the data in the destination

cell or range of cells.

Divide Specifies that the copied data will be divided by the data in the destination cell or

range of cells.

Skip blanks Avoids replacing values in your paste area when blank cells occur in the copy

area when you select this check box.

Transpose Changes columns of copied data to rows, and vice versa when you select this

check box.

Paste Link Links the pasted data on the active worksheet to the copied data.

Save a file
Show All

When you save a file, you can save it to a folder on your hard disk drive, a network location, disk,

CD, the desktop, or another storage location. You need to identify the target location in the Save in

list. Otherwise, the saving process is the same, no matter what location you choose.

What do you want to do?

Save a file

Save a copy of a file

Save a file to another format

Save AutoRecover information automatically

Save a file
Do the following in these 2007 Microsoft Office system programs:

Word, Excel, or PowerPoint

Click the Microsoft Office Button , and then click Save.

Keyboard shortcut To save the file, press CTRL+S.

InfoPath, OneNote, Project, Publisher, or Visio

On the File menu, click Save.

Keyboard shortcut To save the file, press CTRL+S.

NOTE If you are saving the file for the first time, you are asked to give it a name.

Top of Page

Save a copy of a file

1. Do the following in these 2007 Microsoft Office system programs:

Word, Excel, or PowerPoint

 Click the Microsoft Office Button , and then click Save As.

InfoPath, OneNote, Project, Publisher, or Visio

 On the File menu, click Save As.

2. In the Save in list, click the folder or drive to which you want to save.

TIP To save the copy in a different folder, click a different drive in the Save in list or a

different folder in the folder list. To save the copy in a new folder, click Create New Folder

3. In the File name box, enter a new name for the file.

4. Click Save.
 Top of Page

Save a file to another format

1. Do the following in these 2007 Microsoft Office system programs:

Word, Excel, or PowerPoint

 Click the Microsoft Office Button , and then click Save As.

InfoPath, OneNote, Project, Publisher, or Visio

 On the File menu, click Save As.

2. In the File name box, enter a new name for the file.

3. In the Save as type list, click the file format that you want to save the file in.

4. Click Save.

1. On the Page Layout tab, in the Page Setup group, click the Page Setup Dialog
Box Launcher.
2. In the Page Setup dialog box, click the Sheet tab.
3. Click Options.
4. On the Paper/Quality tab, under Tray Selection, click the paper source that you
want.

The following are the buttons and other options in the Print dialog box:

Name In the list, click a printer that you have installed and want to use.

Status For the selected printer, tells the status of the printer, such as idle, busy, or the

number of files before your print job.

Type For the selected printer, tells the type of printer that you selected, such as laser.

Where For the selected printer, tells the location of the printer or which port that the printer

is connected to.

Comment For the selected printer, tells any additional information that you might need to

know about the printer.

 Properties Click this button when you want to change the properties, such as paper type,

for the printer that you are using.

Find Printer Click this button to find a printer that you have access to.

Print to file Select this check box to create a file from the document instead of routing it

directly to a printer. The file is saved with print formatting, such as font selection and color

specification, in a .prn file that can be printed to another printer.

Number of copies In this list, click the number of copies that you want printed.

Collate Select this check box if you want your print job to be kept in the order that you

created in your document when printed. This check box is available if you are printing multiple

copies.

Selection Click this option to print only what you have selected.

Active sheet(s) Click this option to print only the active worksheets.

Entire workbook Click this option to print the entire workbook, including all the worksheets

in the workbook.

Table Click this option to print only the table in the worksheet.

Ignore print areas Select this check box to ignore any print areas that you might

have specified.

All Click this option if you want to print all pages in your file.

Pages Click this option and add the page numbers or page ranges in the box.

Preview Click this button to preview your worksheet before printing it and select other

printing options, such as page setup.

OK Click this button to send your document to the printer.

Cancel Click this button to go back to your document without printing it.

Add or change the header or footer text

For worksheets, you can work with headers and footers in Page Layout view. For other sheet types,

such as chart sheets (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is

beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a

PivotTable report.), or for embedded charts (embedded chart: A chart that is placed on a worksheet

rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print

a chart or a PivotChart report with its source data or other information in a worksheet.), you can work

with headers and footers in the Page Setup dialog box.

Add or change the header or footer text for a worksheet in

Page Layout view

1. Click the worksheet to which you want to add headers or footers, or that contains headers or

footers that you want to change.

2. On the Insert tab, in the Text group, click Header & Footer.

NOTE Excel displays the worksheet in Page Layout view. You can also click Page Layout

View on the status bar to display this view.

3. Do one of the following:

 To add a header or footer, click the left, center, or right header or footer text box at the

top or at the bottom of the worksheet page.

 To change a header or footer, click the header or footer text box at the top or at the

bottom of the worksheet page that contains header or footer text, and then select the

text that you want to change.

4. Type the text that you want.

TIP To return to Normal view, on the View tab, in the Workbook Views group, click

Normal. You can also click Normal on the status bar.

NOTES
 To start a new line in a header or footer text box, press ENTER.

To delete a portion of a header or footer, select the portion that you want to delete in the

header or footer text box, and then press DELETE or BACKSPACE. You can also click in the text

and then press BACKSPACE to delete the preceding characters.

To include a single ampersand (&) within the text of a header or footer, use two ampersands.

For example, to include "Subcontractors & Services" in a header, type Subcontractors &&

Services.

To close the headers or footers, click anywhere in the worksheet, or press ESC.

Add or change the header or footer text for a chart

1. Click the chart sheet or embedded chart to which you want to add headers or footers, or that

contains headers or footers that you want to change.

2. On the Insert tab, in the Text group, click Header & Footer.

NOTE Excel displays the Page Setup dialog box.

3. Click Custom Header or Custom Footer.

4. Click in the Left section, Center section, or Right section box, and then click the buttons

to insert the header or footer information that you want in that section.

5. To add or change the header or footer text, type additional text or edit the existing text in the

Left section, Center section, or Right section box.

NOTES

To start a new line in a section box, press ENTER.

 To delete a portion of a header or footer, select the portion that you want to delete in the

section box, and then press DELETE or BACKSPACE. You can also click in the text and then

press BACKSPACE to delete the preceding characters.

To include a single ampersand (&) within the text of a header or footer, use two ampersands.

For example, to include "Subcontractors & Services" in a header, type Subcontractors &&

Services.

To base a custom header or footer on an existing header or footer, click the header or footer

in the Header or Footer box.

Top of Page

Add a predefined header or footer

For worksheets, you can work with headers and footers in Page Layout view. For other sheet types,

such as chart sheets (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is

beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a

PivotTable report.), or for embedded charts (embedded chart: A chart that is placed on a worksheet

rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print

a chart or a PivotChart report with its source data or other information in a worksheet.), you can work

with the headers and footers in the Page Setup dialog box.

Add a predefined header or footer to a worksheet in Page

Layout view

1. Click the worksheet to which you want to add a predefined header or footer.

2. On the Insert tab, in the Text group, click Header & Footer.

NOTE Excel displays the worksheet in Page Layout view. You can also click Page Layout

View on the status bar to display this view.

3. Click the left, center, or right header or footer text box at the top or at the bottom of the

worksheet page.

TIP Clicking any text box selects the header or footer and displays the Header and Footer

Tools, adding the Design tab.

4. On the Design tab, in the Header & Footer group, click Header or Footer, and then click

the predefined header or footer that you want.

TIP To return to Normal view, on the View tab, in the Workbook Views group, click

Normal. You can also click Normal on the status bar.

Add a predefined header or footer to a chart

1. Click the chart sheet or embedded chart to which you want to add a predefined header or

footer.

2. On the Insert tab, in the Text group, click Header & Footer.

NOTE Excel displays the Page Setup dialog box.

3. Click the predefined header or footer in the Header or Footer box.

Top of Page

Insert specific elements in a header or footer

For worksheets, you can work with headers and footers in Page Layout view. For other sheet types,

such as chart sheets (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is

beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a

PivotTable report.), or for embedded charts (embedded chart: A chart that is placed on a worksheet

rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print

a chart or a PivotChart report with its source data or other information in a worksheet.), you can work

with headers and footers in the Page Setup dialog box.

Insert specific header and footer elements for a worksheet

1. Click the worksheet to which you want to add specific header or footer elements.

2. On the Insert tab, in the Text group, click Header & Footer.

NOTE Excel displays the worksheet in Page Layout view. You can also click Page Layout

View on the status bar to display this view.

3. Click the left, center, or right header or footer text box at the top or at the bottom of the

worksheet page.

TIP Clicking any text box selects the header or footer and displays the Header and Footer

Tools, adding the Design tab.

4. On the Design tab, in the Header & Footer Elements group, click the element that you

want.

TIP To return to Normal view, on the View tab, in the Workbook Views group, click

Normal. You can also click Normal on the status bar.

Insert specific header and footer elements for a chart

1. Click the chart sheet or embedded chart to which you want to add a predefined header or

footer.

2. On the Insert tab, in the Text group, click Header & Footer.

NOTE Excel displays the Page Setup dialog box.

3. Click Custom Header or Custom Footer.

4. Use the buttons in the Header or Footer dialog box to insert specific header and footer

elements.

TIP When you rest the mouse pointer on a button, a ScreenTip displays the name of the

element that the button inserts.

Top of Page

Choose the header and footer options

For worksheets, you can work with headers and footers in Page Layout view. For other sheet types,

such as chart sheets (chart sheet: A sheet in a workbook that contains only a chart. A chart sheet is

beneficial when you want to view a chart or a PivotChart report separately from worksheet data or a

PivotTable report.), or for embedded charts (embedded chart: A chart that is placed on a worksheet

rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print

a chart or a PivotChart report with its source data or other information in a worksheet.), you can work

with headers and footers in the Page Setup dialog box.

Choose the header and footer options for a worksheet

1. Click the worksheet for which you want to choose header and footer options.

2. On the Insert tab, in the Text group, click Header & Footer.

NOTE Excel displays the worksheet in Page Layout view. You can also click Page Layout

View on the status bar to display this view.

3. Click the left, center, or right header or footer text box at the top or at the bottom of the

worksheet page.

TIP Clicking any text box selects the header or footer and displays the Header and Footer

Tools, adding the Design tab.

4. On the Design tab, in the Options group, select one or more of the following:
  To insert a different header or footer for odd pages on an odd page and for even pages

on an even page, select the Different Odd & Even Pages check box.

 To remove headers and footers from the first printed page, select the Different First

Page check box.

 To use the same font size and scaling as the worksheet, select the Scale with

Document check box. To make the font size and scaling of the headers or footers

independent of the worksheet scaling for a consistent display on multiple pages, clear

this check box.

 To make sure that the header margin or footer margin is aligned with the left and right

margins of the worksheet, select the Align with Page Margins check box. To set the

left and right margins of the headers and footers to a specific value that is independent

of the left and right margins of the worksheet, clear this check box.

TIP To return to Normal view, on the View tab, in the Workbook Views group, click

Normal. You can also click Normal on the status bar.

Choose the header and footer options for a chart

1. Click the chart sheet or embedded chart to which you want to add a predefined header or

footer.

2. On the Insert tab, in the Text group, click Header & Footer.

NOTE Excel displays the Page Setup dialog box.

3. Select one or more of the following:

 To insert a different header or footer for odd pages on an odd page and for even pages

on an even page, select the Different Odd & Even Pages check box.
  To remove headers and footers from the first printed page, select the Different First

Page check box.

 To use the same font size and scaling as the worksheet, select the Scale with

Document check box. To make the font size and scaling of the headers or footers

independent of the worksheet scaling for a consistent display on multiple pages, clear

this check box.

 To make sure that the header margin or footer margin is aligned with the left and right

margins of the worksheet, select the Align with Page Margins check box. To set the

left and right margins of the headers and footers to a specific value that is independent

of the left and right margins of the worksheet, clear this check box.

filter and sort

To determine if a filter is applied, note the icon in the column heading:

A drop-down arrow means that filtering is enabled but not applied.

TIP When you hover over the heading of a column with filtering enabled but not applied, a

screen tip displays "(Showing All)".

A Filter button means that a filter is applied.

TIP When you hover over the heading of a filtered column, a screen tip displays the filter

applied to that column, such as "Equals a red cell color" or "Larger than 150".

When you reapply a filter or sort, different results appear for the following reasons:

Data has been added, modified, or deleted to the range of cells or table column.

The filter is a dynamic date and time filter, such as Today, This Week, or Year to Date.

Values returned by a formula have changed and the worksheet has been recalculated.

NOTE When you use the Find dialog box to search filtered data, only the data that is displayed is

searched; data that is not displayed is not searched. To search all the data, clear all filters.

Top of Page
Reapply a filter or sort

NOTE For a table, filter and sort criteria are saved with the workbook so that you can reapply both

the filter and sort each time that you open the workbook. However, for a range of cells, only filter

criteria are saved with a workbook, not sort criteria. If you want to save sort criteria so that you can

periodically reapply a sort when you open a workbook, then it's a good idea to use a table. This is

especially important for multicolumn sorts or for sorts that take a long time to create.

To reapply a filter or sort, on the Home tab, in the Editing group, click Sort & Filter, and

then click Reapply.

Top of Page

Clear a filter for a column

To clear a filter for one column in a multicolumn range of cells or table, click the Filter button

on the heading, and then click Clear Filter from <Column Name>.

Top of Page

Clear all filters in a worksheet and redisplay all

rows

On the Home tab, in the Editing group, click Sort & Filter, and then click Clear.

Data sources that you can access

Show All

Microsoft Office provides drivers that you can use to retrieve data from the following data

sources (data source: A stored set of "source" information used to connect to a database. A data

source can include the name and location of the database server, the name of the database driver,

and information that the database needs when you log on.):
 Microsoft SQL Server Analysis Services (OLAP provider (OLAP provider: A set of software that

provides access to a particular type of OLAP database. This software can include a data source

driver and other client software that is necessary to connect to a database.))

Microsoft Office Access

dBASE

Microsoft FoxPro

Microsoft Office Excel

Oracle

Paradox

Text file databases

You can also use ODBC drivers (Open Database Connectivity (ODBC) driver: A program file used to

connect to a particular database. Each database program, such as Access or dBASE, or database

management system, such as SQL Server, requires a different driver.) or data source drivers (data

source driver: A program file used to connect to a specific database. Each database program or

management system requires a different driver.) from other manufacturers to retrieve information

from data sources that are not listed here, including other types of OLAP databases. For information

about installing an ODBC driver or data source driver that is not listed here, check the documentation

for the database, or contact your database vendor.

You can use the Data Connection Wizard to connect to an external data source that has already been

defined. To open the Data Connection Wizard, on the Data tab, in the Get External Data group, click

From Other Sources, and then click From Data Connection Wizard.

Overview of Excel tables

Show All

To make managing and analyzing a group of related data easier, you can turn a range (range: Two

or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells into a Microsoft

Office Excel table (previously known as an Excel list). A table is a series of rows and columns that

contains related data that is managed independently from the data in other rows and columns on the

worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also
 called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a

worksheet is always stored in a workbook.).

By default, every column in the table has filtering enabled in the header row so that you can filter or

sort your table data quickly. You can add a total row (total row: A special row in a list that provides a

selection of aggregate functions useful for working with numerical data.) to your table that provides a

drop-down list of aggregate functions for each total row cell. A sizing handle in the lower-right corner

of the table allows you to drag the table to the size that you want.

To manage several groups of data, you can insert more than one table in the same worksheet. You

cannot create a table in a shared workbook (shared workbook: A workbook set up to allow multiple

users on a network to view and make changes at the same time. Each user who saves the workbook

sees the changes made by other users.).

If you have access to and authoring permission on a Microsoft Windows SharePoint Services site, you

can use it to share a table with other users. By exporting table data to a SharePoint list, other people

can view, edit, and update the table data in the SharePoint list, and you can synchronize your table

data with these changes to keep the Excel table up to date. After exporting the table data to a

SharePoint list, you can open a SharePoint list in Excel as read-only — any changes that you want to

make can be made only to the data on the SharePoint site.

You can use the following features to manage table data:

Sorting and filtering Filter drop-down lists (drop-down list box: A control on a menu,

toolbar, or dialog box that displays a list of options when you click the small arrow next to the

list box.) are automatically added in the header row of a table. You can sort tables in

ascending or descending order or by color, or you can create a custom sort order. You can

filter tables to show only the data that meets the criteria that you specify, or you can filter by

color. For more information on how to filter or sort data, see Filter data or Sort data.
Formatting table data You can quickly format table data by applying a predefined or

custom table style. You can also choose Quick Styles options to display a table with or without

a header or a totals row, to apply row or column banding to make a table easier to read, or to

distinguish between the first or last columns and other columns in the table. For more

information on how to format table data, see Format an Excel table.

Inserting and deleting table rows and columns You can use one of several ways to add

rows and columns to a table. You can quickly add a blank row at the end of the table, include

adjacent worksheet rows or worksheet columns in the table, or insert table rows and table

columns anywhere that you want. You can delete rows and columns as needed. You can also

quickly remove rows that contain duplicate data from a table. For more information about

adding and deleting table rows and columns, see Add or remove Excel table rows and

columns.

Using a calculated column To use a single formula that adjusts for each row in a table,

you can create a calculated column. A calculated column automatically expands to include

additional rows so that the formula is immediately extended to those rows. For more

information on how to create a calculated column, see Create, edit, or remove a calculated

column in an Excel table.

Displaying and calculating table data totals You can quickly total the data in a table by

displaying a totals row at the end of the table and then using the functions that are provided

in drop-down lists for each totals row cell. For more information on how to display and

calculate table data totals, see Total the data in an Excel table.

Using structured references Instead of using cell references, such as A1 and R1C1, you

can use structured references that reference table names in a formula.

Ensuring data integrity For tables that are not linked to SharePoint lists, you can use the

built-in data validation features in Excel. For example, you may choose to allow only numbers

or dates in a column of a table. For more information on how to ensure data integrity, see

Prevent invalid data entry on a worksheet.

Exporting to a SharePoint list You can export a table to a SharePoint list so that other

people can view, edit, and update the table data.

 Examples of commonly used
formulas
Type of
Example
Formula

Conditional Create conditional formulas

Check if a number is greater than or less than another
number
Display or hide zero values
Hide error values and error indicators in a cell

Lookup Look up values in a range

Date and Time Add dates

Add times
Calculate the difference between two dates
Calculate the difference between two times
Count days before a date
Show dates as the day of the week
Insert the current date and time in a cell
Insert Julian dates

Financial Calculate a running balance

Calculate a compound annual growth rate (CAGR)

Statistical Calculate the average of numbers

Calculate the median of a group of numbers
Calculate the mode of a group of numbers

Math Add numbers

Subtract numbers
Multiply numbers
Divide numbers
Calculate percentages
Round a number
Raise a number to a power
Calculate the smallest or largest number in a range
 Calculate the factorial of a number
Create a multiplication table

Counting Count cells that contain numbers

Count nonblank cells
Count how often a value occurs
Count occurrences of values or unique values in a data range
Count numbers greater than or less than a number
Calculate a running total
Count all of the cells in a range
Count the number of words in a cell or range

Conversion Convert times

Convert dates stored as text to dates
Convert numbers stored as text to numbers
Convert measurements
Convert numbers to different number systems
Convert Arabic to Roman numerals

Text Change the case of text

Check if a cell contains text
Compare cell contents
Combine text and numbers
Combine text with a date or time
Combine first and last names
Combine two or more columns by using a function
Repeat a character in a cell
Display only the last four digits of identification numbers
Remove spaces from the beginning and end of a cell
Remove characters from text
Insert the current Excel file name in a cell
Split names by using Convert Text to Columns
Split text among columns by using functions

Overview of charting
Show All

In Microsoft Office Excel 2007, it is easy to create professional looking charts. Simply by choosing a

chart type, a chart layout, and a chart style—all of which are within easy reach on the new Office Excel

2007 Ribbon—you will have instant professional results every time that you create a chart. You can
 make it even easier by saving your favorite charts as a chart template that you can then quickly apply

whenever you create a new chart.

If you have Excel installed, you can also take advantage of the powerful Excel charting functionality in

other 2007 Microsoft Office system programs, such as Microsoft Office PowerPoint 2007 and Microsoft

Office Word 2007.

In this article

Creating charts in Excel

Creating charts in PowerPoint and Word

Modifying charts

Using predefined chart styles and chart layouts for a professional look

Adding eye-catching formatting to charts

Reusing charts by creating chart templates

Creating charts in Excel

To create a basic chart in Excel that you can modify and format later, you start by entering the data for

the chart on a worksheet. Then you simply select that data and choose the chart type that you want to

use on the Ribbon (Insert tab, Charts group).

 Worksheet data

Chart created from worksheet data

Excel supports numerous types of charts to help you display data in ways that are meaningful to your

audience. When you create a chart or change an existing chart, you can choose from a wide range of

chart types (such as a column chart or a pie chart) and their subtypes (such as a stacked column chart

or a pie in 3-D chart). You can also create a combination chart by using more than one chart type in

your chart.

Example of a combination chart that uses a column and line chart type.

For more information about how to create a chart in Excel, see Create a chart.

Creating charts in PowerPoint and Word

Charts are fully integrated with other 2007 Office release programs, such as Office PowerPoint 2007

and Office Word 2007. Both programs provide the same chart tools that are available in Excel. When

you have Excel installed, you can create Excel charts in PowerPoint and Word by clicking the Chart

button on the Ribbon (Insert tab, Illustrations group), and then by using the chart tools to modify or
format the chart. Charts that you create will be embedded in Office PowerPoint 2007 and Office Word

2007, and the chart data is stored in an Excel worksheet that is incorporated in the PowerPoint or Word

file.

NOTE If you work in Compatibility Mode in Word, you can insert a chart by using Microsoft Graph

instead of Excel. In PowerPoint, you can always use Excel to create a chart.

You can also copy a chart from Excel to PowerPoint 2007 and Word 2007. When you copy a chart, it

can either be embedded as static data or linked to the workbook. For a chart that is linked to a

workbook that you have access to, you can specify that it automatically checks for changes in the

linked workbook whenever the chart is opened.

For more information about how to create a chart in PowerPoint 2007 or Office Word 2007, see Use

charts and graphs in your presentation or Present data in a chart or graph.

Top of Page

Modifying charts

After you create a chart, you can modify it. For example, you may want to change the way that

axes (axis: A line bordering the chart plot area used as a frame of reference for measurement. The y

axis is usually the vertical axis and contains data. The x-axis is usually the horizontal axis and contains

categories.) are displayed, add a chart title, move or hide the legend, or display additional chart

elements.

To modify a chart, you can:

Change the display of chart axes You can specify the scale of axes and adjust the interval

between the values or categories that are displayed. To make your chart easier to read, you

can also add tick marks (tick marks and tick-mark labels: Tick marks are small lines of

measurement, similar to divisions on a ruler, that intersect an axis. Tick-mark labels identify

the categories, values, or series in the chart.) to an axis, and specify the interval at which they

will appear.

Add titles and data labels to a chart To help clarify the information that appears in your

chart, you can add a chart title, axis titles, and data labels (data label: A label that provides
 additional information about a data marker, which represents a single data point or value that

originates from a datasheet cell.).

Add a legend or data table You can show or hide a legend (legend: A box that identifies

the patterns or colors that are assigned to the data series or categories in a chart.) or change

its location. In some charts, you can also show a data table (data table: A range of cells that

shows the results of substituting different values in one or more formulas. There are two types

of data tables: one-input tables and two-input tables.) that displays the legend keys (legend

keys: Symbols in legends that show the patterns and colors assigned to the data series (or

categories) in a chart. Legend keys appear to the left of legend entries. Formatting a legend

key also formats the data marker that's associated with it.) and the values that are presented

in the chart.

Apply special options for each chart type Special lines (such as high-low lines and

trendlines (trendline: A graphic representation of trends in data series, such as a line sloping

upward to represent increased sales over a period of months. Trendlines are used for the

study of problems of prediction, also called regression analysis.)), bars (such as up-down bars

and error bars), data markers (data marker: A bar, area, dot, slice, or other symbol in a chart

that represents a single data point or value that originates from a worksheet cell. Related data

markers in a chart constitute a data series.), and other options are available for different chart

types.

Top of Page

Using predefined chart layouts and chart styles

for a professional look

Instead of manually adding or changing chart elements or formatting the chart, you can quickly apply

a predefined chart layout and chart style to your chart. Excel provides a variety of useful predefined

layouts and styles that you can choose from, but you can fine-tune a layout or style further if needed

by making manual changes to the layout and format of individual chart elements, such as the chart

area (chart area: The entire chart and all its elements.), plot area (plot area: In a 2-D chart, the area

bounded by the axes, including all data series. In a 3-D chart, the area bounded by the axes, including

the data series, category names, tick-mark labels, and axis titles.), data series (data series: Related

data points that are plotted in a chart. Each data series in a chart has a unique color or pattern and is

represented in the chart legend. You can plot one or more data series in a chart. Pie charts have only
one data series.), or legend (legend: A box that identifies the patterns or colors that are assigned to

the data series or categories in a chart.) of the chart.

When you apply a predefined chart layout, a specific set of chart elements (such as titles (titles in

charts: Descriptive text that is automatically aligned to an axis or centered at the top of a chart.), a

legend, a data table (data table: A range of cells that shows the results of substituting different values

in one or more formulas. There are two types of data tables: one-input tables and two-input tables.), or

data labels (data label: A label that provides additional information about a data marker, which

represents a single data point or value that originates from a datasheet cell.)) are displayed in a

specific arrangement in your chart. You can choose from a variety of layouts that are provided for each

chart type.

When you apply a predefined chart style, the chart is formatted based on the document

theme (theme: A combination of theme colors, theme fonts, and theme effects. A theme may be

applied to a file as a single selection.) that you have applied, so that your chart matches your

organization's or your own theme colors (theme colors: A set of colors that is used in a file. Theme

colors, theme fonts, and theme effects compose a theme.) (a set of colors), theme fonts (theme fonts:

A set of major and minor fonts that is applied to a file. Theme fonts, theme colors, and theme effects

compose a theme.) (a set of heading and body text fonts), and theme effects (theme effects: A set of

visual attributes that is applied to elements in a file. Theme effects, theme colors, and theme fonts

compose a theme.) (a set of lines and fill effects).

NOTE You cannot create your own chart layouts or styles, but you can create chart templates that

include the chart layout and formatting that you want. For more information about chart templates,

see Reusing charts by creating chart templates.

For more information about how to change the look of a chart, see Change the layout or style of a

chart.

Top of Page

Adding eye-catching formatting to charts

In addition to applying a predefined chart style, you can easily apply formatting to individual chart

elements such as data markers (data marker: A bar, area, dot, slice, or other symbol in a chart that

represents a single data point or value that originates from a worksheet cell. Related data markers in a

chart constitute a data series.), the chart area, the plot area, and the numbers and text in titles and
labels to give your chart a custom, eye-catching look. You can apply specific shape styles and WordArt

styles, but you can also format the shapes and text of chart elements manually.

To add formatting, you can:

Fill chart elements You can use colors, textures, pictures, and gradient fills to help draw

attention to specific chart elements.

Change the outline of chart elements You can use colors, line styles, and line weights to

emphasize chart elements.

Add special effects to chart elements You can apply special effects, such as shadow,

reflection, glow, soft edges, bevel, and 3-D rotation to chart element shapes, which gives your

chart a finished look.

Format text and numbers You can format text and numbers in titles, labels, and text

boxes on a chart as you would text and numbers on a worksheet. To make text and numbers

stand out, you can even apply WordArt styles.

For more information about how to format chart elements, see Format chart elements.

Top of Page

Reusing charts by creating chart templates

If you want to reuse a chart that you customized to meet your needs, you can save that chart as a

chart template (*.crtx) in the chart templates folder. When you create a chart, you can then apply the

chart template just like you would any other built-in chart type. In fact, chart templates are true chart

types, and you can also use them to change the chart type of an existing chart.

If you use a chart template frequently, you can save it as the default chart type.

NOTE Chart templates are not based on document themes (theme: A combination of theme colors,

theme fonts, and theme effects. A theme may be applied to a file as a single selection.). To use theme

colors (theme colors: A set of colors that is used in a file. Theme colors, theme fonts, and theme

effects compose a theme.), theme fonts (theme fonts: A set of major and minor fonts that is applied to

a file. Theme fonts, theme colors, and theme effects compose a theme.), and theme effects (theme

effects: A set of visual attributes that is applied to elements in a file. Theme effects, theme colors, and
theme fonts compose a theme.) in a chart that you create by using a chart template, you can apply a

style to the chart. A chart style resets the theme of a chart to the document theme. For more

information, see Using predefined chart styles and chart layouts for a professional look.

Set a password for a workbook

To allow only authorized reviewers to view or modify your data, you can help secure your entire

workbook file with a password.

1. Click the Microsoft Office Button , and then click Save As.

2. Click Tools, and then click General Options.

3. Do one or both of the following:

 If you want reviewers to enter a password before they can view the workbook, type a

password in the Password to open box.

 If you want reviewers to enter a password before they can save changes to the

workbook, type a password in the Password to modify box.

NOTES

 Password to open By default, this feature uses advanced encryption. Encryption is a

standard method used to help make your file more secure.

 Password to modify This feature does not use any encryption method. It is designed

so you can collaborate with content reviewers you trust. Is not designed to help make

your file more secure.

 Both passwords You can assign both passwords — one to access the file and one to

provide specific reviewers with permission to modify its content. Make sure each

password is different from the other.

IMPORTANT

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols.

Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password:
 House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or

more characters is better. For more information, see Help protect your personal information

with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot

retrieve it. Store the passwords that you write down in a secure place away from the

information that they help protect.

4. If you don't want content reviewers to accidentally modify the file, select the Read-only

recommended check box. When opening the file, reviewers will be asked whether or not

they want to open the file as read-only.

5. Click OK.

6. When prompted, retype your passwords to confirm them, and then click OK.

7. Click Save.

8. If prompted, click Yes to replace the existing workbook.

NOTE Securing an entire workbook with a password is separate from the workbook and

worksheet protection that you can set on the Review tab in the Changes group. For more

information about workbook and worksheet protection, see Change or remove protection of

worksheet or workbook elements.

[Link] excel lessons:

Overview of formulas
Show All

Formulas are equations that perform calculations on values in your worksheet. A formula starts with

an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result.
=5+2*3

A formula can also contain any or all of the following: functions (function: A prewritten formula that

takes a value or values, performs an operation, and returns a value or values. Use functions to simplify

and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.),

references, operators (operator: A sign or symbol that specifies the type of calculation to perform

within an expression. There are mathematical, comparison, logical, and reference operators.), and

constants (constant: A value that is not calculated and, therefore, does not change. For example, the

number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from

an expression, is not a constant.).

Parts of a formula

Functions: The PI() function returns the value of pi: 3.142...

References: A2 returns the value in cell A2.

Constants: Numbers or text values entered directly into a formula, such as 2.

Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator

multiplies.

In this article

Using constants in formulas

Using calculation operators in formulas

Using functions and nested functions in formulas

Using references in formulas

Using names in formulas

Using array formulas and array constants

Using constants in formulas

A constant is a value that is not calculated. For example, the date 10/9/2008, the number 210, and the

text "Quarterly Earnings" are all constants. An expression, or a value resulting from an expression, is

not a constant. If you use constant values in the formula instead of references to the cells (for

example, =30+70+110), the result changes only if you modify the formula yourself.

Top of Page

Using calculation operators in formulas

Operators specify the type of calculation that you want to perform on the elements of a formula. There

is a default order in which calculations occur, but you can change this order by using parentheses.

Types of operators

There are four different types of calculation operators: arithmetic, comparison, text concatenation, and

reference.

Arithmetic operators

To perform basic mathematical operations such as addition, subtraction, or multiplication; combine

numbers; and produce numeric results, use the following arithmetic operators.

Arithmetic Exampl
operator Meaning e

+ (plus sign) Addition 3+3

– (minus sign) Subtraction 3–1

Negation –1

* (asterisk) Multiplication 3*3

/ (forward slash) Division 3/3

% (percent sign) Percent 20%

^ (caret) Exponentiation 3^2

)

Comparison operators
You can compare two values with the following operators. When two values are compared by using

these oper

Exampl
Comparison operator Meaning e

= (equal sign) Equal to A1=B1

> (greater than sign) Greater than A1>B1

< (less than sign) Less than A1<B1

>= (greater than or equal to Greater than or equal A1>=B1

sign) to

<= (less than or equal to sign) Less than or equal to A1<=B1

<> (not equal to sign) Not equal to A1<>B1

ators, the result is a logical value either TRUE or FALSE.

Text concatenation operator

Use the ampersand (&) to join, or concatenate, one or more text strings to produce a single piece of

text.

Text
operator Meaning Example

& Connects, or concatenates, two values to produce one continuous text "North"&"wind
(ampersand) value "

Reference operators

Combine ranges of cells for calculations with the following operators.

Reference
operator Meaning Example

: (colon) Range operator, which produces one reference to all the cells between two references, B5:B15
including the two references

, (comma) Union operator, which combines multiple references into one reference SUM(B5:B15,D5:D15
)

(space) Intersection operator, which produces on reference to cells common to the two references B7:D7 C6:C8

The order in which Excel performs operations in formulas

In some cases, the order in which calculation is performed can affect the return value of the formula,

so it's important to understand how the order is determined and how you can change the order to

obtain desired results.

Calculation order

Formulas calculate values in a specific order. A formula in Excel always begins with an equal sign (=).

The equal sign tells Excel that the succeeding characters constitute a formula. Following the equal sign

are the elements to be calculated (the operands), which are separated by calculation operators. Excel

calculates the formula from left to right, according to a specific order for each operator in the formula.

Operator precedence

If you combine several operators in a single formula, Excel performs the operations in the order shown

in the following table. If a formula contains operators with the same precedence — for example, if a

formula contains both a multiplication and division operator — Excel evaluates the operators from left

to right.

Operator Description

: (colon) Reference operators

(single
space)

, (comma)

– Negation (as in –1)

% Percent

^ Exponentiation

* and / Multiplication and division

+ and – Addition and subtraction

& Connects two strings of text (concatenation)

= Comparison
<>
<=
>=
<>

Use of parentheses
To change the order of evaluation, enclose in parentheses the part of the formula to be calculated first.

For example, the following formula produces 11 because Excel calculates multiplication before

addition. The formula multiplies 2 by 3 and then adds 5 to the result.

=5+2*3

In contrast, if you use parentheses to change the syntax, Excel adds 5 and 2 together and then

multiplies the result by 3 to produce 21.

=(5+2)*3

In the example below, the parentheses around the first part of the formula force Excel to calculate

B4+25 first and then divide the result by the sum of the values in cells D5, E5, and F5.

=(B4+25)/SUM(D5:F5)

Top of Page

Using functions and nested functions in formulas

Functions are predefined formulas that perform calculations by using specific values, called

arguments, in a particular order, or structure. Functions can be used to perform simple or complex

calculations.

The syntax of functions

The following example of the ROUND function rounding off a number in cell A10 illustrates the syntax

of a function.

Structure of a function
 Structure. The structure of a function begins with an equal sign (=), followed by the function name,

an opening parenthesis, the arguments for the function separated by commas, and a closing

parenthesis.

Function name. For a list of available functions, click a cell and press SHIFT+F3.

Arguments. Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays (array:

Used to build single formulas that produce multiple results or that operate on a group of arguments

that are arranged in rows and columns. An array range shares a common formula; an array constant is

a group of constants used as an argument.), error values such as #N/A, or cell references (cell

reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of

the cell that appears at the intersection of column B and row 3 is B3.). The argument you designate

must produce a valid value for that argument. Arguments can also be constants (constant: A value

that is not calculated and, therefore, does not change. For example, the number 210, and the text

"Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a

constant.), formulas, or other functions.

Argument tooltip. A tooltip with the syntax and arguments appears as you type the function. For

example, type =ROUND( and the tooltip appears. Tooltips only appear for built-in functions.

Entering functions

When you create a formula that contains a function, the Insert Function dialog box helps you enter

worksheet functions. As you enter a function into the formula, the Insert Function dialog box displays

the name of the function, each of its arguments, a description of the function and each argument, the

current result of the function, and the current result of the entire formula.

To make it easier to create and edit formulas and minimize typing and syntax errors, use formula

autocomplete. After you type an = (equal sign) and beginning letters or a display trigger, Microsoft

Office Excel displays below the cell a dynamic drop down list of valid functions, arguments, and names

that match the letters or trigger. You can then insert an item in the drop-down list into the formula.

Nesting functions

In certain cases, you may need to use a function as one of the arguments (argument: The values that a

function uses to perform operations or calculations. The type of argument a function uses is specific to
the function. Common arguments that are used within functions include numbers, text, cell references,

and names.) of another function. For example, the following formula uses a nested AVERAGE function

and compares the result with the value 50.

The AVERAGE and SUM functions are nested within the IF function.

Valid returns When a nested function is used as an argument, it must return the same type of value

that the argument uses. For example, if the argument returns a TRUE or FALSE value, then the nested

function must return a TRUE or FALSE. If it doesn't, Microsoft Excel displays a #VALUE! error value.

Nesting level limits A formula can contain up to seven levels of nested functions. When Function B

is used as an argument in Function A, Function B is a second-level function. For instance, the AVERAGE

function and the SUM function are both second-level functions because they are arguments of the IF

function. A function nested within the AVERAGE function would be a third-level function, and so on.

Top of Page

Using references in formulas

A reference identifies a cell or a range of cells on a worksheet and tells Microsoft Excel where to look

for the values or data you want to use in a formula. With references, you can use data contained in

different parts of a worksheet in one formula or use the value from one cell in several formulas. You

can also refer to cells on other sheets in the same workbook, and to other workbooks. References to

cells in other workbooks are called links or external references (external reference: A reference to a

cell or range on a sheet in another Excel workbook, or a reference to a defined name in another

workbook.).

The A1 reference style

The default reference style By default, Excel uses the A1 reference style, which refers to columns

with letters (A through XFD, for a total of 16,384 columns) and refers to rows with numbers (1 through

1,048,576). These letters and numbers are called row and column headings. To refer to a cell, enter

the column letter followed by the row number. For example, B2 refers to the cell at the intersection of

column B and row 2.

 To refer to Use

The cell in column A and row 10 A10

The range of cells in column A and rows 10 through 20 A10:A20

The range of cells in row 15 and columns B through E B15:E15

All cells in row 5 5:5

All cells in rows 5 through 10 5:10

All cells in column H H:H

All cells in columns H through J H:J

The range of cells in columns A through E and rows 10 through A10:E20

20

Making a reference to another worksheet In the following example, the AVERAGE worksheet

function calculates the average value for the range B1:B10 on the worksheet named Marketing in the

same workbook.

Reference to a range of cells on another worksheet in the same workbook

Refers to the worksheet named Marketing

Refers to the range of cells between B1 and B10, inclusively

Separates the worksheet reference from the cell range reference

The difference between absolute, relative and mixed

references

Relative references A relative cell reference in a formula, such as A1, is based on the relative

position of the cell that contains the formula and the cell the reference refers to. If the position of the

cell that contains the formula changes, the reference is changed. If you copy or fill the formula across

rows or down columns, the reference automatically adjusts. By default, new formulas use relative
references. For example, if you copy or fill a relative reference in cell B2 to cell B3, it automatically

adjusts from =A1 to =A2.

Copied formula with relative reference

Absolute references An absolute cell reference in a formula, such as $A$1, always refer to a cell in

a specific location. If the position of the cell that contains the formula changes, the absolute reference

remains the same. If you copy or fill the formula across rows or down columns, the absolute reference

does not adjust. By default, new formulas use relative references, and you may need to switch them to

absolute references. For example, if you copy or fill an absolute reference in cell B2 to cell B3, it stays

the same in both cells =$A$1.

Copied formula with absolute reference

Mixed references A mixed reference has either an absolute column and relative row, or absolute

row and relative column. An absolute column reference takes the form $A1, $B1, and so on. An

absolute row reference takes the form A$1, B$1, and so on. If the position of the cell that contains the

formula changes, the relative reference is changed, and the absolute reference does not change. If you

copy or fill the formula across rows or down columns, the relative reference automatically adjusts, and

the absolute reference does not adjust. For example, if you copy or fill a mixed reference from cell A2

to B3, it adjusts from =A$1 to =B$1.

Copied formula with mixed reference

The 3-D reference style

Conveniently referencing multiple worksheets If you want to analyze data in the same cell or

range of cells on multiple worksheets within the workbook, use a 3-D reference. A 3-D reference

includes the cell or range reference, preceded by a range of worksheet names. Excel uses any

worksheets stored between the starting and ending names of the reference. For example,

=SUM(Sheet2:Sheet13!B5) adds all the values contained in cell B5 on all the worksheets between and

including Sheet 2 and Sheet 13.

You can use 3-D references to refer to cells on other sheets, to define names, and to create

formulas by using the following functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX,

MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and

VARPA.

3-D references cannot be used in array formulas (array formula: A formula that performs

multiple calculations on one or more sets of values, and then returns either a single result or

multiple results. Array formulas are enclosed between braces { } and are entered by pressing

CTRL+SHIFT+ENTER.).

3-D references cannot be used with the intersection operator (operator: A sign or symbol that

specifies the type of calculation to perform within an expression. There are mathematical,

comparison, logical, and reference operators.) (a single space) or in formulas that use implicit

intersection (implicit intersection: A reference to a range of cells, instead of a single cell, that

is calculated like a single cell. If cell C10 contains the formula =B5:B15*5, Excel multiplies the

value in cell B10 by 5 because cells B10 and C10 are in the same row.).

What happens when you move, copy, insert, or delete worksheets The following examples

explain what happens when you move, copy, insert, or delete worksheets that are included in a 3-D

reference. The examples use the formula =SUM(Sheet2:Sheet6!A2:A5) to add cells A2 through A5 on

worksheets 2 through 6.
 Insert or copy If you insert or copy sheets between Sheet2 and Sheet6 (the endpoints in

this example), Microsoft Excel includes all values in cells A2 through A5 from the added sheets

in the calculations.

Delete If you delete sheets between Sheet2 and Sheet6, Excel removes their values from

the calculation.

Move If you move sheets from between Sheet2 and Sheet6 to a location outside the

referenced sheet range, Excel removes their values from the calculation.

Move an endpoint If you move Sheet2 or Sheet6 to another location in the same workbook,

Excel adjusts the calculation to accommodate the new range of sheets between them.

Delete an endpoint If you delete Sheet2 or Sheet6, Excel adjusts the calculation to

accommodate the range of sheets between them.

The R1C1 reference style

You can also use a reference style where both the rows and the columns on the worksheet are

numbered. The R1C1 reference style is useful for computing row and column positions in

macros (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded

in the Visual Basic for Applications programming language.). In the R1C1 style, Excel indicates the

location of a cell with an "R" followed by a row number and a "C" followed by a column number.

Referenc
e Meaning

R[-2]C A relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that
contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative
reference takes the form A1.) to the cell two rows up and in the same column

R[2]C[2] A relative reference to the cell two rows down and two columns to the right

R2C2 An absolute reference (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell
that contains the formula. An absolute cell reference takes the form $A$1.) to the cell in the second row and in the second
column

R[-1] A relative reference to the entire row above the active cell

R An absolute reference to the current row

When you record a macro, Excel records some commands by using the R1C1 reference style. For

example, if you record a command such as clicking the AutoSum button to insert a formula that adds

a range of cells, Excel records the formula by using R1C1 style, not A1 style, references.
You can turn the R1C1 reference style on or off by setting or clearing the R1C1 reference style

check box under Working with formulas in the Formulas category of the Excel Settings under

the File menu.

Top of Page

Using names in formulas

You can create defined names (name: A word or string of characters that represents a cell, range of

cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to

understand ranges, such as Sales!C20:C30.) to represent cells, ranges of cells, formulas,

constant (constant: A value that is not calculated and, therefore, does not change. For example, the

number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from

an expression, is not a constant.) values, or Excel tables. A name is a meaningful shorthand that

makes it easier to understand the purpose of a cell reference (cell reference: The set of coordinates

that a cell occupies on a worksheet. For example, the reference of the cell that appears at the

intersection of column B and row 3 is B3.), constant (constant: A value that is not calculated. For

example, the number 210 and the text "Quarterly Earnings" are constants. An expression, or a value

resulting from an expression, is not a constant.), formula (formula: A sequence of values, cell

references, names, functions, or operators in a cell that together produce a new value. A formula

always begins with an equal sign (=).), or table (table: A collection of data about a particular subject

that is stored in records (rows) and fields (columns).), each of which may be difficult to comprehend at

first glance. The following information shows common examples of names and how they can improve

clarity and understanding.

Example
Type Example with no name Example with a name

Reference =SUM(C20:C30) =SUM(FirstQuarterSales)

Constant =PRODUCT(A5,8.3) =PRODUCT(Price,WASalesTax)

Formula =SUM(VLOOKUP(A1,B1:F20,5,FALSE), — =SUM(Inventory_Level,—

G5) Order_Amt)

Table C4:G36 =TopSales06

Types of names

There are several types of names you can create and use.
Defined name A name that represents a cell, range of cells, formula, or constant value. You can

create your own defined name, and Excel sometimes creates a defined name for you, such as when

you set a print area.

Table name A name for an Excel table, which is a collection of data about a particular subject that is

stored in records (rows) and fields (columns). Excel creates a default Excel table name of "Table1",

"Table2", and so on, each time you insert an Excel table, but you can change the name to make it

more meaningful. For more information on Excel tables, see Using structured references with Excel

tables.

Creating and entering names

You create a name by using the:

Name box on the formula bar This is best used for creating a workbook level name for a

selected range.

Create a name from selection You can conveniently create names from existing row and

column labels by using a selection of cells in the worksheet.

New Name dialog box This is best used for when you want more flexibility in creating

names, such as specifying a local worksheet level scope or creating a name comment.

NOTE By default, names use absolute cell references (absolute cell reference: In a formula, the exact

address of a cell, regardless of the position of the cell that contains the formula. An absolute cell

reference takes the form $A$1.).

You can enter a name by:

Typing Typing the name, for example, as an argument to a formula.

Using Formula AutoComplete Use the Formula AutoComplete drop-down list, where valid

names are automatically listed for you.

Selecting from the Use in Formula command Select a defined name from a list available

from the Use in Formula command in the Defined Names group on the Formula tab.
For more information, see Use names to clarify formulas.

Top of Page

Using array formulas and array constants

An array formula can perform multiple calculations and then return either a single result or multiple

results. Array formulas act on two or more sets of values known as array arguments. Each array

argument must have the same number of rows and columns. You create array formulas in the same

way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. Some

of the built-in functions are array formulas, and must be entered as arrays to get the correct results.

Array constants can be used in place of references when you don't want to enter each constant value

in a separate cell on the worksheet.

Using an array formula to calculate single and multiple

results

When you enter an array formula (array formula: A formula that performs multiple calculations on one

or more sets of values, and then returns either a single result or multiple results. Array formulas are

enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), Microsoft Excel

automatically inserts the formula between { } (braces).

To calculate a single result This type of array formula can simplify a worksheet model by replacing

several different formulas with a single array formula.

For example, the following calculates the total value of an array of stock prices and shares, without

using a row of cells to calculate and display the individual values for each stock.

Array formula that produces a single result

When you enter the formula ={SUM(B2:D2*B3:D3)} as an array formula, it multiples the Shares and

Price for each stock, and then adds the results of those calculations together.

To calculate multiple results Some worksheet functions return arrays of values, or require an

array of values as an argument. To calculate multiple results with an array formula, you must enter the

array into a range of cells that has the same number of rows and columns as the array arguments.

For example, given a series of three sales figures (in column B) for a series of three months (in column

A), the TREND function determines the straight-line values for the sales figures. To display all of the

results of the formula, it is entered into three cells in column C (C1:C3).

Array formula that produces multiple results

When you enter the formula =TREND(B1:B3,A1:A3) as an array formula, it produces three separate

results (22196, 17079, and 11962), based on the three sales figures and the three months.

Using array constants

In an ordinary formula, you can enter a reference to a cell containing a value, or the value itself, also

called a constant (constant: A value that is not calculated and, therefore, does not change. For

example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value

resulting from an expression, is not a constant.). Similarly, in an array formula you can enter a

reference to an array, or enter the array of values contained within the cells, also called an array

constant. Array formulas accept constants in the same way that nonarray formulas do, but you must

enter the array constants in a certain format.

Array constants can contain numbers, text, logical values such as TRUE or FALSE, or error values such

as #N/A. Different types of values can be in the same array constant — for example,

{1,3,4;TRUE,FALSE,TRUE}. Numbers in array constants can be in integer, decimal, or scientific format.

Text must be enclosed in double quotation marks — for example, "Tuesday".

Array constants cannot contain cell references, columns or rows of unequal length, formulas, or the

special characters $ (dollar sign), parentheses, or % (percent sign).

When you format array constants, make sure you:

Enclose them in braces ( { } ).

Separate values in different columns with commas (,). For example, to represent the values

10, 20, 30, and 40, enter {10,20,30,40}. This array constant is known as a 1-by-4 array and is

equivalent to a 1-row-by-4-column reference.

Separate values in different rows with semicolons (;). For example, to represent the values 10,

20, 30, and 40 in one row and 50, 60, 70, and 80 in the row immediately below, you would

enter a 2-by-4 array constant: {10,20,30,40;50,60,70,80}.

List of worksheet functions (by

category)
The following sections list all of the worksheet functions by category.

Add-in and Automation functions

Function Description

CALL Calls a procedure in a dynamic link library or code resource

EUROCONVERT Converts a number to euros, converts a number from euros to a euro member
currency, or converts a number from one euro member currency to another by
using the euro as an intermediary (triangulation)

GETPIVOTDATA Returns data stored in a PivotTable report

[Link] Returns the register ID of the specified dynamic link library (DLL) or code
resource that has been previously registered

[Link] Connects with an external data source and runs a query from a worksheet,
then returns the result as an array without the need for macro programming

Top of Page
Cube functions

Function Description

CUBEKPIMEMBER Returns a key performance indicator (KPI) name, property, and

measure, and displays the name and property in the cell. A KPI is a
quantifiable measurement, such as monthly gross profit or quarterly
employee turnover, used to monitor an organization's performance.

CUBEMEMBER Returns a member or tuple in a cube hierarchy. Use to validate that

the member or tuple exists in the cube.

CUBEMEMBERPROPERTY Returns the value of a member property in the cube. Use to validate
that a member name exists within the cube and to return the
specified property for this member.

CUBERANKEDMEMBER Returns the nth, or ranked, member in a set. Use to return one or
more elements in a set, such as the top sales performer or top 10
students.

CUBESET Defines a calculated set of members or tuples by sending a set

expression to the cube on the server, which creates the set, and
then returns that set to Microsoft Office Excel.

CUBESETCOUNT Returns the number of items in a set.

CUBEVALUE Returns an aggregated value from a cube.

Top of Page

Database functions

Function Description

DAVERAGE Returns the average of selected database entries

DCOUNT Counts the cells that contain numbers in a database

DCOUNTA Counts nonblank cells in a database

DGET Extracts from a database a single record that matches the specified criteria

DMAX Returns the maximum value from selected database entries

DMIN Returns the minimum value from selected database entries

 DPRODUCT Multiplies the values in a particular field of records that match the criteria in a
database

DSTDEV Estimates the standard deviation based on a sample of selected database entries

DSTDEVP Calculates the standard deviation based on the entire population of selected
database entries

DSUM Adds the numbers in the field column of records in the database that match the
criteria

DVAR Estimates variance based on a sample from selected database entries

DVARP Calculates variance based on the entire population of selected database entries

Top of Page

Date and time functions

Function Description

DATE Returns the serial number of a particular date

DATEVALUE Converts a date in the form of text to a serial number

DAY Converts a serial number to a day of the month

DAYS360 Calculates the number of days between two dates based on a 360-day year

EDATE Returns the serial number of the date that is the indicated number of months
before or after the start date

EOMONTH Returns the serial number of the last day of the month before or after a
specified number of months

HOUR Converts a serial number to an hour

MINUTE Converts a serial number to a minute

MONTH Converts a serial number to a month

NETWORKDAYS Returns the number of whole workdays between two dates

NOW Returns the serial number of the current date and time
 SECOND Converts a serial number to a second

TIME Returns the serial number of a particular time

TIMEVALUE Converts a time in the form of text to a serial number

TODAY Returns the serial number of today's date

WEEKDAY Converts a serial number to a day of the week

WEEKNUM Converts a serial number to a number representing where the week falls
numerically with a year

WORKDAY Returns the serial number of the date before or after a specified number of
workdays

YEAR Converts a serial number to a year

YEARFRAC Returns the year fraction representing the number of whole days between
start_date and end_date

Top of Page

Engineering functions

Function Description

BESSELI Returns the modified Bessel function In(x)

BESSELJ Returns the Bessel function Jn(x)

BESSELK Returns the modified Bessel function Kn(x)

BESSELY Returns the Bessel function Yn(x)

BIN2DEC Converts a binary number to decimal

BIN2HEX Converts a binary number to hexadecimal

BIN2OCT Converts a binary number to octal

COMPLEX Converts real and imaginary coefficients into a complex

number

CONVERT Converts a number from one measurement system to another

DEC2BIN Converts a decimal number to binary

DEC2HEX Converts a decimal number to hexadecimal

DEC2OCT Converts a decimal number to octal

DELTA Tests whether two values are equal

ERF Returns the error function

ERFC Returns the complementary error function

GESTEP Tests whether a number is greater than a threshold value

HEX2BIN Converts a hexadecimal number to binary

HEX2DEC Converts a hexadecimal number to decimal

HEX2OCT Converts a hexadecimal number to octal

IMABS Returns the absolute value (modulus) of a complex number

IMAGINARY Returns the imaginary coefficient of a complex number

IMARGUMENT Returns the argument theta, an angle expressed in radians

IMCONJUGATE Returns the complex conjugate of a complex number

IMCOS Returns the cosine of a complex number

IMDIV Returns the quotient of two complex numbers

IMEXP Returns the exponential of a complex number

IMLN Returns the natural logarithm of a complex number

IMLOG10 Returns the base-10 logarithm of a complex number

IMLOG2 Returns the base-2 logarithm of a complex number

IMPOWER Returns a complex number raised to an integer power

 IMPRODUCT Returns the product of from 2 to 29 complex numbers

IMREAL Returns the real coefficient of a complex number

IMSIN Returns the sine of a complex number

IMSQRT Returns the square root of a complex number

IMSUB Returns the difference between two complex numbers

IMSUM Returns the sum of complex numbers

OCT2BIN Converts an octal number to binary

OCT2DEC Converts an octal number to decimal

OCT2HEX Converts an octal number to hexadecimal

Financial functions

Function Description

Function Description

CELL Returns information about the formatting, location, or contents of a cell

[Link] Returns a number corresponding to an error type

INFO Returns information about the current operating environment

ISBLANK Returns TRUE if the value is blank

ISERR Returns TRUE if the value is any error value except #N/A

ISERROR Returns TRUE if the value is any error value

ISEVEN Returns TRUE if the number is even

ISLOGICAL Returns TRUE if the value is a logical value

ISNA Returns TRUE if the value is the #N/A error value

ISNONTEXT Returns TRUE if the value is not text

 ISNUMBER Returns TRUE if the value is a number

ISODD Returns TRUE if the number is odd

ISREF Returns TRUE if the value is a reference

ISTEXT Returns TRUE if the value is text

N Returns a value converted to a number

NA Returns the error value #N/A

TYPE Returns a number indicating the data type of a value

Top of Page

Logical functions

Function Description

AND Returns TRUE if all of its arguments are TRUE

FALSE Returns the logical value FALSE

IF Specifies a logical test to perform

NOT Reverses the logic of its argument

OR Returns TRUE if any argument is TRUE

TRUE Returns the logical value TRUE

Top of Page

Lookup and reference functions

Function Description

ADDRESS Returns a reference as text to a single cell in a worksheet

AREAS Returns the number of areas in a reference

CHOOSE Chooses a value from a list of values

COLUMN Returns the column number of a reference

 COLUMNS Returns the number of columns in a reference

GETPIVOTDATA Returns data stored in a PivotTable

HLOOKUP Looks in the top row of an array and returns the value of the indicated cell

HYPERLINK Creates a shortcut or jump that opens a document stored on a network server,
an intranet, or the Internet

INDEX Uses an index to choose a value from a reference or array

INDIRECT Returns a reference indicated by a text value

LOOKUP Looks up values in a vector or array

MATCH Looks up values in a reference or array

OFFSET Returns a reference offset from a given reference

ROW Returns the row number of a reference

ROWS Returns the number of rows in a reference

RTD Retrieves real-time data from a program that supports COM

automation (Automation: A way to work with an application's objects from
another application or development tool. Formerly called OLE Automation,
Automation is an industry standard and a feature of the Component Object
Model (COM).)

TRANSPOSE Returns the transpose of an array

VLOOKUP Looks in the first column of an array and moves across the row to return the
value of a cell

Top of Page

Math and trigonometry functions

Function Description

ABS Returns the absolute value of a number

ACOS Returns the arccosine of a number

ACOSH Returns the inverse hyperbolic cosine of a number

ASINH Returns the inverse hyperbolic sine of a number

ATAN Returns the arctangent of a number

ATAN2 Returns the arctangent from x- and y-coordinates

ATANH Returns the inverse hyperbolic tangent of a number

CEILING Rounds a number to the nearest integer or to the nearest multiple of

significance

COMBIN Returns the number of combinations for a given number of objects

COS Returns the cosine of a number

COSH Returns the hyperbolic cosine of a number

DEGREES Converts radians to degrees

EVEN Rounds a number up to the nearest even integer

EXP Returns e raised to the power of a given number

FACT Returns the factorial of a number

FACTDOUBLE Returns the double factorial of a number

FLOOR Rounds a number down, toward zero

GCD Returns the greatest common divisor

INT Rounds a number down to the nearest integer

LCM Returns the least common multiple

LN Returns the natural logarithm of a number

LOG Returns the logarithm of a number to a specified base

LOG10 Returns the base-10 logarithm of a number

MDETERM Returns the matrix determinant of an array

MINVERSE Returns the matrix inverse of an array

MMULT Returns the matrix product of two arrays

MOD Returns the remainder from division

MROUND Returns a number rounded to the desired multiple

MULTINOMIAL Returns the multinomial of a set of numbers

ODD Rounds a number up to the nearest odd integer

PI Returns the value of pi

POWER Returns the result of a number raised to a power

PRODUCT Multiplies its arguments

QUOTIENT Returns the integer portion of a division

RADIANS Converts degrees to radians

RAND Returns a random number between 0 and 1

RANDBETWEEN Returns a random number between the numbers you specify

ROMAN Converts an arabic numeral to roman, as text

ROUND Rounds a number to a specified number of digits

ROUNDDOWN Rounds a number down, toward zero

ROUNDUP Rounds a number up, away from zero

SERIESSUM Returns the sum of a power series based on the formula

SIGN Returns the sign of a number

SIN Returns the sine of the given angle

SINH Returns the hyperbolic sine of a number

 SQRT Returns a positive square root

SQRTPI Returns the square root of (number * pi)

SUBTOTAL Returns a subtotal in a list or database

SUM Adds its arguments

SUMIF Adds the cells specified by a given criteria

SUMIFS Adds the cells in a range that meet multiple criteria

SUMPRODUCT Returns the sum of the products of corresponding array components

SUMSQ Returns the sum of the squares of the arguments

SUMX2MY2 Returns the sum of the difference of squares of corresponding values in two
arrays

SUMX2PY2 Returns the sum of the sum of squares of corresponding values in two arrays

SUMXMY2 Returns the sum of squares of differences of corresponding values in two

arrays

TAN Returns the tangent of a number

TANH Returns the hyperbolic tangent of a number

TRUNC Truncates a number to an integer

Top of Page

Statistical functions

Function Description

AVEDEV Returns the average of the absolute deviations of data points from their mean

AVERAGE Returns the average of its arguments

AVERAGEA Returns the average of its arguments, including numbers, text, and logical
values

AVERAGEIF Returns the average (arithmetic mean) of all the cells in a range that meet a
 given criteria

AVERAGEIFS Returns the average (arithmetic mean) of all cells that meet multiple criteria.

BETADIST Returns the beta cumulative distribution function

BETAINV Returns the inverse of the cumulative distribution function for a specified beta
distribution

BINOMDIST Returns the individual term binomial distribution probability

CHIDIST Returns the one-tailed probability of the chi-squared distribution

CHIINV Returns the inverse of the one-tailed probability of the chi-squared distribution

CHITEST Returns the test for independence

CONFIDENCE Returns the confidence interval for a population mean

CORREL Returns the correlation coefficient between two data sets

COUNT Counts how many numbers are in the list of arguments

COUNTA Counts how many values are in the list of arguments

COUNTBLANK Counts the number of blank cells within a range

COUNTIF Counts the number of nonblank cells within a range that meet the given
criteria

COVAR Returns covariance, the average of the products of paired deviations

CRITBINOM Returns the smallest value for which the cumulative binomial distribution is
less than or equal to a criterion value

DEVSQ Returns the sum of squares of deviations

EXPONDIST Returns the exponential distribution

FDIST Returns the F probability distribution

FINV Returns the inverse of the F probability distribution

FISHER Returns the Fisher transformation

FISHERINV Returns the inverse of the Fisher transformation

FORECAST Returns a value along a linear trend

FREQUENCY Returns a frequency distribution as a vertical array

FTEST Returns the result of an F-test

GAMMADIST Returns the gamma distribution

GAMMAINV Returns the inverse of the gamma cumulative distribution

GAMMALN Returns the natural logarithm of the gamma function, Γ(x)

GEOMEAN Returns the geometric mean

GROWTH Returns values along an exponential trend

HARMEAN Returns the harmonic mean

HYPGEOMDIST Returns the hypergeometric distribution

INTERCEPT Returns the intercept of the linear regression line

KURT Returns the kurtosis of a data set

LARGE Returns the k-th largest value in a data set

LINEST Returns the parameters of a linear trend

LOGEST Returns the parameters of an exponential trend

LOGINV Returns the inverse of the lognormal distribution

LOGNORMDIST Returns the cumulative lognormal distribution

MAX Returns the maximum value in a list of arguments

MAXA Returns the maximum value in a list of arguments, including numbers, text,
and logical values

MEDIAN Returns the median of the given numbers

 MIN Returns the minimum value in a list of arguments

MINA Returns the smallest value in a list of arguments, including numbers, text, and
logical values

MODE Returns the most common value in a data set

NEGBINOMDIST Returns the negative binomial distribution

Top of Page

Text functions

Function Description

ASC Changes full-width (double-byte) English letters or katakana within a

character string to half-width (single-byte) characters

BAHTTEXT Converts a number to text, using the ß (baht) currency format

CHAR Returns the character specified by the code number

CLEAN Removes all nonprintable characters from text

CODE Returns a numeric code for the first character in a text string

CONCATENATE Joins several text items into one text item

DOLLAR Converts a number to text, using the $ (dollar) currency format

EXACT Checks to see if two text values are identical

FIND, FINDB Finds one text value within another (case-sensitive)

FIXED Formats a number as text with a fixed number of decimals

JIS Changes half-width (single-byte) English letters or katakana within a

character string to full-width (double-byte) characters

LEFT, LEFTB Returns the leftmost characters from a text value

LEN, LENB Returns the number of characters in a text string

 LOWER Converts text to lowercase

MID, MIDB Returns a specific number of characters from a text string starting at the
position you specify

PHONETIC Extracts the phonetic (furigana) characters from a text string

PROPER Capitalizes the first letter in each word of a text value

REPLACE, Replaces characters within text

REPLACEB

REPT Repeats text a given number of times

RIGHT, RIGHTB Returns the rightmost characters from a text value

SEARCH, SEARCHB Finds one text value within another (not case-sensitive)

SUBSTITUTE Substitutes new text for old text in a text string

T Converts its arguments to text

TEXT Formats a number and converts it to text

TRIM Removes spaces from text

UPPER Converts text to uppercase

VALUE Converts a text argument to a number

Advanced Worksheet features

l

Editing options

After pressing Enter, move selection Makes an adjacent cell the next active cell after you press

ENTER in the current active cell. In the Direction box, indicate which adjacent cell becomes active.
Direction Makes an adjacent cell the next active cell after you press ENTER in the current active cell.

In the Direction box, indicate which adjacent cell becomes active.

Automatically insert a decimal point Select this check box to display a number with decimal

points by default.

Places Enter the number of decimal places in the Places box to indicate where Microsoft

Office Excel automatically places the decimal point in the numbers that you type as constants

on a worksheet. A positive number moves the decimal point to the left; a negative number

moves the decimal point to the right. If the Places box is left blank or is set to 0 (zero), you

need to enter the decimal point manually. To override this option, type a decimal point in the

cell when you type the number.

Enable fill handle and cell drag-and-drop Select this check box to move and to copy cells and

data by dragging. When this option is selected, you can also drag the fill handle to copy data and to fill

adjacent cells with a series of data.

Alert before overwriting cells Displays a message if you drop cells over other cells that

contain data.

Allow editing directly in cells Select this check box to enable editing inside a cell by double-

clicking the cell, rather than by editing the cell contents in the formula bar.

Extend data range formats and formulas Select to automatically format new items added to the

end of a list to match the format of the rest of the list. Formulas that are repeated in every row are

also copied. To be extended, formats and formulas must appear in at least three of the five last rows

preceding the new row.

Enable automatic percent entry Select to multiply by 100 all of the numbers less than 1 that you

enter in cells that are formatted in the Percentage format. Clear this check box to multiply by 100 all of

the numbers that you enter in cells that are formatted in the Percentage format, including numbers

equal to or greater than 1.

Enable AutoComplete for cell values Completes text entries that you start to type in a column of

data. If the first few letters that you type match an existing entry in that column, Excel fills in the

remaining text for you.

Zoom on roll with IntelliMouse If you have the Microsoft IntelliMouse pointing device, sets the

wheel button to zoom instead of scroll on your worksheet or chart sheet.

Alert the user when a potentially time consuming operation occurs Select if you want to be

notified when an operation affects a large number of cells and may take a long time to process.

When this number of cells (in thousands) is affected Lets you specify the maximum

number of cells that are affected by an operation without being notified. If more cells than the

maximum number of cells are affected, you are notified.

Use system separators Select to use the default Decimal and Thousands separators. Clear

to enter alternate separators.

Decimal separator To change the default decimal separator, clear Use system

separators, select the default separator in the Decimal separator box, and then type

the separator that you want to use.

Thousands separator To change the default thousands separator, clear Use system

separators, select the default separator in the Thousands separator box, and then

type the separator that you want to use.

Cut, Copy, and Paste

Show Paste Options buttons Select to have Excel automatically display a dialog box with special

options when you paste, such as Formatting Only and Link Cells.

Show Insert Options buttons Select to have Excel automatically display a dialog box with special

options when you insert cells, rows or columns, such as Formatting Same As Above and Clear

Formatting.

Cut, copy, and sort inserted objects with their parent cells Keeps graphic objects, buttons, text

boxes, drawn objects, and pictures with their associated cells whenever you cut, copy, filter, or sort on

a worksheet.

Display

Show this number of Recent Documents Displays a list of recently used workbooks under Recent

Documents when you click the Microsoft Office Button , so that you can open those workbooks
quickly. Enter the number of workbooks that you want to display in the Show this number of Recent

Documents box. Enter or select a positive number between 0 and 50.

Ruler units Lets you select the units that you want to display on the Layout view ruler.

Show formula bar Displays the formula bar. The formula bar appears at the top of the worksheet.

Show function ScreenTips Displays brief descriptions of the functions that you select in the list of

functions that is displayed when Formula AutoComplete is turned on.

Show chart element names on hover Displays the name of a chart element when you rest the

pointer over it.

Show data point values on hover Displays the value of a data point (data points: Individual values

plotted in a chart and represented by bars, columns, lines, pie or doughnut slices, dots, and various

other shapes called data markers. Data markers of the same color constitute a data series.) when you

rest the pointer over it.

For cells with comments, show Select one of the following options to determine how comments

are displayed on the worksheet.

o No comments or indicators Hides comments and comment indicators in cells that have

comments attached.

o Indicators only, and comments on hover Displays a small triangle in the upper-right

corner of a cell when there is a comment attached to the cell. Comments are displayed only

when you rest a pointer over a cell that has a comment.

o Comments and indicators Displays the comment and comment indicator, a small triangle

in the upper-right corner of a cell, when there is a comment attached to the cell.

Display options for this workbook

Display options for this workbook Select the workbook in this list box that is affected by the

following options.

Show horizontal scroll bar Displays the horizontal scroll bar at the bottom of the worksheet.

Show vertical scroll bar Displays the vertical scroll bar on the right side of the worksheet.
Show sheet tabs Displays worksheet tabs so that you can move among and select individual

worksheets. Worksheet tabs appear at the bottom of the worksheet window.

Group dates in the AutoFilter menu Changes the hierarchical grouping of dates to a

nonhierarchical list of dates in the list of dates at the bottom of the AutoFilter menu in a date filter. For

example, you can filter for just two-digit years by manually selecting two-digit years from a

nonhierarchical list.

For objects, show Select one of the following options to display or hide graphic objects in the

workbook.

o All Displays all of the graphic objects, buttons, text boxes, drawn objects, and pictures.

o Nothing (hide objects) Hides all of the graphic objects, buttons, text boxes, drawn objects,

and pictures. Hidden objects are not printed.

Display options for this worksheet

Display options for this worksheet Select the worksheet in this list box that is affected by the

following options.

Show row and column headers Displays row numbers on the left side of the worksheet and column

letters at the top of the worksheet.

Show formulas in cells instead of their calculated results Displays the formulas in cells instead

of the values that the formulas produce.

Show page breaks Displays page breaks that have been set automatically by Excel.

Show a zero in cells that have zero value Displays a 0 (zero) in cells that contain zero values.

Show outline symbols if an outline is applied Displays outline symbols. Outline symbols are not

displayed unless the worksheet contains an outline.

Show gridlines Displays cell gridlines. To print gridlines, make sure that the Print check box is

selected under Gridlines in the Sheet Options group on the Page Layout tab.

Gridline color Sets the color for gridlines. If you click Automatic, the gridline color is based

on the text color defined in Windows Control Panel.

Formulas

Enable multi-threaded calculation Selected by default, this option enables fast calculation by

using all of the processors on your computer, or by using the number of processors that you type

manually.

Number of calculation threads Lets you specify the number of processors that are used for

calculation.

o Use all processors on this computer Selected by default, this option uses all of the

processors that are available on your computer.

o Manual Lets you specify the number of processors that you want to use. In the Manual box,

enter a positive number between 1 and 1024.

When calculating this workbook

When calculating this workbook Select the workbook in this list box that is affected by the

following options.

Update links to other documents Calculates and updates formulas that include references to other

applications.

Set precision as displayed Permanently changes stored values in cells from full precision (15 digits)

to whatever format is displayed, including decimal places.

Use 1904 date system Changes the starting date from which all dates are calculated from January

1, 1900, to January 2, 1904.

Save external link values Saves copies of the values contained in an external document linked to

an Excel worksheet. If a worksheet with links to large ranges on an external document requires an

unusually large amount of disk space or takes a very long time to open, clearing the Save external

link values check box can reduce the disk space and time that is needed to open the worksheet.

General

Provide feedback with sound Plays available sounds that are associated with Microsoft Office

program events, such as opening, saving, and printing files, and displaying error messages. Sounds

that are assigned to different events can be changed in the Sounds Properties dialog box in
Windows Control Panel. If you select or clear the Provide feedback with sound check box in one

Office program, it is also turned on or off for all other Office programs. To change the sound that is

associated with an event, open the Sounds folder in Windows Control Panel. Your computer must have

a sound card to play most sounds.

Provide feedback with animation Displays worksheet movement and changes when you insert or

delete cells, rows, or columns. Animation may slow video performance on some systems.

Ignore other applications that use Dynamic Data Exchange (DDE) Prevents the exchange of

data with other applications that use Dynamic Data Exchange (DDE).

Ask to update automatic links Displays a message that lets you confirm before linked items are

updated.

Show add-in user interface errors Displays errors in the user-interface of add-ins that you install

and use.

Scale content for A4 or 8.5 x 11" paper sizes For some countries or regions, the standard paper

size is Letter; for others, the standard size is A4. Select this check box if you want Excel to

automatically adjust documents formatted for the standard paper size of another country or region (for

example, A4) so that they print correctly on the standard paper size for your country or region (for

example, Letter). This option affects the printout only. It does not affect the formatting in your

document.

At startup, open all files in At startup, Excel automatically opens files from the folder that you type

in this text box. Type the full path to the folder in the text box to indicate the location of the files.

Web Options Sets options for how Excel data looks and responds when the data is viewed in a Web

browser.

Service Options Lets you specify customer feedback and document management options.

Lotus Compatibility

Microsoft Office Excel menu key Sets the key that you can use to access the commands on the

Ribbon.

Transition navigation keys Activates an alternate set of keys for worksheet navigation, formula

entry, label entry, and other actions.

Lotus Compatibility Settings for

Lotus Compatibility Settings for Select the worksheet in this list box that is affected by the

following options.

Transition formula evaluation Opens and evaluates Lotus 1-2-3 files without losing or changing

information. When this option is selected, Excel evaluates text strings as 0 (zero), Boolean expressions

as 0 or 1, and database criteria according to the rules that are used in Lotus 1-2-3.

Transition formula entry Converts formulas that are entered in Lotus 1-2-3 release 2.2 syntax to

Excel syntax, and makes names that are defined in Excel behave like names that are defined in Lotus

1-2-3.

Create a PivotTable or PivotChart report

To create a PivotTable or PivotChart report, you need to connect to a data source and enter the

report's location.

1. Select a cell in a range of cells, or put the insertion point inside of a Microsoft Office Excel

table.

Make sure that the range of cells has column headings.

2. Do one of the following:

 To create a PivotTable report, on the Insert tab, in the Tables group, click PivotTable,

and then click PivotTable.

The Create PivotTable dialog box is displayed.

 To create a PivotTable and PivotChart report, on the Insert tab, in the Tables group,

click PivotTable, and then click PivotChart.

The Create PivotTable with PivotChart dialog box is displayed.

3. Select a data source. Do one of the following:

Choose the data that you want to analyze

1. Click Select a table or range.

2. Type the range of cells or table name reference, such as =QuarterlyProfits, in the
Table/Range box.

If you selected a cell in a range of cells or if the insertion point was in a table before you

started the wizard, the range of cells or table name reference is displayed in the

Table/Range box.

Alternatively, to select a range of cells or table, click Collapse Dialog to temporarily

hide the dialog box, select the range on the worksheet, and then press Expand Dialog

NOTE If the range is in another worksheet in the same workbook or another workbook,

type the workbook and worksheet name by using the following syntax:

([workbookname]sheetname!range).

Use external data

3. Click Use an external data source.

4. Click Choose Connection.

The Existing Connections dialog box is displayed.

5. In the Show drop-down list at the top of the dialog box, select the category of
connections for which you want to choose a connection or select All Existing

Connections (which is the default).

6. Select a connection from the Select a Connection list box, and then click Open.

NOTE If you choose a connection from the Connections in this Workbook category,

you will be reusing or sharing an existing connection. If you choose a connection from

the Connection files on the network or Connection files on this computer

 categories, the connection file is copied into the workbook as a new workbook

connection, and then used as the new connection for the PivotTable report.

For more information, see Manage connections to data in a workbook.

Enter a location. Do one of the following:

 To place the PivotTable report in a new worksheet starting at cell A1, click New

Worksheet.

 To place the PivotTable report in an existing worksheet, select Existing Worksheet,

and then type the first cell in the range of cells where you want to locate the PivotTable

report.

Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the

beginning cell on the worksheet, and then press Expand Dialog .

Click OK.

An empty PivotTable report is added to the location that you entered with the PivotTable Field

List displayed so that you can start adding fields, creating a layout, and customizing the

PivotTable report. For more information, see Create and change the layout of fields in a

PivotTable report.

If you are creating a PivotChart report, an associated PivotTable report (associated PivotTable

report: The PivotTable report that supplies the source data to the PivotChart report. It is

created automatically when you create a new PivotChart report. When you change the layout

of either report, the other also changes.) is created directly underneath the PivotChart report

for the location that you enter. This PivotTable report must be in the same workbook as the

PivotChart report. If you specify a location in another workbook, the PivotChart report will also

be created in that workbook. For more information, see Overview of charting, Available chart

types, and Create a chart.

Top of Page

Create a PivotChart report from an existing

PivotTable report
1. Click the PivotTable report.

2. On the Insert tab, in the Charts group, click a chart type.

You can use any chart type except xy (scatter), bubble, or stock.

For more information, see Overview of charting, Available chart types, and Create a chart.

Top of Page

Convert a PivotChart report to a static chart

1. Find the associated PivotTable report (associated PivotTable report: The PivotTable report that

supplies the source data to the PivotChart report. It is created automatically when you create

a new PivotChart report. When you change the layout of either report, the other also

changes.) that has the same name as the PivotChart report by doing the following:

1. Click the PivotChart report.

2. To find the associated PivotTable report name, on the Design tab, in the Data group,

click Select Data to display the Edit Data Source dialog box, and then note the

associated PivotTable name, which is the text that follows the (!) exclamation point, in

the Chart data range text box and then click OK.

3. To find the associated PivotTable report, click each PivotTable report in the workbook,

and then on the Options tab, in the PivotTable group, click Options until you find the

same name in the Name text box.

2. Click OK.

3. On the Options tab, in the Actions group, click Select, and then click Entire PivotTable.

4. Press DELETE.
 Top of Page

Create a static chart from the data in a

PivotTable report

This procedure creates a regular, noninteractive chart rather than a PivotChart report (PivotChart

report: A chart that provides interactive analysis of data, like a PivotTable report. You can change

views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by

showing or hiding items in fields.).

1. Select the data in the PivotTable report that you want to use in your chart. To include field

buttons (field button: Button that identifies a field in a PivotTable or PivotChart report. You can

drag the field buttons to change the layout of the report, or click the arrows next to the

buttons to change the level of detail displayed in the report.) and data in the first row and

column of the report, start dragging from the lower-right corner of the data that you're

selecting.

For more information, see Select data in a PivotTable report.

2. On the Home tab, in the Clipboard group, click Copy .

3. Click a blank cell outside of the PivotTable report.

4. On the Home tab, in the Clipboard group, click the arrow next to Paste, and then click

Paste Special.

5. Click Values, and then click OK.

6. On the Insert tab, in the Charts group, click a chart type.

For more information on charts, see Overview of charting, Available chart types, and Create a

chart.

Top of Page

Delete a PivotTable or PivotChart report

Delete a PivotTable report
1. Click the PivotTable report.
2. On the Options tab, in the Actions group, click Select, and then click Entire PivotTable.
3. Press DELETE.

NOTE Deleting the associated PivotTable report (associated PivotTable report: The PivotTable report

that supplies the source data to the PivotChart report. It is created automatically when you create a

new PivotChart report. When you change the layout of either report, the other also changes.) for a

PivotChart report creates a static chart that you can no longer change.

Delete a PivotChart report

1. Select the PivotChart report.

2. Press DELETE.

NOTE Deleting the PivotChart report does not automatically delete the associated

PivotTable report.

3. Overview of SPSS

SPSS Statistics is a software package used for statistical analysis. Long

produced by SPSS Inc., it was acquired by IBM in 2009. The current versions
(2015) are officially named IBM SPSS Statistics. Companion products in
the same family are used for survey authoring and deployment (IBM SPSS
Data Collection), data mining (IBM SPSS Modeler), text analytics, and
collaboration and deployment (batch and automated scoring services).

The software name originally stood for Statistical Package for the Social
Sciences (SPSS),[2] reflecting the original market, although the software is
now popular in other fields as well, including the health sciences and
marketing.

Contents
 1 Overview
 2 Versions and ownership history
 3 See also
 4 Notes
 5 References
 6 External links

Overview
SPSS is a widely used program for statistical analysis in social science. It is
also used by market researchers, health researchers, survey companies,
government, education researchers, marketing organizations, data miners, [3]
and others. The original SPSS manual (Nie, Bent & Hull, 1970) has been
described as one of "sociology's most influential books" for allowing ordinary
researchers to do their own statistical analysis. [4] In addition to statistical
analysis, data management (case selection, file reshaping, creating derived
data) and data documentation (a metadata dictionary was stored in the
datafile) are features of the base software.

Statistics included in the base software:

 Descriptive statistics: Cross tabulation, Frequencies, Descriptives,

Explore, Descriptive Ratio Statistics
 Bivariate statistics: Means, t-test, ANOVA, Correlation (bivariate,
partial, distances), Nonparametric tests
 Prediction for numerical outcomes: Linear regression
 Prediction for identifying groups: Factor analysis, cluster analysis (two-
step, K-means, hierarchical), Discriminant

The many features of SPSS Statistics are accessible via pull-down menus or
can be programmed with a proprietary 4GLcommand syntax language.
Command syntax programming has the benefits of reproducibility,
simplifying repetitive tasks, and handling complex data manipulations and
analyses. Additionally, some complex applications can only be programmed
in syntax and are not accessible through the menu structure. The pull-down
menu interface also generates command syntax: this can be displayed in the
output, although the default settings have to be changed to make the syntax
visible to the user. They can also be pasted into a syntax file using the
"paste" button present in each menu. Programs can be run interactively or
unattended, using the supplied Production Job Facility.

Additionally a "macro" language can be used to write command language

subroutines. A Python programmability extension can access the information
in the data dictionary and data and dynamically build command syntax
programs. The Python programmability extension, introduced in SPSS 14,
replaced the less functional SAX Basic "scripts" for most purposes, although
SaxBasic remains available. In addition, the Python extension allows SPSS to
run any of the statistics in the free software package R. From version 14
onwards, SPSS can be driven externally by a Python or a [Link] program
using supplied "plug-ins". (From Version 20 onwards, these two scripting
facilities, as well as many scripts, are included on the installation media and
are normally installed by default.)

SPSS Statistics places constraints on internal file structure, data types, data
processing, and matching files, which together considerably simplify
programming. SPSS datasets have a two-dimensional table structure, where
the rows typically represent cases (such as individuals or households) and
the columns represent measurements (such as age, sex, or household
income). Only two data types are defined: numeric and text (or "string"). All
data processing occurs sequentially case-by-case through the file. Files can
be matched one-to-one and one-to-many, but not many-to-many.

The graphical user interface has two views which can be toggled by clicking
on one of the two tabs in the bottom left of the SPSS Statistics window. The
'Data View' shows a spreadsheet view of the cases (rows) and variables
(columns). Unlike spreadsheets, the data cells can only contain numbers or
text, and formulas cannot be stored in these cells. The 'Variable View'
displays the metadata dictionary where each row represents a variable and
shows the variable name, variable label, value label(s), print width,
measurement type, and a variety of other characteristics. Cells in both views
can be manually edited, defining the file structure and allowing data entry
without using command syntax. This may be sufficient for small datasets.
Larger datasets such as statistical surveys are more often created in data
entry software, or entered during computer-assisted personal interviewing,
by scanning and using optical character recognition and optical mark
recognition software, or by direct capture from online questionnaires. These
datasets are then read into SPSS.

SPSS Statistics can read and write data from ASCII text files (including
hierarchical files), other statistics packages, spreadsheets and databases.
SPSS Statistics can read and write to external relational database tables via
ODBC and SQL.

Statistical output is to a proprietary file format (*.spv file, supporting pivot

tables) for which, in addition to the in-package viewer, a stand-alone reader
can be downloaded. The proprietary output can be exported to text or
Microsoft Word, PDF, Excel, and other formats. Alternatively, output can be
captured as data (using the OMS command), as text, tab-delimited text, PDF,
XLS, HTML, XML, SPSS dataset or a variety of graphic image formats (JPEG,
PNG, BMP and EMF).

The SPSS logo used prior to the renaming in January 2010.

SPSS Statistics Server is a version of SPSS Statistics with a client/server
architecture. It had some features not available in the desktop version, such
as scoring functions. (Scoring functions are included in the desktop version
from version 19.)

Versions and ownership history

The software was released in its first version in 1968 as the Statistical
Package for the Social Sciences (SPSS) after being developed by Norman H.
Nie, Dale H. Bent, and C. Hadlai Hull. Those principals incorporated as SPSS
Inc. in 1975. Early versions of SPSS Statistics were designed for batch
processing on mainframes, including for example IBM and ICL versions,
originally using punched cards for input. A processing run read a command
file of SPSS commands and either a raw input file of fixed format data with a
single record type, or a 'getfile' of data saved by a previous run. To save
precious computer time an 'edit' run could be done to check command
syntax without analysing the data. From version 10 (SPSS-X) in 1983, data
files could contain multiple record types.

SPSS Statistics versions 16.0 and later run under Windows, Mac, and Linux.
The graphical user interface is written in Java. The Mac OS version is
provided as a Universal binary, making it fully compatible with both PowerPC
and Intel-based Mac hardware.

Prior to SPSS 16.0, different versions of SPSS were available for Windows,
Mac OS X and Unix. The Windows version was updated more frequently and
had more features than the versions for other operating systems.[citation needed]

SPSS Statistics version 13.0 for Mac OS X was not compatible with Intel-
based Macintosh computers, due to the Rosetta emulation software causing
errors in calculations. SPSS Statistics 15.0 for Windows needed a
downloadable hotfix to be installed in order to be compatible with Windows
Vista.

SPSS Inc announced on July 28, 2009 that it was being acquired by IBM for
US$1.2 billion.[5] Because of a dispute about ownership of the name "SPSS",
between 2009 and 2010, the product was referred to as PASW (Predictive
Analytics SoftWare).[6] As of January 2010, it became "SPSS: An IBM
Company". Complete transfer of business to IBM was done by October 1,
2010. By that date, SPSS: An IBM Company ceased to exist. IBM SPSS is now
fully integrated into the IBM Corporation, and is one of the brands under IBM
Software Group's Business Analytics Portfolio, together with IBM
Algorithmics, IBM Cognos and IBM OpenPages.

Features of SPSS
Data management capabilities include:
 Detailed labeling of variables and data values; additional documentation of
data sets; storage of data and documentation in system files.
 Flexible definition of missing data codes.
 Permanent and temporary transformation of existing variables and
computation of new variables; conditional and looping structures for complex
data transformations.
 Reading raw data files in a wide variety of formats (e.g., numeric,
alphanumeric, binary, dollar, date, and time formats).
 Reading hierarchical and other non-rectangular raw data files.
 Reading, combining, outputting multiple files.
 Reading matrices for input to procedures.
 Flip command to switch the columns and rows in a data set.
 Macro facility to build ones own block of SPSS syntax elements and to
control the execution of these blocks.
 Ability to read and write to compressed files.

Statistical procedures for data analysis include:

 The EXAMINE procedure to explore data sets before deciding on the
course of data analysis to perform.
 Descriptive statistics, frequency distributions, and cross-tabulations, bar
charts, histograms, and scatterplots.
 The RANK procedure, which produces ranks, normal scores, Savage
scores, and percentiles for numeric variables.
 T-tests, univariate and multivariate analysis of variance and covariance,
including repeated measures and nested designs.
 Multiple regression, NonLinear Regression, Constrained NonLinear
Regression.
 Loglinear models for discrete data; probit models.
 Factor and principle components analysis, discriminant analysis, cluster
analysis, multidimensional scaling.
 Nonparametric tests.

Besides these capabilities, SPSS add-on modules feature:

 Tables to produce simple or complex tabulation formatted for presentation.
 Trends including time series plots, plots of autocorrelation, partial
autocorrelation, cross-correlation function, smoothing, seasonal regression,
Box-Jenkins methods, spectral methods and forecasting.
 Categories for doing conjoint analysis and optimal scaling.
 1: Introduction to
SPSS
What is SPSS?

SPSS is a Windows based program that can be used to perform data

entry and
analysis and to create tables and graphs. SPSS is capable of handling large
amounts of
data and can perform all of the analyses covered in the text and much
more. SPSS is
commonly used in the Social Sciences and in the business world, so
familiarity with this
program should serve you well in the future. SPSS is updated often. This
document was
written around an earlier version, but the differences should not cause any
problems. If
you want to go further and learn much more about SPSS, I strongly
recommend Andy
Field’s book (Field, 2009, Discovering statistics using SPSS). Those of us who
have used
software for years think that we know it all and don’t pay a lot of attention
to new
features. I learned a huge amount from Andy’s book.

Opening SPSS

Depending on how the computer you are working on is structured, you

can open
SPSS in one of two ways.
 1. If there is an SPSS shortcut
on thelike
desktop,
this simply put the
cursor on it and double click the left mouse
button. button on your screen, then put
2. Click the left mouse button on the
your cursor on Programs or All Programs and left click the mouse. Select
SPSS 17.0
for Windows by clicking the left mouse button. (For a while that started
calling the
program PASW Statistics 17, but they seem to have given that up as a dumb
idea when
everyone else calls it SPSS. The version number may change by the time
you read this.)
Either approach will launch the program.

Use one of these approaches to open SPSS yourself.

 You will see a screen that looks like the image on the next page. The
dialog box
that appears offers choices of running the tutorial, typing in data, running
queries, or
opening an existing data source. The window behind this is the Data Editor
window
which is used to display the data from whatever file you are using. You
could select any
one of the options on the start-up dialog box and click OK, or you could
simply hit
Cancel. If you hit Cancel, you can either enter new data in the blank Data
Editor or you
could open an existing file using the File menu bar as explained later.

Click Cancel, and we’ll get acquainted with the layout of SPSS.

Layout of SPSS

The Data Editor window has two views that can be selected from the
lower left
hand side of the screen. Data View is where you see the data you are using.
Variable
View is where you can specify the format of your data when you are
creating a file or
where you can check the format of a pre-existing file. The data in the Data
Editor is
saved in a file with the extension .sav.

Menu bar

Icons

Start-up dialog box

 The other most commonly used SPSS window is the SPSS Viewer
window which
displays the output from any analyses that have been run and any error
messages.
Information from the Output Viewer is saved in a file with the
extension .spo. Let’s open
an output file and look at it.
On the File menu, click Open and select Output. Select
[Link] from the
files that can be found at
[Link]
SPSSLongerManual/Data
ForSPSS/. (At the moment this set of web pages is the most recent version
whichever
of my books you are using.) Click Ok. The following will appear. The left
hand side
is an outline of all of the output in the file. The right side is the actual
output. To
shrink or enlarge either side put your cursor on the line that divides them.
When the
double headed arrow appears, hold the left mouse button and move the
line in either
direction. Release the button and the size will be adjusted.

Finally, there is the Syntax window which displays the command

language used to
run various operations. Typically, you will simply use the dialog boxes to set
up
commands, and would not see the Syntax window. The Syntax window
 would be
activated if you pasted the commands from the dialog box to it, or if you
wrote you own
syntax--something we will not focus on here. Syntax files end in the
extension .sps.

SPSS Menus and Icons

Now, let’s review the menus and icons.

Review the options listed under each menu on the Menu Bar by clicking
them one at a
time. Follow along with the below descriptions.
 File includes all of the options
you typically use in other programs, such
as open, save, exit. Notice, that you can
open or create new files of multiple types
as illustrated to the right.

Edit includes the typical cut,

copy, and paste commands, and allows
you to specify various options for
displaying data and output.

Click on Options, and you will see

the dialog box to the left. You can
use this to format the data, output,
charts, etc. These choices are rather
overwhelming, and you can simply
take the default options for now. The
author of your text (me) was too
dumb to even know these options
could easily be set.

View allows you to select which toolbars you want to show, select font
size, add
or remove the gridlines that separate each piece of data, and to select
whether or not to
display your raw data or the data labels.

Data allows you to select several options ranging from displaying

data that is
sorted by a specific variable to selecting certain cases for subsequent
analyses.

Transform includes several options to change current variables. For

example,
you can change continuous variables to categorical variables, change scores
into rank
scores, add a constant to variables, etc.
 Analyze includes all of the commands to carry out statistical analyses
and to
calculate descriptive statistics. Much of this book will focus on using
commands located
in this menu.

Graphs includes the commands to create various types of graphs

including box
plots, histograms, line graphs, and bar charts.

Utilities allows you to list file information which is a list of all

variables, there
labels, values, locations in the data file, and type.

Add-ons are programs that can be added to the base SPSS package.
You probably
do not have access to any of those.

Window can be used to select which window you want to view (i.e.,
Data Editor,
Output Viewer, or Syntax). Since we have a data file and an output file open,
let’s try
this.

Select Window/Data Editor. Then select Window/SPSS Viewer.

Help has many useful options including a link to the SPSS homepage,
a statistics
coach, and a syntax guide. Using topics, you can use the index option to
type in any key
word and get a list of options, or you can view the categories and
subcategories available
under contents. This is an excellent tool and can be used to troubleshoot
most problems.

The Icons directly under the Menu bar provide shortcuts to many
common
commands that are available in specific menus. Take a moment to review
these as well.

Place your cursor over the Icons for a few seconds, and a description of the
underlying
command will appear. For example,
is the shortcut
this for Save. Review the
icon
others yourself.

In the chapters that follow, we will review many specific functions

available
through these Menus and Icons, but it is important that you take a few
moments to
 familiarize yourself with the layout and options before beginning.

Exiting SPSS
To close SPSS, you can either left click on the close button
located on the
upper right hand corner of the screen or select Exit from the File menu.
Choose one of these approaches.

A dialog box like the one below will appear for every open window asking you if
you
want to save it before exiting. You almost always want to save data files.
Output files
may be large, so you should ask yourself if you need to save them or if you
simply want
to print them.
 [Link] of frequency distribution

Using Excel to compute the binomial distribution.

FAQ# 1311 Last Modified 1-January-2009

The exact binomial distributionWhat is the chance of exactly 16 heads

out of 20 tosses?

If we assume that the coin toss is fair and the results are recorded properly,
the results will follow what is called a binomial distribution. The equation that
describes the binomial distribution is built-in to Excel:

=BINOMDIST(16,20,0.5, FALSE)

That formula answers this question: What is the chance of getting exactly 16
‘successes’ out of 20 tries, when the probability of success is 0.5 (since 50%
of fair coin tosses are heads)? The answer is 0.46%, or about one time in
200. (Why ‘FALSE’? Read on for an explanation.)

The cumulative binomial distribution

The chance of observing 16 heads out of twenty coin flips is about 1 in 200.
That’s pretty rare. But it is the answer to the wrong question. If we had
observed 17 heads, we would have been even more surprised. So we need to
add to the probability we calculated the chance of observing 17, 18, 19 or 20
heads. One way to do this is to use a formula similar to the one shown above
(but changing ’16’ to the other values) and adding up the probabilities.
Another way is to use this Excel formula:

=1 - BINOMDIST(15,20,0.5,TRUE)

With the last argument to the function set to True, that function returns the
probability of getting 15 or fewer successes out of 20 tries when the
probability of success is 0.5. Subtract that result from 1 to get the probability
of getting 16 or more successes. So to switch from calculating an exact
probability to a cumulative one, we had to change the last argument to
Excel’s function from False to True, and also had to change the first value
from 16 to 15.

The answer is 0.59%.