Lesson 01 -- Introduction to Microsoft EXCEL

A Virtual- Interactive-Visual Applications (VIVA) Lesson created by Prof. Joseph Auciello, (c) Copyright, May 2003, Joseph Auciello, and California Technical Training.

This introduces a new learning tool -- an "online" achievement / skill-building test where you learn by 'figuring-it-out" the answers. You must open Excel 2000 in a 2nd window, and actually test each answer to demonstrate your ability and knowledge about Excel. This is not a "casual" test of "casual" Excel users, but designed to teach you to "learn by experimenting." as carefully test, and double-check each choice as "True" or "False" before hitting the "Submit" button -- "Hack" your way to success! Zero credit for incorrect choices; partial credit for correct choices. 90% is passing but you can take the test as many times as necessary. We are interested in your mastering these skills!

Topics covered: SHEETS -- Entering Data, Sum Formula, Copying Formulas. CHART --, Creating Charts, Customizing Charts, World-Class Presentation-Quality Reports.

Please fill out the Name, and email boxes (section No. and Date are automatically generated).

Section No Last Name.. First Name

yy.mm.dd. email name .

               (JohnSmith@earthlink.net)

This is an Expense Journal, made up of a Chart (graph) that is based on the data in the Sheet above it.   Your project is to create your own version of this Work-sheet and Chart at or about the same Presentation Quality Level as this one.

This LESSON-TEST is a Teaching Tool involving hands-on practice. Assume nothing. Test everything that you do, every keystroke, etc in Excel. It is the goal of this lesson that you are able to create work-sheets at this level and quality. Click https://auciello.tripod.com/excelquiz01.xls at any time to view and download this worksheet.

Choose all statements that are "True".

1.
This Lesson-Quiz requires: (1)   your opening a blank sheet in Excel.   (1a) Open Excel by clicking its icon on the desktop, or   (1b) finding it under 'Microsoft Office" or (1c) searching for "EXCEL.EXE" using the 'Search' feature on the Main Menu. (2) Then you will see a blank Sheet, like above, without any data. Turn the 'Num Lock' key off, and using the the arrow keys on the key pad, move the Cell Pointer (Highlighted Rectangle) to to A1. Type "CIS 701 ..." etc in this row.      (3) Type "Expenses" in A2, then press the right arrow key on the keypad down to B2, then type "JAN" (do the entire row).   Repeat these directions until what you have typed looks like the first two rows above .

Which of the following statements are true?

THE NUM LOCK KEY MUST BE OFF IN ORDER TO USE THE ARROWS
     ON THE KEYPAD.
Cell A1 CONTAINS 6 WORDS.
Cell B1 CONTAINS 8 WORDS ("EXPENSES thru 'TOTAL")
Cell "H2" is blank.
With "Num Lock" off, you can move the Cell Pointer and enter numbers.


2.
(4) Move to A4. Type "BENEFITS", Press the DOWN ARROW KEY to move to A5, and type "OVERHEAD" etc. Continue to A9 and "TRAVEL". (5) Move to B4, Type 1, Move to C4 to Type 2, continue to G4 and 6. (6) Move to B5 and Enter the Rest of the Numbers on the Sheet. Work by Rows or Columns, whichever you prefer. (7) Move to H4, Carefully Write a Formula to sum "BENEFITS" by typing   =SUM(B4:G4) -- To say this another way -- In cell H4, Type "=" followed by "SUM" (=SUM so far), then type "(", the address of the 1st number in the range (B4), then a colon ":", then the last number in the range (G4), followed by a ")" -- should look like =SUM(B4:G4).   Restating it yet another way:   The Math Function "SUM" begins with an "=" sign, followed by the range to sum -- in this case --   B4:G4 in parenthesis. The colon ":" signifies that there is a range of cells. The formula sums the "BENEFITS" row.   Be sure that you typed =SUM(B4:G4) in H4, then press <ENTER> and you should see 21. Repeat these instructions until the formula gives 21 in H4.

Which statements are true?

Note the Formula Bar" (right above the "C" column).
Click on H4 or move the Cell Pointer to it:

Question: H4 contains =SUM(B5:G5).
Click on H4, note "drag point" in bottom right corner, using the mouse, drag the cell down
       to H9. Verify that the formula has been replicated by checking that correct amounts of
       are in Cells H5 - H9.    Question: H5 contains =SUM(B5:G5).
H5 contains a formula for its row, is displaying 63, and the formula box is showing
      =SUM(B5:G5)
When you copy a formula, EXCEL copies it exactly and does not change the range
       addresses.
In H4, type =B4+C4+D4+E4+F4+G4. Is the result 21?


3.
This item is about "empowering" you with "Help" command.   Showing Excel Help Page - Contents tab.

Access this page by pressing "F1" (the Help key) located on the top row. Click "Contents" tab -- Creating Formulas and Auditing Workbooks -- Using Functions -- About Math and Trigonometry functions.

Which of the following statements are True?

Click "About Math and Trigonometry
      functions." The first function is the "ABS
      Worksheet function."
The last function on the list is the
     "TRUNC" (truncate) function?
Click "About Statistical functions" --
      AVERAGE Worksheet function. You
      can enter up to 32 numbers using the
      following syntax
      =AVERAGE(Number1, Number2, ...)
=AVERAGE(B1:B5) gives the average
      of any numbers in cells A1:A5.
Refer to the Sheet that you created in
      Questions #1 and 2. In cell I10, enter
      =AVERAGE(B4:G9), then press <Enter>
      The number 25 displays in the box.
.

4.
SORTING: means re-ordering the data
table according to Key Fields. Each row in the table as shown is re-arranged in Ascending order of the Total Field, which is the Key field. When sorting, all the cells in a particular row stay together and the row is re-arranged according to the Key Field. In this case, the data is sorted by TOTAL in ascending order -- the smallest TOTAL appears first, and the largest last. Which of the following are true?

"Descending order" means the largest
        number in the Key field appears first.
Fact: To sort on the TOTAL field, click
      anywhere in the H col, click Data - Sort --
      Select "Descending" on Column H, and
      "No header row", press OK. Question: Does
      the top row of the data table now contain
      the largest total?
Click anywhere in the data table in Col B.
      Sort by Col B in Descending order.
      29 is in the top row, the largest expense
      for Jan.
To sort Expense categories (Travel,
      Salaries, etc) in Alphabetical order, select
      Col A, Descending order.
A to Z is Descending Order.
9 to 0 is Descending Order.

5.

The Sheet above was sorted by TOTAL, and we are about to create the above Chart by plotting EXPENSES (horizontally) vs. TOTAL (vertically) to make the above 3D-Column graph.

Fact: This is the Standard Toolbar above. On the Excel Sheet, pass the cursor over each
      icon until you identify the "Chart Wizard" which has Blue-Yellow-Red bars.
      Question: Is the icon just left of the Chart Wizard, the "AutoSum" function?
Click this button if you have the Chart Wizard showing on your toolbar.

Fact: Create the Sheet by holding the "Ctrl" key down, highlight B4 to B9 while holding
      the mouse button down as you move from B4 to B9, then release the mouse and highlight
      H4 to H9. Work at it until your Sheet looks the one above. (Disregard that cell H4 is
      not highlighted. Click here if your Sheet has the ranges highlighted like the one above and
      the Chart Wizard is on the Standard Toolbar.
While continuing to depress the Ctrl Key, Click the Chart
      Wizard icon, find the 3D Column graph at the bottom of
      the Chart Sub-types. Press and hold to view sample.
      Click this answer if your chart looks like this.



6. Time for you to "hack" away and discover!   Follow the prompts and your intuition and continue until your chart looks like this one.

Press the "Next" button to go to Step 2.
In Step 2, you can insert a Title.
In Step 2, the Data Range is shown.
      (Take a close look at this to see how Excel
       refers to cells.)
"Next" takes you to Step 3, where you can
       insert a Title.
Write a Chart Title -- Expense Journal by your
      name -- click the "Finish" button, then click this
      box when you are done.

7.
Your page should look like this: A Sheet in the background, a Chart covering it, and a Chart toolbar overlaying the Chart. Close the toolbar by clicking the "X" in the top right corner.

You can drag the Chart below the Sheet by clicking on it and finding its drag points.
You can remove the "Series Legend" by left-clicking or right-clicking the mouse.
You can remove "S1" (Series Axis) by left-clicking on it, then clicking "clear".
You can "clear" parts of a Chart by right-clicking on the components.
You can change the size of the fonts by left-clicking on the Category Axis.
You can change the size of the fonts by right--clicking on the Category Axis.


8   Which of the following are correct?

Hovering the mouse over the walls produces the word "Walls.
Hovering the mouse over "Salaries" produces the words "Category Axis."
Hovering the mouse over the categories produces the words "Value Axis."
Left-clicking on the "Walls" allows you to change their color.
Left-clicking on a column allows you to "Format Data Series"
Formatting the Data Series includes changing colors and the Chart Type.

9.
Which of the following STATEMENTS are correct?

The expenses (category axis) are aligned at 10 degrees.
The Title is in the Chart Area.
The dark blue area under the columns is called the "Base. "
You can change the "chart type" by right-clicking on the columns and type "T".
To modify "gridlines", right-click on the "Chart Area" then "Chart Options"

10. This project has several features. Which of the following statements are correct?

An Expense Journal Chart plots expenditures by Month and Amount.
Amounts on the Sheet are at the intersection of Categories and Months.
Summing the rows gives Month Totals.
Column totals would give expenses by categories.
Data Tables and their charts are an effective way to communicate budget and expense
      information.
Data is raw numbers / facts. Information is Data that has been organized.
Communication is optimized when Images are mixed with Text.

Section No	Last Name..	First Name
yy.mm.dd.	email name .
	(JohnSmith@earthlink.net)