LSC S2 :Spreadsheet I

Key words
Formula Function Workbook
Chart
The content of this chapter and the activities will enable you to:

1. Use spreadsheet functions to manipulate data.
2. Generate charts to represent information.
3. Print using a spreadsheet software.

Introduction
In senior one, you learnt about data and information. Spreadsheets are one of the tools used in presenting and manipulating data and information. Spreadsheets are computer applications used to store, analyse, organise, manipulate and present data in rows and columns of a grid. Spreadsheets
make it easy to work with different types of data such as numbers, text charts and dates by using commands, formulas and functions. This is the reason why some people use spreadsheets for data analysis.

Do you remember the information processing cycle you learned about in senior one? Spreadsheets make computations simpler. Some jobs that were once done by accountants are now managed by a computer program because it is easy to use, efficient and fast.
In this chapter, you will learn how to usea spreadsheet software to create, save, edit and print spreadsheets. Whereas word processors can be used to store data in tables, they
NOTE
have limitations when dealing with data manipulations.

Activity 1.1: Discussing the meaning of Spreadsheets Do this activity in a group.

1. Discuss the manipulations that have been done on the data to come up with the information in Figure 1.1?
3. What interpretation do you make from the information generated in
   Figure 1.1.
   Share your responses with other groups.

Uses of Spreadsheets
Features of spreadsheets such as worksheets, functions, charts and database are used in several ways as explained in Table 1.1.

Once data has been manipulated, it guides people in making decisions, plans, predictions and other actions of interest. Some of the following questions can be answered by using information generated by spreadsheets.

Activity 1.2: Uses of spreadsheet information

Do this activity in a pair.

1. Using spreadsheet information provided in Table 1.2, discuss questions
   a-c. Give reasons for each of your answers.
2. Give any other two areas where spreadsheet information can be useful
   Share your answers with the class.

Starting MS Excel 2016
Before starting MS Excel program, ensure that it is installed on your computer. You can then start it through the steps in Activity 1.4. Note that there are various ways of doing it. You can explore them later to expand your knowledge.

Activity 1.4: Starting MS Excel 2016
Do this activity in a group.

1. Enter the word “Excel” in the search box at the bottom left of your screen next to the Start button to load the program. See Figure 1.2

2 Click on the Excel 2016 App. The Excel application will open a new blank workbook as shown in Figure 1.3

3. Save the file and observe what happens to the file name in the Title bar.
4. Repeat step 1 and open a workbook with any other template and take note of its features.
   Share your observations with other groups.
   NOTE The Excel file saved is called a workbook.
   The Excel Ribbon
   As learned in senior one, the ribbon is the display you see at the top of the window. It allows you to access most of the commands of the program. The Excel Ribbon is composed of three parts: Tabs, Groups, and Commands.

Ribbon Tabs, Groups and Commands
MS Excel is used to manipulate data using a lot of commands. To make it easier for users to find specific commands, the commands are organized onto eight main tabs each with a number of groups. For example, the Home tab has Clipboard, Font, Alignment and Number groupings. See Figure 1.4.

Not all tabs will be explored in this chapter. Only File, Home, Insert,
NOTE Formulos and Dato will be considered. Others witl be dealt with in senior three under Spreadsheet II.

Activity 1.5: Exploring the Ribbon
Do this activity in a pair.
1 Open MS Excel program.
2 Click on File in the upper left corner of the screen.
3 Click on New and select Blank workbook. Shortcut for opening a
new file is Ctrl+N
4 Type the following data in the worksheet.

5 (Hint: After typing in a cell, press Enter or use arrow keys to go to the
next cell.)
5 Click on each of the tabs; Home, Insert and View and identify the
different groupings in each.
6 Format the table in 3 above to appear as below.

(Hint: Click in the cell before clicking on the action).
Demonstrate your work to the rest of the class.
Workbooks and Worksheets/Spreadsheets
A workbook is a collection of worksheets. Excel enables you to create and edit one or more worksheets that you store in workbooks. When Excel is opened, its workbook, by default, has one worksheet. However, you can add more spreadsheets. Each worksheet contains columns and rows.

NOTE Work sheet/Spreadsheet is a grid of columns (indicated by
lettets) and rows (indicated by numbers).

Spreadsheets
Excel application allows you to insert (create) as many sheets as possible. To add a new worksheet, simply click on the + sign shown in Figure 1.5.

Once a sheet is created, it can be renamed, moved, copied or deleted. The worksheet tab color can also be changed.
Activity 1.6: Working with spreadsheets Do this activity in a pair.

1. Giving examples, explain why different sheets are important in a workbook.
2. Open a new workbook and name it “Our Work”.
3. Insert two new extra sheets.
4. Rename sheet 1, sheet 2 and sheet 3 with any three districts of Uganda. (Hint: Right-click on the Sheet tab and select Rename. Once you click on Rename, the name of the sheet is highlighted and you can simply type in a new name. Double-clicking on the tab will also enable you to type in a new name).
5. Shift the third sheet to become the first. (Hint: Click once on the sheet tab and hold down the left mouse button and drag to the desired location).
6. Change the colour of the second sheet tab to red.
7. Hint: Right click on the tab and follow options)
8. Delete the last sheet.
9. Zoom out the worksheet to make it more readable.
10. Click in any cell and press Ctrl+A to select the whole sheet. Then go to
11. the Font group and insert alt borders.

Demonstrate your work to the rest of the class.

Cursor Styles in Excel
There are four common cursor styles used in Excel as shown in Table 1.3.

Navigating through a Spreadsheet
You can move around the spreadsheet by clicking on various cells, or by using the arrow keys on the keyboard and noticing the changes in the Name box.

Activity 1.7: Navigating through a spreadsheet
Do this activity in a pair.

1. Open any spreadsheet file.
2. Click on a cell and then hold down the down arrow key on the keyboard
   for a few seconds. Notice how the Name Box value changes.

3. Repeat 2 but with the right arrow key. Notice how the alphabet changes from single letters (A, B, C, .. .Z) to multiple letter combinations (AA, AB, AC).
4. Hold down the Ctrl key and tap the right arrow key to go to the last column on the right.

NOTE any time you desire to go to any cell, (say Bl) simply clicking the Name Box and type in cell Bl. Tap the Enter key and you will go to cell Bll

Selecting a range of cells
A range is a selected group of adjacent cells. If you select more than one cell at a time, you can perform actions on the group of them at once, such as applying formatting or clearing the contents.
A range is referenced by the first and last cells in it.

For example, the range of cells A2, A3, B2, and B3 would be referred to as A2:B3.

Activity 1.8: Selecting a range of cells
Do this activity in a pair.

1. Type the following in a new excel sheet starting from A1 to C3.

2.Highlight cells A2 to C3. Select the first cell, and then hold down the
Shift key while you press the arrow key to expand the selection area.
3 Select cells A2, B3 and C2. (Hint: To select a non-rectangular or
non-continuous range, select the first portion of the range, and then
hold down the Ctrl key while you select additional cells with the mouse.
4 Select the entire column B. (Hint: Click the column header where the
letter is).
5 Select an entire row by clicking the row header where the number is.
6 Save the file for use in the next activity.

you can also edit information in a cell by double-clicking in a cell or
NOTE by clicking in the formula bar.

Inserting Rows and Columns
While working in a spreadsheet, you might need to add new data in between the existing rows or columns. Excel allows you to insert new columns or rows in between the existing ones as shown in Figure 1.6. This saves you the trouble of creating, moving and re-arranging data in a worksheet.

A row is the horizontal arrangement of cells from the left to the right of the worksheet. A column is the vertical arrangement Of cells from top to the bottom of the worksheet.

Activity 1.9: Inserting rows and columns
Do this activity in a pair.
Open the file saved in Activity 1.8.
2 Insert a row between Kichwamba and Gulu. (Hint: Right click on row
number 3 and select insert).

3. Enter the following data, Nakawa Town, Kampala District, 12000 nets.
4. Insert a column between District and No.of nets and name it Region. Fill
   in the regions accordingly.
   Save this file for use in the next activity.
   Demonstrate your work to other pairs.

Formatting Data in a Spreadsheet
Once information has been entered into a cell, you can change or enhance the
way it is displayed. Text can be formatted in the same way as it is done in MS
Word. Most of the formatting choices can be found under the Home tab.
Enscue that the cell you want to format is highlighted.

Activity 1.10: Formatting data in a spreadsheet
Do this activity in a pair.
1 Open the file saved in Activity 1.9.
2, Select row 1 and fill it with colour green (Hint: Go to Home tab in the
Font group).

3. Select No. of nets and format them with a comma to indicate
   thousands. (Hint: Go to the number group under the Home tab).
4. Change the font and size to Elephant 14 for all the text.
   Demonstrate your work to another pair.

Using AutoFill
The AutoFill feature allows you to fill cells with data that follows a pattern or is based on data in other cells. AutoFill automatically fills in the numbers, dates, text, days and so on based on the pattern of the first two entries. For example, you can have Excel automatically fill in serial numbers such as 1, 2,
or days of the week such as Tuesday, Wednesday, Thursday, without 3 typing all that information.

Activity 1.11: Using AutoFill function
Do this activity in a pair.

1. Use spreadsheet AutoFill function to automatically number products
   as Product 1, Product 2,
   Steps
   i). Open an existing file and insert a worksheet.
   ii). Type Product 1 in cell 82.
   iii). Highlight cell B2.
   iv). Find the small square (Fill Handle) in the lower right corner of Cell
   B2 as shown below.

v). Hold the Fill Handle and drag with your mouse to fill cell B3 to B8.
2, Try out again starting with Tuesday or 7:00 or Topic 1
Share your observations with other pairs.

Aligning Text, Wrapping Text and Merging Cells
Excel offers the option not only to align text left, center, and right but also top, middle, bottom and even at an angle. When you enter text that is too long to fit in a cell, it overlaps to the next cell. If you do not want it to overlap to the next cell you can wrap it• Some alignment examples are shown in Figure

Activity 1.12: Aligning text in cells
Do this activity in a group.
I. Study Figure 1.7 and identify:
i). The cell containing text for wrapping.
ii). Text which is bottom aligned.
iii). Text which is oriented at an angle.
2 Type the text shown in Figure 1.7 and format it the way it is using the
buttons in the Alignment group.
3 Wrap the text which does not fit in its cell.
Insert a new sheet and type the information below starting from A1.

5. Merge cells A1, Bl and Cl in 4 above for the table heading.
   (Hint: Highlight the cells, click Merge and Center in the Alignment group.)
6. Align the column headings in Name, Gender and Weight at an angle of
   300.
   Save your file and share your work with other groups.

Resizing Rows and Columns
As you type the numbers, some of them may be too wide for the default cell width. To expand the cell, slowly move the mouse arrow to the right edge of cell B (between the B and the C), The cursor will turn into an arrow pointing right and left with a small vertical line in the middle as shown in Figure 1.8.

Hold down the left mouse button and drag the line to the right/left.
Activity 1.13: Resizing rows and columns
Do this activity in a pair.

1. Open a spreed sheet.
2. Type 123456789 and press Enter.
3. Reduce the cell size until you see ##.
   Demonstrate your work to another pair.
   Formulas and Functions
   Formulas and functions are the main features of Excel. Almost all the computations done in Excel make use of formulas and/or functions. In Excel, a formula is an expression or equation that operates on values in a range of cells or a cell. For example, =A1+A2+A3 finds the sum of the range of values from cell A1 to cell A3. Functions are predefined formulas and are already available in Excel. They
   eliminate laborious manual entry of formulas while giving familiar names. Table 1.4.shows how you can use the formula and the function to perform the same
   task.

Activity 1.14: Difference between formulas and functions
Do this activity in a pair.
1. Open an existing workbook and add a new worksheet.
2.Type any three numbers of your choice in cells A1, A2 and A3.
3.Click where you want to put the total and type the formula =A1+A2+A3
and then press Enter.
4. Click in another empty cell and enter equal signs. Observe what happens
to the Name Box.
5 .Click on the Name Box drop down to select your preferred function, such
as SUM. A screen similar to the one below will appear. Enter a range of
cells required, for example, (A1:A3).

6. Once all is set, Click 0K.
7. In your words, explain the difference between a formula and a function
   Share your work with another pair.
   NOTE Alternatively you can manually enter the function by typing
   =Sum(A1:A3) and pressing Enter

Formulas can be categorized into two: Simple and Complex formulas.
Simple formular
Simple formulas have one mathematical operator. Some examples and their
corresponding operators are outlined in Table 1.5.

Activity 1.15: Practising simple formulas
With a neighbour, practise using the formulas in Table 1.5.
l.Type =11+25 in any cell and press Enter. Take note of the answer.
2 Type -A1-7 in A5 and press Enter. Enter 20 in A1, press Enter and observe
how A5 changes.
3 From your observation in (2) above, can you tell the difference between
the formulas: -A1-7 and 20-7?
4 Type =B2-B3 and explain how this formula works.
5 Type a formula to multiply 150 and 2.
6 Type =4AC2 in C3 and press Enter.
7 Using the formula in (6) above, find 4A1, 4A2, 4A3 and 4114. Record your
Share your work with other friends.

Applying Simple Formulas
Once you are familiar with the working of simple formulas, you can use them to handle different tasks such as calculating profit and loss in business, calculating a patient’s rise and fall in temperature and so on.
Activity 1.16: Applying simple formulas

Do this activity in a group.
in this activity, you will practise using simple formulas to calculate profit,
set new prices and make a budget in business.

1. A trader dealing in the sale of ICT tools provided the information in the
   worksheet below

i Enter the above data into a worksheet.
ii Use an appropriate formula to calculate the profit made on each
item and total profit made on all the items.
(Hint: Use the AutoFill function to compute other values once the
first one is done).
As a result of increase in taxes, the purchasing price of each item
is expected to increase by 2,000. Use an appropriate formula to
calculate the new purchasing price.
Save your work as Sales.
share our work with other groups.

2. The following data was a shopping budget for Janet for term one in
   2020.

i). Enterthe above data into a spreadsheet and save your file as “SHOPPING”.
ii). Using an appropriate formula, determine the total budget for shopping.
iii). Using an appropriate formula, determine how much Janet paid if she was given a 9% discount.

3. Other than in business, describe how formulas can be used in any other two sectors.
   Share your work with the rest of the class.
   Complex Formulas
   Complex formulas have more than one mathematical operator. A number of simple formulas when combined make complex formulas. For example, =164+6, =B7-(A2+A3)15. Think about a situation where a learner has done four activities which have to be added and converted to 20 marks, what
   mathematical operators can you use? This requires a complex formula to compute.

Use brackets in advanced formulas to ensure that the different parts
NOTE
of the formula are calculated in the correct order. For example,
=4+2(6-3) is not the same as =4+26-3
When there is more than one operation in a formula, the order of operations tells Excel which operation to calculate first. When using MS Excel to calculate complex formulas, the order of operations matters just like in regular Math when BODMAS or MDAS are used. For example, Excel simplifies the complex formula =4+2*(6-3)
in the following order of operations:

i). Operations enclosed in Parentheses (6-3) is simplified first.
ii). Multiplication (2×3=6).
iii). Addition (4+6=10)
A mnemonic that can help you remember the order is PEM-
NOTE
DAS, or Please Enter My Details As Stated.
Activity 1.17: Practicing complex formulas
I Add a worksheet in an existing file and name it Complex.
2 Type the formula in B2 and press Enter. Take note of the answer and the contents in the formula bar.
3 Demonstrate manually to a neighbor how the answer comes about.
4 Type the formula in B3 and press Enter. The answer is different from the one in 2) above. Explain why?

Activity 1.18: Applying complex formulas
Do this activity in a pair.

1. In an institution, students do two course works a term (CWI and CW2) and a final exam. Each coursework is marked out of 20. The total coursework and final exam contribute 30 and 70 marks to the total mark respectively. A score sheet of nine students is shown below.

i). Enter the student’s data above in a spreadsheet.
ii). Insert a column before the one containing Student’s name and auto number starting from 1.
iii). Use an appropriate formula to compute the total coursework mark for each student. Round this mark to 1 decimal place.
iv). Compute the total mark for each student using an appropriate formula. Share your work with other pairs.

2. The following data is an extract from a trader’s business records for the month of March.

i). Enter the above data into a spreadsheet and save your file as
TRADER. You will use it in other activities.
ii). Using appropriate formulas, determine total profit got from the sale
of all the items and the percentage profit for each item.
iii). Compute how much the trader paid as tax for each item at a rate of
7% of the profit.
iv). Determine the net profit using formula or function.

Common Excel Errors and their Corrections
While using Microsoft Excel, you may encounter common errors. It is important
to learn how to correct them, otherwise you may risk unintended results being displayed on your spreadsheet. Some of the most common errors are listed in Table 1.6.

Activity 1.19: Fixing errors in Excel

1. In groups, practise creating and fixing errors outlined in Table 1.6.

Using Functions
As already discussed, functions are predefined formulas and are already available in Excel. In order to work correctly, a function must be written in a specific way, which is called the syntax. The basic syntax for a function is the equals sign (z), the function name (SUM, for example), and one or more arguments. Arguments contain the information you want to calculate, The function in Figure 1.9 adds the values of the cell range A1:A20. other common Excel functions include Average and Max.

Instead of typing the argument manually, you can simply click in a cell and its cell address will be auto captured in the argument.

Activity1:20: Using functions Do this activity in a pair.
A school produced an end of term one mark sheet for senior one as follows.

1. Using a spreadsheet program, enter the mark sheet data as it is.
2. Insert an appropriate heading for the table with font size 24 and
   colour Blue.
3. Using appropriate functions, determine for each learner the:
   i).
   Total mark
   Average mark
   Highest mark
   Minimum mark
4. Using appropriate functions, determine for each subject the:
   i).
   Total mark
   Average mark
   Highest mark
   Minimum mark
5. Save your workbook as Functions for use in the next activities.
   Activity: 1.21: Using functions and formulas in business
   Do this activity in a pair.
   The data below shows sales and expenditure (in thousands of Uganda Shillings) Ja
   Distribution Company Limited for the years 2016-2019.

Note the following changes in sales and expenditure:
Sales from produce increased by 20% annually.
Stationary sales remained constant yearly.
Sales from electronics decreased by 5% every year.
Transport increased by 10% in 2017 and remained constant in
2018 and 2019.
Wages and accommodation decreased by 5% yearly.

1. Enter the above information into a spreadsheet.
2. Compute the;
   i). Sales and expenditures for each of the items each year.
   ii). Total sales and expenditure for each year.
   Save your work as Financial Report.
   Present your work to the class.
   AutoSum Function
   If you need to add numbers in a column or a row, AutoSum does it very fast.
   Select the cells with the numbers you want to total and click AutoSum on the
   Home tab in the Editing group.

Activity 1.22: Using AutoSum Do this activity in a pair.

1. Open the file that you saved in the Activity 1.21.
2. Highlight any row or column for which you need the total and click AutoSum
3. Click in the total obtained in 2 above and note the formula used in the
4. Formula bar.
   Share your findings with another pair.
   Using the COUNT Function
   The COUNT function is used to get the number of entries in a given range Of
   cells. For example, in Activity 1.21, you can count the names of learners in
   the spreadsheet. The syntax example is =COUNT(A2:B8). It works on cells with
   number entries.

Activity 1.23: Using the COUNT function
Do this activity in a pair.

1. Open the file that you saved in Activity 1.20.
2. Use the COUNT function to;
   i). determine the number of learners in the spreedsheet.
   ii). the number of Maths entries.

Sorting Data
If your worksheet contains a lot of data, it can be difficult to find information easily. The good news is that Sorting can help a lot with data management. Sort function are found in the Sort grouping in Data tab ribbon.
Sorting allows you to re-order your data so that it is easy to locate. The most common type of sorting is alphabetical ordering.

Activity 1.24 : Sorting data
Do this activity in a pair.

1. Open the learners’ mark sheet saved as Financial Report in Activity
   1.21.
2. Highlight all the data (without headers) and click on the Sort tab as
   shown below.

3. On the dialog box that appears (see the figure on the next page), click on the drop down arrow marked A to select the field you want to sort.

4. Select NAME in this case and observe how information in drop dowd
B changes. Try to check and uncheck the part marked C and note
the changes then click 0k.
5 Sort any other two fields of your choice.
6 Sort the Total field from largest to smallest (descending order). Save
your file.
7 Practise sorting using the file saved as Trader in Activity 1.18

NOTE If the field has numbers, the order is either Smallest to Largest or vice versa.
Using Charts in Excel
It can be difficultto interpret Excel worksheetthat conta in a lot of data. However, Charts allow the user to illustrate the worksheet data graphically. This makes it easy to see and interpret data as shown in Figure 1.10.

Excel has different types of charts as shown in Table 1.7 allowing you to choose the one that best fits your data. In order to use charts effectively, you need to understand how different charts are used.

Activity 1.25: Using a pie-chart
Do this activity in a group.

1. With examples, suggest when a pie chart should be used.
2. Use the following data to generate a pie chart: A school has 220 girls
   and 176 boys.
   Steps
   i).
   Highlight the data and click on Pie-chart in the Charts group
   of the Insert tab.
   Select a 2-D pie chart to get to the interface below.

Click on the part marked D to add chart elements such as
Chart title, Data labels and Legend.
Put a chart title of your choice.
Format Data labels to include Category Name and Percentage. (Hint: Right click in the part marked E and select Format Data label from the menu that appears)

Activity 1.26: Using a Column chart
Do this activity in a group.
I. With examples, suggest when a column Chart can be used.

2. Use the file you saved as Functions and generate an appropriate
   column chart showing the performance of learners in Kiswahili. Ensure that the chart has the following; title, labeled axes, grid line and legend.
3. Briefly explain the information in the chart generated in 2 above.
   Share your work with another group.

Activity 1.27: Using a line chart Do this activity in a group.

1. With examples, suggest when a line chart can be used.
2. A population of two schools was recorded over a period of eight
   years as follows.

3.Enter the above data in a worksheet
Insert line charts for:
School A
School B
Schools A and B combined.
4 Use the combined chart to identify the school which had a higher population over the eight-year period.
5 If you were an investigator using the charts, which period(s) of population growth would you investigate for school A and why?
6 Copy the data and the charts and paste them in an MS Word file. our work and share it with other groups.

Headers and Footers
A header is the text that appears in the top margin of every page in a worksheet while a footer is the text that appears in the bottom margin of every page in a worksheet. The information in a header or a footer may include page numbers, date, name of the file or even a picture or a logo. The use of Headers and Footers
in MS Excel is similar to that in MS word.
The headers and Footers function is under the Insert tab in the Text Group as shown in Figure 1.11.

Headers and footers are displayed only in Page Layout View, Print view and on
printed pages.
Activity.1.28: Adding headers and footers
Do this activity in a pair.

1. Click in the worksheet where you want to add a Header ora Footer
2. Go to the Insert tab in the Text group, click Header and Footer. Excel
   displays the worksheet with the Design tab which has Header and Footer elements as shown below.

3. Add or edita header or footer by adding a text or image in the left , center or right header or footer text box at the top or bottom of the worksheet page• Explore other Headers and Footers that have been edited, click in any cell to view the added header or footer. The worksheet displays in Page layout view•
4. Change the worksheet view to Normal and observe what happens to the header.
5. Go to page layout view, click in the header and edit it.

Printing Worksheets and Workbooks
When you have finished creating the worksheets in your workbook, you may
want to print. The printing in MS Excel is similar to printing in other applications
like MS Word.
The gridlines in an excel sheet are not visible when work is printed.
NOTE To make them visible, highlight the data or sheet and put Borders.

Activity 1.29: Printing Worksheets and Workbooks
Do this activity in a pair.

1. To print your workbooks and worksheets, click the File tab to get to the Backstage area as shown below.

2. Set the printing options such as pages, num ero copes an page orientation. Select a printer and click on Print.

Quick Check
Puzzle: Spreadsheet
Copy and complete the puzzle below to quick check your understanding.