L-30

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

Print

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

Print

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