- Author:
- Charleen Makgoale
- Subject:
- Engineering
- Material Type:
- Lecture Notes
- Level:
- Community College / Lower Division
- Tags:

- License:
- Creative Commons Attribution
- Language:
- English

# Education Standards

# EXERCISES AND SOLUTIONS

# MAIL MERGE TUTORIAL

# RESOURCE FOR ADVANCED EXCEL

# RESOURCE FOR CHARTS

# RESOURCE FOR MAIL MERGE

# SOLUTIONS TO GIVEN EXERCISES

# WHAT IS COMPUTER LITERACY

# COMPUTER LITERACY

## Overview

THIS IS AN OVERVIEW OF NATIONAL CERTIFICATE (VOCATIONAL) LEVEL 4 COMPUTER SKILLS FOR TVET COLLEGES. THE RESOURCE PROVIDES STUDENTS WITH BASIC AND ADVANCED COMPUTER SKILLS. THE CONTENT RANGES, BUT NOT LIMITED TO ADVANCED EXCEL, MAIL MERGE, CHARTS, EXERCISES AND SOLUTIONS.

# INTRODUCTION

**TABLE OF CONTENT**

**1. TOPICS**

- MAILMERGE

- EXCEL SPREADSHEET

- CHART AND GRAPH INSERTION

**2. ASSESSEMENTS**

**3. LINKS**

**4. VIDEOS**

CLICK THE BELOW URL FOR MORE UNDERSTANDING OF WHAT COMPUTER LITERACY IS.

# CALCULATIONS IN ADVANCED EXCEL

**LEARNING OBJECTIVES**

After completing the instructions in this booklet, you will be able to:

**1. PRACTICE CALCULATIONS**

**2. SIMPLE FORMULAS**

**3. AUTOSUM**

- Formula & Functions Basics
- Checking for formula
- Building a formula to add
- Autosum to add row or column totals
- Building a formula to subtract
- Building a formula to multiply or divide
- Use insert function (formula builder) to make formulae easier
- Autofill to copy formula to other cells
- Relative cell references
- Absolute references for common variable.

**1. PRACTICE CALCULATIONS**

**Instead of using a calculator, use Microsoft Excel to do the math!**

You can enter simple formulas to add, divide, multiply, and subtract two or more numeric values. Or use the AutoSum feature to quickly total a series of values without entering them manually in a formula. After you create a formula, you can copy it into adjacent cells — no need to create the same formula over and over again.

**Subtract in Excel**

**Multiply in Excel**

**Divide in Excel**

**2. Simple formulas**

All formula entries begin with an equal sign (**=**). For simple formulas, simply type the equal sign followed by the numeric values that you want to calculate and the math operators that you want to use — the plus sign (**+**) to add, the minus sign (**-**) to subtract, the asterisk (*****) to multiply, and the forward slash (**/**) to divide. Then, press ENTER, and Excel instantly calculates and displays the result of the formula.

For example, when you type **=12.99+16.99** in cell C5 and press ENTER, Excel calculates the result and displays 29.98 in that cell.

The formula that you enter in a cell remains visible in the formula bar, and you can see it whenever that cell is selected.

**Important:** Although there is a **SUM** function, there is no **SUBTRACT** function. Instead, use the minus (-) operator in a formula; for example, =8-3+2-4+12. Or, you can use a minus sign to convert a number to its negative value in the SUM function; for example, the formula =SUM(12,5,-3,8,-4) uses the SUM function to add 12, 5, subtract 3, add 8, and subtract 4, in that order.

**3. AutoSum**

The easiest way to add a SUM formula to your worksheet is to use AutoSum. Select an empty cell directly above or below the range that you want to sum, and on the **Home** or **Formula** tabs of the ribbon, click **AutoSum** > **Sum**. AutoSum will automatically sense the range to be summed and build the formula for you. This also works horizontally if you select a cell to the left or right of the range that you need to sum.

**Note:** AutoSum does not work on non-contiguous ranges.

**AutoSum vertically**

In the figure above, the AutoSum feature is seen to automatically detect cells B2:B5 as the range to sum. All you need to do is press ENTER to confirm it. If you need to add/exclude more cells, you can hold the Shift Key + the arrow key of your choice until your selection matches what you want. Then press Enter to complete the task.

**Intellisense function guide:** the SUM(number1,[number2], …) floating tag beneath the function is its Intellisense guide. If you click the SUM or function name, it will change o a blue hyperlink to the Help topic for that function. If you click the individual function elements, their representative pieces in the formula will be highlighted. In this case, only B2:B5 would be highlighted, since there is only one number reference in this formula. The Intellisense tag will appear for any function.

**AutoSum horizontally**

After you create a formula, you can copy it to other cells — no need to rewrite the same formula. You can either copy the formula, or use the fill handle to copy the formula to adjacent cells.

For example, when you copy the formula in cell B6 to C6, the formula in that cell automatically changes to update to cell references in column C.

**IMPORTANT TIP! **When you copy the formula, ensure that the cell references are correct. Cell references may change if they have relative references.

# HOW TO CREATE A COLUMN, LINE AND PIE CHART.

**LEARNING OBJECTIVES**

1 Identify the different types of charts

2 Create a column, line and pie chart

3 Insert chart titles and legends

4 Edit the appearance of a chart by changing the fill colour

5 Spell check; preview and print the document.

Charts are previewed an Charts are created and edited within a spreadsheet Programme Range: Identify different types of charts, create column, line and pie chart, insert chart titles and legends, edit appearanced printed.

**NOTE: CLICK ON THE POWERPOINT ATTACHMENT FOR A FULL LESSON.**

# USING MAIL MERGE FEATURES

**Learning Objectives**

After completing the instructions in this booklet, you will be able to:

**MAIL MERGE**

- Format word document

- MS Access to print a data source

- Create main document

- Rename table

- Create data source

- Merge main document and data source

- Use the Mail Merge Wizard.

# EXERCISES

*EXERCISE 1 - CREATE SPREADSHEET*

You are required to create a spreadsheet showing how many liters of water that WERE used for house chores in the years 2010 and 2015

1. Use MS Excel and create the spreadsheet below. Do not key in the row and column headings (borders)

*EXERCISE 2 - CALCULATIONS*

2.1 Insert the new columns and row as indicated.

2.2 Provide formulae where the letters of the alphabet appear to do the following calculations:

A DECREASE AND INCREASE = Number of liters of water used in 2010 **minus **Number of liters of water used in 2015 for __cleaning only.__

B TOTAL NO OF LITERS = Calculate the total amount of liters used in 2010

C AVERAGE NO OF LITERS = Calculate the average amount of liters used in 2010

D HIGHEST NO OF LITERS = Calculate the maximum amount of liters used in 2010

E LOWEST NO OF LITERS = Calculate the minimum amount of liters used in 2010

F NUMBER OF HOUSEHOLD CHORES =Calculate count If

*EXERCISE 3 - CHARTS AND GRAPHS*

3.1 Insert a column graph to compare the amount of liters of water used in 2010 and 2015 for house chores.

3.2 Insert the following as a title at the top: WATER USAGE FOR HOUSE CHORES

3.3 Display legend on the top left.

3.4 Insert a suitable title for X-axis and Y-axis.

**NOTE: AFTER COMPLETING THE EXERCISE, OPEN THE ATTACHED DOCUMENT FOR SOLUTIONS. **