• No results found

Workshop Information

N/A
N/A
Protected

Academic year: 2023

Share "Workshop Information "

Copied!
40
0
0

Loading.... (view fulltext now)

Full text

(1)

Le v e l 2 - M I C RO S O FT EX C EL 2 0 1 0

Ex c e l F o r m a t t i n g , F o r m u l a s & F u n c t i o n s

LEARNING

GUIDE

(2)

Workshop Information

Information Technology Services is happy to provide you with this training opportunity. We hope you enjoy it and the time you invest in participating is valuable to your work here at Massey University.

Learning Outcomes

In this workshop you will:

 Learn how to format a worksheet.

 Learn how to apply protection to a worksheet and workbook.

 Learn how to link to another worksheet or workbook.

 Learn how to use sorts and filters.

 Gain an understanding on how to use conditional functions.

Format Face to face workshop, duration approximately 2 hours. All exercises are done in the Excel2 Workbook.xlsx file.

Additional

Resources Training courses for Excel 2010

http://office.microsoft.com/en-nz/excel-help/training-courses-for-excel-2010- HA104039038.aspx?CTT=1

Help For further assistance please contact ITS Service Desk on extension 82111.

Feedback After this workshop please complete our online ITS Training Feedback form. Your feedback is appreciated. Hearing from you about your training experience allows us to improve the relevance and quality of this training.

http://www.massey.ac.nz/itstraining/feedback/

A digital copy of this document is available online. ITS thanks you for considering the environment before printing.

(3)

Contents

Workshop Information ... 1

Learning Outcomes ... 1

Format ... 1

Additional Resources ... 1

Help ... 1

Feedback ... 1

Conditional Functions ... 4

Creating an IF function ... 4

Creating a nested IF function ... 5

Creating SUMIF functions ... 5

3 argument SumIf ... 6

COUNTIF function ... 6

AVERAGEIF function ... 7

Exercise Nested If ... 8

Exercise SumIf ... 10

Exercise CountIf ... 10

Sorting and Filtering ... 11

One column sort ... 11

Multi-level sort ... 12

Exercise simple sort ... 14

Exercise multi-level sort ... 14

Filtering ... 15

Filtering data ... 15

Exercise simple filters ... 17

Using advanced filters ... 17

Exercise Advanced filters ... 19

Formatting a Worksheet... 20

Inserting rows ... 20

Inserting columns ... 21

Deleting rows or columns ... 22

Alignment ... 23

Format numbers ... 24

Styles ... 25

Adjusting column width manually ... 26

Adjusting column width automatically ... 26

Click and drag column width ... 27

Adjusting row height manually ... 27

Autofit row height automatically ... 27

Formatting exercise ... 28

(4)

Protecting Workbooks & Worksheets ... 29

Unlock cells ... 29

Hide formulas ... 30

Unlock graphic objects ... 31

Protect Sheet ... 32

Protect Workbook elements ... 32

File Protection ... 33

Remove password protection ... 33

Worksheet protection, exercise ... 34

Workbook protection, exercise ... 35

Remove workbook protection, exercise ... 35

Linking ... 36

Link – copy and paste ... 36

Link – using a formula ... 37

Managing links ... 37

Linking worksheets, exercise ... 37

Data Validation ... 38

Using data validation ... 38

Data validation, exercise ... 39

(5)

Conditional Functions

Introduction One of the common tasks in Excel is to evaluate an answer and if the result is one thing do a particular calculation, or if the result is something else then do a different calculation. For example if you are evaluating students grades and want to allocate them an A letter grade, if they achieve a mark of 80% or more, a B letter grade, if they achieve a mark of between 59% – 79% etc.. . This is achieved by using the IF function.

Three other conditional functions that are useful are the COUNTIF, AVERAGIF AND SUMIF functions. These functions only count, average or sum items in a list that meet a certain criterion.

Creating an IF

function The IF statement has three parts, separated by commas as follows:

=IF(logical_test, action_if_true, action_if_false)

Argument Description

Logical test A logical test is one where the answer is true or false. The test itself may be quite complex but it must be, in the end, a yes/no answer.

Action if true What to do if the test is result is true. The action can be a numeric value, text or formula. All text must be between quotes.

Action if false What to do if the test result is false. The action can be a numeric value, text, or a formula. All text must be between quotes.

Example: An IF function for giving a Pass grade if the mark in cell A1 is greater than or equal to 50, and a Fail grade if the value in cell A1 is less than 50 looks like the

following:

=IF(A1>=50,”Pass”,”Fail”)

(6)

Creating a nested IF function

A nested IF function can have a maximum of 64 IF statements within it and is evaluated from left to right.

Nested IF functions allow testing on multiple sequential conditions. Only one test is performed at a time, however the action that results from that test is itself another IF function. For example if a class grade is from 80 to 100, then show ‘A’, if it is from 70 to 79 show ‘B’ , if it’s from 59 to 69 show ‘C’ otherwise show ‘F’.

For the above example, a nested IF function would look like the following assuming that the grade is in cell A1

=IF(A1>=80,"A",IF(A1>=70,"B",IF(A1>=59,"C","F"))).

Creating SUMIF

functions You use the SUMIF function to sum the values in a range that meet criteria that you specify. There are two main types of SUMIF, the two arguments and the three arguments.

Two Argument SUMIF

The values that are summed are the ones that match. This is only for numeric data and has the following format.

=SUMIF(Range, Criteria))

Argument Description

Range The range of cells that will be summed up based on the set criterion.

Criteria Specifies what the criterion is. It is numeric in nature but in quotes, for example “>5”

Example: Suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula:

=SUMIF(B2:B25,">5")

In this example, the criteria are applied to the same values that are being summed.

(7)

3 argument SumIf Three Argument SUMIF

You can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal

"John."

=SUMIF(Range, Criteria, Sum Range)

Argument Description

Range The range of cells that will be summed up based on the set criterion.

Criteria Specifies what the criterion is. It is numeric in nature but in quotes, for example “>5”

Sum Range The range to sum up if the criteria is met.

COUNTIF function This function counts the total number of cells in a range that meet a criterion. For example in a range named Department, calculate how many times a specific

department appears. Another example may be to calculate in a range of values, how many of those values are over a specific amount

This function has numeric and text criteria and has the following format,

=COUNTIF(Range, Criteria):

Argument Description

Count Range The range that has to be counted based on the set criterion.

Criteria Specifies what the criterion is. Criteria are numeric in nature or text and in quotes for example “Faculty” or “>35000”

Example: If a range named Salaries contains the salary amounts for each Faculty, count the total number of salaries that are over 50000:

=COUNTIF(Salaries, “>50000”)

(8)

AVERAGEIF

function This function averages a range of cells that meet a criterion. For example in a range named Faculty, calculate the average of that specific department.

=AVERAGIF(range, criteria,average_range):

Argument Description

Count Range The range that has to be counted based on the set criterion.

Criteria Specifies what the criterion is. Criteria are numeric in nature or text and in quotes for example “Faculty” or “>35000”

Example: If a range named Salaries contains the salary amounts for each Faculty, work out the average of all salaries less than 50,000.

=AVERAGEIF(Salaries, “<50000”).

(9)

Exercise Nested If

Step Action

1

Select the worksheet called Nested If.

In this exercise we are going to create a nested if statement to allocate a symbol to a student based on the grade that they attained.

Grade Symbol to be allocated

Greater than 80 A

Between 69 and 80 inclusive B Between 59 and 68 inclusive C Between 50 and 58 inclusive D

Everything else E

2 Select cell C2.

3 Use the button (to the left of the formula bar) to bring up the Insert Function dialog.

4 Select the IF function. Click OK.

5 For Logical_test enter B2>=80.

6 For Value_if_true enter “A”.

7 For Value_if_false enter 0.

8 Click OK.

9 Auto fill the formula down to C10 and check that it works so far.

10 Select cell C2 again.

11 Click into the formula bar.

12 Backspace over the 0 at the end of the existing formula.

(10)

Exercise Nested If, continued

Step Action

13

To the left of the Formula Bar should be a button with the word IF showing. Click this to bring up the Function Arguments dialog again.

14

Create another IF function using the following conditions For Logical_test enter B2>=69

For Value_if_true enter “B”

For Value_if_false enter 0

15 Click OK.

16 Auto fill the formula down to C10 and check that it works so far.

17

Repeat steps 11 to 17 for the third condition as follows:

For Logical_test enter B2>=59 For Value_if_true enter “C”

For Value_if_false enter 0

18

Repeat steps 11 to 17 for the fourth condition as follows:

For Logical_test enter B2>=50 For Value_if_true enter “D”

For Value_if_false enter “E”

(11)

Exercise SumIf

Step Action

1

Select the worksheet called Sum if.

In this exercise we want to sum the number of participants in semester 2 who attended the Excel Level 3 course.

2 Select cell C30.

3 Use the fx button to bring up the Insert Function dialog.

4 Select the SUMIF function and click OK.

5 For the Range argument, select cells B2:B28.

6 For the Criteria argument select cell B4

7 For the Sum_range argument select cells F2:F28, then click OK.

Exercise CountIf

Step Action

1

Select the worksheet called Sum if. Select cell C31.

In this exercise we want to count how many courses in semester one were delivered 5 or more times.

2 Use the fx button to bring up the Insert Function dialog

3 Select the function COUNTIF. (It can be found under the Statistical category).

4 Click OK.

5 For the Range argument select C2:C28 6 For the Criteria enter “>5” then click OK.

(12)

Sorting and Filtering

Introduction

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list (such as Large, Medium, and Small) or by format, including cell colour, font colour, or icon set. Most sort operations are column sorts, but you can also sort by rows.

One column sort To sort text in a range of cells by one column follow the steps below:

Step Action

1 Select or click in the list of data you want to sort

2

You can select only a single column of data if you want to sort that column independently of the rest of the data. The following warning message would then appear

However, in most cases you are likely to sort the entire list, so you would choose to expand the selection

3

On the Data tab, in the Sort & Filter group, do one of the following:

 To sort in ascending alphanumeric order, click Sort A to Z.

 To sort in descending alphanumeric order, click Sort Z to A.

(13)

Multi-level sort The Sort dialog box lets you tell Excel what column to sort on next if two cells in the main sort column contain the same value or data.

Step Action

1

On the Data tab in the Sort & Filter group, click the Sort button. The Sort dialog box appears. If your data includes column headings, make sure the My Data Has Headers option is checked. Excel usually detects the correct setting automatically.

2

From the Sort By drop-down list, select the column by which you want to sort.

This drop-down list includes the column headings for each column in the list

3

From the Sort On drop-down list, choose Values.

Note that you also can perform the sort on Cell Color, Font Color, or Cell Icon. Typically, you will perform a sort based on values (rather than formatting).

(14)

Multi-level sort,

continued Step Action

4

From the Order drop-down list, select how you want to sort the data. The options that appear in this list change based on the contents of the sort column. Choose A to Z or Z to A to sort text values, Smallest to Largest or Largest to Smallest to sort numeric data, or Oldest to Newest or Newest to Oldest to sort by dates.

5

Click the Add Level button

. Additional drop-down list boxes appear for the secondary sort column. This is the column Excel will sort by if two or more items are identical in the first Sort By option.

6

Repeat steps 3 through 5 for the new sorting level. After you specify the options for the secondary sort column, you can add more sort columns as needed. Use the Move Up and Move Down buttons if you decide to change the order of the sort columns.

7 Click OK.

(15)

Exercise simple

sort In this exercise we sort our data by one column. In the worksheet Filtering we are going to sort in ascending order by the Title field

Step Action

1 Select a cell in column B.

2

On the Data tab in the Sort & Filter group click on the A –Z sort button.

All the titles will now be sorted in ascending order starting with numbers and then the letters of the alphabet.

Exercise multi-

level sort Next we are going to perform a multi-level sort. We want to sort our data in ascending order by Exam Date (Oldest to Newest). Within each exam date we want all the am exams to appear before the pm exams. Within am/pm we want to sort by the Location of the exam (ascending) and within each location by the Code.

The multi-level sort order therefore is:

Exam date > am/pm > Location > Code

Step Action

1 Click anywhere onto the data.

2

On the Data tab in the Sort & Filter group, click the Sort button. The Sort dialog box appears. If your data includes column headings, make sure the My Data Has Headers option is checked. Excel usually detects the correct setting automatically

3 From the Sort By drop-down list, select Exam date.

4 From the Sort On drop-down list, choose Values.

5 From the Order drop-down list, choose Oldest to Newest.

6

Click the Add Level button

. Additional drop-down list boxes appear for the secondary sort column. This is the column Excel will sort by if two or more items are identical in the first Sort By option.

7

Repeat steps 7 through 10 for am/pm, Location and Code. Use the Move Up and Move Down buttons if you decide to change the order of the sort columns

.

8

Click OK.

(16)

Filtering Filtering allows us to view just the data we need to see from a large list based on specific criteria. When criteria are applied, Excel will show the rows that meet the criteria and hide those that don’t.

Filtering data Step Action

1 Position your cursor anywhere in the data.

2

On the Data tab, in the Sort & Filter group, select the Filter button.

3

Note the drop down arrows to the right of each column title.

4

Click on a drop down arrow to the right of the title you want to set criteria on.

5

To display only unique values for the columns, select the value you want.

The data will only show records with that value.

(17)

Filtering data,

continued Step Action

1

To display text filters, select Text Filters and choose a filter i.e. Equals, Does not Equal etc.

2

To remove a filter on the Data tab, in the Sort & Filter group, de-select the Filter button.

Note: The drop down arrows display a funnel next to them when a filter is in place.

Auto filtering limitations

Using the custom auto filter options you cannot filter for more than two

items in a single column. In order to do so you would need to use an

Advanced Filter.

(18)

Exercise simple

filters In this exercise we will filter for all exams held on the 14th June, am in SSLB3.

Step Action

1 Click onto the data. On the Data tab, in the Sort & Filter group, select the Filter button. Note the drop down arrows to the right of each column title.

2

Click on the drop down arrow to the right of Exam date.

Deselect everything by un-ticking the box to the left of June.

Select 14 under June and then OK.

3 Repeat step 4 above for the am/pm and Location fields.

Using advanced

filters Step Action

1

Create a separate range that contains the criteria by copying all the column labels from your list to another range, preferably at the bottom of the existing list.

2

In the example below the criterion range is A1:L4.

3

The criteria above will filter for Media Relations where the year is 2010 and where the Revenue is greater than 50 and all records from Sales and Marketing and all records from Finances.

4

You can filter for records that have any data in a field, by typing * for the criterion. You can filter for records that don’t have any data in a field by typing

<>*.

5 One row in the criterion range is an AND statement while the columns are OR statements.

6

In the example above we are looking for those records that have (Year = 2010 and Department = Media Relations AND Revenue > 50) OR (Department = Sales and Marketing) OR (Department = Finances).

(19)

Advanced filters, continued

Step Action

7

Click once inside the range you wish to filter, and then on the Data tab in the Sort &

Filter group select Advanced.

8

Check that the List range and the Criteria range are correct.

Note: If your criteria range includes an entirely blank row then all records will be returned.

9

Choose to Filter the list in-place or Copy to another location in the same sheet. If you choose to copy to another location, you must specify the range to copy.

Note: You can only copy filtered data to the active sheet.

11

The filtered results will display as follows:

(20)

Exercise Advanced filters

Step Action

1

In this exercise we will create an advanced filter where we will filter for the following criteria

All courses with a Title beginning with Intro (between the 10th and 13th June 2008), or Human (which has no Requirements), or Adv (in Location SSLB4)

Note:

1) One row in the criterion range is an AND statement while the columns are OR statements

2) You can filter for records that have any data in a field, by typing * for the criterion. You can filter for records that don’t have any data in a field by typing <>*.

2

Create a separate range that contains the criteria by copying all the column labels from your list (A1:G1) to another range (A685:G685), at the bottom of the existing list

3 Copy the Exam Date column heading (E685) to column H (H685) so that we can create a filter condition for a range of dates.

4

Enter you Criteria range (A685:H688) as follows:

5 Click into the data and then in the Data tab in the Sort & Filter group click Advanced.

6

Enter the Advanced Filter dialogue box as follows:

List Range: $A$1:$G$683

Criteria Range: Filtering!$A$685:$H$688 Copy to: Filtering!$A$690

7 Click OK and check your results.

(21)

Formatting a Worksheet

Introduction Once data has been entered into your worksheet, applying formatting will help improve the look of your spread sheet and make it easier to read.

Inserting rows You can insert blank cells above or to the left of the active cell on a worksheet.

When you insert blank cells, Excel shifts other cells in the same column down or cells in the same row to the right to accommodate the new cells. Similarly, you can insert rows above a selected row and columns to the left of a selected column. You can also delete cells, rows, and columns.

To insert rows on a worksheet follow the steps below:

Step Action

1

To insert a single row, select either the whole row or a cell in the row above which you want to insert the new row. For example, to insert a new row above row 5, click a cell in row 5.

To insert multiple rows, select the rows above which you want to insert rows. Select the same number of rows as you want to insert. For example, to insert three new rows, you select three rows

2

On the Home tab, in the Cells group, click the arrow next to Insert, and then click Insert Sheet Rows

Note: You can also right-click the selected rows and then click Insert.

(22)

Inserting columns To insert columns on a worksheet follow the steps below.

Step Action

1

To insert a single column, select the column or a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of column B, click a cell in column B.

2

To insert multiple columns, select the columns immediately to the right of where you want to insert columns. Select the same number of columns as you want to insert. For example, to insert three new columns, you select three columns.

3

To insert multiple columns, select the columns immediately to the right of where you want to insert columns. Select the same number of columns as you want to insert. For example, to insert three new columns, you select three columns.

Note: You can also right-click the selected columns and then click Insert.

(23)

Deleting rows or columns

You can delete cells, rows, or columns by following the step below.

Step Action

1 Select the cells, rows, or columns that you want to delete.

2

On the Home tab, in the Cells group, click the arrow below Delete, and then do one of the following:

.

 To delete selected cells, click Delete Cells.

 To delete selected rows, click Delete Sheet Rows.

 To delete selected columns, click Delete Sheet Columns.

Note: You can right-click a selection of cells, click Delete, and then click the option that you want. You can also right-click a selection of rows or columns and then click Delete.

(24)

Alignment For the optimal display of the data on your worksheet, you might want to reposition the data in a cell

Step Action

1

To insert a single column, select the column or a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of column B, click a cell in column B.

2

On the Home tab, in the Alignment group, do one or more of the following:

3 To change the vertical alignment of cell contents, click Top Align , Middle Align , or Bottom Align ..

4 To change the horizontal alignment of cell contents, click Align Text Left , Center , or Align Text Right .

5 To change the indentation of cell contents, click Decrease Indent or Increase Indent .

6 To wrap the text in a cell, click Wrap Text.

7 To centre or align data that spans several columns or rows, such as column and row labels, select a range of cells and then click Merge and Center

(25)

Format numbers By applying different number formats, you can display numbers as percentages, dates, currency, and so on.

Step Action

1 Select the cell or range of cells that contains the data that you want to format.

2

On the Home tab, in the Number group, click the dialog box launcher next to Number (or just press Ctrl+1).

3

In the Category list, click the format that you want to use, and then adjust settings, if necessary. For example, if you're using the Currency format, you can select a different currency symbol, show more or fewer decimal places, or change the way negative numbers are displayed.

(26)

Styles Use styles to format your document so you can quickly and easily apply a set of formatting choices consistently throughout your document.

Step Action

1 Select the cell or range of cells that contains the data that you want to format.

2

On the Home tab, in the Styles group, click the more button to open the styles gallery.

3

From here you can apply various styles to your cell or range selection, such as shading for totals, or a heading style for your worksheet headings

Note: Cell styles are based on the document theme that is applied to the whole workbook. When you switch to another document theme, the cell styles are updated to match the new document theme.

(27)

Adjusting column width manually

To manually change the width of a column follow the steps below:

Step Action

1 Select the column or columns that you want to change.

2

On the Home tab, in the Cells group, click Format.

3

Under Cell Size, click Column Width.

Note: To quickly set the width of a single column, right-click the selected column, click Column Width, and then type the value that you want.

4

In the Column width box, type the value that you want.

Note: To quickly set the width of a single column, right-click the selected column, click Column Width, and then type the value that you want.

Adjusting column width

automatically

To automatically change the width of a column to fit the contents, follow the steps below:

Step Action

1 Select the column or columns that you want to change.

2 On the Home tab, in the Cells group, click Format.

3 Under Cell Size, click Autofit Column Width.

(28)

Click and drag column width

To change the width of a column by using the mouse, follow the steps below:

Step Action

1 Select the column or columns that you want to change.

2

To change the width of one column, drag the boundary on the right side of the column heading until the column is the width that you want.

3

To change the width of multiple columns, select the columns that you want to change, and then drag a boundary to the right of a selected column heading.

4

To change the width of columns to fit the contents, select the column or columns that you want to change, and then double-click the boundary to the right of a selected column heading

Adjusting row height manually

To set a row to a specific height follow the steps below:

Step Action

1 Select the row or rows that you want to change.

2 On the Home tab, in the Cells group, click Format.

3 Select Row Height and enter a value.

Autofit row height automatically

To set a row to a specific height follow the steps below:

Step Action

1 Select the row or rows that you want to change.

2 On the Home tab, in the Cells group, click Format.

3 Under Cell Size, click Autofit Row Height.

(29)

Formatting exercise

In this exercise we are going to change some of the formatting in the worksheet.

Step Action

Select the Outstanding Amounts worksheet in the Excel2 workbook.

1

Insert a row above P. North and call it P. North – Hokowhitu:

1. Right click row 6 and from the shortcut menu select Insert.

2. Type P.North – Hokowhitu into the new row.

2

Autofit the width of column A.

Move your cursor between the headers of column A and column B until it becomes a double sided arrow, and then double left click.

3 Copy all the Auckland data and paste it into row 6.

4

Centre and merge the worksheet title across columns A to H.

Select cells A1 to H1. On the Home tab in the Alignment group click on Merge & Center.

5

Format the numbers so that a thousands separator is displayed, without any decimal places:

1. Select cells B4 to H7.

2. On the Home tab in the Number group, click the comma separator symbol.

3. Remove the two decimal places by clicking the Decrease Decimal button twice.

6 Save the Excel2 Workbook.xls.

(30)

Protecting Workbooks & Worksheets

Introduction To prevent a user from accidentally or deliberately changing, moving, or deleting important data from a worksheet or workbook, you can protect certain worksheet or workbook elements, with or without a password. You can remove the protection from a worksheet as needed.

Note: Worksheet and workbook element protection should not be confused with workbook-level password security. Element protection cannot protect a workbook from users who have malicious intent. For an additional layer of security, you should help protect your whole workbook file by using a password. This allows only authorized users to view or modify data in the workbook.

Note: Worksheet elements are only protected once the sheet is protected.

Unlock cells To unlock any cells or ranges that you want other users to be able to change, do the following.

Step Action

1 Select each cell or range that you want to unlock.

2 On the Home tab, in the Cells group, click Format, and then click Format Cells.

3

On the Protection tab, clear the Locked check box, and then click OK.

(31)

Hide formulas To hide any formulas that you do not want to be visible, do the following Step Action

1 In the worksheet, select the cells that contain the formulas that you want to hide.

2 On the Home tab, in the Cells group, click Format, and then click Format Cells.

3

On the Protection tab, select the Hidden check box, and then click OK.

(32)

Unlock graphic objects

To unlock any graphic objects (such as pictures, clip art, shapes, or Smart Art graphics) that you want users to be able to change, do the following:

Step Action

1

Hold down Ctrl and then click each graphic object that you want to unlock.

Tip: You can also use the Go To command to quickly select all the graphic objects in a worksheet. On the Home tab, in the Editing group, click Find & Select, and then click Go To. Click Special, and then click Objects.

2

Under the Picture Tools tab click Format and then click the dialogue box launcher.

3

Click on the Properties group on the left panel of the dialogue box. On the right hand side untick the Locked checkbox.

(33)

Protect Sheet In order for formulas to be hidden and for unlocked cells to be activated, the worksheet needs to be protected first. Follow the steps below to protect a worksheet:

Step Action

1 On the Review tab, in the Changes group, click Protect Sheet.

2 In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.

3 In the Password to unprotect sheet box, type a password for the sheet, click OK, and then retype the password to confirm it.

Protect Workbook elements

Step Action

1

On the Review tab, in the Changes group, click Protect Workbook.

2

Under Protect workbook for, do one or more of the following:

 To protect the structure of a workbook, select the Structure check box.

 To keep workbook windows in the same size and position every time the workbook is opened, select the Windows check box.

3 To prevent other users from removing workbook protection, in the Password (optional) box, type a password, click OK, and then retype the password to confirm it.

(34)

File Protection To encrypt your workbook and set a password to open it follow the steps below:

Step Action

1

On the File tab, under Info, click Protect Workbook.

2

Select Encrypt with Password.

3 In the Password box, type a password, and then click OK.

4 In the Reenter password box, type the password again, and then click OK.

5 To save the password, save the file.

Remove password protection

Step Action

1 Use the password to open the spreadsheet.

2 On the File tab, under Info, click Protect Workbook.

3 Select Encrypt with Password.

4 In the Encrypt Document dialog box, in the Password box, delete the encrypted password, and then click OK.

5 Save the spreadsheet.

(35)

Worksheet protection, exercise

Step Action

1

In this exercise we will only allow users to make changes to cells without formulas.

3. Open the Outstanding Amounts worksheet.

4. Select cells B4:G6.

2 On the Home tab, in the Cells group, click Format, and then click Format Cells

3 On the Protection tab, clear the Locked check box, and then click OK.

4

Next we are going to hide the formulas in the Totals row, so that if someone selects any of the Totals cells, a formula won’t be displayed in the formula bar. (In practice this might contain sensitive information such as a

commission rate).

Select cells B8:H8.

5 On the Home tab, in the Cells group, click Format, and then click Format Cells

6 On the Protection tab, select the Hidden check box, and then click OK

7

Next we are going to protect the worksheet and check that we aren’t able to modify Totals or view their formulas in the Formula bar.

On the Review tab, in the Changes group, click Protect Sheet.

8 In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.

9 In the Password to unprotect sheet box, type a password for the sheet, click OK, and then retype the password to confirm it.

10 Click OK.

11 Click onto one of the totals in the Totals row and try and make changes. Look at the formula bar to check that the formula isn’t displayed.

(36)

Workbook protection, exercise

Step Action

1

In this exercise we will protect the workbook, so that a password must be entered before the workbook can be opened..

Open the workbook Excel2 Workbook.xls.

2 On the File tab, under Info, click Protect Workbook.

3 Select Encrypt with Password.

4 In the Password box, type a password, and then click OK.

5 In the Reenter password box, type the password again, and then click OK.

6 To save the password, save the file.

7 Close the file and re-open it. You should be prompted for the password.

Remove workbook protection,

exercise

Step Action

1

In this exercise we will remove the password that must be entered before the workbook can be opened..

Open the workbook Massey University Outstanding Amounts.xls.

2 Use the password to open the spreadsheet.

3 On the File tab, under Info, click Protect Workbook.

4 Select Encrypt with Password.

5 In the Encrypt Document dialog box, in the Password box, delete the encrypted password, and then click OK.

6 Save the spreadsheet.

7 Close the file and re-open it to make sure that a password doesn’t need to be entered to open it.

(37)

Linking

Introduction An internal link is a link between worksheets in the same workbook. Links between worksheets in the same workbook have the sheet name followed by the exclamation mark, then the cell address or name. For example =Sheet1!A!.

An external link is where one cell in a workbook, the dependant, is connected to another cell in a different source workbook.

Linking is useful when it is not practical to keep large worksheets together in the same workbook. Linking is also useful to consolidate data from several worksheets, to streamline large and complex worksheets and for designing more flexible worksheets.

A link to another workbook will show the other workbooks name in square brackets followed by the sheet name and an exclamation mark, then the cell address. For example a link to cell B3 on the Table 1 worksheet in the annual household income workbook will show as

If the sheet name has a space in it then the file and sheet name is enclosed in single quotes.

Link – copy and paste

Step Action

1 Select the cell in the worksheet that you want to link from (the source worksheet).

2 Copy the cell.

3 Select the cell in the worksheet that you want the link to appear in. (the dependant worksheet)

4

Paste the link into the destination cell.

(38)

Link – using a formula

Step Action

1 Select the cell in the worksheet that you want to link to appear in. (the dependant worksheet)

2 Type =

3 Select the cell you want to link (you may need to switch to another worksheet/workbook via the View tab, Switch Windows button).

4 Press Enter.

Managing links The dependant workbook does not have to be open for changes to the data to be made in the source workbook. When you next open the dependent workbook, by default links will be updated automatically. (This can be changed in the File tab under Options, Advanced).

If you move a source file to a different folder or drive then you will have to open the dependent workbook and from the Data tab, under the Connections group choose Edit Links to update the source

Linking worksheets,

exercise In this exercise we will create a link to another worksheet in the same workbook

Step Action

1 Open the worksheet Auckland Affordability in Excel2 Workbook.xlsx.

2 Fill out the Auckland Median Price (link to the Median Price Worksheet)

3

Now we are going to link to a worksheet in another workbook. Fill out the average annual household income by linking to the annual household income workbook.

(39)

Data Validation

Data validation introduction

Data validation is used in Excel to make sure that users enter certain values into a cell. Setting validation for cells enables users to define simple rules for cell entries, to ensure that the proper data is entered into spreadsheet cells.

To ensure that the correct data is entered into a cell (or range of cells), you can specify what data is valid. This could be data types like whole numbers, decimal numbers, text or dates, or could have defined limits (for example, the number of characters allowed).

Using data validation

Step Action

1 Select the cell you want to apply validation to.

2 On the Data tab, under the Data Tools group, choose Data Validation and the Settings tab.

3

In the Allow box choose the data type you want to allow. Set a limit if required by entering values in the Data box and entering the limits

4 Input messages display when the user selects a cell that has validation set.

Typically this would be used to outline the type of data entry allowed.

5

Error messages display when the user enters data that is not valid. They may be either informative or a warning. There are three styles of message available. The Stop style prevents the user from entering invalid data at all.

The Warning and Information styles will allow invalid data to be entered after notifying the user. This may be useful if the user in this case is only concerned with data entry and it is someone else’s job to check the validity.

(40)

Data validation,

exercise Step Action

1

In this exercise we will add data validation to the Outstanding Amounts worksheet, so that only whole numbers (no decimals) between 10000 and 25000 can be entered for Jan for Auckland, Wellington and P.North.

Select the Outstanding Amounts worksheet.

2 Select cells B4:B6.

3 On the Data tab in the Data Tools group select Data Validation and then Data Validation again.

4 In the Data Validation dialogue box on the Setting tabs select the drop down arrow next to Allow and choose Whole Number.

5 In the Minimum box enter 10000.

6 In the Maximum box enter 25000.

7

On the Input Message tab:

1. In the Title box enter Data Validation.

2. In the Input message box type Enter a whole number between 10000 and 25000.

8

On the Error Alert tab :

1. Under Style select Stop.

2. Under Title type Invalid Amount Entered.

3. Under Error message type Please re-enter a whole number between 10000 and 25000.

9 Click OK.

10 Test the validation by typing different values in cells B4:B6.

11

Clear the validation rules by selecting cells B4:B6 and then on the Data tab in the Data Tools group select Data Validation and then Data Validation again.

In the bottom right hand corner of the Data Validation dialogue box click Clear All and then OK.

References

Related documents

In your answer you will be assessed on how well you: ■ write from a particular perspective in a specified context ■ demonstrate an understanding of the prescribed text ■ communicate