PRINT THIS 6 SLIDES PER PAGE
Microsoft Excel
Part II
1
Format
Format menu (main menu) Shortcut menu (right mouse click)
2
Wrapping Text in a Cell
Creates a multi-line cell Format menu | Cells | Alignment | Text control: Wrap text
3
Center Text Across Columns
Highlight destination (range of cells) Format menu| Cells| Alignment: Text Alignment: Horizontal: select Center Across Selection OR <-a-> Click on “Merge and Center” button on the Formatting toolbar
• If not see it, click on the “Toolbar Options” button at the end of the toolbar
4
Currency Format
Right mouse click | Format cells | Number tab: Currency
• Specify number of decimal places • Specify symbol (e.g., U.S. $)
OR buttons on the formatting toolbar Negative numbers represented by…
• • • •
negative sign -$300.00 parenthesis ($300.00) Color red $300.00 e.g. Custom type:
$#,##0.00_);($#,##0.00)
5
Format Painter
Copies formats (not values) to other cells
• Double-click the button to use over & over
Looks like a paint brush Copies formats
• Select source, click Format painter button, select destination
If not see it, click on “Toolbar Options” button at end of Standard toolbar
6
Other Formats
Number Percentage
% • Percent Style button • Right mouse click | Format cells | Number tab | Percentage: Decimal Places
Decimal places
• Increase/Decrease Decimal button on toolbar • Right mouse click | Format cells | Number tab | Number: Decimal Places
7
Adding & Removing Borders
Select cell(s) for border OR Format menu | Cells | Borders tab
• Select Line Style • Select Color • Select Border Style
Borders button on the toolbar
8
Applying Patterns and Color
Highlight cells Format menu | Cells | Patterns tab Select color and pattern
9
Activate a Toolbar
Click on View menu then Toolbars OR Right mouse click at toolbar level and select desired toolbar
• Example: add the drawing toolbar
10
Adding Comments to Worksheet
Click Text Box button on
• Drawing toolbar
Different from a Cell Comment
• Cell Comment - attached to single cell (presence denoted by small red triangle at the upper right hand corner) • Text Box - displayed on the worksheet on top of one or more cells and can be moved around on the worksheet
11
Add Arrow
Click on arrow button on drawing toolbar
Click and Drag line to desired point on spreadsheet Format menu | AutoShape OR Right mouse click|Format AutoShape
12
13
Chart
Types
• 2-D or 3D • Area, Bar, Column, Doughnut, Line, Pie, XY (Scatter), Radar, ...
Chart formats
• Several for each type
Chart wizard (later)
14
Elements of Chart
Title X-axis - category axis
• Includes title
Y-axis - value axis
• Includes title
Tick mark label
• Identifies categories, values, series in chart • Represent scale on an axis just like a ruler
Category names - labels on x-axis
15
Excel Chart Elements
Value Y-axis Y axis title
Sales by State and Model
Sales ($US)
300,000 200,000 100,000 Vermont Maine Territories
Chart Title
Star Box West
Tick Mark Category (X) axis names or labels
Category X-axis
Data
Data point - single value
• $200,000 sales
Data marker - graphic representing a data point (like a column or wedge representing a single value) Data series - group of related points Legend - identifies data series
17
More Excel Chart Elements
Star Data Series Legend
Sales by State and Model
Sales ($US)
300,000 200,000 100,000 Vermont Maine Territories Star Box West
Data Marker Data Point: single value ($200,000 sales)
Purpose of Charts
Pie chart - proportion of parts to whole
25%
$
Q1
Q2
Q3
Column or bar chart - Comparisons between the data represented by each column or bar
19
Creating a Chart
Use the chart wizard button (on the standard toolbar)
• Example later
OR Insert menu|Chart
20
Chart Wizard
Click Chart Wizard button on toolbar Click Pie Chart (3D) “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
21
Pie Chart
One data series Shown as percentages of parts to whole Explode wedges Show labels and percent
• Wizard: Data Labels tab (check Category name and percentage)
No legend
• Wizard: Legends tab (uncheck)
22
Pie Chart Demo
Concepts.xls Pie chart
• using oven types (star, etc.) and % of Total
23
Select Non-adjacent Cells
Highlight first range of cells
• Example: labels for chart
press CTRL key - highlight second range of cells
• Example: values in the cells
24
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
25
Pull out a Wedge/Cut Slice
Click on pie Click on a wedge/cut slice Drag wedge from center Now you have an exploded pie chart
26
Select a Pattern
Select a wedge Double-click on the selected wedge Select Patterns tab | Area : Fill Effects
• Select pattern
27
Create a Column Chart
Highlight range of data to be graphed Include row and column labels
• Column labels become x-axis labels • Row labels become legend
Click chart Wizard & Column Chart (3D)
• To show values Wizard’s Data Labels tab: show Value • Click on a series/column, then right mouse click, Format Data Series (or Point) | Options tab | Vary colors by point
28
Column Chart Demo
Concepts.xls Column chart
• using States and Totals
29
Revising the Charts
To format/revise chart: First, click entire chart to select it, then
• • • •
re-click Chart Wizard button again OR use the Chart menu OR double-click the chart OR right-mouse click
Nothing seems to work
• Start over using the Chart Wizard
30
Print: Page Setup/Print Preview
File menu | Page Setup Page tab
• Orientation: Portrait vs. Landscape • Scaling: Fit to: _ page(s) wide by _ tall
Margins tab
• Center on page (Horizontally & Vertically)
Sheet tab
• No gridlines (also: Tools|Options|View tab) • Row & column headings
31
Are > and < Opposites?
Negation/opposite of
• • • • “<” is “>=” “>” is “<=” “>=“ is “<“ etc.
Include “=” not just > or <, & vice versa
32
Are > and < Opposites?
Another example: which of the following two statements are equivalent to this one =IF(A1>=60, “Pass”, “Fail”)? A. =IF(A1<60, “Pass”, “Fail”) B. =IF(A1<60, “Fail”, “Pass”) Notice when the logical test (A1>=60) was negated or reversed to (A1<60); also True & False parts were reversed (“Pass” and “Fail” switched places)
33
Question
Which of the following statements are equivalent to this one (assume A1 or age is a whole number only, so we cannot have 20 yr 2 months olds, etc.) =IF(A1<21, “Underage”, “Legal”)? A. =IF(A1<21, “Underage”, “Legal”) B. =IF(A1>=21, “Legal”, “Underage”) C. =IF(A1>20, “Legal”, “Underage”) D. =IF(A1<=20, “Underage”, “Legal”) (multiple answers possible ????)
34
Miscellaneous
Moving by drag-and -drop Inserting picture into column/bar chart
• Select data marker (column) then go to the Insert menu | Picture | From File phone.jpg • may also stack pictures (right-mouse, Format data point| Patterns| Fill effects)
Indenting text within a cell
• Increase/Decrease Indent buttons • Format cells | Alignment tab | Text Alignment | Indent
35
Shortcut Keys
36
Common Keystrokes
Ctrl-C = copy Ctrl-V = paste Ctrl-X = cut Ctrl-Z = undo last action
Ctrl-Home = go to beginning Ctrl-End = go to the end
37
Working with Files
Select the first file you want in Windows Explorer Use Ctrl and/or Shift to select more than one file
• Ctrl = non-contiguous • Shift = contiguous
38
Switching Between Applications
Use Task Bar OR “Alt-Tab” to switch between open applications
• Keep holding down on Alt key • Then simultaneously press on the Tab key and only let the Tab go, or keep pressing on Tab till you switch to a desired application • Let go of both keys
39
More Shortcut Keys
Right-mouse click In any Office application
• Help menu
– Type “Microsoft Word Application Help” – Select “Print a list of shortcut keys”
• Follow instructions to print the list
• Type “shortcut keys”
40
Excel HW
Questions? 3D formula Formatting “Totals” row in table
• Border: double-line under row
Making up values
• Overhead percentage, etc.
Relative/absolute cell references
41