Objectives

The learner will be able to:

Assessment Criteria

The learner has achieved this objective because he/she can:

1. Format Cells

1. Apply an autoformat/table style to a cell range.

2. Apply conditional formatting based on cell content.

3. Create and apply custom number formats.

2. Format Worksheets

1. Copy, move worksheets between spreadsheets.

2. Split a window. Move, remove split bars.

3. Hide, show rows, columns, worksheets.

3. Use Functions and Formulas

1. Use date and time functions: today, now, day, month, year.

2. Use mathematical functions: rounddown, roundup, sumif.

3. Use statistical functions: countif, countblank, rank.

4. Use text functions: left, right, mid, trim, concatenate.

5. Use financial functions: fv, pv, pmt.

6. Use lookup functions: vlookup, hlookup.

7. Use database functions: dsum, dmin, dmax, dcount, daverage.

8. Create a two-level nested function.

9. Use a 3-D reference within a sum function.

10. Use mixed references in formulas.

4. Create Charts

1. Create a combined column and line chart.

2. Add a secondary axis to a chart.

3. Change the chart type for a defined data series.

4. Add, delete a data series in a chart.

5. Format Charts

1. Re-position chart title, legend, data labels.

2. Change scale of value axis: minimum, maximum number to display, major interval.

3. Change display units on value axis without changing data source: hundreds, thousands, millions.

4. Format columns, bars, plot area, chart area to display an image.

6. Analyse using Tables

1. Create, modify a pivot table/datapilot.

2. Modify the data source and refresh the pivot table/datapilot.

3. Filter, sort data in a pivot table/datapilot.

4. Automatically, manually group data in a pivot table/datapilot and rename groups.

5. Use one-input, two-input data tables/multiple operations tables.

7. Analyse using Sorting and Filtering

1. Sort data by multiple columns at the same time.

2. Create a customized list and perform a custom sort.

3. Automatically filter a list in place.

4. Apply advanced filter options to a list.

5. Use automatic sub-totalling features.

6. Expand, collapse outline detail levels.

8. Analyse using Scenarios

1. Create named scenarios.

2. Show, edit, delete scenarios.

3. Create a scenario summary report.

9. Validate

1. Set, edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time.

2. Enter input message and error alert.

10. Audit

1. Trace precedent, dependent cells. Identify cells with missing dependents.

2. Show all formulas in a worksheet, rather than the resulting values.

3. Insert, edit, delete, show, hide comments/notes.

11. Enhance Productivity using named Cells

1. Name cell ranges, delete names for cell ranges.

2. Use named cell ranges in a function.

12. Enhance Productivity using Paste Special

1. Use paste special options: add, subtract, multiply, divide.

2. Use paste special options: values /numbers, transpose.

13. Enhance Productivity using Templates

1. Create a spreadsheet based on an existing template.

2. Modify a template.

14. Enhance Productivity by Linking, Embedding and Importing

1. Insert, edit, remove a hyperlink.

4. Import delimited data from a text file.

15. Enhance Productivity through Automation

1. 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.

2. Run a macro.

3. Assign a macro to a custom button on a toolbar.

16. Use Collaborative Editing - Tracking and Reviewing

1. Turn on, off track changes. Track changes in a worksheet using a specified display view.

2. Accept, reject changes in a worksheet.