1 ca202 spreadsheet application setting up a workbook lecture # 2
Post on 22-Dec-2015
217 views
TRANSCRIPT
![Page 1: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/1.jpg)
1
CA202Spreadsheet Application
Setting up a Workbook
Lecture # 2
![Page 2: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/2.jpg)
2
Objectives
• Make workbooks easier to work with
• Navigation and Selection
• Make data easier to read
• Add graphic to read
![Page 3: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/3.jpg)
3
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
![Page 4: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/4.jpg)
4
Advance Navigation and Selection
• You know there are 16,777,216 cell in the worksheet to move on
![Page 5: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/5.jpg)
5
Knowing your place
• Excel gives you a highlight to row and column to know where you are
![Page 6: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/6.jpg)
6
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
![Page 7: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/7.jpg)
7
Keyboard Navigation (contd.)To go here key combination
Last filled cell incurrent block
Ctrl + arrow key or 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 Window
With scroll lock on, Press Home
Lower right corner of the Window
With scroll lock on, Press End
![Page 8: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/8.jpg)
8
Keyboard Navigation
To go here key combination
Next/Previous worksheet
Ctrl + Page Up/Down
Next/Previous workbook or window
Ctrl + Tab/Ctrl + Shift + Tab
Next/Previous pane F6 or Shift F6
![Page 9: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/9.jpg)
9
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
![Page 10: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/10.jpg)
10
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
![Page 11: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/11.jpg)
11
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
![Page 12: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/12.jpg)
12
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
![Page 13: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/13.jpg)
13
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
![Page 14: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/14.jpg)
14
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
![Page 15: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/15.jpg)
15
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
![Page 16: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/16.jpg)
16
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
![Page 17: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/17.jpg)
17
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
![Page 18: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/18.jpg)
18
Navigating Inside a selection
Enter Move cell by cell in a preset direction
Shift + Enter Move cell by cell in a preset direction
Tab Move Right
Shift + Tab Move Left
Ctrl+ Period Jump to each corner
![Page 19: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/19.jpg)
19
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
![Page 20: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/20.jpg)
20
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
![Page 21: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/21.jpg)
21
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
![Page 22: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/22.jpg)
22
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
![Page 23: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/23.jpg)
23
Entering Non Dynamic dates and times
ok
=NOW() function display date and time dynamically
Ctrl + ; to enter the current date as text
Ctrl + : to enter the current time as text
![Page 24: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/24.jpg)
24
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
![Page 25: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/25.jpg)
25
Making Data Easier to Read
• Alignment• Format Cells…
![Page 26: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/26.jpg)
26
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
![Page 27: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/27.jpg)
27
Add a Graphic to a Document
• Insert Picture
![Page 28: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/28.jpg)
28
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
![Page 29: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/29.jpg)
29
To Ungroup the Worksheet
• Ungroup Sheets, Use Right Click on any sheet Tab in the group, and choose ungroup sheet
![Page 30: 1 CA202 Spreadsheet Application Setting up a Workbook Lecture # 2](https://reader036.vdocuments.us/reader036/viewer/2022081519/56649d765503460f94a57f90/html5/thumbnails/30.jpg)
30
Chapter Key Points1. 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