Microsoft Excel by pengxuebo

VIEWS: 3 PAGES: 15

									                          Lab 3 - Spreadsheets and Charts with Microsoft Excel

A computer spreadsheet application can contain various types of information including text, numbers,
dates, and formulas. Spreadsheets allow for efficient computation of formulas while providing organized
storage for the data as well as the computed results. They are effective tools for many business and
bookkeeping applications such as maintaining grade books, inventories, and account balances to name a
few.

Getting Started

The spreadsheet application can be opened by double clicking on the Excel spreadsheet icon in the desktop
window or by clicking the Start Menu > All Programs > Microsoft Office > Microsoft Excel. The
spreadsheet user interface has a regular grid pattern, with numbered rows along the left side and columns
across the top labeled with letters of the alphabet. The box at the intersection of a row and column is called
a cell and is identified by its row and column designation. Each spreadsheet is called a worksheet. By
default there are three blank worksheets in a new spreadsheet file. They can be accessed from tabs labeled:
Sheet1, Sheet2, and Sheet3 at the bottom of the spreadsheet interface. Clicking on the tabs will shift from
one active worksheet to another. The tabs can be given more meaningful names by highlighting the tab
(Sheet1, Sheet2, or Sheet3) and typing a new name, or right-click and then choose Rename. More
worksheets can be inserted. Each worksheet has 8 million cells.




Figure 1

A Typical Spreadsheet Application

Suppose that you wanted to include the following information about your next paycheck in a spreadsheet:

Monthly Income (gross)     $1500
Medical Insurance          $65.00
Life Insurance             $39.00
Income tax                 15%
Final Monthly Check

The information can be entered in the worksheet
cells as shown in Figure 2.

                                                      Figure 2



                                                      1
 In cell B4, we use a formula, which calculates withholding at 15 percent of the monthly income. All
 formulas start with an equal sign. The numbers used in the calculations are referred to by their cell
 addresses. So the formula inserted in cell B4 will be typed according to Figure 3 below.




                                                    Formula




 Figure 3: To calculate the withholding amounts of the income tax, the formula is entered into cell B4.


 We can now calculate the final take-home pay, based on another formula inserted in cell B5. The final pay
 is equal to the monthly income less all the deductions. (Final Monthly Check = Monthly Income – Medical
 Insurance – Life Insurance – Income Tax) The cell addresses are used in the formula not the actual values.
 This allows the values in the cells to be changed and the values in the formula cells to be automatically
 recalculated. (See What-if analysis in your text and below.) When that formula is typed in B5, the value of
 the final monthly check will be calculated and shown in cell B5. The complete spreadsheet is shown in
 Figures 4 and 5 below. Figure 5 shows the calculated values in cells B4 and B5.




                                                                                Formula Box
                                              Formula



Figure 4




                         Figure 5:
                         Shows
                         nShows
 The What-if analysis is the biggest advantage of using an electronic spreadsheet. All of the values can be
 changed and all of the calculations are recalculated automatically because the formulas refer to the values
 within the cell addresses not the actual values. Suppose, for example, that the next month, you get a raise to
 1800 per month. You can use the same spreadsheet, since all
 the cells that contain numerical data are dynamic. This
 means that any formula, which refers to cell addresses, will
 use the current values in those cells in the calculations. In
 the formulas in B4 and B5, the monthly income is always
 referenced as cell B1, not the values 1500 or 1800. Given a
 new value of 1800 for the monthly income in cell B1, the
 formulas in B4 and B5 will pick it up and properly figure out
 new values for withholding (B4) and net monthly pay (B5).
 The revised spreadsheet is shown in Figure 6.
                                                                     Figure 6

                                                       2
Formatting the Data

Formatting the data allows the cells to be represented
in the appropriate context, including numbers, dates
and text. To add formatting, highlight the desired cell
or group of cells and choose Format > Cells on the
menu bar. An attribute dialog box will open with a list
of choices about formatting cell contents. You may
alternatively right-click after you have highlighted the
cells to get a pop-up menu with the option format
cells.

Add Notes

Notes are character strings that are not part of the cells
but are attached to them, a kind of annotation. To add
a note to a cell, select the desired cell by clicking on it.  Figure 7
Next, select Insert > Comment from the menu bar, and
the program inserts a yellow box with a black border for inserting text. When you are finished inserting
text, just click outside the note box and the note will no longer be visible. To activate the note, move the
cursor over the cell near the tiny red spot in the upper right corner (signifying that a note is there), and the
note appears temporarily.




              Figure 8


Formulas and Additional Spreadsheet Features

Moving Around in a Spreadsheet

Just click inside a cell to make it active. To highlight an entire row or column, click on the number of the
row or letter of the column. All the cells will be activated. Dragging over a range of adjacent cells in any
direction (over either row cells or column cells) will highlight all of them. Dragging the mouse down
diagonally over several rows and columns will highlight all of them. Alternatively you can select cells with
the arrow keys (up, down, left, and right) while holding down the Shift key. You can also select one cell
and select another while holding all the Shift Key down to select all the cells in between.




                                                       3
Adjusting the Size of Rows or Columns

For widening columns, move the cursor carefully between column letters without clicking the mouse.
When the cursor turns into an arrow pointing left and right, you can drag it with the mouse button held
down and widen or narrow the column to any desired width.




    Figure 9



Adding and Deleting Rows and Columns

To add a row, click the mouse in the row where a new row is wanted and choose Insert > Rows from the
menu bar. A new blank line will push down the row where you clicked the mouse. To add a column,
choose Insert > Column from the menu bar. The new column will be inserted to the left of the selected
column. To delete a row, highlight it, and choose Edit > Delete. A dialog box will open that offers Delete
entire row as an option. See the figures below.

                                                        Figure 10 – Dialog box to from selecting Insert >
                                                        Cell




                                                        Figure 11 – Dialog box to delete cell(s), row(s)
                                                        and/or column(s)




                                                    4
Worksheets: Adding, Deleting and Renaming

Remember by default a spreadsheet comes with three worksheets. To add or delete a worksheet, choose the
commands Insert > Sheet or Edit > Delete Sheet from the menu bar. You can also right-click on the tab and
choose insert > worksheet or choose rename to put a new name on the tab.


More about Spreadsheet Formulas

Creating Formulas

In Excel there are three ways to enter a formula: by typing it directly into a cell, by typing it into the
Formula box, and by using the Automatic Formula function to create it. In our earlier example, we could
easily type the two formulas directly into cells B4 and B5. Excel always uses the equal sign to start a
formula. It is the equal sign, which tells the spreadsheet program that a calculation is coming in that cell,
instead of a number or label or date. When you press enter, the formula will be inserted into the cell.
Later, to see the formula, just click on the cell and the formula will be displayed in this formula box. If it
contains an error or needs to be changed, it can easily be edited here before pressing the Enter key again.

Spreadsheet Functions

Sometimes it is not convenient to create very long formulas. For instance, to enter the following formula
for 100 figures:

         = (J1+J2+J3 … J100) / 100

For cases like this, the spreadsheet software has already stored functions for many common operations
(mathematical, statistical, and logical). These functions can be activated with Insert > Function in the menu
bar, which brings up a dialog box. Having chosen a function, the user then gets a function shell in the
Formula Bar and must put in the proper cell addresses before the formula is carried out. In this case, the
function needed is called AVERAGE, and the formula for averaging would look like the following after it
is filled in with the cell addresses:

         = AVERAGE (J1:J100)

                                                  Figure 12 – Dialog box after selecting Insert > Function
                                                  on the menu bar




                                                                      Figure 13 – Dialog Box after selecting
                                                                      Average, and the appropriate cells to
                                                                      be used.




                                                      5
 Figure 14. If the cell chosen for the result is at the bottom of a column, the cell range will be automatically
selected as that column. You can also just enter the cells to be averaged or the range of cells.

Built into the function is the summing of the numbers in cells J1 to J100 and dividing them by 100, the
number of elements. In a case like this with a range of cells, you specify them with shorthand of the first
and last separated by a colon. Without using the Insert menu, the AVERAGE function can also be typed
directly into the Formula Window with the elements inserted within the parenthesis, using the colon as a
separator.

                                                                   Figure 15 – Manually typing the
                                                                   AVERAGE formula into the formula box.




The Automatic Formula Function

A feature of Excel which allows you to use functions easily is the Automatic Formula function. Its icon,
located next to the  icon, resembles an Fx. Let us examine the Automatic Formula function in detail.




Figure 16 - First click the cell where the formula goes, then click the Automatic Function Icon.




                                                       6
                                               Figure 17. Within the dialog box, choose the category and
                                               appropriate formula. To select a formula, double click on its
                                               name, and another dialog box opens up.




Figure 18 –Click on the button pointed to
by the arrow and then highlight all cells
that are part of the calculation.




A description of the formula appears at the bottom of the dialog box. Above that, the dialog box shows the
user how to fill in the arguments. After the correct arguments are inserted, the result is displayed below the
range in the menu box. If it is correct, choose OK, and the formula will be inserted into the cell.




                  Figure 19 – The red arrows point to the result of this Sum function.

It is possible to put literal numbers instead of addresses in the argument of a function like AVERAGE or
SUM, and the formula will average them. Doing this, the user is forcing the function to work on specific
numerical data rather than the value typed into the addresses, such as A1, B2, etc.

Excel has a great variety of prepared functions like AVERAGE or SUM already created for use. Perhaps
you should open the Automatic Formula function dialog box and check some of them out before writing
formulas for yourself. As you move through the list of functions, one click on any formula name in the
dialog box will bring up a description above the Formula text box (remember that a double click brings up
the formula for inserting arguments). Take a moment to look at some of these possibilities to see how they
work.

You can make your own formulas. For example, suppose cell A10 looks like this: = 2+3*4


                                                      7
          Figure 20

In this case, the formula is calculating with numbers, not cell addresses. A question remains: is the answer
20 (2 plus 3 times 4) or 14 (3 times 4 plus 2)? The answer is 14, since the spreadsheet follows the rules of
algebra, which carries out multiplication and division before addition and subtraction. To overrule this
convention, use parenthesis liberally. In other words, to get 20, the formula in cell A10 should be = (2+3)*4




                                                                                             Figure 21
                                                                                             Changing the
                                                                                             Formula


A spreadsheet cell can also be used to make logical choices for the user with the IF function. As an
example of its use, consider the following case: if the price of Stock A reaches $40, then I will sell; if not, I
will buy more in the next month. A condition is checked with two alternative actions. The IF function
allows the spreadsheet to examine a changing condition and carry out different actions based on the
changes.

The general construction of the logical IF function is as follows:

         =IF(logical-condition,true-value,false-value)

To see how it works, consider the following IF function located in cell A12:

         =IF(A6>A7,A11-A10,0)

The function will compare the values in cells A6 and A7. If the value in A6 is greater than the value in A7,
then the true case applies and the value in A10 will be subtracted from the value in A11. The result will be
placed in cell A12, where the IF function is located. If the value in A6 is not greater than the value in A7,
then the value 0 will be placed in the A12 cell.


                                                                                             Figure 22 – IF
                                                                                             function with
                                                                                             sample values




                                                       8
The three part IF function can be understood in logical terms:

   IF the value in A6 is greater than the value in A7
   THEN subtract the value in A10 from the value in A11 and place the result in cell A12
   ELSE put zero in A12


                                                           Figure 23 – Results of the Formula in the
                                                           Spreadsheet



                                                           IF function makes the spreadsheet a very powerful
                                                           tool for making logical choices by having the
                                                           ability to test and choose what to do next depending
                                                           on the results of the test.

                                              In a large and complex spreadsheet, a good rule of
                                              thumb is to consider adding a note to a cell
                                              containing any complicated formula to remind
                                              yourself what you were intending when the formula
                                              was created. Several weeks and hundreds of cells
                                              later, you may look at this cell and forget what it is
                                              supposed to be calculating. The text note will be a
                                              reminder of the thinking which created it.
Remember that we used a note in the take-home pay example which introduced the discussion of
spreadsheets.



Filling Adjacent Cells with Data or Formulas

One of the most useful features yet to discuss with spreadsheets is the concept of filling adjacent cells with
data of formulas rather than typing or cutting and pasting them manually. For instance, suppose that you
have a cell, D6, with a number 10 in it. The next several cells from D7 to D10 should be increased by 10
each so that D7 will have 20 in it and D10 will have 50. A shortcut to typing all this involves filling. First
put the formula =D6+10 in cell D7. That will take care of the 20 which goes there. With that cell
highlighted, place the cursor on the bottom-right corner of the highlighted cell till you see a crossbar. Drag
the mouse over all the cells through D10. When you release the mouse, the formula will be applied to all
the cells, making each 10 larger than the one above.

An even quicker solution to this example uses the automatic fill handle. First put a 10 in cell D6 and a 20
in D7. Then select both of these cells with the mouse. You can grab the fill handle on the bottom-right
corner of D7 as explained above and drag it down through cell D10. When you have finished, all cells will
have the correct numbers, each one being 10 greater than the one above it. The program recognizes the
relationship between the first two cells (the increment of 10) and continues to apply it across the whole
range. This will work with more complex relationships or with a single value. If you need a column with
all of the values equal to 100, you can type 100 in the first cell an drag the fill handle down to fill the other
cells.

Formula inconsistency

If a formula is inconsistent with other formulas in nearby cells the inconsistent formula will be flagged with
a little triangle in the upper left corner of the cell. When the cell is selected the AutoCorrect Options button



                                                       9
appears. If you click the button, a menu opens with more options including copying the formula from other cells, correcting errors in
formulas, and ignoring the inconsistency.

Displaying all formulas

Use Control ~ to display all of the formulas on the spreadsheet instead of the values. You can then edit the
formulas. Press Control ~ again to return to the calculated values.

Toolbars and Other Spreadsheet Concepts

Let us look at some of the additional special features of Excel that make using spreadsheets easy and
convenient. One is the  icon on the formula toolbar next to the Auto Function icon. It is called the Sum
icon. If you need to sum up a row or column of figures, you can do this with the shortcut involving one
mouse click. For instance, let us assume that row 4 has four numbers in it, columns B through E. If you
click cell F4 and then the Sum icon, the formula =SUM(B4:E4) will be created for your approval. The icon
then turns into two icons, a checkmark to Accept or an X to cancel the formula before it is executed. If you
accept the formula, the sum of the numbers in row 4 will be in cell F4. We shall assume cell A4 has no
numbers in it; maybe it carried a text label. Nevertheless, the sum of the four legitimate numbers will be
carried out. If A4 also contains a number, the sum will be for the five consecutive numbers in row 4. This
Sum icon is useful for cases where a quick addition of consecutive cells, either in rows or columns, is
needed.

Formatting

The formatting toolbar includes all the typical formatting features that were used in the word processor to
be used in displaying the values in the cells. Further to the right of the formatting icon are a number of
icons suitable for formatting numbers in cells. There are icons for displaying a cell number as a currency
or percentages. There are also icons to align figures within a cell to the left, right or center of the cell,
depending on the size of the cell. These options and many others can be found under Format > Cells on the
menu bar.

Filtering and Sorting

AutoFilter can be accessed through the menu bar by selecting Data > Filter > Autofilter. Using the filter,
you can split out individual sections of your spreadsheet for inspection. For instance, you might want to
look at categories of expenses in a very large budget spreadsheet. The individual items for the same
category could be rows or columns away from each other and not readily examined. However, with the
filter, they will be collated and shown in their own window. AutoFilter places a small dropdown arrow
over the selected column or columns if all are to be examined. Using the drop-down arrow choose the
category of information to filter. The first choice, all, is the default. For example, you could look at all
travel expenses among business items. When the data is filtered it is done over the existing worksheet, no
new windows open. A second mouse click on the AutoFilter icon will return you to the full spreadsheet.
AutoFilter > Show All is designed for looking at the filtered information.

For sorting, use the dialog boxes under Data > Sort from the menu bar, which allows sorting on more than
one field. It gives you the option to select what column to use as the sorting category and whether to sort in
ascending or descending order. You may also select second and third sorting columns which will be used in
case there are two cells with identical data in the first sort criteria column.

Goal Seek

Another powerful feature of Excel is Goal Seek, available under Tools > Goal Seek. Before seeing it in
action, let us look at a situation where it is used. Suppose that you are trying to sell 100 computers, fifty of
which cost you $400 apiece and the other fifty which cost $500 apiece, and want to make a profit of
$50,000. What price should you sell them for? Goal Seek is the ideal for any situation in which you know
the desired result, a profit of $50,000 in this case, before you known one important item that is needed to
achieve the result, the sales price for each computer. The relationships between the elements are known:


                                                                10
        50 * (sales price – 400) + 50 * (sales price – 500) = 50000

In the spreadsheet, the formula needs to be placed in one cell, and another needs to be selected for the
unknown value, the sales price. For instance, cell A1 could contain the formula, with cell B1 chosen for
the unknown sales price. The formula above is then typed in A1 as follows:

        =50 * (B1 – 400) + 50 * (B1 – 500)

After choosing Goal Seek under Tools, a dialog box appears, containing the following three lines: Set Cell,
To Value, By Changing Cell. In the Set Cell, we enter A1, this is our formula cell. In the To Value cell we
enter 50000, our target value. Finally, B1 is entered in the By Changing Cell, where the unknown value
will go. After all of these are inserted and the OK button clicked, the program returns a message that the
Goal Seek worked. The unknown value is 950. In other words, if all 100 computers are sold at $950, the
profit will be $50,000. This feature offers a powerful tool to someone who knows the bottom line of some
process but is missing a key component of the process. After it is set up, Goal Seek also allows one to
change the bottom line quickly and see the results.

Making Charts and Graphs from Spreadsheets

Charts and graphs can be made in Excel from data in a spreadsheet. To create a chart, select Insert > Chart
on the menu bar or select the Chart Wizard icon, which resembles a multi-colored bar graph. The Chart
                                                                       Wizard will pop up on the screen
                                                                       as a series of four dialog boxes. In
                                                                       the first dialog box the type of
                                                                       chart and the orientation or sub-
                                                                       chart are selected. The second box
                                                                       gives the user the opportunity to
                                                                       select the items to be charted by
                                                                       selecting the data in the
                                                                       spreadsheet. If an area was
                                                                       selected before the Chart Wizard
                                                                       was opened that area will appear
                                                                       in the dialog box. You can change
                                                                       that area by highlighting another
                                                                       area of the spreadsheet with the
                                                                       mouse. The next dialog box
                                                                       allows you to insert titles, legends,
                                                                       and labels. The fourth dialog box
                                                                       gives the option to choose where
                                                                       the graph will be displayed, either
                                                                       in one of the existing worksheets
                                                                       or in a new worksheet. The graph
                                                                       can be dragged to a new location
                                                                       or copied to another document
                                                                       after if is displayed. There is help
                                                                       box in all of the Chart Wizard
  Figure 24                                                            dialog boxes.




                                                    11
Figure 25 – Chart Wizard Step 2. You can
click the button pointed to by the red arrow
to roll the box up to allow you to highlight
the desired cells.




                                                    Figure 26 – The series tab. Add a
                                                    new series by clicking the button
                                                    pointed to by the red arrow, and
                                                    highlighting the cells corresponding
                                                    to the series. Re-click the button to
                                                    return to the prompt. Presses add to
                                                    add the series. You may also select a
                                                    set of cells for the category X-axis
                                                    label for each series. These cells will
                                                    be used to label the intervals between
                                                    tick marks on the X-axis. Note that
                                                    you may either select your chart data
                                                    using data range or series. Do not do
                                                    both.




                                               12
Figure 27 – Chart Wizard
Step 3. You choose how
you want the chart labeled.




Moving and Resizing a Graph

Once you have pressed Finish, the graph will be displayed on the worksheet where you have chosen to put
it. If you click once on the graph, it will be selected as an object that can be moved around or resized. You
will notice that at the edges there are little boxes. Grabbing them with the mouse allows stretching or
shrinking the graph to fit your needs. Also you should be able to select the graph and move it around on
the worksheet where it was created. It can also be cut or copied and pasted in a new location, in another
worksheet, or in another document. Clicking on individual elements of the graph, such as the title, legend
or labels, will also allow them to be edited. .

Selecting Non-Adjacent Cells for Graphing

You may need to graph cells that do not lie adjacent to each other in a spreadsheet. Click in the first cell,
then hold down the Control key, and click on the other cells to select, wherever they are in the worksheet.
When you have finished, you will have selected a group of nonadjacent cells that can be graphed.

Editing a Chart or Graph

Often you forget something or need to make changes once a graph has been created. Excel has options to
allow further editing after the graph is created. If you move the cursor onto the chart and right-click, a
shortcut menu appears that allows editing many of the features of the graph. A different menu will appear if
you right-click on the legend or labels. Double-clicking on the chart will open a menu box that allows you
to make many format changes including changing the colors in the chart.

Macros
If you perform a task repeatedly in Microsoft Excel, you can automate the task with a macro. A macro is a
series of commands and functions that are stored in a Microsoft Visual Basic module and can be run
whenever you need to perform the task. For example, if you often enter long text strings in cells, you can
create a macro to format those cells so that the text wraps within the cell.




                                                     13
Recording a Macro

1.   On the Tools menu, point to
     Macro, and then click
     Record New Macro.

2.   In the Macro name box,
     enter a name for the                                                                 Stop           Relative
                                                                                          Recording      Reference




                                                           Figure 28
Notes

    The first character of the macro name must be a letter Other characters can be letters, numbers, or
     underscore characters. Spaces are not allowed in a macro name; an underscore character works well as
     a word separator.

    Do not use a macro name that is also a cell reference or you can get an error message that the macro
     name is not valid.

3.   If you want to run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key
     box. You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters),
     where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or
     special character such as @ or #. Note The shortcut key will override any equivalent default Microsoft
     Excel shortcut keys while the workbook that contains the macro is open.

4.   In the Store macro in box, click the location where you want to store the macro. If you want a macro
     to be available whenever you use Excel, select Personal Macro Workbook.

5.   If you want to include a description of the macro, type it in the Description box.

6.   Click OK.

7.   If you want the macro to run relative to the position of the active cell, record it using relative cell
     references. On the Stop Recording toolbar, click Relative Reference so that it is selected. Excel will
     continue to record macros with relative references until you quit Excel or until you click Relative
     Reference again, so that it is not selected.

8.   Carry out the actions you want to record.

9.   Click the Stop Recording button once you are finished.



Running a Macro
1.   Open the workbook that contains the macro.

2.   On the Tools menu, point to Macro, and then click Macros.

3.   In the Macro name box, enter the name of the macro you want to run.



                                                      14
4.   Click Run. If you want to interrupt, press ESC.

Start a Macro from a Keyboard Shortcut

1.   On the Tools menu, point to Macro, and then click Macros.

2.   In the Macro name box, enter the name of the macro you want to assign to a keyboard shortcut key.

3.   Click Options. Assign a shortcut key to the macro if you have not already done so as explained above.

4.   Click OK.

5.   You may now use the assigned shortcut key to start the macro.




                                                       15

								
To top