excel formulas by falgal17

VIEWS: 917 PAGES: 10

									                     Creating Formulas and Charts in Excel - Basic

      Using Excel

      When you open up Excel, this is the first window you will see.

Choose Excel
Workbook and
then click “OK”

      This will open up a new Excel workbook and a spreadsheet will appear. You will notice
      that columns are labeled alphabetically and rows are labeled numerically.

An active cell has a light blue border around it. Click in the cell you want to make active.

                        Formula Bar

Entering Values

In excel, a value can be any text, number, date, or time you enter in a cell. Activate the
cell in which you want to enter a value. As you type the data will appear in two places:
the active cell and the formula bar.

If the value you enter does not fit into the cell, it will overflow into the cell in the next
column. In order to fit the value, place the cursor between the first two columns the
value occupies. A double arrow with a line in the middle will appear. Double-Click.
Excel will auto fit the column to the width of the cell.

Cell References

In Excel, formulas use cell references to refer to other cells. If you change the content of
the cells, then the result of the formula also change. The cell reference is found in the
*Formula Bar or you put the cursor over the cell, Excel tells you the cell reference.

                                   The cell reference
                                   for 21 is D3.

*If you do not see the formula bar on your screen, then click View and select
Formula Bar.

Entering Formulas by Typing Using Cell References
Type the values in the cell. Click on an empty cell you want to place the formula in.
Once you click the active cell, it will have a blue border around it. Type a formula in the
Formula Bar OR in the active cell.

To write a formula, first, type an equal (=) sign. All formulas need to begin with an equal
sign. Next, enter a cell reference, constant or operator (+ ,-. *, /,^) until finish entering
the formula in the bar. Complete the formula by pressing return or the green
check mark icon. You should now see the result of the formula in the cell.


Click on the cell you want to enter a formula. Type an equal sign. Click on the cells you
want to reference. The cell will have a gray fuzzy boarder around it. If you click on the
cell references without operations, Excel assumes you want to add the references to the
formula. When you have finished entering the formula, press return or click on the green
check mark icon.

Tip: Using arrow will change the cell reference. For example, if I click on C2 and then
use my arrow key to move to C3, then the formula will read =B2+C3 instead of =B2+C2.

Entering Functions

Functions are predefined formulas Excel uses to make calculations. A common function
used in Excel is the SUM Function. The SUM Function allows you to add a range of

Click on the cell you want to type in the formula. Type an equal sign. Go to the Insert
menu and select Function. The Paste Function dialogue box will appear. Select the
function you want to use. Click “OK”.

  After you click “OK”, another dialogue box will appear. In the dialogue box, Number 1
  references the range of cells to be summated. Excel automatically selects the range of
  numbers that it thinks is most logical to summate. Check the cell references, edit if
  necessary. Click “OK”
                                                                        Cell references
                                                                        of numbers to
Box A

   The result of the formula should be displayed in the active cell.



You can also use a button on the toolbar to create a formula.
First, like the above method, click on the cell in which you want to use the formula. In
the cell, enter an equal sign (=). Once you do this, the upper-left hand box next to the
tool bar will turn into a function box.

You will be able to decide what function you want to use by clicking on the downward-
pointed arrow on the right of the box.

                                              Once you click on this arrow,
                                              a list of functions will appear.
                                              You simply need to highlight
                                              the function you want to use.
                                              In this example, the IF
                                              function is highlighted.

Once you have decided your function, Box A (above) will appear. You may follow these
directions to complete your function.


Another way to enter a formula is by using a different icon in the toolbar.
First, click on the cell in which you want to place your formula. Next, click on an icon in
the middle of the tool bar.

                                                                        If you click on the
                                                                        downward-pointed arrow on
                                                                        the right, it will give you
                                                                        options as to what you want
                                                                        your formula to be.
                                                                        Highlight the desired

 Once you click on the function, the computer will automatically choose a range for your
function. This will show up in the active cell, and a dotted line will surround the cells
that are being calculated.

                                      If for some reason the computer has not chosen the
                                      values that you want calculated, you can adjust the
                                      values by either changing them in the formula bar, or
                                      the active cell, or by adjusting what is inside the
                                      dotted-line box. For example, on the left, the dotted
                                      line box includes B2(4), B3(87), B4(3), and B5(54). I
                                      did this by first clicking on B2(4), then holding and
                                      dragging down to B5(54). Once I let go of the mouse
                                      button, the dotted line box comes out like on the left.
Once you press enter, the value
will appear. In this case, 148.

Creating Charts

First, on your table, highlight what you want to be included in your chart.

Above, the light blue line encompasses what you have highlighted. One way to easily
highlight everything, is to click on the diamond icon on the upper left hand side. Another
way to highlight just certain values is to click on cell A1, then hold and drag to where
you want. In the above example, I clicked first on A1, then held and dragged to E6.

After you have highlighted your values, click on the chart wizard icon. This is icon is in
the middle of the top row of icons.

Once you click on this icon, a table will appear.

                                                    On this table, you can
                                                    choose what type of chart
                                                    you want to make. There
                                                    are many options, as there
                                                    are even “chart sub-types”
                                                    within each chart type.
                                                    Some chart types are more
                                                    appropriate than others,
                                                    depending on the type of
                                                    values you have selected.
                                                    Once you have chosen, click
                                                    on “Next>”. I have chosen
                                                    the “Line” chart type, with
                                                    the specific subtype
                                                    highlighted in blue.

     In this next table, you have a few
     options as to how your chart may
     First, the “Data range” box
     reiterates what you want to show in
     your graph. Generally, if you have
     correctly highlighted your values,
     the values will be fine.
     Next, the “Series in” option,
     between Rows and Columns, gives
     you the option of viewing the
     values based on how you arranged
     your information in your table.
     For example,


To top