Transcript excel - ShoWorks

Microsoft Excel
Presented by ShoWorks Fair Software and Online Entries
www.fairsoftware.com
What is Excel?


A computerized “spreadsheet”
(worksheets)
Spreadsheets are most often used for







Cash flow analysis
Budgeting
Decision making (what-if analysis)
Cost estimating
Inventory management (limited)
Financial reporting (limited)
An Excel file is a workbook that contains
multiple worksheets
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Formula
bar
sheet tab
active cell
(C3: column C, row 3)
Worksheet Window

Headings



intersection of a row and column
Cell reference


A, B, C
1, 2, 3
Cell


Columns (letters at the top)
Rows (numbers along the side)
Example: A1
Active cell

cell you currently working on

Range

cell or rectangular block of cells designated with the colon
 Example: A1:A5
says “cells A1 through A5”
Sheet tabs at the bottom for “sheets” (pages) of workbook

Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Cell Contents

Values



Numbers, dates, text
Text is left aligned, others are right aligned
Formulas


math
Begin with =
Examples include
* Multiply
/ divide
^ power



Contain numbers or cell references
Result shows in cell
Formula bar shows formula
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Example Formulas

Add cells:
=A1+A2
=B14+F23
=A1+A2+A3+A4+A5

Multiply cells:
=A1*A2
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Functions


A special prewritten formula that provides a shortcut for
commonly used calculations
Each function has a syntax


Syntax specifies order of typing the parts, where to put
commas, punctuation, etc.
Determines order of arguments or values that Excel must
use in the calculation
=A1+A2+A3+A4+A5
=(A1+A2)/2
=MIN(A1:A3)
=MAX(A1:A3)
can be easier written as
can be easier written as
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
=SUM(A1:A5)
=AVERAGE(A1:A2)
Mike’s favorite functions








SUM (sums a range of cells)
AVERAGE (average of a range of cells)
TODAY (shows the date/time as of now)
IF (evaluates a condition and acts accordingly)
MIN (returns the minimum value in a range of cells)
MAX (returns the maximum value in a range of cells)
IRR (financial - internal Rate of Return)
NPV (financial - the Net Present Value of a range of
income)
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
IF Function

Makes a decision
Format:

=IF(condition, what to do if true, what to do if false)

Put quotes around text argument

=IF(A1>=60, “Pass”, “Fail”)
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Today Function

Shows today’s date
=TODAY()


No arguments
To format:


Format menu >>Cells>>Number tab: Category: Date,
mmm-yy==>”Mar-98”
OR right-mouse click, then Format cells
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Relative Reference


Cell reference is based on its location relative to
the cell containing the formula
Cell references that change when copied are
called relative cell references
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Relative Reference
(Double-click on this table to invoke Excel, then double-click
on the cell that you want to see the formula in)
1
2
3
4
A
B
Q1
C
Q2
40
50
60
60
70
70
Total
100
120
130
Notice “A2+B2” changes to “A3+B3” to “A4+B4”
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Absolute Reference



Is a cell reference in a formula that does not change
when copied into another cell
Fixed location on spreadsheet
Insert $ before the column and/or row of the cell
reference


=A1/$A$4
F4 Key for changing the type of cell references
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Absolute Reference
(Double-click on this table to invoke Excel, then double-click
on the cell that you want to see the formula in)
A
B
Q1
Q2
1
40
60
2
3
50
70
4
60
70
5
6 Predicited Q3
C
D
Q3
63
73.5
73.5
Total
100
120
130
5%
Notice “B2*(1+C$6)” changes to “B3*(1+C$6)” to “B4*(1+C$6)”
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Changing Column Width



Double-click dividing line for autofit to make column
as wide as longest label or number in column
Drag column heading dividing line
Select column heading(s), go to Format menu>>
Column >> Width or Autofit Selection


Shift-click for adjacent columns
Ctrl-click for non-adjacent columns
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Headers and Footers



File menu>>Page setup>>Header/Footer
Printed on top margin (header) or bottom margin
(footer) of every page
By default:





Worksheet name header
Page number footer
Must do for every worksheet
Custom header/footer
Page number, date, time, filename tools
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Display Formulas


Tools menu>>Options>>View tab>>Formulas
Short-cut key
Ctrl ~ (toggle switch--on/off)
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Comment




Text attached to a cell
Small red triangle shows in upper right hand corner.
When mouse is moved over the cell, a yellow box
appears with the comment in it.
Advantage: Provides documentation that not every
user needs to see
Insert >>Comment

OR right-mouse click, Insert Comment
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Center Text Across Columns


Highlight destination (range of cells)
Format menu>>Cells>>Alignment: Text Alignment:
Horizontal: select Center Across Selection
OR
Click on “Merge and Center” button on the Formatting
toolbar
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Charts

Types



2-D or 3D
Area, Bar, Column, Doughnut, Line, Pie, XY (Scatter),
Radar, ...
Chart formats

Several for each type
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Purpose of Charts

Pie chart


Column or bar chart


Magnitude of change over time
XY


Trends/changes over time
Area


Comparisons between the data represented by each
column or bar
Line chart


Proportion of parts to whole
Relationship between sets of (x,y) data points
Radar

Changes in data relative to center point
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Chart Wizard


Click Chart Wizard button on toolbar
Click Chart Type
“Press and hold to view sample” button

Data labels tab
Show label and percent for pie chart


Legends tab (show legend or not)
Wizard still works after chart is created: Select chart,
then click on Wizard button to format further
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Selecting Non-adjacent Cells

Highlight the first cells (or range of cells)
Example: labels for chart
Press CTRL key
Then highlight second range of cells
Example: values in the cells
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Embedded Chart or Object

Charts placed in the same worksheet as the data


The other option is to place the chart on a separate
worksheet
Click chart to select

Selection or sizing handles appear


Small black squares that appear on the boundaries of the chart
Double-click to activate
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software
Developing Worksheets

Plan - generally first







Determine purpose
Build (enter data and formulas)
Test worksheet
Correct errors
Document worksheet
Improve appearance
Save and print
Copyright 2007 Gladstone Inc. Sponsored by
ShoWorks software