You can also use Excel's predefined functions, either by typing them
in or copying them into the formula using the Function command from the
Insert Menu. Select one from the list of functions and it will be pasted
into the formula with a dummy parameter where a value is expected. This
will be highlighted so that the next thing typed in will replace it. Where
there is more than one parameter, they are separated by commas. Some functions
take a range of cells as a parameter.
Besides AutoSum, described above, some other useful functions are AVERAGE,
which gives the arithmetic mean of a set of values, and MAX and MIN which
find the largest and smallest. Other functions include
Trigonometric functions.
Logarithmic functions.
Statistical functions such as variance and standard deviation, and those
which perform linear and multiple regression.
String functions to analyse and manipulate text.
To edit a cell, just double click on it. What appears in the cell also
appears in the formula bar located directly underneath the toolbar; you
can also edit what's stored in a cell in that bar. (If the formula bar
does not appear, make sure there is a check mark next to Formula Bar in
the View menu.)
3.2. Data Types
Excel classifies data into types. Each type is treated differently. When
you input data, Excel decides which type is most appropriate following
certain rules. Where possible it will also select the internal format closest
to that of the input data and apply it to the cell. Text constants are
contained in quotes and force Excel to accept what you type in a particular
way if the program assigns the wrong type. For example typing ="12:00"
into a cell will result in it displaying the text rather than converting
it to a time. Formats are described in section 4.4.
The data types are described below together with their uses:
Text
Ordinary characters, or anything which is not another data type. If there
is more text than a cell can show, it will overflow into the adjoining
cell if that cell is empty, or onto a new line if Wrap Text is specified
for the cell (see section 4.4). The text is assigned
to one cell but displayed across several. Text will normally be left justified
within the cell.
Number
Figures. If a number is too large to be shown in the cell, the format is
changed to a scientific notation. If there is still insufficient room or
the cell has a specific format, a row of hashes (#######) will appear instead.
Numbers will normally be right justified within the cell.
Date
Anything matching one of the specified types of date format.
Formula
When the first character typed is the = symbol, Excel expects a formula
to follow. Formulae can be complicated and can contain references to cells,
numbers, and predefined functions (see Section 3.1).
3.3. Fill cells
Let's say you have a spreadsheet that has numbers in columns A and B. You
want the sum of these numbers to appear in column C. You've summed up A1
and B1 in C1, but now you want to extend this equation to the other cells
in column C. To do this, simply highlight C1 and drag the mouse down to
the last cell that you want to apply the summation to:
Now go to the Edit menu, go to the Fill submenu, and select Fill Down.
This copies the equation of the first cell into all the cells below it.
This is done relative to the row, so the equation in C2 refers to cells
A2 and B2, the equation in C3 refers to cells A3 and B3, and so on. Now
your worksheet looks like this:
Note that this method will work for any equation in C1, not just summations.
So if you wanted to multiply A1 and B1 and display the product in C1, simply
enter =A1 * B1 in C1, then perform the above operations again.
3.4. Data Series
There is often a need to set up a column or row which contains values in
a sequence. For some sequences this can be done quickly. Type the initial
value of the sequence into the first cell and then select the column or
row and click on Edit, go to the Fill submenu and click on Series.
If you selected one row or column, the Series in the "Setting" box will
already by filled in. If a rectangle is currently selected the operation
could involve rows or columns and this setting must be made manually.
The Step Value can be specified in either positive or negative steps.
The Type settings are as follows:
Linear
Adds the step value to the previous cell value.
Growth
Multiplies the step value by the previous value.
Date
Used if the initial value was a date and permits use of the Date Unit column
to set up a series of dates, stepping through days, week days, months or
years.
AutoFill
Based on the data supplied the computer guesses what would be the best
way to complete the series. It covers all of the individual options shown
above (linear, growth, date and more).
Trend Box
This box, when checked, overrides the step value box and calculates a best
fit line based on the available data, i.e. if the data highlighted were
graphed, it calculates the line that would best fit the data and fills
the highlighted area with values from this line. If the data values on
the line are different from the values that were originally given, the
original values are rewritten as the new ones. The best fit line is either
linear or exponential, depending on whether a linear or growth series is
presented. Don't worry too much about this one; this isn't an engineering
class, so you probably won't find this function too helpful.
4. Editing data
4.1. Selecting
The following methods can be used to select cells or groups of cells:
Single cell
Click on it with the mouse. You can also go directly to a cell by selecting
Go To from the Edit menu and typing the cell reference in the space provided.
Rectangular range of adjacent cells
Drag across them with the mouse or select one corner cell and, holding
the Shift key down, click the opposite corner.
Set of non-adjacent cells
Mac: click on each cell while holding down the Command key. PC:
click on each cell while holding down the Ctrl key.
Complete column or row
Click the column letter or row number. Drag across the letters or numbers
to select adjacent rows or columns. Use the Command key to select non-adjacent
rows and/or columns.
Entire spreadsheet
Click the blank box above row 1 and to the left of column A.
4.2. Moving Data (copying and pasting)
Data in one part of a worksheet can be copied to another part, or to a
second worksheet. This can be done using the Cut, Copy and Paste commands
on the Edit Menu, standard keyboard shortcuts or the special shortcuts
unique to Excel.
Select the cell or group of cells you want to copy. Select Copy from
the Edit Menu. When you move to a new location in the worksheet, the old
selection will be surrounded by a whirling marquee. So long as the marquee
remains whirling, whatever it encloses is available for pasting.
Select the upper left cell of the portion of the worksheet you want
to paste to. The copy will be expanded to be the same shape and size as
the original if there is room. Paste the data in by selecting Paste from
the Edit Menu, or pressing the Enter key. Using the Enter key cancels the
marquee while using the Paste command or the keyboard shortcut leaves it
whirling, so that further pastes are possible.
Cut and Paste works in exactly the same way, except that the original
is deleted as the copy is pasted and the marquee is cancelled automatically.
Paste copies values and formulae. It will also transfer any formatting
applied to the original cells. To transfer only the formatting information
or to copy the value calculated by a formula rather than the formula itself
use Paste Special under Edit instead of Paste, and choose the appropriate
options from the dialogue box.
Note that Paste only copies the information from the original cell.
If you change the original cell in any way, the copied cell will not reflect
these changes. If you want the data in the copied cell to automatically
change, see section 4.3, "Linking cell values."
The Operation settings can combine the values being pasted with those
in the cells being pasted to. The original values will be gone forever
so if they are needed as well as the new values, use a calculation function
instead.
The Skip Blank Cells option in Paste Special can be used to ignore any
blank cells in the data being copied, so that a value that would otherwise
have been pasted over remains in place. The Transpose option can be used
to change columns into rows and vice versa.
By clicking the Paste Link button in the Paste Special dialog box, you
can create links between cells in different workbooks. Suppose you had
two workbooks, called Book1 and Book2. You want to create a link to cell
A1 in Book1 from cell B2 in Book2. To do this, you would copy cell A1 in
Book1 as described above. Then you would open up Book2 (or switch to it
using the Window menu if it's already open), go to the Edit menu, go to
Paste Special, and click the Paste Link button. Now if you change cell
A1 in Book1, the changes will show up in Book2.
The operations described above are used between cells in the worksheet
using a mechanism specific to Excel. They are wholly independent of the
standard cut and paste operations which use the clipboard and are used
when editing text in the Formula Bar and to import graphics. Excel's cell
level cut and paste operations do not use the clipboard, so data cut or
copied from Excel cannot be pasted into other applications.
4.3. Linking cell values
To link cell values, instead of typing a value, type the = sign, then click
on the cell whose value you want to refer to and press Return. When you
change the value of the original cell, the linked cell will be updated
automatically. This also works across worksheets, so you can reference
a value from a cell in a different worksheet.
As described above, you can also use cell references
in formulas. For example, to multiply the value of cell A1 by the value
of cell B1 and display the product in C1, simply type = A1*B1
in cell C1. If you fill this formula into C2 using the method described
above, the formula in C2 will read =A2*B2.
If you don't want this to happen, you can use absolute cell references.
For example, if you want all the values in column C to display the product
of A1*B1, you should enter =$A$1 * $B$1 in C1, then fill the formula
into the other cells in column C. This is useful if you have certain values
that you want represented in several different places in a worksheet. A
practical example of this could be a business that sells a number of different
items. Suppose the sales tax rate is entered in cell A1 and the cost without
sales tax of each item is entered in the rest of the cells in column A.
You could display the cost including sales tax of each item in column B
very easily. Simply enter =A2 + $A$1 * A2 in cell B2, then fill
it into the rest of the cells in column B. Now, suppose the sales tax rate
changes. All you need to do is change the value stored in A1, and all the
values in column B will update automatically to reflect the new rate.
4.4. Formatting cells
Excel provides many options for formatting cells. For example, you can
make the text of the cell appear in a larger font, make the cell data center-aligned,
or automatically place dollar signs before currency values. If you've used
Microsoft Word at all, many of these options will be quite familiar to
you.
Before you format a cell, you need to select it. You can also format
multiple cells at once by selecting them as described in the previous section.
Once you've selected the appropriate cell(s), you can then do one or more
of the following:
To make text bold, italicized, or underlined:
click on the button marked B, I, or U on the toolbar.
To make text left, center, or right aligned:
click on one of the alignment buttons on the toolbar.
To create borders:
click the Borders button, which looks like this: 
You can change the type of border created by clicking the down arrow right
next to the button.
To change the background color of a cell:
click the button that looks like this: 
You can change the current color by clicking the down arrow right next
to the button.
To change the text color of a cell:
click the button that looks like this: 
You can change the current color by clicking the down arrow right next
to the button.
To format numerical data as currency:
click on the dollar sign button on the toolbar. For example, if you enter
123.45 in a cell and then click this button, it will appear as $123.45.
To format numerical data as a percentage:
click on the percent sign button on the toolbar. For example, if you enter
.5 into a cell and then click this button, it will appear as 50%.
Wrap text:
if you type information that takes up more space than the cell size, Excel
usually makes the text flow into the next column. If you want it to wrap
to the next line instead, select Cells... from the Format menu, click on
the Alignment tab and check the box marked Wrap Text.
For more formatting options:
select Cells... from the Format menu.
4.5. Inserting and removing rows and columns
To insert a row, click on a cell and select Rows from the Insert menu.
The new row will be inserted just above the row containing the currently
selected cell.
To insert a column, click on a cell and select Columns from the Insert
menu. The new column will be inserted just to the left of the column containing
the currently selected cell.
To delete a row or column, select the row/column as described
above. Then go to the Edit menu and select Delete.
5. Charts
You can use the Chart Wizard tool to quickly and easily convert figures
into charts. These charts can then be overlaid onto worksheets to create
unified reports.
To use this feature,
select the data that you want represented in the chart. You can select
non-ajoining data by holding the command key down when selecting (see section
4.1, "Selecting"). Then click on the Chart Wizard button. Then click
and drag to select the region in which you want your chart to appear. If
you want your chart to appear on a different worksheet, just switch to
that worksheet before clicking and dragging. When you release your mouse
button, the Chart Wizard will appear with easy-to-follow on-screen instructions.
If you want to know more about any options, just click the Help button.
The first dialog box that appears will confirm which information will
be considered in preparing your chart. If you selected the data before
clicking on the chart wizard button, the information in this dialog box
should be all set. Just click "Next". The next step is to select which
type of chart you want. Just click on the picture of the chart that you
want to use and click "Next". Again, you will be asked to specify certain
details of how you want your chart to appear. Simply follow the on-screen
directions.
When your chart is established, you can double-click on any part of
it to edit certain features. For example, if you double-click on the axis-labels,
you can rename them or change their color or font, etc. The chart can be
manipulated like any other object. If you change the data on your spreadsheets,
the chart will change automatically.
6. Advanced Features
6.1. Macros
If you need to perform one action multiple times, it might be easier to
record a small program, called a "macro" to perform it for you. To do this,
you simply tell Excel to start recording, then perform the action you want
to assign to the macro. You can then run the macro quickly by using a shortcut
key.
To record a macro, go to the Tools menu, go to Macro, and make sure
"Use relative references" is checked. Then go back to that Macro submenu
and click on Record New Macro. The "Record new macro" dialog box will pop
up. You can assign a name to your macro if you'd like, as well as type
a short description. Now click on the button marked Options>>. The dialog
box will now give you some more options. You'll probably want to make your
macro into a menu item (so you can access it through the Tools menu) or
assign a keyboard shortcut to it (so you can press a sequence of keys to
run the macro).
Now click on OK. You'll be returned to Excel. Simply perform the actions
you want the macro to do. For example, suppose you wanted your macro to
enter the word "Hello" in the current cell, then go to the cell directly
below it. Then you would just type "Hello" in the current cell and press
Return. Once you're done recording your macro, be sure to press the Stop
button which should now be visible on your screen, probably in the right-hand
corner. (The button just has a little black square in it.)
Your macro has now been recorded. Now, whenever you want to have the
word "Hello" appear in a cell, just select that cell and run your macro
(using the menu item and/or the shortcut key that you defined just before
recording your macro). You can also run macros by going to the Tools menu
and selecting Macro... This will pop up a list of all your macros. To run
a macro, click the Run button.
6.2. Goal Seek
Goal seek is a tool to help the user find out which value of a cell produces
a given output in another cell (calculated using a formula). Excel varies
the value of the cell defined in "By changing cell" until the one defined
in "Set Cell" has the value set in "To value". This is very useful for
solving single variable equations. The solver can be used to solve multiple
variable equations.
To use goal seek, go to the Tools menu and select Goal seek.
6.3. Solver
Solver is a powerful optimization and resource allocation tool. It lets
you maximize or minimize whatever parameters you are manipulating. There's
a lot involved in using Solver, but the short description here should be
enough for whatever you need to do. However, asking the computer for help
is never a bad idea.
To start Solver, go the Tools menu and select Solver...
First specify the target cell that you want to minimize, maximize or set
to a certain value
Then specify the changing cells that you want to be adjusted until your
solution is found.
Specify the constraint cells that must fall within certain limits or satisfy
target values. You can specify up to 500 constraints, 2 for each changing
cell plus 100 additional constraints, representing a total of no more than
1000 cells in a problem
After solving the problem you can create three types of reports that summarize
the results of a successful solution process.
When you save a workbook, the last selections made in the Solver
Parameters dialog box are retained for each worksheet on which you have
defined a Solver problem. However, you can define more than one problem
for a worksheet by saving them individually using the Save Model button
in the Solver Options dialog box. You can then use the Load Model button
to open a saved problem model.
You can also save the Solver settings for the problem as a scenario
using the Scenario Manager
6.4. Pivot Tables
The Pivot Table Wizard lets you create an interactive table which summarizes
the data of a list (similar to a database, where the rows are records and
the columns are fields) in different ways by organizing the different fields
in rows or columns.Once the pivot table is created, it is possible to modify
the organization by dragging the buttons to different areas.
To start creating a pivot table, go to the Data menu and select PivotTable.
7. Online Help
Online help is available by clicking on the button with the arrow and question
mark on the right side of the toolbar, then clicking on the part of screen
that you want to know more about.
You can also hold the mouse pointer over a toolbar button without clicking.
After a few seconds, the function of the button will pop up.
8. Further Information
Excel is a complex package with many facilities other than those described
here. Several of the features described here have extra aspects which have
not been covered. The manual and the on-line help system describe these
fully and comprehensively.
Excel is a popular package, and so has attracted a wide range of books
explaining the package. Most of these are more useful than the manual supplied
with Excel which tries to cover the Macintosh and PC versions in one volume.
System: Apple Macintosh or Microsoft Windows
Author: Rod Ackland &Kath Baker
Date: August 1992
Modified for Excel 4: Scott M. Lewandowski, 2/10/96
Modified for Excel 5: Alicia Svenson, Eugene Yi, Thomas Crulli,
2/17/97
Modified: Lisa Cozzens, Amanda Kudler, 2/7/98
Great care was taken to maintain the accuracy of this document during
its transition from explaining version 3 and version 5 of Excel. Should
errors exist, the author apologizes.
You can also
enter formulas in cells. Formulas can automatically perform mathematical
functions for you, using data from other cells. Some formulas are already
defined in Excel; for example, you can sum up a number of adjacent cells
by selecting them and then clicking the AutoSum button, signified by the
Greek letter Sigma (shown at left). You can also enter your own formulas
in a cell. To do this, you need to prefix the formula by the = sign. For
example, to multiply cells A1 and A2 and display the product in cell A3,
click on A3 and enter the following formula: