End-User Computing

Study Guide

Microsoft Excel 2016

Copyright: In terms of the Copyright Act, no 98 of 1978, no part of this manual may be reproduced or
transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by
any other information storage and retrieval system without permission in writing from Damelin.
Table of contents
WELCOME NOTE ................................................................................................................... 3
COURSE OBJECTIVES .......................................................................................................... 4
LEARNING OUTCOMES ......................................................................................................... 4
PRESCRIBED AND RECOMMENDED BOOKS ...................................................................... 4
MANDATORY COURSE REQUIREMENTS ............................................................................ 4
CHAPTER 1: INTRODUCTION TO EXCEL .......................................................................... 5
CHAPTER 2: WORKSHEET WITH FORMULAS AND FUNCTIONS ..................................... 20
CHAPTER 3: WORKING WITH LARGE WORKSHEETS, CHARTING, AND WHAT-IF
ANALYSIS ............................................................................................................................. 29
CHAPTER 4: FORMATTING THE SPREADSHEET .......................................................... 39
CHAPTER 5: COMPUTER VIRUSES ................................................................................ 44

Page 2 of 49
Module: End User Computer: MS EXCEL
WELCOME NOTE

Dear Student,

Damelin would like to welcome you to Microsoft Excel. We hope that you find your time of studies to be an
informative and exciting experience.

As an institution, we aim to provide you with the highest quality private education. This course is developed
to prepare you for a future career, equipping you with the necessary competencies required in your chosen
career field.

We would like to encourage you to interact with other students and staff as you can build lasting friendships
and future contacts. We also have a variety of student activities throughout the year that you can participate
in as a balance between work and social life is essential for all students.

Wishing all the best with your studies!

Damelin Management and Staff

Page 3 of 49
Module: End User Computer: MS EXCEL
COURSE OBJECTIVES

The objective of this module is to introduce students to Microsoft Excel. This spreadsheet package will equip
the student to work well with all the basic functionality of a popular spreadsheet software package, with
knowledge on how to work with formulas.

LEARNING OUTCOMES

On completion of this course the students should be able to:

• Use MS Excel effectively

• Create spreadsheets for various activities

PRESCRIBED AND RECOMMENDED BOOKS

Prescribed textbook:

Prescribed
Module Title Author Edition Publisher ISBN
Textbook

End User
MS Office 2016
Computing Shelly Cashman 2016 Cengage 9781305870017
Introductory

Recommended reading:
Guide to Operating Systems – enhanced edition. 2007. Palmer and Walters. ISBN 1418837199.
Hands on Network Fundamentals. 2006. Palmer. M. ISBN 1418835544.

MANDATORY COURSE REQUIREMENTS

Students must submit all assignments and meet assessment requirements in order to pass this
subject.
Class attendance is mandatory; at least 80% of all lectures per programme have to be attended.

Page 4 of 49
Module: End User Computer: MS EXCEL
MODULE OUTLINE

CHAPTER 1: INTRODUCTION TO EXCEL

LE AR N I NG OU TCOM ES

At the end of this module, you must be able to:

• Understand the importance of a • Apply cell styles
spreadsheet and how to use it • Format cells in a work sheet
• Load MS Excel • Create a 3D pie chart
• Describe the excel worksheet • Change a worksheet name and sheet tab
• Enter text and numbers color
• Use the Sum button to sum a range of • Change documents properties
cells • Preview and print a worksheet
• Enter simple function • Use the Auto Calculate area to display
• Copy the contents of a cell to a range of statistics
cells using the fill handle • Correct errors on a worksheet

TOPICS
• INTRODUCTION
• ADVANTAGES OF AN ELECTRONIC SPREADSHEET
• LOADING MS EXCEL

INTRODUCTION TO MS EXCEL

Almost every organization collects vast amounts of data. Often, data is consolidated into a summary so that
people in the organisation better understand the meaning of data. An Excel worksheet allows data to be
summarized and charted easily. A chart conveys a visual representation of data. In this module, you will
create a worksheet that includes a chart. The data in the worksheet and chart comprise a personal budget
that contains monthly estimates for each income and expense category.

▪ It is an electronic spreadsheet package that can be used to organize numeric and textual data
▪ The spreadsheet is also called a worksheet and is made up of columns and rows
▪ Excel also offers an efficient database that can sort, search and retrieve information
▪ Typical applications include budgets and financial planning such as basic bookkeeping and
accounting, keeping control of stocks in both business and personal solutions
▪ Excel can increase the efficiency with which you conduct business and track your own affairs

ADVANTAGES OF AN ELECTRONIC SPREADSHEET

▪ It is used to create a professional looking document (worksheet) by enhancing the design and
appearance
▪ EXCEL offers options to perform calculations, create graphs, edit and format data
▪ Data entered can be erased, edited, moved, or copied from one cell to another

Page 5 of 49
Module: End User Computer: MS EXCEL
 ▪ More than one spreadsheet can be opened simultaneously and data can be shared between them

LOADING MS EXCEL
▪ Make sure that the computer, monitor and any other necessary devices are switched on
▪ Click START from the taskbar that is usually found at the bottom of your screen
▪ Using the mouse move up the Start Menu and click on PROGRAMS
▪ Click MICROSOFT EXCEL

ROADMAP
• Enter text in a blank worksheet
• Calculate sums and use formulas in the worksheet
• Format text in the worksheet
• Insert a pie chart into the worksheet
• Assign a name to the worksheet tab
• Preview and print the worksheet

Selecting a Cell
• Make the cell active
• Use the mouse
• Use the arrow keys
• Cell is active when a heavy border surrounds the cell
Entering Text
• To Enter the Worksheet Titles
• Run Excel and create a blank workbook in the Excel window
• Click the A1 to make the cell A1 the active cell
• Type desired text
• Click the ENTER button to complete the entry and enter the worksheet title
• Click cell A2 to select it
• Click the ENTER button to complete the entry and enter the worksheet subtitle

To Enter Column Titles

Click cell A3 and enter a column title
Press the RIGHT ARROW key to enter a column title and make the cell to the right the active cell
Repeat the previous steps until all column titles are entered. Click the Enter box after entering the last
column title

Page 6 of 49
Module: End User Computer: MS EXCEL
To Enter Row Titles
• Click cell A4 and enter a row title
• Press the DOWN ARROW key to enter a row title and make the cell below the current cell the active
cell
• Repeat the previous steps until all row titles are entered
ENTERING NUMBERS
• In Excel, you can enter numbers in Excel to represent amounts
• If a cell entry contains any other keyboard character, Excel interprets it as text and treats it
accordingly
• To Enter Numbers
• Click cell B4 to select it
• Type desired number and then press the RIGHT ARROW key to enter the data in the selected cell
and make the cell to the right the active cell
• Continue until all numbers are entered

CALCULATING A SUM
• To Sum a Column of Numbers
• Click the first empty cell below the column of numbers to sum
• Click the Sum button on the HOME tab to display a formula in the formula bar and in the active cell
• Click the Enter box in the formula bar to enter a sum in the active cell
• Repeat above steps to enter the SUM function in other locations

Page 7 of 49
Module: End User Computer: MS EXCEL
USING THE FILL HANDLE TO COPY A CELL TO ADJACENT CELLS

To Copy a Cell to Adjacent Cells in a Row

With the cell containing the contents to fill across the row active, point to the fill handle to activate it
Drag the fill handle to select the destination area to display a shaded border around the source
area and the destination area
Release the mouse button to copy the SUM function from the active cell to the destination area
and calculate the sums
Repeat the above steps to copy the SUM function to other ranges

To Calculate Multiple Totals at the Same Time

Highlight a range at the end of rows or columns of numbers to total
Click the Sum button on the HOME tab to calculate and display the sums of the corresponding rows
Repeat the above steps to calculate and display the sums of the corresponding rows

Page 8 of 49
Module: End User Computer: MS EXCEL
Using the Fill Handle to Copy a Cell to Adjacent Cells

To Enter a Formula Using the Keyboard

• Select the cell that will contain the formula
• Type the formula in the cell to display it in the formula bar and in the current cell and to display
colored borders around the cells referenced in the formula
• Click the cell to the right to complete the formula and to display the result in the worksheet.
Formatting the Worksheet
Unformatted Formatted

Page 9 of 49
Module: End User Computer: MS EXCEL
To Change a Cell Style
• Click the desired cell and then click the Cell Styles button on the HOME tab to display the Cell
Styles gallery
• Point to the Title cell style in the Titles and Headings area of the Cell Styles gallery to see a live
preview of the cell style in the active cell
• Click the Title cell style to apply the cell style to the active cell

Formatting the Worksheet

• To Format Numbers in the Worksheet
• Select the range of cells containing numbers to format
• Click the desired format on the HOME tab to apply the format to the cells in the selected range

CHART TYPES

• Excel has various charts types to choose from

• Each one serves a particular function

These types include:

• Column
• Bar
• Pie
• Area
• Bubble
• Pyramid

Chart Elements

1. Chart Title
2. Chart Area
3. Legend
4. Plot Area
5. Series

Adding a Pie Chart to the Worksheet

To Add a 3-D Pie Chart
Select the range for the 3-D pie chart
Click the ‘Insert Pie or Doughnut Chart’ button on the INSERT tab to display the Insert Pie or Doughnut
Chart gallery
Click the Insert Pie or Doughnut Chart gallery to insert the chart
Click the chart title to select it
Type a chart title and then press the ENTER key to change the title

Page 10 of 49
Module: End User Computer: MS EXCEL
Deselect the chart title
To Apply a Style to a Chart
• Click the Chart Styles button to display the Chart Styles gallery
• Click a style in the Chart Styles gallery to change the chart style to the desired style

Page 11 of 49
Module: End User Computer: MS EXCEL
Changing the Sheet Tab Names

To Move a Chart to a New Sheet

Click the Move Chart button on the CHART TOOLS DESIGN tab
Click New sheet to select it and then type a title for the worksheet that will contain the chart
Click the OK button to move the chart to a new chart sheet with a new sheet tab name
Changing the Sheet Tab Names
To Change the Sheet Tab Name
Double-click the sheet tab you wish to change in the lower-left corner of the window
Type a new name as the worksheet tab name
Right-click the sheet tab in the lower-left corner of the window to display a shortcut menu
Point to Tab Color in the Tab Color gallery
Click the desired color in the Theme Colors area to change the color of the tab

Printing a Worksheet

To Preview and Print a Worksheet in Landscape Orientation

In Backstage view, click the Print tab to display the Print gallery
Verify that the printer listed on the Printer Status button will print a hard copy of the workbook. If
necessary, click the Printer Status button to display a list of available printer options and then click the
desired printer to change the currently selected printer
Click the Portrait Orientation button in the Settings area and then select Landscape Orientation to change
the orientation of the page to landscape.
Click the No Scaling button and then select ‘Fit Sheet on One Page’ to print the entire worksheet on one
page
Click the Print button in the Print gallery to print the worksheet in landscape orientation on the currently
selected printer
When the printer stops, retrieve the hard copy
AutoCalculate
Using the AutoCalculate Area to Determine a Maximum
Select the range of which you wish to determine a maximum, and then right-click the status bar to display
the Customize Status Bar shortcut menu
Click Maximum on the shortcut menu to display the Maximum value in the range in the AutoCalculate area
of the status bar

Page 12 of 49
Module: End User Computer: MS EXCEL
Correcting Errors
Correcting Errors after Entering Data into a Cell
If the entry is short, select the cell, retype the entry correctly
If the entry is long, use the EDIT mode using in-cell editing
To Clear the Entire Worksheet
Click the Select All button on the worksheet
Click the Clear button and then click Clear All

Page 13 of 49
Module: End User Computer: MS EXCEL
EXAMINING THE EXCEL SCREEN

THE MS EXCEL SCREEN:

• TITLE BAR
Displays the name of the program
(Microsoft Excel)
It also displays the workbook that is being used

• MENU BAR
This bar appears below the title bar and displays the different menu commands such as Home, Insert,
Page Layout

• THE STANDARD TOOLBAR

This bar is made up of numerous icons. Each icon represents a particular task and when clicked on, will
activate that function.

• THE FORMATTING TOOLBAR

This bar is made up of numerous icons. Each icon represents a particular task and when clicked on, will
activate that function.

• FORMULA BAR
This bar consists of the reference area. It displays the active cell as well as the information that is
entered into the cell.

Page 14 of 49
Module: End User Computer: MS EXCEL
• STATUS BAR
This bar is found at the bottom of the EXCEL screen
It displays the different “modes” or “states” the program is currently in e.g. “Ready” or “Enter Mode”
It also gives an explanation of the icons on the other toolbar and displays the keyboard status, indicating
if the CAPS LOCK or NUM LOCK keys are “on” or “off”
As you continue using the program the “modes” will change

• WORK AREA (WORK PLACE)

This is the blank document that is divided into columns and rows

THE SPREADSHEET

• The spreadsheet (worksheet) is made up of rows and columns

• The rows appear on the left hand-side and are numerically starting from 1, 2, 3…
• The columns are labeled according to the letters of the alphabets starting with a, b, c…
• A CELL ADDRESS is made up of a column letter and a row number e.g.

A!
Aaaaaa

Column Letter Row Number

• The CELL POINTER highlights the currents or active cell. This is indicated by a dark border around
the cell
• A RANGE is a group of cells that have been selected together

EXAMPLE

A1..A6 OR A1..C7

• A LABEL is textual data that can start with a letter of a alphabet or a special symbol such as (^ ! ‘),
labels are always left-aligned (appear on the left side of the column)

EXAMPLE

TEST1, ‘TEST2, ^TEST3

MOVING AROUND THE SPREADSHEET

Page 15 of 49
Module: End User Computer: MS EXCEL
• A. USING THE KEYBOARD
1)
Moves the cell pointer one row/column to the right

2)
Moves the cell pointer one row/column to the left

3)

Moves the cell pointer one row down

4)
Moves cell pointer one row up

5) Ctrl + Home
Moves the cell pointer to the first row in the spreadsheet i.e. A1
6) Ctrl + End
Moves the cell pointer to the last entry on the sheet

7) Home
Moves cell pointer to the beginning of the row

8) Page Up
Moves the cell pointer up one screen

9) Page Down
Moves cell pointer down one screen

10) F5 (Go To Key)

Moves the cell pointer directly to a particular cell in the spreadsheet

EXAMPLE

To go to cell D9
Press the F5 key (A dialogue box appears)
Type D9 and click OK

HIGHLIGHTING CELLS
Using the Mouse
• Position the mouse pointer in the center of the cell
• The shape of the mouse pointer changes
• Click and drag mouse pointer over cells
Using the Keyboard
• Use the arrow keys to position the cell pointer
• Press Shift + Arrow Keys to highlight the cells
Page 16 of 49
Module: End User Computer: MS EXCEL
CHAPTER 2: FORMULAS, FUNCTIONS, AND FORMATTING

LE AR N I NG OU TCOM ES

At the end of this module, you must be able to:

• Use Flash Fill

• Enter formulas using the keyboard
• Enter formulas using Point mode
• Apply the MAX, MIN, and AVERAGE functions
• Verify a formula using Range Finder
• Apply a theme to a workbook
• Apply a date format to a cell or range
• Add conditional formatting to cells
• Change column width and row height
• Check the spelling on a worksheet
• Change margins and headers in Page Layout view
• Preview and print versions and sections of a worksheet
The first step in creating an effective worksheet is to make sure you understand what is required. The person
or persons requesting the worksheet may supply their requirements in a requirements document, or you can
create one. A requirements document includes a needs statement, a source of data, summary of
calculations, and any other special requirements for the worksheet, such as charting and web support .

What decisions will you need to make when creating workbooks and charts.

1) Determine the workbook structure.

a) Determine the data you need for your workbook

b) Sketch a layout of your data and chart.

2) Create the worksheet

a)Enter titles, subtitles, and headings.

b)Enter data, functions and formulas

3) Format the worksheet

a) Format the titles, subtitles, and headings using styles.

b) Format the totals
c) Format the numbers
d) Format the text.
e) Adjust column widths.

4) Create the chart

a) Determine the type of chart to use

b) Determine the chart title and data
c) Format the chart.

CONSTANTS
• Are data that you type into a cell
• The data does not change unless you click in the cell and retype the data

Page 17 of 49
Module: End User Computer: MS EXCEL
FORMULAS
• Are a sequence of values, cell references, function or operators that produce a new value from an
existing value
• A formula begins with an equal to (=) sign

EXAMPLE

=SUM (B1...B5)

• Mathematical operators are:

+ (plus sign) – addition
- (Minus sign) - subtraction
* (asterisk) – multiplication
/ (slash) - division

USING FORMULAS
• + Addition
• - Subtraction
• * Multiplication
• / Division
• ^ Exponentiation (raised to the power)
• = Logical comparison
• Logical comparison (>)
• Logical comparison (<)
• Select the cell where you want to place the formula’s result
• Type your formula
• Press enter

NOTE: 1) When working with formulas always use the cell address of the values
2) There should be no spaces when typing formula
3) =SUM - is used to total/add values in a cell
=MAX - is used to calculate highest value in a cell
=MIN - is used to calculate the lowest value in a cell
=AVERAGE - is used to find the average of values a cell

CENTRE HEADINGS
A: TO CENTER MAIN HEADING/TITLES
• Highlight cells
• Click MERGE AND CENTER button on the formatting toolbar

B: TO CENTER COLUMN HEADING

• Click in the cell with column headings
• Click on the CENTER button on the formatting toolbar
OPENING A SPREADSHEET
• Open MS EXCEL on the screen
Page 18 of 49
Module: End User Computer: MS EXCEL
• Click File from the Menu bar
• Click NEW
• Click BLANK WORKBOOK
• Click OK

Page 19 of 49
Module: End User Computer: MS EXCEL
CHAPTER 2: WORKSHEET WITH FORMULAS AND FUNCTIONS

Objectives
• Use Flash Fill
• Enter formulas using the keyboard
• Enter formulas using Point mode
• Apply the MAX, MIN, and AVERAGE functions
• Verify a formula using Range Finder
• Apply a theme to a workbook
• Apply a date format to a cell or range
• Add conditional formatting to cells
• Change column width and row height
• Check the spelling on a worksheet
• Change margins and headers in Page Layout view
• Preview and print versions and sections of a worksheet

Roadmap

• Enter formulas in the worksheet

• Enter functions in the worksheet
• Verify formulas in the worksheet
• Format the worksheet
• Check spelling
• Print the worksheet

Entering the Titles and Numbers into the Worksheet

• Run Excel and create a blank workbook

• Select cell A1 and type the desired text, then press the DOWN ARROW key to enter the worksheet
title
• Select cell A2 and type the desired then press the DOWN ARROW key to enter the worksheet
subtitle
• To Enter the Column Titles
• Select cell A3 and type the desired text, then press the RIGHT ARROW key to enter the column
heading
• Continue until all the columns you desire have headings

Flash Fill
• To Use Flash Fill
• Click a cell
• Type desired text and then press the DOWN ARROW to select the next cell
• Type desired text again following the same pattern (for example an email address)
• Click Data on the ribbon to select the Data tab
• Click Flash Fill to enter similarly formatted text

Page 20 of 49
Module: End User Computer: MS EXCEL
Flash Fill
• To Enter the Row Titles
• Select a cell in the A column
• Type desired text and then press the DOWN ARROW key to enter a row header.
• Continue until all Rows have a header
• To Change the Sheet Tab Name and Color
• Double-click the Sheet1 tab and enter the desired text as the sheet tab name and then press the
ENTER key
• Right-click the sheet tab to display the shortcut menu
• Point to Tab Color on the shortcut menu to display the Tab Color gallery. Click desired color
• Save the workbook

Entering Formulas
To Enter a Formula Using the Keyboard
With the cell to contain the formula selected, type the formula in the cell to display the formula in the formula
bar and in the current cell and to display colored borders around the cells referenced in the formula
Press the right arrow key to complete the arithmetic operation indicated by the formula, to display the result
in the worksheet, and to select the cell to the right

Page 21 of 49
Module: End User Computer: MS EXCEL
OPTION BUTTONS

Name Menu Function

Auto Fill Options Provides options for how to fill cells following a fill operation, such as dragging the fill handle

AutoCorrect Options Undoes an automatic correction, stops future automatic corrections of this type, or causes Excel
to display the AutoCorrect Options dialog box

Insert Options Lists formatting options following an insertion of cells, rows, or columns

Paste Options Specifies how moved or pasted items should appear (for example, with original formatting,
without formatting, or with different formatting)

Trace Error Lists error-checking options following the assignment of an invalid formula to a cell

USING THE AVERAGE, MAX, AND MIN FUNCTIONS

To Determine the Highest Number in a Range of Numbers Using the Insert Function Dialog box
• Select the cell to contain the maximum number
• Click the Insert Function box in the formula bar to display the Insert Function dialog box
• If necessary, scroll to and then click MAX in the Select a function list
• Click the OK button to display the Function Arguments dialog box and type the cell range in the
Number1 box to enter the first argument of the function
• Click the OK button to display the highest value in the chosen range in the selected cell

Page 22 of 49
Module: End User Computer: MS EXCEL
To Determine the Lowest Number in a Range of Numbers Using the Sum Menu

Select cell that is to contain the minimum value and then click the Sum arrow in the HOME tab
Click Min to display the MIN function in the
formula bar and in the active cell
Drag through the range of values of which you want to determine the lowest number
Click the Enter box to determine the lowest value in the range and display the result in the formula
bar and in the selected cell

To Determine the Average of a Range of Numbers Using the Keyboard

Select the cell that will contain the average

Type =av in the cell to display the Formula AutoComplete list Press the DOWN ARROW key to highlight the
required formula
Double-click AVERAGE in the Formula AutoComplete list to select the function
Select the range to be averaged to insert the range as the argument to the function
Click the Enter box to compute the average of the numbers in the selected range and display the result in the
selected cell

Page 23 of 49
Module: End User Computer: MS EXCEL
VERIFYING FORMULAS USING RANGE FINDER

To Verify a Formula Using Range Finder

• Double-click a cell to activate Range Finder
• Press the ESC key to quit Range Finder and then click anywhere in the worksheet to
deselect the current cell

Page 24 of 49
Module: End User Computer: MS EXCEL
FORMATTING THE WORKSHEET

To Change the Workbook Theme

• Click the Themes button on the PAGE LAYOUT tab to display the Themes gallery
• Click the desired theme in the Themes gallery to change the workbook theme

To Change the Background Color and Apply a Box Border to the Worksheet Title and
Subtitle
• Select the range to color and then click the Fill Color arrow on the HOME tab to display the
Fill Color gallery
• Click a color to select it and change the background color of the range of cells
• Click the Borders arrow on the HOME tab to display the Borders gallery
• Click a border in the Borders gallery to select it and display a border around the selected
range

To Format Dates and Center Data in Cells

• Select the range to contain the new date format

• Click the Format Cells: Number Format Dialog Box Launcher in the HOME tab to display
the dialog box
• If necessary, click the NUMBER tab and click Date in the Category list, and then click a
date type to choose the format for the selected range
• Click the NUMBER tab and then click Date in the Category list to choose the format for the
selected range
• Click the OK button to format the dates in the current column using the selected date format
style
• Select the range to be centered and then click the Center button on the HOME tab to center
the data in the selected range

Page 25 of 49
Module: End User Computer: MS EXCEL
To Apply an Accounting Number Format and Comma Style Format Using the Ribbon

• Select the range to contain the accounting number format

• While holding down the CTRL key, select the nonadjacent ranges and cells
• Click the ‘Accounting Number Format’ button on the HOME tab to apply the accounting
number format to the selected nonadjacent ranges
• Click the Comma Style button on the HOME tab to assign the Comma style format to the
selected range

To Apply a Currency Style Format with a Floating Dollar Sign Using the Format Cells Dialog
Box

• Select the range to format and then click the Number Format Dialog Box Launcher in the HOME tab
to display the Format Cells dialog box
• If necessary, click the NUMBER tab to display the Number sheet
• Click Currency in the Category list to select the necessary number format category and then tap or
click a style in the Negative numbers list to select the desired currency format
• Click the OK button to assign the currency style format to the selected ranges

To Apply Percent Style Format and Using the Increase Decimal Button

• Select the range to format

• Click the Percent Style button in the HOME tab to display the numbers in the selected range as a
rounded whole percent
• Click the Increase Decimal button in the HOME tab two times to display the numbers in the selected
range with two decimal places

To Apply Conditional Formatting

• Select the range to which you wish to apply conditional formatting

• Click the Conditional Formatting button on the HOME tab to display the Conditional Formatting list
• Click New Rule in the Conditional Formatting list to display the New Formatting Rule dialog box
• Click the desired rule type in the Select a Rule Type area
• Select and type the desired values in the Edit the Rule Description area

Page 26 of 49
Module: End User Computer: MS EXCEL
Summary of Conditional Formatting Relational Operators

Relational Operator Formatting will be applied if…

Between cell value is between two numbers
not between cell value is not between two numbers
equal to cell value is equal to a number
not equal to cell value is not equal to a number
greater than cell value is greater than a number

less than cell value is less than a number

greater than or equal to cell value is greater than or equal to a number
less than or equal to cell value is less than or equal to a number

To Change Column Width

• Drag through column headings to select the columns

• Point to the boundary on the rightmost column heading to cause the pointer to become a split double
arrow
• Double-click the right boundary of the column to change the width of the selected columns to best fit
• To resize a column by dragging, point to the boundary of the right side of the column heading. When
the mouse pointer changes to a split double arrow, drag to the desired width, and then Lift your
finger or release the mouse button to change the column widths

Formatting the Worksheet

To Change the Row Height

• Point to the boundary below the row heading to resize
• Drag the boundary to the desired row height and then release the mouse button
• Lift your finger or release the mouse button to change the row height

CHECKING SPELLING

To Check Spelling on the Worksheet

• Click cell A1 so that the spell checker begins at the beginning of the worksheet
• Click the Spelling button on the REVIEW tab to run the spell checker and display the misspelled
words in the Spelling dialog box
• Apply the desired action to each misspelled word
• When the spell checker is finished, click the Close button

Page 27 of 49
Module: End User Computer: MS EXCEL
PRINTING THE WORKSHEET

To Change the Worksheet’s Margins, Header, and Orientation in Page Layout View

Click the Page Layout button on the status bar to view the worksheet in Page Layout view
Click the Adjust Margins button on the PAGE LAYOUT tab to display the Margins gallery
Click the desired margin style to change the worksheet margins to the selected style
Click above cell A1 in the center area of the Header area
Type the desired worksheet header, and then press the ENTER key
Click the ‘Change Page Orientation’ button on the PAGE LAYOUT tab to display the Change Page
Orientation gallery
Click the desired orientation in the Orientation gallery to change the worksheet’s orientation

To Print a Section of the Worksheet

• Select the range to print

• Click FILE on the ribbon to open Backstage view
• Click the Print tab to display the Print gallery
• Click ‘Print Active Sheets’ in the Settings area on the PRINT tab to display a list of printing options
• Click Print Selection to print the selected range
• Click the Print button in the Print gallery to print the selected range of the worksheet
• Click the Normal button on the status bar to return to Normal view

DISPLAYING AND PRINTING THE FORMULAS VERSION OF THE WORKSHEET

To Display the Formulas in the Worksheet and Fit the Printout on One Page

• Press CTRL+ACCENT MARK (`) to display the worksheet with formulas

• Click the Page Setup Dialog Box Launcher on the PAGE LAYOUT tab to display the Page Setup
dialog box
• If necessary, click the desired Orientation in the Page sheet to select it
• If necessary, click Fit to in the Scaling area to select it
• Click the Print button to print the formulas in the worksheet on one page. If necessary, in the
Backstage view, select the Print Active Sheets option in the Settings area of the Print gallery
• When Excel displays the Backstage view, click the Print button to print the worksheet
• After viewing and printing the formulas version, press CTRL+ACCENT MARK (`) to
display the values version

Page 28 of 49
Module: End User Computer: MS EXCEL
CHAPTER 3: WORKING WITH LARGE WORKSHEETS, CHARTING, AND WHAT-IF
ANALYSIS

L E AR N IN G OUT C OMES
At the end of this module, you must be able to:
• Rotate text in a cell • Create a clustered column chart on a
• Create a series of month names separate chart sheet
• Copy, paste, insert, and delete cells • Use chart filters to display a subset of
• Format numbers using format symbols data in a chart
• Enter and format the system date • Change the chart style and type
• Use absolute and mixed cell references in • Reorder worksheet tabs
a formula • Change the worksheet view
• Use the IF function to perform a logical • Freeze and unfreeze rows and columns
test • Answer what-if questions
• Create and format sparkline charts • Goal seek to answer what-if questions
• Change sparkline chart types and styles • Use the Smart Lookup insight
• Use the Format Painter button to format • Understand accessibility features
cells

Roadmap

• Enter the headings and data in the worksheet

• Enter formulas and functions in the worksheet
• Create sparkline charts in a range of cells
• Format the worksheet
• Create a column chart on a separate chart sheet
• Change views of the worksheet
• Ask what-if questions

Rotating Text and Using the Fill Handle to Create a Series

To Rotate Text in a Cell

If necessary, click the HOME tab and then select the cell that will include the text
Type the text that you want to rotate into the cell and then click the Enter box
Click the Alignment Settings Dialog Box Launcher on the HOME tab to display the Format Cells dialog box
Click the desired rotation degrees in the Orientation area to move the indicator in the Orientation area to that
point and to display a new orientation in the Degrees box
Click the OK button to rotate the text in the active cell and automatically increase the height of the current
row to best fit the rotated text

Page 29 of 49
Module: End User Computer: MS EXCEL
 Auto Fill Option Description

Copy Cells Fill destination area with contents using format of source area. Do not create
a series.

Fill Series Fill destination area with series using format of source area. This option is the
default.

Fill Formatting Only Fill destination area using format of source area. No content is copied unless
fill is series.

Fill Without Formatting Fill destination area with contents, without applying the formatting of source
area.

Fill Months Series and shows as an option only if source area contains the name of a
month

Rotating Text and Using the Fill Handle to Create a Series

To Increase Column Widths

• Move the pointer to the boundary between two column headings so that the pointer changes to a
split double arrow in preparation of adjusting the column widths
• Drag the pointer to the right until the ScreenTip displays the desired column width - Do not lift your
finger or release the mouse button
• Lift your finger or release the mouse button to change the width of the column
• Click a column heading to select the column and then drag through multiple column headings to
select the range in which to change the widths
• Move the pointer to the boundary between any two selected column headings and then drag the
pointer to the right until the ScreenTip displays the desired width
• Lift your finger or release the mouse button to change the width of the selected columns

Copying a Range of Cells to a Nonadjacent Destination Area

To Copy a Range of Cells to a Nonadjacent Destination Area

Select the cell or range of cells to copy, and then click the Copy button on the HOME tab to copy the values
and formats of the selected range to the Office Clipboard
Click the first cell in the destination area
Click the Paste button on the HOME tab to copy the values and formats of the last item placed on the Office
Clipboard to the destination area

INSERTING AND DELETING CELLS IN A WORKSHEET

To Insert a Row
Right-click the row heading below where you want to insert a row to display the shortcut menu and the Mini
toolbar
Click Insert on the shortcut menu to insert a new row in the worksheet by shifting the selected row and all
rows below it down one row

Page 30 of 49
Module: End User Computer: MS EXCEL
Inserting and Deleting Cells in a Worksheet

To Enter and Format the System Date

Select the cell in which you want to insert the date, and then click the Insert Function box in the formula bar
to display the Insert Function dialog box
Click the ‘Or select a category’ box arrow and then select Date & Time in the list to populate the ‘Select a
function’ list with date and time functions
Scroll down in the ‘Select a function list’ and then click NOW to select the function
Click the OK button to close the Insert Function dialog box
Click the OK button to display the system date and time in the selected cell

Absolute Versus Relative Addressing

Examples of Absolute, Relative, and Mixed Cell References

Cell Reference Type of Reference Meaning

$B$4 Absolute cell reference Both column and row references remain the same
when you copy this cell, because the cell
references are absolute

B4 Relative cell reference Both column and row references are relative.
When copied to another cell, both the column and
row I the cell reference are adjusted to reflect the
new location

B$4 Mixed reference This cell reference is mixed. The column reference
changes when you copy this cell to another column
because it is relative. The row reference does not
change because it is absolute

$B4 Mixed reference This cell reference is mixed. The column reference
does not change because it is absolute. The row
reference changes when you copy this cell
reference to another row because it is relative

Page 31 of 49
Module: End User Computer: MS EXCEL
To Enter a Formula Containing Absolute Cell References
With the desired cell selected, enter the formula and then press the F4 key to change the most recently
typed cell reference from a relative cell reference to an absolute cell reference

MAKING DECISIONS-THE IF FUNCTION

To Enter an IF Function
• Click the cell to contain the formula, and then click the Insert Function box in the formula bar to
display the Insert Function dialog box
• Click the ‘Or select a category’ arrow and then select Logical in the list to populate the Select a
function list with logic functions
• Click IF in the Select a function list to select the required function
• Click the OK button to display the Function Arguments dialog box
• Enter the logical test in the Logical test box to enter a logical test for the IF function
• Type the result of the IF function if the logical test is true in the Value_if_true box
• Type the result of the IF function if the logical test is false in the Value_if_false box
• Click the OK button to insert the IF function in the selected cell

To Copy Formulas with Absolute Cell References Using the Fill Handle
• Select the range to fill and then point to the fill handle in the lower-right corner of the selected cell
to display the crosshair pointer
• Drag the fill handle to the right to copy the formulas from the source area to the destination area and
display the calculated amounts

Adding and Formatting Sparkline Charts

To Add a Sparkline Chart to the Worksheet

Select the cell in which you want to insert a sparkline chart
Display the INSERT tab and then click the Line Sparking button to display the Create Sparklines dialog box
Drag through the range of cells of which you want to chart, and then lift your finger or release the mouse
button to insert the selected range in the Data Range box
Click the OK button to insert a line sparkline chart in the selected cell and display the SPARKLINE TOOLS
DESIGN tab

Page 32 of 49
Module: End User Computer: MS EXCEL
Adding and Formatting Sparkline Charts

To Change the Sparkline Style and Copy the Sparkline Chart

• Click the More button on the SPARKLINE TOOLS DESIGN tab to display the Sparkline Style gallery
• Click a desired style in the Sparkline Style gallery to apply the style to the Sparkline chart in the
selected cell
• Point to the fill handle in the cell and then drag through the desired range to copy the line Sparkline
chart

FORMATTING THE WORKSHEET

To Assign Formats to Nonadjacent Ranges

• Select the first range to format
• While holding down the CTRL key, select the nonadjacent ranges and then release the CTRL keyp
to select nonadjacent ranges
• Click the Number Dialog Box Launcher on the HOME tab to display the Format Cells dialog box
• Click the desired category and style, and then click the OK button

To Copy a Cell’s Format Using the Format Painter Button

• Select the source cell for the format to paint
• Double-click the Format Painter button on the HOME tab and then move the pointer onto the
worksheet to cause the pointer to change to a block plus sign with a paintbrush
• Click the cell(s) to receive the format to assign the format of the source cell to the destination cell(s)
• Click the Format Painter button or press the ESC key to stop the format painter

Page 33 of 49
Module: End User Computer: MS EXCEL
Adding a Clustered Column Chart to the Workbook

• To Draw a Clustered Column Chart on a Separate Chart Sheet Using the Recommended Charts
Features
• Select a range to identify the range of the categories
• Hold down the CTRL key and select a non adjacent data range
• Click the Recommended Charts button on the INSERT tab to display the Insert Chart dialog box with
the Recommended Charts tab active
• Click the recommended chart to select it and then click the OK button
• When Excel draws the chart, click the Move Chart button on the CHART TOOLS DESIGN tab to
display the Move Chart dialog box
• Click New sheet and then enter a name in the in the New sheet text box to enter a sheet tab name
for the chart sheet
• Click the OK button to move the chart to a new chart sheet with a new sheet tab name

To Insert a Chart Title

Click anywhere in the chart title placeholder to select it
Select the text in the chart title placeholder and then type a new chart title

To Add Data Labels

Click the Chart Elements button to display the CHART ELEMENTS gallery
Point to Data Labels to display an arrow and then click the arrow to display the Data Labels fly-out menu
Click a selection from the Data Labels fly-out menu so that data labels are displayed as desired
Click the Chart Elements button to close the gallery

Page 34 of 49
Module: End User Computer: MS EXCEL
To Apply Chart Filters
Click the Chart Filters button (on the chart) to display the Chart Filters gallery
In the SERIES section, click the desired check boxes to remove their check marks and then click the Apply
button to filter these series from the chart
Click the Chart Filters button to close the gallery

To Add an Axis Title to the Chart

Click anywhere in the chart area outside the chart to select the chart, and then click the Chart Elements
button to display the CHART ELEMENTS gallery
Point to Axis Titles to display an arrow and then click the arrow to display the Axis Titles fly-out menu
Click an axis on the Axis Titles fly-out menu to add an axis title, and then click the Chart Elements button to
remove the CHART ELEMENTS gallery
Select the placeholder text in the axis title and replace it with the desired text
Right-click the axis title to display a shortcut menu, and then click ‘Format Axis Title’ on the shortcut menu to
open the Format Axis Title task pane
Click the TITLE OPTIONS tab, click the ‘Size & Properties’ button, and then, if necessary, click the
ALIGNMENT arrow to expand the ALIGNMENT section
Click the Text direction arrow, and then click the desired direction from the direction list to change the
orientation of the axis title

To Change the Chart Style

Click the More button on the CHART TOOLS DESIGN tab to display the Chart Styles gallery
Click a style to apply a new style to the chart

To Modify the Chart Axis Number Format

Right-click any value on the vertical axis to display the shortcut menu, and then click Format Axis to open the
Format Axis task pane
If necessary, click the AXIS OPTIONS tab and then scroll until NUMBER is visible

Page 35 of 49
Module: End User Computer: MS EXCEL
Click the NUMBER arrow to expand the NUMBER section and then scroll to display options related to
formatting numbers
Change the number to the desired format

ORGANIZING THE WORKBOOK

To Reorder the Sheet Tabs

Drag a tab to the left or right to rearrange the sequence of the sheets

Changing the View of the Worksheet

To Shrink and Magnify the View of a Worksheet or Chart

Display the VIEW tab and then click the Zoom button to display a list of magnifications in the Zoom dialog
box
Click the desired zoom level, and then click the OK button
Click the Zoom in button on the status bar until the worksheet is displayed at the desired magnification

To Split a Window into Panes

Click the cell at the intersection of the four proposed panes to select the cell at which to split the window
Click the Split button on the VIEW tab to divide the window into four panes

Page 36 of 49
Module: End User Computer: MS EXCEL
To Freeze Worksheet Columns and Rows

• Click the cell at the intersection of the row and column at which to freeze panes
• Click the Freeze Panes button on the VIEW tab to display the Freeze Panes gallery
• Click Freeze Panes in the Freeze Panes gallery to freeze rows and columns to the left and above
the selected cell

WHAT-IF ANALYSIS
To Goal Seek
If you know the result you want a formula to produce, you can use goal seeking to determine the value of a
cell on which the formula depends
Goal Seek command on the Data tab
Goal seeking assumes you can change the value of only one cell referenced directly or indirectly to reach a
specific goal for a value in another cell

Page 37 of 49
Module: End User Computer: MS EXCEL
Insights

To Use the Smart Lookup Insight

Page 38 of 49
Module: End User Computer: MS EXCEL
CHAPTER 4: FORMATTING THE SPREADSHEET

L E AR N IN G OUT C OMES
At the end of this module, you must be able to:
• Edit the contents of a cell
• Use the Auto sum feature
• Select non-adjacent cells
• Hide columns and rows
• Clear and delete cells
• Add boarders on a spreadsheet

TOPICS
• EDITING THE CONTENTS OF A CELL
• USING THE AUTOSUM FEATURE
• SELECTING NON-ADJACENT CELLS
• HIDING COLUMNS AND ROWS
• CLEARING AND DELETING A CELL
• ADDING BORDERS

EDITING THE CONTENTS OF A CELL

THE CONTENTS OF A CELL CAN BE EDITED IF:

A. IF YOU WANT TO REPLACE AN ENTIRE ENTRY

• Click in cell
• Type the information you want
• Press enter
B. IF YOU WANT TO REPLACE CERTAIN INFORMATION IN THE CELL
• Click in the cell
• Press F2
• Change the information
• Press Enter
C. USING THE AUTOSUM FEATURE

• This feature is found on the standard toolbar

• The function of this feature is to automatically enter the sum function by first selecting a range of
cells in current column/row you wish to total
INSTRUCTIONS
1. Calculate the Total for each row using the Autosum feature

Page 39 of 49
Module: End User Computer: MS EXCEL
TO DO:
• Click in cell where you want the total to appear
• Click in the Autosum feature on the toolbar

HIDING COLUMNS AND ROWS

• The purpose of this feature is to conceal certain confidential information on your worksheet
(spreadsheet) that you may not want others to see
[Link] HIDE A COLUMN
• Highlight the entire column by clicking on the letter of the column
• Right Click with mouse
• Click Hide
B. TO HIDE A ROW

• Highlight the entire row by clicking on the number of the row

• Right Click with mouse
• Click Hide
C. UNHIDING A COLUMN

• Position the mouse pointer on the border of the column i.e where the hidden column is
• Slowly drag the mouse pointer to reveal the column
D. UNHIDING A ROW

• Position the mouse pointer on the border of the rows i.e. where the hidden row is
• Slowly drag the mouse pointer to reveal the row

CLEARING AND DELETING A CELL

TO CLEAR A CELL

• It is used to delete the contents of the cell

• Click in the cell which contains the information
• Press the Delete key on the keyboard

A. TO DELETE A CELL

• It is used to delete the entire cell along with the information in the cell as well.
• Click in the cell
• Click Delete

ADDING BORDERS

A:
• Highlight the cells you want to put a border around

• Click on the Border Button on the formatting toolbar

• Click the style of border you wantFILTERING AND SORTING
Page 40 of 49
Module: End User Computer: MS EXCEL
 You can store information on just any type in a list.
• Columns respond to fields
• Rows respond to record
• The first row in the table should field names that describe the data in each column.

APPLYING A DATABASE FUNCTIONS WITH LISTS

• To create a formula that returns based on the filtered criteria, use the Excel database worksheet
functions
• Text comparisons are not case sensitive
• Computed criteria is a logical formula that refers to cells in the first row of data in the list, it does not
refer to the header row

FILTERING A LIST WITH AUTOFILTERING

• Move the cell pointer anywhere in the list
• Choose the Data, Filter, Auto Filter command
• Click the arrow on one of these drop-down lists.
• Select an item

FILTERING A LIST WITH CUSTOM AUTOFILTERING

• Values above or below a specified value:
• Values within a range
• Values outside a range
• Two discrete values
• Approximate matches
• A criteria range must be set up.

The criteria range must conform to the following specification

• The criteria range consists of at least two rows
• The first row contains some or all of the field names form the list
• The other rows consist of filtering criteria

The entries you make in a criteria range can be either of the following
• Text or value criteria: the filtering involves comparisons to a value or text, using operators such as
equal (=), greater than (>), not equal to (<>)
• Computed criteria: the filtering involves some sort of computation

Advanced filtering provides you with the following capabilities:

• You can specify more complex filtering criteria
• You can specify computed filtering criteria
• You can extract a copy of the rows that meet the criteria to another location

PIVOT TABLES
• It is dynamic summary of data contained in a database
• It lets you create frequency distributions and cross-tabulation made up of several different data
dimensions

Page 41 of 49
Module: End User Computer: MS EXCEL
The fields in a database table can be

• Data: contains a value

• Category: describes the data

Creating a pivot table

• Move the cell pointer to any cell in the database
• Choose Insert, pivot table, and pivot chart report command
• Make sure that the options labeled Microsoft excel list or database and pivot table are selected and
click Next
• In the second dialog box ensure that the database range is specified and click next
• In the third dialog box specify the location for the pivot table
• Click the options button if desired to specify additional options and click finish

To change the number format for the data in the pivot table, you need to do
• Select any cell in the pivot table’s data table area
• Right-click and choose field settings from the short cut menu
• Click the number button
• Select the number format you need

Page 42 of 49
Module: End User Computer: MS EXCEL
INSERTING A NEW FIELD INTO A PIVOT TABLE
• Move the cell pointer anywhere the pivot table
• Drag the new field from the pivot table toolbar to the desired location in the pivot table

REMOVING A FIELD FROM A PIVOT TABLE

• Click the field button that you want to remove
• Drag it away from the pivot table
• Release the mouse button, excel updates the pivot table
• Removing the field you dragged away

INSERTING A CALCULATED FIELD INTO A PIVOT TABLE

• Move the cell pointer anywhere within the pivot table
• Right-click and choose formulas, calculated field from the shortcut menu
• Enter a name for the field and specify the formula. The formula can use other fields
and worksheets functions, but it cannot refer to cells that use names
• Click the add button
• Repeat steps 3 and 4 if you want to create additional fields
• Click ok to close the dialog box

INSERTING A CALCULATED ITEM INTO A PIVOT TABLE

• Move the pointer to a row, column or page area of the pivot table
• Right-click and choose formulas, calculated item from the shortcut menu
• Type a name for the item and specify the formula
• Repeat steps 3 and 4 if you want to create additional items
• Click ok to close the dialog box

MODIFYING A PIVOT TABLES STRUCTURE

• A pivot table displayed in a worksheet includes the field buttons

REFRESHING A PIVOT TABLE

• Any of the field buttons can be dragged to any position
• Choose data, refresh data command
• Right-click anywhere in the pivot table and select refresh data from the shortcut menu
• Click the refresh button on the pivot toolbar

Page 43 of 49
Module: End User Computer: MS EXCEL
CHAPTER 5: COMPUTER VIRUSES

LE AR N I NG OU TCOM ES

At the end of this module, you must be able to:

• Understand computer viruses and how they function
• How to check for viruses
• Understand the levels of security as well as changing the levels of security
• What security certificates are and how they function?
• About detecting viruses in Excel
• About macro signatures

TOPICS
• VIRUSES
• WHAT BUTTON SHOULD I PRESS

VIRUSES

• A macro virus is a type of computer virus that’s stored in a macro within a workbook or add-in
program
• Viruses are programs that are written by individuals, usually with the intent to destroy or corrupt data
• When you open an affected workbook or perform an action that triggers a macro virus, the virus can
become active, be transmitted to your computer, and be stored in [Link] a hidden workbook,
or some other undetectable location
• From that point on, every workbook you save can be “infected” automatically with the virus
• If other people open infected workbooks the macro virus can also be transmitted to their computers
• Microsoft Excel doesn’t scan your floppy disk, hard disk, or network drive to find and remove macro
viruses. If you want this kind of protection, you need to purchase and install antivirus software.
However, Excel can display a warning message whenever you open a workbook that contains
macros
• You can then decide whether to open the workbook with the macros enabled or whether to open the
workbook with the macros disabled so that you can only examine and edit them
• A macro virus can be harmful only if it is allowed to run, so disabling the macros allows you to open
the workbook safely

What to do in those circumstances

• If you expect the workbook to contain useful macros (for example, an order form used in your
company), you might want to
• Click Enable Macros to open
• The workbook with the macros enabled
• If you don’t expect the workbook to contain macros, or if you don’t know or are uncertain about the
reliability of its source, you might want to
• Click Disable Macros to open the workbook with macros disabled

Page 44 of 49
Module: End User Computer: MS EXCEL
For example, you might get a workbook as an attachment in an email message from an unfamiliar source or
from a network or Internet site that is not secure. In these cases, it’s a good idea to talk to the originator of
the workbook to verify that it is free of viruses.

• If you disable the macros, you won’t be able to run any macros, recalculate custom functions, run
event handlers (macros that run in response to actions such as mouse clicks), or use Active X
controls to initiate events. You can still examine and edit the macros by using the Visual Basic Editor
• When you click Save on the File menu, the macros are saved. You can then close the workbook and
open it again with macros enabled if you want to run the macros
• To prevent the possibility that macros containing viruses might contaminate your system, use this
procedure to display a warning message whenever you try to open a workbook that contains a
macro. This message appears regardless of whether the macro actually contains a virus. Make sure
that you know and trust the source of the workbook before you continue

Page 45 of 49
Module: End User Computer: MS EXCEL
CLASS EXERCISE

Ms Excel

Please save your worksheet with your Name and Surname on the desktop

1. Design a spreadsheet below the following configurations:

Text wrap the first row, apply a color of your choice, font size 14, font “Comic sans MS “and apply a
solid boarder.

Title Name Location Salary Deductions Bonus Net Salary Limit

Mr Thabo Boston 7000
Mr Simba Boston 4000
Mr James Boston 6500
Miss Vuyiswa Boston 3500
Mrs Thuli Seattle 5000
Mr Petros Seattle 4500
Mr Jubu Seattle 6000
Mr Andile Seattle 3000
Mr Alice Ottawa 5500
Mr Emilda Ottawa 6800

INSTRUCTIONS

1. Deductions are calculated as follows: For each employee in Boston 2.2% from their salary is
deducted and the rest 5% is deducted. Use an If statement.

2. The Group CEO gives only his employees in Seattle R 500.00 Bonus and the others Nil. Use an If
statement.

3. Calculate net Salary. ( salary – total deductions)

4. Add a new field after the column net salary and name it Limit. The limit is determined as follows: If
employee net salary is greater or equal to R 5,000.00 then it is “Over Budget”, if not, “Within
Budget”. Calculate the Limit.

5. Rename sheet 1 to Salaries.

6. Make sheet 2 to be before Sheet 1(Salaries).

7. Freeze row number one.

8. Format the headings to 450 and apply a back ground color of your choice.

9. Copy cell range A1:J10 and paste in sheet 3 and then rename it as “copy”.

10. Using the worksheet “salaries” Insert a column chart using the first names and net salary.

11. Insert “Salaries” as the chart title and then format the chart area using a color of your choice.
Page 46 of 49
Module: End User Computer: MS EXCEL
Please note your chart should look like the one below:

Page 47 of 49
Module: End User Computer: MS EXCEL
CONCEPTS

CONCEPT DESCRIPTION

Analyse Describe the different parts of a topic and explain how they work together or not. Give
arguments for and against each situation. A reasonable amount of insight must be
shown in terms of knowledge already gained in this regard.

Apply Show the application of acquired knowledge or given information in practice, or in

relation to what is asked. Use knowledge to find an answer to the question.

Comment Give your own opinion regarding the subject matter and illustrate it through examples.
Interpret and evaluate.
Compare Contrast facts, events or problems and indicate the similarities and differences, or
analyse the similarities and differences between statements, ideas, etc.

Contrast Point out the differences between certain objects, facts, events or characteristics.

Criticise Point out the good and bad characteristics or viewpoints and give your own opinion after
taking into account all the facts.
Define Give a clear, to the point, systematic explanation/description of concepts, to reflect the
precise meaning thereof.
Describe Give the characteristics, basic facts or results in a logical, systematic and well-
structured manner. Comments and your own interpretation are not necessary.

Discuss Give terminology and concepts in your own words with comments or your own
interpretation. Compare, contrast and debate.
Design Create and plan. Portray by means of illustrations or concrete objects. Create a model
with a specific objective in mind and indicate the planning phase.

Evaluate Make an assessment of values based on specific points of reference or criteria and give
your own opinion. Do not describe. Personal viewpoints may be given.

Explain Clarify the term, concept or topic by presenting it with your own knowledge/words. If
required you can use illustrations, descriptions, or a simple logical layout of the facts.

Illustrate Use a sketch, picture, diagram, graph or concrete item to explain a concept or solve a
problem. This can also mean to give examples in well-chosen, descriptive words.

Motivate Prove give reasons and comment.

Summarise State the key or most important aspects of a topic without detail, illustrations, critical
analyses and discussion.

Page 48 of 49
Module: End User Computer: MS EXCEL
PLAGIARISM

1. Submitting another’s published or unpublished work, in whole, in part, or in paraphrase, as one’s own
without fully and properly crediting the author with footnotes, citations or bibliographical reference.
2. Word-for-word copying of sentences or whole paragraphs from one or more sources (the work or data of
another person) or presenting of substantial extracts from books, articles, theses, other unpublished
work such as working papers, seminar and conference papers, internal reports, lecture notes or tapes,
without clearly indicating their origin.
3. Submitting as one’s own, original work, material obtained from an individual or agency without reference
to the person or agency as the source of the material.
4. Submitting as one’s own, original work, material that has been produced through unacknowledged
collaboration with others without release in writing from the collaborators.
5. Copying computer files, algorithms or computer codes without clearly indicating their origin.
6. Submitting work that has been derived, in whole or in part, from another learner’s work by a process of
mechanical transformation (e.g. changing variable names in computer programs).

For more information contact your Lecturers, Librarian(s) or Management at the main campus.

Page 49 of 49
Module: End User Computer: MS EXCEL