Module 4 – Spreadsheets

Table of Contents
1. Section 1 - Introduction ........................................................................................................................ 2
1.1. Module Learning Outcomes.......................................................................................................... 2
1.2. Tools required: .............................................................................................................................. 2
2. Section 2: Interface with Spreadsheets ................................................................................................ 3
2.1. Session 1A – Introduction to spreadsheets .................................................................................. 3
2.2. Session 1B – Working with cells and basic formulas .................................................................. 10
3. Section 3 – Working with Worksheets ................................................................................................ 19
3.1. Session 2A – Managing worksheets and user interface ............................................................. 19
3.2. Session 2B: Managing advanced spreadsheet user environment .............................................. 30
4. Section 4 – Working with formulas and creating charts ..................................................................... 37
4.1. Session 3A Basic formulas and Cell Referencing ......................................................................... 37
4.2. Session 3B – Creating and Formatting Charts ............................................................................. 45
5. Section 5 – Advanced Formatting ....................................................................................................... 58
5.1. Session 4A – Data Manipulation with Formatted Data .............................................................. 58
5.2. Session 4B: Formatting for Enhanced User Interface ................................................................. 66
6. Section 6 – Advanced formatting........................................................................................................ 72
6.1. Session 5A: Rules-based Data Manipulation and Formatting..................................................... 72
6.2. Session 5B: Working with Variables and Data Types .................................................................. 83
7. Section 7 – Creating and printing data reports ................................................................................... 90
7.1. Session 6A: Report Generation ................................................................................................... 90
7.2. Session 6B: Managing Headers/Footers and Page setups .......................................................... 97

1|Page
1. Section 1 - Introduction
The goal of this module is to give skills to leaners on how to interact with spreadsheets
to solve various needs of an organization. Spreadsheets are digital workspaces that
allow users to manipulate data and generate summaries in graphical format.

1.1.Module Learning Outcomes

At the end of the module, learners will be able to

1. Enter data in worksheets and navigating worksheets

2. Use formulas and functions to get data summaries using formula libraries

3. Enhance worksheets by formatting cells and worksheets

4. Create theme based chart to give a visual feel of data

5. Edit worksheets in preparation for printing with page setup

6. Get data from external sources

7. Use filters and grouping to organise and summarise data

1.2.Tools required:
 Microsoft Excel 2010 or higher,
 OpenOffice Calc,
 Google Sheets

2|Page
2. Section 2: Interface with Spreadsheets

2.1.Session 1A – Introduction to spreadsheets

Learning outcomes

By the end of this session, you will be able to:

i. Launch the various spreadsheet tools available in the market

(MS Excel, Apache Calc and google sheets)
ii. Describe the various menus and tools available for use
iii. Work with the cell, rows and columns (Add and delete
rows)
iv. Enter and align data in a cell

Introduction:

A spreadsheet is an application that allows calculations

and generation of advanced graphs and charts from the
data to be carried out efficiently. Excel is one of the most
common spreadsheet applications.

In Spreadsheet applications, each document is

known as a Workbook and contains a worksheet.

3|Page
When a new document is opened it has the default name Workbook and contains
Sheet1 as the first worksheet. As many worksheets as necessary can be added to a
Workbook. Only one sheet can be active at a time.

The worksheet is made of a table-like sheet

with columns, identified with a letter
reference (A,B,C,D…) and rows identified
with a number reference (1,2,3,4….) The
intersection of a row and a column is known
as a cell. Each cell is uniquely named relative
to its column and row number. This unique
name is known as a cell “address” for
instance the cell address C5 refers to the data
in column C and row 5 of the sheet.

Other cells can be used to refer to the data or formulas in a cell. This is known as
referencing.

Characteristic of spreadsheets

1) They allow users to see data in a graphical format using bar graphs, scatter
diagrams, trend lines etc.
2) They do automatic calculations of data once the formulas are put in.

4|Page
3) They perform complex data analysis using
built in formulas in statistics, engineering,
finances etc

Examples of Spreadsheets

1) Microsoft Excel by Microsoft

2) OpenOffice Cal by Apache

3) Google Sheets by Google

4) Numbers for Mac by Apple

5|Page
Platform and Price Comparisons

Microsoft Office Apple Google OpenOffice or

iWork Drive LibreOffice
Operating Windows and Mac Mac Online only Windows, Mac,
systems and Linux
Apps iOS, Android, Windows iOS Android N/A
Phone and iOS
Cost Starts at $100 for Free Free Free
subscription service
(Office 365)

Spreadsheet Interface (OpenOffice Calc)

1
2
3

1) Title bar- contains the name of the current workbook

2) Menu bar- contains additional functions available on a workbook. Click each
menu to see the options.
3) Formatting toolbar-icons of formatting shortcuts

6|Page
4) Formula Bar-Has two sections. The left shows the cell address of the cell where
the cursor is located. The right contains the actual contents of the cell. Cell
formulas are also visible in this bar.

5
6

5) Status bar- displays the current view and a description of what the application is
currently doing. Results of formulas are also displayed on the status bar.
6) Tab bar-Shows the sheets on the workbook. Select a tab to make a sheet active.

Spreadsheet User Inteface

Class Exercise

1. Open the Microsoft Excel application installed on your computer and select
“New workbook”. Use the search icon on the status bar to search for the
application

2. Using the mouse, click in cell A2 to place the cursor and type Student Name, use
the right arrow key on the keyboard to move to cell B2 and type Course.

7|Page
3. Using the arrow keys or the mouse to place the cursor in the respective cells,
populate cells A3-A9 with student names, B3-B9 with courses.

Student Name Course

Robert Mugabe PSC 101 - Political Science
Bill Gates MCS 101 - Microsoft
Mark Zukerberg FBK 101 - Facebook
Jerry Yang YHO 101 - Yahoo
David Filo YHO 101 - Yahoo
Kevin Systrom ING 101 - Instagram
Mike Krieger ING 101 - Instagram

4. To insert an additional row, select row 7 (the row where a new row will be
inserted above), which is where we want to insert a new row. Select the same
number of rows as you want to insert. To insert four rows, select four rows. In our
example, we have selected only one row, therefore only one row will be added.

Method I - right-click on Row 7 and in the popup,

select insert. This will automatically shift the rest of
the rows down, to make room for the new row.

Method II- On the Home tab, Cells grouping, select

Insert Sheet Rows. This will automatically shift the
rest of the rows down, to make room for the new
row.

Added rows appear above the selected cell or row.

8|Page
5. Populate the new row with the student name “Moses Musa” taking a course in
“EXD 101 - Exodus”.
6. To insert an additional column, select column A (the column where a new
column will be inserted before)
a. Method I- right-click on column A and in the popup, select insert. This
will automatically shift the rest of the columns to the right, to make from
for the new column.
b. Method II - On the Home tab, Cells grouping, select Insert Sheet Rows.
This will automatically shift the rest of the rows down, to make room for
the new row.
7. Populate the new column with the heading Student Number and populate the
rest of the cells with student numbers as shown.

Student Student Name Course

Number
Student
Number Student Name Course
DAC/000/2017 Robert Mugabe PSC 101 - Political Science
DAC/001/2017 Bill Gates MCS 101 - Microsoft
DAC/002/2017 Mark Zukerberg FBK 101 - Facebook
DAC/003/2017 Jerry Yang YHO 101 - Yahoo
DAC/004/2017 Moses Musa EXD 101 - Exodus
DAC/005/2017 David Filo YHO 101 - Yahoo
DAC/006/2017 Kevin Systrom ING 101 - Instagram

8. Adjust the Column width by taking the mouse pointer between the two columns
and double click the mouse button.

Added columns appear to the left of the selected cell or above selected column.

When new rows and columns are added, cell references are automatically adjusted.

9|Page
9. To delete rows or columns, select the row or column to delete, on the Edit menu,
click delete. Rows or columns shift up or to the left to fill the space left by the
deleted for or column.

Delete the row that has called Robert Mugabe

Note: Spreadsheet applications also adjust formulas if a cell is deleted. The #REF
error is displayed if a formula references a deleted cell.

10. Select cells A2,B2 and C2,set the font to Bold and alignment to Center.
11. Save the workbook as StudentMarks1.xlsx, in your class folder.

Class Exercise 1

Class exercise:

1. What is a cell and a cell reference?

2. What happens to existing cells when a new row or column is
added?
3. What is the default document name for Excel?
4. How would you insert multiple rows or columns to a worksheet?

2.2.Session 1B – Working with cells and basic formulas

Learning outcomes

10 | P a g e
By the end of this session, you will be able to:

i. Merge and Unmerge cells

ii. Hide and Unhide rows and columns
iii. Copy data using AutoFill and selecting from drop-down list
iv. Create and edit hyperlinks

The Merge capability is a common tool used to join selected cells into one or separate
joined cells into single cells. The cells to be merged must be adjacent to each other.
When merging, only the contents of the leftmost column are retained.

Class Exercise

1. Open the file StudentMarks1.xlsx

2. Enter the title “STUDENT MARKS” into cell A1.
3. To merge cells: Select the range of column cells A1-E1.

On the Home tab, under Alignment, select downward

arrow and choose Merge and Center. The cells will be
merged into one and the title centered. When a cell is
merged, it takes the cell reference of the leftmost cell. Our
merged cell therefore has the new reference A1.

To unmerge the cells, select the merged cell, on the Home tab under
Alignment, highlight Merge and click unmerge.

11 | P a g e
4. Select the merged cell A1, change the font to Times New Roman, 14pt , Bold and
select the fill color Blue, Accent 1, Lighter 60%

5. Select the cells A2-E2 and set the font to Bold.

6. Save the workbook.
7. In cell D2 and E2, enter the column headings “Project Marks” and “Exam Marks”
respectively. Format the two headings to have the same properties as student
number and student name added earlier. Populate the table as follows.

8. To hide and unhide rows and columns, select column D and E

a. Method I- right-click and from the popup, select Hide/Unhide.

12 | P a g e
b. Method II- on the Home  Cells grouping,  Format  Visibility 
Hide & Unhide  Hide Columns

Note:

When a column/row is hidden, the column/row

reference is no longer visible. To unhide the hidden
row/column, select the adjacent row or column to the
top/bottom and left/right.

9. Select column C and F, right-click and pick unhide from the drop down menu, or
Home  Cells grouping,  Format  Visibility  Hide & Unhide  Unhide
Columns.

13 | P a g e
10. Add a column before column D and label it “Intake Year”. Fill in 2016 in cell D3.

To auto fill data, select cell D3, and get the + sign on the cell. drag
the fill handle down to D9. This should fill in 2016 in all the cells.

11. Save the workbook.

Note:

Using the auto fill feature users can enter a data series faster. A data series refers to a
set of repeated data. You can continue a series of numbers, text, text/number
combinations or formulas using auto fill. Simply add the data in two cells and use the
auto fill handle to populate the rest of the cells. The fill handle can only be dragged
down a single column or across a single row. Auto Fill options include

 Copy Cells- copy the entire contents of the cell including formatting
 Fill Formatting only- copy only the cell formatting
 Fill without formatting- copy the contents of the cell but not the formatting.

It is possible to create a hyperlink to another document, email address or webpage in

Excel.

Class Activity.

1. Open the file StudentMarks1.xlsx

2. Insert a column before Student Number
3. Label the Column Serial Number
4. Enter Number “1” and number “2” in cells A3 and A4 respectively.
5. Select both cells, take the cursor to the bottom right corner of cellA4. When the
pointer changes to cross hair (+), double click.

14 | P a g e
6. Alternatively, select cells A3 and A4, get the cross hair, hold down the left mouse
button and scroll downwards. You achieve the same autofill. When the mouse
button is pressed down, a status information is given to the user a the bottom of
the page.

7. Delete the series filled in cells A3 to A9.

8. Type number 1 in cell A3. Copy down the number. At the end, a pop-up option
window shows. Select, Fill Series.

15 | P a g e
Create a hyperlink to a Web page

A hyperlink is a link from a hypertext file or

document to another location or file, typically
activated by clicking on a highlighted word or
image on the screen. The link can open a
document in the hardisk of the computer, or a network location or a website.

Class Activity

1. Select the cell C11 (the cell where you want the hypelink to be placed.
2. To on the Insert tab, click group, select Hyperlink. Insert  Link  Hyperlink
3. The dialogue box appears where you can type in the address (location which you
would like to open) and the Text to display. Text to display is what makes sense
to the user. A file path or a URL may not make much sense to the user. Enter the
following details and select Ok.

16 | P a g e
A link is inserted in the appropriate cell, C11, showing the display text.

4. To activate the link, drag the mouse pointer to get the mouse pointer to change
and get a tool tp.

To edit a hyperlink

5. Hold down the mouse button for at least two seconds on the cell that contains
the hyperlink, and then release the button.
6. Alternative, right click on the cell where the link is and select “Edit Hyperlink”.
This opens the window that enables you to make changes to the hyperlink.

7. To remove a hyperlink, hold down Ctrl key, click the hyperlink, and press delete
on the keyboard. This removes the hyperlink. Or, right click and select “Remove
Hyperlink”.

17 | P a g e
Class Exercise 2

Class exercise:

1. How would you merge/unmerge cells?

2. What is the importance of the auto fill feature in Excel?
3. What is a data series?
4. How are hyperlinks useful in Excel?
5. How would you add a hyperlink to an excel worksheet?

18 | P a g e
3. Section 3 – Working with Worksheets

3.1.Session 2A – Managing worksheets and user interface

Learning outcomes

By the end of this session, you will be able to:

i. Name worksheets
ii. Insert and delete worksheets
iii. Copying worksheets
iv. Use different views of worksheets and modify pages with
page break view
v. Adding Headers and Footers in a Worksheet

The default name for Excel worksheets is Sheet1, Sheet2,….Sheetn. When working with
multiple sheets of data, it is necessary to give meaningful names for easier access and
reference. It is important to remember that:

Microsoft Excel

Apache OpenOfficre Calc

19 | P a g e
Google spreadsheets

 A worksheet name must be between 1-31 characters long.

 Two worksheets in the same workbook cannot have the same name.
 Special characters are not allowed in a worksheet name.

Class activity – Re-naming Worksheets

1. Open the application Apache Open office Calc from the start menu
2. Click on sheet 1 to rename. In the Dialogue window, type an name with a special
character (<>?/!@#$%^&*). When you select OK, you will get an error message

3. Open the application Microsoft Excel.

4. To rename Sheet1, double click on the name and overwrite with the following text
“This is an extremely long name for a sheet”. What happens?

20 | P a g e
Class activity – Adding Worksheets

1. Open the file Stdentmarks1.xlsx

2. Click on the new worksheet tab next to the last tab or press (Shift + F11)

Note:
 Pressing F11 on its own adds a special worksheet for charts alone.
 The worksheet is added to the right of the selected worksheet i.e. N + 1
 Chart worksheets are added to the left of the selected sheet i.e. N – 1

3. Open Apache OpenOffice Calc. Save the workbook as University Data.

4. Rename the three default worksheets as follows: Computer Science | Commerce
| Communication.

5. In the blank area after Communication tab, click once. A dialogue window
appears.

21 | P a g e
6. Select the radio button “From File”. Browse to the location of Studentmarks1.xlsx
and insert the worksheet names “This is an extremely long name”

Class activity – Changing the colour of Worksheets

1. Aside from changing the name, the tab color of a worksheet can also be changed.
Right click on the tab. From the short cut menu, select Tab Color. Select a color of
your choice.

22 | P a g e
2. Open OpenOffice Calc, open the file University Data.Ods and colour the
departments

3. OpenOffice Calc allows longer worksheet names.

Hiding Worksheets.

In Excel, one can hide any worksheet in a workbook to remove it from view. You can
also hide the workbook window of a workbook to remove it from your workspace. The
data in hidden worksheets and workbook windows is not visible, but it can still be
referenced from other worksheets and workbooks. You can display hidden worksheets
or workbook windows as needed. By default, all workbook windows of workbooks that
you open are displayed on the taskbar, but you can hide or display them on the taskbar
as needed.

Class Exercise

1. Open the file StudentMarks1.xlsx.

2. Right click on the tab “This is an extremely long name” and select Hide. The
worksheet selected is not visible. You can hide multiple worksheets in one go by
selecting them, then hiding.
3. To unhide a worksheet, right click on any worksheet name and select Unhide. A
dialogue box appears from where you select the worksheets that you want to
Unhide.

23 | P a g e
4. You can hide a workbook using the Hide tool in the Window grouping of View
tab. View  Hide. This clears the workbook from view and the excel application
is left grey. Close the application and save changes.
5. Open the file StudentMarks1.xlsx in the class folder. Excel loads without the file.

6. Select View  Unhide and specify the workbook that is hidden.

By default, Microsoft Office Excel provides three worksheets

in a workbook, but you can insert additional worksheets (and
other types of sheets, such as a chart sheet, macro sheet, or
dialog sheet) or delete them as needed.

Class Activity – Deleting Worksheets

Note: This Action Cannot Be Undone

1. Open the file StudentMarks1.xlsx.

24 | P a g e
2. Rename the Worksheet “This is an extremely long name” to Marks
3. Create a copy of the worksheet Marks.
 Right click on the worksheet Marks and select “Move or Copy”
 Tick the field “Create a Copy” and select OK

4. An exact copy of the worksheet is created with addition of the copy number

Rename the worksheet Marks (2) to “Copy for Deleting”

5. Right click on the worksheet named “Copy for Deleting”. Select Delete. A
warning is issued for confirmation. Select Delete.

Alternatively, in the Home tab, Cells grouping, select Delete Sheets.

25 | P a g e
Using Page Layout

1. In the View tab, select “Page Break Preview”. Select OK in the dialogue box that
pops on Page Break Preview. DO NOT tick.

In the View tab, Workbook Views grouping, select Normal.

Select Page Layout tab. Dotted line will be added to show the
2. To use page layout, click View, click Page Layout. This will give you the view of
how the document will look when printed.
As seen in this step, the student marks spill over to several pages. It is important
to adjust the columns and rows when printing, so as to ensure that the document
is complete.

Changing page orientation:

3. To change the orientation of the document from portrait (default) to landscape,

select Page Layout tab, select Orientation, and change from Portrait to
Landscape.

26 | P a g e
The marks now fit in one page and the orintout will be OK.
4. Select File  Print. You will see a preview of how the page will look on paper
after printing.
5. A user can also set a print area. Select cells A2 to D9, normal indicated as A2:D9

6. From the Page Layout tab, select Print Area  Set Print Area.

7. A print area is set and shown with dotted line. Select File  Print. The preview is
the set print area. This can be cleared and reset.

27 | P a g e
Adding Headers and Footers

1. To add Header/Footer and other page settings, select Insert tab, and select
Header/Footer tool. The page is altered to allow Header to be typed in.

2. Type Daystar University in the Header section.

3. Select the Header/Footer toggle tool to swith between Header and Footer. Move
to Footer.

4. In the Footer section, add 3 tools. As follows. Note you need to type “Page” and
“of”. The other fields are available in the tools. File Name | Page Number |
Number of pages.

28 | P a g e
Class Exercise 3

1. List the rules to follow when renaming a worksheet.

2. How would you change the tab color and name of a
worksheet?
3. How would you use different page layout views to modify a
worksheet?
4. What is the procedure of adding Header/Footer to a
worksheet?

How can repeating rows/columns be added to a worksheet?

29 | P a g e
3.2.Session 2B: Managing advanced spreadsheet user environment
Learning outcomes

By the end of this session, you will be able to:

i. Split Windows vertically and horizontally

ii. View multiple worksheets simultaneously
iii. Freezing panes
iv. Splitting cells

When working with large volumes of data, it is possible to view and compare this data
either by opening it in a new window or by splitting a worksheet into different panes. It
is possible to open multiple windows for a single workbook at the same time.

When viewing multiple workbook windows, it is possible to arrange them in different

ways to that different parts of a worksheet are visible.

Once the windows are arranged in the preferred format, click the window you want to
view to activate it.

When working with large amounts of data, it is possible to split panes to lock rows or
columns in different areas to compare data.

Class Exercise

To open different worksheets from the same workbook in a new window

1. In your class folder, open the workbook OlympicStandings.xlsx

2. On the View tab select New Window.

30 | P a g e
3. The Workbook will appear in a new window. If you select the Excel tool on the
taskbar, you will see the two Windows open, labeled with a postfix 1 and 2.

It is now possible to compare different worksheets from the same workbook in

different windows.
4. Repeat step 2 two times and Select Rio2016, London2012 and Beijing2008 in each
workbook window.

5. On the View tab in the Window group, click Arrange All and select an option. A
menu loads where you select arrangements.

31 | P a g e
Tiled – the windows are resized and arranged so
that they all fit side by side on the screen in the
order in which they were opened.
Horizontal- the windows are sized equally above
each other from top to bottom.
Vertical- the windows are resized equally and
placed side by side from left to right.
Cascade- the windows are resized and arranged in an overlapping manner with
only their title bars visible.
6. Compare the medal standings for the three years. In one window, display Beijing
2008 worksheet, in the second Window, London 2012 worksheet and the third
Window, Rio 2016 worksheet.
7. To activate a window, click on the title section of the window.

8. To restore a window, activate the window the select Maximize at the top-right
corner of the workbook.

To view two workbooks at the same time side-by-side

9. In the View tab, Window grouping, select and click Side By Side. You get an
option to specify which other window you want to view.

32 | P a g e
10. To scroll both worksheets at the same time, click Synchronous viewing

11. Close the three (3) windows and keep one.

Split Panes in a worksheet

1. Open the worksheet Rio 2016

2. Locate Kenya (Row 29) and Qatar (Row 86) in the Olympic medal standings.
3. To split the worksheet, select the row below Kenya (select the row below where
you want the split-Excel splits the row above)

4. Click Split under the Window group in the view tab and the worksheet will be
split horizontally.

33 | P a g e
5. Click, drag and hold the new split put to the middle of the screen.
6. Using the scroll bar, locate Qatar in the lower pane and compare its performance
against Kenya.

7. You can also slip a window horizontally without selecting a row. Take the mouse
pointer to the top if the scroll bar. The mouse shape changes to 2 horizontal bars.
Hold down mouse key, and drag the spilt down.

8. To split a worksheet vertically, select the column to the right of the column
where you want the split and select Split on the Window menu.
9. To undo a split, click Split again OR double click on the split.
10. Close the workbook.

34 | P a g e
Freezing Panes.

To keep an area of a worksheet visible while you scroll to another area of the worksheet,
go to the View tab, where you can Freeze Panes to lock specific rows and columns in
place, or you can Split panes to create separate windows of the same worksheet.

When you freeze panes, Microsoft Excel keeps specific rows or columns visible when
you scroll in the worksheet. For example, if the first row in your spreadsheet contains
headers, you might freeze that row to make sure that the column headers remain visible
as you scroll down in your spreadsheet.

Class Activity.

1. Open the worksheet Rio 2016.

2. Select cell B10. (This means you want all the columns to the left of B visible and
all the rows above 10 visible.
3. In the View pane, Windows Grouping, select the downward arrow to see
options. Select Freeze Panes.

35 | P a g e
4. To remove the free, select View  Freeze Panes  Unfreeze Panes. The freeze
lines go.
5. Other options will be freezing first Row, or first column.
6. Close the workbook and save changes.

Class Exercise 4

1. What are the options available when viewing multiple worksheets

at the same time?
2. What tool is used to split the window into different parts of the
same worksheet?
3. How can you scroll two worksheets simultaneously?

36 | P a g e
4. Section 4 – Working with formulas and creating charts

4.1. Session 3A Basic formulas and Cell Referencing

Learning outcomes

By the end of this session, you will be able to:

i. Work with formulas in the formula bar

ii. Use built in functions
iii. Use relative and absolute cell referencing
iv. Use conditional logic to generate data summaries

Formulas are used to perform calculations on numeric data in cells. All formulas begin
with an equals sign (=). Formulas can be applied to:

 Cell values for instance where small amounts of data are being computed, you
can use =C1+C2+C3
 Cell range (adjacent or non-adjacent cells), using =SUM(C1:C40)

Whenever the value in the cells is changed, results are automatically recalculated.

1. Functions: The PI() function, returns the value of pi: 3.142.

2. References: A2 returns the value in cell A2.

37 | P a g e
3. Constants: Numbers or text values entered directly into a formula, such as 2.

4. Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk)
operator multiplies numbers.

Class activity

1. Start Microsoft Excel.

2. In cell 2B, type the = sign. The Function dropdown menu becomes active, from
where a user can select function to use.

3. Click on the arrow and select more functions. This loads Insert Menu dialogue
box. You can search a function, or select a category and then a function in that
category.

4. Microsoft Excel has 12 function categories.

5. Start OpenOffice Calc and create a new workbook.
6. Type the = sign in cell B2.
7. In the Insert Menu, select Function. This loads the function wizard.

38 | P a g e
When using formulas, spreadsheets use absolute and relative cell referencing. The
default is relative referencing. A relative reference changes based on the relative
position of the rows and columns. For example, if you copy the formula =G3+H3 to row
four, the formula will now become =G4+H4 and so on. An absolute reference contains
a $ before the row, column or both. This tells the application that the formula has to
repeatedly point to a specific cell. Select the cell that you want to have an absolute
reference and click F4.

Conditional formulas are used to perform logical comparisons. The most common
logical function is IF. Using the IF function tests a condition and if the condition is met,
performs an action, otherwise does something else. Other logical functions include

39 | P a g e
AND, FALSE, NOT, IFERROR, OR and TRUE. Conditional formulas are helpful in
highlighting certain aspects of data.

Class Exercise

To enter a formula that refers to values in other cells:

1. Open the file ExamRecords.xls in your class folder. Save the file as Filled Exam
Records.xlsx
2. In cell I2, enter the Text Average.
3. Click cell I3 under the column-heading Total, type “= (G3+H3)/2”

4. Press ENTER to display the result of the calculation.

5. To automatically replicate the formula up to cell I72, double-click the fill handle
on cell I3.

To enter a formula that uses functions:

6. Scroll to the bottom of the worksheet August2016.

7. Type the following in the respective cells:
a. F75 – LOWEST MARK
b. F76 - HIGHEST MARK
c. F78 - AVERAGE SCORE
8. In cell G75 type the formula =MIN(G3:G72)
Min Function returns the smallest number in the column
9. In cell G76 type the formula =MAX(G3:G72)
Max function returns the largest number in the column

40 | P a g e
10. In cell G78 type the formula=AVERAGE(G3:G72)
Average function returns the average of the range of cells
11. Use auto fill to replicate the formulas on columns H and I.(Hint: Drag the auto
fill handle to the right). Final results should be as follows.

12. Save the worksheet.

Using absolute reference in excel

1. Open the workbook titled Products.xlsx. Save the file as Filled Products.xlsx
2. In cell D2 type the formula =B2*C2. Use the fill handle to drag the formula to the
rest of the cells in column D. Put comma (,) separator in the cells from the Home
tab, Number grouping. Further, reduce the decimal points to no decimal point.

3. In Cell B16, the VAT has been set as 16%. We would like to calculate the VAT for
each of the items the customer has purchased. In cell E2, type =B16*D2 and drag
the formula to the rest of the cells below. The rest of the cells read 0 as the VAT.
This is because the application is using relative referencing and has moved the
formula in the next cell to read =B17*E3.

41 | P a g e
Note: We want the formula to consistently refer to cell B16 which has our VAT,
so we set an absolute reference point (cell).
4. Select cell E2. In the formula bar, click on B16 and press F4 to insert a $ and make
the column absolute. Press again to insert another $ and make the row absolute.
Your formula in E2 should now read = $B$16*D2

5. Press enter.
6. Use the fill handle to replicate the formula in column E2, to drag to E6.
7. Insert a formula to calculate the Amount Due in column F (Hint: sum of Total
and VAT column).

8. Change the number format for column B to Currency. Select the cells range
B2:B6. In the Home tab, number grouping, change from general to currency.

42 | P a g e
9. Save the worksheet and close.

To use conditional formulas

Return to the worksheet August2016 in the Exam Records workbook.

1. Type the column title “Grade” in Column J.

2. In cell J3, type the following formula:
=IF(I3>70,"A",IF(I3>60,"B",IF(I3>50,"C",IF(I3>40,"D","Fail"))))

Explanation: Use an IF function to determine the grade that the student got. The
score is held in column I. If they scored above 70, award an A, if they scored
between 60 and 70 award a B, if they scored between 50 and 60 award a C, if they
scored between 40 and 50 award a D, otherwise if their score was less than 40,
they failed.
Note: Always start from highest to lowest.
3. Replicate the formula up to cell J72. Save the worksheet.

43 | P a g e
To generate the number of students per grade:

1. In cell F81 Type “Grade Summary” and enter the grade values A,B,C,D,Fail in
cell F82-F86.
2. In cell G82, we will use the COUNTIF function to check the data in column J for
A’s, cell G83 for B’s and so on.
3. In cell G82 type the formula =COUNTIF(J3:J72,"A")
4. In cell G83 type the formula =COUNTIF(J3:J72,"B")
5. In cell G84 type the formula =COUNTIF(J3:J72,"C")
6. In cell G85 type the formula =COUNTIF(J3:J72,"D")
7. In cell G82 type the formula =COUNTIF(J3:J72,"Fail")

Grade Count
As 26
Bs 21
Cs 11
Ds 8
Fail 4
Total 70

44 | P a g e
8. Save the worksheet.

Class Exercise 5

1. How does a spreadsheet application know that the data entered in

a cell is a formula?
2. Where can formulas be applied?
3. What is the difference between absolute and relative cell
referencing?
4. What is the purpose of conditional formulas?
5. Name the number formats that are available in a spreadsheet
application.

4.2.Session 3B – Creating and Formatting Charts

Learning outcomes

By the end of this session, you will be able to:

i. Create different charts types

ii. Modify the text content of the chart images
iii. Apply sparklines on a chart

45 | P a g e
Spreadsheet applications provide an excellent tool for creating charts. They offer
different types of charts to represent different types of information in a graphical
format.

 Column Chart-compare values across categories

 Line Chart-display trends over time
 Pie Chart-Display contribution of each value to a total
 Bar Chart- shows comparisons among individual items
 Area Chart- emphasizes the magnitude of change over time and draw attention
to a trend.
 Scatter charts- show the relationship in several data series plotted as X-Y
coordinates. Mostly used to display statistical and engineering data.
 Bubble Chart-type of scatter chart where the size of a bubble represents the value
of a third variable

46 | P a g e
 Stock Chart-show the fluctuation of stock prices or scientific data
 Surface Chart- find optimal combinations between two sets of data
 Doughnut Chart- can contain more than one data series and shows the
relationship of parts to a whole.
 Radar Chart- compares the aggregate values of several data series.

The charts available in Apache OpenOffice are as follows:

A Sparkline is a small chart that fits inside a cell on a sheet and can reveal patterns and
show trends.

47 | P a g e
Class Exercise

1. Add a new sheet to the workbook ExamRecords.xls. Rename it to Charts.

2. In cell A1 Type “Gender” and B1 “Number of Students”. In cell A2 and A3, type
Male and Female respectively.

To reference data in a different worksheet

48 | P a g e
3. Spreadsheets allow you to use data even if it is not in the current worksheet. To
count the number of male and female students in the worksheet August2016, in
Cell B2:
a) Method I-type =COUNTIF(
b) Click on sheet August2016 and select data in the column C3 to C72 to
create a reference to the worksheet
=COUNTIF(August2016!C3:C72
c) And a ‘, ’ after 72 and type “M”) . this will count the number of
students whose gender is male.
=COUNTIF(August2016!C3:C72,"M")
d) Do the same for female. Replace “M” with “F”. Remember:
Spreadsheets use relative referencing by default. Confirm that your
formula begins count from cell C3 to C72.
=COUNTIF(August2016!C3:C72,"F")

4. Save the worksheet.

Creating Pie Chart

1. To create a Pie Chart to show gender distribution: Select the rows A1 to B3.
2. On the Charts tab, under Insert Chart, select Pie and pick Pie in 3D

49 | P a g e
3. In the Design tab, Chart Layout grouping, Change the chart layout to Layout 6.

4. Any change to the data set in the worksheet August2016 will automatically be
updated on the chart.
5. Save the worksheet.

Copying Data and Using Different Options to Paste

1. Copy the grade summary data in cell F81 to G86 from the worksheet
August2016.
2. Return to the worksheet Charts and in cell H1, right-click and under Paste
Special pick Paste Values.

50 | P a g e
3. (Unlike in the previous example, this method will paste the number of students
per grade as static text. If there is a change in the August2016 worksheet, it will
not be reflected.) . Change the column Headers as shown:

Grade Number of
Summary students
A 26
B 21
C 11
D 8
Fail 4

4. Highlight the data range H1 to I6.

5. Select Insert  Column  2-D Column  Clustered Column chart.

51 | P a g e
The column inserted is as follows:

Grade Summary
30

25

20

15
Count
10

0
As Bs Cs Ds Fail

6. To change the emphasis from number of students to grades, click the chart. From
the Design tab, Data grouping, select Switch Row/Column.

7. Select the text in the Chart Title box, and type the chart title: ”Grades Summary”.

52 | P a g e
Grade Summary
30

25

20 As
Bs
15
Cs
10 Ds
Fail
5

0
Count

8. Save the worksheet.

Working with Line Charts

1. Insert a Line Chart

Type the following data in Cell A27 of the worksheet Charts.

Postgraduate
Diploma Degree diploma
2011 1 2 2
2012 0 2 3
2013 2 3 1
2014 8 9 0
2015 1 1 1
2016 7 10 1

2. Select the cells A27:D33. From the Insert tab Charts grouping, select 2 – D Line,
Line. (Insert  Line  2D Line  Line.)

53 | P a g e
3. In the Design tab, Chart Layout grouping, select Layout 5

4. Change the chart title to “Intake- Trends 2011-2016” and the Axis on the left to
“Number of Students”.

54 | P a g e
Intake- Trends 2011-2016
12
Intake- Trends 2011-2016

10
8
6
4
2
0
2011 2012 2013 2014 2015 2016
Diploma 1 0 2 8 1 7
Degree 2 2 3 9 1 10
Postgraduate diploma 2 3 1 0 1 1

5. Save the worksheet.

Applying Sparkline on a chart

1. In the Intakes data set, select the data range B28-D33.

2. From the Insert tab, Sparkling Lines grouping, select Line. The Create
Sparklines dialogue box loads.
3. Select the Pointer to the worksheet that allows you to select the range, where
Excel will place the sparkline

55 | P a g e
4. Click cell E28, hold down the mouse button and scroll to cell E33. The range is
automatically added to the small dialogue window. Click on the pointer that
takes you back to the formula

5. The required data in the dialogue box is now complete.

Note: This method is better than typing in formulas or entering the required
information manually, because is it Accurate (less possibility of making
mistakes), Visually guiding (shows you the worksheet areas you are working
on/selecting) and faster (dragging mouse is faster than typing).

6. The Sparklines as placed in the range as follows.

56 | P a g e
7. You can specify the areas of interest that are required for highlight. In the Design
(Sparkling Tools) Tab, Show Grouping, select what you want to highlight.

To highlight and differentiate the options available (High, Low, Negative..Etc.), a

user can alter the colours, for ease of differentiation of the item of interest. In the
Design (Sparkling Tools) Tab, Style Grouping, select different colours for each item.

57 | P a g e
Class Exercise 6

1. Why are charts important when working with spreadsheets?

2. What data is displayed by the various chart types?
3. How is a Sparkline different from a line chart?
4. How can you reference data in a different worksheet?

5. Section 5 – Advanced Formatting

5.1.Session 4A – Data Manipulation with Formatted Data

Learning outcomes

By the end of this session, you will be able to:

i. Format cell borders and apply shading to a cell or group of

cells
ii. Add and delete comments
iii. Sort data by specifying various criteria

58 | P a g e
Spreadsheets allow a user to sort data by specifying various criteria. Sorting can be
done on numbers, text or custom lists. Sorting rearranges the contents of a column.
When sorting a table with multiple columns, all the rows are rearranged based on the
contents of a particular column.

When sorting data:

To sort the lowest values to the top of the column, sort Ascending

To sort the highest values to the top of the column, sort Descending

It is also possible to sort several columns of data by adding several

levels of sorting criteria such as sorting a report first by student
grades, then by course then by student name. Each sort level is
represented by a single row in the Sort dialog.

Class Activity

1. Open the file ChurchActivities.xls in your class folder. Save the File As Church
Activities – Filled.xlsx
2. Select the worksheet ChildrenMinistry.

Applying borders

3. Select cells A1-D13

59 | P a g e
a. Method I- On the Home tab, in the Font grouping, click the arrow besides
the Borders Button. Select All Borders for a line to appear as the border for
the selected cells.

b. Method II – Right click on the selected cell, and select Format Cells. In the
Format Cells dialogue box, select Border tab. Select the style/thickness of
the lines you want, and click on the Outline button and Inside button.

60 | P a g e
2. Select A1-D2 and Merge the cells. Add a Top and Thick Bottom border to the
merged cell from the Borders toolbox

Formatting Colours

1. Method I- Select cell A1

2. In the Home tab, Font grouping, click on drop down arrow to get the Border
menu option.

3. Choose a color of your choice on the right, and on the left, select the border you
want to apply the style to.

4. In the Home tab, Font grouping, click on drop down arrow to get the Border
menu option. Select the different lines you want to colour following this process.

61 | P a g e
5. Save the document.

Working with Fonts

1. Change the font “Upcoming Children’s Ministry Activities” to a color and font of
your choice, and font size 14. (Font Style – Bradley Hand ITC, Font Colour – Dark

Blue, Text 2, Lighter 40%, Font Size 14.

Applying a Fill Colour

1. Select cells A3:D3
2. In the Home tab, Font Grouping, Fill Colour (Theme Colour), select light green.

62 | P a g e
3. Save the worksheet.

Adding a comment to a cell

1. Select the cell B5

2. Right-click and select Insert Comment from the pop-up menu.
3. Delete the name or initials preceding or keep. Type the comment “Please pack a
snack” click outside the comment box.

4. Resize the Comment box to have less space after the comment
5. For all rows that have children less than 6 years old, add the comment “Please
pack a snack”.
6. To delete a comment, right click on the cell that has the comment and select.

7. Save and close the workbook.

Sorting Data

1. Open the workbook OlympicStandings.xlsx in your class folder and save as

OlympicStandings – Sorted.xlsx
2. Select the worksheet “Olympic History”

To sort a column

63 | P a g e
3. Click on any cell in the Location column.
4. On the Data tab under Sort & Filter grouping, click the arrow next to Sort

5. Sort Ascending.
This will sort the Olympic hosts in alphabetic order and move the respective year
to the sorted row. This automatically sorts data ROW-WISE, that is, not just the
selected column.
6. Save the worksheet

Sorting a list by multiple columns

1. Select the worksheet “All Time” in the workbook OlympicStandings.xlsx.

2. Select the data A10:H239
3. On the Data tab under Sort Filter, select Filter.

4. In the Sort dialog window

- Select the checkbox “My list has headers”

64 | P a g e
- In Sort By, select “Nation”, in Sort on, select “Values” and Order, “A to Z”
Next to Then by, “Total” Sort on “Values” and Order “Largest to Smallest”.

Quiz: Explain what kind of sorting this results are.

5. Save and close the workbook.

Class Exercise 7

1. How can you remove borders applied to cells?

2. What is the importance of a comment added to a cell?
3. Under what menu and grouping of commands will you find the sort tool
in excel?
4. How would you change the color, weight and style of a borderline?
5. What is the difference between sorting in ascending and descending
order?

65 | P a g e
5.2.Session 4B: Formatting for Enhanced User Interface
Learning outcomes

By the end of this session, you will be able to:

i. Add images to a worksheet

ii. Editing Images (Resizing, positioning and borders)

iii. Enhance the look of a worksheet using Clip Art, SmartArt,

shapes

iv. Use the image Editor tool to format images

Excel is typically used as a way to store, sort and manipulate text

and numbers, it is also a helpful way to store images along with
that data, especially if a user need the image to be part of the
data.

Inserting Bitmaps In Apache OpenOffice Calc

A bitmap image can be inserted in OpenOffice Writer, OpenOffice Calc,

OpenOffice Draw and OpenOffice Impress documents. To add an
image in OpenOffice Calc, follow the following g steps.

1. Choose Insert - Picture - From File.

2. Select the file. In the File type box you can restrict the selection to certain file
types.

66 | P a g e
3. Click the Link box if you want a link to the original file.

If the Link box is marked, whenever the document is updated and loaded the bitmap
image is reloaded. The editing steps that you have carried out in the local copy of the
image in the document are re-applied and the image is displayed.

If the Link box is not marked, you are always working with the copy created when the
graphic was first inserted.

To embed graphics that were first inserted as links, go to Edit - Links and click the
Break Link button.

4. Click Open to insert the image.

Using F4 Function key for last Activity

Class Activity

1. Start MS Excel. Open the file ChurchActivities - Filled.xlsx in your class folder
and locate the ChildrenMinistry worksheet.
2. Insert 5 rows above the heading “Upcoming Children’s Ministry Activities”.
 Right click row one and select Insert Row. This adds one row.
 Press F4 key on the keyboard 4 times. This repeats the last action by the
user (which was to Insert a row).

Inserting and Editing Images

1. Insert the image church.jpg from the class folder: In the Insert tab, Illustrations
grouping, select Picture.

67 | P a g e
2. With your mouse, move the cursor to the bottom right of the image until you get
a double-headed arrow. Holding down the SHIFT key, resize the image to fit at
the top left of the rows we have just added.

3. To add an effect to the image, double click on the image to bring up the Format
(Picture Tools) tab. In the Picture style grouping, select Soft Edge Oval or one
you are comfortable style.

68 | P a g e
Inserting and Formatting SmartArt graphic

1. On the Insert tab, Illustrations grouping, select SmartArt tool.

2. From the Choose a SmartArt Graphic, select the Process category on the left side
pane of the dialogue window. On the right side of the dialogue window, select
Step up process and click OK.

Move the inserted canvas to below the rows with data.

3. Type “Cribs” “A.R.K”, “Pathfinders”
4. Right click on the Text Pane “Pathfinders” and select Add Shape  Add Shape
After

69 | P a g e
5. Click on the new Text Pane to select it. In the Design tab, Create Graphic
category, select Text Pane tool.

6. In the new Text Pane dialogue box, add “Crossroads” in the new pane.

7. Under each of the levels, indent using the right arrow on the text pane and type
the ages for each group.

70 | P a g e
8. To format the Smart Art graphic, select the Canvas. From the Design tab 
SmartArt Styles grouping, select Change Colors tool  Colorful  Colorful –
Accent Colors.

9. Change the color for each of the titles of each level to match the color of the step.
Adjust Font Size and style to suit the sizes.

10. Save the worksheet.

71 | P a g e
Class Exercise 8

1. How can you remove borders applied to cells?

2. What is the importance of comments?
3. Under what menu and grouping of commands will you find the sort tool in MS
Excel?
4. How would you change the border line color?

6. Section 6 – Advanced formatting

6.1.Session 5A: Rules-based Data Manipulation and Formatting

Learning outcomes

By the end of this session, you will be able to:

i. Apply styles to worksheets

ii. Apply conditional formatting to a group of cells
iii. Format cells as a table

Spreadsheets provide a quick and effective way to create professional-looking

worksheets that effectively display data. Styles are used to change the look of tables,

72 | P a g e
data, charts or shapes and are predefined in the spreadsheet application. The following
styles can be applied to a worksheet:

- Cell styles – used to ensure that all cells have consistent formatting: font size,
number formats, cell borders and cell shading
- Table styles – select predefined alternate row styles on table rows
- Chart Styles - apply a predefined style to a chart
- Pivot table styles – select a predefined alternate-row style

Using styles makes it faster to apply a style and multiple formatting attributes to a
worksheet or to multiple worksheets as opposed to changing each element individually.

Conditional Formatting allows a format to be added to a cell

or a range of cells based on a specified criteria. These formats
can be applied based on the value of the current cell, the value
of another cell in the current worksheet or as a result of a
formula. The conditional formatting menu provides different
formats that can be applied to a cell or range of cells.

Conditional Formatting using OpenOffice Calc.

Using the menu command Format - Conditional formatting, the dialog allows you to
define up to three conditions per cell, which must be met in order for the selected cells
to have a particular format.

Caution: To apply conditional formatting, AutoCalculate must be enabled.

Choose Tools - Cell Contents - AutoCalculate (you see a check mark next
to the command when AutoCalculate is enabled).

73 | P a g e
With conditional formatting, you can, for example, highlight the totals that exceed the
average value of all totals. If the totals change, the formatting changes correspondingly,
without having to apply other styles manually.

Class Activity.

1. Open Apache OpenOffice Calc

2. Enable Autocalculate: Tools  Cell Content  Autocalculate {}

Conditional Formatting in MS Excel

Conditional formatting helps you visually answer specific questions about your data.
You can apply conditional formatting to a cell range, a Microsoft Excel table, or a
PivotTable report. There are important differences to understand when you use
conditional formatting on a PivotTable report.

To Define the Conditions

1. Open MS Excel. Select the cells to which you want to apply a conditional style.
A1:M20

74 | P a g e
2. On the Home tab, in the Styles group, click the arrow next to Conditional
Formatting, and then click Color Scales.

Highlight Cells Rules - Apply formatting to cells that

satisfy a specific condition

Top/Bottom Rules - Apply formatting to cells that

satisfy a statistical condition in relation to other cells in
the range

Data Bars / Color Scales / Icon Sets - Apply formatting

to all cells in the range, depending on their value in
relation to one another;

New rule – Apply a custom rule

Clear rules – Removes all the rules that have been

applied to the worksheet.

Manage Rules – View or edit the conditions that have bee set

When more than one condition is used, the conditions are applied in the order in which
they appear in the “Rules Manager” window.

Tables can be formatted to make reading data easier. Rows and columns that have been
formatted as tables can be managed independently from the rest of the data.

Class Exercise

75 | P a g e
.

To apply styles to a worksheet

1. Open the file ExamRecords Filled.xlsx from your class folder and activate the
worksheet August2016. Save the file as ExamRecords Formatted.xlsx
2. In the worksheet “August2016”, select the column headings (A2:J2)
3. On the Home tab , in the Styles group, click Cell Styles
4. Select a style and click the worksheet to deselect and see the effect.

5. In the charts worksheet, apply the same style to the headings for the tables
(Gender, Grades and Intake). Click the worksheet to deselect and see the effect.
6. Go back to the worksheet August2016 and select any cell on row 2.
7. In the Styles drop-down, right-click the style you applied in step 3/4 above.
Select Modify from the pop-up menu.

76 | P a g e
8. In the dialog box, view what formatting options are included in the style. Click
the Format button.

9. Change the Font, Font Size, Font color and Font weight. In the Fill tab, choose a
darker color and in the Alignment tab, in vertical alignment select Center.

77 | P a g e
10. Select OK on the Format Cells dialogue box, and OK on the Style dialogue box.
11. In both worksheets, the column headers that have the same style applied will
change to reflect the new formatting options.

12. Save the workbook.

To apply conditional formatting to a group of cells

1. In the workbook ExamRecords Formatted.xlsx, select the cells the Grades

Column from (J3:J72).
2. On the Home tab , in the Styles group, click Conditional Formatting
3. Under conditional formatting, select Highlight Cell Rules and pick Equal to

4. In the dialog window that opens, enter Cell value is equal to Fail. Pick light red
fill with dark red text from the Format with drop down.

78 | P a g e
5. Click OK. All the cells that have a Fail grade will be highlighted.
6. Using the same process, apply cell formatting to all cells that have a Grade –D.
Apply a different format color.
7. Save the worksheet.

To add a customized style

1. In the Home tab, Styles group, Click the Cells Styles tool and select New Cell
Style

2. Set the Style Name as Sheet Titles and select all the checkbox options except
Number.

79 | P a g e
3. Click Format and set the properties for font, Fill, border and alignment. Click
OK.

4. Select the heading Student Marks on row A1 and apply the custom style Sheet
Titles. Select Home  Cells Style  Custom. You will see the custom made
titles.

5. Save the workbook.

To format a table

1. In the workbook ExamRecords Formatted.xlsx, select the cells the (A2:J72) within
the grades table.
2. In the Insert tab, select Table. Specify that the table has headers.

80 | P a g e
3. This loads a new tab, Design (Table Tools). On the Design tab, in the Table
Styles group, select a style to apply.

4. Click OK to apply the style

5. To convert this table back to a normal range of cells and keep the formatting,
a. Method I -Right click on any cell with the column heading. From the
Design Tab, Tools group, select Convert to Range tool.

81 | P a g e
When prompted to save changes, select Yes.

Method II.
Right click on the cells with the Table Header

6. Save the files.

82 | P a g e
Class Exercise 9

1. What is the advantage of applying styles to a worksheet?

2. Why is conditional formatting useful?
3. How can you create a custom style?

6.2.Session 5B: Working with Variables and Data Types

Learning outcomes

By the end of this session, you will be able to:

i. Use different data types for different cells

ii. Format number types to currency and other types
iii. Format commas and decimal places
iv. Use format painter
v. Formatting date and time.

Data on a spreadsheet can be displayed in different formats such as number, currency,

percentage, fraction, date, Time, and fraction.

83 | P a g e
In the Home tab, number group, there are different menus available. The currency
format, percentage and decimal points are the default formats for numbers.

The drop down menu offers advanced formatting for numbers.

Applying Formats to Numeric Data

Class Activity

1. Open the Excel file ChurchActivities - Filled.xlsx in your class folder.

2. In the worksheet Churchbudget, select the data in Cell (B4:B17).

84 | P a g e
3. In the Home tab, Number group, set the format to Currency from the drop down
menu. This will automatically insert the coma delimiter and decimal points. And
a dollar sign is added to show it is currency.

4. To increase or decrease the number of decimal places, click on the icon:

5. Highlight cells (A3:D3) and change the font to Comic Sans MS, Size 14 and Bold.
Change the fill color to Orange.

6. To change the currency from Dollars ($) to Kenya Shillings (Kes), select the data
in Cell (B4:B17).
7. Right click select Format Cells from the short cut menu. In the Format Cells
dialogue box, in the Number tab, under Symbol, select KES.

85 | P a g e
8. Save the worksheet.

Using format painter

Format painter tool picks all the formatting of one item

to another. It is possible to quickly copy cell formats
(font, colors and size) from one cell to another using the
format painter.

1. In the worksheet ChurchBudget, merge the cells (A1:D1).

2. Change the font to Arial Black size 24.
3. To use format painter to replicate the format of the heading (Budget Presentation
2016-2017)

86 | P a g e
4. On the Home tab, Clipboard group click once on the paintbrush. The mouse
pointer changes to a brush wen moved over the cells.

5. Click on cell A2. The font type, size and merging of cells gets repeated in the new
selection. The paint brush changes back to normal mouse pointer.
6. Center the cell content of cell A2 with the center tool.

7. Select paint brush and replicate the formatting in cell A1.

8. Save the workbook.

Working with Date and Time

1. In cells B21 fill a date in the format date-Month-year e.g. 8th February 2017.
2. Place the cursor in the bottom right corner till the mouse pointer changes to (+).

87 | P a g e
Hold down the left mouse button and drag down to cell B28. This automatically
adds a day to the date.
3. Click on Undo button to change the changes.

4. Select cell B22. Type = B21+7. Select enter.

This adds 7 days to the previous date.

Copy the date formula down to cell B28.

5. Select Cells (B20:B28). Right click and select Format Cells. In the number tab of
the Format Cells dialogue box, under Category, select custom.
6. Under Type text box, type the british style date format. dd/mm/yyyy.

88 | P a g e
7. Select Cells (D21:D28) and set the number format to Time.

Type in time from 10:00 Am and add intervals of 1 hour.

8. Select cells (D21:D28) and use a date format of your choice, using the custom
option of Format Cells menu.
9. Save the workbook.

89 | P a g e
Class Exercise 10

1. What tool is used to replicate formatting on a different cell(s)

2. What are the number formats available for use and what data do they
represent?

7. Section 7 – Creating and printing data reports

7.1.Session 6A: Report Generation

Learning outcomes

By the end of this session, you will be able to:

i. Add and remove data filters

ii. Group and ungroup rows and get sub-totals
iii. Show an hide data details of grouped data

90 | P a g e
Data filters applied in a spreadsheet sift the data so that only data that meets set
conditions is displayed. This makes it easier to focus on specific information. Filtering
focuses on rows of data. If the row does not meet the condition, it is filtered out by
temporarily hiding it from view. Data filtering can be applied to

 Numeric data – this can be filtered on the whether the data equals a certain
number, the data is above or below the average value or whether the data is
greater /less than a specific number.
 Text data – this can be filtered on whether the word begins/ends with a specific
letter of the alphabet, contains one or more letters or matches a certain word.

By filtering information in a worksheet, you can find values quickly. You can filter on
one or more columns of data. With filtering, you can control not only what you want to
see, but what you want to exclude. You can filter based on choices you make from a list,
or you can create specific filters to focus on exactly the data that you want to see.

You can search for text and numbers when you filter by using the Search box in the
filter interface.

When you filter data, entire rows are hidden if values in one or more columns don't
meet the filtering criteria. You can filter on numeric or text values, or filter by color for
cells that have color formatting applied to their background or text.

Class Activity

1. Open the file ExamsRecord filled.xlsx

2. Save the File As ExamsRecord Filled with Filters.xlsx
3. Select cells A2:J72
4. On the Home tab, Editing group select Sort & Filter  Filter

91 | P a g e
5. This adds filters for the data on the top row as follows

6. To filter courses the courses, Uncheck the (Select All) check box. This deselects all
the programmes. Select Level Filter and tick “Diploma” by clicking on it once.

7. Select OK. This lists all the Diploma courses only. 19 records only.
8. Filter the Diploma students with grade A.

92 | P a g e
9. To get back to the original record set, select “(Select All)” in both categories that
had filters applied.

Sorting Records with Multiple Fields

1. Clear the filters by selecting Home  Editing  Sort and Filters  Filters
2. Click on any cell with data. Select Home  Editing  Sort and Filters  Custom
Sort.

3. Sort the records to have the programmes sorted based on the Column= Level, Sort =
Values and Order = A to Z. Always “My data has headers” to ensure the row with
header rows does get included in the data.
4. The default for sorting is only one column. To sort multiple columns, select “Add
Level” tab.
5. Add three column to sort with. Then select the following values.

93 | P a g e
6. This sorts The courses first, then the Gender then the grade.

Grouping and Ungrouping Data.

If you have a list of data that you want to group and summarize, you can create an
outline of up to eight levels, one for each group. Each inner level, represented by a
higher number in the outline symbols displays detail data for the preceding outer level,
represented by a lower number in the outline symbols.

Use an outline to quickly display summary rows or columns, or to reveal the detail data
for each group. You can create an outline of rows (as shown in the example below), an
outline of columns, or an outline of both rows and columns.

1. Open the file Exam Records Filled with Filters.xlsx, if not already open.
2. Make a copy of “August2016” worksheet by right clicking on the worksheet name,
and selecting “Move or Copy  Create a Copy

94 | P a g e
3. Delete Row 1 with the data “Student Marks”
4. Delete all the content between Row 73 and Row 86
5. Move the first five columns to the end, so that Level becomes the first column.

Student Number Student Name Gender Course Intake Year

6. Delete the five columns (A to E) so that Level becomes the first Column.
7. Select cell range A1:J71
8. From the Data tab  Sort & Filter  Sort Ascending (A to Z). This lists degree as top
and PostGraduate at the bottom.

9. In the Data tab, Outline group, select Subtotal. Fill in the Subtotals of the level as
follows.

95 | P a g e
10. This gives a Count of the different Levels in the first column and a total of the
Levels.

11. Click on the collapsing icons on the levels on the left side to create a view of
summary data only.

96 | P a g e
7.2.Session 6B: Managing Headers/Footers and Page setups
Learning outcomes

By the end of this session, you will be able to:

i. Set up a document for printing

ii. Configuring page orientation
iii. Managing page scaling by configuring page margins
iv. Add header and footer to worksheets

Using the Page Layout view, it is possible to organize pages before printing. On the
page layout,

97 | P a g e
Page Margins.

Page margins are the blank spaces between the

worksheet data and the edges of the printed page.
Top and bottom page margins can be used for
some items, such as headers, footers, and page
numbers.

To better align a worksheet on a printed page, you

can use predefined margins, specify custom
margins, or center the worksheet horizontally or
vertically on the page.

Note: Page margins that you define in a given worksheet are stored with that worksheet
when you save the workbook. You cannot change the default page margins for new
workbooks

Page Layout

Page orientation defines the placement of elements on a page are

laid out. The two main orientations are landscape and portrait.

With the scaling option

- A worksheet can be enlarged or shrunk to better fit the printed pages

- Fit the worksheet to the paper before printing
- Print the worksheet on a specific number of pages

98 | P a g e
Class Exercise

To shrink/enlarge a worksheet for better printing

1. Open the file ExamRecords – Filled.xlsx in the class folder.

2. Click anywhere in the worksheet August 2016
3. On the Page Layout tab in the Page Setup group, select Margins  Custom
Margins

Alternatively, on the Page Layout tab in the Page Setup group, select the drop
down arrow on the bottom right corner of the menu.

4. On the Page Tab, under Scaling, click Adjust To and enter a percentage. To
reduce the worksheet choose a percentage less than 100%, to enlarge the
worksheet select a percentage larger than 100%.

99 | P a g e
To change the page margins

5. Under the Page Setup dialog, select the margins tab and adjust the Top/Bottom,
Left/Right margins.

Working with Pages

1. On the Page Layout tab in the Page Setup group, select Orientation. Change
from Portrait to Landscape.

2. From the View tab, Workbook Views group, select Page Break Preview. This
view shows a user where pages will break when the document is printed.

100 | P a g e
3. You get a dialogue box informing you the purpose of the blue lines inserted in
the document. Do not Tick the checkbox. If you check, the dialogue box will not
show again in the future and a new user may not know how to manipulate the
blue line.

4. Take the mouse pointer to the line that separates “Page 1” from “Page 5” as
indicated in the page water marks.
5. The mouse pointer changes to double edged arrow, hold down the mouse button
and drag right, to move the page boundary.

6. The pages are reduces from 6 to 3.

7. Save the file.

101 | P a g e
Adding Headers and Footers

1. On the View tab, Workbook Views group, select Page Layout.

2. This changes the worksheet to show the user how the print version will look
like.
3. Type the Header as follows:

4. Type the Footer as follows:

8. Click Ok to change the margin settings.

9. On the View tab, Workbook Views group, select Normal view.
10. To see how the three pages will look like after printing, in the Fila tab, select
Print. The Print dialogue box loads. School down to see the 3 pages.
11. Save the file.

Print Menu Settings.

102 | P a g e