Open Learning Guide

Open LearningExcel 2007 Advanced

Open Learning Guide

Microsoft®

Excel 2007

Advanced

Note:Microsoft and Excel are registered trademarks of the Microsoft Corporation in the US, UK and other countries. AutoSum is a trademark of the Microsoft Corporation.

Release OL314v1

Published by:

CiA Training Ltd

Business & Innovation Centre

SunderlandEnterprisePark

Sunderland SR5 2TH

United Kingdom

Tel: +44 (0)191 549 5002

Fax: +44 (0)191 549 9005

Email:

Web:

ISBN 13: 978-1-86005-533-1

Important Note

This guide was written using Windows Vista. If using Windows XP some dialog boxes will look different, although the content is the same.

A screen resolution of 1024 x 768 was used. Working in a different screen resolution, or with an application window which is not maximised, will change the look of the Office 2007 Ribbon.

The ribbon appearance is dynamic, it changes to fit the space available. The full ribbon may show a group containing several options, but if space is restricted it may show a single button that you need to click to see the same options, e.g. the Editing group may be replaced by the Editing button.

First published 2007

All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written permission of CiA Training Limited.

CiA Training's OPEN LEARNING guides are a collection of structured exercises building into a complete open learning package, to teach how to use a particular software application. They are designed to take the user through the features to enhance, fulfil and instil confidence in the product.

EXCEL ADVANCED - The third and final guide in the Excel 2007 series contains exercises covering the following topics:

Advanced Functions

Input Data Tables

Data Analysis Tools

Manipulating Charts

Objects

Outlines

Drawing Tools

Macros

Data Consolidation

Lookup Tables

Auditing

Importing Data

Forms

Sharing Workbooks

Styles

Hyperlinking

Creating Web Pages

This Open Learning Guide is suitable for:

Any individual wishing to further their knowledge of Microsoft Excel following the Introductory and Intermediate guides or equivalent. The user works through the guide from start to finish.
Tutor led groups as reinforcement material.

Aims and Objectives

To further the user's knowledge and techniques for the successful creation and use of complicated spreadsheet models using Excel 2007.

After completing the guide the user will be able to:

create and maintain complex spreadsheets.
manipulate charts and data.
use look up tables.
use date and time, statistical and string functions.
use Data Tables and Databases.
create, use and edit simple Macros.
create Web pages and use hyperlinking.
create on-line forms.
import data from a variety of sources.

Downloading the Data Files

The data associated with these exercises must be downloaded from our website. Go to: . Follow the on screen instructions to download the appropriate data files.

By default, the data files will be downloaded to Documents\CIADATA FILES\Open Learning\Excel 2007 Advanced Data.

If you prefer, the data can be supplied on CD at an additional cost. Contact the Sales team at .

Introduction

This Open Learning Guide assumes that the program has been correctly and fully installed on your PC (Personal Computer). Some features described in this guide such as Data Analysis will not work if the program was not fully installed.

Important Note For All Users

The accompanying downloaded data contains files, enabling the user to practise new techniques without the need for data entry.

This guide cannot be copied without the permission of CiA Training Ltd.

Notation Used Throughout This Guide

All key presses are included within < >, e.g.Enter> means press the Enter key.
The guide is split into individual exercises. Each exercise consists of a written explanation of the feature, followed by a stepped exercise. Read the Guidelines and then follow the Actions, with reference to the Guidelines if necessary.

Recommendations

It is suggested that users add their name, the date and exercise number after completing each exercise that requires a printed copy.
Read the whole of each exercise before starting to work through it. This ensures the understanding of the topic and prevents unnecessary mistakes.

Section 32 Advanced Functions......

235 - Logical Functions

236 - Date and Time Functions

237 - Lookup & Reference Functions

238 - Statistical Functions

239 - Maths & Trig Functions

240 - Text Functions

241 - Financial Functions

242 - Database Functions

243 - Nested Functions

244 - Revision: Advanced Functions

Section 33 Advanced Charts......

245 - Chart Options

246 - Moving Average

247 - 3-D Chart Rotation

248 - Data Labels and Markers

249 - Stacked Bar Charts

250 - Revision: Advanced Charts

Section 34 Outlines......

251 - Creating an Outline

252 - Working with an Outline

253 - Revision: Outlines

Section 35 Objects......

254 - Drawing Tools

255 - Creating/Deleting Drawn Objects

256 - Formatting Objects

257 - Grouping Objects

258 - Aligning and Rotating Objects

259 - Inserting WordArt

260 - Inserting Objects

261 - Revision: Objects

Section 36 Data Tables......

262 - One Input Data Table

263 - Two-Input Data Table

264 - Revision: Data Tables

Section 37 Data Analysis......

265 - Data Analysis

266 - Descriptive Statistics

267 - Correlation

268 - Histograms

269 - Moving Averages from a Data Range

270 - Random Number Generator

271 - Sampling

272 - Revision: Data Analysis

Section 38 Styles......

273 - Styles

274 - Using Styles

275 - Deleting a Style

276 - Revision: Styles

Section 39 Data Consolidation......

277 - Data Consolidation

278 - Data Consolidation by Position

279 - Data Consolidation by Category

280 - Editing Reference Areas

281 - Revision: Data Consolidation

Section 40 Macros......

282 - Macros and Security

283 - Recording a Macro

284 - Running Macros

285 - Assign Macro to a Button on the Quick Access Toolbar

286 - Adding a Macro to a Worksheet Object

287 - Editing Macro Buttons

288 - Editing Macros

289 - Revision: Macros

Section 41 Auditing......

290 - Auditing

291 - Tracing Precedents

292 - Tracing Dependents

293 - Tracing Errors

294 - Data Validation

295 - Watch Window

296 - Evaluate Formulas

297 - Revision: Auditing

Section 42 Forms......

298 - Forms

299 - Form Controls

300 - Create an On-Line Form

301 - Revision: Forms

Section 43 Web Pages......

302 - Publishing to the Web

303 - Updating a Web Page

304 - Hyperlinks

305 - Send To

306 - Revision: Web Pages

Section 44 Sharing Workbooks......

307 - Sharing Workbooks

308 - Track Changes

309 - Accepting or Rejecting Changes

310 - Revision: Sharing

Section 45 Importing Data......

311 - Importing Text Files

312 - Importing Data from Access

313 - Converting to Access

314 - Importing Data from an Excel File

315 - Refreshing Data

316 - Revision: Importing Data

Section 46 Designing Solutions......

317 - Spreadsheet Design

318 - Data Entry Forms

319 - Testing Formulas

320 - Testing Spreadsheets

321 - Creating a Test Plan

322 - Revision: Designing Solutions

Answers

Glossary

Index

Other Products from CiA Training

Section 32
Advanced Functions

By the end of this Section you should be able to:

Use Logical Functions

Use Date and Time Functions

Use Lookup Functions

Use Maths & Trig Functions

Use Statistical Functions

Use Text Functions

Use Financial Functions

Use Database Functions

Use Nested Functions

Exercise 235 - Logical Functions

Guidelines:

The logical function IF tests the contents of a cell and, if the logical test is met (TRUE condition), performs one action; if not (FALSE condition), it performs another.

=IF(Logical_test,Value_if_true,Value_if_false)

For instance, if the value in cell A1 is greater than 10 then multiply it by 3, if not, multiply it by 2. This is expressed as: =IF(A1>10,A1*3,A1*2)

The IF function is sometimes described as IF THEN ELSE. IF the condition is true THEN do this ELSE do that.

AND and OR are logical functions that can be either TRUE or FALSE. AND tests 2 or more conditions and if every one is satisfied returns a value of TRUE, otherwise it returns a value of FALSE. So

=AND(A1>10,B1>10,C1>10)

is only TRUE if A1 is greater than 10 and B1 is greater than 10 and C1 is greater than 10.

OR tests 2 or more conditions and if any one is satisfied returns a value of TRUE, otherwise it returns a value of FALSE. So

=OR(A1>10,B1>10,C1>10)

is TRUE if A1 is greater than 10 or B1 is greater than 10 or C1 is greater than 10.

As the functions AND and OR return TRUE or FALSE values, they are often used in conjunction with IF functions in order to return a value for the logical test based on multiple conditions.

Actions:

1.On a blank worksheet, enter the label Interest Calculation in B1.

2.Enter the label Balance in cell B3 and Interest in B4.

3.Enter any number in C3 for your bank balance.

4.The interest on your money depends on whether the balance is over or under £100. Click in cell C4 and display the Formulas tab.

5.In the Function Library group, click the Logical button and select IF.

Exercise 235 - Continued

6.Enter the following parts of the test with a mixture of pointing and typing into the Function Arguments dialog box.

7.Click OK to complete the function. The function looks at the contents of cell C3 and if less than 100, calculates the interest at 6% otherwise it calculates it at 8%.

8.The result of the function, the interest, depends on the balance. Move to C3 and enter 100. The interest is £8, the higher rate. Enter 50 and the interest is £3. Experiment, change the balance and see the interest change.

9.Enter the column of numbers 27, 8, 16, 35 in cells D5 to D8.

10.In cell F5 enter the logical function =AND(D5>10,D5<20) by keying or using Insert Function. This will return the value TRUE if both conditions (>10 and <20) are met and FALSE if they are not.

11.Copy the function from F5 to the range F6 to F8. Only one cell value (16) meets both conditions and is therefore TRUE.

12.In cell H5 enter the logical function =OR(D5<10,D5>20) by keying or using Insert Function. This will return the value TRUE if either condition (<10 and >20) is met and FALSE if not.

13.Copy the function from H5 to the range H6 to H8. Only one cell value (16) does not meet either condition and is FALSE, the others are TRUE.

14.Close the workbook without saving.

Exercise 236 - Date and Time Functions

Guidelines:

Date and Time functions deal with the processing and reformatting of all data relating to dates and times.

There are several functions for use purely with dates and times.

DATEReturns the number for a particular day, e.g. DATE(92,4,13) returns 33707, the number of days from 1st Jan 1900 to 13th Apr 1992 when formatted as a number.

DAY, MONTH,YEARConverts a date to a number representing the day, month, or year, e.g. DAY(“23/11/67”) would be 23.

NOWUsed as NOW(). Returns the current date and time as a number, and is updated as the worksheet is calculated.

DATEVALUEConverts the date as text to a number, e.g. DATEVALUE(“21-Sept-49”) returns 18162.

TODAYUsed as TODAY(). Returns the current date as a number and is updated as the worksheet is calculated.

WEEKDAYConverts a number to an integer representing the day of the week from 1 (Sunday) to 7 (Saturday), e.g. WEEKDAY(“21-Sept-49”) returns 4, Wednesday.

TIMEUsed as TIME(hour,minute,second). When formatted as a number returns a value in the range 0 to 0.99999999, representing a fraction of a day, e.g. TIME(16,48,10) returns 0.700115741.

TIMEVALUEReturns a number as a fraction of the day, e.g.TIMEVALUE(“22nd-Aug-67 6:35 am”) returns .274305556.

HOUR, MINUTE,SECONDConverts a time into hours, minutes, or seconds, e.g. HOUR(“6:35pm”) returns 18.

There are also two key presses which automatically insert the current date and time.

Ctrl ;>Inserts the current date.

Ctrl Shift ;Inserts the current time.

Exercise 236 - Continued

Actions:

1.Open a new workbook. This exercise shows some of the above functions in action.

2.In B2, enter the label Timeas a function. In D2, enter Time as a value.

3.In B4, enter =NOW().

4.To format the cellB4, click on it then click the Number group dialog box launcher on the Home tab.

5.The Format Cells dialog box is displayed, showing the Number tab. Select the Time category and the13:30:55 format. The cell is formatted in the form hh:mm:ss.

6.In D4, press Ctrl Shift ; to enter the current time.

7.In B8, enter the function =TIME(8,30,0) and format to display as hh:mm:ss.

8.In B12, enter =B4-B8 to calculate an elapsed time.

9.The Time as a function and Time as a value should now appear as different times. This is because the function NOW() is updated as the worksheet is calculated, while using Ctrl Shift ; puts a value in the sheet, which is not updated.

10.In F2, enter =TODAY(). Widen the column if necessary.

11.In F5, enter =DATE( then your birthday as numbers in the form yy,mm,dd followed by a ).

12.In F8, enter =F2-F5.This shows your age in days (widen the column if you are very old!!).

13.In H5, enter =WEEKDAY(F5). This gives a number corresponding to the day of the week on which you were born (Sunday = 1, Saturday = 7).

14.Close the workbook without saving.

Exercise 237 - Lookup & Reference Functions

Guidelines:

Lookup & Reference functions deal mainly with data in tables or ranges, for example, retrieving values or transposing vertical and horizontal ranges.

A Lookup table consists of a selection of bands, or intervals, where a given value can be found. There are two functions, HLOOKUP, which searches a horizontal table and VLOOKUP, which searches a vertical table.

Actions:

1.Open the workbook Discount. The worksheet consists of a discount calculation at the top and two lookup tables at the bottom, one horizontal and one vertical, containing the same data. The discount to be used (in D8) depends directly on the number of items bought.

2.To look up the discount from the horizontal table using the HLOOKUP function, click in cell D8.

3.On the Formulas tab, click the Lookup & Referencebutton and select the function nameHLOOKUP.

4.The Lookup_value is cell D4 (number bought). The Table_array is C14:I15 (the table without the labels) and the Row_index_num is 2 (to return the value from the 2nd row of the table). Move the dialog box as required.

Note:Setting the Range_lookup to FALSE causes the function to return a value only if there is an exactmatch between the Lookup_value and the table entry.

Exercise 237 - Continued

5.Click OK. The value returned is 10%, corresponding to selling between 5 and 9 items.

6.Change the number bought in D4 to 23. The Discount % changes, and so does the Discount Price.

7.Delete the contents of cell D8.

8.To use the VLOOKUP function, click in cell D8.

9.Click theLookup & Referencebutton and select VLOOKUP (this function is similar to HLOOKUP except the base data is stored in columns).

10.The Lookup_value is cell D4 (number bought). The Table_array is B19:C25 (the table without the labels) and the Col_index_num is 2 (to return the value from the 2nd column of the table).

11.Click OK to complete the function.

12.Change the number bought in D4 to 52. The Discount % changes to 45% the Discount Price is £2000.57.

13.Close the workbook without saving

Exercise 238 - Statistical Functions

Guidelines:

Statistical functions deal with analysing numerical data, from simple counting and averaging to calculating complex distribution parameters.

Some useful functions are: AVERAGE, COUNT, MAX, MIN, COUNTIF, STDEV(standard deviation), FREQUENCYand TREND.

All functions are used in a similar way. Select the function, if help is required click the Help on this function link. As an example this exercise demonstrates COUNTIF. This function counts numeric items that match a set condition, e.g. to count the number of clients that owe more than £100.

Actions:

1.Open the workbook Invoice.

2.Click in cell D16 and enter the label Invoices under £500.

3.Select cell E16 and click the MoreFunctions button from the Formulas tab.

4.Select Statistical and thenCOUNTIFfrom the list.

5.The Function Arguments for COUNTIFare displayed.

6.Select the Range as E6:E14.

7.Set the criteria in the Criteria box as <500.

8.Click OK. Check the Formula Bar for the formula (the speech marks are added automatically around the criteria). The cells that match the condition are counted.

9.Leave the workbook open.

Exercise 239 - Maths & Trig Functions

Guidelines:

Math & Trig functions deal with processing individual numerical data, from simple rounding to complex trigonometric calculations. Some useful functions are: INT, ROUND, SUMIFand SUBTOTAL.

SUBTOTALperforms various functions on a specified vertical range of cells. It uses a parameter Function_num to define which function to use. The available parameter values are:

1AVERAGE5MIN9SUM

2COUNT6PRODUCT10VAR

3COUNTA7STDEV11VARP

4MAX8STDEVP

In a filtered list, the SUBTOTALfunction only acts on the displayed rows.

Actions:

1.The workbook Invoice should be open, if not open it.

2.In the previous exercise cells matching a set criteria were counted. These cells can also be summed using SUMIF. In cell D17, enter the label Small invoices total.

3.In cell E17 click the Math & Trigbutton and scroll down the list and select SUMIF.

4.In the SUMIF box, select the Range as E6:E14 and set the Criteria condition as <500.

5.Click OK to complete the function. The invoices that are under £500 are summed. The formula is similar to COUNTIFexcept that the values that match the criteria are added. So the 4 values counted in the previous exercise are now added here. The total of these four values is 1454.5.

6.Close the workbook without saving.

7.Open the workbook List. The worksheet data ends at row 225.

8.Move to cell G226. Click the Math & Trig and selectSUBTOTAL.

9.The SUBTOTAL dialog box is displayed. Function_num is the number from the list above. Type 2 (for count).

10.Ref1 is the range to act on. Type or select G6:G225 (further references could be added if required, using Ref2, etc.).

Exercise 239 - Continued

11.Click OK to complete the function. There should be 44 replies.

12.In cell H226, enter the formula =COUNT(G6:G225). Both formulas show 44 because both are counting all the replies in the list.

13.Click in the main list. Display the Data tab and click the Filter button to apply the AutoFilter. Filter Town and select Sunderland. The Subtotal function now only shows the number of replies in the filtered list (17), whereas the Count function still shows the total for the whole list.

14.In cell F228, enter the label Percentage and in G228, the formula =G226/H226. Format the cell as a percentage, with 0 decimal places. This displays the replies from the filtered Town as a percentage of the total replies. What is the percentage from Sunderland?

15.In cell E226, enter the SubTotal function to find the Average age of the people in the filtered list. The Function_num is 1 and Ref1 is the full range E6:E225.

16.Click OK to complete the function. What is the average age?