Create a simple Spreadsheet

Research and Guided Practical

Class Task 1:Research

1.Give definitions and a labelled diagram for the following terms:

worksheet, cell, cell address, range, row , column.

2. Create and save spreadsheet document  which replicates the following arrangement:
[a student report cart , Subject and marks]

Class Task 2: Guided Practical

Create and save spreadsheet document  which replicates the following arrangement:

Student NamePhysicsArtMusicMathematicsTotal

Use formulas to calculate the maximum mark and totals for each student.

© 2020  Vedesh Kungebeharry. All rights reserved. 

Spreadsheet Functions


A function is a predefined formula provided by a spreadsheet application.

Functions are written in the following format:

                =FunctionName(parameter1, parameter2, …, parameterN) 


=sum(e3, d4, r5),



a function with a single parameter is shown below:



=SUM(F3:F4)  (note this is 1 parameter, a range)


Note that a function’s  actions can be accomplished with a formula:

e.g =SUM(D3:F3) becomes =D3+E3+F3

Absolute addressing – prevents a cell’s row number or column letter from being updated when a formula is copied to another cell.

e.g          $C4        – prevents the column letter from being updated

                C$4        – prevents the Row number from being updated

                $C$4      – prevents both column letter and row number from being updated.

Secret tip!!!!!!!!  – use F4 to set a cell’s address to absolute addressing.

Class Demo

Download the excel file here:

StudentRoll NoMathFrenchSpanishTotalsAverageBonus ScorePass/Fail?
Jim1105520710Work harder
Mary2752051003350Good Job
Tobias Reiper3125 17913Work harder
Mario4135523812Work harder
Student Number4
Max Average33
Min Average7
# Marks < 303
Number of spanish marks entered3
Number of spanish marks Missing1

Sheet with formulas

StudentRoll NoMathFrenchSpanishTotalsAverageBonus ScorePass/Fail?
Jim11055=SUM(C3:E3)=AVERAGE(C3:E3)=(1+$B$10)*G3=IF(G3<30, “Work harder”, “Good Job”)
Mary275205=SUM(C4:E4)=AVERAGE(C4:E4)=(1+$B$10)*G4=IF(G4<30, “Work harder”, “Good Job”)
Tobias Reiper3125 =SUM(C5:E5)=AVERAGE(C5:E5)=(1+$B$10)*G5=IF(G5<30, “Work harder”, “Good Job”)
Mario41355=SUM(C6:E6)=AVERAGE(C6:E6)=(1+$B$10)*G6=IF(G6<30, “Work harder”, “Good Job”)
Student Number=COUNT(B3:B6)
Max Average=MAX(G3:G6)
Min Average=MIN(G3:G6)
# Marks < 30=COUNTIF(G3:G6, “<30”)
Number of spanish marks entered=COUNTA(E3:E6)
Number of spanish marks Missing=COUNTBLANK(E3:E6)


Give examples of the following functions:

Sum, average, max, min , count, counta, countif , date , if 

© 2019  Vedesh Kungebeharry. All rights reserved. 

Pivot tables. [Size – 9 mb]

This post contains a gif 
image that is 
9 MB in size.


You’re going to wish that your teachers started excel with a pivot table instead of teaching you formulas and filters, some of which can be complex to understand and difficult to put into practice. I guess teachers know that our absolute addressing lab class would be interrupted with , “You should’ve just used a pivot table!”

What are they and what do they do?

Pivot tables are new tables that are created from an existing table that contain custom summary features for your original table’s data.

For example, consider the following data:

Employee #NameRegion Sales Commission For January# Items Sold
1Roly BasimaAmericas18501
2Louna RebaAfrica28307
3Cole KhayriyyaCaribbean10505
4Khalida FuadCaribbean22807
5Perlie ShanelleAsia15103
6 Briscoe Benjamin Asia16001
7Misha StreetAmericas46008
8Janice KemplinCaribbean18302
9Aracelis OylerAfrica29304
10Alvina CoppolaAfrica17707

Figure 1 – Raw data (note that all data was randomly generated.)

You could quickly arrive at a summary view:

Pivot table showing a summary of our data.
Pivot table showing a summary of our data.

This takes about 20 seconds and requires no knowledge of formulas.

The proof is shown in the gif below:

Inserting a Pivot Table in less than 20 seconds.

Creating a pivot table from our data

  1. Copy and paste the data from the table above (Figure 1) into a new excel workbook. The result is shown below:

2. Select all the data that will be used to create your pivot table:

3. Click on the insert tab on the ribbon, and choose Recommended Pivot Tables :

4. Choose a suggested pivot table that you’d like to insert. In this example, we chose the third one. Click “Ok” to insert the table.

5. After Clicking on “Ok” (from step 4), Excel inserts your pivot table on a new sheet. See the result below:

Resulting pivot table

Making sense of your pivot table by manipulating it’s rows, columns and values.

A demonstration of how to manipulate the pivot table in the video below:

Using a pivot table effectively.

Download the finished tutorial from the video here:

Here is a link to the Pivot table tutorials provided by Microsoft office:–y2q6

A pivot table’s purpose

A pivot table can help you to make strategic decisions based on your operational data. In our example above, we look at some sale information for the month of January (Operational Data) and create a summary (Strategic Data) to make strategic decisions.

Strategic decisions are made by the leaders of your organization (by you if you’re the leader) and affects how you approach your next set of goals.

From our example, we see that the largest commissions are paid to our sales people in the African continent who also sold the most items in January. Using that information, we may want to communicate with our sales force in Africa to determine the causes and try to apply their methods fro success in other regions.

Our assumption becomes, “Our African Sales force seems to be motivated and practice good methods in selling our products. We must investigate if this is true by comparing their methods to other regions.”

From the assumption above, we can now make our first strategic decision:

  1. Monitor sales in Africa to see if the trend continues

If we find our assumption to be true we can now make the following strategic decisions and plan:

2. Learn the best practices from our African Sales Force

3. Start a training program for our entire company scheduled to run in the month of July .

In class exercise

Describe any other scenario that can result after analyzing the pivot table from our example.


Random name generators for names used in example:

© 2019  Vedesh Kungebeharry. All rights reserved.