EXCEL 2010: PAGE LAYOUT

Page 1 of 3

E ^XCEL 2010: P ^AGE L ^AYOUT

P

S

Options to change the page layout of a spreadsheet are available from the PAGE LAYOUT tab. Most of these options are available from the PAGE SETUP group on this tab.

M

Margins can be a preset size (normal, wide or narrow) or can be customised to the size you require.

To choose a preset margin:

1. Click margin required from the Margins button on the Page Layout tab.

To customise margins:

1. Click Margins button  Custom Margins... option

2. Enter in the margins required for top, left, right, bottom and header/footer if required.

3. Click OK to confirm changes.

O

The orientation of a spreadsheet can be either portrait or landscape.

This is selected from the Orientation button from the Page Layout tab.

Choose these options to align

spreadsheet centrally on the

page

To see the margins layout in Print Preview

mode

To view the Properties of

the Printer

S

The paper size of the spreadsheet can be selected from the Size button on the Page Layout tab.

P

You can print a specific section of your spreadsheet if you wish. This is a useful feature for those who print a section of a larger spreadsheet on a regular basis – it saves wasting paper on data that is not required to be printed.

To set a print area: click Set Print Area option from the Print Area button on the Page Layout tab.

To clear a print area: click Clear Print Area option from the Print Area button on the Page Layout tab.

S

S

S

F

Scale to Fit: This setting enables you to scale a spreadsheet to fit the paper printed on. For example, if you had a wide spreadsheet you could scale it to fit on 1 page wide by however number of pages long the data was. This option is available from the expand button (bottom right corner) on the Page Setup Group.

You can scale a spreadsheet to a percentage of it’s original size

You can Scale a Document to

Page 3 of 3

P

R

C

/R

H

Having the column headings/row headings repeating across the top of each printed page often aids the readability of the printed document – for example, it saves time from keep returning to the front page to see what the column headings of certain columns are.

1. Click on the PRINT TITLES button from the Page Layout tab.

2. Click in the text area for ‘Rows to be repeated at top:’ or ‘Columns to repeat at left:‘ depending on the option(s) required.

3. Select Row or Column required on Spreadsheet.

4. Click OK button.

P

G

/ C

R

NUMBERING

In the Sheet Options group on the Page Layout tab you can select to view

‘Gridlines’ or ‘Column letters and Row Numbering’ (aka. Headings) on your spreadsheet or print them when you print the document. These options can be switched on/off by selecting/deselecting the tick.

Microsoft images used with permission from Microsoft

E ^XCEL 2010: D ATA VALIDATION

D

V

I

There is a saying in the computer world – Garbage In, Garbage Out. In Excel we can apply some validation on the inputted data to check the data is at least within an expected range.

You can check a range of different validations:

• To allow values from a list. You then type in the list, separating the values with commas.

• To allow a decimal number with limits – then enter in the minimum and maximum values permitted.

• To allow a date within a particular timeframe – then enter the start date and end date allowed.

• To allow a time within a particular timeframe – then enter the start time and end time allowed.

HH:MM format.

• Allow text of a particular length – for example, you could enter the maximum number of characters permitted.

• Custom - Calculate what is allowed based on contents of another cell – for example, you could allow data to be entered only if the value in another cell is positive.

T

A

D

V

:

1. Click the cell(s) you wish to apply a particular validation to.

2. Click Data tab and click Data Validation button.

3. In the Settings tab, choose the validation criteria that you will allow in the cell.

4. Enter in the specific criteria as needed – e.g. if you chose list, you will need to enter in the list of values permitted, separate the values with commas. Ensure these are spelt accurately.

5. Click Input Message tab - this will show a message when the highlighted cell(s) are selected – prompting user to enter in correct data.

6. Enter in a message, such as ‘Please choose from the list’ (if you chose list option).

Page 2 of 2 7. Click Error Alert tab. This will show a

message, if data is invalid; to tell the user the data input is incorrect.

8. Enter in the error message you would like to appear.

9. Choose the ‘Style’ of the warning – probably

‘Stop’ style (explanation below) 10. Click OK button.

STYLES OF ERROR

–INPUT MESSAGE ALERTS OR ERROR ALERTS ONLY APPEAR WHEN TEXT IS ENTERED DIRECTLY INTO A CELL CONTAINING VALIDATION.ERROR ALERTS ONLY APPEAR WHEN A INCORRECT DATA IS ENTERED INTO A CELL WITH VALIDATION ON.

Stop – Can only enter in data from options available. If error, a error message is displayed and users will be allowed to RETRY or CANCEL.

Warning – User will be warned if data is invalid. User will receive a warning message where they can select YES to accept the entry, or cancel to reject it.

Information – Very similar to the warning - but is provided as ‘Information’. If data entered is not validation the user can accept or reject the data.

R

D

V

1. Click Data tab and click Data Validation button.

2. Click Clear All button and then OK button.

Microsoft images used with permission from Microsoft

U ^SING N ^AMED R ^{ANGES IN} F ^ORMULAS

You can use named cells in formulas and functions. This is especially useful in ‘between sheets’ arithmetic.

U

N

R

S

You can use named ranges to calculate formulas between different sheets.

For example to add the ‘Undergrads_Year1’ named range in sheet1 to the ‘Undergrads_Year2’ named range in sheet 2, the formula would be:

= Undergrads_Year1 + Undergrads_year2

(note: you do not need to reference the sheet the named range is applying to as the named range is unique to the spreadsheet and it cannot be confused with any other cell reference).

W

– C

N

R

If you make changes to a ‘named range’ which falls outside the original area of the named range, you will need to delete the original named range then redefine the range, including the new data.

C

N

R

1. Highlight the cells you would like in your named range.

2. Click on Formula Tab and then select Define Name button.

3. Give the range a suitable name (not too long). Choose the scope of the named range.

4. Click OK button.

U

C

R

N

F

Page 2 of 2

S

N

R

1. Highlight the cells you would like in your named range 2. In the Name Box type in the name of your range 3. Press ENTER key on the keyboard to confirm changes

R

N

R

1. Click Formulas tab and click Name Manager button in the Defined Names group 2. Select the Named range you wish to remove

3. Click Delete button

4. Click Close button to return to normal screen

Microsoft images used with permission from Microsoft