basic excels in information communication technology

basic excels

1. Excel
A Brief Overview

2. 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
items in a list
Creating Charts and Graphs – You can
create colorful charts and graphs from the data
in your worksheet. Excel will automatically
update the chart to display any changes you
make in your data.
0
2
4
6
8
10
Mon TueWed Thu Fri
=sum(B6:B23)
=AVERAGE(F4:F8)
=count(B2:B25)

3. The Excel Window
ROW 3
COLUMN F
Column
labels
Row labels
Worksheet
tabs
Active Cell
F3
Fill handle
Menu bar
Tool bar
Formula Bar
gridlines

4. The Active Cell
The active cell is indicated by a dark outline,
and the column letter and row number in the headers are raised.
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.

5. 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.
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.
When working
with cells, your
mouse pointer
becomes a plus
icon

6. 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

7. 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!!

8. 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

9. Format Your Worksheet
•Select a column •Select a row
•To select the entire
worksheet click upper left
corner
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.

10. 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.

11. 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.

12. Clearing Cells
•Cells can be cleared of just the contents or just the formatting – or both.
•If you select a cell and press the delete key, the
contents only will be deleted.
Choose
Edit Clear

13. Fill – down, across, series
•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
ACTIVE CELL FILL HANDLE

14. 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.
  
                                                                                                                    

15. 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.
 

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 Value2
^ Exponentiation 3
* or / Multiplication or Division4
+ - 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
Operation Symbol Symbol Name
Addition + Plus Sign
Subtraction - Dash or hyphen
Multiplication * Asterisk
Division / Forward slash
Formula Operators

16. 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.

17. 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.
Excel has hundreds of prewritten formulas which make it easy to do
complex procedures with numbers, dates, times, text, and more.
Insert Function
•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.

18. 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.

19. 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
  
                                                                             

20. 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.

21. 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
Relative Absolute
To toggle between seeing the formulas
and seeing the results, hold down the
Ctrl key and press the tilde ~

22. 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

23. The Auto Calculate Space
Select any cells with numbers in
them, the sum of those numbers
automatically display in the “auto
Calc” space.

24. 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

25. 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.

26. 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
0
10
20
30
40
50
60
70
Amherst Buffalo Clarence East Aurora Kenton Orchard ParkWilliamsville other
Buffalo Seminary / School Districts
0
10
20
30
40
50
60
70
Amherst Buffalo Clarence East Aurora Kenton Orcha rd ParkWilliamsville other
Buffalo Seminary / School Districts
0
10
20
30
40
50
60
70
Amherst Buffalo Clarence East Aurora Kenton Orchard ParkWilliamsville other
Buffalo Seminary / School Districts
Amherst
Buffalo
Clarence
East Aurora
Kenton
Orchard Park
Williamsville
other
Sem girls come from all over
Western New York

27. Chart Wizard
Select all the cells containing the data you want to chart.
Click the Chart Wizard button on the Standard toolbar.

28. 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.

29. 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.

31. 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!