Training: ICDL ADVANCED – Spreadsheet – AM4

Chapter 1: Formatting

• Module 1: Cells
  • Apply conditional formatting
  • Create and apply custom number formats
  • Split text to columns
• Module 2: Worksheets
  • Copy, move worksheets between spreadsheets
  • Split a window. Move, remove split bars
  • Hide, show rows, columns, worksheets
  • Save a spreadsheet as a template, modify a template

Chapter 2: Formulas and Functions

• Module 1: Using Formulas and Functions
  • Use date and time functions: today, now, day, month, year
  • Use logical functions: and, or, not
  • 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, average, minimum, maximum function
  • Use mixed references in formulas

Chapter 3: Charts

• Module 1: Creating Charts
  • Create a combined chart like: column and line, column and area
  • Create, change, delete a sparkline
  • Add a secondary axis to a chart
  • Change the chart type for a defined data series
  • Add, delete a data series in a chart
• Module 2: Formatting Charts
  • 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, pie slices, plot area, chart area to display an image

Chapter 4: Analysis

• Module 1: Using Tables
  • Create, modify a pivot table/datapilot
  • Modify the data source and refresh the pivot table/datapilot
  • Filter, sort data in a pivot table/datapilot
  • Automatically, manually group data in a pivot table/datapilot and rename groups
  • Use one-input, two-input data tables/multiple operations tables
• Module 2: Sorting and Filtering
  • 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, manual outline features: group, ungroup, sub-total
  • Expand, collapse outline detail levels
• Module 3: Scenarios
  • Create named scenarios
  • Show, edit, delete scenarios
  • Create a scenario summary report

Chapter 5: Validating and Auditing

• Module 1: Validating
  • Set, edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time
  • Enter input message and error alert
• Module 2: Auditing
  • Trace precedent, dependent cells. Identify cells with missing dependents
  • Display all formulas in a worksheet, rather than the resulting values
  • Insert, edit, delete, show, hide comments/notes in a worksheet locally, online

Chapter 6: Enhancing Productivity

• Module 1: Naming Cells
  • Name cell ranges, delete names for cell ranges
  • Use named cell ranges in a function
  • Activate, deactivate the group mode
• Module 2: Paste Special
  • Use paste special options: add, subtract, multiply, divide
  • Use paste special options: values/numbers, transpose
• Module 3: Linking, Embedding and Importing
  • Insert, edit, remove a hyperlink
  • Link data within a spreadsheet, between spreadsheets
  • Update, break a link
  • Import delimited data from a text file
• Module 4: Automation
  • 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

Chapter 7: Collaborative Editing

• Module 1: Reviewing and Security
  • 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