Transcript Class 02 Excel CA202.ppt

CA202
Spreadsheet Application
Setting up a Workbook
Lecture # 2
1
Objectives
•
•
•
•
Make workbooks easier to work with
Navigation and Selection
Make data easier to read
Add graphic to read
2
Make Workbooks Easier to Work
With
• Change worksheet’s name, Right Click on
worksheet Tab and click Rename
• Change No. of default worksheets
– Tool  Option  General and change there
•
•
•
•
Insert  Column
Insert  Row
Insert  Cell
Delete a Cell, Row, or Column
– Edit  Delete…
• Hide or Unhide Column
3
Advance Navigation and
Selection
• You know there are 16,777,216 cell in the
worksheet to move on
4
Knowing your place
• Excel gives you a highlight to row and
column to know where you are
5
Keyboard Navigation (contd.)
To go here
key combination
Active cell
Ctrl + Backspace
Next unlocked cell
Tab
Beginning of current row Home
Last filled column
End, then Enter
Beginning of worksheet
Ctrl + Home
Last worksheet cell
Ctrl + End
6
Keyboard Navigation (contd.)
To go here
key combination
Last filled cell incurrent Ctrl + arrow key or
block
End, then Arrow
Up/Down one screen
Page up/Page Down
Left/Right one screen
Alt +Page up/Page
Down
Upper left corner of the With scroll lock on,
Window
Press Home
Lower right corner of
With scroll lock on,
the Window
Press End
7
Keyboard Navigation
To go here
key combination
Next/Previous
worksheet
Next/Previous
workbook or window
Next/Previous pane
Ctrl + Page Up/Down
Ctrl + Tab/
Ctrl + Shift + Tab
F6 or Shift F6
8
Using Workspace Files
• Use workspace file to open a group of
worksheets all at once
• To create a workspace file, Open the
workbooks you want to include
• Choose File  Save Workspace
• Excel save the file with the .xlw extension
• Use File  Open command to open
workspace file
9
Art of Natural Selection
• User must select the data before moving ,
copying, deleting, bolding, shading or changing
background etc
• Typically user use mouse to drag but when
selection is larger, dragging becomes a problem
10
Art of Natural Selection
• Following methods are used for
selection
– Shift magic
– With the GOTO box
– With the Name box
– While double clicking a cell border
11
Art of Natural selection
• Shift Magic
– Its hard to control by dragging mouse, use the Shift +
Click
– Click on left most corner of the selection
– Use scroll bar to go to the opposite bottom corner
– Press Shift and Click to select the range
12
Art of Natural selection
• With the GOTO box
– While sitting in cell, bring GOTO box by using
• Edit  GOTO or Ctrl + G or F5
– Enter a cell address in reference box, Hold down Shift
as you press Enter or OK
– The selected range now span from the original cell to
the new address you typed
Thu, sep 14 2006
13
Art of Natural selection
• With the Name box
– While sitting in a cell
•
•
•
•
Click the Name box on the left side of formula bar
Type the Address of a new Cell
Press Shift + Enter
Entire range will be selected
14
Art of Natural selection
• With Double Clicking a Cell border
– Double click on Cell border to jump to the edge of the
Current region
– Hold down Shift key while doing so
– This will help in selecting a larger area
15
Making Multiple selection
• Excel lets you make multiple selection or non
adjacent selection
• While you selected the first cell or range Hold
Ctrl key and select some more
• Now you can set the formatting, bold, italic,
underline etc
16
Selecting from with in a dialog
box
• Dialog box field that accept range reference are
called range selection boxes
• You can type the range into a range selection
box
• OR
• Enter the range by dragging the mouse on
worksheet
17
Navigating Inside a selection
Enter
Shift + Enter
Tab
Shift + Tab
Ctrl+ Period
Move cell by cell in a preset
direction
Move cell by cell in a preset
direction
Move Right
Move Left
Jump to each corner
18
A table of selection Shortcuts
• Edit  GOTO has many of these features
Ctrl + A
Select entire worksheet
Ctrl +Space
Whole Column
Shift + Space
Whole row
Shift + Cursor
Create or extend selection
Ctrl + *
Current region
Ctrl + shift + Up
Filled cell in current Column up
Ctrl + shift + Dn
Filled cell in current Column Dn
19
A table of selection Shortcuts
Ctrl + shift + Left
Filled cell in current Column left
Ctrl+ shift + right
Filled cell in current Column Right
End, shift +arrow
Move to the last filled in the current
cells column or row
With Scroll Lock on
Shift + Home
Upper left corner of the window
Shift + End
Lower right corner of the window
20
Detail of Data Entry
• Getting it right at the first time
– If you type ¼, Excel sees the slash and converts it to
4-Jan, but you want it 0.25 not the date
– Quick fix to this problem is precede fraction with a 0
(zero). Type 0 ¼ and excel will place ¼ in cell and
display 0.25 in the formula bar
– For zip code 64404 just use ‘64404
– Enter carriage return in cell press Alt + Enter where
you want a line break. This automatically turn on the
wrap text format
21
Copying Data from neighboring Cells
Ctrl + D To copy the content and formatting of
the cell above
Ctrl + R To copy the content and formatting
directly to the left
Ctrl + ‘
Copy only formula from the cell
directly above
Ctrl + “ Copy only resulting value from the cell
above
22
Entering Non Dynamic dates and times
=NOW() function display date and time
dynamically
Ctrl + ;
to enter the current date as text
Ctrl + :
to enter the current time as text
ok
23
Confining Data Entry to a Range
• If you know that data belong to a specific range
of cell
–
–
–
–
Select the whole range
Enter Data and press Enter
Excel respect the range boundaries
When you reach at the bottom, pressing Enter the
active cell pops up to the top of the next row
– Similarly when you are at the right bottom, pressing
Enter will take you to left top
24
Making Data Easier to Read
• Alignment
• Format  Cells…
25
Making Data Easier to Read
• Window  Freeze Pane will freeze the
cell from moving up/down or right/left
• Window  Unfreeze Pane will remove the
freeze pane
• Window  Split will split the excel
worksheet, easy to see top and bottom at
the same time
• Window  Remove Split will remove the
split window
26
Add a Graphic to a Document
• Insert  Picture
27
Entering Common Data in Group of
Worksheet
• Select Sheet by using Shift or Ctrl or Right Click
shortcut menu
• When Sheets are grouped, Start typing
• What you type in one sheet will be available in
all Selected Sheet
28
To Ungroup the Worksheet
• Ungroup Sheets, Use Right Click on any sheet
Tab in the group, and choose ungroup sheet
29
Chapter Key Points
1. You can control how many worksheets appear in new
workbooks you create. If you always use workbooks where each
worksheet represents a month of the year, change the default
number of worksheets to 12!
2. Making sure your data is easily readable is one of the best
things you can do for your colleagues. Be sure your worksheet
columns and rows are roomy enough to accommodate your
data.
3. Remember that you can add or delete individual cells from a
worksheet. Rather than go through a lengthy cut-and-paste
routine when you forgot to type a cell value, just add a cell
where you need it.
4. If you add a graphic to your worksheet, you can change the
graphic’s size and appearance using the Format Picture dialog
box.
5. You can Navigate and made selections in different ways
30