Transcript EXCEL INTERMEDIATE - Algonquin College

EXCEL INTERMEDIATE
1
WORKSHEETS
• Worksheet Tabs
–
–
–
–
Rename by double clicking
Can be moved by click and drag
Change colour by right click and choose Tab Color
Grouping worksheets by clicking ctrl and tab
• Allows formatting and formula insertion in
multiple sheets simultaneously
– Refer to formulas on another sheet by beginning to
type formula on current sheet, then click on sheet
tab, and select cell on new sheet
• =B5-Sheet2!C3
• =J27*’Quarter1’F17
2
CELL FORMATTING
• Clear Cells
– Home tab, Edit group,
Clear button
• Text Wrapping
– Right click, Format Cells,
Alignment tab, Wrap text box
• Text Rotation
– Right click, Format Cells,
Alignment tab, drag “Text”
3
FUNCTIONS
•
•
•
•
•
•
•
•
•
Autosum
=today()
=now()
=min()
=max()
=median()
=Average()
=Round()
=Countif()
• =pmt()
• =if()
• Dragging formulas
4
IF FUNCTION
• =IF(logical_test,value_if_true,value_if_false)
• Checks whether a condition is met, and
returns one value if TRUE, and another
value if FALSE
5
Cell References
• Relative cell references
– Default
– Automatically change cell references
relative to which column/row you copy it to
• Absolute cell references
– Absolutely will not change when you copy
formula
• Mixed cell references
– Either ROW or COLUMN will not change
depending which one is preceded by a 6
$ dollar sign
MIXED CELL REFERENCE
• A mixed cell reference contains only one
dollar sign:
=$A1
– the column part of the reference (A)
is absolute and the row part (1) is
relative.
=A$1
– the column part of the reference is
relative and the row part is absolute.
7
“PROJECTED 1st QUARTER”:
Absolute Cell Reference
• A formula to calculate:
“PROJECTED 1st Quarter Sales”
– Because sales are PROJECTED to
increase, the projected value should
be greater than the current data
=B7+(B7*$B$17)
• FV
8
ERROR MESSAGES
• #NAME?
– i.e. =DIV(C2,B5)
• (no such FUNCTION name as “DIV”)
• #VALUE!
– i.e. =SUM(B5,”H3”)
• Cell reference should not be in
“quotation marks”
• #DIV/0!
– If the value in a cell is “0” (no division
by zero)
9
VIEW/PRINT FORMULAS
 CTRL key +
 (to the left of the #1 key)
o This key combination will toggle to viewing
formulas--ON or OFF
10
CHARTS
• 3-D pie chart
• Resize
• CHART TOOLS DESIGN tab >
CHART STYLES group > CHART
STYLES gallery
• CHART TOOLS DESIGN tab >
CHART LAYOUT group > CHART
11
LAYOUT gallery
Charts cont.
• Change the 3D rotation of a chart:
– Select the chart
– FORMAT tab > CURRENT
SELECTION group, click on: FORMAT
SELECTION
– Choose: 3-D ROTATION
-change the “X” &
“Y” rotations
boxes
accordingly
12
Charts cont.
• Change font of Category X (horizontal) axis
– And Y (vertical) axis
• Change other options
of X & Y axes:
• Select specific axis
• Right-mouse click
• Select: FORMAT AXIS
13
Charts cont.
• If you change the “MAJOR” axis to “fixed”, and
then set a specific value:
– the value will be the “bottom” value, and other
values will be Incremented by that same amt.
i.e. 75,000:
Values increase
by 75,000
14
Adding a title to an axis:
– Select the axis
– Go to the LAYOUT tab > click on AXIS
drop-down arrow
• Select either
Horizontal or
Vertical & then
the location of
The title
TITLES
15
Format data series
• Right-mouse click on: data series
• Select: FORMAT DATA SERIES
• Change desires options i.e. FILL
16
LINE CHART
• SWITCH LEGEND INFO TO HORIZONTAL AXIS
• Select chart > DESIGN tab > DATA group
• Select: SWITCH ROW/COLUMN
• CHANGE A CHART TYPE FOR A SERIES:
• Right-mouse click on a specific
series > Change Series Type Chart
17
INSERTING AND FORMATTING A GRAPHIC SHAPE
• LAYOUT tab > INSERT group >
select: SHAPES
18
ROWS and COLUMNS
• Insert
– Right click on a cell and choose Insert
form pop up menu
– Note that functions and formulas update
automatically
• Freeze Panes
– VIEW tab > WINDOW group > FREEZE
PANES
19
ROWS and COLUMNS
• select the row below where you want the row to
be frozen
• To freeze columns, select the column to the
right of where you want the column to be frozen
20
PASTE LINK
• Insert data saved to the clipboard so
that the inserted data will change if
the ORIGINAL data changes.
• Warning - for this to work the original and
destination files must be kept together
•
•
•
•
HOME tab>
CLIPBOARD group>
PASTE drop-down arrow>
PASTE LINK
21
FREE “TIP OF THE WEEK”
• Free anti-virus software from
Microsoft – Microsoft Security
Essentials
• http://www.microsoft.com/Security_essentials/
22