24 hours – ICDL Perform Spreadsheet Functions (Excel 2016)

Using the Application

Working with Spreadsheets

• Open, close a spreadsheet application. Open and 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

Enhancing Productivity

• 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).
• Recognize 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, Sort

• 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, Move, Delete

• 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

• Recognize 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.

Functions

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

Contents

• 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

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

Edit

• 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

Setup

• 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 Print

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