ICDL Spreadsheets 2013 5.0 - Nu-Vision High School

ICDL SPREADSHEETS
Syllabus 5.0
Learning Material (MS Excel 2013)
Provided by:
Nu-Vision High School
Page i ICDL Spreadsheets 2013 S5.0 V1 130616 © 2016

European Computer Driving Licence, ECDL, International Computer Driving Licence, ICDL, e-Citizen and related logos are all registered
Trade Marks of The European Computer Driving Licence Foundation Limited (“ICDL Foundation”).
This courseware may be used to assist candidates to prepare for the ICDL Foundation Certification Programme as titled on the
courseware. ICDL Foundation does not warrant that the use of this courseware publication will ensure passing of the tests for that ICDL
Foundation Certification Programme.
The material contained in this courseware does not guarantee that candidates will pass the test for the ICDL Foundation Certification
Programme. Any and all assessment items and / or performance-based exercises contained in this courseware relate solely to this
publication and do not constitute or imply certification by ICDL Foundation in respect of the ICDL Foundation Certification Programme or
any other ICDL Foundation test. This material does not constitute certification and does not lead to certification through any other process
than official ICDL Foundation certification testing.
Candidates using this courseware must be registered with the National Operator before undertaking a test for an ICDL Foundation
Certification Programme. Without a valid registration, the test(s) cannot be undertaken and no certificate, nor any other form of
recognition, can be given to a candidate. Registration should be undertaken at an Approved Test Centre.
Screenshots used with permission from Microsoft.
Page ii ICDL Spreadsheets 2013 S5.0 V1 170616 © 2016

ICDL Spreadsheets
Having the skills to operate and get the most from a spreadsheet application directly
enhances your ability to manage numerical data and will positively impact on your job
performance. This ICDL Spreadsheets module provides you with the tools to understand
the concept of spreadsheets, and to demonstrate an ability to use a spreadsheet to produce
accurate work outputs.
On completion of this module you will be able to:

 Work with spreadsheets and save them in different file formats.
 Choose built-in options, such as the Help function, within the application to enhance
productivity.
 Enter data into cells; use good practice in creating lists.
 Select, sort and copy, move and delete data.
 Edit rows and columns in a worksheet.
 Copy, move, delete, and appropriately rename worksheets.
 Create mathematical and logical formulas using standard spreadsheet functions; use
good practice in formula creation; recognise error values in formulas.
 Format numbers and text content in a spreadsheet.
 Choose, create, and format charts to communicate information meaningfully.
 Adjust spreadsheet page settings.
 Check and correct spreadsheet content before finally printing spreadsheets.
What are the benefits of this module?

This module gives you the skills to operate spreadsheet software, including the most
common commercial and open-source offerings. Spreadsheets maintain an important role
in business operations, and having the knowledge to utilise their functions, formulas and
features is a necessary requirement for any worker. Once you have developed the skills
and knowledge set out in this book, you will be in a position to become certified in
an international standard in this area - ICDL Spreadsheets.
For details of the specific areas of the ICDL Spreadsheets syllabus covered in each section
of this book, refer to the ICDL Spreadsheets syllabus map at the end of the book.
How to use this book

This book covers the entirety of the ICDL Spreadsheets course. It introduces important
concepts and sets out the specific steps associated with using different features of the
application. You will also have the opportunity to practice some of these activities yourself
using sample files provided in the Student Folder. It is recommended that you do not save
your changes to sample files, as you may want to practice an activity more than once.
© 2016 RW000002 Page iii

Page iv RW000002 © 2016
ICDL SPREADSHEETS
LESSON 1 - EXPLORING MICROSOFT EXCEL 2013 ............................................................... 1

1.1 Starting Excel 2013 ............................................................................................................. 2
1.2 The User Interface .............................................................................................................. 2
1.3 Excel Options ...................................................................................................................... 5
1.4 Creating a Workbook .......................................................................................................... 7
1.5 Opening a Workbook .......................................................................................................... 8
1.6 Saving a New Workbook ..................................................................................................... 9
1.7 Closing a Workbook .......................................................................................................... 11
1.8 Working with Worksheets.................................................................................................. 11
1.9 Using the Ribbon .............................................................................................................. 14
1.10 Hiding the Ribbon ........................................................................................................... 15
1.11 Using Magnification/Zoom Tools ..................................................................................... 17
1.12 Closing and Exiting Excel ................................................................................................ 18
1.13 Review Exercise ............................................................................................................. 19
LESSON 2 - GETTING HELP .................................................................................................... 20

2.1 Using Microsoft Excel Help and Resources....................................................................... 21
2.2 Working with Excel Help ................................................................................................... 21
2.3 Looking Further for Answers ............................................................................................. 22
2.4 Review Exercise ............................................................................................................... 24
LESSON 3 - BASIC WORKBOOK SKILLS .............................................................................. 25

3.1 Using the Keyboard to Select Cells ................................................................................... 26
3.2 Using the Mouse to Scroll ................................................................................................. 28
3.3 Using the Scroll Bar Shortcut Menu .................................................................................. 29
3.4 Using Go To...................................................................................................................... 31
3.5 Entering Text .................................................................................................................... 33
3.6 Entering Numbers ............................................................................................................. 35
3.7 Shortcuts for Data Entry .................................................................................................... 36
3.8 Editing Data ...................................................................................................................... 38
3.9 Spell Check....................................................................................................................... 40
3.10 Saving the Workbook with another Name ....................................................................... 42
© 2016 RW000002 Page v
3.11 Saving a Workbook as Another File Type ....................................................................... 43
3.12 Review Exercise ............................................................................................................. 45
LESSON 4 - SELECTION ......................................................................................................... 47

4.1 Selecting a Cell ................................................................................................................. 48
4.2 Selecting a Range of Adjacent cells .................................................................................. 48
4.3 Selecting a Range of Non-Adjacent cells .......................................................................... 49
4.4 Selecting the Entire Worksheet ......................................................................................... 50
4.5 Selecting a Row ................................................................................................................ 50
4.6 Selecting a Range of Adjacent Rows ................................................................................ 51
4.7 Selecting a Range of Non-Adjacent Rows......................................................................... 51
4.8 Selecting an Entire Column ............................................................................................... 52
4.9 Selecting a Range of Columns .......................................................................................... 52
4.10 Selecting a Range of Non-Adjacent Columns ................................................................. 53
4.11 Review Exercise ............................................................................................................. 54
LESSON 5 - WORKING WITH COLUMNS AND ROWS ........................................................... 55

5.1 Adjusting Column Width .................................................................................................... 56
5.2 Adjusting the Row Height .................................................................................................. 57
5.3 Automatically Adjusting Column ........................................................................................ 58
5.4 Inserting Columns and Rows ............................................................................................ 59
5.5 Deleting Columns and Rows ............................................................................................. 61
5.6 Freezing and Unfreezing Columns and Rows ................................................................... 63
5.7 Review Exercise ............................................................................................................... 68
LESSON 6 - NUMBER FORMATTING ...................................................................................... 69

6.1 About Number Formats ..................................................................................................... 70
6.2 Accounting Number Style .................................................................................................. 71
6.3 Percent Style .................................................................................................................... 72
6.4 Comma Style .................................................................................................................... 73
6.5 Decimal Places ................................................................................................................. 74
6.6 Review Exercise ............................................................................................................... 76
LESSON 7 - TEXT FORMATTING ............................................................................................ 77

7.1 Formatting Text ................................................................................................................. 78
7.2 Changing the Font ............................................................................................................ 78
Page vi RW000002 © 2016

7.3 Changing Font Size .......................................................................................................... 80
7.4 Bold and Italic ................................................................................................................... 81
7.5 Underlining Text ................................................................................................................ 83
7.6 Font Colour ....................................................................................................................... 84
7.7 Rotating Text .................................................................................................................... 85
7.8 Text Wrapping .................................................................................................................. 86
7.9 Cell Alignment ................................................................................................................... 88
7.10 Review Exercise ............................................................................................................. 90
LESSON 8 - CELL FORMATTING ............................................................................................ 91

8.1 Merging Cells .................................................................................................................... 92
8.2 Vertical Alignment ............................................................................................................. 93
8.3 Splitting Cells .................................................................................................................... 94
8.4 Adding Borders ................................................................................................................. 95
8.5 Drawing Borders ............................................................................................................... 97
8.6 Adding Fill Colour to Cells ............................................................................................... 100
8.7 Format Painter ................................................................................................................ 102
8.8 Inserting Cut or Copied Cells .......................................................................................... 103
8.9 Deleting Cells .................................................................................................................. 106
8.10 Review Exercise ........................................................................................................... 108
LESSON 9 - WORKING WITH TABLES ................................................................................. 109

9.1 Adding Table Rows and Columns ................................................................................... 110
9.2 Review Exercise ............................................................................................................. 111
LESSON 10 - FORMULAS ...................................................................................................... 112

10.1 Using Basic Formulas ................................................................................................... 113
10.2 Entering Formulas......................................................................................................... 114
10.3 Basic Functions............................................................................................................. 116
10.4 Using the AutoSum Button ............................................................................................ 118
10.5 Using the AutoSum List ................................................................................................. 119
10.6 Using AutoComplete ..................................................................................................... 120
10.7 Editing Functions .......................................................................................................... 123
10.8 Using Auto Calculate..................................................................................................... 124
10.9 Modifying Formulas Using Range Borders .................................................................... 126
© 2016 RW000002 Page vii

10.10 Error Checking ............................................................................................................ 127
10.11 Creating an Absolute Reference ................................................................................. 129
10.12 Using the IF Function .................................................................................................. 132
10.13 Review Exercise ......................................................................................................... 135
LESSON 11 - CUT, COPY, AND PASTE ................................................................................ 136

11.1 Copying and Pasting Data ............................................................................................ 137
11.2 Cutting Data .................................................................................................................. 138
11.3 Copying Formulas ......................................................................................................... 139
11.4 Paste Options ............................................................................................................... 141
11.5 Paste List ...................................................................................................................... 142
11.6 Filling Cells ................................................................................................................... 143
11.7 Drag-and-Drop Editing .................................................................................................. 144
11.8 Undo and Redo ............................................................................................................. 146
11.9 Review Exercise ........................................................................................................... 148
LESSON 12 - DATA MANAGEMENT ..................................................................................... 149

12.1 Sorting .......................................................................................................................... 150
12.2 Finding Data ................................................................................................................. 151
12.3 Replacing Data ............................................................................................................. 152
12.4 Finding and Replacing Cell Formats ............................................................................. 154
12.5 Review Exercise ........................................................................................................... 157
LESSON 13 - CREATING CHARTS ........................................................................................ 158

13.1 Inserting a Column Chart .............................................................................................. 159
13.2 Inserting a Line Chart .................................................................................................... 160
13.3 Inserting a Bar Chart ..................................................................................................... 162
13.4 Inserting a Pie Chart ..................................................................................................... 163
13.5 Moving and Resizing a Chart ........................................................................................ 164
13.6 Adding Chart Title ......................................................................................................... 166
13.7 Changing the Chart Background ................................................................................... 167
13.8 To format a chart title, chart axis, chart legend. ............................................................. 169
13.9 Changing a column, bar, line or pie slice colours .......................................................... 171
13.10 Changing the Chart Type ............................................................................................ 172
13.11 Adding Data Labels to a Chart .................................................................................... 173
Page viii RW000002 © 2016

13.12 Changing the Chart Layout ......................................................................................... 174
13.13 Copying and Moving Charts ........................................................................................ 175
13.14 Deleting a chart ........................................................................................................... 176
13.15 Review Exercise ......................................................................................................... 177
LESSON 14 - USING PAGE SETUP ....................................................................................... 178

14.1 Worksheet Margins ....................................................................................................... 179
14.2 Worksheet Orientation .................................................................................................. 180
14.3 Worksheet Page Size.................................................................................................... 181
14.4 Headers and Footers .................................................................................................... 181
14.5 Header and Footer Fields ............................................................................................. 184
14.6 Scaling your Worksheet to fit your page(s) .................................................................... 185
14.7 Repeating Row and Column Labels .............................................................................. 186
14.8 Changing Sheet Options ............................................................................................... 188
14.9 Review Exercise ........................................................................................................... 190
LESSON 15 - PRINTING ......................................................................................................... 191

15.1 Print Preview ................................................................................................................. 192
15.2 Printing the Current Worksheet ..................................................................................... 193
15.3 Apply Automatic Title Rows to All Printed Pages........................................................... 194
15.4 Printing a Selected Range ............................................................................................ 195
15.5 Printing a Page Range .................................................................................................. 196
15.6 Printing Multiple Copies ................................................................................................ 196
15.7 Review Exercise ........................................................................................................... 198
ICDL Syllabus.......................................................................................................................... 199
© 2016 RW000002 Page ix

LESSON 1 -
EXPLORING MICROSOFT EXCEL 2013
In this section, you will learn about:
 Starting Excel
 The user interface
 Excel options
 Creating a workbook
 Opening a workbook
 Saving a new workbook
 Closing a workbook
 Working with worksheets
 Using the ribbon
 Hiding the ribbon
 Using magnification/zoom tools
 Exiting Excel
Lesson 1 – Exploring Microsoft Excel 2013 ICDL Spreadsheets
1.1 STARTING EXCEL 2013

Concepts
Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft
Windows and Mac OS X. It allows you to enter numerical values or data into the
rows or columns of a spreadsheet, and use these numerical entries for
calculations, graphs, and statistical analysis.
Steps
To start Microsoft Excel 2013:
1. Click Start.
The Start menu appears.
2. Point to All Programs.

The All Progams menu appears.
3. Click Microsoft Office 2013.

The Microsoft Office submenu appears.
4. Click Microsoft Excel 2013.
5. Click Blank workbook and a new workbook

opens.
1.2 THE USER INTERFACE

Concepts
The Microsoft Excel 2013 user interface uses ribbons and tabs just like its
predecessor, Microsoft Office 2010. The user interface itself has been tweaked,
with changes like blocky worksheet tabs and capitalised ribbon tab names ‒ e.g.,
"HOME" instead of "Home." Now, you can share your files via e-mail or online.
You can also export your file to create a PDF/XPS document.
Page 2 RW000002 © 2016

ICDL Spreadsheets Lesson 1 - Exploring Microsoft Excel 2013
HOME Tab
Excel Layout
Active Cell
In an Excel 2013 worksheet, an active cell is the cell with the black outline. Data is
always entered into the active cell.
Column Letter
Columns run vertically on a worksheet and each column is identified by a letter in
the column header.
© 2016 RW000002 Page 3

Formula Bar
Located above the worksheet, this area displays the contents of the active cell. It
can also be used for entering or editing data and formulas.
Name Box
Located left to the formula bar, the Name Box displays the cell reference or the
name of the active cell.
Row Numbers
Rows run horizontally in a worksheet and are identified by a number in the row
header. Together a column letter and a row number create a cell reference. Each
cell in the worksheet can be identified by this combination of letters and numbers
such as A1, F456, or AA34.
Sheet Tabs
By default there is one worksheet in an Excel file. The tab at the bottom of a
worksheet tells you the name of the worksheet - such as Sheet1, Sheet2 etc.
Quick Access Toolbar

This customisable toolbar allows you to add frequently used commands. Click on
the down arrow at the end of the toolbar to display the available options.
Application Button
Clicking on the Application Button displays a drop down menu containing a
number of options, such as open, save, and print. The options in the Button menu
are very similar to those found under the File menu in previous versions of Excel.
Ribbon
The Ribbon is the strip of buttons and icons located above the work area in Excel
2013. The Ribbon replaces the menus and toolbars found in earlier versions of
Excel.
Page 4 RW000002 © 2016

1.3 EXCEL OPTIONS

Concepts
Excel Options lets you change some of the basic option preferences in Excel 2013,
such as the user name to be used for spreadsheets and the default folder from
which to open and save spreadsheets.
Steps
To enter a user name:
1. Click the FILE tab.

The Backstage view is displayed.
2. Select Options.
The Excel Options dialog box is displayed.
3. Select the General category from the left hand

pane.
The options for the General category appear in the
right-hand pane.
4. Enter a user name in the User name box and click

OK. Click OK
The user name is entered.
© 2016 RW000002 Page 5

Steps
To enter a default file location from which to open and save spreadsheets.

2. Select Options.
The Excel Options dialog box is displayed.
3. Select the Save category from the left hand pane.

The options for the save category appear in the
right-hand pane.
4. Change the file path in the Default file location Change the default location at the
box. end of the file path from \My
The file location will be changed. Documents to \My Music.
5. Click OK.
The Excel options dialog box closes and the Click
options are applied.
Save the file as Practice Options.xlsx and notice it in the My Music folder.
Practice the Concept: Change the Excel options back to having My Documents
as the default location. Having done this delete the Practice Options.xlsx file from
My Music.
Page 6 RW000002 © 2016

1.4 CREATING A WORKBOOK

Concepts
A Microsoft Office Excel workbook is a file that contains one or
more worksheets that you can use to organise various kinds of related
information. You can create a new workbook by simply opening a blank one. You
can also use templates on which to base the new workbook, such as the default
template provided with Microsoft Excel or templates you have created.
Creating a New Workbook
Steps
To create a new blank workbook:

The Backstage View is displayed.
2. Click New.
The pane on the right displays the Click
available templates.
© 2016 RW000002 Page 7

3. Click Blank workbook.

The Blank workbook template is
opened.
Close the new workbook without saving.
1.5 OPENING A WORKBOOK

Concepts
You can open an existing workbook to work on in Excel instead of always starting
with a blank workbook. The workbook may be on a storage device, cloud service or
an online application.
Steps
Open an existing workbook from a specific drive and folder location. Open a blank
workbook.

2. Click Open.
Click
The Open dialog box is displayed.
3. Select the approriate drive that Click Student Folder

contains the Student Folder. Open
the Student Folder.
The Student Folder will appear.
4. Select Annual Sales.xlsx. Click Annual Sales.xlsx

The Annual Sales workbook is
selected.
Page 8 RW000002 © 2016

5. Click the Open button.

Click
The Open dialog box closes and
the Annual Sales workbook opens.
Close the Annual Sales.xlsx workbook without saving.

Quick Tip: When multiple workbooks are opened, use the Switch Windows
option in the VIEW tab to navigate between the opened workbooks.
1.6 SAVING A NEW WORKBOOK

Concepts
Whether using the desktop or web version of Excel, you save documents through
the FILE tab, no matter where you wish to save the document to.
Select a Location to Save
Steps
To save a new workbook:
© 2016 RW000002 Page 9

1. Open a new blank worksheet. Open Excel

A blank worksheet is displayed.

The backstage view will open.
3. Select the Save button. Click Save

The Save section will open.
4. Select a location for you to save Click Computer, then Browse.

the workbook to.
Enter a new file name and navigate
to the location required.
5. Type Annual Sales in the File

name box.
The existing text is overwritten with
the file name you specified.
6. Select Documents from the list of

folders.
The Documents folder is selected.
7. Click the Save button.

Click
The Save As dialog box closes and
the file is saved to the Documents
folder.
Page 10 RW000002 © 2016

1.7 CLOSING A WORKBOOK

Steps
To close a workbook:

2. Click Close.
The workbook closes. Click
If a message box opens, asking you if you want to save the workbook, click Don’t
save.
1.8 WORKING WITH WORKSHEETS

Concepts
The tab at the bottom of a worksheet displays the name of the worksheet - such as
Sheet1, Sheet2 etc. You can switch between worksheets by selecting the desired
tab. You can add, rename, and move tab positions as shown in the steps below.
Excel Sheet Tab
Steps
To work with worksheets:
Open Explore.xlsx. Notice the worksheet tabs at bottom of the Excel window.
© 2016 RW000002 Page 11

1. Click the Expenses sheet tab.

The Expenses sheet is displayed.
You can quickly insert a new worksheet by clicking on the button. Excel named
these worksheets using a default name, so consider renaming your worksheets to
reflect what they contain. To rename it, double-click on the existing sheet name
(e.g. Sheet1) and type a new name.
Concepts
You can copy and move a worksheet within a spreadsheet by right-clicking the
worksheet at the bottom of the workbook window, click Move or Copy, select the
location to move the worksheet to, and clicking OK.
Steps
1. Select the worksheet tab at the Right-click the Sales Data tab at the bottom of
bottom of the worksheet window. the worksheet window.
The menu displays.
2. Select Move or Copy. Click Move or Copy.

The Move or Copy dialog box
opens.
3. Select the location to copy the Click (move to end).

worksheet to.
Page 12 RW000002 © 2016

4. Select the Create a Copy Click in the Create a Copy checkbox, and click
checkbox. OK.
A new worksheet entitled Sales
Data (2) appears.
To Move a worksheet within a spreadsheet
Steps
1. Select the worksheet tab at the Right-click the Sales Data (2) tab at the bottom
bottom of the worksheet window. of the worksheet window.
The menu displays
2. Select Move or Copy. Click Move or Copy.

The Move or Copy dialog box
opens.
3. Select the location to move the Click Sales Data in the Before sheet: list.
worksheet to and click OK.
Sales Data (2) appears before
Sales Data.
Quick Tip: You can also move a worksheet within a workbook by clicking a sheet tab,
holding the left mouse button, dragging the sheet to the desired location.
To rename a worksheet within a spreadsheet
© 2016 RW000002 Page 13

Steps
1. Select the worksheet tab at the Right-click the Sales Data (2) tab at the bottom
bottom of the worksheet window. of the worksheet window.
The menu displays
2. Select Rename. Click Rename.

The worksheet name is highlighted.
3. Enter the worksheet name into the Type Copy of Sales Data.
highlighted worksheet tab.
The worksheet is renamed.
Quick Tip: You can quickly move to the next sheet or previous sheet in the
workbook by pressing Ctrl+PgDn or Ctrl+PgUp respectively.
1.9 USING THE RIBBON

Concepts
The Ribbon is designed to help you quickly find the commands that you need to
complete a task. Commands are organised in logical groups, which are collected
together under tabs. Each tab relates to a type of activity, such as writing or laying
out a page. To reduce clutter, some tabs are shown only when needed. For
example, the Picture Tools tab is shown only when a picture is selected.
Page 14 RW000002 © 2016

Excel Ribbon
Steps
Using the ribbon to make the text bold:
1. Click the HOME tab.

The Home ribbon is displayed. Click
2. Click B to make the text bold.

The selected text is displayed in
bold.
1.10 HIDING THE RIBBON

Concepts
You can’t delete or replace the Ribbon with the toolbars and menus from the earlier
versions of Microsoft Office, although you can minimise it to allow for more on-
screen space. When this option is in use, the ribbon reappears when you click on a
tab, then disappears after you select a command or when you click anywhere in the
worksheet.
© 2016 RW000002 Page 15

Full Ribbon
Minimised Ribbon
Steps
To hide the ribbon:
1. Double-click on the currently

selected tab.
The ribbon is hidden temporarily.
2. Click on any tab.

The ribbon is displayed at the top of
a few rows as an overlay.
Page 16 RW000002 © 2016

3. Click any cell in the worksheet.

The ribbon is minimised.
4. Double-click on any tab.

The ribbon is permanently
displayed once again.
1.11 USING MAGNIFICATION/ZOOM TOOLS

Concepts
You can use the magnification / zoom tools to display book depending on your
needs.
Steps
1. On the VIEW tab in the Zoom group, select Click Zoom
the Zoom button.
The Zoom dialog box appears.
2. Check the required magnification check-box Click the 75% option
or click the Percent box and enter the
magnification required.
The options will allow you to set your
preferred zoom measurement.
3. Apply the changes. Click OK
The zoom options will be applied.
© 2016 RW000002 Page 17

1.12 CLOSING AND EXITING EXCEL

Concepts
When you’re ready to quit Excel, you have several choices for shutting down the
program:
 Press Alt+F4.
 If you have only one workbook open, click the Close button (the X) in the
upper-right corner of the Excel 2013 program window. If you have more
than one workbook open, you need to close each workbook individually
using this method.
 Double click the Excel Application icon in the top left corner of the Excel
2013 program window.
Be sure to save your changes before exiting the workbook you’ve been working
on. If you attempt to leave the workbook without saving, an alert box appears in
Excel warning you that your changes will not be saved. To save these changes
before exiting click the Save button. If you don’t want to save your changes click
Don’t Save.
Steps
To exit Excel:
1. Navigate your cursor to the upper-left hand corner of

the Excel program and click on the Excel Application
button. Click
2. Click Close.
If you have only one workbook open, the Excel
program closes.
Select Don’t Save if you are prompted to save any changes.
Page 18 RW000002 © 2016

1.13 REVIEW EXERCISE

Explore Microsoft Excel 2013
1. Start Excel.
3. Open the Excel Options window.
4. Display the VIEW tab.
5. Minimise the Ribbon.
6. Maximise the Ribbon.
7. Exit Excel without saving changes to the workbook.
© 2016 RW000002 Page 19

LESSON 2 -
GETTING HELP
 Using Microsoft Excel help and resources

 Working with Excel help
 Looking further for answers
ICDL Spreadsheets Lesson 2 - Getting Help
2.1 USING MICROSOFT EXCEL HELP AND

RESOURCES
Steps
You can use Excel’s extensive Help facility when you need assistance on any
Excel topic or task. You can search online and offline versions of Excel Help to
provide assistance and training, as well as answer your questions about other
Office products if needed.
Excel Help Window
2.2 WORKING WITH EXCEL HELP

Steps
To use the Excel Help window:
© 2016 RW000002 Page 21

Lesson 2 - Getting Help ICDL Spreadsheets
If necessary, open a new blank workbook.
1. Select the Help icon on the Title

Click the ? icon
Bar.
The Excel Help window opens,
displaying a number of topics.
Online help will be displayed if the
Help settings are set to display
Help from Office.com.
2. Type Statistical Functions into

the Search box.
The text appears in the Search
box.
3. Click the Search button.

The results display in the main
pane of the window.
4. Select the desired search result. Scroll if necessary, and click Statistical
The help topic opens in the same Functions.
pane.
Click the Home button. Then, close the Excel Help window.
2.3 LOOKING FURTHER FOR ANSWERS

Concepts
You can look for further answers online if you are connected to Internet. You need
to change the settings of Excel Help so that online help is displayed whenever you
need assistance.
Steps
To use Online Excel Help:
Page 22 RW000002 © 2016

ICDL Spreadsheets Lesson 2 - Getting Help
1. Select the Help icon on the Title

Click the ? icon
Bar.
The Excel Help window opens,
displaying a number of topics.
2. Select the arrow besides Excel

Help list.
The Excel Help options are
displayed.
3. Select Excel Help from

Office.com
Online help settings will be applied
for new search.
4. Type Lookup Functions into the

Search box.
The text appears in the Search
box.
5. Click the Search button.

The online help results are
displayed in the main pane of the
window.
6. Select the desired search result. Scroll if necessary, and click lookup functions.
The help topic opens in the same
pane.
© 2016 RW000002 Page 23

Lesson 2 - Getting Help ICDL Spreadsheets
2.4 REVIEW EXERCISE

Getting Help
1. Open Excel and select the Help icon.
2. Change the Help settings to display Excel help from your computer.
3. Clear the Search Help text box and search for conditional formatting.
4. Select the desired search result and view the information.
5. Change the Help settings to display Excel help from Office.com.
6. Navigate to the Excel Help Home page using the Home button.
7. Type Bar Chart in the Search box and select the desired result.
8. Close the Internet Explorer window, Excel Help and Excel.
Page 24 RW000002 © 2016

LESSON 3 -
BASIC WORKBOOK SKILLS
 Using the keyboard to select cells

 Using key tips
 Using the mouse to scroll
 Using the scroll bar shortcut menu
 Using go to
 Entering text
 Entering numbers
 Shortcuts for data entry
 Editing data
 Spell check
 Creating a new folder when saving
 Saving the workbook with another name
 Save a workbook as another file type
Lesson 3 - Using Basic Workbook Skills ICDL Spreadsheets
3.1 USING THE KEYBOARD TO SELECT CELLS

Concepts
You can use the keyboard to select cells or a range of cells in the worksheet. This
is done by clicking into the appropriate cell and using the arrow keys on the
keyboard to move left, right, up, and down in the worksheet.
To select a rectangle area around the active cell, hold down the SHIFT key and
press the arrow keys.
Selected Cells
Steps
To navigate using the keyboard:
Open Navigation.xlsx.
1. Press the DOWN arrow key to move

one cell down.
The active cell moves one cell down.
Page 26 RW000002 © 2016

ICDL Spreadsheets Lesson 3 - Using Basic Workbook Skills
2. Press the RIGHT arrow key to move

one cell to the right.
The active cell moves one cell to the
right.
3. Press the UP arrow key to move one

cell up.
The active cell moves one cell up.
4. Press the LEFT arrow key to move one

cell to the left.
The active cell moves one cell to the
left.
5. Press the Page Down key to move one

screen down.
Page
The active cell moves down one screen.
Down
6. Press Alt + Page Down to move one

screen to the right.
Page
Alt +
The active cell moves one screen to Down
the right.
7. Press Page UP key to move one screen

up. Page
The active cell moves up one screen. Up
8. Press Alt + Page Up to move one

screen to the left. Page
Alt +
The active cell moves one screen to the Up
left.
9. Press Ctrl + Home to move to the first

cell in the worksheet.
The active cell moves to the first cell in
Ctrl
+ Home
the worksheet.
© 2016 RW000002 Page 27

3.2 USING THE MOUSE TO SCROLL

Steps
To navigate through the worksheet using the mouse, open Selection.xls:
1. Click cell A1
The Active Cell moves to the first cell in
the worksheet.
2. Click the Scroll Arrow at the bottom of

the vertical scrollbar 5 times
The display scrolls down 5 rows but the
Active Cell remains unchanged.
3. Click the Scroll Arrow at the top of the

vertical scrollbar 5 times
The display scrolls up 5 rows. The Active
Cell remains unchanged.
4. Click the Scroll Arrow at the right end of

the horizontal scrollbar 5 times
The display scrolls 5 columns to the
right. The Active Cell remains
unchanged.
5. Click the Scroll Arrow at the left end of

the horizontal scrollbar 5 times
The display scrolls 5 columns to the left.
The Active Cell remains unchanged.
6. Drag the vertical Scroll Box all the way

down.
The display scrolls within the utilised
area of the worksheet (down to row 101).
Page 28 RW000002 © 2016

7. Drag the horizontal Scroll Box all the

way to the right.
The display scroll within the utilised area
of the worksheet (up to column O).
8. Hold down the [Shift] key and then drag

the horizontal scroll bar to the right.
The display scrolls beyond the utilised
area of the worksheet.
9. Press [Ctrl + Home] to move the active

cell back the first cell in the worksheet.
The Active Cell moves to cell A1.
3.3 USING THE SCROLL BAR SHORTCUT MENU

Concepts
When you right-click on the vertical or horizontal scroll bars, a menu appears to let
you quickly scroll through the worksheet. You can use the menu to scroll to the
top, bottom, left edge, right edge, one page up, one page down, one page left or
one page right within the worksheet. You can right-click anywhere on the scroll
bars and make the sheet scroll to the selected location.
© 2016 RW000002 Page 29

Steps
To use the Scroll Bar shortcut menu to scroll through the worksheet:
If needed, select cell A1.
1. Right-click on the mid-point of the vertical scroll bar.

A shortcut menu is displayed.
2. Click Scroll Here.

The shortcut menu disappears and the worksheet scrolls
to the position specified.
3. Right-click anywhere on the vertical scroll bar.

4. Select Top.
The worksheet scrolls to the top of the worksheet.
Page 30 RW000002 © 2016

5. Right mouse click anywhere on the vertical scroll bar.

6. Select Page Down.

The worksheet scrolls one page down.
Practice the Concept: Right-click in the horizontal scroll bar and select the Left
Edge command. Notice that the worksheet scrolls to display column A. Right-click
in the vertical scroll bar and select the Top command. Notice that the worksheet
scrolls to display row 1.
3.4 USING GO TO
Concepts
You can use the Go To command to find and select cells or select cells that
contain specific data or types of data such as formulas, blank cells or cells that
contain data validation.
© 2016 RW000002 Page 31

Go To Window
Steps
To use Go To to navigate to a specific cell in the worksheet:
If necessary, select cell A1.
1. Select the HOME tab.

The HOME tab is displayed. Click
2. Select Find & Select in the Editing

group.
The menu appears below.
Click
3. Select Go To…
Click Go To…
The Go To dialog box appears.
Page 32 RW000002 © 2016

4. Type the cell reference E150 in the

Reference box.
The cell reference is entered in the
Reference box.
5. Click OK.
The Go To dialog box closes and Click
the active cell moves to cell E150.
Practice the Concept: Open the Go To dialog box by pressing [F5] and go to cell
AZ25. Then use [Ctrl+G] to open the Go To dialog box and go to cell A1.
3.5 ENTERING TEXT

Concepts
You can enter text into cells by selecting the cell and then typing the text directly
in the cell or in the formula bar. Unless formatted differently, the text is aligned to
the left. If the length of the entered text is too long to fit in the cell, it will spill over
to the adjacent cell if it is empty.
It is important to understand that a cell in a worksheet should only contain one

element, or type, of data: For example, first name details in one cell and surname
details in an adjacent cell.
© 2016 RW000002 Page 33

Text in Cells
Steps
To enter text into a worksheet:
1. Click cell A1.

Cell A1 becomes the Active Cell.
2. Type the Infinity Trading Inc.

The text appears in cell A1 and in
the formula bar.
3. Press the Enter key on your

keyboard.
Enter
The Active Cell moves one cell
down.
Continue by filling in data as shown below:
Page 34 RW000002 © 2016

Notice that the text Infinity Trading Inc. spreads across cells A1 to B1. Click cell
A1. The Formula Bar shows that all the text is in cell A1. Click cell B1, notice the
Formula Bar shows that this cell is empty. Since there is no text in cell B1, the
text in cell A1 uses the space in cell B1 to display the text.
You can also edit a cell that already has content in it by selecting the appropriate
cell and modifying, or deleting the cell contents.
3.6 ENTERING NUMBERS

Steps
To enter numbers into cell in the worksheet.
1. Click cell C5.

Cell C5 becomes the Active Cell.
2. Type 1870 in the cell.

The number is displayed in the cell
and in the Formula Bar.
© 2016 RW000002 Page 35

3. Press the Enter key on your

keyboard.
Enter
The Active Cell moves one cell
down.
Continue by filling in data as shown below:
3.7 SHORTCUTS FOR DATA ENTRY

Concepts
When you have to enter repetitive text in a column, there are a few ways you can
avoid having to type the text over and over again. When you type the first few
characters of text that match previously entered text in the same column,
Microsoft Excel will complete the entry for you.
This feature only works with text or a combination of text and numbers. Numbers
and dates will not be automatically completed. Another feature that helps you with
data entry is Pick from Drop-down list. This feature allows you to select the data
you want to enter from a list of previously entered data.
Steps
To use data entry shortcuts to repeat an entry in a column:
Open Navigation.xlsx from the Student Data Folder.
Page 36 RW000002 © 2016

1. Select cell B15. Select cell B15.

The Active Cell moves to cell B15.
2. Begin by typing the letter G in the

cell.
Notice that the text Graphics Cards
appears in the cell.
3. Press Enter on your keyboard.

Enter
The active cell moves to the next
row and the completed text is
entered in cell B15.
4. Right mouse click on cell B16.

A menu of options is displayed.
5. Select Pick From Drop-down List.

A list of previously entered data is
displayed.
© 2016 RW000002 Page 37

6. Select Ethernet Cards from the

list.
The selected text is entered in cell
B16.
3.8 EDITING DATA

Concepts
When you need to edit the data in a cell, you can edit directly in the cell or edit the
data in the formula bar.
Steps
To edit cell entries in a worksheet.
1. Select cell D5. Select cell D5.

The active cell moves to cell D5.
2. Type 1750 in the cell.

The data appears in the cell and in
the Formula Bar.
3. Select cell C6. Select cell C6.

Cell C6 becomes the Active Cell.
4. Type Raymond in the selected cell.

The data appears in the cell and
formula bar.
Page 38 RW000002 © 2016

5. Press Enter on your keyboard.

Enter
The active cell moves to the next
row.
6. Double-click on cell D6.

The cell is displayed in editing
mode.
7. Position the insertion point to the

right of the number 6.
The insertion point moves to the
selected location.
8. Press Backspace twice.

Two of the numbers are deleted.
9. Type 95 at the insertion point.

The changes are displayed in the
cell and in the formula bar.
10. Press Enter.

Enter
The active cell moves to the cell
below, and the changes are
confirmed.
11. Select cell D7. Select cell D7

The Active Cell moves to cell D7.
12. Press Delete.

Delete
The data in cell D7 is deleted.
Practice the Concept:

Change the number 3120 in cell D8 to 4320.
© 2016 RW000002 Page 39

Delete the entries in cells C14 and D14

Close the workbook without saving.
3.9 SPELL CHECK

Concepts
The spelling tool allows you to automatically find and then correct spelling
mistakes in your workbook.
Steps
To check the worksheet for spelling errors:
From the Student Folder, open SpellCheck.xlsx.

If necessary, select cell A1.
1. Click the REVIEW tab.

The ribbon displays the commands
under the REVIEW tab.
Page 40 RW000002 © 2016

2. Click the Spelling button in the

Proofing group.
The Spelling dialog box opens and
the cell containing the first error in
the worksheet is selected.
3. Click Change.
The mispelled word “Grapics” is
replaced with the word Graphics
and the next misspelled word is
selected.
4. The next word identified is Nfinity,

click Ignore All to stop Excel from
identifying this word as misspelled.
The entry is not replaced and the
next mispelled word is selected.
5. Continue checking the rest of the

worksheet and correct the errors.
Click No, if you are prompted to
continue checking at the beginning
of the sheet.
The Spelling Checker window
closes.
© 2016 RW000002 Page 41

3.10 SAVING THE WORKBOOK WITH ANOTHER NAME

Concepts
After making changes to the file, you might want to keep the original file intact and
save the file with the latest modification as another file. You can use Save As to
save a copy of a workbook with another name, another folder or another file type.
Save As Dialog Box
Steps
To rename an existing workbook:

The Backstage view appears.
2. Click the Save As button.

The Save As dialog box is Click
displayed.
Page 42 RW000002 © 2016

3. Click Current Folder from the

Save As backstage view. Click Current Folder.
4. Type Sales Report in the File

Name box.
The text is displayed in the File
Name box.
5. Select the Save as type list.

The list of file types is displayed.
6. Click the Save button to save

the file.
The Save As dialog box closes
and the workbook is saved. Click
Close the workbook.
3.11 SAVING A WORKBOOK AS ANOTHER FILE TYPE

Concepts
You can save a workbook as another file type, such as template, text file, a
software specific file extension or another version number.
 Template – it can be useful to save a workbook as a template if it contains
a structure that you are likely use again; for example, a quarterly report.
 Text file – if you want to save the data in the spreadsheet to be used in
another application, such as a database, you can save the workbook as a
text file.
 Software specific file – you can save the workbook as another file type,
such as Portal Document Format (.pdf).
 Version number – you can save a version of the workbook that can, for
example, be opened by older versions of Excel, such as Excel 1997 –
2003.
© 2016 RW000002 Page 43

Steps
1. On the FILE tab, click Save As. Click Save As

The Save As options appear.
2. Select the location, for example Click Browse

Computer, then click Browse.
A window with a options for
locations appears.
3. Click on the Save as Type Select Excel Template

drowdown list and click a file
type.
A dropdown list of file types
appears, such as Excel
Template, Excel 1997-2003
Workbook, Text (tab delimited),
PDF.
4. Choose a location and select Select My Documents and click Save

Save.
The save as dialog box will
close and the new file type will
save.
Close the workbook and delete it from My Documents.
Page 44 RW000002 © 2016


Using Basic Workbook Skills
1. Create a new workbook.
2. Use the keyboard to move the active cell around the worksheet.
3. Use the Go To dialog box to select cell M90. Then, return to cell A1.
4. Enter the text and numbers beginning in cell A1 as shown in the following table:
A B
1 Region
2 Northern 20986
3 Southern 35284
4 Central 40436
5 Western 10675
6 Midwest
5. Delete the entry in cell A6.
6. Select cell A1.
7. Save the workbook to the student data folder with the name Region.
8. Close the workbook.
9. Open RegionSales.xlsx.
10. Use the AutoComplete feature to enter the name Jones, P. in cell B9.
11. Use the Pick From Drop-down List feature in cell B10 to enter the name Banes, M.
12. Edit cell C6; change the value from 3952.68 to 3932.68.
13. Enter the number 43567.50 in cell C9. Note: Notice that the ending zero (0) is
dropped.
14. Enter the number 33500.7 in cell C10.
© 2016 RW000002 Page 45

15. Create a new workbook based on the Personal Monthly Budget template. Change
the Actual Monthly Income figure in cell E7 to 2000. Change the Extra income
figure in cell E8 to 0. View the Actual Balance figure in cell J6.
16. Save the workbook with the name My Budget to the student data folder.
17. Close the workbook.
18. Use the Open dialog box and delete the Regions folder and its contents. Close the
Open dialog box.
Upon completion of this review exercise, delete the new files that have been
created.
Page 46 RW000002 © 2016

LESSON 4 -
SELECTION
 Selecting a cell
 Selecting a range of adjacent cells
 Selecting a range of non-adjacent cells
 Selecting the entire worksheet
 Selecting a row
 Selecting a range of adjacent rows
 Selecting a range of non-adjacent rows
 Selecting an entire column
 Selecting a range of columns
 Selecting a range of non-adjacent columns
Lesson 4 – Selection ICDL Spreadsheets
4.1 SELECTING A CELL

Concepts
You can quickly select cells, ranges, rows, or columns, or all data on a
worksheet — for example, to format the data in the selection, or to insert other
cells, rows, or columns. You can also select all or part of the cell contents and turn
on Editing mode so that you can modify the data.
Selecting Cells
Steps
To select a cell:
Open Selection.xlsx.
1. Select cell A4.

The active cell moves to cell A4.
4.2 SELECTING A RANGE OF ADJACENT CELLS

Steps
To select a range of adjacent cells:
Page 48 RW000002 © 2016
ICDL Spreadsheets Lesson 4 – Selection
1. Click on cell A4 and then drag to

cell D10. Release the mouse
button.
The range is selected.
Click any cell in the worksheet to deselect the range.
4.3 SELECTING A RANGE OF NON-ADJACENT CELLS

Steps
To select a range of non-adjacent cells:
1. Click on cell A4 and then drag to

cell A10. Release the mouse
button.
2. Press and Hold down the Ctrl key

on the keyboard. Ctrl
The Ctrl key is pressed.
© 2016 RW000002 Page 49

3. Click on cell C4 and then drag to

cell C10. Release the mouse
button and Ctrl key.
Two ranges are selected.
4.4 SELECTING THE ENTIRE WORKSHEET

Steps
To select all cells in a worksheet:
1. Click the Select All button.

All the cells in the worksheet are
selected.
4.5 SELECTING A ROW

Steps
To select a row:
1. Click row heading 4.

Row 4 is selected.
Page 50 RW000002 © 2016

4.6 SELECTING A RANGE OF ADJACENT ROWS

Steps
To select a range of contiguous rows.
1. Click on row heading 4 and drag

to row heading 10. Release the
mouse button.
The rows are selected.
4.7 SELECTING A RANGE OF NON-ADJACENT ROWS

Steps
To select a range of non-contiguous rows.
1. If needed, click row heading 4.

Row 4 is selected.

3. Click row heading 6. Release the

mouse button and Ctrl key.
Rows 4 and 6 are selected.
© 2016 RW000002 Page 51

4.8 SELECTING AN ENTIRE COLUMN

Steps
To select an entire column:
1. Click on column heading A.

Column A is selected.
4.9 SELECTING A RANGE OF COLUMNS

Steps
To select a range of columns:
1. Click on column heading A and

drag to column heading C.
Release the mouse button.
The columns are selected.
Page 52 RW000002 © 2016

4.10 SELECTING A RANGE OF NON-ADJACENT

COLUMNS
Steps
To select a range of non-adjacent columns:
1. Click on column heading A.


3. Click column heading C. Release

the mouse button and Ctrl key.
Columns A and C are selected.
© 2016 RW000002 Page 53


Work with selection
1. Open ExSelection.xlsx.
2. Use the mouse to select A5:C8.
3. Select the following non-adjacent ranges: A5:A8 and C5:C8.
4. Click anywhere in the workbook to deselect the cells.
5. Close the workbook without saving it.
Page 54 RW000002 © 2016

LESSON 5 -
WORKING WITH COLUMNS AND ROWS
 Adjusting the column width

 Adjusting the row height
 Automatically adjusting columns
 Inserting columns and rows
 Deleting columns and rows
 Freezing and unfreezing columns and rows
Lesson 5 - Working with Columns and Rows ICDL Spreadsheets
5.1 ADJUSTING COLUMN WIDTH

Concepts
The default column width in a worksheet is 8.43 characters, but you can specify a
width of anywhere between 0 (zero) and 255. Zero (0) column width hides the
column, while 255 represents the number of characters that can be displayed in a
cell that is formatted using the standard font.
Default Column Width
Steps
From the Student Folder, open ColsRows.xlsx.
To adjust the width of columns:
1. Select columns D to G, and then

release the mouse button.
Columns D to G are selected.
Page 56 RW000002 © 2016

ICDL Spreadsheets Lesson 5 - Working with Columns and Rows
2. Drag the line to the right of

column G heading until the
Screen Tip displays 11.00.
The width of the columns are
adjusted accordingly.
3. Release the mouse button.

The data in the columns is
displayed.
Click any cell to deselect the range.
5.2 ADJUSTING THE ROW HEIGHT

Concepts
You can specify a row height of 0 (zero) to 409. This value represents the height
measurement in points (1 point equals approximately 1/72 inch or 0.035 cm). The
default row height is 12.75 points (approximately 1/6 inch or 0.4 cm). Zero (0) row
height hides the row.
Adjusting a Row Height
Steps
To adjust the row height:
© 2016 RW000002 Page 57
1. Point to the line below the row 4 heading.

The mouse pointer changes to a double
arrow.
2. Click and drag the line down untli the

screen tip displays 33.00. Then release
the mouse button.
The row height is adjusted accordingly.
5.3 AUTOMATICALLY ADJUSTING COLUMN

Concepts
AutoFit allows you to automatically adjust columns and rows so that all data is
displayed properly.
Steps
To adjust columns to automatically fit entries:
1. Select columns M through O.

Columns M through O are selected.
Page 58 RW000002 © 2016

2. Point to the line to the right of column O

heading.
The mouse pointer shows a double
arrow.
3. Double-click on the line to the right of the

column O heading.
The width of the columns are
automatically adjusted to the appropriate
width.
5.4 INSERTING COLUMNS AND ROWS

Concepts
When you insert blank cells in a worksheet, they go above or to the left of the
active or selected cell. Excel will shift other cells in the same column down, or
cells in the same row to the right, to facilitate the newly inserted cells. As well as
© 2016 RW000002 Page 59

this, you can insert entire rows above a selected row and entire columns to the left
of a selected row.
A Microsoft Excel 2013 worksheet has a maximum size of 16,384 columns wide
by 1,048,576 rows tall.
Inserting Cells
Steps
To insert columns and rows in a worksheet, open Selection.xls if necessary:
1. Select column D.
Column D is selected.

The HOME tab is selected and the
relevant commands are displayed on
the ribbon.
3. Click the Insert button in the Cells

group.
The Insert List is displayed below. Click
4. Select Insert Sheet Columns from Click Insert Sheet Columns

the list.
A new column is inserted in column
D.
Page 60 RW000002 © 2016

5. Type Region in cell D4, then press

Enter.
The word Region is displayed in cell
D4.
6. Select row 2.
Row 2 is selected.

the ribbon.

group.
The Insert List is displayed below. Click
9. Select Insert Sheet Rows from the Click Insert Sheet Rows
list.
A new row is inserted in row 2.
10. Type the formula =today() in cell A2,

then press Enter.
The current date is displayed in cell
A2.
Quick Tip: You can also insert columns and rows by right-clicking on a column or
row heading and selecting Insert from the menu.
5.5 DELETING COLUMNS AND ROWS

Concepts
You can delete a selected row and column. Before you delete, it is important to
realise that any data located in the rows or columns being deleted will be deleted
© 2016 RW000002 Page 61

as well. If you make a mistake use the Undo button from the Quick Access
Toolbar.
Deleting Cells
Steps
To delete columns and rows from a worksheet:
1. Select column D.
Column D is selected.

the ribbon.
3. Select the Delete button in the Cells

group.
The Delete list is displayed below. Click
4. Select Delete Sheet Columns from Click Delete Sheet Columns

the list.
The selected column is deleted.
Quick Tip: Right-click on a column
heading then select Delete from the
shortcut menu to quickly delete a
column.
Page 62 RW000002 © 2016

5. Select row 2.
Row 2 is selected.
Note: The date format displayed may
vary.

the ribbon.
7. Select the Delete button in the Cells

group.
The Delete list is displayed. Click
8. Select Delete Sheet Rows from the Click Delete Sheet Rows
list displayed.
The selected row is deleted.
Quick Tip: Right mouse click on a
row heading then select Delete from
the shortcut menu to quickly delete a
row.
5.6 FREEZING AND UNFREEZING COLUMNS AND

ROWS
Concepts
In Excel you can lock specific rows and columns by freezing or splitting panes.
This allows you to scroll to other areas of the worksheet while a particular section
remains visible as you do so. This can be used, for example, when comparing
data over a long-period of time while keeping titles or a row of key figures fixed for
you to use as a reference point.
© 2016 RW000002 Page 63

Frozen Column
Steps
To freeze and unfreeze columns and rows in a worksheet, open selection.xlsx if
necessary:
1. Select column A.
2. Select the VIEW tab.

The VIEW tab is selected and the
the ribbon.
3. Click the Freeze Panes button in the

Window group.
The Freeze Panes list is displayed.
Page 64 RW000002 © 2016

4. Click Freeze First Column from the

list.
The first column is frozen. Scroll right
and notice that column A is frozen on
the screen.
5. Select column A to unfreeze or

select any cell in the worksheet

the ribbon.

Window group.
8. Click Unfreeze Panes from the list.

The first column is unfreezed. Scroll
right and notice that column A is not
visible in the screen.
9. Select row 2 or any other cell in the

worksheet.
Row 2 is selected.
© 2016 RW000002 Page 65


the ribbon.

Window group.
12. Click Freeze Top Row from the list.

The first row is frozen
13. Select Unfreeze Panes from the

Freeze Panes list in the Window
group.
The top row freeze is removed.
14. Click cell B6 to freeze the Click cell B6.

salesdetails’ column headings in row
5 and the invoice number displayed
in column A.
B6 is selected.
15. Select Freeze Panes from the

Freeze Panes list in the Window
group.
The top 5 rows and 1 column are
freezed.
Page 66 RW000002 © 2016

16. Click any cell and select Unfeeze

Panes from the Freeze Panes list in
the Window group.
The top 5 rows and 1 column are
unfreezed.
Close the workbook without saving any changes.
© 2016 RW000002 Page 67

5.7 REVIEW EXERCISE
Work with columns and rows
1. Open ExColsRows.xlsx.
2. Select columns B through E. Change the width to 15.00.
3. Select rows 2 through 7. Change the height to 33.00.
4. Select column F. Use the AutoFit feature to make the column wide enough to display
the text in cell F1. AutoFit column I as well.
5. Insert a blank column before column I.
6. Insert three blank rows at the top of the worksheet.
7. Type Infinity Trading Pte Ltd. into cell A1, and type Regional Sales into cell A2.
8. Insert two blank rows before row 10.
9. Delete the blank row 10 and the blank column I.
Page 68 RW000002 © 2016

LESSON 6 -
NUMBER FORMATTING
 Number formats
 Accounting number style
 Percent style
 Comma style
 Decimal places
Lesson 6 – Number Formatting ICDL Spreadsheets 2013
6.1 ABOUT NUMBER FORMATS

Concepts
You can format cells to change the way numbers and text appear in the
worksheet. Formatting does not change the underlying value of a cell. That
underlying value appears on the Formula Bar when the cell is selected and is
what is used in calculations.
Formatting improves the overall appearance of a worksheet and makes numbers

easier to read. Using formatting, you can add features such as currency symbols
(€), percent symbols (%), and commas (,), as well as specify a fixed number of
decimal places.
Number formatting can be applied to a single cell, entire columns or rows, a

select range of cells, or the whole worksheet.
The default format for cells containing a value is the General Format. This style
has no specific format and displays values as plain numbers – no dollar symbols,
commas etc.
Number Formats in Excel
Page 70 RW000002 © 2016

ICDL Spreadsheets Lesson 6 – Number Formatting
6.2 ACCOUNTING NUMBER STYLE

Concepts
The Accounting format is also used for formatting monetary values, but with this
format the currency symbols and decimal points of numbers in a column are
aligned. In addition, the Accounting format displays zeros as dashes and negative
numbers in parentheses.
General Format vs Accounting Format
Steps
Open FormatNum.xlsx.
To format cells using the Accounting Number Format button:
1. Select cells B10 to F10. Select the cell range B10:F10

Cells B10 to F10 is selected.

relevant commands are displayed on the
ribbon.
3. Click the Accounting Number Format

button in the Number group. Click
The Accounting number format is applied

to the selected cell.
© 2016 RW000002 Page 71

Quick Tip: To select a different currency, click the arrow on the right of the
Accounting Number Format button, and then select the currency you want from
the list.
6.3 PERCENT STYLE

Concepts
Applying the Percentage format to existing numbers in a workbook results in those
numbers being multiplied by 100 to convert them to percentages.
For example, if a cell contains the number 5, Excel multiplies that number by 100,
which means that you will see 500.00% after you apply the Percentage format.
This may not be what you expected.
To accurately display percentages, before you format the numbers as a

percentage, make sure that they have been calculated as percentages, and that
they are displayed in decimal format. Percentages are calculated by using the
equation amount / total = percentage.
For example, if a cell contains the formula =5/100, the result of that calculation
is 0.05. If you then format 0.05 as a percentage, the number will be correctly
displayed as 5%.
Percent Style Format
Page 72 RW000002 © 2016

Steps
To use the Percent Style button to format cells:
1. Select cells G5 through G9. Select cells G5:G9

Cells G5 through G9 is selected.

ribbon.
3. Click the Percent Style button in the

Number group. Click
The percent style is applied to the
selected cells.
6.4 COMMA STYLE

Concepts
The Comma Style format, or the thousands separator inserts commas in larger
numbers to separate thousands, hundred thousands, etc.
The Comma Style format also displays two decimal places and puts negative
values in parentheses. It doesn’t display dollar signs.
Comma Style Format
Steps
To use the Comma Style to format cells:
© 2016 RW000002 Page 73
1. Select cells B5 through F9. Select cells B5:F9

Cells B5 through F9 is selected.

ribbon.
3. Click the Comma Style button in the

Number group. Click
The comma style is applied to the
selected cells.
6.5 DECIMAL PLACES

Concepts
For numbers that are already entered on a worksheet, you can increase or
decrease the number of places that are displayed after the decimal point by using
the Increase Decimal and Decrease Decimal buttons.
By default, Excel displays 2 decimal places when you apply a built-in number
format, such as a currency format or a percentage, to the cells or data. However,
you can change the number of decimal places that you want to use when you
apply a number format. To have Excel enter the decimal points for you, you can
specify a fixed decimal point for numbers.
Page 74 RW000002 © 2016

Steps
To change the decimal places in cells:
1. Select cells B5 through F9.

Select cells B5:F9.

ribbon.
3. Click the Decrease Decimal button twice.

The number of decimal places in the cell
is decreased. No decimals are displayed
in the selected cells.
Practice the Concept: Select cells B10 through F10, format comma style and
then format the numbers to display no decimals.
Close the workbook without saving the file.
© 2016 RW000002 Page 75

6.6 REVIEW EXERCISE

Format numbers in a worksheet
1. Open ExFormatNum.xlsx.
2. Format the range B3:E7 with the Comma style and decrease the number of decimal
places to none.
3. Format the range G3:I8 with the Comma style. (The cells display pound signs (#)
because the columns are not wide enough to display the formatted numbers).
Decrease the number of decimal places to none.
4. Close the workbook without saving the changes.
Page 76 RW000002 © 2016

LESSON 7 -
TEXT FORMATTING
 Formatting text
 Changing the font
 Changing the font size
 Bold and italic
 Underlining text
 Font colour
 Rotating text
 Text wrapping
 Cell alignment
Lesson 7 – Text Formatting ICDL Spreadsheets 2013
7.1 FORMATTING TEXT

Concepts
You can format cells to change the way text appear in the worksheet. Formatting
does not change the underlying value of a cell but can improve the overall
appearance of a worksheet. You can apply formats to a cell before or after you
enter the data. Formatting can be applied to one cell; a range of cells, columns, or
rows; or the entire worksheet.
Text alignment controls how the text lines up within cells. You can use the controls
on the HOME tab to work with cell alignment. Cell alignment refers to how the text
interacts with the available space in the cell.
Orientation refers to the direction of the text, which runs horizontally from left to
right by default. You can edit this by using the Orientation button on the HOME
tab, allowing you to use vertical or slanted text so that labels in a heading row take
up less space horizontally.
Formatted Worksheet
7.2 CHANGING THE FONT

Concepts
Calibri (Body) in font size 11 is the default font Microsoft Excel uses. However,
this can be changed to another font and font size which is then applied to all new
workbooks that you create.
Page 78 RW000002 © 2016

ICDL Spreadsheets Lesson 7 – Text Formatting
Cell Font Changed from Calibri to Arial Black
Steps
Open FormatText.xlsx.
To change the font of existing text:
1. Select cells A1 through A2. Select cell A1:A2

Cells A1 through A2 are selected.

ribbon.
3. Click the arrow on the right-hand side of

the Font button in the Font group.
The list of fonts is displayed.
4. Scroll down the list, and then select Arial

Black. Select
The Arial Black font is applied to the

selected cells.
© 2016 RW000002 Page 79

7.3 CHANGING FONT SIZE

Concepts
You can change the font or font size for selected cells or ranges in a worksheet .
Steps
To change the font size of existing data:
1. Select cells A4 through G4. Select cell A4:G4.

Cells A4 through G4 is selected.

relevant commands are displayed
on the ribbon.
3. Click the arrow on the right-hand

side of the Font Size button.
The list of font sizes is displayed.
4. Click font size 14.

The font size is applied to the
selected cells.
Quick Tip: You can also use the Increase Font Size and Decrease Font Size
buttons to change the font size of text in your worksheet. Select cells A4 through
Page 80 RW000002 © 2016

G4, and then click the Decrease Font Size button once. The selected cells change
font size accordingly.
Increase / Decrease Font Size buttons
7.4 BOLD AND ITALIC

Concepts
You can display the text as bold and italic for selected cells or ranges in a
worksheet.
Steps
To bold and italicise existing text:
1. Select cells A4 through G4. Select cell A4:G4


the ribbon.
© 2016 RW000002 Page 81

3. Click the Bold button in the Font

group.
Click
The Bold format is applied to the
selected cells.

Cells A5 through A9 is selected.

the ribbon.
6. Click the Italic button in the Font

group.
Click
The Italic format is applied to the
selected cells.
7. Select cell A10. Select cell A10

Cell A10 is selected.
8. Type Total in the selected cell.

The word Total is displayed in cell A10
and in the formula bar.
9. Press the Enter key.

Enter
The active cell moves to the cell below
and the Italic formatting is applied to
the text.
Practice the Concept: Select cell A10, and then click the Italic button to remove
the Italic formatting. Apply Bold formatting to cells A10 through F10.
Quick Tip: You can use keyboard shortcuts to apply bold (Ctrl + B) or italic (Ctrl
+ I) formatting to text in selected cells.
Page 82 RW000002 © 2016

7.5 UNDERLINING TEXT

Concepts
You can display the text in selected cells or cell ranges as underlined or double
underlined.
Steps
To underline or double underline cell entries:
1. Select cells B10 through F10. Select cell B10:F10


the ribbon.
3. Click the Underline button in the Font

group. Click
The Underline format is applied to the
selected cells.
4. To double underline the selected cells,

click the Underline button arrow in the
Font group and select Double
Underline.
© 2016 RW000002 Page 83

7.6 FONT COLOUR

Concepts
You can change the font colour of the selected cells or range of cells in a
worksheet.
Steps
To change the font colour of cell entries:
Font Colour

Cells A1 through A2 is selected.

The HOME tab is selected and the relevant
commands are displayed on the ribbon.
3. Click the arrow on the right-hand side of the Font

Color button in the Font group.
The colour palette is displayed.
Page 84 RW000002 © 2016

4. Click the Red, Accent 2, Darker 50% (6th row, 6th

column of colors)
The color of the data changes to the specified color.
Practice the Concept: Select the range A5:A9. Change the font colour to Tan,
Background 2, Darker 75% (5th row, 3rd column). Deselect the range by clicking
any cell.
7.7 ROTATING TEXT

Concepts
If you have a number of headings in rows that are quite long, rotating the text can
help to keep the data and worksheets in an appropriate layout. The rotated text
will appear within the same width as the columns to maintain the correct structure.
Steps
To rotate text in a cell:
1. Select cells A4 through G4. Select cell A4:G4
© 2016 RW000002 Page 85


the ribbon.
3. Click the Orientation button in the

Alignment group.
The Orientation menu is displayed.
4. Select Angle Counterclockwise from

the menu.
The selection is formatted with the
specified format.
Practice the Concept: Select cells A4 through G4, click the Orientation button,
and then click on Angle Counterclockwise to disable it. The cell contents return to
the original orientation.
7.8 TEXT WRAPPING

Concepts
When entering text in cell that is too narrow, the text either flows over to the next
cell or does not display completely in the cell. To display the entire cell entry, you
can adjust the column width or make the text wrap within the cell. Wrapping text
makes the text flow down within the cell instead of flowing to the next cell.
Page 86 RW000002 © 2016

Steps
To apply text wrapping to contents within a cell:
1. Select cell A1. Select cell A1


on the ribbon.
3. Click the Wrap Text button in the

Alignment group.
Click
The selected text wraps with the
cell.
Practice the Concept: Select cell A2, and then apply wrap text. Select cell A1, and
then deselect Wrap Text to restore it to its original formatting.
Steps
To apply text wrapping to contents within a cell range:
1. Select cell A1 and cell A2. Select cell A1 and cell A2.

on the ribbon.
3. Click the Wrap Text button in the

Alignment group.
Click
The selected text wraps with the
cells.
© 2016 RW000002 Page 87

7.9 CELL ALIGNMENT

Concepts
The default alignment of text data, such as labels and column titles is on the left
side of a cell.
Numbers, formulas, and dates, which are referred to as values, are right aligned
by default.
Excel’s default alignments are not necessarily the best choice for the data. So,
Excel makes it easy to improve the layout and appearance of a worksheet by
using the cell alignment icons on the HOME tab of the ribbon.
Steps
To change the alignment of cells:
1. Select cells A4 through G4.

Cells A4 through G4 is
selected.

The HOME tab is selected and
the relevant commands are
displayed on the ribbon.
Page 88 RW000002 © 2016

3. Click the Center button in the

Alignment group.
The cell contents of the
selected cells are aligned
accordingly.
Practice the Concept: Select cell A4, and then select the Align Text Left
button.
© 2016 RW000002 Page 89


Format text in a worksheet
1. Open ExFormatText.xlsx.
2. Change the font in the range A1:J8 to Arial Rounded MT Bold.
3. Change the font size of the range A1:J2 to 12.
4. Bold the ranges A1:J2 and A3:A8.
5. Italicise the range J3:J7. Left align the range J2:J7. Then, centre the range J2:J7
instead.
6. Underline the range B7:I7.
7. Change the font colour of the range B2:J2 to Red (second colour under Standard
Colours).
8. Apply the selected font colour to the range A3:A8.
9. Rotate the text in the range B2:G2 forty-five degrees to the right. Then, right align
the range B2:E2.
10. Wrap the text in cell A1. Then restore the text in A1 to its original format.
Page 90 RW000002 © 2016

LESSON 8 -
CELL FORMATTING
 Merging cells
 Vertical alignment
 Splitting cells
 Adding borders
 Drawing borders
 Adding fill colour to cells
 Format painter
 Inserting cut or copied cells
 Deleting cells
Lesson 8 – Cell Formatting ICDL Spreadsheets
8.1 MERGING CELLS

Concepts
In Excel 2013, you can merge two or more adjacent cells into one cell and display
the contents of one cell in the merged cell. A title is commonly centered over
the data in the worksheet.
Steps
Open FormatCell.xlsx, and open the Sales worksheet.
To use the Merge & Center button to merge and centre data:
If necessary, select the Sales sheet.
1. Select cells A1 through G1. Select cells A1:G1.


on the ribbon.
Page 92 RW000002 © 2016

ICDL Spreadsheets Lesson 8 – Cell Formatting
3. Click the Merge & Center button in

the Alignment group.
The selected cells are merged and
the text is center aligned.
Practice the Concept: Select cells A2 through G2, and then click the Merge &
Center button to merge the cells and centre the text.
8.2 VERTICAL ALIGNMENT

Concepts
In Excel 2013, it is possible to change the horizontal and vertical alignment
of cell data. Text is left-aligned and values and dates are right-aligned by default.
You can use the buttons in the Alignment group of the HOME tab to alter the
alignment.
Values formatted as Accounting can only display as right-aligned, but it is possible
to change alignment on all other formatting styles.
Steps
To vertically align the contents in a cell:
1. Select cells A4 through G4. Select cells A4:G4


on the ribbon.
© 2016 RW000002 Page 93

3. Click the Middle Align button in

The selected text is vertically
aligned to the middle of the cell.
8.3 SPLITTING CELLS

Concepts
You can split the merged cells of an Excel worksheet. Use the buttons in the
Alignment group of the HOME tab.
Steps
To use the Merge & Center button to split a merged cell:
1. Select cell A1. Select cells A1


The HOME tab is selected and the relevant commands
are displayed on the ribbon.
Page 94 RW000002 © 2016

3. Click the arrow besides the Merge & Center button in

The merge & center list is displayed below.
4. Click Unmerge Cells from the list displayed.

The merged cells are split into individual cells and the text
is aligned left.
5. Click Align Left button from the Alignment group to

display the text.
The selected text is aligned left.
Practice the Concept: Select cell A2, and then click the Merge & Center button
to split the cells.
8.4 ADDING BORDERS

Concepts
By using predefined border styles, you can quickly add a border around cells
or ranges of cells. You can create a custom border if the predefined cell borders
do not meet your needs.
Steps
To add borders to selected cells:
© 2016 RW000002 Page 95

1. Select cells A4 through G10. Select cells A4:G10


The HOME tab is selected and the relevant
commands are displayed on the ribbon.
3. Click the arrow on the right-hand side of the

Borders button in the Font group.
The Borders menu is displayed.
4. Select All Borders from the Borders menu.

The borders style is applied to the selected
cells and the borders menu disappears.
Practice the Concept: Select cells B10 through G10, and then apply the
Bottom Double Border style.
Page 96 RW000002 © 2016

8.5 DRAWING BORDERS

Concepts
You can also draw borders using the Draw Border button to create custom
borders.
Steps
To draw cell borders:
Select the Expenses worksheet and highlight A4:E9.
© 2016 RW000002 Page 97

1. Click the arrow on the right-hand side

of the Borders button in the Font
group.
2. Click Draw Border.

The mouse pointer displays a pencil.
Drag the mouse pointer to draw the
border around the selected table.

of the Borders button again, and
then point to Line Color.
The color palette is displayed.
4. Select Dark Red from the color

palette (first color under Standard
Colors).
The Dark Red color is selected and
the Borders menu disappears.
5. Click and drag the pencil on the

gridline below the Expenses
Report heading from columns A
through E.
The gridline is highlighted as you
drag.

The specified border color and style
is applied to the selected gridline.

of the Border button in the Font
group.
Page 98 RW000002 © 2016

8. Click Draw Border Grid.

The mouse pointer displays a pencil
and grid.

of the Borders button again, and
then point to Line Style.
The Line Style menu is displayed.
10. Select the 9th item in the Line Style

menu.
11. Click and drag the mouse pointer to

select cells A4 through E9, and then
release the mouse button.
The selected line style is applied to
the cells.
12. Press the Esc key to deactivate the Esc

Draw Border Grid feature.
The mouse pointer returns to normal
and the Draw Border feature is
deactivated.

of the Border button in the Font
group.
© 2016 RW000002 Page 99

14. Click Erase Border.

The mouse pointer displays an
eraser.
15. Click and drag the eraser on the

border below the Expenses Report
heading, and then release the mouse
button.
The border below Expenses Report
is erased.
16. Press the Esc key on the keyboard to Esc

deactivate the Erase Border feature.
The Erase Border feature is
deactivated.
Practice the Concept: Use the Draw Border feature to apply a thin solid line,
black border around cells A4 through E4.
Use the Draw Border feature to draw a diagonal border from the bottom left
corner to the top right corner of cell A4.
Use the Erase Border feature to erase the diagonal line in cell A4.
8.6 ADDING FILL COLOUR TO CELLS

Concepts
The Fill Color button is used to apply colour shading to cells and drawing objects.
You can choose from a variety of colours with varying degrees of brightness and
darkness.
Page 100 RW000002 © 2016

Steps
To use the Fill Color button to add colour shading to a cell:
If necessary, select the Expenses sheet.


the ribbon.

of the Fill Color button.
The color palette is displayed.
4. Select the Aqua, Accent 5, Darker

25% color (5th row, 9th column).
The color is applied to the selected
cells.
Practice the Concept: Select cells A4 through G4, and then apply the Red,
Accent 2, Lighter 40% fill colour.
© 2016 RW000002 Page 101

8.7 FORMAT PAINTER

Concepts
The format painter is used to quickly “paint” the formatting of one cell onto another
cell. You can use the tool to format one cell at a time, a range of adjacent cells or
non-adjacent cells.
Steps
To use the Format Painter button to copy and paste formatting:


The HOME tab is selected and
the relevant commands are
displayed on the ribbon.
3. Click the Format Painter button

in the Clipboard group.
The Format Painter button is
selected and the mouse pointer
displays a paintbrush.
4. Click and drag the paintbrush

over cells A5 through A9.
The formatting is applied to cells
A5 through A9 and the
paintbrush disappears.
Page 102 RW000002 © 2016

8.8 INSERTING CUT OR COPIED CELLS

Concepts
You can insert cut or copied cells from one worksheet to another worksheet or
within the same worksheet or between open spreadsheets.
Steps
To insert cut or copied cells:
Open FormatCellC.xlsx.
If needed, select the Report worksheet.
1. Select cells A12 through D17. Select cells A12:D17

Cells A12 through D17 is selected.

the ribbon.
3. Click the Cut button in the

Clipboard group.
The selected cells are cut and a
marquee border is displayed around
the selection.
4. Select cell F4. Select cells F4

Cell F4 is selected.
© 2016 RW000002 Page 103


group.
The Insert menu appears.
6. Click Insert Cut Cells.

The Insert Paste dialog box is
displayed.
7. If needed, select Shift cells right,

and then click OK.
The cut cells are moved and inserted
in the selected location and the
existing data shifts to the right.
Steps
To copy a cell or cell range to a different worksheet.
If needed, select the Report worksheet.

1. Select cells F4 through I9. Select cells F4:I9
Cells F4 through I9 is selected.
the ribbon.
3. Click the Copy button in the
Clipboard group. Click
The selected cells are copied and a
the selection.
Page 104 RW000002 © 2016

4. Select sheet Q2. Click on sheet Q2

Sheet Q2 is selected.
5. Select A3 Click A3
group.
7. Insert the Copied Cells. Click Insert Copied Cells

8. If needed, select Shift cells down, Click OK
and then click OK.
The copied cells are copied and
inserted in the selected location.
Steps
To copy a cell or cell range to a different workbook:
Open FormatCellC.xlsx. If needed, select the Report worksheet. Also open

FormatNum.xlsx and select Sheet2.
1. In FormatCellC.xlsx in the Report Select cells F4:I9

worksheet, select cells F4 through
I9.
Cells F4 through I9 is selected.
the ribbon.
3. Click the Copy button in the
Clipboard group. Click
The selected cells are copied and a
the selection.
© 2016 RW000002 Page 105

4. Switch to FormatNum.xlsx and Click Sheet2

select Sheet2.
Sheet2 is selected.
5. Select A3 Click A3
A3 is selected.
group.
7. Insert the Copied Cells. Click Insert Copied Cells

The copied cells are inserted.
8. If needed, select Shift cells down, Click OK
and then click OK.
The copied cells are inserted in
FormatNum.xlsx on Sheet2.
Close FormatCellC.xlsx and FormatNum.xlsx without saving.
8.9 DELETING CELLS

Concepts
If you are deleting rows or columns, other rows or columns automatically shift up
or to the left.
Note: To quickly repeat deleting cells, rows, or columns, select the next cells,
rows, or columns, and then press CTRL+Y.
Page 106 RW000002 © 2016

Steps
To delete cells in a worksheet:
Open FormatCellC.xlsx.
Select the Q1 worksheet.
1. Select cells A5 through D5. Select cells A5:D5

Cells A5 through D5 is selected.

the ribbon.
3. Click the Delete button in the Cells

group.
The Delete list is displayed.
4. Click Delete Cells… from the list

displayed.
The seleted cells are deleted and the
remaining cells shift up.
Practice the Concept: Select cells C4 through C8 and then delete the selected
cells, moving the remaining data to the left.
© 2016 RW000002 Page 107

Format cells to improve the appearance of a worksheet.
1. Open ExFormatCell.xlsx.
2. Add a Thick Box Border to the range A4:J4. AutoFit column J to view the right edge
of the border.
3. Apply the same thick border style to the range A11:J11.
4. Shade the range A4:A11 in Aqua, Accent 5, Lighter 60% (third row, ninth column).
5. Repeat the aqua shading in the range B4:J4.
6. Merge and centre the text in cell A1 across the range A1:J1.
7. Use the Format Painter to copy the formats from cell A1 to cell A2.
8. Split cell A2 by removing the merge and centre format.
9. Centre cell A1 vertically.
10. Use the Borders menu to draw a black, double line along the bottom edge of cells
A2:J2.
11. Scroll to cell P1. Cut cells P10:V10. Then, insert the cut cells into the range P5:V5.
12. Insert cells in column AA, rows 15 and 16 only. (Hint: AA15:AA16.) Shift the cells to
the right. Type 200 in cell AA15 and 25 in cell AA16.
13. Delete the range P15:V15, shifting the cells up.
Page 108 RW000002 © 2016

LESSON 9 -
WORKING WITH TABLES
 Adding table rows

 Adding table columns
Lesson 9 – Working with Tables ICDL Spreadsheets
9.1 ADDING TABLE ROWS AND COLUMNS

Steps
Open Table.xlsx to add new rows of data to the table:
1. Select cell A34.

Cells A34 is selected. Select cells A34
2. Type Diaz in the selected cell.

The text appears in cell A34.
3. Press Tab.
The active cell moves to the
next cell and a new row is
added to the table.
Practice the Concept: Enter the rest of the data as indicated in the table below:
A B C D E
34 Diaz David Sales 08-07-2006 3324
35 Daniels Fred Marketing 09-06-2007 2936
Practice the Concept: Select cell F4, type bonus and then press [Enter]. The
table expands to include the new column.
Page 110 RW000002 © 2016

ICDL Spreadsheets Lesson 9 – Working with Tables
9.2 REVIEW EXERCISE

Use table features
1. Open ExTable.xlsx.
2. Insert a new column in the table between Product and Inv Num. Then delete the
new column.
3. Scroll as necessary and select cell G67. Press [Tab] and enter the following data:
Column Data
Product Gloves
Inv Num 4230
Sales Rep John Carpenter
Date Sold 7/23/2007
Price Each 12
Qty Sold 19
© 2016 RW000002 Page 111

LESSON 10 -
FORMULAS
 Using basic formulas

 Entering formulas
 Basic functions
 Using the AutoSum button
 Using the AutoSum list
 Using formula autocomplete
 Editing functions
 Using the auto calculate
 Modifying formulas using the range border
 Error checking
 Creating an absolute reference
 Using the IF function
ICDL Spreadsheets 2013 Lesson 10 - Formulas
10.1 USING BASIC FORMULAS

Concepts
Formulas are used to perform calculations on values entered into the cells of a
worksheet. A formula is an equation that performs a calculation. Excel can
execute many formulas, including those that add, subtract, multiply, and divide.
One of the most useful features of Excel is called a cell reference. Cell reference
identifies the location of a cell, and this cell reference can be used in formulas.
Excel uses standard operators for equations, such as a plus sign for addition (+),
a minus sign for subtraction (-), an asterisk for multiplication (*), and a forward
slash for division (/).
When you write formulas in Excel, you must begin with an equal sign (=) because
the cell contains, or is equal to, the formula and its value.
The mathematical operators that can be used in a formula are listed in the following
table:
Operator Performs
+ (plus sign) Addition
- (minus sign) Subtraction
* (asterisk) Multiplication
/ (slash) Division
( ) (parentheses) Controls the order of mathematical operations; calculations
within parentheses are performed first.
% (percent) Converts a number into a percentage; for example, when you
type 10%, Excel reads the value as .10.
^ (caret) Exponentiation; for example, when you type 2^3, Excel reads
the value as 2*2*2.
© 2016 RW000002 Page 113

Lesson 10 - Formulas ICDL Spreadsheets
When more than one operator appears in a formula, it is calculated using the
standard mathematical order of precedence. This order determines which
operations are carried out first. The order of precedence is as follows:
 Parentheses
 Exponentiation
 Multiplication and division
 Addition and subtraction.
For example, the result of 2+3*4 is 14, but the result of (2+3)*4 is 20.
10.2 ENTERING FORMULAS

Concepts
Formulas begin with an equal sign (=) to tell Excel to perform a calculation and
usually contain cell addresses. The equal sign prevents Excel from interpreting
the formula as text, since all cell addresses begin with letters. You enter a formula
in the cell where you want the result to appear.
When you enter a formula into a cell, you can either type the cell addresses
referenced or use the mouse to select the cells and allow Excel to enter the cell
addresses into the formula automatically.
As you type or select cell addresses, Excel places a coloured border with squares
at each corner around each referenced cell. Excel uses a different colour border
for each cell referenced in the formula.
Steps
From the Student Folder, open Formula.xlsx.
To enter a formula into a cell:
Create a formula to compute the Net Profit for District 1 by selecting the Total
Sales in cell B16 and subtracting the Expenses in cell B17.
Page 114 RW000002 © 2016

1. Select the cell in which you want to enter the

formula, B18.
The cell becomes the Active Cell.
2. Type an equal sign (=) to begin the formula.

An equal sign (=) appears in the Formula Bar and
in the cell.
3. Enter the first cell referenced in the formula, B16.

The cell address appears in the Formula Bar and
in colour in the cell, a matching coloured border
appears around the referenced cell.
4. Enter the first mathematical operator, -.

The operator appears in the formula bar and in the
cell.
5. Enter the next cell referenced in the formula, B17.

The cell address appears in the Formula Bar and
in a different colour in the cell, a matching coloured
border appears around the referenced cell.
6. When you have finished creating the formula, press Press [Enter]
[Enter].
The result of the formula appears in the cell, and the
coloured borders of the referenced cells no longer
appear.
Select cell B18. Notice that the formula appears in the formula bar and the result
of the formula appears in the cell. The result of the formula is 57578. Now change
the Total Sales for District 1 to 74500. Notice that the formula recalculates the Net
Profit in cell B18 to 67074.
Practice the Concept: District 2 is projecting that expenses will be 8% of sales.

To calculate the Expenses for District 2, select cell C17 and type an = (equal sign)
to start the formula. Type C16*.08 to multiply the Total Sales for District 2 by 8%,
and press [Enter] to complete the formula. The result should be 1472. (Note: You
could have also typed =C16*8%.)
© 2016 RW000002 Page 115

Now use the mouse to create a formula that calculates the Net Profit for District 2.
Start by typing an equal sign (=) into cell C18. Then, click cell C16, type a minus
sign (-) and click cell C17. Finally, press [Enter] to complete the formula. The result
should be 16928.
10.3 BASIC FUNCTIONS

Concepts
There is a long list of Excel’s built-in formulas that make it easy to perform complex
mathematical operations. These formulas are organised into categories which you
can view. You can use the Insert Function button to insert the basic functions.
Insert Function Dialog Box
The basic functions used are:

Function Name Description
Sum SUM The sum of the values
Average AVERAGE The average of the values
Minimum MIN The smallest value
Maximum MAX The largest value
Count COUNT The number of data values
Page 116 RW000002 © 2016

Counta COUNTA The number of data values in non-blank cells

Round ROUND Numbers rounded to whole numbers
Steps
To use functions:
1. Select the cell into which you want to enter Click cell B9.
the formula.
The selected cell becomes the Active Cell.
2. On the FORMULAS tab in the Functions

Library group, click the Insert Function Click
button.
The Insert Function dialog box is displayed.
3. Select SUM from the Select a function list

and click the OK button.
4. Select the Collapse Dialog button for the

Click the Number 1
argument you want to edit.
The Function Arguments dialog box
collapses.
5. Select the range you want to use in the

calculation.
The range is selected as you drag.
6. Release the mouse button Release the mouse button
The range appears in the collapsed Function

Arguments dialog box, as well as in the
formula in both the Formula Bar and the
cell.
7. Click the Expand Dialog button

Click
The Function Arguments dialog box

expands.
© 2016 RW000002 Page 117

8. Select OK.
Click
The Function Arguments dialog box closes,
and the result of the formula appears in the
cell.
9. Press [Enter]. Press [Enter]

The result of the formula appears in the
Active Cell.
The result of the function should be 7490. Select cell B9 and notice the SUM
function in the formula bar.
Delete B9 cell contents.
10.4 USING THE AUTOSUM BUTTON

Steps
To use the AutoSum button to total the values in a column or row:
1. Select the cell into which you want to enter the Click cell B9.
formula.
2. Click the arrow of the AutoSum button in the

Function Library group on the FORMULAS tab.
The suggested range is surrounded by a coloured
border, and a function ScreenTip appears.
3. Press [Enter]. Press [Enter].

The result of the formula appears in the Active Cell.
Page 118 RW000002 © 2016

The result of the function should be 7490. Select cell B9 and notice the SUM
function in the formula bar.
Practice the Concept: Use the AutoSum button to total the sales figures for Feb
in cell C9 and for Mar in cell D9. The results should be 7495 and 7628.
10.5 USING THE AUTOSUM LIST

Step
You can also use formula options other than sum, such as minimum or maximum,
using the AutoSum list.
To use the AutoSum list:
1. Select the cell into which you want to enter the Select cell B11.
formula.
2. Select the arrow part of the AutoSum button on

the FORMULA tab.
A list of additional functions appears.
3. Select the desired function.

The suggested range is surrounded by a
blinking, coloured border, and a function
ScreenTip appears.
© 2016 RW000002 Page 119

4. Drag to select the range you want to calculate, if

necessary.
5. Release the mouse button. Release the mouse button

The blinking, coloured border appears around
the selected range.
6. Hit [Enter]. Press [Enter]

The result of the formula appears in the cell.
The result of the formula in B11 should be 2009.
10.6 USING AUTOCOMPLETE

Concepts
Although the AutoSum list assists you in creating formulas for the most
commonly used functions, you may prefer to manually enter a function.
The SUM, AVERAGE, MAX, MIN, and COUNT functions are entered with the same
syntax, including beginning the function with an equal sign (=) and then typing the
name of the function and an open parenthesis. You then enter the cell range by
dragging to select the cells or by typing the first and last cells in the range. These
functions are defined in the following table:
Page 120 RW000002 © 2016

Function Example Description

SUM =SUM(A1:A20) Totals all the numbers in a range.
AVERAGE =AVERAGE(A1:A20) Returns the average of a range of
numbers; if a cell in the range is empty,
it is not used in calculating the average;
if a cell in the range contains the number
zero, it is used in calculating the
average.
MAX =MAX(A1:A20) Returns the highest value in a range of
numbers.
MIN =MIN(A1:A20) Returns the lowest value in a range of
numbers.
COUNT =COUNT(A1:A20) Returns the number of cells in the range
that contain numbers.
COUNTA =COUNTA(A1:A20) Returns the number of cells in the range
that contain data (e.g. text or numbers).
ROUND =ROUND(A1,0) The numbers are rounded to the nearest
whole number.
After you type an = (equal sign) and the beginning letters of a formula, the
Formula AutoComplete feature displays valid functions, names and text strings
that match the letters in a dynamic drop-down list.
Steps
To use Formula AutoComplete to create a formula using a basic function.
1. Select the cell into which you want to enter the Select cell B12
formula.
The active cell moves accordingly.
2. Begin the formula by typing the equal character [=].

Type =
The equal character [=] is entered in the selected cell.
© 2016 RW000002 Page 121

3. Type the first letter of the formula.

The Formula AutoComplete drop-down list is
displayed with the first option highlighted and a
ScreenTip describing its use.
4. Type the next letter in the formula.

A list of options becomes shorter.
5. Press the down arrow on the keyboard to highlight

the desired option.
The desired function is highlighted
6. Press the [Tab] key to select the desired function. Press [Tab]
The Formula AutoComplete drop-down list closes,
the function is inserted in the cell with the insertion
point positioned immediately after the opening
parenthesis and a ScreenTip describes the structure
of the function.
7. Select the range of cells to be calculated.

The range is outlined as you drag and a ScreenTip
indicates how many columns and rows are selected.
Page 122 RW000002 © 2016


The formula appears in the Formula Bar and in the
cell, and a blinking border with coloured corners
appears around the selected cells.

The result of the formula should be 1704.
Practice the Concept: Select cell E5 and type the function =sum(B5:D5). Notice
that a coloured border surrounds the range as you type. Press [Enter] to complete
the function. The result should be 5527. Copy this function to the cell range E6:E8.
10.7 EDITING FUNCTIONS

Steps
To edit a function:
Select cell C12 and use the AutoSum list to insert the MIN function; accept the
suggested range.
1. Select the cell containing the function you want to Select cell C12
edit.
2. Select the Insert Function button on the FORMULA

tab.
The Function Arguments dialog box opens.
3. Select the Collapse Dialog button for the argument

Click the Number 1
you want to edit.
The Function Arguments dialog box collapses.
4. Select the range you want to use in the calculation.

© 2016 RW000002 Page 123

5. Release the mouse button Release the mouse button

The range appears in the collapsed Function
Arguments dialog box, as well as in the formula in
both the Formula Bar and the cell.
6. Select the Expand Dialog button

Click
The Function Arguments dialog box expands.
7. Select OK.
Click
The Function Arguments dialog box closes, and the
result of the formula appears in the cell.
The result of the calculation should be 1725.
Function Arguments Dialog Box
10.8 USING AUTO CALCULATE

Concepts
The fastest way to perform a calculation on a range of cells is by using the
AutoCalculate feature. And the best part is, you don’t even have to type in a
formula – it’s automatic! Whenever you highlight a range of cells, the sum of that
range is displayed in the status bar.
Page 124 RW000002 © 2016

However, you aren’t limited to just the SUM function. You can also calculate the
Average, Count, Count Nums, Maximum, and Minimum of the range simply by
right clicking on the Status Bar and choosing the desired function.
Steps
To use the AutoCalculate feature:
1. Select the range you want to calculate.


The results of the enabled AutoCalculate functions Release the mouse button
are displayed in the Status Bar.
3. To enable additional AutoCalculate results, right-

click anywhere on the Status Bar. Right-click on the Status Bar
The Customize Status Bar menu appears.
4. Select the desired AutoCalculate function(s).

The selected function(s) appear in the Status Bar.
5. Select the Status Bar. Left-click on the Status Bar

The Customize Status Bar menu closes.
© 2016 RW000002 Page 125

10.9 MODIFYING FORMULAS USING RANGE BORDERS
Steps
To use range borders to modify a formula:
1. Double-click the cell F9 containing the formula Double click on cell F9

you want to edit.
The formula range references and their
corresponding range borders appear in the same
colour.
2. To change the size of a referenced range, point to

the square range handle at the appropriate corner
of the range border.
The mouse pointer changes to a black, diagonal,
double-headed arrow.
3. Drag the range border to the desired position.

The range changes as you drag.
Page 126 RW000002 © 2016


The result of the modified formula appears in the
cell.
Undo your last action so that it displays the total of only F5:F6. Notice that Excel
shows a green arrow at the top left corner of the cell as it detects there may be
some error in the calculation.
10.10 ERROR CHECKING

Concepts
It is possible to implement certain rules to check for errors in formulas, similar to a
spelling checker. While the rules do not guarantee that your worksheet is error-
free, they can go a long way toward identifying repeated mistakes.
Standard error values associated with using formulas include:
Error Cause
#NAME? Does not recognise text in formula
#DIV/0! Number is divided by zero
#REF! Cell reference is not valid
##### Column is not wide enough to display value
#Value! Wrong type of argument or operand is used
#N/A Value is not available to a function or formula
#NUM! Invalid numeric values in a formula or function
#NULL! Cell references are not separated correctly in a formulas
You can resolve an error by using the options that appear, or you can ignore the
error by clicking Ignore Error. If you ignore an error in a particular cell, the error in
that cell does not appear in further error checks. However, you can reset all
previously ignored errors so that they appear again.
© 2016 RW000002 Page 127

Steps
To use error checking options to correct an error in a formula:
1. Select a cell displaying a green triangle in the upper,

left corner.
The cell is selected, and the error checking smart tag
appears to its left.
2. Point to the error checking smart tag to display the

ScreenTip.
A ScreenTip displays the reason for the identified
error.
3. Click the error checking smart tag to display a list of

error checking options.
A list of available error checking options appears.
Page 128 RW000002 © 2016

4. Select the desired option

The error is corrected, the smart tag list closes, and
the cell is no longer identified as containing an error.
You can also perform an error checking search throughout a worksheet by going
to REVIEW, clicking the Error Checking button in the Formulas Auditing group
and selecting either Update Formula or Ignore Error when errors are found in
the worksheet.
10.11 CREATING AN ABSOLUTE REFERENCE

Concepts
There are two basic types of cell references in Excel: relative and absolute. The
difference between absolute and relative cell references becomes apparent when
you copy formulas from one cell to another.
When you copy a formula containing relative references, the references are
adjusted to the new location. For example, if you create a formula to total column
A, and you then copy that formula to columns B and C, the cell references are
adjusted to total the corresponding values in columns B and C. Relative references
are the default.
Absolute references always refer to the same cell, regardless of where the formula
is copied. Absolute cell references are useful when you do not want a cell reference
to change when a formula is copied to another location. For example, if you create
a formula to calculate the commission for a group of salespeople and the
commission rate of 10% appears in cell C1, you want the formula to always refer to
cell C1, no matter where it may be copied. Making the reference to cell C1 absolute
ensures that the commission calculation is always based on cell C1, even if you
copy the formula to another location. Putting a value such as a commission rate in
a cell, rather than in each formula, is a good idea; if the commission rate changes,
you only have to change the value in cell C1 and all commissions based on the
formula will automatically update.
An absolute reference is designated by a dollar sign ($) before both the column
letter and row number. You can press the [F4] key after typing the cell reference
© 2016 RW000002 Page 129

and Excel will add both dollar signs ($) to make the cell reference absolute. If you
continue to press the [F4] key, you cycle through each of the four types of
references:
Cell Entry Type of Reference Result

C1 Relative Both the row number and column letter are
adjusted when copied.
$C1 Mixed The column letter is not adjusted when
copied.
C$1 Mixed The row number is not adjusted when
copied.
$C$1 Absolute Neither the column letter nor the row number
is adjusted when copied.
Steps
Create a formula with an absolute reference.
Copy the commission formula in cell I5 and paste it into cells I6:I8. Notice that the
formulas did not give the correct results for rows 6 to 8. View the formulas in I6, I7
and I8. The commission is not calculated because due to relative referencing, the
formulas do not reference the commission rate in cell H1. Delete the contents in
cells I5:I8.
1. Select the cell in which you want to enter the formula. Click cell I5
Page 130 RW000002 © 2016

2. Type the desired formula. Type =E5*I1

The formula appears in the Formula Bar and in the
cell.
3. Click anywhere in the cell reference you want to make Click in the text E5 in the
absolute, either in the Formula Bar or in the cell itself. Formula Bar
The insertion point appears in the cell reference.
4. Press [F4] as needed, until the desired type of cell Press [F4] once
reference appears.
Dollar signs ($) appear before both the column letter
and the row number.

Select cell I5; look at the formula in the Formula Bar. The cell reference $I$1
indicates an absolute reference.
Practice the Concept: Copy the formula from cell I5 to the range I6:I8. Press
[Esc] to remove the blinking marquee and the Paste Options button.
Select cell I6 and look at the formula in the Formula Bar.. The first cell reference is
relative and now references cell E6. The second cell reference in the formula is
absolute and continues to reference cell I1.
Close Formula.xlsx without saving.
© 2016 RW000002 Page 131

10.12 USING THE IF FUNCTION

Concepts
Logical functions calculate outcomes based on criteria. If the criteria are true, one
action is taken; if the criteria are false, a different action is taken.
Logical functions can be used in a range of situations. For example, you can use a
logical function to decide if a student has passed a test. If a mark is greater than or
equals a specified value, the student passes. If the mark is less than the specified
amount, the student fails.
The IF function returns one value if a condition is true and another value if a
condition is false. In the example above, if the test score is greater than or equal to
the pass mark, a true value is returned. If the score is less than the pass mark, a
false value is returned.
You can use the IF function to display text as a result of a logical test, but you must
enclose the text you want to display in quotation marks.
For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1
is greater than 10, and "10 or less" if A1 is less than or equal to 10.
The syntax of an IF function is:
=IF(logical test, value_if_true, value_if_false)
Page 132 RW000002 © 2016

Component Description
logical test This can contain cell references, text in quotes, cell
names, and numbers. The items are compared using the
following operators:
= equal to
<> not equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to
value_if_true The outcome if the logical test is true. It can be a number,
formula, cell reference, cell name, text in quotes, or
another function.
value if false The outcome if the logical test is false. It can be a
number, formula, cell reference, cell name, text in quotes,
or another function.
Steps
To use the IF function:
Open the Sales72.xlsx. Display the Bonus worksheet. You want to calculate a
bonus of 10% of sales for a team of salespeople, but they will only receive this
bonus if they exceed their quota.
1. Select the cell in which you want the result of the IF Click cell G8
function to appear.
The cell is selected.
2. Input =if and an open parenthesis ( ( ). Type =if(

=if( appears in the cell and on the formula bar. As you
start typing a function, a Screen Tip is displayed to
help you enter valid arguments.
3. Input the logical test. Type e8>f8

The text appears in the cell and on the formula bar.
4. Input a comma ( , ). Type ,

The comma ( , ) appears in the cell and on the
formula bar.
© 2016 RW000002 Page 133

5. Input the action to be taken if the logical test is true. Type e8*10%
6. Input a comma ( , ). Type ,

The comma ( , ) appears in the cell and on the
formula bar.
7. Input the action to be taken if the logical test is false. Type 0

8. Input the closing parenthesis ( ) ) . Type )

The closing parenthesis ( ) ) appears in the cell and
on the formula bar.

The result of the IF function appears in the cell.
Notice that since the first quarter sales total for Deb Tan was below his quota, a
zero (0) was entered as his bonus.
Enter similar formulas in the range G9:G13 to calculate bonuses for the other
sales people. Then, click anywhere in the worksheet to deselect the range.
Close the workbook without saving it.
Page 134 RW000002 © 2016


Create and work with formulas
1. Open ExFormula.xlsx.
2. In cell B9, use the AutoSum button to total the sales for Qtr 1.
3. In cell F5, use the AutoSum button to total the sales for the Northern region.
4. Select the blank cells in the range F6:F8 and use the AutoSum button to total the
sales for the three regions at the same time. Check each formula on the formula bar
to make sure that columns B through E were calculated for each row.
5. In cell H5, create a formula that subtracts the expenses in cell G5 from the total sales
in cell F5 for the Northern region.
6. In cell I5, type a function that averages the Northern region sales for the four quarters
in the range B5:E5.
7. In cell I6, use the AutoSum list to enter a function that averages the Southern region
sales for the four quarters in the range B6:E6.
8. In cell I7, use the Insert Function button to average the Central region sales for the
four quarters in the range B7:E7.
9. In cell I8, use any method to average the Western region sales for the four quarters in
the range B8:E8.
10. In cell H1, use the AutoSum list to find the maximum quarterly sales for all regions
(the range B5:E8).
11. Use the AutoCalculate feature to verify the answer in cell H1.
12. Use the AutoCalculate feature to find the sum of all sales (the range B5:E8).
13. In cell B14, create a formula that calculates an increase of 15% on the total sales in
cell B9. (Hint: Try multiplying cell B9 by 1.15. Refer to the formula in cell C13 if you
need an example.)
14. In cell B15, create a formula that calculates an increase of 20% on the total sales in
cell B9. (Hint: Try multiplying cell B9 by 120%.)
15. Use the range borders to edit the formula in cell B9. Drag the range border to include
both the first and second quarter sales for all regions. Observe the changed results in
cells B9, C13, C14 and C15. Then, change the formula back to include only the
original range of B5:B8.
© 2016 RW000002 Page 135

LESSON 11 -
CUT, COPY, AND PASTE
 Copying and pasting data

 Cutting data
 Copying formulas
 Paste options
 Paste list
 Filling cells
 Drag-and-drop editing
 Undo and redo
ICDL Spreadsheets Lesson 11- Cut, Copy and Paste
11.1 COPYING AND PASTING DATA

Concepts
When you copy cells that contain text or numbers, Excel creates a copy of the
contents when they are pasted to another location.
Steps
From the Student Folder, open CopyPaste.xlsx.
To copy and paste data:
If necessary, display the HOME tab.
1. Select the cell range A4:A8 you want to copy. Select cell A4:A8
2. Select Copy arrow in the Clipboard group on the

HOME tab.
The Copy menu is displayed below.
© 2016 RW000002 Page 137

Lesson 11- Cut, Copy and Paste ICDL Spreadsheets
3. Select Copy from the menu displayed.

A blinking marquee appears around the selected
cell or range and its contents are copied to the
Office Clipboard.
4. Select the cell or range into which you want to Select cell A12
paste the cell contents.
The cell or range is selected.
5. Select the top part of the Paste button in the

Clipboard group on the HOME tab.
The contents of the Office Clipboard are pasted
into the selected range.
Press [Esc] to remove the blinking marquee and hide the Paste Options button.
Select A12:A16, if necessary, and delete the copied text. Click in a blank cell to
deselect the range.
11.2 CUTTING DATA
Steps
To cut and paste data:
1. Select the cell K1. Click the cell K1

Page 138 RW000002 © 2016

2. Select Cut in the Clipboard group on the HOME tab.

A blinking marquee appears around the selected cell
or range and its contents are placed on the Office
Clipboard.
3. Select the cell or range into which you want to paste Select the cell L1
the cell contents.

The contents of the Office Clipboard are pasted into
the selected range.
11.3 COPYING FORMULAS

Concepts
When you copy cells containing formulas, Excel adjusts the cell references to the
row or column where the formula is pasted. For example, if the formula
=B5+B6+B7+B8 calculates the total of three cells in column B and you copy that
formula to the adjacent cell in column C, Excel adjusts the formula to
=C5+C6+C7+C8 so that the total of the three corresponding cells in column C are
calculated.
When you move cells containing formulas, Excel does not adjust the cell
references in the formulas. The formulas still refer to the original cells for the
© 2016 RW000002 Page 139

calculation. If you move both the formula and the cells containing the data, the cell
references in the formula adjust to the new location of the data.
The Paste button in the Clipboard group on the HOME tab provides a Paste list.
Steps
To copy and paste formulas:
1. Select the cell containing the formula you want to copy. Click cell E5
2. Select Copy in the Clipboard group on the HOME tab.

The Copy menu is displayed below

or range and its contents are copied to the Office
Clipboard.
4. Select the cell or range into which you want to paste Drag to select E6:E8
the formula.

The contents of the Office Clipboard are pasted into
the selected cell or range, the cell references in the
formula change accordingly, and the Paste Options
button appears.
Select cell E6 and look at the function in the Formula Bar. Notice that the SUM
function that was copied from row 5 has adjusted its cell references to refer to the
data in row 6 (=SUM(B6:D6)). Select cell E7 and then cell E8 and look at the
adjusted formulas in the Formula Bar.
Page 140 RW000002 © 2016

Practice the Concept: Copy the formula in cell H5 and paste it into the range
H6:H8. Check the Formula Bar for each cell to see how the formula was adjusted
for each row.
11.4 PASTE OPTIONS

Concepts
The Paste Options button appears in Excel after you paste data into a cell, located
in the bottom right-hand corner of the cell. If you click on the button you are given a
variety of choices that determine how the data can be pasted into that cell.
Steps
To use the Paste Options button.
1. Select the cell or range you want to move or copy. Click cell E5
2. Select the Cut or Copy button in the Clipboard group

on the HOME tab, as desired.
A copy menu is displayed below.

A blinking marquee appears around the selected cell or
range and its contents are copied to the Office
Clipboard.
4. Select the cell or range into which you want to paste Click cell E18
the cut or copied data.
5. Select the top part of the Paste button in the Clipboard

group on the HOME tab.
The data is pasted and the Paste Options button
appears.
© 2016 RW000002 Page 141

6. Select the Paste Options button.

A menu of available paste options appears.
7. Select the Link Cells option from the Paste Options

menu.
The pasted data changes accordingly.
8. To hide the Paste Options button, press [Esc]. Press [Esc]

The Paste Options button closes and the cell from
which the data was copied is deselected.
Practice the Concept: Change the number in cell B5 to 1950 and press [Enter]
Notice that both cells E5 and E18 are updated accordingly. Copy the text Sales
Report in cell A2 and paste it into cell E16. Select the Paste Options button and
the Match Destination Formatting option to paste the text without its original
formatting.
11.5 PASTE LIST

Concepts
As noted above, after you click the Paste Options button, you are presented with a
list of options for how you would like that data to appear in the cell.
Steps
Use the Paste list.
1. Select the cell or range you want to move or copy. Drag A5:A8
2. Select the Cut or Copy button in the Clipboard group

on the HOME tab, as desired.
A copy menu is displayed below.
Page 142 RW000002 © 2016


or range and its contents are copied to the Office
Clipboard.
4. Select the cell or range into which you want to paste Click cell E17
the cut or copied data.
5. Select the bottom part of the Paste button in the

A list of available paste options appears.
6. Select the Transpose option.

The contents are pasted accordingly.
Notice that the transposed vertical row headings now appear as column headings.
Practice the Concept: Copy cell E6. Select cell F18 and use the Paste list to paste
the value of the formula. Look at the formula bar. Notice that only the value was
pasted, not the formula. Change the number in cell B6 to 1850. Notice that cell E6
updates the results of the formula to 5310, but cell F18 still displays the constant
5164.
11.6 FILLING CELLS

Concepts
It is possible to enter data automatically in Excel using the Auto Fill feature. This
feature operates by using data or patterns in existing cells, allowing you to drag and
fill in several cells using the fill handle.
© 2016 RW000002 Page 143

Steps
To fill a range.
1. Select the cell containing the data you want to copy. Click cell B9
2. Point to the fill handle at the bottom-right corner of

the selected cell.
The mouse pointer changes into a solid, black plus
sign (+).
3. Drag the fill handle over the range you want to fill. Drag the fill handle over C9:I9
The range is outlined with a shaded border as you
drag.

The selected range is filled and the Auto Fill
Options button appears.
Click each of the filled cells. Notice that the formula has been adjusted for each one,
relative to its location.
Practice the Concept: Use the fill handle to copy the formula in cell G5 to the range
G6:G8. Click any cell to deselect the range.
11.7 DRAG-AND-DROP EDITING

Concepts
The mouse technique Drag-and-Drop allows you to pick up a cell selection and drop
it into a new area on the worksheet.
Page 144 RW000002 © 2016

Steps
To use drag-and-drop editing to move and copy cells:
1. Select the cell or range you want to move. Drag to select A9:I9
The cell is selected or the range is selected as you
drag.

3. Point to the border of the selected cell or range. Point to the border of A9:I9
The mouse pointer changes, a four-headed arrow
is added to the standard pointer.
4. Drag the cell or range to the desired location. Drag the range to A13:I13
A shaded outline of the cell or range is displayed
as you drag and a ScreenTip appears beside the
mouse pointer showing the location currently
occupied by the outline.

The cell contents move to the new location.
6. Select the cell or range you want to copy. Drag to select A4:I4
The cell is selected or the range is selected as you
drag.
7. Point to the border of the selected cell or range. Point to the border of A4:I4
The mouse pointer changes, a four-headed arrow
is added to the standard pointer.
8. Hold down the [Ctrl] key. Hold [Ctrl]

The mouse pointer changes, the four-headed
arrow disappears and a plus sign (+) appears
beside the standard pointer.
9. While holding [Ctrl], drag the range to the desired Hold [Ctrl] and drag the range to
location. A12:I12
A shaded outline of the range is displayed as you
drag and a ScreenTip appears beside the mouse
pointer showing the location currently occupied by
the outline.
© 2016 RW000002 Page 145


The cell contents are copied to the new location.
11. Release the [Ctrl] key. Release the [Ctrl] key

The mouse pointer reverts to the standard pointer.
Practice the Concept: Use drag-and-drop editing to move the cell contents of the
range A13:I13 back to the range A9:I9. Delete the cell contents of the range
A12:I12. Click any cell to deselect the range.
11.8 UNDO AND REDO

Concepts
The Undo feature allows you to reverse the results of the previous command or
action.
Once you have used the Undo feature, the Redo feature becomes available. The
Redo feature allows you to restore the results of the command or action you
reversed with the Undo feature. Both features can be accessed on the Quick
Access Toolbar.
Page 146 RW000002 © 2016

Steps
To use the Undo and Redo features.
Delete the contents of cell C5 and move cell B9 to A11.
1. To undo the previous command or action, select

the left-hand part of the Undo button on the Quick
Access Toolbar.
The previous command or action is reversed.
2. To redo the undone command or action, select the

left-hand part of the Redo button on the Quick
Access Toolbar.
The command or action is redone.
3. To undo multiple consecutive actions, select the

arrow on the right-hand part of the Undo button.
A list of actions appears, with the most recent
action at the top of the list.
4. To redo multiple consecutive actions, select the

arrow on the right-hand part of the Redo button.
A list of actions appears, with the most recently
undone action at the top of the list.
© 2016 RW000002 Page 147


Copy and move formulas and data
1. Open ExCopyPaste.xlsx.
2. Copy the range A4:A8 and paste to cell A14. .

3. Copy the range B4:E4 and paste to cell B14.
4. Use the Copy and Paste buttons to copy the formula in cell H5 to the range H6:H8.
5. Use the fill handle to copy the formula in cell I5 to the range I6:I8.
6. Use the fill handle to copy the formula in cell B9 to the range C9:I9.
7. In cell B15, enter a formula that multiplies the contents in cell B5 by the projected
increase in cell D12. In the Formula Bar, select D12 and the press F4 on the
keyboard.
8. Use the fill handle to copy the contents in cell B15 to the range C15:E15.
9. Select the range B15:E15, if necessary, and use the fill handle to copy the contents
down to rows 16, 17, and 18.
10. Change the projected increase in cell D12 from 1.08 to 1.12. Notice that all the
projected values update automatically when you enter the new value for cell D12. In
cell A12, change the text in the label from 8% to 12%.
11. Use drag-and-drop editing to move the cell contents in the range E14:E18 to the
range G14:G18. View the formulas in each of the cells G14:G18; notice that since you
did not move the source data, the cell references did not change.
12. Use the Undo button to reverse the previous action.
13. Use drag-and-drop editing to copy the cell contents in the range F4:F8 to the range
F14:F18. Look at each of the cells F14:F18; notice that the cell references changed to
reflect the new location.
14. Copy the range H4:H9 and use the Paste list to paste the values to cell K4. View the
contents of cells K5:K9 in the Formula Bar; notice that only the values of the
formulas were pasted.
15. Change the expenses in cell G5 to 50000. Notice that cell H5 updates the net profit
while cell K5 retains its original value.
Page 148 RW000002 © 2016

LESSON 12 -
DATA MANAGEMENT
 Sorting
 Finding data
 Replacing data
 Finding and replacing cell formats
Lesson 12 –Data Management ICDL Spreadsheets
12.1 SORTING
Concepts
Sorting data is an integral part of data analysis. Sorting data helps you quickly
visualise and understand your data better, organise and find the data that you
want, and ultimately make more effective decisions.
Tip: To assist you when sorting data, you should, when you are creating a list,
avoid blank rows and columns in the main body of the list. An exception to this is
when you might want to insert a blank row before the Total row.
Sorting data is useful in a variety of contexts. You might want to put a list of
names in alphabetical order, compile a list of levels of stock from highest to
lowest, or order rows by colours or icons. You can quickly sort your data by using
the A-Z and Z-A Sort buttons on the Ribbon's DATA tab.
Steps
From the Student Folder, open Sort.xlsx.
To sort a list in ascending or descending order.
If necessary, display the DATA tab.
Page 150 RW000002 © 2016

ICDL Spreadsheets Lesson 12 –Data Management
1. Select any cell in the column you want to sort. Click cell D6
2. Click the Sort A to Z button in the Sort & Filter group

on the DATA tab.
The list is sorted in ascending or descending order
accordingly.
Use the Undo button on the Quick Access Toolbar to undo all sorting and return
the table to its unsorted state.
12.2 FINDING DATA

Concepts
Excel‘s Find and Replace feature can be a powerful tool. You use Find and
Replace to search for — and optionally replace — text or values in a worksheet.
You can narrow the search results by specifying formatting to look for as well as
other search options, including Match Case.
Steps
To find data in a range.
Display the Employees worksheet.
1. Select the range you want to search.

Drag A6:E23

Click HOME
The HOME tab is displayed.
3. Select the Find & Select button.

The Find and Select dialog box opens to the Find
page.
4. Select the Find button.

Click Find
The Find dialog box opens.
© 2016 RW000002 Page 151

5. Type the value you want to find in the Find what box.
Type edwards
The entry appears in the Find what box.
6. Select the Find Next button.

The first occurrence of the Find what entry becomes Click
the active cell.
7. Select the Find All button.

The first occurrence of the Find what entry becomes
the active cell, and a list of all found occurrences Click
appears in the Find and Replace dialog box.
8. Click any entry in the Find All list to activate that cell. Click $A$20 in the Cell
The selected cell becomes the active cell. column
9. Select Close.
The Find and Replace dialog box closes. Click
12.3 REPLACING DATA

Concepts
You can use Excel’s Find and Replace feature to change data. For example, if
you prepare a report or project plan and then you realise that all the department
called “development” needs to be changed to “R&D”, you can use find-replace
(CTRL+H) to do this for you.
Steps
To replace data in a range:
1. Select the range that contains the characters you Drag A6:E23
want to replace.
2. Select the HOME tab. Click HOME

3. Select the Find & Select button.

The Find and Replace dialog box opens with the
Replace page displayed.
Page 152 RW000002 © 2016

4. Select the Replace command. Click Replace...

The Find & Replace dialog box opens.
5. Select the Find what box. Click in the Find what box
The text is selected, or the insertion point appears in
the Find what box.
6. Type the value you want to find. Type development

The value appears in the Find what box.
7. Select the Replace with box. Press [Tab]

The insertion point appears in the Replace with box.
8. Type the desired replacement characters. Type R&D

The characters appear in the Replace with box.

Click
The first occurrence of the Find what entry becomes
the active cell.
10. Select Replace to replace the current occurrence

with the replacement characters, Replace All to Click
replace all occurrences with the replacement
characters, or Find Next to skip the current
occurrence.
The characters are either replaced or skipped, and
the active cell moves to the next occurrence of the
entry in the Find what box.
11. Continue replacing or skipping occurrences as

desired. Click
All remaining matching occurrences are replaced or
skipped, and a Microsoft Excel message box opens
when the search is complete.
12. Select the OK button when you are prompted that the
search is complete. Click
The Microsoft Excel message box closes.
13. Select the Close button.

Click
The Find and Replace dialog box closes.
Click anywhere in the worksheet area to deselect the range.
© 2016 RW000002 Page 153

12.4 FINDING AND REPLACING CELL FORMATS

Concepts
You can use Excel’s Find and Replace feature to change data. For example, if
you prepare a report or project plan and then you realise that all of the red colour
cells need to change to blue. Then, you can use find-replace (CTRL+H) to do this
for you.
. Steps
To find and replace data and formats.
If necessary, display the Employees worksheet.
1. Select the range containing the formatting you want to Drag A6:E23
find or replace.
2. Select the HOME tab. Click HOME

3. Select the Find & Select button in the Editing group.

The Find and Select list opens.
4. Select the Replace command.

The Find and Replace dialog box opens.
5. Select the Find what box. Click in the Find what box
The text is selected, or the insertion point appears in
the Find what box.
6. Type the characters you want to find or delete the Type production
existing characters to find formatting only.
The characters appear in or are deleted from the Find
what box.
7. Select the Replace with box. Press [Tab]

The insertion point appears in the Replace with box.
Page 154 RW000002 © 2016

8. Type the desired replacement characters or delete Press [Delete], if necessary

the existing characters to replace formatting only.
The characters appear in or are deleted from the
Replace with box.
9. Select the Options button.

Click
The Find and Replace dialog box expands to display
the advanced search options.
10. Select the Format button for either the Find what or
the Replace with box, as desired. Click to the
right of Replace with
The Find Format or Replace Format dialog box opens
accordingly.
11. Select the tab on which the formatting you want to Click the Font tab
find or use as a replacement is located.
The corresponding page appears.
12. Select the desired formatting options. Select Italic under Font
The options are selected. style
13. Select the OK button.

Click
The Find Format or Replace Format dialog box
closes, and the corresponding No Format Set
message is replaced with the word Preview,
formatted accordingly.

Click
The active cell moves to first occurrence of the Find
what entry.
15. Select the Replace button to replace the current

occurrence with the replacement formatting, Replace Click
All to replace all occurrences, or Find Next to skip
the current occurrence.
The current occurrence is replaced, and the next
occurrence of the Find what entry becomes the
active cell.
16. Continue replacing or skipping occurrences as

desired. Click
All occurrences are replaced, and a Microsoft Excel
message box opens.
© 2016 RW000002 Page 155

17. Select OK.

Click
The Microsoft Excel message box closes.
18. Select the Close button.

Click
The Find and Replace dialog box closes.
Click in a cell to deselect the range.
Close the Find and Replace dialog box.
Close Sort.xlsx.
Page 156 RW000002 © 2016


Manage data in a worksheet
1. Open ExSort.xlsx.
2. Sort the list in the Employees worksheet in descending order by hire date.
3. Sort the list in the Administration worksheet in ascending order by last name.
4. Display the Employees worksheet.
5. Use the Find and Replace dialog box to find employees with a status of 2. Notice that
Excel locates any entry in the worksheet containing the number 2.
6. Select the Match entire cells contents option in the Find and Replace dialog box.
Now, use the Find All button to find all employees with a status of 2. Notice that
Excel locates entries that contain only the number 2, for a total of 16 found
occurrences.
7. Find and replace all occurrences of a status of 7 with a status of 5. Be sure to find
entire cells only.
© 2016 RW000002 Page 157

LESSON 13 -
CREATING CHARTS
 Inserting a column chart

 Inserting a line chart
 Inserting a bar chart
 Inserting a pie chart
 Resizing a chart
 Deleting a chart
 Adding a chart title
 Changing the chart background
 Changing a column, bar, line, or pie slice colours
 Changing the chart type
 Changing the chart layout
 Copying and moving charts
ICDL Spreadsheets Lesson 13 - Creating Charts
13.1 INSERTING A COLUMN CHART

Concepts
You can create basic charts in Excel by selecting a suggested chart type. It is also
possible to modify the chart, apply predefined styles and layouts, and add
formatting to create a professional-looking chart.
Data which has been arranged in columns or rows on a worksheet can be plotted
in a column chart. A column chart usually displays categories along the horizontal
(category) axis and values along the vertical (value) axis.
Excel Column Chart
Steps
To create a column chart:
From the Student Folder, open Chart.xlsx.
If necessary, select the INSERT tab.
© 2016 RW000002 Page 159

Lesson 13 - Creating Charts ICDL Spreadsheets
1. Select the cell range containing the data you want Select range A2:D6
to chart
2. Select the Column button in the Charts group.

The Column Chart gallery opens.
3. Select the 2-D Clustered Column chart subtype

from the gallery.
The gallery closes and the chart appears in the
worksheet. CHART TOOLS contextual tab is
displayed.
An embedded Clustered column chart is inserted in the worksheet. Select the

chart by clicking it. Press the Delete key to delete the chart.
13.2 INSERTING A LINE CHART

Concepts
Line charts are mostly used to plot changes in data over a period of time, such as
monthly temperature changes or daily changes in stock market prices.
Similar to most other charts, line charts have a vertical axis and a horizontal axis.
If you are plotting changes in data over time, time is plotted along the horizontal or
x-axis and your other data, such as rainfall amounts is plotted as individual points
along the vertical or y-axis.
Page 160 RW000002 © 2016

Excel Line Chart
Steps
To create a line chart:
1. Select the cell range containing the data you Select range A2:D6
want to chart
2. Select the Line button in the Charts group.

The Line Chart gallery opens.
3. Select the Line with Markers chart subtype

from the gallery.
displayed.
An embedded Line with markers chart is inserted in the worksheet.
© 2016 RW000002 Page 161

Select the chart and press the Delete key to delete the chart.
13.3 INSERTING A BAR CHART

Concepts
Bar charts are similar to column charts, but the difference is that bar charts display
horizontal bars.
Excel Bar Chart
Steps
To create a bar chart:
From the Student Data directory, open Chart.xlsx.
If necessary, select the INSERT tab on the Ribbon and the Sheet1 sheet.
1. Select the cell range containing the data you Select range A2:D6
want to chart
Page 162 RW000002 © 2016

2. Select the Bar button in the Charts group.

The Bar Chart gallery opens.
3. Select the Clustered Bar chart subtype from

the gallery.
displayed.
An embedded Clustered Bar chart is inserted in the worksheet. Select the chart
and Press the Delete key to delete the chart.
13.4 INSERTING A PIE CHART

Concepts
Pie charts are excellent for displaying data points as a percentage of the whole.
Excel Pie Chart
Steps
To create a pie chart:
© 2016 RW000002 Page 163

1. Select the cell range containing the data you Select range A2:B6
want to chart
2. Select the Pie button in the Charts group.

The Pie Chart gallery opens.
3. Select the Pie subtype from the gallery.

displayed.
An embedded 2D Pie chart is inserted in the worksheet.
13.5 MOVING AND RESIZING A CHART

Concepts
Excel charts can be moved both within a worksheet and to another one. They can
also be resized to fit correctly within the worksheet using the sizing handles.
Page 164 RW000002 © 2016

Steps
To move and resize a chart.
1. Select the chart you want to move.

A frame with sizing handles appears around the
chart.
2. Drag the chart to the cell C8.

An outline of the chart appears as you drag and the
chart appears in the new location when you release
the mouse button.
3. To move the chart to another worksheet, select Move Click DESIGN then Move
Chart in the DESIGN tab. Chart
The Move Chart dialog box will open.
4. Check the New sheet checkbox and click OK. Click New sheet then OK
The chart will be moved to the selected sheet.
5. To resize a chart, point to the desired sizing handle. Scroll if necessary and point
The mouse pointer changes into a double-headed to the lower, right sizing
arrow. handle
© 2016 RW000002 Page 165

6. Drag the sizing handle to the cell F17. Drag the lower right sizing
The chart expands or contracts as you drag and the handle to the lower, right
resized chart appears when you release the mouse corner of cell H26
button.
13.6 ADDING CHART TITLE

Concepts
Adding a chart title in Excel can help identify work in a worksheet, as well as adding
a finishing touch to a chart, especially if used for presenting data in the future.
Steps
To add a title to a chart:
If necessary select the chart.
1. Select the Add Chart Element button in the Chart

Layouts group.
The Chart Element list is displayed.
2. Select the Chart Title button from the list displayed.

The Chart Titles menu opens.
3. Select the Above Chart option.

The title is displayed on the chart, the Chart Titles
menu closes and the insertion point appears in the
formula bar.
4. Type the desired text as necessary. Type January Sales

The text appears in the formula bar.
5. Select the [Enter] key. Click [Enter]

The text appears in the title box.
Page 166 RW000002 © 2016

Chart Title Added to Pie Chart
To format the chart title, select the title “January Sales”, click HOME tab, and
select the desired font type and font size from the font group. You can edit the title
by selecting it and making the desired changes. To remove the chart title, select
the title, press [Delete].
You can also change the font size and colour of a chart title by going to the HOME
tab, and using the font size and font colour options in the Font group. These steps
can also be applied to chart axes and chart legend text.
13.7 CHANGING THE CHART BACKGROUND

Concepts
Changing the background of a chart can add depth to the chart’s data, and make
the colours of a chart more defined.
Steps
To change the chart background.
If necessary, select the Design tab on the Ribbon and the Sheet1 sheet.
1. Select the FORMAT tab from the CHART TOOLS

contextual tab..
The Format tab is selected and the relevant commands
are displayed on the ribbon.
© 2016 RW000002 Page 167

2. Select the Chart Area option in the Current Selection

group.
The Chart Area list opens.
3. Select the Format Selection option from the Current

Selection group.
Format Chart Area pane is displayed on the right pane.
4. Select the FILL option from the right pane.

The FILL list is displayed
5. Select Gradient fill from the list displayed.

The selected fill option is applied on the chart
background.
Page 168 RW000002 © 2016

Chart Background Updated on Pie Chart
13.8 TO FORMAT A CHART TITLE, CHART AXIS, CHART

LEGEND.
Concepts
When you create a chart the legend is by default displayed below the chart. The
legend is linked to the graphically displayed data in the plot area of the chart and
helps you to better understand what the chart represents. You can format a legend
by changing its fill colour or by changing the font size and colour of the legend text.
Steps
To change the Legend fill colour select the chart.
1. Select the chart legend. Click the chart legend

The chart legend is selected.
2. On the FORMAT tab, in the Current Selection group,

click the Format Selection button.
Click
© 2016 RW000002 Page 169

3. Click Fill & Line.
4. Select the Solid fill option. Click Solid fill
5. Select the Color button. Click the Color button
6. Click the colour required. Click the appropriate colour

The desired colour is selected.
Steps
To change the font size and colour of the chart legend text, chart title text or chart
axis select the chart.
1. Select the chart title text, chart axis or chart legend text Click the appropriate option
to change.
2. On the HOME tab, in the Font group, click the Font

Size or Font Color buttons.
3. Click the font size or font colour required. Click the appropriate font
size or font colour
Page 170 RW000002 © 2016

13.9 CHANGING A COLUMN, BAR, LINE OR PIE SLICE

COLOURS
Concepts
You can format the column, bar or pie slice colours and give a different look to
your chart. Depending on the chart type, you can change the colour of a data
series (represented by rectangles of the same colour) in a column or bar chart, a
data point (represented by a single data value), or a pie slice in a pie chart.
Steps
To change the pie slice colours:
If necessary, select the DESIGN tab.
1. Select the chart. Click in the chart area, if

Selection handles appear around the chart. necessary
2. Select the lower right quadrant slice of the chart.
3. Right-click on the selected slice for which you want to

change the colour and select the Fill option
The shortcut menu is displayed and fill list with
different colours are displayed.
© 2016 RW000002 Page 171

4. Select the desired colour from the gallery.

The selected colour is applied on the slice.
Click any cell in the worksheet to deselect the chart.
13.10 CHANGING THE CHART TYPE

Concepts
You can change the chart type of the whole chart to give it a different look, or you
can select a different chart type for any single data series, which turns the chart
into a combination chart. There are a large range of different chart types, including
a clustered column chart, 3-D clustered column chart, line chart, bar chart, and
more.
Updated Chart Type
Steps
To change the chart Type:
Page 172 RW000002 © 2016
If necessary, select the DESIGN tab.
1. Select the chart. Click in the chart area, if

Selection handles appear around the chart. necessary
2. Select the Change Chart Type button in the Type

group.
The Change Chart Type dialog box is displayed.
3. Select the Recommended Charts tab. Click Recommended Charts

The Recommended Charts are displayed. tab
4. Select the desired chart from the gallery in the right

pane of the dialog box.
The chart is highlighted in the gallery.

The Change chart type dialog box closes and the new Click
chart type is displayed.
13.11 ADDING DATA LABELS TO A CHART

Concepts
A data label in a chart helps you to quickly identify data series in a chart at
particular points. They are linked to the data values by default and automatically
update when changes are implemented to these values.
Steps
1. Select the chart. Click the chart

The chart is selected.
© 2016 RW000002 Page 173

2. On the DESIGN tab, in the Chart Click Add Chart Element

Layouts group, select the Add Chart
Element button.
The Add Chart Element options will
appear.
3. Choose Data Labels. Click Data Labels
Data labels options will appear.
4. Choose the location required for the Select the appropriate location.
data labels.
Choose from the list of data label
locations to apply to the chart.
Tip: If you select More Data Label Options… from the Data Labels options list, you can
choose options such as displaying values or percentages as data labels.
13.12 CHANGING THE CHART LAYOUT

Concepts
Excel 2013 offers some useful chart layouts which can be used to give charts a
new and interesting look. Besides supporting a dozen of styles, many layouts alter
the positions of data labels, which is helpful in a situation when you’re not sure
where to position data labels.
Steps
To change the chart layout:
If necessary, select the chart.
1. Select the DESIGN tab of the Ribbon.

The DESIGN tab appears.
2. Select the Quick Layout button in the Chart Layout

group.
The Chart Layout gallery opens.
Page 174 RW000002 © 2016

3. Select the Layout 4 from the Chart Layout gallery.

The selected layout is applied to the chart.
13.13 COPYING AND MOVING CHARTS

Steps
Change the chart location by moving a chart to a new sheet.
If necessary, select the chart.
1. Select the chart. Click the chart.

The chart is selected.
© 2016 RW000002 Page 175

2. Select the Move Chart button in the Location group.

The Move Chart dialog box opens.
3. Select the New sheet option. Click New sheet:

The New sheet option is selected.

Click .
The Move Chart dialog box closes, and the chart is
moved to a chart sheet.
13.14 DELETING A CHART

Steps
To delete a chart:
1. Select the chart. Click the chart area, if

The chart is selected. necessary
2. Press keyboard Delete. Delete

The selected chart is deleted.
Page 176 RW000002 © 2016


Create and format an embedded chart
1. Open the ExChart.xlsx file.
2. Select the range A4:D10 on the Totals sheet.
3. Insert a 3-D Column chart.
4. Move and resize the chart so that in spans cells A12 through G25.
5. Change the chart type to a Clustered Column.
6. Move the chart to a new sheet called Totals chart.
© 2016 RW000002 Page 177

LESSON 14 -
USING PAGE SETUP
 Worksheet margins
 Worksheet orientation
 Worksheet page size
 Headers and footers
 Header and footer fields
 Scaling your worksheet to fit a page(s)
 Repeating row and column labels
 Changing sheet options
ICDL Spreadsheets Lesson 14 – Using Page Setup
14.1 WORKSHEET MARGINS

Concepts
Page margins are the blank spaces located between the worksheet data and the
edges of the printed page. You can insert headers, footers, and page numbers in
the page margins.
You can use predefined margins, specify custom margins, or centre the worksheet
horizontally / vertically on the page. This will help you better align a worksheet on
a printed page.
Page Setup options in Excel
Steps
From the Student Folder, open Margin.xlsx.
To change worksheet margins.
1. Select the PAGE LAYOUT tab on the Click PAGE LAYOUT

Ribbon.
The PAGE LAYOUT tab is displayed.
2. Select the Margins button. Click Margins

The Margins gallery opens.
© 2016 RW000002 Page 179

Lesson 14 – Using Page Setup ICDL Spreadsheets
3. To use pre-defined margins, select the Click Wide

desired option from the Margins gallery.
Excel applies the selected margins, and
page break indicator lines appear in the
worksheet.
To set specific margins, select Custom Margins…, and set top, bottom, left, and
right margins.
Practice the concept: Click the Margins button again and select Narrow from the
Margins gallery. Notice the new position of the page break indicator lines.
14.2 WORKSHEET ORIENTATION

Concepts
In Excel, you select portrait or landscape page orientation, which affects the layout
of the printed page. You also can adjust the size setting to match the size of the
paper you plan to use to print your worksheet.
Steps
To change the worksheet orientation:

Ribbon.
2. Select the Orientation button. Click Orientation

The Orientation gallery opens.
3. Select Portrait or Landscape. Click Portrait

The desired orientation is selected and the
Orientation gallery closes.
Page 180 RW000002 © 2016

14.3 WORKSHEET PAGE SIZE

Steps
To change the page size:

Ribbon.
2. Select the Size button. Click Size

The Size gallery opens.
3. Select the desired size. Click A3

The desired size is selected and the Size
gallery closes.
It is also possible to adjust page setup to fit worksheet contents on a specified

number of pages by opening the PAGE LAYOUT tab, going to the Scale to Fit
group, and entering the number of pages required in the Width and Height boxes.
14.4 HEADERS AND FOOTERS

Concepts
You can add headers or footers at the top or bottom of a printed worksheet. For
example, you might create a footer that has page numbers, the date and time, and
the name of your file. You can insert headers or footers in Page Layout view where
you can see them, or you can use the Page Setup dialog box.
Headers and footers are not displayed on the worksheet in Normal view — they are
only displayed in Page Layout view and on the printed pages.
Steps
To create a header and footer for the current worksheet.
1. Select the VIEW tab. Click VIEW

The VIEW tab is displayed.
© 2016 RW000002 Page 181

2. Select Page Layout from the Workbook Click Page Layout

Views group.
The Page Layout view is applied.
3. Scroll up to elect the Header & Footer area

in the worksheet.
Excel switches to Page Layout view, the
Header and Footer Tools contextual
Design tab is displayed to the right of the
standard tabs, three Header section boxes
appear above the worksheet and the
insertion point is positioned in the centre
section box.
4. Select the desired section box. Click in the Left section box
The insertion point is positioned in the
selected box.
5. Type the desired text. Type Date printed-

The text appears in the section box.
6. To insert an information code, select the

appropriate button in the Header and Footer
Elements group on the Design tab
The code appears in the section box.
Click
7. To enter Footer information, select the Go to

Footer button in the Navigation group on
the Design tab.
Excel displays the Footer section boxes and
the insertion point is positioned in the
corresponding Footer section box. Click
8. Select the desired section box. Click in the Left section box
The insertion point is positioned in the
selected box.
9. Type the desired text or select the desired

code in the Header and Footer Elements
group.
The text or code appears in the section box.
Click
Page 182 RW000002 © 2016

10. Select any cell in the worksheet. Click A1


The VIEW tab is displayed
12. Select the Normal button in the Workbook Click

Views group.
The worksheet returns to Normal view.
Practice the concept: Click in the Center section box. Notice that the date, rather
than the code, is now displayed in the left section box. Type Monthly Sales
Figures.
Steps
To edit or delete text in headers, footers in a worksheet.
1. On the INSERT tab, in the Text group, click

the Header & Footer button.
By default, the header section opens; to go to
the footer, on the DESIGN tab, in the
Navigation group, click the Go to Footer
button.
2. Edit or delete the text as required.

Delete the appropriate text
The text will be deleted.
© 2016 RW000002 Page 183

14.5 HEADER AND FOOTER FIELDS

Steps
To use built-in headers and footers.
1. Select the INSERT tab. Click INSERT

The INSERT tab is displayed.
2. Select Page Layout from the Workbook Views Click

group.
The Page Layout view is applied.
3. Scroll up to elect the Header & Footer area in Scroll up to select the Header
the worksheet. Section.
Excel switches to Page Layout view, the Header
and Footer Tools contextual Design tab is
displayed to the right of the standard tabs, three
Header section boxes appear above the
worksheet and the insertion point is positioned in
the centre section box
4. To insert a built-in Header, select the Header

button in the Header & Footer group on the
Design tab.
The Header menu opens.
Click
5. Select the desired option from the Header menu. Click Sheet1, Confidential, Page
The selected Header text appears in the section 1
boxes, the Design tab closes and the INSERT
tab is displayed.
6. Click anywhere in the Header. Click in the Header area

The Design tab is displayed.
Page 184 RW000002 © 2016

7. To insert a built-in Footer, select the Footer

button in the Header & Footer group on the
Design tab.
The Footer menu opens.
Click
8. Select the desired option from the Footer menu. Click the last option in the Footer
The selected Footer text appears in the section menu
boxes, the DESIGN tab closes and the INSERT
tab is displayed.

The VIEW tab is displayed.
10. Select the Normal button in the Workbook Click

Views group.
The worksheet changes to Normal view.
14.6 SCALING YOUR WORKSHEET TO FIT YOUR

PAGE(S)
Concepts
To better fit printed pages, you can scale a worksheet for printing by shrinking or
enlarging its size. You can specify the number of pages that you want to fit the
worksheet in when printed, and adjust the worksheet scale to fit the paper width of
printed pages.
Steps
To scale a worksheet to fit on fewer pages:
Preview the worksheet. Scroll through the pages; notice that the printed
worksheet will be 6 pages long. Then, close print preview.
© 2016 RW000002 Page 185

1. Select the PAGE LAYOUT tab. Click PAGE LAYOUT

The PAGE LAYOUT tab appears.
2. Select the arrow on the right-hand part of the Width Click on the Width button
button in the Scale to Fit group.
The Width list opens.
3. Select how many pages wide you want the printout to Click 1 page
be.
The option is selected and the Scale percentage is
adjusted.
4. Select the arrow on the right-hand part of the Height Click on the Height button
button in the Scale to Fit group.
The Width list opens.
5. Select how many pages tall you want the printout to Click 2 pages
be.
The option is selected and the Scale percentage is
adjusted.
Open print preview; notice that there are now only 2 printed pages. Then, close print
preview.
Practice the Concept: Select the Scale to Fit launcher arrow to open the Page
Setup dialog box. Return the worksheet to its original settings by changing the
Adjust to figure under Scaling to 100%. Click the OK button. Notice that the Width
and Height options in the Scale to Fit group have reset to Automatic.
14.7 REPEATING ROW AND COLUMN LABELS

Concepts
If a worksheet spans more than one page, you can print row and column headings
or labels on every page, which ensures that the data is properly labelled.
Steps
To repeat row or column labels on each printed page.
Page 186 RW000002 © 2016

Preview the document in the Backstage view. View pages 2 & 3. Notice that there
are no labels above the columns. View pages 4, 5 & 6. Notice that there are no
labels to the left of the column.
1. Select the PAGE LAYOUT tab. Click PAGE LAYOUT

The PAGE LAYOUT tab appears.
2. Select the Print Titles button.

The Page Setup dialog box appears with the Sheet
page displayed.
3. Select the Collapse Dialog button to the right of the Click Rows to repeat at top
Rows to repeat at top box under Print titles.
The Page Setup dialog box collapses.
4. To repeat the labels in a single row, click anywhere in Drag cells A1 to A4 to select
the row, or drag to select multiple rows. rows 1 to 4
A flashing outline indicates the rows selected as you
drag.

The rows are selected.
6. Click the Expand Dialog button.

Click
The Page Setup dialog box expands and the range
appears in the Rows to repeat at top box.
7. Select the Collapse Dialog button to the right of the Click Columns to repeat at
Columns to repeat at left box under Print titles.
left
The Page Setup dialog box collapses.
8. To repeat the labels in a single column, click Click cell A1 to select column
anywhere in the column, or drag to select multiple A
columns.
A flashing outline indicates the column selected.

The column is selected.
10. Click the Expand Dialog button.

Click
The Page Setup dialog box expands and the range
appears in the corresponding box.
© 2016 RW000002 Page 187

11. Select OK.

Click
The Page Setup dialog box closes.
Preview pages 1 to 3. Notice that the titles in cells A1 and A2 and the months of the
year in row 4 appear at the top of each page. View pages 4 to 6. Notice that the
titles in column A appear at the left of each page, and the titles in cells A1 and A2
and the months of the year in row 4 appear at the top of each page. Close Print
Preview.
14.8 CHANGING SHEET OPTIONS

Concepts
There are various options in Excel that you can modify to make setting up your
workbooks quicker and easier according to your preferences.
Steps
To change gridlines and headings options:
1. Select the PAGE LAYOUT. Click PAGE LAYOUT

2. To hide or display the on-screen gridlines, deselect or

select the View option under Gridlines in the Sheet
Options group, as desired.
The gridlines are hidden or displayed accordingly.
3. To enable or disable gridlines for printing, select or

deselect the Print option under Gridlines in the
Sheet Options group, as desired.
The gridlines are enabled or disabled for printing
accordingly.
Page 188 RW000002 © 2016

4. To hide or display the on-screen column and row

headings, deselect or select the View option under
Headings in the Sheet Options group, as desired.
The headings are hidden or displayed accordingly.
5. To enable or disable column and row headings for

printing, select or deselect the Print option under
Headings in the Sheet Options group, as desired.
The headings are enabled or disabled for printing
accordingly.
Close Margin.xlsx without saving.
© 2016 RW000002 Page 189


Using Page Setup
1. Open ExMargin.xlsx.
2. Change all the margins to .5 and the header and footer margins to .25.
3. Centre the worksheet horizontally on the page.
4. Change the orientation to landscape, and scale the worksheet to fit on 1 page wide by
3 pages tall.
5. Select the built-in footer Page 1 of ?.
6. Create a custom header by adding the title District Sales Report. Make the title
centred.
7. Create a custom footer. Add the file name at the left and the date at the right. Do not
remove the page numbers in the centre.
8. Select any cell in the worksheet, then return to Normal view.
9. Set the option to print the gridlines.
10. Repeat the months of the year (row 4) at the top of each printed page.
11. Repeat the district and product names (column A) at the left of each printed page.
12. Preview all pages of the worksheet.
13. Vertically centre the worksheet and return the scaling to 100%.
18. Return the worksheet to Normal view.
Page 190 RW000002 © 2016

LESSON 15 -
PRINTING
 Print Preview
 Printing the current worksheet
 Printing a selected range
 Printing a page range
 Printing multiple copies
Lesson 15 - Printing ICDL Spreadsheets
15.1 PRINT PREVIEW

Concepts
Preview and printing is carried out in Microsoft Office Backstage view.
Steps
From the Student Folder, open Print.xlsx.
To Preview the current worksheet before printing:
1. Select the FILE tab

Click
2. Select the Print option

The Preview of the document is displayed on the right Click
pane.
3. Select the Zoom to Page button.

The preview zooms in.
Click
4. Select the Zoom to Page button again.

The preview zooms out.
Click
5. Select the Next Page arrow to view the next page in

a multiple page printout. Click
The next page appears in print preview.
6. Select Previous Page arrow to view the previous

page in a multiple page printout. Click
The previous page appears in print preview.
Page 192 RW000002 © 2016

ICDL Spreadsheets Lesson 15 - Printing
15.2 PRINTING THE CURRENT WORKSHEET

Steps
To print the current worksheet:
1. Select the FILE tab.

Click
2. Select the Print option.

The Preview of the document is displayed on Click
the right pane.
3. To choose the printer you want to use, select

the Printer list.
A list of available printers appears.
4. Select the desired printer from the list. Click the highlighted printer
The printer is selected.
5. To set options for the selected printer, select Click Printer Properties
the Properties button.
The printer settings dialog box for the
selected printer opens (the available settings
vary according to the type of printer).
6. Select Print.
Click
© 2016 RW000002 Page 193

15.3 APPLY AUTOMATIC TITLE ROWS TO ALL

PRINTED PAGES
Concepts
Applying automatic title rows to all printed pages of a worksheet is useful for long
tables that may contain a lot of data. Having a title on each page to differentiate
the rows will be effective in keeping track of what you are viewing.
Steps
1. Go to the Page Setup group in the Click PAGE LAYOUT

PAGE LAYOUT tab.
The Page Setup section will appear.
Click Print Titles

2. Choose the Print Titles option.
The Page Setup dialogue box will
appear.
3. In the Rows to repeat at top box, click Click

the box at the right hand side.
You can select the rows you wish to
repeat.
4. Select the row you want to repeat at the Click Row 4 in the worksheet
top of the printed pages.
The row is selected.
5. Implement your selection. Click OK
Page 194 RW000002 © 2016

15.4 PRINTING A SELECTED RANGE

Steps
To print a selected worksheet range:
1. Select the range you want to print. Drag to select A1:H10


3. Hold [Ctrl] and select additional ranges, if Hold [Ctrl] and drag to select A18:H22
desired.
The additional ranges are selected as you
drag.

The additional range is selected.
5. Select the FILE tab.

Click

The Preview of the document is displayed Click
on the right pane.
8. Select Print Selection from the Settings list.

The option is selected.
9. Select Print.
Print preview closes, and Excel prints the
selected ranges.

© 2016 RW000002 Page 195
15.5 PRINTING A PAGE RANGE

Steps
To print a page range:

Click
2. Select the Print option.

The Preview of the document is
Click
displayed on the right pane.
3. Enter the page range you want to Enter 2 in the first box and 3 in the second
print. box of the pages option.
The numbers appear in the Pages
boxes.
4. Select Print.
The Print dialog box opens.
Click
15.6 PRINTING MULTIPLE COPIES

Steps
To print multiple copies of a worksheet:
1. Select the range A4:H10. Select the range A4:H10.

Click
Page 196 RW000002 © 2016


The Preview of the document is displayed Click
on the right pane.
4. Enter the number of copies you want to Click the up arrow in the Copies spin box
print in the Copies spin box.
to display 2 in the Copies box
The number appears in the Copies spin
box.
5. Select Print.
The Print dialog box opens.
Click
Close Print.xlsx without saving.
© 2016 RW000002 Page 197


Printing
1. Open ExPrint.xlsx.
2. Preview the worksheet.
3. Zoom to page; then zoom out.
4. Use the Print button to print the current worksheet.
5. Select the data for District 1 and 2 from January through the QTR 2 totals (A4:I16).
6. Print two copies of the selected range.
7. Print just pages 2 and 3 of the worksheet.
Page 198 RW000002 © 2016

ICDL Syllabus
Ref ICDL Task Item Location Ref ICDL Task Item Location
1.1.1 Open, close a spreadsheet 1.1 Starting 2.1.1 Understand that a cell in a 3.5 Entering Text
application. Open, close Excel 2013 worksheet should contain
spreadsheets. only one element of data,
1.5 Opening a (for example, first name
Workbook detail in one cell, surname
1.7 Closing a detail in adjacent cell).
Workbook 2.1.2 Recognise good practice in 12.1 Sorting
1.12 Exiting creating lists: avoid blank
Excel rows and columns in the
main body of list, insert
1.1.2 Create a new spreadsheet 1.4 Creating a blank row before Total row,
based on default template. Workbook ensure cells bordering list
are blank.
1.1.3 Save a spreadsheet to a 1.6 Saving a
location on a drive. Save a 2.1.3 Enter a number, date, text 3.6 Entering
New Workbook in a cell
spreadsheet under another Numbers
name to a location on a 3.10 Saving the
Workbook with 3.5 Entering
drive text
Another Name
1.1.4 Save a spreadsheet as 2.1.4 Select a cell, range of 4.1 Selecting a

3.11 Save a adjacent cells, range of
another file type like: Workbook as Cell
template, text file, software non-adjacent cells, entire
Another File worksheet. 4.2 Selecting a
specific file extension, Type range of Adjacent
version number. Cells
1.1.5 Switch between open 1.5 Opening a 4.3 Selecting a
spreadsheets. Workbook range of Non-
adjacent Cells
1.2.1 Set basic 1.3 Excel
options/preferences in the Options 4.4 Selecting the
application: user name, entire worksheet
default folder to open, save
spreadsheets. 2.2.1 Edit cell content, modify 3.7 Entering Text
existing cell content
1.2.2 Use available Help 2.1 Using 3.8 Editing Data
functions. Microsoft Excel 2.2.2 Use the undo, redo 11.8 Undo and
Help and command
Resources Redo
2.2 Working with 2.2.3 Use the search command 12.3 Finding
Excel Help for specific content in a Data
worksheet.
1.2.3 Use magnification/zoom 1.11 Using 2.2.4 Use the replace command 12.4 Replacing
tools. Magnification for specific content in a Data
/Zoom Tools worksheet.
1.2.4 Display, hide built-in 1.10 Hiding the 2.2.5 Sort a cell range by one 12.1 Sorting
toolbars. Restore, minimise Ribbon criterion in ascending,
the ribbon. descending alphabetic
order.
© 2016 RW000002 Page 199

2.3.1 Copy the content of a cell, 8.8 Inserting 3.1.4 Modify column widths, row 5.1 Adjusting the
cell range within a Cut or heights to a specified value, Columns Width
worksheet, between Copied Cells to optimal width or height.
worksheets, between open 5.2 Adjusting the
spreadsheets. Row Height
2.3.2 Use the autofill tool/copy 5.3 Automatically

11.6 Filling Cells
handle tool to copy, Adjusting
increment data Column
2.3.3 Move the content of a cell, 8.8 Inserting Cut 3.1.5 Freeze, unfreeze row 5.6 Freezing and
cell range within a or Copied Cells and/or column titles. Unfreezing
worksheet, between Columns and
worksheets, between open Rows
spreadsheets.
3.2.1 Switch between 1.8 Working with
2.3.4 Delete cell contents 3.8 Editing Data worksheets. Worksheets
3.1.1 Select a row, range of 4.5 Selecting a 3.2.2 Insert a new worksheet, 1.8 Working with
adjacent rows, range of Row delete a worksheet. Worksheets
non-adjacent rows.
4.6 Selecting a
Range of 3.2.3 Recognise good practice in 1.8 Working with
Adjacent rows naming worksheets: use Worksheets
meaningful worksheet
4.7 Selecting a names rather than accept
Row of Non- default names.
Adjacent rows
3.2.4 Copy, move, rename a 1.8 Working with
3.1.2 Select a column, range of 4.8 Selecting an worksheet within a Worksheets
adjacent columns, range of Entire Column spreadsheet.
non- adjacent columns.
4.9 Selecting a 4.1.1 Recognise good practice in 10.1 Using Basic
Range of formula creation: refer to Formulas
Columns cell references rather than
type numbers into formulas.
4.10 Selecting a
Range of Non- 4.1.2 Create formulas using cell 10.1 Using Basic
Adjacent references and arithmetic Formulas
Columns operators (addition,
subtraction, multiplication, 10.2 Entering
3.1.3 Insert, delete rows and division). Formulas
5.4 Inserting
columns. Columns and 4.1.3 Identify and understand 10.10 Error
Rows standard error values Checking
5.5 Deleting associated with using
Columns and formulas: #NAME?,
Rows #DIV/0!, #REF!.
4.1.4 Understand and use 10.11 Creating
relative, absolute cell an Absolute
referencing in formulas. Reference
4.2.1 Use sum, average, 10.3 Basic

minimum, maximum, count, Functions
counta, round functions.
Page 200 RW000002 © 2016

4.2.2 Use the logical function if 10.12 Using the 6.1.1 Create different types of 13.1 Inserting a
(yielding one of two specific IF Function charts from spreadsheet Column Chart
values) with comparison data: column chart, bar
operator: =, >, <. chart, line chart, pie chart. 13.2 Inserting a
Line Chart
5.1.1 Format cells to display 6.4 Comma Style
numbers to a specific 13.3 Inserting a
number of decimal places, 6.5 Decimal Bar Chart
to display numbers with, Places
13.4 Inserting a
without a separator to Pie Chart
indicate thousands.
6.1.2 Select a chart. 13.1 Inserting a
5.1.2 Format cells to display a 6.2 Accounting
date style, to display a Column Chart
Number Style
currency symbol
6.1.3 Change the chart type 13.10 Changing
5.1.3 Format cells to display 6.3 Percent Style the Chart Type
numbers as percentages.
6.1.4 Move, resize, delete a 13.5 Moving and
5.2.1 Change cell content 7.2 Changing the chart. Resizing a Chart
appearance font sizes, font Font
types.
7.3 Changing 6.2.1 Add, remove, edit a chart 13.6 Adding
Font Size title Chart Title
5.2.2 Apply formatting to cell 7.4 Bold and 6.2.2 Add data labels to a chart: 13.11 Adding
contents: bold, italic, Italic values/numbers, Data Labels to a
underline, double underline. percentages. Chart
7.5 Underling
Text 6.2.3 Change chart area 13.8 To Format a
background colour, legend Chart Legend
5.2.3 Apply different colours to 7.6 Font Colour fill colour.
cell content, cell 13.7 Changing
background the Chart
5.2.4 Copy the formatting from a Background
8.7 Format
cell, cell range to another Painter 6.2.4 Change the column, bar, 13.9 Changing a
cell, cell range.
line, pie slice colours in the column, bar, line
5.3.1 Apply text wrapping to 7.8 Text chart or pie slice
contents within a cell, cell Wrapping colours
range.
5.3.2 Align cell contents: 8.2 Vertical
horizontally vertically. Alignment 6.2.5 Change font size and 13.8 To Format
Adjust cell content colour of chart title, chart A Chart Title,
orientation axes, chart legend text. Chart Axis, Chart
5.3.3 Merge cells and centre a Legend.
8.1 Merging
title in a merged cell. Cells 7.1.1 Change worksheet 14.1 Worksheet
margins: top, bottom, left, Margins
5.3.4 Add border effects to a cell, 8.4 Adding
right.
cell range: lines, colours. Borders
7.1.2 Change worksheet 14.2 Worksheet
8.5 Drawing orientation: portrait, Orientation
Borders landscape, paper size
7.1.3 Adjust page setup to fit 14.6 Scaling
worksheet contents on a Your Worksheet
specified number of pages. To Fit Your
Page(S)
© 2016 RW000002 Page 201

Ref ICDL Task Item Location
7.1.4 Add, edit, delete text in 14.4 Header and
headers, footers in a Footers
worksheet.
7.1.5 Insert and delete fields: 14.4 Header and
page numbering Footers
information, date, time, file
name, worksheet name into 14.5 Header and
headers, footers. Footer Fields
7.2.1 Check and correct 3.9 Spell Check

spreadsheet calculations
and text. 10.10 Error
Checking
7.2.2 Turn on, off display of 14.8 Changing
gridlines, display of row and Sheet Options
column, headings for
printing purposes
7.2.3 Apply automatic title row(s) 15.3 Apply
printing on every page of a Automatic Title
printed worksheet. Rows To All
Printed Pages
7.2.4 Preview a worksheet. 15.1 Print

Preview
7.2.5 Print a selected cell range 15.2 Printing the

from a worksheet, an entire Current
worksheet, number of Worksheet
copies of a worksheet, the
entire spreadsheet, a 15.4 Printing a
selected chart. Selected Range
15.5 Printing a
Page Range
15.6 Printing
Multiple Copies
Page 202 RW000002 © 2016

Congratulations! You have reached the end of the ICDL Spreadsheets book.
You have learned about the key skills relating to spreadsheet applications, including:
 Working with spreadsheets and saving them in different file formats.

 Choosing built-in options, such as the Help function, within the application to
enhance productivity.
 Entering data into cells; using good practice in creating lists.
 Select, sort and copy, move and delete data.
 Edit rows and columns in a worksheet.
 Copy, move, delete, and appropriately rename worksheets.
 How to create mathematical and logical formulas using standard spreadsheet
functions; use good practice in formula creation; recognise error values in formulas.
 Formatting numbers and text content in a spreadsheet.
 Choose, create, and format charts to communicate information meaningfully.
 Adjust spreadsheet page settings.
 Check and correct spreadsheet content before finally printing spreadsheets.
Having reached this stage of your learning, you should now be ready to undertake ICDL
certification testing. For further information on taking this test, please contact your ICDL test
centre.
Nu-Vision High School
PO Box 2681, Rusororo Sector
Kabuga 2, Kigali
Rwanda
icdlafrica.org
Page 204 RW000002 © 2016

ICDL Spreadsheets 2013 5.0 - Nu-Vision High School

Uploaded by

Copyright:

Available Formats

ICDL Spreadsheets 2013 5.0 - Nu-Vision High School

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

ICDL Spreadsheets 2013 5.0 - Nu-Vision High School

Uploaded by

Copyright:

Available Formats

ICDL SPREADSHEETS

Nu-Vision High School

Page i ICDL Spreadsheets 2013 S5.0 V1 130616 © 2016

Screenshots used with permission from Microsoft.

Page ii ICDL Spreadsheets 2013 S5.0 V1 170616 © 2016

On completion of this module you will be able to:

What are the benefits of this module?

How to use this book

© 2016 RW000002 Page iii

LESSON 1 - EXPLORING MICROSOFT EXCEL 2013 ............................................................... 1

LESSON 2 - GETTING HELP .................................................................................................... 20

LESSON 3 - BASIC WORKBOOK SKILLS .............................................................................. 25

LESSON 4 - SELECTION ......................................................................................................... 47

LESSON 5 - WORKING WITH COLUMNS AND ROWS ........................................................... 55

LESSON 6 - NUMBER FORMATTING ...................................................................................... 69

LESSON 7 - TEXT FORMATTING ............................................................................................ 77

Page vi RW000002 © 2016

LESSON 8 - CELL FORMATTING ............................................................................................ 91

LESSON 9 - WORKING WITH TABLES ................................................................................. 109

LESSON 10 - FORMULAS ...................................................................................................... 112

© 2016 RW000002 Page vii

LESSON 11 - CUT, COPY, AND PASTE ................................................................................ 136

LESSON 12 - DATA MANAGEMENT ..................................................................................... 149

LESSON 13 - CREATING CHARTS ........................................................................................ 158

Page viii RW000002 © 2016

LESSON 14 - USING PAGE SETUP ....................................................................................... 178

LESSON 15 - PRINTING ......................................................................................................... 191

© 2016 RW000002 Page ix

In this section, you will learn about:

1.1 STARTING EXCEL 2013

2. Point to All Programs.

3. Click Microsoft Office 2013.

4. Click Microsoft Excel 2013.

5. Click Blank workbook and a new workbook

1.2 THE USER INTERFACE

Page 2 RW000002 © 2016

© 2016 RW000002 Page 3

Quick Access Toolbar

Page 4 RW000002 © 2016

1.3 EXCEL OPTIONS

1. Click the FILE tab.

The Excel Options dialog box is displayed.

3. Select the General category from the left hand

4. Enter a user name in the User name box and click

© 2016 RW000002 Page 5

1. Click the FILE tab.

The Excel Options dialog box is displayed.

3. Select the Save category from the left hand pane.

Page 6 RW000002 © 2016

1.4 CREATING A WORKBOOK

Creating a New Workbook

1. Click the FILE tab.

© 2016 RW000002 Page 7

3. Click Blank workbook.

Close the new workbook without saving.

1.5 OPENING A WORKBOOK

1. Click the FILE tab.

3. Select the approriate drive that Click Student Folder

4. Select Annual Sales.xlsx. Click Annual Sales.xlsx

Page 8 RW000002 © 2016