Transcript Microsoft Excel - Colorado State University Extension

Microsoft Excel
Spreadsheet
Software
What is a Database and
what is it used for?
A database is an organized collection
of data related to a particular topic
or purpose.
 The primary function of a database
is to enable the user to organize and
retrieve information in a manner
defined by the user.

7/18/2015
Microsoft Excel 2000
2
Flat-File vs. Relational


A Flat-file database consists of a
single database file or table
which contains all the
information about a topic. It
does not physically link or point
to other files.
A Relational database consists of
multiple tables linked together
by at least one common field.
7/18/2015
Microsoft Excel 2000
3
STUDENT INFORMATION TABLE
Name
Mary Wills
Address
ClassID ClassName Instructor
Time
1010 1st St. CO150 Composition Matthews
3 MWF
Jim Johnson 234 Maple CO150 Composition Matthews
3 MWF
Mark Smith
3 MWF
111 W. 3rd CO150 Composition Matthews
Products Table
ProdID
ProductName UnitPrice Quantity SupplierID
1234
Cajun Seasoning $1.00
2000
S450
Supplier Table
SupplierID SupplierName Address Contact
S450
New Orleans
Delights
New
Orleans,
LA
Phone
Anne Rice 800-555-1111
Basic Concepts
7/18/2015
Microsoft Excel 2000
6
Basic terms
Columns have letter headings
 Rows have number headings
 Intersection of a row and a column
is called a “cell”
 Cells are basic building blocks of
Excel
A
B

row
7/18/2015
column
1
2
cell
Microsoft Excel 2000
7
Customizing the toolbar

If menus show recent commands
only…


Tools/Customize/Options to turn it off,
delay, or reset
If you can’t see all of the standard &
format toolbars…

Tools/Customize/Options to break it
into 2 separate toolbars
7/18/2015
Microsoft Excel 2000
8
Text and Numbers


Type in cell, appears in formula bar
Edit 3 ways





Backspace (if you’re still in cell)
D-click inside cell
Formula bar
Text has no value
Numbers (0-9) and symbols (+, /, *, -)
have values, can use formulas

Negative numbers use - or ( )
7/18/2015
Microsoft Excel 2000
9
Inserting, deleting, shifting
cells
Insert/cells > to insert
 R-click will do it too (contextsensitive)
 Always above and to the left
 Always reletters, renumbers
 Edit/Delete > to delete

7/18/2015
Microsoft Excel 2000
10
Cutting, copying, pasting
cells
Select first cell, then cut or copy
 Select destination cell, then paste
 For multiple
pastes,Toolbars/Clipboard >
Clipboard
 12 items stored on clipboard
 Screentips shows you which one

7/18/2015
Microsoft Excel 2000
11
Text formats

Adjust text formats with:
Font and size pulldowns
 B, I, U buttons
 Font color pulldown
 Fill color pulldown
 Font style
 Left, center, right buttons

7/18/2015
Microsoft Excel 2000
12
Numeric formats
Don’t type in commas or dollar signs
– use Format/Cells/Number for
numeric format options
 Buttons for $ and , formats
 Decimal increase, decrease buttons
will round off, but don’t change
value

7/18/2015
Microsoft Excel 2000
13
Alignment, size, rotation






Defaults: text left justify, numbers right
justify
Change with alignment buttons
Format/Cells/Alignment for more
alignment options
Wrap, shrink to fit, merge checkboxes for
sizing problems
Rotate text with orientation box
Merge and center button useful for
headings
7/18/2015
Microsoft Excel 2000
14
Sizing cells
Drag rows and columns to proper
size by pulling borders
 Choose all rows or cells and drag as
one – it will evenly widen size
 D-click border will auto-fit box to
longest record

7/18/2015
Microsoft Excel 2000
15
Sheet formats





Format/Autoformat/Choose a style to
automatically format a sheet
Options button allows only certain aspects
to be chosen
Borders buttons to manipulate borders
Style painter button will copy styles – (1click for 1 time use, 2-click to leave it
on)
Paste special will allow you to copy
certain aspects
7/18/2015
Microsoft Excel 2000
16
Formulas and logical
functions
7/18/2015
Microsoft Excel 2000
17
Formulas
Always pick destination cell first
 Always click = to begin (except
autosum)
 Autosum (∑ button) will
automatically add a column of
figures
 If wrong, You can adjust by typing in
formula bar

7/18/2015
Microsoft Excel 2000
18
Copying formulas
Drag by lower right-hand
corner(cursor will be small black +
sign, not big white + sign)
 Relative referencing (i.e. - D9) – will
change as cells are added or deleted
 Absolute referencing ($D$9) –
specified cell only – will not refigure

7/18/2015
Microsoft Excel 2000
19
Math functions
4 main functions: + (add), (subtract), / (divide), * (multiply)
 Type it just like an algebraic formula
(e.g. – e3*f3 means to multiply the
value in cell e3 by the value in cell
f3)

7/18/2015
Microsoft Excel 2000
20
Math functions (con’t)

For more complex formulas, click
the = button, then pull down a
function in upper right
Sum
 Average
 Min
 Max
 Count

7/18/2015
Microsoft Excel 2000
21
Ordering



10 * 2 – 1 = 19 or 10?
Order of preference – which order excel
will calculate expressions
PEDMAS






Parentheses
Exponents
Division
Multiplication
Addition
Subtraction
7/18/2015
Microsoft Excel 2000
22
Date and time functions
“Now” function, “today” function to
capture time or day, choose format
after the fact in
Format/Cells/Number
 Dates/times represent mathematical
values
 Date due minus date delivered to
track lag time

7/18/2015
Microsoft Excel 2000
23
Logical functions
If statements – checks values and
returns text on false and true
statments
 Or statements - returns true if any
cell fits a condition
 And statements – returns true if all
cells fit a condition

7/18/2015
Microsoft Excel 2000
24
More functions

Financial functions



Randomizing




FV > Future value
PMT > Payment functions
RAND
Remember to set value, then cut and paste
SUMIF to count only certain values
COUNTBLANK to count blanks
7/18/2015
Microsoft Excel 2000
25
Previewing and printing
7/18/2015
Microsoft Excel 2000
26
Page setup



File/Page setup
Place to set up printing format – Use
“Print preview” to check
Page setup – 4 tabs




Page (portrait vs. landscape, scaling, scale to
fit)
Margins – set margins, center horz. And vert.)
Headers/footers – set standard or customized
footers and headers here)
Sheet (print all or part of book/sheet?, repeat
rows?, gridlines?)
7/18/2015
Microsoft Excel 2000
27
Setting and clearing print
areas

To print part of worksheet
L-click and drag area you want
 File/Print area/Set print area
 Dashes show print area

File/Print/Chose “Selection”
 Be sure and clear it when done

7/18/2015
Microsoft Excel 2000
28
Web page preview
File/Web page preview
 Opens in new window
 Tabs included
 HTML code (View/Page source)

7/18/2015
Microsoft Excel 2000
29
Charts
7/18/2015
Microsoft Excel 2000
30
Chart Wizard


Use chart wizard icon
Wizard leads you through decision
process




Choose chart type (press and hold for
preview)
Define data range
Set titles, axes, gridlines, legends, labels
New sheet vs. embedded
7/18/2015
Microsoft Excel 2000
31
Modifying charts
Click and drag to include more cells,
chart will change
 L-Click on specific elements to
choose them – R-click to get format
options – change fill colors, fonts,
gridlines, etc.
 Change cell numbers, chart will
change

7/18/2015
Microsoft Excel 2000
32
Inserting objects
Chart is an object
 You can add others (pictures, clip
art, logos, text boxes)
 Size (use sizing boxes) and move
(click and drag) them afterward

This is a textbox,
and an arrow
pointing to clipart.
7/18/2015
Microsoft Excel 2000
33
Drawing toolbar


Icon next to Chart Wizard, or R-click in
toolbars
Text box



Line and arrow buttons


Size it, start typing
Size and move when finished
Click and drag, use sizing boxes to manipulate
Multiple objects – group them before
printing (hold down shift key, select all
objects)
7/18/2015
Microsoft Excel 2000
34
Printing charts
If you only want to print chart, click
chart, then File/Print Preview
 To print whole page, click away from
the chart

7/18/2015
Microsoft Excel 2000
35
Sorting and filtering
7/18/2015
Microsoft Excel 2000
36
Sorting caveat



Filtering masks, doesn’t change
numbering
Sorting rearranges, changes numbering
BE CAREFUL – when sorting, click inside
column, NOT in column letter


it will strip column from data when sorting
When filtering, you can use column
7/18/2015
Microsoft Excel 2000
37
Sorting
Click anywhere within column
 Click ascending, descending order
buttons
 Data/Sort (for multiple sorts)

7/18/2015
Microsoft Excel 2000
38
Auto-filter





Select column letter
Data/Filter/Auto-filter
Click arrow, choose value, all records with
that value will be shown
Notice the row numbering changes – and
is in blue
If you don’t select column,
Data/Filter/Auto-filter, and you can filter
by multiple columns
7/18/2015
Microsoft Excel 2000
39
Restoring the List
Restore all or some
 All > Data/Filter/Autofilter again
 Some > choose column, arrow, then
all

7/18/2015
Microsoft Excel 2000
40
Custom filter
Two criteria filter
 Data/Filter/Autofilter/(Custom)
 1st agument, 1st value, 2nd
argument, 2nd value
 Wild cards (* and ?) can be used

7/18/2015
Microsoft Excel 2000
41
Subtotals
Choose cell within sheet
 Total, Subtotal

Choose Column to separate by
 Choose math function
 Choose column with values
 OK


Use levels on left to manipulate level
of detail
7/18/2015
Microsoft Excel 2000
42
Managing workbooks
7/18/2015
Microsoft Excel 2000
43
Changing zoom setting

Zoom window allows you to choose
percentage from 10-200%

Ctrl + mouse wheel does it too
Doesn’t change document, or print
size, just your view
 Zoom to any part of sheet by
selecting it, choosing selection in
zoom window

7/18/2015
Microsoft Excel 2000
44
Freezing and unfreezing
columns and rows





Easy way to freeze certain areas of long
spreadsheets
Click cell where you want the page to
“break”, then Window/Freeze pane
Window will freeze columns and rows
above and to the left of the cell you chose
Doesn’t change document, just your view
Window/Unfreeze pane to unfreeze
7/18/2015
Microsoft Excel 2000
45
Hiding and unhiding




For confidential data (salaries), or data
you don’t want to see
Hidden data doesn’t print – good way to
shrink size of large spreadsheets
Select column or row > R-click/Hide
To unhide, select columns or rows before
and after hidden one, R-click, unhide
7/18/2015
Microsoft Excel 2000
46
Inserting and deleting
sheets
Insert/Worksheet
 Will insert to left
 R-click on tab to delete

7/18/2015
Microsoft Excel 2000
47
Copying, renaming, moving
sheets
D-click tab to rename, or
Format/Sheet/Rename
 Click and drag tab to move
 To copy:

R-click on tab, choose “move or copy”
 Choose sheet, check “make a copy”
 Filename (2) will appear
 Click and drag + Ctrl will copy as well

7/18/2015
Microsoft Excel 2000
48
Linking workbooks
7/18/2015
Microsoft Excel 2000
49
Linking formulas
Used to create hierarchies of data
 3-D formulas will reference cells
from other sheets
 Good for summarizing large
amounts of data spread over several
sheets

7/18/2015
Microsoft Excel 2000
50
Summarizing data from
identical formats

Create blank “totals” sheet, identical to
ones you want to summarize







Choose destination cell
Click Autosum
Select beginning cell, first sheet
Shift + ending cell, last sheet
Hit Enter
Fill across (drag by lower right corner) to
apply to other columns
Fill down to apply to other rows
7/18/2015
Microsoft Excel 2000
51
Consolidating data
Will add, average, multiply multiple
cells from different sheets
 Select destination cell
 Data/Consolidate
 Choose function
 Select 1st cell, click add button,
select 2nd cell, etc., then OK
 Will not fill in formula, just a value

7/18/2015
Microsoft Excel 2000
52
Creating hyperlinks


Easy way to impress your boss!
Link to other documents, other places on
same document, web page




Type text heading, then R-click
Select “Create Hyperlink”
Type screen-tip (will show during mouseover)
Select file to link to:



Existing file or web page
Place in current document
Email address
7/18/2015
Microsoft Excel 2000
53
Workgroups
7/18/2015
Microsoft Excel 2000
54
Adding and removing
comments







R-click inside cell/Insert comments
Type in your comments, click outside cell
Triangle appears in upper right to show a
comment has been added
Mouseover or R-click to see comments
It will identify you (Tools/Options/General
to change login name), any others who
comment
R-click to edit, add comments
Will track a dialogue between users
7/18/2015
Microsoft Excel 2000
55
Protecting data
Tools/Protection/Worksheet or
Workbook
 Password is case-sensitive
 BE CAREFUL > No way to retrieve
password
 Remove protection >
Tools/Protection/Unprotect

7/18/2015
Microsoft Excel 2000
56
Shared workbooks







Generally stored on network drive
Tools/Share workbook/Check “Allow
changes”
[Shared] appears in title bar
Anyone at anytime can now make
changes, so track them
Tools/Track changes/Highlight changes
Changes appear in blue, triangle appears
in upper left
Screentip shows history of changes
7/18/2015
Microsoft Excel 2000
57