OFFICE AUTOMATION – I EXCEL - BASICS 30. PRINT SETTINGS AND PRINTING This lesson will teach you all about printing and settings for printing. This will help you to setup your workbook/worksheet for printing.
30.0 Objectives On completion of this lesson, you will be able to: • • •
Set the workbook for printing Setup a worksheet for printing of data Preview and Check before actually printing the data
30.1 Introduction In the previous lessons you have learnt how to make a workbook, enter data, present that data in the form of a Table/List and make charts and graphs. This lesson will discuss the various print settings and printing options.
30.2 Copy and Paste Special After you have copied the data range, “Paste Special”… plays a very important role. Copy the data and then choose “Paste Special” option from the Edit menu. “Paste Special” dialogue box appears as follows:
1
30.2.1 Paste “Paste Special” option provides you with many options for pasting your data which are listed below:
30.2.2 Operations The various Paste Special “Operations” options are listed below:
Transpose is the way of converting data in to rows if it exists in columns and vice versa.
2
30.3 Concept of Cell and Sheet Reference Based on the cell address the cell/sheet reference are of two types. 30.3.1 Relative Reference Relative reference is a type of cell reference where the Column name and/or Row number changes when a formula is copied and pasted from one cell to another. Example of Relative Reference When formula from cell C2 is copied and pasted on cells C3:C5 then the formula A2+B2 changes to A3+B3, A4+B4 and A5+B5 taking the relative reference from cell C2. 30.3.2 Absolute Reference Absolute Reference is a type of cell reference in which the cell address does not change. Hence values remain unchanged when you copy the formula from one cell to another cell and the cell address also remains the same. This is represented by a $ sign. The application of $ sign restricts the changes in the cell address and the values in the cell, while copying the formula from one cell to another. $ sign before a column name restricts the change of column name. $ sign before a row number restricts the change of row number . For example: $A1 means column name “A” is constant; C$1 means row number “1” is constant. $B$1 means row number “1” and column name “B” is constant. Example of Absolute Reference Consider a case where you want to add 5 to every number available in column A. One way to do this is to type 5 in every cell of column B against the values in column A. Another way is to use Absolute reference.
3
Type the formula in cell C2 as shown in the figure, and copy the formula and then paste the formula in all the cells of C column. When formula from cell C2 is copied and pasted on cells C3:C5 then A2+$B$2 is changed to A3+$B$2, A4+$B$2 and A5+$B$2 in this case the row numbers are changing for column “A” but not for column “B”, ultimately 5 which is value of the cell B2 is being added in all column “A” values. Sheet Reference also works on the same pattern of cell reference. The only difference is in the above example the cells were on the same sheet, but in sheet reference these cells will be on other sheets of either same workbook or of another workbook. Sheet 1 of Book 1 has this data. A1 cell has the value as 10.
Now on Sheet 2 type “=” sign and click on Sheet1 and click on the cell A1. Figure here shows the formula on Sheet2 for the reference of value from Sheet1. This is the formula of linking Sheet 1 with Sheet 2, here the result is 10. This is the example of referencing from same file. Now let us see the example to link 2 excel files. In this case Book1 file has some values in Sheet1.
• • • •
Now In Book2 file on Sheet1 in cell A1 type “=” sign and Click on the file Book1 and Then click on Sheet1 and Then click on Cell A1 In the Book2 file you are able to setup a link/reference.
4
Here the data is lying in Book1 and link has been setup with Book2. In the cell reference case if data is changed in main file the linked file also changes automatically.
30.4 Print Settings and Printing Print Settings and Printing of Worksheets and Charts 30.4.1 Print Settings and Printing For printing any workbook, first of all set the area to be printed from the workbook. Set the page size to print the data, setup the number of copies, and the fashion in which they are required (Collate) for final printing. 30.4.2 Print Area Before printing a file, ensure that you have completed your work on the worksheet. Select all data in the sheet you want to print, then from “File” menu select “Print Area”. Choose “Set Print Area”. The whole area will have a dotted black color boundary line, separated in the pages, like Letter, A4, A3 etc. based on the setup of the printer and the file. To clear print area choose clear print area option. From View menu select “Page Break Preview”. Click “OK” here.
You will see all data you want to print surrounded by thick blue color lines dividing the data into pages. You can adjust the number of pages by dragging the blue color lines. For more than one page MS Excel shows “Page 1” and “Page 2” at the background of excel sheet as a water mark.
5
In this figure on one excel worksheet, four pages are shown which will be printed when a print command is given. These page numbers can be increased or decreased based upon the requirement by moving the dotted lines. 6
30.4.3 Printing From “File” menu select “Print” option.
“Print” dialog box appears.
30.4.4 Print Range Options Selecting “All” will print all the worksheets. Type page numbers in “From” Box and “To” Box to start printing the required page(s).
7
30.4.5 Print what options “Selection” option is chosen when you want to select the data before selecting the “Print” option from “File” menu. “Entire workbook” option is chosen when you want to print all worksheets of the excel file. “Active Sheet(s)” option will select the active work sheet as shown: 30.4.6 Copies options If one worksheet has 3 pages and you want to print 3 copies of this, then you can do one of the following: In the “Number of copies” box enter 3. In this case each page will be printed 3 times and you will get 9 pages - 1,1,1, 2,2,2, 3,3,3. By checking the box “Collate” , 3 sets will be printed as 1,2,3, 1,2,3, 1,2,3. This option is useful for large number of pages to print.
30.5 Print Preview Click on “Print Preview” button. The worksheet will be displayed on the screen as shown:
8
30.5.1 Preview: Margins Click on “Margins” button. The worksheet will be displayed with grey color borders on the page.
You can select “Zoom” option to zoom in or zoom out on the sheet.
Click on the “Setup” button for Page Setup, Margins, Header and Footer. These options are the same as explained in Unit IV of MS Word. 30.5.2 Preview: Setup Click on the “Setup” button. The “Page Setup” dialog box appears.
9
30.5.3 Preview: Setup: Page Tab Orientation options
Scaling options “Adjust to:” option is used to define percentage size of the data to be printed.
“Fit to:” option is used for printing the data on one page or more pages as per the requirement.
“Paper Size” option is used to select the size of the paper on which the data to be printed. For example “Letter”, “A4”, “A3” etc “Print quality:” option depends on the type of printer used for the purpose of printing. “First page number:" Auto option means excel will start numbering on its own. You can also give your own numbering pattern if you desire so. 10
30.5.4 Preview: Setup: Margins Tab Use “Margins” settings as per your requirement. You can set Left, Right, Top and Bottom margins here in this dialog box.
“Center on page” options
11
30.5.5 Preview: Setup: Header and Footer Tab Select “Header and Footer” from dropdown menu. You can also create your custom Header and Footer by clicking on “Custom Header” and “Custom Footer” buttons.
30.5.6 Preview: Print After you have finished all settings for your worksheet, press “Print” button to Print the worksheet.
If you want to print the chart, Select the chart you want to print from “File” menu and select “Print” option.
12
Click on selected chart, which is selected by default for printing.
Click “Preview” button, the chart will appear on the screen as it will print on the paper, as shown in the picture below.
Zoom, Setup and Margin buttons work in the same manner as for worksheet printing. Click on “Print” button to print the chart. 13
30.6 Save a File as Template and Web Page From File menu select “Save as” option in order to save the file as a Template, “Save as” dialog box appears.
From “Save as type:” drop down menu select “Template”
The file will be stored by default in Templates folder. From “Save as type:” select “Web Page”. Click on Entire Workbook to save entire workbook as a web page or you can select the worksheets you want to save as a Web page.
14
Click on “Change Title”… button to change the title of the web page. “Set Page Title” dialog box appears. Enter the title and Press “OK”.
Click on “Publish” button, “Publish as Web Page” dialog box appears. Select “Item to publish” from drop down menu.
In Viewing options: Check on “Add interactivity with” option if you want to allow the user to interact with the web page and select the option from “Spreadsheet functionality” drop down menu. In the “Publish as Title:” the path and name of file is shown, Check on “Auto Republish every time this workbook is saved” to automatically update the web page. Click on “Change” button to change the title of web page. Check on “open published web page” browser option to open the worksheet on a web page. Click on “Publish” to finish.
15
Self-Check Questions Fill in the Blanks 1. ___________________ command is used for printing a worksheet. 2. ___________________ option from _____________ menu is used to preview a worksheet. 3. To save a file ___________________ command is used.
30.7 Summing Up In this lesson you have familiarized yourself with the following: • • • • •
Copy and Paste Special Concept of cell and sheet reference Print Settings and Printing Print Preview Save file as template and web page
30.8 Answers to Self-Check Questions 1. Print Command 2. Print Preview option, File 3. Ctrl + S
30.9 Terminal Questions 1. 2. 3. 4. 5. 6.
What is the use of Page Setup? What is the Print Area? Name the two types of paper orientations? What is the purpose of margins? What is the benefit of Print Preview? How can you create Header/Footer?
30.10 Glossary • •
16
Setup a Workbook: Setting up of the worksheets of a workbook to print the data available on the worksheets. Preview and Printing: Checking of the status of the data to be printed before actually printing it on the paper.
COS 104
Unit V, Short Cut Keys
Shortcut Keys in Microsoft Excel Shortcuts Keys with the use of Function keys. Key
Alone
Shift
F1
Help
What's Help
F2
Edit Mode
Edit Comment
F3
Paste Name Formula
Paste Function
Define Name
F4
Repeat Action
Find Again
Close Window
F5
Go to
Find
Restore Window Size
F6
Next Pane Previous Pane
Next Workbook
F7
Spell Check
Move Window
F8
Extend Selection
Add To Selection
Resize Window
F9
Calculate All
Calculate Worksheet
Minimize Workbook
F10
Activate Menu
Context Menu
Restore Workbook
F11
New Chart New Worksheet
New Macro Sheet
F12
Save As
Open
Save
Ctrl This
Alt
Shift Ctrl
Insert Chart Sheet Save As Names From Labels Exit
Previous Workbook
Macro List
VB Editor
17
COS 104
Unit V, Short Cut Keys
Shortcuts Keys with the use of Alphabet keys
Key
Alone
Shift
Ctrl
A
Select All
B
Bold
C
Copy
D
Fill Down
E
Alt
Shift Ctrl Formula Arguments
Data Menu Edit Menu
F
Find
File Menu
G
Go to
H
Replace
Help Menu
I
Italics
Insert Menu
Font Name
J K
Insert Hyperlink
L M N
New Workbook
O
Open Workbook
P
Q R
18
Fill Right
Format Menu
Select Comments Font Size
COS 104
Key S
Unit V, Short Cut Keys
Alone
Shift
Ctrl
Alt
Shift Ctrl
Save
T
Tools Menu
U
Underline
V
Paste
W
Close Workbook
X
Cut
Y
Repeat Active
Z
Undo
Window Menu
19
COS 104
Unit V, Short Cut Keys
Shortcuts Keys with the use of Number keys
Key
Shift
Ctrl
Alt
Shift Ctrl
` (~)
Toggle Formula View
General Format
1 (!)
Cell Format
Number Format
2 (@)
Time Format
3 (#)
Date Format
4 ($)
Currency Format
5 (%)
20
Alone
Strikethrough
Percent Format
6 (^)
Exponent Format
7 (&)
Apply Border
8 (*)
Outline
Select Region
9 (()
Hide Rows
Unhide Rows
0 ())
Hide Columns
Unhide Columns
To Paste evTo Transpose divide the Bar data. existing data.To Paste dat divide
COS 104
Unit V, Short Cut Keys
Shortcuts Keys with the use of Special keys
Key
Alone
-
= (+)
Shift
Ctrl
Alt
No Border
Delete Selection Formula
Shift Ctrl
Auto Sum
[
Direct Dependents
Direct Precedents
]
All Dependents
All Precedents
; (semicolon)
Insert Date
Insert Time
Style
' (apostrophe)
: (colon)
Insert Time
/
Select Array
\
Select Differences
Copy Cell Value Above
21