ICDL Workforce modules – Office Applications – Spreadsheets

The Spreadsheet is intelligent software that typically has many sheets with thousands of rows and columns. The main purpose of a spreadsheet is to allow a user to record and analyze data in a tabular format. These spreadsheets have made accounting and other calculations easier with their built-in formulae. All you have to do is enter data, choose a row for your answer and select the formula that you want to apply. Advanced spreadsheets allow users to enter their own formulae to scale its functions. We can also compare the data entered and produce an analysis report with a variety of graphs and charts. A spreadsheet is capable of much more functionalities that make it one of the best tools for an organization. This software is especially useful for data analysts and accountants.

Category

Using the Application

Skill Set

Working with Spreadsheets

Task Item

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

Skill Set

Enhancing Productivity

Task Item

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

Category

Cells

Skill Set

Insert, Select

Task Item

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

Skill Set

Edit, Sort

Task Item

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

Skill Set

Copy, Move, Delete

Task Item

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

Category

Managing Worksheets

Skill Set

Rows and Columns

Task Item

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

Skill Set

Worksheets

Task Item

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

Category

Formulas and Functions

Skill Set

Arithmetic Formulas

Task Item

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

Skill Set

Functions

Task Item

Use sum, average, minimum, maximum, count, counta, countblank functions

Use the logical function if (yielding one of two specific values) with comparison operator: =, >, <

Category

Formatting

Skill Set

Numbers/Dates

Task Item

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

Skill Set

Contents

Task Item

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

Skill Set

Alignment, Border Effects

Task Item

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

Category

Charts

Skill Set

Create

Task Item

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

Skill Set

Edit

Task Item

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

Category

Prepare Outputs

Skill Set

Setup

Task Item

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

Skill Set

Check and Print

Task Item

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

To have all lessons sent directly to your email click here: Training Courses For All