ECDL Advanced – Excel

Who is the course for?

The ECDL / ICDL Advanced Spreadsheets certification presents candidates with the opportunity to bring their spreadsheet skills to an expert level. This allows the candidate to use the full potential of the spreadsheet application to produce higher quality management information.

What will I study?

• Apply autoformat/table style to: Cell range/content, Formats, Spreadsheets, Bars, Worksheets
• Use date and time functions: today, now, day, month, year.
• Use mathematical functions: rounddown, roundup, sumif.
• Use statistical functions: countif, countblank, rank.
• Use text functions: left, right, mid, trim, concatenate.
• Use financial functions: fv, pv, pmt.
• Use lookup functions: vlookup, hlookup.
• Use database functions: dsum, dmin, dmax, dcount, daverage.
• Create a two-level nested function.
• Use a 3-D reference within a sum function.
• Use mixed references in formulas.
• Create a combined column and line chart.
• Add a secondary axis to a chart.
• Change the chart type for a defined data series.
• Add, delete a data series in a chart.
• Re-position chart title, legend, data labels.
• Change scale of value axis: minimum, maximum number to display, major interval.
• Change display units on value axis without changing data source: hundreds, thousands, millions.
• Format columns, bars, plot area, chart area to display an image.
• Create, modify a pivot table/datapilot.
• Modify the data source and refresh the pivot table/datapilot.
• Filter, sort data in a pivot table/datapilot.
• Use one-input, two-input data tables/multiple operations tables.
• Sort data by multiple columns at the same time.
• Create a customized list and perform a custom sort.
• Automatically filter a list in place.
• Apply advanced filter options to a list.
• Use automatic sub-totalling features.
• Expand, collapse outline detail levels.
• Create named scenarios.
• Show, edit, delete scenarios.
• Create a scenario summary report.
• Set, edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time.
• Enter input message and error alert.
• Trace precedent, dependent cells. Identify cells with missing dependents.
• Show all formulas in a worksheet, rather than the resulting values.
• Insert, edit, delete, show, hide comments/notes.
• Name cell ranges, delete names for cell ranges.
• Use named cell ranges in a function.
• Use paste special options: add, subtract, multiply, divide.
• Use paste special options: values /numbers, transpose.
• Create a spreadsheet based on an existing template.
• Modify a template.
• Insert, edit, remove a hyperlink.
• Link data within a spreadsheet, between spreadsheets, between applications.
• Update, break a link.
• Import delimited data from a text file.
• Record a simple macro like: change page setup, apply a custom number format, apply autoformats to a cell range, insert fields in worksheet header, footer.
• Run a macro.
• Assign a macro to a custom button on a toolbar.
• Turn on, off track changes. Track changes in a worksheet using a specified display view.
• Accept, reject changes in a worksheet.
• Compare and merge spreadsheets.
• Add, remove password protection for a spreadsheet: to open, to modify.
• Protect, unprotect cells, worksheet with a password.
• Hide, unhide formulas.

How will the course be delivered?

The 10 week course is delivered in an evening.

Course Entry Requirements

In order to take an Advanced ECDL module you do not need to have passed ECDL Level 2, however, you do need Intermediate skills as the course builds upon the knowledge and skills gained from basic ECDL. Ideally you should also be using the software as part of your daily work. By booking onto this Advanced Excel course, you are agreeing that you are proficient in the following:

Working with Spreadsheets – Open, close a spreadsheet application. Open, close spreadsheets, Create a new spreadsheet based on default template, Save a spreadsheet to a location on a drive. Save a spreadsheet under another name to a location on a drive, Save a spreadsheet as another file type like: template, text file, software specific file extension, version number, Switch between open spreadsheets, Set basic options/preferences in the application: user name, default folder to open, save spreadsheets, Use available Help functions, Use magnification/zoom tools, Display, hide built-in toolbars. Restore, minimize the ribbon.

Cells – Insert, Select, Understand that a cell in a worksheet should contain only one element of data, (for example, first name detail in one cell, surname detail in adjacent cell), Recognise good practice in creating lists: avoid blank rows and columns in the main body of list, insert blank row before Total row, ensure cells bordering list are blank, Enter a number, date, text in a cell, Select a cell, range of adjacent cells, range of non-adjacent cells, entire worksheet, Edit cell content, modify existing cell content, Use the undo, redo command, Use the search command for specific content in a worksheet, Use the replace command for specific content in a worksheet, Sort a cell range by one criterion in ascending, descending numeric order, ascending, descending alphabetic order, Copy the content of a cell, cell range within a worksheet, between worksheets, between open spreadsheets, Use the autofill tool/copy handle tool to copy, increment data entries, Move the content of a cell, cell range within a worksheet, between worksheets, between open spreadsheets, Delete cell contents.

Managing Worksheets – Rows and Columns, Select a row, range of adjacent rows, range of non-adjacent rows, Select a column, range of adjacent columns, range of non-adjacent columns, Insert, delete rows and columns, Modify column widths, row heights to a specified value, to optimal width or height, Freeze, unfreeze row and/or column titles, Switch between worksheets, Insert a new worksheet, delete a worksheet, Recognize good practice in naming worksheets: use meaningful worksheet names rather than accept default names, Copy, move, rename a worksheet within a spreadsheet.

Formulas and Functions – Arithmetic Formulas, Recognise good practice in formula creation: refer to cell references rather than type numbers into formulas, Create formulas using cell references and arithmetic operators (addition, subtraction, multiplication, division), Identify and understand standard error values associated with using formulas: #NAME?, #DIV/0!, #REF!, Understand and use relative, absolute cell referencing in formulas, Use sum, average, minimum, maximum, count, counta, round functions, Use the logical function if (yielding one of two specific values) with comparison operator: =, >, <.

Formatting – Numbers/Dates, Format cells to display numbers to a specific number of decimal places, to display numbers with, without a separator to indicate thousands, Format cells to display a date style, to display a currency symbol, Format cells to display numbers as percentages, Change cell content appearance: font sizes, font types, Apply formatting to cell contents: bold, italic, underline, double underline, Apply different colours to cell content, cell background, Copy the formatting from a cell, cell range to another cell, cell range, Alignment, Border Effects, Apply text wrapping to contents within a cell, cell range, Align cell contents: horizontally, vertically. Adjust cell content orientation, Merge cells and centre a title in a merged cell, Add border effects to a cell, cell range: lines, colours.

Charts – Create different types of charts from spreadsheet data: column chart, bar chart, line chart, pie chart, Select a chart. Change the chart type, Move, resize, delete a chart, Add, remove, edit a chart title, Add data labels to a chart: values/numbers, percentages, Change chart area background colour, legend fill colour, Change the column, bar, line, pie slice colours in the chart, Change font size and colour of chart title, chart axes, chart legend text.

Prepare Outputs – Change worksheet margins: top, bottom, left, right, Change worksheet orientation: portrait, landscape. Change paper size, Adjust page setup to fit worksheet contents on a specified number of pages, Add, edit, delete text in headers, footers in a worksheet, Insert and delete fields: page numbering information, date, time, file name, worksheet name into headers, footers, Check and correct spreadsheet calculations and text.

Turn on, off display of gridlines, display of row and column headings for printing purposes, Apply automatic title row(s) printing on every page of a printed worksheet, Preview a worksheet, Print a selected cell range from a worksheet, an entire worksheet, number of copies of a worksheet, the entire spreadsheet, a selected chart.

If you are not proficient with any of the above, please contact us before booking onto the course.

The college operates a no refunds policy as detailed below. Cancellations and transfer requests are subject to the following conditions:

• More than 4 weeks notice – no charge
• 4 weeks notice – 30% of the cost
• 2-3 weeks notice – 50% of the cost
• 1-2 weeks notice – 75% of the cost
• Less than 1 weeks notice – 100% of the cost

All cancellations must be made in writing and are subject to a £10 administration charge. The full fee’s policy is available on request.

What will I get at the end of the course?

ECDL Unit Certificate upon successful achievement following examination.

What can I do next?

ECDL Advanced – Word

EXCL Advanced – Access

How much does it cost?

This 10 week course is priced at £275.00 per delegate (including all tuition, Registration, ECDL Test fees (First Attempt) and Certification). (Prices Subject to Change)