### ![](media/image1.png){width="5.03125in" height="3.78125in"}

### 

![](media/image2.png){width="5.0in" height="3.75in"}

### **Excel Tutorial**

Adapted from <http://einstein.cs.uri.edu/excel97/excel.html> Also used:
<http://www.baycongroup.com/el0.htm> **The BayCon Group**
<denise@baycongroup.com> **and** <http://www.usd.edu/trio/tut/excel/> by
<bwjames@usd.edu> and Energy Information Administration -- Henry Weigel,
Presented in Bangladesh, February 2001

**[1. What Is Excel ?]{.underline}**

Microsoft Excel is an electronic spreadsheet program. You might of heard
the terms \"spreadsheet\" and \"worksheet\". People generally use them
interchangeably. To remain consistent with Microsoft and other
publishers the term ***worksheet*** refers to the row-and-column matrix
sheet on which you work upon and the term ***spreadsheet*** refers to
this type of computer application. In addition, the term ***workbook***
will refer to the book of pages that is the standard Excel document. The
workbook can contain worksheets, chart sheets, or macro modules.

A spreadsheet is the computer equivalent of a paper ledger sheet. It
consists of a grid made from columns and rows. It can make number
manipulation easy and somewhat painless.

  ---------------------------------------- -- ----------------------------------------------------
  ![](media/image3.png){width="2.1875in"      ![](media/image4.png){width="2.2083333333333335in"
  height="1.3229166666666667in"}              height="1.1145833333333333in"}

  ---------------------------------------- -- ----------------------------------------------------

The math that goes on behind the scenes on the paper ledger can be
overwhelming. If you change the loan amount, you will have to start the
math all over again. Look at a worksheet.

+--------------------------------------+--------+----------------------+
| ![](media/ima                        | Ch     | ![](media/image      |
| ge5.png){width="3.589583333333333in" | anging | 6.png){width="1.0in" |
| height="3.2625in"}                   | the    | height="0.           |
|                                      | column | 8402777777777778in"} |
|                                      | width. |                      |
|                                      |        | ![](media/           |
|                                      | Moving | image7.png){width="2 |
|                                      | to a   | .0902777777777777in" |
|                                      | New    | height="0.           |
|                                      | Wor    | 6666666666666666in"} |
|                                      | ksheet |                      |
|                                      |        | ![](media            |
|                                      | Lo     | /image8.png){width=" |
|                                      | cating | 1.632638888888889in" |
|                                      | the    | height="1.           |
|                                      | fill   | 3159722222222223in"} |
|                                      | h      |                      |
|                                      | andle. |                      |
+--------------------------------------+--------+----------------------+

**[The Workbook: Sheets, Rows, Columns, Cells.]{.underline}** The Excel
workbook can contain as many as 256 sheets, labeled Sheet1 through
Sheet256. The initial number of sheets in a workbook, which can be
changed by the user is 16. Each sheet consists of rows (as many as
16,384) and columns (as many as 256). The intersection of a row and
column is a rectangular area called a ***cell***. ***Cell references***
are the combination of column letter and row number. For example, the
upper-left cell of a worksheet is A1. A cell can contain a value, a
formula, or a text entry. A text entry is used to label or explain the
contents of the workbook. A value entry can either be a constant or the
value of a formula. The value of a formula will change when the
components (***arguments***) of the formula change. The appeal of
spreadsheet programs is the ability to change one value and watch all
other values that depend on that first value automatically change when
the spreadsheet is recalculated.

**[2. Getting Started]{.underline}**

Excel displays a new workbook when it is opened. In a new workbook all
the cells are empty. A cell is active when the border is highlighted in
blue. When you enter information, the information is stored in the
active cell. Let\'s learn how to enter information into a workbook.

  ----------------------------------- -----------------------------------------------------
  **[Entering Text and                ![](media/image9.jpeg){width="2.9243055555555557in"
  Constants.]{.underline}** Click on  height="1.7173611111111111in"}
  the Excel window, select a cell by  
  clicking on it, and enter: Excel is 
  fun. See the example.               

  ----------------------------------- -----------------------------------------------------

Observe that your text is displayed in two areas. Text is displayed in
the active cell within the workbook and it is also displayed in the
***formula bar***. The formula bar is activated as soon as you begin
typing in a cell. At the far left is the ***reference section***, which
will show the reference of the active cell. Next to the formula bar are
the **Cancel** and **Enter** buttons
(![](media/image10.jpeg){width="0.4583333333333333in"
height="0.19791666666666666in"}). The **Cancel** and **Enter** buttons
are only visible while Excel is in edit mode. Excel is in edit mode
anytime you begin typing an entry. To put Excel in edit mode, click in
the formula bar.\
\
Within the Excel window, click in the formula bar to display the
**Cancel** and **Enter** buttons. The **Enter** button enters the text
you typed into the cell. You could also press the Return key on the
keyboard. If you want to edit the text you entered into a cell, you
click the formula bar, type your changes and click on the **Enter**
button. The **Cancel** button cancels your changes.\
\
Entering **constant values** is the same as entering text, except that
constant values are right-justified by default. You will learn how to
change this default when you learn other formatting changes. **Entering
Formulas**: All formulas in Excel must begin with an equal sign (=).
When a formula is entered into a cell, the formula itself is displayed
in the formula bar when that cell is highlighted, and the result of the
formula is displayed in the actual cell. When you are typing in
formulas, do not type spaces; Excel will delete them. For example,

+-----------------------------------+-----------------------------------+
| Select cell A2 and enter the      | ![](m                             |
| constant value 12                 | edia/image11.jpeg){width="2.75in" |
|                                   | height="2.183333333333333in"}     |
| Select cell A3 and enter the      |                                   |
| constant value 15                 |                                   |
|                                   |                                   |
| Select cell A4 and click on the   |                                   |
| formula bar                       |                                   |
|                                   |                                   |
| Enter an equal sign followed by   |                                   |
| A3+A4                             |                                   |
|                                   |                                   |
| Click on the **Enter** button or  |                                   |
| press the Return key to enter the |                                   |
| formula.                          |                                   |
+-----------------------------------+-----------------------------------+
| Your worksheet should look as     | ![](media/image12.                |
| shown at right.                   | jpeg){width="2.997916666666667in" |
|                                   | height="2.342361111111111in"}     |
| Excel displays the result of the  |                                   |
| formula in cell A4.               |                                   |
|                                   |                                   |
| Within the Excel window, select   |                                   |
| cell A3 and change the number 15  |                                   |
| to the number 40, and enter the   |                                   |
| edit.                             |                                   |
|                                   |                                   |
| The formula value should have     |                                   |
| changed in cell A4 to the number  |                                   |
| 52.                               |                                   |
+-----------------------------------+-----------------------------------+

## [3. Creating a Simple Workbook]{.underline}

To create a simple workbook, you need to start with a blank workbook.
Choose **New** from the **File** menu. You can resize and position the
new workbook.

+-----------------------------------+-----------------------------------+
| In this part of the tutorial you  | ![](media/image13.j               |
| will be creating a check          | peg){width="2.7993055555555557in" |
| register. Starting in cell A1,    | height="2.5805555555555557in"}    |
| build the table at right:         |                                   |
|                                   |                                   |
| To move through your worksheet,   |                                   |
| you can use the arrow keys or     |                                   |
| place the cursor in the cell with |                                   |
| the mouse. Enter the text and     |                                   |
| numbers as shown.                 |                                   |
|                                   |                                   |
| Remember to click on the          |                                   |
| **Enter** button or press the     |                                   |
| Return key to enter each check    |                                   |
| number.                           |                                   |
+-----------------------------------+-----------------------------------+

+------------------------------+---------------------------------------+
| Select cell B1 and type in   | ![](media/image                       |
| the text: **Data**.          | 14.jpeg){width="3.5756944444444443in" |
|                              | height="2.6243055555555554in"}        |
| Starting in cell C1, enter   |                                       |
| text as shown.               |                                       |
|                              |                                       |
| Select cell D1, and type the |                                       |
| following text: **Payment    |                                       |
| Amount**. Observe that the   |                                       |
| text is too large for the    |                                       |
| cell. You can change the     |                                       |
| width of the cell and as a   |                                       |
| result the whole column to   |                                       |
| fit the text.                |                                       |
|                              |                                       |
| Enter Column D values        |                                       |
| starting in D3.              |                                       |
+------------------------------+---------------------------------------+

**[Changing Column Widths.]{.underline}** Position the pointer between
the column headings for column D and column E. The pointer should change
shape to show a double arrow as you position the pointer between the two
column headings. When the pointer changes shape, you can change the
width of the column by dragging to the right or left. Press the mouse
button and drag to the right until the width of column D will fix the
text.\
\
Enter text and values in Column E starting in cell E1. Make sure you
change the column width of column E. Enter text and values in Column F
starting in cell F1.

![](media/image15.jpeg){width="5.9375in" height="2.6666666666666665in"}\
\
It is now time to save your worksheet. Choose **Save** from the **File**
menu or click on the **Save** button and call your worksheet \"checks\".
Before you add more to your \"checks\" worksheet, you will need to learn
how to write formulas using arithmetic operators and functions.

**[4. Writing Formulas Using Operators and Functions]{.underline}**

***Operators*** are what connects the elements of a formula. Some
familiar operators are: addition (+), subtraction (-), multiplication
(\*), and division (/). There is an ***order of operations*** when you
are evaluating a formula. Formulas are evaluated from left to right,
with expressions enclosed in parentheses evaluated first, then
exponents, multiplication, division, addition, and subtraction. Excel
has many more operators, but we will work with the operators listed here
for now.

+----------------------------+-----------------------------------------+
| Sample Spreadsheet         |   -------------------- ----             |
|                            | ---- ----------- ------------- -------- |
| +---+--------+---------+   |   **Operation**        Sy               |
| |   | #      | ##### B |   | mbol   Constant\   Referenced\   Answer |
| |   | #### A |         |   |                                         |
| +---+--------+---------+   |              Data        Data           |
| | * | 5      | 3       |   |                                         |
| | * |        |         |   |   **Multiplication**                    |
| | 1 |        |         |   |   \*       = 5 \* 6    = A1 \* B3    30 |
| | * |        |         |   |                                         |
| | * |        |         |   |   **Division**                          |
| +---+--------+---------+   |    /        = 8 / 4     = A3 / B2     2 |
| | * | 7      | 4       |   |                                         |
| | * |        |         |   |   **Addition**                          |
| | 2 |        |         |   |   \+       = 4 + 7     = B2 + A2     11 |
| | * |        |         |   |                                         |
| | * |        |         |   |   **Subtraction**                       |
| +---+--------+---------+   |    \-       = 8 - 3     = A3 - B1     5 |
| | * | 8      | 6       |   |   -------------------- ----             |
| | * |        |         |   | ---- ----------- ------------- -------- |
| | 3 |        |         |   |                                         |
| | * |        |         |   |                                         |
| | * |        |         |   |                                         |
| +---+--------+---------+   |                                         |
+----------------------------+-----------------------------------------+

**[Describing Formulas]{.underline}** Excel allows you to add comments
to explain the purpose of an inserted formula. The comments are
displayed in the **Cell Note** window which can be viewed by choosing
**Note** from the **Insert** menu. Let\'s enter a formula using
operators and comment the formula. Open a new workbook window.

+----------------------------------------+-----------------------------+
| Enter text in Cells A1, A11, and A12,  | ![](media/ima               |
| as shown.                              | ge16.jpeg){width="2.6875in" |
|                                        | hei                         |
| Enter values in Cells B2 through B10.  | ght="2.5208333333333335in"} |
|                                        |                             |
| You are going to enter formulas that   |                             |
| will calculate the total and then the  |                             |
| average of these nine values.          |                             |
|                                        |                             |
| In Cell B11 enter:                     |                             |
| =B2+B3+B4+B5+B6+B7+B8+B9+B10           |                             |
|                                        |                             |
| In Cell B12 enter: =B11/9              |                             |
|                                        |                             |
| Let\'s annotate (comment) the formula  |                             |
| in cell B12. In the Tool Bar, press    |                             |
| Insert, then select Comment. Enter the |                             |
| comment in Comment Box.                |                             |
+----------------------------------------+-----------------------------+

Here is a look at the name box and the formula bar when the formulas for
Total and Average are entered. Select cell B11. It will appear in the
name box and enter the formula as shown. It appears in the formula bar.

![](media/image17.jpeg){width="5.302083333333333in" height="0.21875in"}

Remember to click on the **Enter** button or press the Return key to
enter the formula.\
Select cell B12. It will appear in the name box and enter the formula as
shown. It appears in the formula bar.
![](media/image18.jpeg){width="2.84375in"
height="0.22916666666666666in"}

**[Annotating Formulas.]{.underline}** You can attach notes to cells by
using comments, formerly called cell notes. You can view each comment
when you rest the pointer over the cell or view all comments at the same
time. You can print comments in the same locations where they are
displayed on the worksheet or a list at the end. Microsoft Excel uses
the user\'s name in each comment.

  ----------------------------------- ------------------------------------------------------
  Select cell B12. Choose **Comment** ![](media/image19.jpeg){width="1.6743055555555555in"
  from the **Insert** menu. The       height="1.05in"}
  following **Cell Comment** box      
  should appear:                      

  ----------------------------------- ------------------------------------------------------

Within the Text Note box, type in the text: Average of nine grades and
then click anywhere outside the box. Note that there is a red note
indicator in the upper- right corner of the cell. Anytime you put the
mouse over the red note indicator, your comment will appear. Now that
you know how to compute the average using operators, it is time to learn
how to write a formula using Excel\'s functions.\
\
**[Functions.]{.underline}** Functions are used to form all or part of a
formula. Excel provides two general types of mathematical functions:
those that are used in business applications and those that are oriented
to higher mathematics. In this tutorial we will focus on the business
applications formulas. The **AutoSum** button
(![](media/image20.jpeg){width="0.23958333333333334in"
height="0.23958333333333334in"}) located in the Standard toolbar.
Whenever you click the **AutoSum** button, Excel inserts a SUM()
function in the active cell. Not only will the SUM() function write the
sum formula, but it will make a guess at what range of cells you desire
to sum, and will leave you in edit mode so that you can correct the sum
range. Click on C11 and use the sum function to store the sum of B2:B10
there. Alternatively, you can highlight the cells in a column (or row)
that are to be summed, including one empty cell following the cells to
be summed, in which the function will store the sum. The Sum function is
one of the many functions Excel provides. Excel also provides many
statistical functions in particular the Average function. Excel provides
two ways for entering function names. You can type the name of the
function in if you know it or you can use the ***Function Wizard***.

**[Using The Function Wizard.]{.underline}**

+-------------------------+--------------------------------------------+
| To use the Function     | ![](media/image22.jpeg){width="4.03125in"  |
| Wizard you can choose   | height="3.0729166666666665in"}             |
| **Function** from the   |                                            |
| **Insert** menu or you  |                                            |
| can click on the        |                                            |
| **Function Wizard**     |                                            |
| button                  |                                            |
| (![](me                 |                                            |
| dia/image21.jpeg){width |                                            |
| ="0.2708333333333333in" |                                            |
| height="0.25in"})       |                                            |
| located on the Standard |                                            |
| toolbar. Select cell    |                                            |
| C12 and open the        |                                            |
| **Function Wizard**     |                                            |
| dialog box by either    |                                            |
| method.                 |                                            |
|                         |                                            |
| We want to use the      |                                            |
| AVERAGE function. The   |                                            |
| AVERAGE function will   |                                            |
| take the average of all |                                            |
| the numbers you list in |                                            |
| the parentheses.        |                                            |
+-------------------------+--------------------------------------------+

  ---------------------- ----------------------------------------------------- ------------------------------------------- --
  The Function Wizard    ![](media/image23.jpeg){width="4.547916666666667in"                                               
  will take you through  height="1.7055555555555555in"}                                                                    
  setting up the formula                                                                                                   
  step by step. Within                                                                                                     
  the **Function                                                                                                           
  Wizard** dialog box                                                                                                      
  highlight the Function                                                                                                   
  Category: Most                                                                                                           
  Recently Used and                                                                                                        
  highlight the Function                                                                                                   
  Name: AVERAGE then                                                                                                       
  click on the **OK**                                                                                                      
  button. The dialog box                                                                                                   
  should appear:                                                                                                           

  Enter the range B2:B10                                                       ![](media/image24.jpeg){width="2.71875in"   
  and then click on the                                                        height="2.5208333333333335in"}              
  **OK** button. Click                                                                                                     
  on the **Enter**                                                                                                         
  button or press the                                                                                                      
  Return key to enter                                                                                                      
  the formula.\                                                                                                            
  Your workbook should                                                                                                     
  look as at right:                                                                                                        
  ---------------------- ----------------------------------------------------- ------------------------------------------- --

The next function we will discuss is **IF**. The IF function will check
the logical condition of a statement and return one value if true and a
different value if false. The syntax is:

=IF (condition, value-if-true, value-if-false)value returned may be
either a number or text. If the value returned is text, it must be in
quotes.

+-----------+-----------------------+---------------------------------+
| Lets use  |   --- --              |   ------------                  |
| the table | --------- ----------- | ------------ --------- -------- |
| here for  |       A           B   |   Example of                    |
| the       |                       |  **IF**\       Compare   Answer |
| d         |   1                   |   in column B                   |
| iscussion |    Price       Over a |              Col. A    in Col B |
| that      |                       |                                 |
| follows.  |               dollar? |                      value      |
| We will   |                       |                                 |
| look at   |   2   \$.95       No  |   =IF                           |
| several   |                       |                    is ( .95  No |
| different |   3   \$1.37      Yes |   (A2\                          |
| specific  |                       | >1,\"Yes\",\"No\")   \> 1)      |
| examples  |   4                   |                                 |
| that show | comparing   returning |   =IF (A3                       |
| how the   |       \#          \#  | \>1, \"Yes\",     is (1.37  Yes |
| IF        |                       |   \"No                          |
| functions |                       | \")                  \> 1)      |
| can be    |  5   14000       0.08 |                                 |
| used!     |                       |   =IF (A5                       |
|           |                       | \>10000, .08,     is (14000 .08 |
|           |  6   8453        0.05 |   .05)                          |
|           |   --- --              |                      \> 10000)  |
|           | --------- ----------- |                                 |
|           |                       |   =IF (A6                       |
|           |                       | \>10000, .08,     is (8453  .05 |
|           |                       |   .05)                          |
|           |                       |                      \> 10000)  |
|           |                       |   ------------                  |
|           |                       | ------------ --------- -------- |
+-----------+-----------------------+---------------------------------+

5\. Formatting The Appearance of a Workbook

You will learn how to format an Excel workbook in this part of the
tutorial using the **\"checks\"** workbook. But first, let us enter the
formulas in the "Balance" Column.

In cell F3 enter =F2-D3+E3. Copy the formula to the remaining cells. The
cell references will adjust automatically. Put the cursor on Cell F3, go
to Edit (in the tool bar), select Copy. Then Select Cells F4 through F6.
Go to Edit and Select Paste. Now let's format the **\"checks\"**
workbook. Select the first row, by clicking in the cell containing the
bold face 1.\
![](media/image25.jpeg){width="6.697916666666667in"
height="1.8229166666666667in"}

**[Cell Ranges.]{.underline}** You have just selected what Excel
describes as a ***range***. A range is a rectangular block of cells.
Many things are accomplished in Excel using ranges. For instance, the
format used to display values can be changed for an entire range. All
the values in a range can be referred to when writing a formula. A range
of cells can also be protected, which means the contents of the cells
cannot be altered. Ranges can also be named. Excel also allows you to
select ***discontinuous*** ranges. With the range of cells A1:F1
selected, click on the **Bold** button and **Center** alignment button.
If this formatting made the text too big for the cells, then adjust the
column widths. Your workbook should look similar to the following:

![](media/image26.jpeg){width="6.502777777777778in"
height="1.6513888888888888in"}\
**Selecting Discontinuous Ranges.** Select the first range of cells:
A3:A6. Hold down the Ctrl key and select the range: C2:C6.\
\
![](media/image27.jpeg){width="6.204861111111111in"
height="1.6652777777777779in"}\
\
Click on the **Center** alignment button.\
\
**[Formatting Dates and Numbers.]{.underline}** The basic formatting
rule \"select and then do\" is used when working with Excel. Select the
range of cells: B3:B6.

+---------------------------+---+--------------------------------------+
| Choose **Cells** from the |   | ![](m                                |
| **Format** menu. The      |   | edia/image28.jpeg){width="3.50625in" |
| following **Format        |   | height="3.2416666666666667in"}       |
| Cells** dialog box should |   |                                      |
| appear:                   |   |                                      |
|                           |   |                                      |
| Click on the **Number**   |   |                                      |
| tag if it is not already  |   |                                      |
| displayed. Within the     |   |                                      |
| Category box highlight    |   |                                      |
| Date to view all the      |   |                                      |
| Format Codes.             |   |                                      |
|                           |   |                                      |
| Scroll through the        |   |                                      |
| options in the Format     |   |                                      |
| Codes. There is no format |   |                                      |
| that displays as: Aug. 8, |   |                                      |
| 96. You can custom format |   |                                      |
| by typing in the Code     |   |                                      |
| box.                      |   |                                      |
+---------------------------+---+--------------------------------------+
| Select Custom, then       | ! |                                      |
| within the Code box, type | [ |                                      |
| in the custom format as   | ] |                                      |
| shown at right.           | ( |                                      |
|                           | m |                                      |
|                           | e |                                      |
|                           | d |                                      |
|                           | i |                                      |
|                           | a |                                      |
|                           | / |                                      |
|                           | i |                                      |
|                           | m |                                      |
|                           | a |                                      |
|                           | g |                                      |
|                           | e |                                      |
|                           | 2 |                                      |
|                           | 9 |                                      |
|                           | . |                                      |
|                           | j |                                      |
|                           | p |                                      |
|                           | e |                                      |
|                           | g |                                      |
|                           | ) |                                      |
|                           | { |                                      |
|                           | w |                                      |
|                           | i |                                      |
|                           | d |                                      |
|                           | t |                                      |
|                           | h |                                      |
|                           | = |                                      |
|                           | " |                                      |
|                           | 3 |                                      |
|                           | . |                                      |
|                           | 8 |                                      |
|                           | 6 |                                      |
|                           | 4 |                                      |
|                           | 5 |                                      |
|                           | 8 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 5 |                                      |
|                           | i |                                      |
|                           | n |                                      |
|                           | " |                                      |
|                           | h |                                      |
|                           | e |                                      |
|                           | i |                                      |
|                           | g |                                      |
|                           | h |                                      |
|                           | t |                                      |
|                           | = |                                      |
|                           | " |                                      |
|                           | 1 |                                      |
|                           | . |                                      |
|                           | 8 |                                      |
|                           | 9 |                                      |
|                           | 5 |                                      |
|                           | 8 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | 3 |                                      |
|                           | i |                                      |
|                           | n |                                      |
|                           | " |                                      |
|                           | } |                                      |
+---------------------------+---+--------------------------------------+

Click on the **Center** alignment button to align the dates.\
\
Now let\'s format the dollar amounts. Select the discontinuous range
displayed below:\
![](media/image30.jpeg){width="6.510416666666667in"
height="1.4791666666666667in"}\
Remember to select the first region, then hold down the control key for
the remaining regions.

+------------------------+---------------------------------------------+
| Choose **Cells** from  | ![](media/image31.jpeg){width="4.15625in"   |
| the **Format** menu.\  | height="3.8229166666666665in"}              |
| \                      |                                             |
| Click on the           |                                             |
| **Number** tab if it   |                                             |
| is not already         |                                             |
| displayed.             |                                             |
|                        |                                             |
| Within the Category    |                                             |
| box highlight          |                                             |
| Currency.              |                                             |
|                        |                                             |
| Select the Format Code |                                             |
| shown at right and     |                                             |
| then click **OK**:     |                                             |
|                        |                                             |
| Click on the           |                                             |
| **Center** alignment   |                                             |
| button to align the    |                                             |
| dollar amounts.        |                                             |
+------------------------+---------------------------------------------+

Your **\"checks\"** workbook should look as follows:\
![](media/image32.jpeg){width="6.34375in"
height="1.4895833333333333in"}\
\
**[Inserting Rows.]{.underline}** Let\'s insert a row between row 2 and
row 3 in the **\"checks\"** workbook, to make the workbook more
appealing to the eye. Select row 3 by clicking on the bold face 3.
Choose **Rows** from the **Insert** menu.\
\
You have now learned how to format an Excel document. Note that within
the **Format Cells** dialog box you can format the borders of the cells,
change the color, pattern, and shading of the cells and protection of
cells can be set there too.\
\
To preview the workbook, choose **Print Preview** from the **File**
menu. The look of the printed page appears on the screen. Click on the
**Close** button to return you to the workbook.\
\
Dotted lines appear that define the printed area. If this is too large
to fit on a page you need to do a page setup. What you want to do is
actually flip the table so it will fit on the whole page. You can do
this by choosing **Page SetUp** from the **File** menu.\
\
The following **Page Setup** dialog should appear:\
![](media/image33.jpeg){width="4.875in" height="3.7395833333333335in"}\
\
Click on the **Page** tab if it isn\'t already displayed. Within the
**Orientation** box click on Landscape and then click on the **OK**
button. Observe the dotted line (indicating a page break) at the bottom
of your workbook and running horizontal.\
Preview your **\"checks\"** workbook and then print a copy of it. Then
close it.

**[6. Advanced Excel (Copying Cells)]{.underline}**

In this part of the tutorial you will be creating a workbook and
learning how to fill a cell or range of cells with formulas to speed
data entry.\
\
Choose **New** from the **File** menu to start a new workbook if there
isn\'t a new workbook open.\
Choose **Formula Bar** from the **View** menu to display the formula
bar.

+-----------------------------------+-----------------------------------+
| Create an income statement        | ![](media/image34.j               |
| worksheet by making the entries   | peg){width="3.0833333333333335in" |
| shown at right.                   | height="3.6145833333333335in"}    |
|                                   |                                   |
| To right justify the text in      |                                   |
| Column A, you need to select the  |                                   |
| cells and click on the right      |                                   |
| justify button on the tool bar.   |                                   |
| Remember to hold down the Ctrl    |                                   |
| key when selecting a              |                                   |
| discontinuous range of cells.     |                                   |
+-----------------------------------+-----------------------------------+

Before you enter any formulas, let\'s ***name*** some of the cells in
your worksheet.

**[Naming Cells.]{.underline}** Excel allows you to name any cell,
range, or value in a workbook. You can then use this name to refer to
the cell, range, or value. Names make formulas easier to read,
understand, and maintain. You can change or delete names that have been
defined previously. Names appear in the reference area of the formula
bar when you select a named cell or range.\
\
Select cell B6 and choose **Name** from the **Insert** menu, and choose
**Define** from the **Name** submenu. Within the **Names** dialog box,
Excel provides a possible name: Sales. This is fine. Click the **OK**
button. Select cell B7 and name it: Service.\
\
Select cell B8 and enter the formula that sums Sales and Services. Save
your workbook as \"**company**\".\
\
The formula in cell B8 should be either the formula =B6+B7 or
=SUM(B6:B7). The formula is summing up the two cells directly above cell
B8. The formula in cell B13 will also sum up the two cells directly
above it. This is where Excel\'s **Copy** command is useful. What you
can do is copy the formula from one cell to another cell.

**[Copying From One Cell to Another Cell.]{.underline}**

  ------------------------------------------- ------------------------------------------------------
  Select cell B8. Choose **Copy** from the    Select cell B13. The formula should be =B11+B12 or
  **Edit** menu. Excel places a marquee       =SUM(B11:B12). That is the formula has summed the two
  around the cell. Select cell B13 and choose cells directly above cell B13. Your **company**
  **Paste** from the **Edit** menu. Your      workbook should look as below.
  **company** workbook should look as below.  
  Press the Escape key on your keyboard to    
  cancel the marquee.                         

  ![](media/image35.jpeg){width="2.71875in"   ![](media/image36.jpeg){width="2.6430555555555557in"
  height="3.198611111111111in"}               height="3.2083333333333335in"}
  ------------------------------------------- ------------------------------------------------------

You now know how to copy a formula from one cell to another. In order to
duplicate the Qtr. heading across columns C through E, you will make a
copy from one cell to several other cells.

# [Copying From One Cell to Several Cells.]{.underline} 

+--------------------+-------------------------------------------------+
| # Se               | # ![](media/i                                   |
| lect cell B4 and c | mage37.jpeg){width="4.475in" height="3.6875in"} |
| hoose Copy from th |                                                 |
| e Edit menu. Highl |                                                 |
| ight the range C4: |                                                 |
| E4. Choose Paste f |                                                 |
| rom the Edit menu. |                                                 |
|  Press the Escape  |                                                 |
| key to remove the  |                                                 |
| marquee. Center th |                                                 |
| e Qtr. headings. T |                                                 |
| he next step is to |                                                 |
|  fill in the range |                                                 |
|  C6:E7 with projec |                                                 |
| ted revenues. Sele |                                                 |
| ct cell C6 and ent |                                                 |
| er the formula =B6 |                                                 |
| \*1.08. Choose Cop |                                                 |
| y from the Edit me |                                                 |
| nu. Highlight the  |                                                 |
| range C6:E7. Choos |                                                 |
| e Paste from the E |                                                 |
| dit menu. Your com |                                                 |
| pany workbook shou |                                                 |
| ld look as at righ |                                                 |
| t. Save your work. |                                                 |
+--------------------+-------------------------------------------------+

# Highlight each cell in the range C6:E7 and observe the cell\'s formula. In each cell the formula multiplies the cell directly to the left of it by 1.08. You can also copy data and formulas from one location on the workbook to another using the Fill command. 

[The Fill Command.]{.underline} Filling Cells Automatically

You can use Microsoft Excel to automatically fill cells with information
that occur in a series. Type the following into the worksheet as shown.

  --- ------- ------- ------------ ------ ------------- -------- ------------
      A       B       C            D      E             F        G

  1   1:00    Sun     Sunday       Jan    January       2000     Type 1
  --- ------- ------- ------------ ------ ------------- -------- ------------

> Grab the Fill Handle and drag with your mouse to highlight cells A1 to
> G24. 

![](media/image38.png){width="4.458333333333333in"
height="4.354166666666667in"}

Now going back to our example.

  ------------------- -----------------------------------------------------
  Select the range    ![](media/image39.jpeg){width="4.716666666666667in"
  B8:E8. Choose       height="3.2284722222222224in"}
  **Fill** from the   
  **Edit** menu, and  
  from the **Fill**   
  submenu choose      
  **Right**. The      
  projected expenses  
  in the range        
  C11:E12 are         
  computed as 1.02    
  times the value of  
  the previous        
  quarter. Select     
  cell C11 and enter  
  the formula         
  =B11\*1.02. Select  
  the range C11:C13   
  and choose **Fill** 
  from the **Edit**   
  menu, and from the  
  **Fill** submenu    
  choose **Down**.    

  ------------------- -----------------------------------------------------

Excel provides another means of copying text, values, or formulas from
one cell to other cells. Excel provides the ***AutoFill*** command.
**AutoFill** copies cell\'s contents with out using the menu. You work
directly on the workbook using the mouse.\
\
**[Copying Cells Using AutoFill.]{.underline}** The **AutoFill** feature
is used to copy formatting, formulas, or all cell contents, or to
perform different types of ***series extensions***. For example, if you
type Monday and Tuesday in consecutive columns, and then drag the fill
handle to the right, Excel fills Wednesday, Thursday and so on into
selected cells.\
\
Select cell C11. Cell C11 contains the formula you want to copy. Observe
the fill handle located in the lower-right corner. Place your mouse over
the fill handle, until the arrow becomes a black cross. Drag the fill
handle across the cells D11 and E11 and then release the mouse button.
You can also select more than one cell and then use the **AutoFill**
command to copy. Select the range C12:C13. Drag the fill handle across
the range D12:E13.\
\
You may use any method above to finish the **company** workbook. Select
cell B16 and enter the formula =B8-B13. (Gross Income is equal to Total
Revenue minus Total Expenses). Select cell B17 and enter the formula
=B16\*.22. (The Tax is 22%). Select cell B18 and enter the formula
=B16-B17. (Net Income is Gross Income minus the Tax).\
\
Copy the formulas in the range B16:B18 to the range C16:E18 using any
method you would like.\
\
You have almost completed your **company** workbook. There is one more
column to enter. Select cell F4 and enter and center the text: Year.
Select cell F6 and enter the formula =SUM(B6:E6). Copy the formula in
cell F6 into the following ranges: F7:F8, F11:F13, and F16:F18.\
\
Your completed **company** workbook should look as follows:\
![](media/image40.jpeg){width="5.958333333333333in"
height="3.4895833333333335in"}\
\
Always remember to save your changes. Your **company** workbook is
complete. You can now use the power of Excel\'s automatic recalculation
feature. You can now use ***What If?*** analysis.\
**[What If? Analysis.]{.underline}** What If? analysis involves three
steps: (1) First, you ask a What If? question about your workbook. For
example, \"What if the total revenue in the first quarter was \$5000?\"
(2) Second, you alter the appropriate cell or cells in your workbook. In
this case it would be cell B8. (3) Third, you observe how the different
values in the workbook change.

Now that you are done with your **company** workbook, you can learn one
more of Excel\'s advanced features: ***Linking***.

#### 7. Linking Documents in Excel

Excel can dynamically link a workbook to source data in another workbook
so that any changes you make in one workbook are immediately reflected
in the other workbook.

**[Creating Links between workbooks.]{.underline}** You will need two
workbooks to create a link. The **company** workbook will serve as your
first and as the Source Workbook. The second workbook will be created
and serve as the Dependent Workbook.

  ------------------------------ ------------------------------------------------------
  Let\'s start by creating the   ![](media/image41.jpeg){width="3.6756944444444444in"
  Dependent Workbook. Choose     height="2.425in"}
  **New** from the **File** menu 
  to start a new workbook.       
  Create the following workbook  
  and call it **budget** by      
  entering data in cells C5, C9, 
  and C10, as shown. Enter sum   
  formulas =C4+C5 in C6, =C9+C10 
  in C11, and =C6-C11 in C13.    

  ------------------------------ ------------------------------------------------------

It is now time to create a link between the workbooks; **company** and
**budget**. Have both workbooks open. In the Source Workbook:
**company,** Select cell F18. This is the cell you want to refer. You
want to insert this value into your **budget** workbook. Click the
**Copy** button or choose **Copy** from the **Edit** menu. A moving
border should appear around cell F18.

Switch to the **budget** workbook. This is the workbook you want to
paste the linked data. Select cell C4. This is the cell in which you
want the linked data to appear.

+---------------------------+------+-----------------------------------+
| Choose **Paste Special**  |      | ![](medi                          |
| from the **Edit** menu.   |      | a/image42.jpeg){width="3.15625in" |
| The **Paste Special**     |      | height="2.875in"}                 |
| dialog box shown at the   |      |                                   |
| right should appear.\     |      |                                   |
| Choose All in the         |      |                                   |
| **Paste** box and None in |      |                                   |
| the **Operation** box.    |      |                                   |
|                           |      |                                   |
| Choose the **Paste Link** |      |                                   |
| button to paste the link  |      |                                   |
| into cell C4.             |      |                                   |
+---------------------------+------+-----------------------------------+
| Excel created an external | ![]( |                                   |
| reference formula that    | medi |                                   |
| links the workbooks. This | a/im |                                   |
| formula appears in the    | age4 |                                   |
| formula bar.              | 3.jp |                                   |
|                           | eg){ |                                   |
| You have successfully     | widt |                                   |
| linked two documents. You | h="3 |                                   |
| can now play around with  | .854 |                                   |
| What If? analysis and see | 1666 |                                   |
| how changes in the the    | 6666 |                                   |
| Book Company\'s income    | 6666 |                                   |
| will effect your personal | 5in" |                                   |
| budget.                   | hei  |                                   |
|                           | ght= |                                   |
|                           | "2.5 |                                   |
|                           | 3125 |                                   |
|                           | in"} |                                   |
+---------------------------+------+-----------------------------------+

One of Excel\'s major features is its ability to produce charts that
illustrate the numbers on the worksheets you produce. In the last part
of the tutorial you will learn the creation, formatting, and printing of
charts.

#### 8. Creating Charts

Before you can draw a chart using Excel, the numbers that compose the
chart must be entered in a workbook. There are five general steps in
defining a chart.\
\
**Steps in Creating a Chart:**

1.  Enter the numbers into a workbook.

2.  Select the data to be charted.

3.  Choose **Chart** from the **Insert** menu.

4.  Choose either **Chart Type** from the **Format** menu or click on
    the **ChartWizard** button.

5.  Define parameters such as titles, scaling color, patterns, and
    legend.

These five steps should be performed in this order. Note that since the
chart is linked to the workbook data, any subsequent changes made to the
workbook are automatically reflected in the chart. You will be making
two charts in this part of the tutorial. The first chart will be a pie
chart and the second chart will be a column chart.

**[Creating a Pie Chart.]{.underline}** Pie charts are used to show
relative proportions of the whole, for one ***data series*** only. Data
series are a group of related ***data points***. A data point is a piece
of information that consists of a category and value. When you create a
chart with Excel, the categories are plotted along the horizontal or
X-axis, while the values are plotted along the vertical or Y-axis. Data
series originate from single worksheet rows or columns. Each data series
in a chart is distinguished by a unique color or pattern. You can plot
one or more data series in a chart except for pie charts. An example of
a data series is the population of the United States over ten years.
Each data point would be made up of a year (the category) and the
population in that year (value).

+-----------------------------------+-----------------------------------+
| Open an Excel workbook and enter  | ![](media/image44.                |
| the data into the worksheet as    | png){width="1.9256944444444444in" |
| shown at right.                   | height="1.8444444444444446in"}    |
|                                   |                                   |
| You will be using the ChartWizard |                                   |
| to create your pie chart.         |                                   |
+-----------------------------------+-----------------------------------+

**[Using The ChartWizard.]{.underline}** The ChartWizard is a series of
dialog boxes that guides you through the steps required to create a new
chart or modify settings for an existing chart. When creating a chart
with the ChartWizard, you can specify the worksheet range, select a
chart type and format, and specify how you want your data to be plotted.
You can also add a legend, a chart title, and a title to each axis.\
\
There are two commands and two buttons that start the ChartWizard. The
command you choose or the button you click will create either an
***embedded chart*** or a ***chart sheet***. An embedded chart is a
chart object that has been placed on a worksheet and that is saved on
that worksheet when the workbook is saved. When it is selected you can
move and size it. When it is ***activated***, you can select items and
add data, and format, move, and size items in the chart. A chart sheet
is a sheet in a workbook containing a chart. When a chart sheet is
created, it is automatically inserted into the workbook to the left of
the worksheet it is based on. When a chart sheet is activated, you can
select items and add data, and format, move and size items in the
chart.\
\
In this tutorial you will be creating chart sheets only. Select the data
you just entered.

+-----------------------------------+----------------------------------+
| Choose **Chart** from the         | ![](media/image46.p              |
| **Insert** menu. The              | ng){width="3.1131944444444444in" |
| **ChartWizard\'s** first dialog   | height="3.6243055555555554in"}   |
| box appears:                      |                                  |
|                                   |                                  |
| ![](media/image45.j               |                                  |
| peg){width="2.7506944444444446in" |                                  |
| height="2.6979166666666665in"}    |                                  |
|                                   |                                  |
| You want a regular pie chart not  |                                  |
| a 3-D pie chart. Select the chart |                                  |
| type: **Pie** and click on the    |                                  |
| **Next** button. See at right.    |                                  |
| Read the dialog box, make sure    |                                  |
| the range is correct and then     |                                  |
| click the **Next** button.        |                                  |
+-----------------------------------+----------------------------------+

  ------------------------- -----------------------------------------------------
  Step 3 is shown at        ![](media/image47.png){width="4.001388888888889in"
  right.\                   height="2.763888888888889in"}
  Select the **Titles** tab 
  and then enter \"Energy   
  Consumption\" as the      
  chart title.              

  Select the **Legend** tab ![](media/image48.png){width="4.116666666666666in"
  and make the following    height="2.8604166666666666in"}
  adjustments:              

  Select the Data Labels    ![](media/image49.png){width="4.1090277777777775in"
  tab and select the        height="2.8444444444444446in"}
  options shown at right.   
  ------------------------- -----------------------------------------------------

Select the following options and then click the **Finish** button.

![](media/image50.png){width="4.252083333333333in"
height="1.9048611111111111in"}

Your **Energy Consumption** chart should look as follows:

![](media/image51.png){width="3.4993055555555554in"
height="2.5729166666666665in"}

Once you complete the ChartWizard, Excel displays the new chart sheet,
the Chart toolbar (![](media/image52.jpeg){width="3.84375in"
height="0.5104166666666666in"}), and the chart menu bar. Note that if
the chart toolbar is not displayed, simply choose **Toolbars** from the
**View** menu and check of the chart box. The chart menu bar is similar
to the worksheet menu bar, except the **Insert** and **Format** menus
have some different commands. Now to format it.

#### 9. Formatting Charts

Before we can discuss the details of how to edit and format a chart, you
need to know how to activate the chart and select items in the chart
using a mouse.

**[Activating a Chart Sheet.]{.underline}** When you activate a chart,
the chart menu commands become available and the Chart toolbar is
displayed. To activate a chart sheet, select the chart sheet tab you
want. Select the chart sheet tab to activate the pie chart. Once a chart
is active, you can use the mouse to select chart items one at a time. To
confirm what you have selected, refer to the name box on the formula
bar. Note that many items in a chart are grouped together. For some
grouped items, such as data series, you click once to select the entire
group, and then click the individual item you want to select within the
group. The following list is an brief overview on how to select items in
a chart using a mouse.\
\
**To select one of the following items in an Excel chart using a
mouse:**

*Data Series*- click any data marker belonging to a data series.

*Pie slice*- select the pie ring, and then click the slice.

*Data labels*- click any data label associated with a data series.

*Single data label*- select the data labels, and then click an
individual label.

*Legend*- click anywhere in the legend, or click its border.

*Single legend entry*- select the legend, and then click the legend
entry.

*Title*- click the chart title, axis title, or text box.

*Axis*- click the axis or a tick-mark label to format or modify the
axis.

+-----------------------------------+----------------------------------+
| Let\'s change the colors of the   | ![](media/image53.p              |
| pie slices. Select the Pie ring.  | ng){width="3.4097222222222223in" |
| Then select the 61% pie slice as  | height="2.4402777777777778in"}   |
| shown at right.                   |                                  |
|                                   |                                  |
| Choose **Selected Data Point**    |                                  |
| from the **Format** menu.\        |                                  |
| The **Format Data Point** dialog  |                                  |
| box should appear as shown below: |                                  |
|                                   |                                  |
| Select the **Patterns** tab and   |                                  |
| choose a different color and      |                                  |
| pattern for the slice (push the   |                                  |
| "Fill Effects" button).           |                                  |
|                                   |                                  |
| Similarly, change the 11% slice,  |                                  |
| (See below, right). Patterns are  |                                  |
| useful in black and white         |                                  |
| printouts.                        |                                  |
+-----------------------------------+----------------------------------+
| ![](media/image54.                | ![](media/image55.p              |
| png){width="3.6180555555555554in" | ng){width="3.5479166666666666in" |
| height="3.3125in"}                | height="2.626388888888889in"}    |
+-----------------------------------+----------------------------------+

Select the chart. Observe the ChartWizard toolbar that is displayed on
your screen. There is a **Legend** button located on the toolbar. If you
want to add or delete a legend to a chart just press that button. Now
that you have completed the pie chart let\'s print it out. Before you
print it out let\'s preview it. Save all your changes.\
\
To print choose **Print Preview** from the **File** menu. Make sure that
the text: Page 1 and Chart 1 is not displayed, if this text appears the
layout of the page must be set. Click on the **Close** button.

  ---------------------------------------------------- ------------------------------------------------------
  Choose **Page SetUp** from the **File** menu. Within Within the **Page Setup** dialog box, select the
  the **Page Setup** dialog box select the             **Chart** tab and select the following setting:
  **Header/Footer** tab. Within the **Header/Footer**  
  box select none from the Header and Footer pull-down 
  menus. This will clear the text: Chart1 at the top   
  of your pie chart and it will also clear the text:   
  Page 1 at the bottom of your pie chart.              

  ![](media/image56.png){width="3.685416666666667in"   ![](media/image57.jpeg){width="3.7291666666666665in"
  height="2.847916666666667in"}                        height="2.86875in"}
  ---------------------------------------------------- ------------------------------------------------------

Click on the **OK** button. Click on the **Print** button. You are now
ready to create a column chart.

**[10. Creating a Column Chart]{.underline}**

+---------------------------+------------------------------------------+
| Column charts use bars of | ![](media/i                              |
| varying lengths to        | mage58.png){width="3.8826388888888888in" |
| indicate amount. The bars | height="4.840277777777778in"}            |
| are of different colors   |                                          |
| or patterns to indicate   |                                          |
| the different type of     |                                          |
| data, and they run        |                                          |
| vertically across the     |                                          |
| chart.\                   |                                          |
| \                         |                                          |
| Open your **expenses**    |                                          |
| workbook. Click on the    |                                          |
| Sheet 2 tab at the bottom |                                          |
| of the **expenses**       |                                          |
| workbook to enter the     |                                          |
| data for your column      |                                          |
| chart.                    |                                          |
|                           |                                          |
| Create a worksheet as at  |                                          |
| right. Remember to use    |                                          |
| Excel\'s Copy features    |                                          |
| that you learned in the   |                                          |
| previous part of the      |                                          |
| tutorial.\                |                                          |
| \                         |                                          |
| Select the data to be     |                                          |
| charted. Choose **Chart** |                                          |
| from the **Insert** menu. |                                          |
+---------------------------+------------------------------------------+

+------------------+---------------------------------------------------+
| The Chart Wizard | !                                                 |
| will appear as   | [](media/image60.png){width="4.675694444444445in" |
| shown at the     | height="4.081944444444445in"}                     |
| right; ready for |                                                   |
| the first step   |                                                   |
| in creating a    |                                                   |
| chart.           |                                                   |
|                  |                                                   |
| Choose the chart |                                                   |
| type: **Column** |                                                   |
| and click on the |                                                   |
| **Next**         |                                                   |
| button.\         |                                                   |
| \                |                                                   |
| Choose following |                                                   |
| format type and  |                                                   |
| click on the     |                                                   |
| **Next**         |                                                   |
| button.\         |                                                   |
| ![](media        |                                                   |
| /image59.jpeg){w |                                                   |
| idth="0.71875in" |                                                   |
| height="0.6666   |                                                   |
| 666666666666in"} |                                                   |
+------------------+---------------------------------------------------+

We are now at Step 2 of the Chart Wizard: Data Range and Series.

  ---------------------------------------------------- ---------------------------------------
  ![](media/image61.png){width="3.498611111111111in"   ![](media/image62.png){width="3.55in"
  height="3.140277777777778in"}                        height="3.3069444444444445in"}

  ---------------------------------------------------- ---------------------------------------

If the range is correct, click on the **Next** button.

  --------------- -------------------------------------------------------
  Insert the      ![](media/image63.png){width="5.083333333333333in"
  following on    height="3.6625in"}
  the **titles**  
  tab and click   
  the **Next**    
  button. See the 
  chart options   
  at right.       

  --------------- -------------------------------------------------------

  -------- -------------------------- ---------------------------------------------------- ---------------------------------------------------- ---
           Select the options shown                                                        ![](media/image64.png){width="2.709722222222222in"   
           at right and click the                                                          height="1.7416666666666667in"}                       
           **Finish** button.                                                                                                                   

  Your                                ![](media/image65.png){width="3.848611111111111in"                                                        
  column                              height="2.7159722222222222in"}                                                                            
  chart                                                                                                                                         
  should                                                                                                                                        
  look as                                                                                                                                       
  shown at                                                                                                                                      
  right:                                                                                                                                        
  -------- -------------------------- ---------------------------------------------------- ---------------------------------------------------- ---

+--------------------+-------------------------------------------------+
| After some editing | ![]                                             |
| and adding         | (media/image67.png){width="4.541666666666667in" |
| annotations, your  | height="3.0729166666666665in"}                  |
| column chart       |                                                 |
| should look        |                                                 |
| similar to the one |                                                 |
| shown at the       |                                                 |
| right:             |                                                 |
|                    |                                                 |
| Use the text box   |                                                 |
| tool (the A        |                                                 |
| underlined several |                                                 |
| times) and the     |                                                 |
| arrow tool to      |                                                 |
| accomplish this.   |                                                 |
|                    |                                                 |
| ![](               |                                                 |
| media/image66.png) |                                                 |
| {width="1.46875in" |                                                 |
| height="0.33       |                                                 |
| 33333333333333in"} |                                                 |
+--------------------+-------------------------------------------------+

+----------------------+---+-------------------------------------------+
| Select a grid line   |   | ![](media/i                               |
| and choose           |   | mage68.jpeg){width="3.9652777777777777in" |
| **Selected           |   | height="3.45in"}                          |
| Gridlines** from the |   |                                           |
| **Format** menu.\    |   |                                           |
| The following        |   |                                           |
| **Format Gridlines** |   |                                           |
| dialog should        |   |                                           |
| appear:              |   |                                           |
|                      |   |                                           |
| Choose a different   |   |                                           |
| style for the line   |   |                                           |
| and click the **OK** |   |                                           |
| button.              |   |                                           |
+----------------------+---+-------------------------------------------+
| Lastly, let\'s       | ! |                                           |
| change the alignment | [ |                                           |
| of the text that     | ] |                                           |
| makes up the years.  | ( |                                           |
| Select the X-axis.   | m |                                           |
| Choose **Selected    | e |                                           |
| Axis** from the      | d |                                           |
| **Format** menu.     | i |                                           |
| Within the **Format  | a |                                           |
| Axis** dialog box    | / |                                           |
| click on the         | i |                                           |
| **Alignment** tab.\  | m |                                           |
| Select the following | a |                                           |
| option and click the | g |                                           |
| **OK** button.       | e |                                           |
|                      | 6 |                                           |
| The option selected  | 9 |                                           |
| here is the one      | . |                                           |
| already invoked in   | p |                                           |
| the previously shown | n |                                           |
| finished disruption  | g |                                           |
| chart.               | ) |                                           |
|                      | { |                                           |
|                      | w |                                           |
|                      | i |                                           |
|                      | d |                                           |
|                      | t |                                           |
|                      | h |                                           |
|                      | = |                                           |
|                      | " |                                           |
|                      | 3 |                                           |
|                      | . |                                           |
|                      | 9 |                                           |
|                      | 6 |                                           |
|                      | 4 |                                           |
|                      | 5 |                                           |
|                      | 8 |                                           |
|                      | 3 |                                           |
|                      | 3 |                                           |
|                      | 3 |                                           |
|                      | 3 |                                           |
|                      | 3 |                                           |
|                      | 3 |                                           |
|                      | 3 |                                           |
|                      | 3 |                                           |
|                      | 3 |                                           |
|                      | 3 |                                           |
|                      | i |                                           |
|                      | n |                                           |
|                      | " |                                           |
|                      | h |                                           |
|                      | e |                                           |
|                      | i |                                           |
|                      | g |                                           |
|                      | h |                                           |
|                      | t |                                           |
|                      | = |                                           |
|                      | " |                                           |
|                      | 3 |                                           |
|                      | . |                                           |
|                      | 7 |                                           |
|                      | 3 |                                           |
|                      | 1 |                                           |
|                      | 2 |                                           |
|                      | 5 |                                           |
|                      | i |                                           |
|                      | n |                                           |
|                      | " |                                           |
|                      | } |                                           |
+----------------------+---+-------------------------------------------+

**[11. Creating a Line Chart]{.underline}**

Now you have gone through two chart development procedures. The third
one, constructing a line chart, will be presented much more briefly --
various snapshots of the process will be displayed and you are left to
fill in the details.

  ---------------------------------------------------- ----------------------------------------------------
  Prepare a spreadsheet with the data as shown below.  Under the Insert menu select Chart and then select
  Highlight the data from I3 to K11.                   the line chart shown below and click Next at the
                                                       bottom. By clicking on the various charts shown, an
                                                       explanation will appear in the section below the
                                                       charts.

  ![](media/image70.png){width="3.120138888888889in"   ![](media/image71.png){width="3.220833333333333in"
  height="3.2402777777777776in"}                       height="3.323611111111111in"}
  ---------------------------------------------------- ----------------------------------------------------

  ----------------------------------------------------- ------------------------------------------
  Step 2, Data Range, shows the graphs. The data series Click on the Series tab. Define the series
  is in columns, as indicated below, and the range is   names and set the X-axis range. Click
  shown in the Data range box.                          Next.

  ![](media/image72.png){width="2.9923611111111112in"   ![](media/image73.png){width="3.00625in"
  height="3.5430555555555556in"}                        height="3.54375in"}
  ----------------------------------------------------- ------------------------------------------

  ----------------------------------------------------- -----------------------------------------------------
  Step 3, Enter the Chart Title and the Axes labels.    Step 4, Select the sheet where the chart is to be
  The X-axis needs no label because the Years are       stored.
  obvious. Click Next.                                  

  ![](media/image74.png){width="3.4993055555555554in"   ![](media/image75.png){width="3.4993055555555554in"
  height="2.426388888888889in"}                         height="1.6208333333333333in"}
  ----------------------------------------------------- -----------------------------------------------------

The resulting chart is shown here. Now for some additional formatting.

![](media/image76.png){width="3.6243055555555554in"
height="2.057638888888889in"}

Now make the graph lines heavier, select any color changes and select a
pattern for each. Click on a graph line too select it. From the Format
menu select "Selected Data Series". Or, while pointing the mouse at the
line, right click and select, "Format Data Series". Select the options
desired.

![](media/image77.png){width="6.490972222222222in"
height="4.411111111111111in"}

The graph lines can be annotated by selecting the whole chart by
clicking on the inside near the boundary. Then click on the text box
tool at the bottom, click on the chart near a line to be annotated and
type in the text. Right the click the boundary of the text box and
select "Format Text Box" to change font size. The text box can be moved
and resized with the mouse.

![](media/image78.png){width="5.020833333333333in" height="2.8125in"}

Congratulations! You have finished the Excel tutorial.

## A Short Quiz on Excel

**Question 1.** The advantage of using a spreadsheet is:

\(a\) calculations can be done automatically, (b) changing data
automatically updates calculations (as long as Excel is not set to
calculate manually), (c) more flexibility, (d) all of the above.

**Question 2.** The intersection of a row and a column is called:

(a) data, (b) a field, (c) a cell, (d) an equation.

**Question 3.** When you are typing an equation into a cell the first
thing that must be entered is:

\(a\) the first cell referenced, (b) parenthesis, (c) quotation marks,
(d) an equal sign

**Question 4.** Labels are used in a spreadsheet to:

\(a\) tell the computer what we are doing, (b) help us identify the
information, (c) do the calculations, (d) set up the equations.

**Question 5.** The cell labeled F5 refers to:

\(a\) row F column 5, (b) column F row 5, (c) functions available in
cells, (d) Function key F5.

**Question 6.** There are three types of data found in a spreadsheet:

\(a\) data, words, numbers, (b) equations, data, numbers, (c) words,
numbers, labels, (d) numbers, formulas, labels.

**Question 7.** The formula **=B2+A3** is located in cell **B3, as
shown.**

  -------- --------------- --------------- --------------- ---------------
  ** **    **A**           **B**           **C**           **D**

  **1**    2               4               5               5

  **2**    3               3               4               6

  **3**    5               =B2+A3          3               8

  **4**    4               3               4               ????????
  -------- --------------- --------------- --------------- ---------------

If this was copied and pasted into cell **D4** the resulting formula
would be:

(a) =C2+C3, (b) =D3+C3, (c) =D3+C4, (d) none of the above.

**Question 8.** The formula **=\$c\$3\*D3** is located in cell **B1**.

  ------------- ----------- ------------- -------------- -------------------
  ** **         **A**       **B**         **C**          **D**

  **1**                     =\$C\$3\*D3   ?????????       

  **2**         3           3             4              6

  **3**         5           4             3              8

  **4**         4           3             4              9
  ------------- ----------- ------------- -------------- -------------------

If this was copied and pasted into cell **C1**, what would the resulting
formula be:

\(a\) =\$C\$3\*E3, (b) =C3\*E3, (c) =\$C\$3\*D3, (d) 24.

**Question 9.** A constant is another name for this type of data:

(a) number, (b) equation, (c) formula, (d) description.

**Answers to the Quiz**

> 1\. The advantage of using a spreadsheet is:
>
> **(d)** all of the above.
>
> 2\. The intersection of a row and a column is called:
>
> **(c)** a cell.
>
> 3\. When you are typing an equation into a cell the first thing that
> must be entered is:
>
> **(d)** an equal sign.
>
> 4\. Labels are used in a spreadsheet to:
>
> **(b)** help us identify the information.
>
> 5\. The cell labeled F5 refers to:
>
> **(b)** column F row 5.
>
> 6\. There are three types of data found in a spreadsheet:
>
> **(d)** numbers, formulas, labels.
>
> 7\. The formula =B2+A3 is located in cell B3. If this was copied and
> pasted into cell D4 the resulting formula would be:
>
> **(c)** =D3+C4.

8.  The formula =\$C\$3\*D3 is located in cell B1. If this was copied
    and pasted into cell C1, what would the resulting formula be:

> **(a)** =\$C\$3\*E3.

9.  A constant is another name for this type of data:

> **(a)** a number.