CIS 314: Advanced Software Solutions
Lesson 3: Microsoft Excel Basics
This course requires students to review basic functions and learn new, more advanced functions in Microsoft Office applications. This lesson discusses basic features in Microsoft Excel that the student should already know well, and introduces features required for the course project.
Objectives important to this lesson:
- Starting the Excel program
- Excel files and commands
- Excel-specific commands
As stated on the previous note page, the process to start any Office application in Windows XP should be familiar:
- Click Start
- Click All Programs (or Programs)
- Click Microsoft Office
- Click the application you need
It is also possible to start an associated application by finding a data file in Windows Explorer (or on your desktop) and double-clicking that file. Windows will open the file in the application that it has been told to use for files of its type: Word for .doc files, Excel for .xls files, PowerPoint for .ppt files, etc. Note that this file association with an application depends on filename extensions. This is one reason to make sure you save files with appropriate extensions, avoiding the temptation to leave the extensions off your filenames.
Basic Excel file concepts vary just a bit from what you learned about Word:
- What is an Excel file?
Word files are typically single documents, although they may have many
pages. Excel files used to be this way, but this stopped being true
a few versions back. The blank file that opens when you start Excel
is a workbook. Workbooks, by default, contain three
separate worksheets. (Worksheets are also called spreadsheets.)
It is easy to add more worksheets to your workbook
if they are needed.
Each of the three worksheets can act as a separate Excel document. However,
you can also perform calculations on one worksheet based on data on
another. In this way, Excel demonstrates data integration
all by itself.
- How do I navigate in a worksheet?
You have several choices. One is to just click on the cell you want
to go to. Another is to use arrow keys or the page up and page down
keys to move to cells you can't see on the current screen. You can also
use the scroll bars to move quickly to different parts of a worksheet.
- How do I select all cells on a worksheet?
first Excel project tells you to click the Select All
button in your current worksheet. The location of this button is not
clear because it has no icon or label. It does not
even look like a button, nor does it have the simulated 3D behavior
of a button when it is clicked.
It appears to be just a corner of the border of your worksheet, above
row 1, and to the left of column A. In the
image on the right, I have drawn a red arrow pointing to the Select
All button. Yes, that's it, the beige rectangle that my arrow
is pointing to.
When you click this "button", all cells in the current worksheet
are selected. Click any single cell to deselect the entire worksheet.
- What about Absolute vs. Relative
Your text introduces a concept that previous users of Excel should already
know more about, but it sidesteps an important point. When you create
a formula in Excel, such as =A5*B7, you are using cell
In this example, both references are relative. This
means that if you were to copy this formula from the
cell it is in to a cell two columns to the right, each
of the references would be adjusted by two
columns. In the new cell, the formula would read =C5*D7.
If you were to copy the original formula to a cell two cells
below it, your formula would be adjusted by two rows.
The formula in the new cell would read =A7*B9.
The text tells you that you can prevent this relative adjustment of
your formula by making the cell references absolute.
This is done by putting a $ before each element of the reference. (e.g.
What the text has not described is that you are not required to make
both elements (column and row) of a cell reference absolute. You can
use finer control by only making the element that you don't want to
||All relative. Excel will adjust column references if copied
horizontally, row references if copied vertically.
||Column references are absolute. Excel will only adjust row references
if copied vertically.
||Row references are absolute. Excel will only adjust column references
if copied horizontally.
||All absolute. Excel will not adjust column references or row
references if copied.
- What is a What-if?
The first Excel project creates a budget worksheet and walks you through
several what-if questions. A what-if is when you change the data in
some cells in a worksheet to see what effect that change will have on
formulas in other cells. This is typically done to determine what effect
changing items in our budget, our income, or our expenses would have
on other dependent items in our worksheet. This requires that the worksheet be constructed with formulas that accurately depict dependencies, and that you make changes to data or formulas in appropriate cells.