ECDLA L3 Spreadsheet Software Excel 2010 S2.0 V1
ECDL AdvancedEuropean Computer Driving Licence Advanced Spreadsheet SoftwareBCS ITQ Level 3Using Microsoft Excel 2010Syllabus Version 2.0This training, which has been approved by BCS, The Chartered Institutefor IT, includes exercise items intended to assist learners in their trainingfor an ECDL Certification Programme. These exercises are not ECDLcertification tests. For information about Approved Centres in the UKplease visit the BCS website at www.bcs.org/ecdl.Release ECDL271 UKv1
ECDL AdvancedAdvanced Spreadsheet SoftwareContentsSECTION 1 INTRODUCTION . 81 - SPREADSHEET DESIGN . 92 - TECHNIQUES TO USE. 113 - REVISION: INTRODUCTION . 12SECTION 2 FORMATTING . 134 - SPLIT A WINDOW . 145 - CONDITIONAL FORMATTING . 156 - FORMAT AS TABLE . 177 - PASTE SPECIAL . 188 - TRANSPOSING DATA . 209 - COPYING AND MOVING SHEETS. 2110 - REVISION: FORMATTING . 23SECTION 3 PROTECTION . 2411 - PROTECTION . 2512 - WORKSHEET & CELL PROTECTION . 2613 - HIDING ROWS & COLUMNS . 2814 - HIDING FORMULAS . 2915 - WORKBOOK PROTECTION . 3016 - HIDING WORKSHEETS & WORKBOOKS . 3217 - REVISION: PROTECTION . 33SECTION 4 CELL COMMENTS. 3418 - CELL COMMENTS. 3519 - DISPLAY COMMENTS . 3620 - CREATE, EDIT AND DELETE COMMENTS . 3721 - REVISION: CELL COMMENTS . 38SECTION 5 NAMES. 3922 - NAMES . 4023 - USING NAMES IN FORMULAS . 4224 - USING GO TO WITH NAMES . 4325 - REVISION: NAMES . 44SECTION 6 TEMPLATES. 4526 - CREATING A TEMPLATE . 4627 - USING A TEMPLATE . 4728 - EDITING A TEMPLATE . 4829 - REVISION: TEMPLATES . 49SECTION 7 FORMULAS . 5030 - DISPLAY FORMULAS . 5131 - FORMULAS THAT PRODUCE ERRORS. 5232 - MIXED REFERENCING . 5333 - CUSTOM NUMBER FORMATS . 5534 - REVISION: FORMULAS . 57Excel 20105 CiA Training Ltd 2010
ECDL AdvancedAdvanced Spreadsheet SoftwareSECTION 8 OUTLINES. 5835 - CREATING AN OUTLINE . 5936 - WORKING WITH AN OUTLINE . 6137 - REVISION: OUTLINES . 63SECTION 9 SCENARIOS . 6438 - CREATING SCENARIOS . 6539 - USING, EDITING AND DELETING SCENARIOS . 6640 - SCENARIO SUMMARY REPORT . 6741 - REVISION: SCENARIOS . 68SECTION 10 LINKING & IMPORTING . 6942 - LINKING . 7043 - CREATING LINKS . 7144 - LINKING BETWEEN WORKBOOKS . 7345 - LINKING TO A WORD DOCUMENT . 7646 - HYPERLINKS . 7847 - IMPORTING DELIMITED DATA. 8048 - REVISION: LINKING & IMPORTING . 82SECTION 11 SORTING. 8349 - SORTING . 8450 - MULTIPLE COLUMN SORTS . 8551 - CUSTOM LISTS AND SORTS . 8752 - REVISION: SORTING . 89SECTION 12 LISTS . 9053 - LISTS . 9154 - FILTERING LISTS . 9255 - AUTOFILTER . 9356 - CUSTOM AUTOFILTER . 9457 - ADVANCED FILTERING . 9558 - EXTRACTING FILTERED DATA . 9859 - ADDING SUBTOTALS . 9960 - REVISION: LISTS . 101SECTION 13 PIVOT TABLES . 10261 - PIVOTTABLES . 10362 - FILTERING A PIVOTTABLE . 10563 - GROUPING AND SORTING IN PIVOTTABLES. 10664 - REVISION: PIVOTTABLES . 109SECTION 14 FUNCTIONS. 11065 - FUNCTIONS . 11166 - DATE AND TIME FUNCTIONS. 11267 - LOOKUP FUNCTIONS . 11468 - MATHEMATICAL FUNCTIONS . 11669 - STATISTICAL FUNCTIONS . 11770 - TEXT FUNCTIONS . 11971 - FINANCIAL FUNCTIONS . 12172 - DATABASE FUNCTIONS . 123Excel 20106 CiA Training Ltd 2010
ECDL AdvancedAdvanced Spreadsheet Software73 - NESTED FUNCTIONS. 12574 - REVISION: FUNCTIONS . 126SECTION 15 CHARTS. 12775 - FORMATTING CHARTS . 12876 - MODIFYING CHARTS . 13177 - LINE-COLUMN CHARTS . 13378 - ADDING IMAGES TO CHARTS . 13479 - REVISION: CHARTS . 136SECTION 16 DATA TABLES . 13780 - ONE INPUT DATA TABLE . 13881 - TWO INPUT DATA TABLE. 13982 - REVISION: DATA TABLES . 140SECTION 17 MACROS . 14183 - MACROS . 14284 - RECORDING A MACRO . 14385 - RUNNING A MACRO . 14586 - ASSIGNING A MACRO TO THE QUICK ACCESS TOOLBAR . 14787 - REVISION: MACROS . 148SECTION 18 AUDITING. 14988 - AUDITING . 15089 - TRACING PRECEDENTS . 15190 - TRACING DEPENDENTS . 15291 - TRACING ERRORS . 15392 - DATA VALIDATION . 15493 - REVISION: AUDITING . 158SECTION 19 TRACKING CHANGES. 15994 - TRACKING CHANGES IN A WORKSHEET . 16095 - SHARED WORKBOOKS . 16396 - MERGING WORKBOOKS . 16597 - REVISION: TRACKING . 167ANSWERS . 168GLOSSARY . 172INDEX . 174RECORD OF ACHIEVEMENT MATRIX . 176OTHER PRODUCTS FROM CIA TRAINING . 180Excel 20107 CiA Training Ltd 2010
ECDL AdvancedSection 2 FormattingSection 2FormattingBy the end of this Section you should be able to:Split WindowsUse Conditional FormattingUse Format as a TableUse Paste SpecialTranspose DataCopy and Move SheetsTo gain an understanding of the above features, work through the DrivingLessons in this Section.For each Driving Lesson, read the Park and Read instructions, withouttouching the keyboard, then work through the numbered steps of theManoeuvres on the computer. Complete the Revision Exercise(s) at the end ofthe section to test your knowledge.Excel 201013 CiA Training Ltd 2010
ECDL AdvancedSection 2 FormattingDriving Lesson 4 - Split a WindowPark and ReadSplit is similar to freeze panes except that the individual panes can be scrolledto show different information in each pane.Manoeuvres1.Open the workbook Retail (see page 4 - Downloading the Data Files forthe location).2.Split is created at the active cell position using the Ribbon. Click on cellF8. Display the View tab and then click the Split button, in theWindow group. The worksheet is now split into four areas, all displayingthe same worksheet.3.Move the active cell to the right and up a few cells, using the arrow keys.4.Use the four scroll bars to display different parts of the worksheet tocreate four different views of the same worksheet - very useful if using alarge worksheet.5.The split can be moved by clicking on the split lines and dragging themappears. Drag the vertical split to the left to display only 2whencolumns in the left panes.6.To remove the Split, click the Split button again.7.Split can also be created using the split boxes at the top or right of theScroll Bars.HorizontalSplit boxVerticalSplit box8.Drag the Horizontal Split button at the top of the vertical scroll bar downto between rows 7 and 8. Drag the Vertical Split button at the right of thehorizontal scroll bar down to between columns E and F. This achieves thesame split as created using the Ribbon.9.An alternative method to remove a single split is to double click on thesplit line or the split box. Remove the horizontal and then the vertical split.10. Leave the workbook Retail open for the next Driving Lesson.Excel 201014 CiA Training Ltd 2010
ECDL AdvancedSection 2 FormattingDriving Lesson 5 - Conditional FormattingPark and ReadAs well as applying formatting to certain cells, it is possible to apply differentformatting to cells depending on the values within those cells. Selected cellscan be compared to a value, or the results of a formula, to decide which formatshould be used. This is called Conditional Formatting.Multiple conditions can be used to determine the formatting for the same cell,so for example, a cell could be coloured red if it is below a certain value andblue if it is greater than another value.Manoeuvres1.Open the workbook Retail, if it is not already open.2.Highlight the Turnover figures, the range B4:M4.3.With the Home tab displayed, from the Styles group, select ConditionalFormatting.4.Select New Rule. The New Formatting Rule dialog box is displayed.5.From the Select a Rule Type box click on Format only cells thatcontain.6.In the drop down criteria box (between) select less than or equal to.7.Enter the value 19000 in the next box.continued overExcel 201015 CiA Training Ltd 2010
ECDL AdvancedSection 2 FormattingDriving Lesson 5 - ContinuedIf entering a formula in the value box remember to start with 8.Click the Format button and select the font colour red. Click OK. Click OKagain to apply the rule.9.To add another condition to the same range, click the ConditionalFormatting icon and select New Rule. Select Format only cells thatcontain, again. Select greater than and enter 25000 in the next box.Format the text to be bold and blue. Click OK and OK again to apply theformatting.10. Highlight the range B14:M14, apply the conditional formatting, values lessthan the average of the range Average( B 14: M 14). Format therange with a pale yellow cell shading using the Fill tab and More Colorsoption. Click OK, OK and OK again.11. To remove Conditional Formatting, select the range B4:M4, clickConditional Formatting and select Manage Rules.The current selection has two rules applied to it. To view all the rules, selectThis Worksheet from the Show formatting rules for box.12. Select the Cell Value 25000 rule and click the Delete Rule button,. Click OK. All values of over 25000 should now not be blueor bold.13. Experiment with adding and removing conditional formats to highlightresults in a way that had not been possible before.14. Close the workbook without saving.Excel 201016 CiA Training Ltd 2010
ECDL AdvancedSection 2 FormattingDriving Lesson 6 - Format as TablePark and ReadThere is a set of pre-defined worksheet formats to enhance the appearance ofa worksheet, via the Home tab Styles group, Format as Table command.Manoeuvres1.Open the workbook Budget.2.Highlight the range A1:N14 and on the Home tab, in the Styles group,click Format as Table.3.There is a list of the available Table Formats, covering Light, Mediumand Dark options. Scroll through the list of Table Formats. Select TableStyle Light 2.4.Click OK to confirm the range that is to be formatted.5.The sheet is now formatted in the Light 2 style. A Design tab is displayedon the ribbon, showing various groups for different types of formatting.6.To change the table format, select an option from the Table Styles groupon the Design tab. Click the More drop down (shown below) to display allthe available styles. The sheet displays each format as the mouse passesover it.More7.To remove the table format, with the range A1:N14 selected, click theMore button in the Table Styles group on the Design tab and then selectClear.8.With a filled cell selected, display the Data tab and click the Filter buttonto clear the filter buttons.9.Close the workbook without saving.Excel 201017 CiA Training Ltd 2010
ECDL AdvancedSection 2 FormattingDriving Lesson 7 - Paste SpecialPark and ReadWhen using Copy and Paste or Cut and Paste, the default is to paste the cellexactly as it was originally. Paste Special is used to paste cell informationwhen a complete copy is not required. For example, Paste Special can: Paste only formulas, values or formats. Pasting values from cells thatcontain formulas is a way of fixing the data, as it will never then berecalculated. Combine ranges using an operation, e.g. adding, subtracting, etc. Transpose ranges (change a spreadsheet round by converting rows tocolumns and columns to rows). Paste links to the original data (covered in a later section).Manoeuvres1.Open the workbook Finances.2.Highlight the range A1:N1. Change the range to italic and the font colourto blue.3.To paste this formatting to another range, click the cell A1 and in theHome tab, Clipboard group, click the Copy button.4.Highlight the range A2:A16 and select the Paste drop down arrow.5.Many paste options are displayed in the drop down panel, but for thepurposes of this exercise, select Paste Special to display the PasteSpecial dialog box.6.Under Paste select the Formats option and click OK to paste theformatting. The cell border from A1 is pasted as well. Remove theunwanted borders to match those in column B.continued overExcel 201018 CiA Training Ltd 2010
ECDL AdvancedSection 2 FormattingDriving Lesson 7 - Continued7.Open the workbook Outgoings.8.Select the range A1:L10 and click on the Copy button.9.Click on the Excel button on the Taskbar and then select the Financesworkbook to make it active.10. Click on cell B5. Click the Paste button to paste the contents normally.11. Data can be pasted using a mathematical operation. Open the file Incomeand highlight the range A1:L2, the income figures.12. Click the Copy button.13. Switch back to Finances using the Taskbar.14. The income figures are to go into the range B2:M3 but this range alreadycontains data, which would be overwritten if a normal paste was used. Tokeep the original data, select cell B2 then click the Paste drop down andselect Paste Special.15. Under Operation click Add to add the two sets of figures together, thenclick OK. The data is now combined.The Add operation was used in this example. Subtract, Multiply and Dividework in the same way.16. It may be required to convert formulas to values because the numbers arefinal, e.g. a VAT return or expenditure after the month end, etc. Using theFinances workbook, January has ended and the figures in column B arerequired to be converted to values. Check the cells B4, B15 and B16 tosee that they contain formulas.17. Select the range B2:B16.18. Click Copy, then click the Paste drop down and select Paste Special.19. Under Paste, select the Values option and click OK. Click away from theselection.20. Check that the cells B4, B15 and B16 contain values.Remember that this process removes formulas and therefore the worksheetcannot be used again. If repeated use is required, create and use a template.21. Save the workbook as Finances2 and close it.22. Close the workbooks Income and Outgoings without saving.Excel 201019 CiA Training Ltd 2010
ECDL AdvancedSection 2 FormattingDriving Lesson 8 - Transposing DataPark and ReadIt is sometimes necessary to change the way data is stored in a worksheet.Data stored in rows may be needed in columns and vice versa. This process iscalled Transposing Data.Manoeuvres1.Open the workbook Transpose. The Result column is needed in a row.2.Select the range B3:C17, then select to Copy the range.3.Move to A20 and click the Paste drop down and select Paste Special.Check the Transpose box.4.Click OK. Press Esc to exit the copy command.5.The data is now shown in rows. Notice that the formats, i.e. the borders,have also been copied. This could have been avoided by selecting topaste just Values in the Paste Special dialog box.6.Transpose the range B3:C17, with values only, to A23.7.Close the workbook without saving.8.Open the workbook Budget.9.Transpose the range A1:N14 to cell A18.10. The borders are not appropriate. Undo the paste and transpose the rangeagain, this time pasting only values.11. Highlight the new range and add new borders as appropriate.12. Check the formulas and print a landscape copy of the worksheet on onepage.13. Close the workbook without saving.Excel 201020 CiA Training Ltd 2010
ECDL AdvancedSection 2 FormattingDriving Lesson 9 - Copying and Moving SheetsPark and ReadSheets within a workbook can be moved or copied within the same workbook orto a different workbook.Manoeuvres1.Open the workbook Divisions.2.Sheets can be moved and copied within the same workbook by draggingthe sheet tab with the mouse. Move the North Midlands sheet tobetween South Wales and Midlands by clicking and dragging the tab tothe correct position (the black triangle always shows where the sheet willbe inserted). More sheets will appear when the cursor reaches the end ofthe displayed sheets.3.Move the North West sheet to be
Advanced Spreadsheet Software BCS ITQ Level 3 Using Microsoft Excel 2010 Syllabus Version 2.0 This training, which has been approved by BCS, The Chartered Institute for IT, includes exercise items intended to assist learners in their training for an ECDL Certification Programme. These exercises are not ECDL certification tests.
Excel 5.0 Excel 5.0 1993 Excel 5.0 1993 Excel 7.0 Excel 95 1995 Excel 8.0 Excel 97 1997 Excel 98 1998 Excel 9.0 Excel 2000 1999 Excel 2001 2000 Excel 10.0 Excel XP 2001 Excel v.X 2001 Excel 11.0 Excel 2003 2003 Excel 2004 2004 2.1.2 Worksheet Document Definition: Worksheet Document A worksheet document consists of a single sheet only.
Converting Excel to XML The first thing you need is, of course, an Excel spreadsheet. Below, we have an example spreadsheet with six columns and 26 rows of data. The columns separate the last names, first names, ages, majors, GPAs, and school year of each student listed in the spreadsheet. Step 1 - Create your Excel spreadsheet. excel to xml
What is Microsoft Excel 2010? Microsoft Excel is a spreadsheet program. The version covered in this tutorial is Excel 2010. Excel 2010 is slightly different than Excel 2007 and quite different than Excel 2003. The function of a spreadsheet is to store and manipulate data, in
Excel 2007 - Part I: Getting Started I. Introduction What is Microsoft Excel 2007? Microsoft Excel is a spreadsheet program. The version covered in this tutorial is Excel 2007. Excel 2007 is different than Excel 2000. The function of a spreadsheet is to store and manipulate data, in particular numerical data.
Spreadsheet graphs for Excel 2007 Nuffield Free-Standing Mathematics Activity ‘Spreadsheet graphs.xlsx’ Student sheets for Excel 2007 Copiable page 1 of 5 . Nuffield Free-Standing Mathematics Activity ‘Spreadsheet graphs.xlsx’ Student sheets for Excel 2007 Copiable page 5 of 5
Graph (Spreadsheet, digitizer, online graphing tools) Spreadsheet & Data Processing (Calc, excel, online spreadsheet tools - Zoho Office, Google spreadsheet) Checklist (Word Processing, survey tools, online polls, Spreadsheet) Chart (Spreadsheet, digitizer, mind mapping tools online
Presenter: Hello students, Welcome to this learning session on spreadsheet. Today we are going to learn about how to get started with Spreadsheet. Slide Title: Lesson Contents Presenter: In this video, you will learn about What a Spreadsheet is? What is a Spreadsheet Software? Examples of Spreadsheet Software.
brother’s life ended in death by the hands of his brother. We are going to see what the Holy Spirit revealed that caused the one to murder his flesh and blood. We are also going to see God’s expectation and what he needed to operate in as his brother’s keeper. My desire is for us to all walk away with a greater burden for each other as we see each other as ourselves and uphold each other .
