Excel Formula Research

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

                                                Table of Contents
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
What is a spreadsheet
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.

Basics of a spreadsheet
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
    menu




    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
links.

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