Excel Tutorial


The Excel Screen and Terms
AutoFill and AutoSum
Math Symbols and Rules for Calculations
Relational Formulas
Absolute Formulas
Building Your Spreadsheets
Using Cell Formatting


1. The Excel Screen and Terms

This page introduces some of the terminology used in talking about Excel.

  1. When you open a file, you are opening a workbook.
  2. At the bottom of the screen the tabs are for different worksheets in that workbook
  3. Cells in a worksheet can refer to cells in another worksheet. This is helpful if you want to keep detailed records in one sheet, and then summarize them in another sheet.
  4. To get to a different worksheet, just click on the tab for that worksheet.
  5. You select a group of cells the same as you do in other software … by dragging the mouse w/ the left button down.
  6. If you select a group of cells, you can enter into those cells a column at a time by just typing your entry, then hitting enter … you will advance through the selection of cells a column at a time. (and if you just select one row, then you'll go across the row).
  7. The different cursors (usage will be explained further on).
    1. The white "red cross" cross is the standard pointer.
    2. The left pointing white arrow points to a selected object.
    3. The little black cross appears when you move the cursor to the bottom right corner of a selected cell or range of cells. The dot on that corner is called the "fill handle".
Top

2. AutoFill and AutoSum

How to use Excel to do Autofill and Autosum.

Create the above spreadsheet, starting in cell A1
  • Type "Goshen College" and hit enter
  • Type "Donations …" , and enter
  • Move to cell B4 and enter remaining cell contents
Center the title and subtitle across columns
  • Select cells A1 .. D2
  • Click the button on the toolbar that is white w/ an "a" between 2 arrows
  • This centers what is in the first cell of each row across all the cells selected.
Use AutoFill and carry months through June
  • Select the January and February cells, then let go of the left button
  • Move the mouse cursor to the bottom right corner of the selected area until the cursor turns into a cross
  • Press the left mouse button and drag to the right through cell N4
  • If you've dragged properly, the cells will have been "AutoFilled" with March through December
Enter sales figures for new months
  • Just enter some data into the other cells
AutoSum the columns
  • Select cells C5..C10
  • Click on the AUTOSUM BUTTON on the toolbar (The one w/ the S on it)
  • The sum of the numbers in the column will be put in the bottom cell.
  • Now select cell C10 where the sum of the first column is
  • Let go of the mouse button, then put the mouse cursor to the bottom right corner again
  • Press the left mouse button and drag across through the last column of the worksheet
  • This will copy the formula from C10, adjusting the formulas as you would want it to, into the other cells so that each cell is the sum of the column.
Color the background of the Regions.
  • Select the cells
  • Click the drop-down arrow beside the button w/ a paint can and a "square" of paint in front of it
  • Choose your color
Color the text of the months.
  • Select the cells from January..December
  • Click the drop-down arrow beside the button w/ a T w/ a square in front of it
  • Choose your text color
Adjust column width
  • Place the mouse cursor in the button at the top of a column (the gray button that has the column letter on it).
  • Move the cursor to the right until it looks like a cross w/ arrow points on the ends of the arms (the white "red-cross" cross won't work).
  • While the cursor is as above, double click. This will expand or shrink the width of the column.
Create a chart from the figures for Jan-March using Wizard.
  • Select the cells containing the data
  • Click the button w/ a magic wand in front of some vertical bars
  • Create the chart
Top

3. Math Symbols and Rules for Calculations

The following are some of the mathematic symbols and rules that are common to Excel.

  • Excel uses computer syntax for operands:
    • Division is /
    • Multiplication is *
    • Addition is +
    • Subtraction is -
  • Excel uses the "Associative Law of Math" which means:
    • Multiplication and Division before Addition and Subtraction
    • (mnemonic: MiDAS is the Golden Rule of Math)
    • The order can be changed by using parenthesis
    • 2 + 4 / 2 = 4
      but, ( 2 + 4 ) / 2 = 3
  • To create basic formulas:
    • = begins a formula
  • Some examples:

     

If in cell A4, or any other cell, you enter:
 The results in that cell will be:
=b3+b2
 21
=c1-c3
 3
=a2*b2
 30
=c3/a1
 1.5

Top


4. Relational Formulas

The following shows how to use relational formulas in Excel.

    (incorrect Min Pmnt shown!)

  • Create the above spreadsheet.
    • Click on the tab for Sheet2 at the bottom of the screen
    • Enter the data in the first 4 columns above
    • When typing in Column Headers (also called Labels or Field Names), Bold them and place one word in one cell, the second word in the cell below it.
    • Adjust Column width by resting your mouse cursor between the A and B on the column bar and drag to size.
  • Write formulas to fill in the other columns (HINT: Enter the formula into the first cell in the column, then drag down from the bottom right corner to copy the formula into the other rows.)
    • The Current Balance is the Previous Balance plus New Purchases less returns, ex: =b4+c4-d4
    • The Carrying Charge is the Current Balance times 1.5% (.015)
    • The Total Owed is the Current Balance plus the Carrying Charge
    • The Minimum Payment is 10 percent (.1) of the Current Balance plus the Carrying Charge
  • Total the Columns using AutoSum (S button).
    • Again, no need to do this for every column, just do it once and then drag to copy it.
    • OR: You can do all of the columns at once by selecting all columns and a blank cell at the bottom of each column … then click the AutoSum button and it will sum each column at the bottom
  • Apply Borders to the chart.
  • Select all of the numeric fields and click on the "$" button to instantly format them as money amounts.
Top

5. Absolute Formulas

The following shows how to use absolute formulas in Excel.

 

  • Create the above table on Sheet3, starting in cell a1
  • Insert a column for Gross Wages between Hours and FICA
    • Click on the column heading for FICA, column D
    • This will select the whole column
    • Right click on any of the selected column
    • Click on Insert. This will insert a column in front of the selected column
    • In the top cell of the column, enter GrossWages.
  • Insert Formulas to complete spreadsheet (IMPORTANT: Use the B8,B9, and B10 cell references instead of typing the numeric value).
    • First do the formulas for Larry.
      • Now, if you just copy those formulas by dragging the bottom right corner, the results will not be as expected. Try it and see.
      • What has happened is that the reference to the FICA rate for Curly will point to the row below FICA, and the reference for Moe will point to 2 rows down. We need a way to make the reference copy correctly, exactly as it is for Larry.
      • To make those references absolute, you use a $. The $ makes each piece of the cell reference be absolute … that is, not change when the formula is copied.
      • For Larry, you could point to FICA as $b8, b$8, or $b$8 … or b8 of course.
      • The way we want to do it here is $b$8, but you should try the other options to see what happens.
    • Once you have Larry correctly entered w/ $c$r (c=column, r=row), then you can drag to copy the formulas.
  • Format columns to currency where applicable
    • Select the column or a portion of it
    • Right click and choose "Format Cells", on the number tab choose "currency", and then choose how you want it to appear.
  • Let's see what happens when we change an entry used by the formulas.
    • Change Joe’s hourly wage and total hours.
    • Notice that all of the other field values for Joe change once you hit
    • Change the Federal Tax Rate, notice that all of the calculations above change.
Top

6. Building Your Spreadsheets

The following shows how to build and use Excel as a spreasheet.

Mary Miller-Yoder is building a sabbatical proposal for a research project. Her goal is to layout a $42000 expense list for the six month she would be away from Goshen. Mary would receive payments (revenue) for six months: January, $5000; February, $6000; March $7000; April $8000; May $7000; and June $9000. She estimates that her monthly expenses during this time will be as follows:

    Research assistant salaries 2900
    Rent 735
    Telephone 475
    Equipment and Supplies 275
    Misc. 110

She also has two periodic expenses. In January, Mary will need to prepay her insurance premium for six months, which costs a total of $736. In June, she has a computer payment of $4200 due.

Create a worksheet using the following steps:

  1. In cell A1, enter the title of: Six-Month Budget for Research.
  2. In cells B3 and C3, enter the labels: JAN, FEB , then drag out 4 more columns to fill it out to June
  3. In the next cell of row 3, enter TOTAL
  4. Center the title across the columns
    1. Select A1 and across through the last column in use
    2. Click that button for "Center Across Columns". If you are unsure which button it is, then move slowly across the buttons and the button names will appear.
  5. Enter the labels down Column A as follows:

    Row 5 REVENUES
    Row 7 EXPENSES
    Row 8 Salaries
    Row 9 Rent
    Row 10 Telephone
    Row 11 Insurance
    Row 12 Computer
    Row 13 Supplies
    Row 14 Misc
    Row 16 TOTAL EXP.:
    Row 18 NET 

  6. Enter the values for the monthly revenues.
  7. Enter the periodic expenses.
  8. Enter the fixed monthly expenses for January only
  9. AutoFill the fixed expenses across the months
    Enter a formula for calculating Total Revenues, Total Expenses and Net.
Top

7. Using Cell Formatting

This page helps using Excel to do cell formatting.

   

  1. Create the above spreadsheet
    1. Hint: If you make a mistake and enter data in the wrong cell(s), you can select the cells that are out of order and drag them to where you want them. If you move slowly, you will see the outline of where they are to go.
  2. Use Format Cells to set the Student ID format to seven places (0000000)
    1. Select the cells that you want to format
    2. Right click in the selected area
    3. Choose Format Cells, the "number" tab
    4. Click on custom at the bottom of the list and type in "0000000".
  3. Use Format Cells "Alignment" to set the Vertical Alignment and Orientation of the Headers
  4. Use Autosum to fill in the Net Points .. and Total Possible Points
  5. Use Absolute Addressing to calculate the Final Percentage (Final Points/Total Possible Points)
  6. Use the % feature to format the Final score to percent instead of decimal format
    1. Use Format Cells "Number" and then choose decimal format.
    2. Use Format/AutoFormat to create an interesting looking chart.
Top

Login Button