Transcript Document

MICROSOFT EXCEL
Znanatej Panga
[email protected]
January 20, 2015
MYITLAB!
• MYITLAB LESSON A IS DUE JANUARY 27th by 11:59pm!
• DO NOT WAIT TO START ON MYITLAB!
2
MYITLAB ISSUES
• If you are experiencing issues registering, and you have
correctly followed the registration steps:
– Contact Pearson Technical Support (24/7)
– (855) 875-1797
– Or use this link for other options:
http://cs101.wvu.edu/resources/myitlab/technical-support/
3
ATTENDANCE SYSTEM
• It is very important to always wait until I say to sign in and
not do it before.
• You must do this every day you come to class.
• You must be here on time and stay the whole time.
• Never try to do this outside of class or do anything the
syllabus describes as not being allowed.
4CS101 Excel Chapter 1
ATTENDANCE SIGN-IN PROCEDURE
Let’s do this…
1. Go to the CS101 web site: http://cs101.wvu.edu
2. Click Course Tools & Record Attendance
3. Enter your MyID username
4. Enter your MyID Password
5. Click on all the human faces in image
6. Click the Record Attendance button
• If it did not work, E-mail me !
• Call 293-4444 after class to have them help you reset your MyID
5CS101 Excel Chapter 1
EXCEL WINDOW ELEMENTS
Quick Access Toolbar
Insert Function
Name Box
Select All
Formula Bar
Column Heading
Row Heading
Status Bar
View/Zoom Controls
Insert new worksheet
CS101
6
Excel Chapter 1
CELL NAVIGATION
Keystroke
Use
Up Arrow
Up one cell in the same column.
Down Arrow / Enter
Down one cell in the same column.
Left Arrow
Left one cell in the same row.
Right Arrow / Tab
Right one cell in the same row.
Page Up
Move the active cell up one screen.
Page Down
Move the active cell down one screen.
Home
Move the active cell to column A of
current row.
Ctrl+Home
Make cell A1 the active cell.
Ctrl+End
Rightmost lowermost cell of active
row/columns
F5 / Control+G
Display the Go to dialog box
7
DATA TYPES
• Text – can’t be used for calculations
Ex: Bob, banjo, 555-1234, 154-23-9304, m#5l*fish
Which side does text auto align to?
Long text: Alt+Enter to begin a
new line
• Values- represent a quantity or a measurable amount
Ex: 0.5, 123,456,
Which side do values auto align to?
DATA TYPES – CONT’D
• Dates- (Not those dates) can be used in calculations
Ex: 9/15/2012; 9/15/12; September 15, 2012
Current Date: Ctrl+;
• Times- can also be used in calculations
Ex: 1:30pm, 13:30
Current Time: Ctrl+Shift+;
ENTERING DATA INTO A WORKSHEET
• Data types can be:
– Numeric (numbers only)
– Alphabetic (a single letter or strings)
– Alphanumeric (letters and numbers together)
– Dates (calendar)
• In addition to being a specific type, data can be:
– A constant (value never changes)
– A formula (arithmetic operations)
– A function (simple or complex data operation)
10CS101 Excel Chapter 1
ENTERING DATA, AUTOFILL - EXAMPLE
• In cell B1 enter January
• We now need to fill cells C1 to M1 with the months
• We can either type the months in or, click on B1 then move your mouse to the
bottom right corner of cell B1 until you see a + symbol then drag to the right
• This is commonly called Autofill
• Now if you notice, some months are cramped together. We can fix this in a couple
of ways
• Select B1 through M1 (you can do this by selecting B1, then click the left mouse
button and move your mouse until you get to cell M1).
• Then under Cells select Format and then Autofit Column Width
• Or we can simply double click between the column letters to autofit the contents
11CS101 Excel Chapter 1
AUTO FILL …..
• Copy Formulas using Auto Fill
• Complete Sequences like months, weekdays, quarters
etc.,
• For numeric sequences, you need to specify the first two
values in the sequence.
• Auto Fill options: Copy Cells, Fill Series, Fill Formatting
Only, Fill Without Formatting, Flash Fill.
CS101
12
Excel Chapter 1
ENTERING DATA
•
•
•
•
Text
Values
Date – Excel auto formats the date
Formulas
ENTERING DATA CTD…
• Edit the data – F2
• Clear the data – Delete
• Line break – Alt + Enter
ENTERING DATA CTD..
 Start Excel. Save the new workbook as
lastname_firstname.xlsx
 Type OK Office Systems Pricing Information in cell
A1, and press Enter.
 Enter the following values in the corresponding cells:
A4 Product
A5 Computer System
A6 Color Laser Printer
A7
Filling Cabinet
A8
Desk Chair
A9
Computer Desk
A10 28” Monitor
ENTERING DATA CTD..
C4
D4
E4
F4
G4
Markup Rate
Retail Price
Percent Off
Sale Price
Profit Margin
ENTERING FORMULAS
• A formula is a mathematical expression that calculates a
value.
• In Excel, formulas always begin with an equal sign (=).
• A formula can consist of one or more arithmetic operators.
• Remember to follow the order of precedence rules.
• Using Semi-Selection to create a formula
19CS101 Excel Chapter 1
ARITHMETIC OPERATIONS
•
•
•
•
•
•
+ Addition (example =C1+C2)
- Subtraction (example =C1-C2)
* Multiplication (example = C1*C2)
/ Division (example =C1/C2)
^ Exponent (example =C1^C2)
( ) operations in Parenthesis (example =C1+(C2*C3))
20CS101 Excel Chapter 1
MORE MATH - ORDER OF PRECEDENCE
•
•
•
•
•
Operations in Parenthesis done first
Exponents done second
Multiplication/Division done third
Addition/Subtraction done last
These are performed from LEFT to RIGHT.
21CS101 Excel Chapter 1
ORDER OF PRECEDENCE
• Go to Sheet 2
• In cell A1 type  =3+7/2
– instead of 5, we get 6.5
– 7 was divided first by 2 and then division
result added to 3
• Now delete the contents of A1
– Type in =(3+7)/2
– The addition here is made a higher priority
22CS101 Excel Chapter 1
CONTD…
• Different ways to display cell formula
- Ctrl + `(BackQuote/acute/grave)
- Click Show Formulas in Formula Auditing group on the
FORMULAS tab
CS101
23
Excel Chapter 1
FORMULA
 Click cell D5, the cell where you will enter the
formula to calculate the retail selling price of
the first item.
 Type =B5*(1+C5) and view the formula and
colored cell borders on the screen.
 Click Enter to the left of Formula Bar and
view the formula.
 Similarly click F5, enter formula =D5-D5*E5
and click G5, enter formula =(F5-B5)/F5.
ENTER THE FORMULA AND RELATED
CELLS HIGHLIGHTED
DRAG AND DROP TO AUTO FILL OTHER
CELLS IN THE COLUMN
ADD OTHER FORMULAS
WORKBOOK AND WORKSHEET
• Insert a Worksheet
- New Sheet button
- Home->Insert->Insert sheet
- Right click on sheet tab
- Shift+F11
• Delete a Worksheet
- Right click on sheet tab
- Home->Delete->Delete Sheet
CS101
29
Excel Chapter 1
WORKSHEET CONTD..
• Rename a Worksheet
- Right Click the sheet tab
- Double Click the sheet tab
- Home->Format->Rename Sheet
• Change Worksheet Tab Color
- Right click the sheet tab
- Home->Format->Tab Color
CS101
30
Excel Chapter 1
WORKSHEET CONTD..
• Move a worksheet
- Drag to desired location
- Home->Format->Move or Copy
• Copy a worksheet
- press, hold Ctrl key and drag to desired location
- click on Create a copy check box available on
Home->Format->Move or Copy dialog box
You can Move or Copy Worksheet within the current workbook or
to a different workbook
CS101
31
Excel Chapter 1
ADDING NEW SHEET
RENAMING A SHEET
ADD COLOR
COLUMNS AND ROWS
• Insert Column or Row
- Right click
- Home->Insert->Insert sheet Columns/Rows
Columns will be inserted to the left of the selected current
column and new rows will be inserted above the active row.
• Insert a cell
- Home->Insert->Insert cells…
CS101
35
Excel Chapter 1
COLUMNS AND ROWS CONTD..
• Delete Columns and Rows
- Home->Delete
- Right click on row, column or cell and select delete
option
• Adjust Column Width
• Adjust Row Height
• Hide and unhide Columns, rows and cells
CS101
36
Excel Chapter 1
INSERT
DELETE
HIDE /
UNHIDE
ROW HEIGHT / COLUMN WIDTH
DATA
•
•
•
•
•
Selection
Move
Copy
Paste
Paste special
SELECTING RANGES
44
• Move a range:
– Select the range
– Use the Cut command (Ctrl+X)
– Make sure the destination range has enough
empty cells
– Use the Paste command (Ctrl+V)
• Copy and paste a range:
– Select the range
– Use the Copy command (Ctrl+C)
– Make sure the destination range has enough
empty cells
– Use the Paste command (Ctrl+V)
45
SELECTION - COPY
PASTE
SPECIAL
FORMATTING CELLS
Sometimes you might need to format the appearance of a cell,
you can do this by right clicking and selecting Format Cell or
doing the same thing from the toolbar.
There are 13 different formatting options: General, Number,
Currency, Accounting, Comma, Date, Time, Percentage, Fraction,
Scientific, Text, Special, Custom.
Remember how to get to formatting cells, you will use this feature
a lot.
48CS101 Excel Chapter 1
FORMATTING DATA
•
•
•
•
•
•
•
Merge and center labels
Applying alignment and font options
Wrap text
Apply borders
Fill colors
Applying number formats
Increase/decrease decimal places
FONT OPTIONS IN HOME RIBBON
• Font type
• Font size
• Increase/decrease font size
•
•
•
•
•
•
Bold ( Ctrl+B )
Italic ( Ctrl+I )
Underline ( Ctrl+U )
Border
Fill color ( fills the cell background with selected color)
Font Color
50
ALIGNMENT OPTIONS IN HOME RIBBON
• Top/Middle/Bottom Align
• Orientation
• Wrap text ( makes text visible on multiple lines within
cell)
• Left/Center/Right Align
• Increase/Decrease Indent
• Merge cells
51
NUMBER OPTIONS IN HOME RIBBON
•
•
•
•
•
Number Format
Accounting number format
Percent Style (Ctrl+Shift+%)
Comma Style (Displays with a thousands separator)
Increase / Decrease decimal value.
52
STYLE OPTIONS IN HOME
RIBBON
• Format as Table - Formats a range of cells and converts
into table by choosing a pre-defined table style.
• Cell Styles - Formats a cell by choosing from pre-defined
styles. Can also define own cell style.
53
MERGE AND CENTER
ALIGNMENT AND FONT
WRAP DATA
APPLY BORDERS
APPLY BORDERS
FILL COLORS
NUMBER
FORMAT
NUMBER
FORMAT
CONDITIONAL FORMATTING
• Conditional formatting is used to apply specific formats
automatically to cells that contain particular values or
content
• Use to highlight interesting cells or ranges of cells,
emphasize unusual or duplicate values, or visualize data
7/18/2015
62
CONDITIONAL FORMATTING
• Conditional formatting options include:
– Highlight Cell Rules
– Top/Bottom Rules
– Data Bars
– Color Scales
– Icon Sets
7/18/2015
63
DATA BARS
• Gradient colored bars that help you visualize the value of a cell
relative to other cells
– Used when identifying high and low values in large amounts of
data
– Most useful when working with a big range of values
– More effective with wider columns
7/18/2015
64
COLOR SCALES
• Formats cells with different colors based on the relative value of a
cell compared to other adjacent cells
• Using a two-color scale, the shade of the color represents higher
or lower values
• Using a three-color scale, the shade of the color represents the
high, middle, or lower values
7/18/2015
65
ICON SETS
• Little graphics or symbols that display in cells
• Used to classify data into three to five categories, based on the
contents of the cells
• Each icon represents a range of values
• The icons are effective when you want to annotate or present data
that is quickly readable and understandable
7/18/2015
66
RULES
• Highlight cells rules
• Top-bottom rules
• Create New rule
• Lecture Notes -> Project – Gradebook
Problem
67
• To apply a conditional format:
– Select the cells
– Click Conditional Formatting
– Select the specific conditional formatting style
you want to apply
• To remove conditional formatting from an
entire sheet:
– click Conditional Formatting
– select Clear Rules
– select Entire Sheet
7/18/2015
68
PAGE OPTIONS
•
•
•
•
•
•
•
•
Margins
Orientation
Size
Print area
Breaks
Background
Print titles
Headers/footers
PAGE OPTIONS
SET PRINT AREA
HEADERS / FOOTERS