Excel

A Brief Overview
 What is a Spreadsheet?
A spreadsheet is a document that is
entirely made up of rows and columns.
It is used to list and analyze data.

Editing and formatting – Excel works much

like the tables in MS Word

Formulas and functions – Excel

allows you to perform calculations
and analyze data. Common
calculations include: finding the
sum, average or total number of
Creating Charts and Graphs – You can items in a list
create colorful charts and graphs from the data
in your worksheet. Excel will automatically
update the chart to display any changes you =sum(B6:B23)
make in your data.
10
8 =AVERAGE(F4:F8)
6
4
2
=count(B2:B25)
0
Mon Tue Wed Thu Fri
 The Excel Window
Active Cell Fill handle
F3

Menu bar
Tool bar
Formula Bar
Column
labels
ROW 3

Row labels COLUMN F

gridlines

Worksheet tabs
 The Active Cell

The worksheet is a grid of columns (designated by letters) and rows (designated by numbers).
The letters and numbers of the columns and rows (called labels) are displayed in gray buttons
across the top and left side of the worksheet. The intersection of a column and a row is called a
cell. Each cell on the spreadsheet has a cell address that is the column letter and the row
number. Cells can contain either text, numbers, or mathematical formulas.

The active cell is indicated by a dark outline,

and the column letter and row number in the headers are raised.
 Entering Data

When you enter data, the characters appear simultaneously in the Formula Bar and cell.
The characters do not actually go into the cell until you press Enter or Tab.

When working
with cells, your
mouse pointer
becomes a plus
icon

To enter data into a cell, first click the cell in which you want to
enter your information. Then type the data in either the cell or
Formula Bar and press Enter or Tab.

Pressing Enter moves you to the next cell down, while pressing Tab
moves you to the next cell to the right.
 Resize a Column

In a cell, text can be any combination of

numbers, spaces, and non-numeric
characters.

If the entered text exceeds the column

width it will overlap the boundary into the
next column when that column is blank. If
the next column already contains data, text
that does not fit in the cell is hidden.

Clicking the cell, however, reveals its entire

contents in the Formula Bar.

To increase column width, drag the right

side of the column header with the double-
headed pointer.

To make the column width fit the contents

of its widest cell, double-click the boundary
on the right side of the column
 Insert/delete a row or column
To insert:
Select a column to the right of where you want
to insert a new one.
Or select a row beneath where you want to
insert a new one.
From the INSERT menu choose row or column.
If you want to insert more than one, select
more than one column or row.

To delete:
Select either the row or column you
wish to delete and press the del key
or choose “delete” from the EDIT
menu.
You can also access all
of these commands
from the context menu
-RIGHT CLICK!!
 Move or Copy Data
• Drag and drop to move selected data

Grab any edge with

your cursor and
drag

You can copy and paste by selecting cells – right click to cut or copy
Select either the exact number of cells to paste into – or just the very first one
– right click to paste
 Format Your Worksheet
Formatting your spreadsheet is very similar to formatting in Word.
Many of the same commands work in both.
Remember that before you do any formatting, you must SELECT (highlight) the items to be formatted.

•To select individual cells, just click on them

•To select adjacent cells. Click and drag to include them
•To select several cells which are not adjacent, hold down the Ctrl key and click on each cell to include.

•To select the entire worksheet

•Select a column •Select a row click upper left corner
Formatting Dialog Box
This dialog box is very
similar to what you
learned about in MS
Word. You should be
able to experiment
with the tools found
on each of the tabs.
Change Number Format

One of the tabs in the format dialog box is

new. It is the FORMAT NUMBER tab.
Because Excel is all about numbers and
calculations, this section makes it easy to
use the right type of number for the job!

Remember to select the cells, columns,

rows or entire spreadsheet before you
choose the format for you numbers or
dates.
 Clearing Cells
• Cells can be cleared of just the contents or just the formatting – or both.

Choose
Edit Clear

•If you select a cell and press the delete key, the contents
only will be deleted.
 Fill – down, across, series
ACTIVE CELL FILL HANDLE

• In the lower right hand corner of the active cell is Excel’s “fill handle”.
• When you hold your mouse over the top of it, your cursor will turn to a crosshair.
• If you have just one cell selected, if you click and drag to fill down a column or
across a row, it will copy that number or text to each of the other cells.

• If you have two cells selected, Excel will fill in a SERIES. It will complete the
pattern. For example, if you
– Put 4 and 8 in two cells
– Select them
– Click and drag the fill handle
– Excel will continue the pattern with 12,16,20.etc.
• Excel can also auto- fill series of dates, times, days of the week, months
 Formulas
Formulas are entered in the worksheet cell and must begin with an equal sign
"=". The formula then includes the addresses of the cells whose values will be
manipulated with appropriate operands placed in between. After the formula is

 
typed into the cell, the calculation executes immediately and the formula itself is
visible in the formula bar. See the example below to view the formula for
calculating the sub total for a number of textbooks. The formula multiplies the
quantity and price of each textbook and adds the subtotal for each book.

                                                                                                                     
 Formula Operators
There are four basic Mathematical Operators when writing a formula.  These operators are used to
tell the formula what action to perform.  The following table lists the operators, its symbol.

Operation Symbol Symbol Name

 
Addition + Plus Sign
Subtraction - Dash or hyphen
Multiplication * Asterisk
Division / Forward slash

The next table lists the order of operation for each mathematical operator.  As you begin to write your
formulas, keep in mind that information in parenthesis ( ) is always performed first while everything
outside the parenthesis is performed left to right.

Operator Operation Order of Calculation

AND, OR, NOT Logic Test: AND, OR, NOT 1
+ or - Positive or Negative Value 2
^ Exponentiation 3
* or / Multiplication or Division 4
+- Addition or Subtraction 5
& Text Concatenation 6
Logic Test      7
= Equal to  7
<> Not Equal To 7
<= Less than or Equal to 7
>= Greater than or Equal to 7
 Functions
• Built-in Excel Functions can be a faster way of doing mathematical operations than
formulas.

• Example- if you wanted to add the values of cells D1 through D10, you could type the
formula "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10".
 
• A shorter way would be to use the SUM function and simply type "=SUM(D1:D10)".

Function Example Description

SUM =SUM(A1:A100) finds the sum of cells A1 through A100
AVERAGE =AVERAGE(B1:B10) finds the average of cells B1 through B10
MAX =MAX(C1:C100) returns the highest number from cells C1 through C100
MIN =MIN(D1:D100) returns the lowest number from cells D1 through D100
SQRT =SQRT(D10) finds the square root of the value in cell D10
TODAY =TODAY() returns the current date (leave the parentheses empty)

SUM( ) function
The SUM( ) function is probably the most common function in Excel. It adds a range of numbers. To build a
SUM( ) function, begin by typing the = sign; all functions begin with the = sign. Next type the word SUM
followed by an open parenthesis. You must now tell Excel which cells to sum. Using the mouse, click and drag
over the range of cells you wish to add. A dotted outline will appear around the cells and the cell range will be
displayed in the formula bar. When you have the correct cells selected, release the mouse button, type a closing
parenthesis and press the <Enter> key.
If you do not want to use the mouse, type in the references of the cells you want to sum. For example, to add
cells B3 through B5, type =SUM(B3:B5). Excel interprets B3:B5 as the range of cells from B3 to B5.
 Insert Function

Excel has hundreds of prewritten formulas which make it easy to do complex

procedures with numbers, dates, times, text, and more.

•Type a brief description of what you want to do in the

Search for a function box. In this example, you
could type "mortgage payment" or some other
keywords.

•Click Go.

Tips
•You can also select a function category in the Or
select a category box. This action will display a list of
related functions, which you can then browse through.

•If you'd like help on how to enter the arguments, you

could type the function name in the Search for a
function box and click OK.

Click the Insert Function button on the formula bar.

The Insert Function dialog box opens

In the Search for a function box, type a description

of what you want to do.
 AutoSum
AutoSum button

In Excel, the standard toolbar has a button that simplifies adding a column or row of numbers. The
AutoSum button, which resembles the Greek letter Sigma (shown above), automatically creates a
SUM( ) function. When you click the AutoSum button Excel creates a sum function for the column of
numbers directly above or the row of numbers to the left. Excel pastes the SUM( ) function and the
range to sum into the formula bar. If the range is not correct, simply select the proper range with
your mouse on the worksheet. When you have the correct range entered, press the <Enter> key to
complete the function.
 Autofilling Functions
Autofill can also be used to copy functions.  In the example below, column A and column B each
contain lists of numbers and column C contains the sums of columns A and B for each row. The
function in cell C2 would be "=SUM(A2:B2)". This function can then be copied to the remaining
cells of column C by activating cell C2 and dragging the handle down to fill in the remaining
cells. The autofill feature will automatically update the row numbers as shown below if the cells
are reference relatively

                                                                                
 Cell Reference
There are two basic types of cell references in Excel: relative and absolute. The difference between
absolute and relative cell references becomes apparent when you copy formulas from one cell to
another. When you copy a formula containing relative references, the references are adjusted to
reflect the new location. Absolute references always refer to the same cell, regardless of where the
formula is copied. Relative references are the default.

To create an absolute reference, type $ before each part of the cell address.
 Relative / Absolute

Relative Absolute
This shows the formulas used to
create the order form below.

We used the fill handle which

usually gives us the relative
reference.

For the sales tax calculation

we needed to use the absolute
reference in cell C9

To toggle between seeing the formulas and

seeing the results, hold down the Ctrl key
and press the tilde ~
 Merge cells
A shortcut to merge cells and center data is the icon on the formatting toolbar.

Select the cells you want to merge and click the icon on the toolbar
 The Auto Calculate Space

Select any cells with numbers in them,

the sum of those numbers
automatically display in the “auto Calc”
space.
Printing Tips
To only print a small part of your
spreadsheet
–Highlight the area you want to print
–From the FILE menu
–choose PRINT AREA
–Set print area
 Page Set Up Tips
Two handy items in the PAGE SETUP dialog box (under the FILE menu)

Fit to ___ pages

Excel will fit your document into the number of pages you
specify. If you are working on a chart or diagram that is just a
bit over the size for a page, checking the “fit to” button will
shrink your document proportionally to fit.

Print your document without those pesky grey gridlines

by unchecking the button on the “Sheet” tab of the page
setup dialog box.
 Charts
A chart is a graphic representation of data. Charts are often used to make large quantities of data more easily
understandable, and recognizable on first view. Charts represent data in different ways depending on the type of
data that is presented.

Buffalo Seminary / School Districts

70

60

50

40 Buffalo Seminary / School Districts

30 70

60
20

50
10

40
0
Amherst Buffalo Clarence
Buffalo Seminary / East Aurora
School Kenton
DistrictsOrchard Park Williamsville other
30

70
20

60
10

50 0
Amherst Buffalo Clarence East Aurora Kenton Orchard Park Williamsville other

40 Buffalo Seminary / School Districts

30

Amherst
20

Buffalo
10
Clarence

0 East Aurora
Amherst Buffalo Clarence East Aurora Kenton Orchard Park Williamsville other
Kenton

Orchard Park

Williamsville

other

Sem girls come from all over

Western New York
 Chart Wizard

Select all the cells containing the data you want to chart.

Click the Chart Wizard button on the Standard toolbar.

The Chart Wizard will present a selection
of chart types, each of which includes
several subtypes. If none of these options
suits your needs, you can click the Custom
Types tab to access a list of specialized
chart types.

Click Next, and the Chart Wizard will

present a screen verifying the range of
data you want to include in your chart.
You can change the range if necessary—
just click in your worksheet and drag to
select the appropriate cells.
Click Next again, and the Chart Wizard will
present options that govern which
elements are included in your chart. For
instance, you can click the Titles tab and
enter a title for the chart and for the chart
axes.

Click Next once more to advance to

the Chart Wizard’s final screen. Here
you can specify whether to insert the
chart on its own chart sheet or embed
it on a worksheet. If you select the
first option, type a new sheet name in
the As New Sheet: text box. If you
select the second option, just use the
As Object In: drop-down list to choose
the sheet where you want the chart to
appear. (The current sheet is the
default.) After you make a selection,
click Finish. Excel will create your new
chart.
After you’ve created a chart, you can still modify any specification made while running the
Chart Wizard. The Chart menu and the Chart toolbar, which appear whenever a chart or
chart sheet is selected, include options that correspond to the choices the Wizard offers.
You can also click the Chart Wizard button to run the wizard again and revise their
original choices.

You can right click to

format any item on
your chart. The format
dialog box should be
familiar to you by now!