# Excel Formula Research

Description

Excel Formula Research document sample

Shared by:
Categories
Tags
-
Stats
views:
16
posted:
2/3/2011
language:
English
pages:
14
Document Sample

```							                                                   Excel - Beginner
Documentation
The Center for Teaching, Research & Learning
Teaching & Learning Resources

Basics .................................................................................................................................. 1
What is a spreadsheet ...................................................................................................... 1
Why on a computer ......................................................................................................... 1
Basics of a spreadsheet ................................................................................................... 1
Types of data ................................................................................................................... 1
Formatting ........................................................................................................................... 2
Organize content in rows and columns ........................................................................... 2
Change column and row sizes..................................................................................... 2
Merge cells .................................................................................................................. 3
Hide rows and columns ............................................................................................... 3
Insert and Cut & Paste rows and columns .................................................................. 4
Paste special ................................................................................................................ 4
Transpose .................................................................................................................... 5
Freezing panes ................................................................................................................ 6
Freeze panes ................................................................................................................ 6
Format data in cells ......................................................................................................... 6
Formulas ......................................................................................................................... 8
Basic formulas ( +, -, /, *, ^, () ).................................................................................. 8
Formula function ......................................................................................................... 8
Copying formulas........................................................................................................ 9
Referencing ................................................................................................................. 9
Relative references ...................................................................................................... 9
Absolute references ................................................................................................... 10
Applying Styles ......................................................................................................... 10
Conditional formatting .................................................................................................. 12
Insert Charts & Graphs ..................................................................................................... 12
Printing.............................................................................................................................. 13
Print preview ................................................................................................................. 13
Page Break Preview ...................................................................................................... 13
The Center for Teaching,                           Excel - Beginner
Research & Learning                               Documentation
Teaching & Learning Resources

Basics
It’s the equivalent of a paper ledger sheet. Spreadsheets are used for tracking money,
working with numerical data, statistics, etc.

Why on a computer
The computer does the calculating for you. Also, if you change values (say, if you change
a grade) the computer recalculates instantaneously for you.

Spreadsheets are made up of columns and rows, whose intersections are cells.
Columns: vertical; labeled with letters
Rows: horizontal; labeled with numbers
Cells: referred to with letter first, then number (in the example below, cell F2 is
selected)

Types of data
Labels: text (in the above example, Row 1 has the labels: First, Last, etc.)
Constants: numbers (in the above example, the grades in columns C, D, and E are
constants: 87, 76, etc.)
Formulas: equations; must start with equals sign (in the above example, the formula
in cell F2 is displayed in the function field: =AVERAGE(C2:E2))
(More on formulas on page 8)

1
The Center for Teaching,                         Excel - Beginner
Research & Learning                             Documentation
Teaching & Learning Resources

Formatting
Organize content in rows and columns
Change column and row sizes
Several ways to do this:
Select the whole column or row and drag it to the appropriate size

OR select from the Home tab > Cells group > Format > Column Width

2
The Center for Teaching,                      Excel - Beginner
Research & Learning                          Documentation
Teaching & Learning Resources

Then enter the column width. Click OK.

Merge cells
Select two cells (click and drag)
On the Home tab > Alignment group > click Merge and Center

Hide rows and columns
Select entire row or column

3
The Center for Teaching,                         Excel - Beginner
Research & Learning                             Documentation
Teaching & Learning Resources

On the Home tab > Cells group, select Format > Hide & Unhide > Hide Rows (or
Columns)

Insert and Cut & Paste rows and columns
Excel inserts columns to the left of the column you have selected, and inserts rows above
the row you have selected. To insert columns or rows, highlight the column or row that
you want the new column or row to go to the left or above.
On the Home tab > Cells group, select Insert > Insert Sheet Columns (or Insert Sheet
Rows)

Paste special
Select a cell (or row, or column) and Copy (Ctrl + C OR the Copy icon in Home tab >
Clipboard group)
Click on the destination cell, row, or column

4
The Center for Teaching,                           Excel - Beginner
Research & Learning                               Documentation
Teaching & Learning Resources

On the Home tab > Clipboard group, click Paste > Paste special

Select the appropriate option (for example, to paste a number that is the result of a
formula as a constant, select Values)
Click OK

Transpose
To transpose a row or column (make a column a row, or vice versa):
Select a group of cells, row, or column and Copy (Ctrl + C OR the Copy icon in
Home tab > Clipboard group)
Click on destination cells, row, or column
On the Home tab > Clipboard group, go to Paste > Transpose

5
The Center for Teaching,                           Excel - Beginner
Research & Learning                               Documentation
Teaching & Learning Resources

Freezing panes
Freeze panes
This lets you keep some parts of the spreadsheet still (frozen) while you scroll through or
modify another part.
Select the cell to the right of and below the point where you want the screen to freeze.
In the View tab > Window group, select Freeze Panes > Freeze Panes
To remove the split, in the View tab > Window group, select Freeze Panes >
Unfreeze Panes

Format data in cells
Select the cell you want to apply a special format to

6
The Center for Teaching,                        Excel - Beginner
Research & Learning                            Documentation
Teaching & Learning Resources

In the Home tab > Number group, choose More Number Formats from the dropdown

A Format Cells window will appear. Select the format you want.

Then click OK

7
The Center for Teaching,                           Excel - Beginner
Research & Learning                               Documentation
Teaching & Learning Resources

Formulas
Use formulas to perform mathematical operations in Excel. The equals sign (=) is the
signal to Excel that you are typing a formula in a cell. Always start your formula with an
equals sign.

Basic formulas ( +, -, /, *, ^, () )
You can use Excel to: add, subtract, divide, multiply, raise to the exponent. You can also
group operations to have Excel, for example, first add values in cells and then divide by
the total number of cells.

Formula function
To insert a function into a cell:
Go to the Formulas tab > Function Library group and click on Insert Function

Look for the function you want to use in the Select a function: menu. If you don’t see
it, search for it by typing the name of the function in the Search for a function: text
box and click Go.

8
The Center for Teaching,                             Excel - Beginner
Research & Learning                                 Documentation
Teaching & Learning Resources

Select the function you want and click OK.

Specify the cells that you want to perform the function on (in this case, average) by
clicking and dragging on the spreadsheet, or by entering the addresses of the cells in
the Number fields.
Click OK
Some other functions you might want to use include: Absolute value, count, and rounding
(which is different than applying a particular decimal format)

Copying formulas
To re-use a formula you’ve created in another cell, either select the cell with the formula,
click the black box in the bottom right-hand corner and drag to other cells, OR:
Select the cell that contains the formula you want to copy
Ctrl+C (to copy)
Select the cell where you want the formula to go
Ctrl+V (to paste)

Referencing
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

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 the formula
across rows or down columns, the reference automatically adjusts. By default, new
formulas use relative references. For example, if you create a formula in cell B2 with a
relative reference to A1, like this:

9
The Center for Teaching,                          Excel - Beginner
Research & Learning                              Documentation
Teaching & Learning Resources

Then copy the formula from cell B2 into cell B3, it automatically adjusts from =A1 to
=A2.

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 the formula across rows or down columns, the
absolute reference does not adjust. By default, new formulas use relative references, and
you need to switch them to absolute references. For example, For example, if you create a
formula in cell B2 with an absolute reference to A1, like this:

Then copy the formula from cell B2 into cell B3, it stays the same in both cells =\$A\$1.

Applying Styles
To automatically apply colors and borders to a table:
Select the range of cells you want to apply formatting to

10
The Center for Teaching,                         Excel - Beginner
Research & Learning                             Documentation
Teaching & Learning Resources

Go to the Home tab > Styles group. Go to Format as Table and scroll over styles to
preview them. Click on one to select from the styles.

11
The Center for Teaching,                         Excel - Beginner
Research & Learning                             Documentation
Teaching & Learning Resources

Conditional formatting
Select the cell or group of cells you want to apply conditional formatting to
In the Home tab > Styles group, select Conditional Formatting and choose a method
for calculating your formatting, such as Less Than…

Set condition (if you want to modify the default colors, choose Custom Format…
from the dropdown menu and choose your formats there) and click OK

Insert Charts & Graphs
Use the Excel Chart Wizard to insert a chart or graph:
Select the cells you want to make the graph out of
Go to the Insert tab > Charts group and select a chart type and subtype. Choose Other
Charts if the chart type you would like is not listed in the group.

12
The Center for Teaching,                          Excel - Beginner
Research & Learning                              Documentation
Teaching & Learning Resources

Printing
The print area shows you what part of the spreadsheet will print.

Print preview
To see what your document will look like when it prints:
From the logo button, choose Print > Print Preview
To leave the print preview view, click the Close Print Preview button

Page Break Preview
In the Page Break Preview mode, you can change the print area by clicking and dragging
the blue lines.
Go to the View tab > Workbook Views group and choose Page Break Preview

13

```
Related docs