Formulas are the essential part of Excel or any spreadsheet program. Workbooks use formulas to perform calculations on the data entered in a spreadsheet. With formulas you can perform addition, multiplication, subtraction, division. Formulas consist of one or more cell addresses and/or values and a mathematical operator, such as + (addition), - (subtraction), * (multiplication), or / (division).
formula in the cell where you want the resulting average to appear.
forget the Order of Operations that applies here.
In spreadsheets it is called operator precedence.
Excel performs a series of operations from left to right in the following
All operations in parentheses
Exponential equations or operations
Multiplication and division
Addition and subtraction
cell in which you want the calculation to appear.
Type in the
equal sign (=) followed by the formula (Using the = sign identifies to Excel
that you are entering a formula).
formulas using cell references
cell in which you want the result to appear
Type the =
cell whose address you want to appear first in the formula
Click on the
next cell address followed by a mathematical operator until you finish entering
want to view your formula, click in the cell with the result. Your formula will appear in the formula bar.
formulas is the same as editing any entry in Excel.
cell that contains the formula
insertion point in the Formula bar with the mouse or press F2 to enter Edit mode
mouse or arrow keys to move to the place you want to edit. Type additional characters or delete to edit the formula.
recalculates the formulas in a worksheet every time you edit a value in a cell.
If you want Excel to wait to recalculate you must tell it by using the
Calculate Option. Open Tools,
Options and click on Calculation. Select one of the options and click OK.
are complex ready-made formulas that you can use to perform a series of
operations on a specified range of values.
Example: to determine the
sum of the range A5 to G5 enter the formula =SUM (A5: G5) instead of entering
function is one of many ready-made functions in Excel.
To see the other functions in Excel use the Insert menu, select Functions
and take a look at the Functions Wizard.
functions consist of three elements.
sign = indicates that what follows is a formula.
name indicates the type of operation you want to perform.
indicates the cell addresses of the values the function will act upon.
To view the
functions select Help Topics, Index. Select
Index of Functions and click the Display button.
This opens a help screen from which you can display function categories
to see the lists of functions.
can enter functions by typing them in the cell or by using the Function Wizard.
The Function Wizard
leads you through the process of inserting a function.
cell in which you want to insert the function
Insert menu and choose Function or click the Function Wizard button (the fx
button) on the Standard toolbar.
Function Category list and select the type of function you want to insert.
function from the Function Name list and click the Next button
the function you selected, another box appears.
Enter the values or cell ranges for the argument.
the most frequently used function. Excel
provides a fast way to perform summations.
Click the AutoSum button on the standard toolbar.
AutoSum guesses what cells you want summed based on the currently
cell in which you want to insert the sum (Try to choose a cell at the end of a
row or column of data
AutoSum button which inserts =SUM and the range of the cells to the left of or
above the selected cell
necessary, adjust the range of cells by clicking inside the selected cell or
formula bar and edit the range
calculates the total for the selected range and places the answer in the
Excel has an
AutoCalculate feature that automatically sums up a range of cells and displays
the figure on your status bar. If
you right-click the status bar, you can access a shortcut menu for viewing the
average of the selected cells.
Copy a Formula
cell that contains the formula you want to copy
the Edit menu and select Copy or click on Copy toolbar button
cell into which you want to Copy the formula
the Edit menu and select Paste or click Paste on the toolbar
You can also
drag and drop by selecting the cell with the formula you want to copy.
Press and hold the Ctrl key and drag the cell selector border to the
place where you want to copy the formula. When
you release the button Excel copies the formula to the new location.
Adding Rows, Columns, or Cells
you to add, delete rows and columns when you have additions to your data.
With the commands in the Insert menu, you can insert more cells, entire
rows and columns.
cell or cells where you want the new cells to be inserted.
Insert menu and choose Cells. The
insert dialog box appears.
Cells Right or Shift Cells Down.
Click OK or
Enter and Excel inserts the cell or cells and shifts the data from the original
cells in the specified direction.
For Rows and
cell above which you want the new row added.
If you want to add a column select a cell
to the left of where you want the new column inserted
Insert menu Column or Row and Excel inserts one row or column as indicated.
insertion there is a shortcut menu. Select
one or more rows or columns then right click one of them with the mouse.
Choose Insert from the shortcut menu.
Deleting Cells, Rows, Columns
group of cells, rows, or columns you want to delete
Edit menu and choose Delete
delete dialog box appears select the desired Delete Option: Shift Cells Left,
Shift Cells Up, Entire row, or Entire Column.
Make It Fit
To move the
lines in your spreadsheet, move your mouse pointer in the heading areas of the
column or row. The pointer will
change to a double-headed arrow. When
you see this shape, drag and drop the border to a new height or width. (Hold down the left mouse button and drag the row or column
to a new size to fit your data.)
To make a
column as wide as its widest entry, double-click the right border of the column
To make a
row as tall as its tallest entry, double-click the bottom border of the row
to determine the width and height of your rows and columns.
Select the rows or columns you want to change, open the Format menu and
choose Column or Row and select AutoFit Selection.
Excel will make the selected areas as wide or tall as needed.
Making Your Document Look Great!
Formatting Toolbar to Dress up your spreadsheet.
canít decide what to do, Excel will help with AutoFormat.
Select the data you want to format then open Format menu and choose
AutoFormat. A dialog box appears
with options you can try. Look
through the Table Format list to find a style you want to use. Look at the sample, highlight, click OK and Excel formats
your data according to your choice.
bottoms on your Format Toolbar, you can add dollar signs, percent signs, align
cell or range that contains the values you want to format.
Format menu and choose Cells.
Number tab in the dialog box
Category list, select the numeric format you want to use.
default aligns data depending on its type.
Text left, numbers right. To change this configuration
cell or range you want to align. To
center a title or other text over a range of cells, select the entire range of
cells in which you want the text centered, including the cell that contains the
format menu and select Cells
format cells dialog box, click the Alignment tab.
alignment you want and click OK.
Decorate Your Cells
backgrounds will enhance your worksheet. Try
the Borders choice in the format cells dialog box.
The Patters tab will also offer options for the background of your cells.
create charts as part of your worksheet (embedded) or as a separate chart
worksheet. Both types of chart can
be linked to your worksheet.
data you want in your chart. Be
sure to include the labels when you select if you want them in the chart.
ChartWizard with the data selected.
mouse pointer to where you want the upper left corner of the chart to appear on
your worksheet. Hold down the mouse
button and drag to define the size and dimensions of the chart.
To create a perfect square, hold down the Shift key as you drag.
If you want your chart to exactly fit the borders of the cells if
occupies, hold down the Alt key as you drag.
Once you set
your parameters, release the mouse button.
The ChartWizard Step 1 of 5 will appear. Respond to the verification of the range and if correct click
Step 2 of 5
appears asking you to select a chart type, then click Next.
Step 3 asks
you to select a chart format, a variation on the selected chart type.
requires that you determine the basis for the data series, rows or columns and
selection of the starting row and column. Click
allows you to add a legend, title, or axis labels then click Finish.
Your completed chart appears.
Return to Spreadsheets Handout
|This page was updated on: 04/10/02|