Spreadsheet Microsoft Excel 2013

Chapter 1: The Basics

1.1 Working environment

Name box Formula bar Column

Command tab

Worksheet area

Row

Cell

1.2 Cell reference (cell address)

Each cell is identified by its reference the column letter and row number that intersect at the
cell's location. For example, a cell in column D and row 5 is cell D5.

1.3 Basic editing

You can edit cells with the help of Enter key, arrow keys, Delete key, Backspace key, and Escape key.
Wrap text can be done by using shortcut Alt-Enter .

1.4 Format Cell

You can change the following formats of a cell:
a. Number: including General, Number, Currency, Date categories
b. Alignment: including Text alignment, and wrap text
c. Font: including Font, Font style, Size, Underline, and Color
d. Border: Line Style
e. Fill (shading): Background Color

1.5 Changing the column width or row height

To change the width of one column, drag the boundary on the right side of the column heading
until the column is the width that you want.
To change the row height of one row, drag the boundary below the row heading until the row is
the height that you want.

Page :1 / 9
Spreadsheet Microsoft Excel 2013
1.6 Merge cells
Merging combines two or more cells to create a new, larger cell.

1.7 Autofill
Instead of entering data manually on a worksheet, you can use the Auto Fill feature to fill cells with
data that follows a pattern.

1.8 Insert/rename/delete/move/copy worksheets

You can add more worksheets, rename them, or delete them as needed.

Activity 1: 9-day Weather Forecast

Open file [Link] .
Visit the Hong Kong Observatory website. ([Link]
Go to the page 9-day Weather Forecast.
Using the data shown on the webpage, edit the spreadsheet to show the following information.

Format the spreadsheet with the following specific formatting features:

Font to be used: Calibri
Font size to be used: 12
Sundays should be filled red
Merge the cells B1 to J1
Wrap the text in cells A4 and A5
Rename the worksheet name to Weather Forecast

Exercise1: Calendar of your birthday month in 2015

Create a spreadsheet to show the calendar of your birthday month. You should follow the
requirements below:
Merge cells to show the name of the month (2)
Shade all cells of Sundays and/or public holiday(s) in red (except your birthday) (1)
Shade the cell of your birthday in green (1)
Apply appropriate borders (2)
Good appearance (2)
Submit the file with the filename format “ex1_1CNN.xlsx” (e.g. ex1_1A01.xlsx) (1)

Page :2 / 9
Spreadsheet Microsoft Excel 2013
Chapter 2: Manipulating worksheets

2.1 Hide/Show columns or rows

You can hide/show one or more rows or columns.

2.2 Freezing panes

When you scroll down to see your rows of data, your column names in the top row might
disappear. To fix it, you use Freeze Panes.

2.3 Sort data

You can change the order of the data, according to specific column(s) and ascending/descending
order.

2.4 AutoFilter
In order to focus on a specific set of data, you can filter data based on the cell value(s) or with
customized criteria.

2.5 Conditional Formatting

It provides visual effects to help you quickly make sense of the data.

Activity 2: Manipulating mark sheet

Open file [Link].
Follow the instructions below:
1. Hide Column J
2. Freeze panes so that the top row and leftmost column will always be visible
3. Sort the data first in descending order of column H, and then in ascending order of column A
4. Show only records with final mark greater than or equal to 35
5. Shade the cells in column B in yellow if the value is less than 75

Page :3 / 9
Spreadsheet Microsoft Excel 2013

Exercise 2: Showing desired data

Open file [Link]
Apply the skills you have learnt to display the data shown in the figure.

You should follow the requirements below:

Hide columns to show only Name, Month, Area, Product, Quantity, and Amount (2)
Freeze the first row (header row) and the first column (Name) (2)
Filter the rows so that only Month=1 will be shown (2)
Sort the rows. First by Area in ascending order and then by Amount in descending order. (2)
Shade the cells in blue under the Amount column with values greater than 10000 (2)
Center text, bold the headings, add borders, adjust column width. (2)
Submit the file with the filename format ex2_1CNN.xlsx e.g. ex2_1A01.xlsx) (1)

Page :4 / 9
Spreadsheet Microsoft Excel 2013
Chapter 3: Formulae and spreadsheet functions

A formula performs calculations or other actions on the data in your worksheet. A formula always
starts with an equal sign (=), which can be followed by numbers, math operators (such as a plus or
minus sign), and functions, which can really expand the power of a formula.

Activity 3: Understanding Formulae and spreadsheet functions

Open file [Link] .
Follow the instructions in the spreadsheet file.

3.1 Arithmetic operators

Arithmetic operator Meaning Example
+ (plus sign) Addition 3+3
- (minus sign) Subtraction 3-1
Negation -1
* (asterisk) Multiplication 3*3
/ (forward slash) Division 3/3

3.2 Use of parentheses

To change the order of evaluation, enclose in parentheses the part of the formula to be calculated
first.
e.g. The formula =5+2*3 will give 11, and the formula =(5+2)*3 will give 21.

3.3 Relative, Absolute, and Mixed references

The following table summarizes how a reference type updates if a formula containing the
reference is copied two cells down and two cells to the right.
For a formula being copied: If the reference is: It changes to:
$A$1 (absolute column and $A$1 (the reference is absolute)
absolute row)

A$1 (relative column and C$1 (the reference is mixed)

absolute row)
$A1 (absolute column and $A3 (the reference is mixed)
relative row)
A1 (relative column and C3 (the reference is relative)
relative row)

Page :5 / 9
Spreadsheet Microsoft Excel 2013
3.4 Moving/Copying formulae
When you move a formula, the cell references within the formula do not change no matter what
type of cell reference that you use.
When you copy a formula, the cell references may change based on the type of cell reference that
you use.

3.5 Spreadsheet functions

Function Syntax Description Example
SUM(number1,[number2],...) It adds all of its arguments. =SUM(A2,A3)
=SUM(A5,A6, 2)
=SUM(A5:B7)
ROUND(number, num_digits) It rounds a number to a specified =ROUND(2.15, 1)
number of digits. =ROUND(-1.475, 2)
=ROUND(21.5, -1)
AVERAGE(number1, Returns the average (arithmetic =AVERAGE(A2:A6)
[number2], ...) mean) of the arguments. =AVERAGE(3, 5, 7)
MAX(number1, Returns the largest number in a set of =MAX(A2:A6)
[number2], ...) values. =MAX(A2:A6, 10)
MIN(number1, Returns the smallestnumber in a set =MIN(A2:A6)
[number2], ...) of values. =MIN(A2:A6, 10)
RANK(number,ref,) Returns the rank of a number in a list =RANK(A3,A$2:A$6)
of numbers in descending order.
IF(logical_test, value_if_true, Returns one value if a condition is =IF(B2>=50,"Pass","Fail")
[value_if_false]) true and another value if it's false.
There are different comparison
operators:
= equals to <> not equals to
< less than > greater than
>= greater than or equals to
<= less than or equals to
COUNT(value1, [value2], ...) It counts the number of cells that =COUNT(A2:A7)
contain numbers, and counts
numbers within the list of arguments.
COUNTA(value1, [value2], ...) It counts the number of cells that are =COUNTA(A2:A7)
not empty in a range.
COUNTBLANK(range) Counts empty cells in a specified =COUNTBLANK(A2:B5)
range of cells.
COUNTIF(range, criteria) It counts the number of cells that =COUNTIF(A2:A5,"apples")
meet a criterion. =COUNTIF(A2:A5,A4)

Page :6 / 9
Spreadsheet Microsoft Excel 2013

Exercise 3: Prepare a mark sheet for a group of students.

Open file [Link]
Apply the skills you have learnt to display the data shown in the figure below.

You should follow the requirements below:

Add functions to cells H2 to H17 to show the average mark. (2)
Add functions to cells I2 to I17 to show the ranking of the students. (2)
Add functions to cells J2 to J17 to show pass/fail (mark < 50 means fail). (2)
Add functions to cells K2 to K17 to show the number of missing homework. (2)
Add functions to cells H18 & H19 to show the lowest and highest average mark. (1)
Add functions to cells K19 to show the total number of homework missing in this class. (1)
Center align all values in the cells, add appropriate border. (2)
Submit the file with the filename format ex3_1CNN.xlsx e.g. ex A .xlsx

Page :7 / 9
Spreadsheet Microsoft Excel 2013
Chapter 4: Creating Chart

4.1 Chart elements

The chart area.
1. The plot area.
2. The data points of the data series that
are plotted in the chart.
3. The horizontal (category) and vertical
(value) axis along which the data is
plotted in the chart.
4. The legend of the chart.
5. A chart and axis title that you can use in
the chart.
6. A data label that you can use to identify
the details of a data point in a data series.

4.2 Chart types

There are many chart types. Four commonly used chart types are Column chart, Line chart, Pie
chart, and Bar chart.
Column charts use vertical bars to represent data.
Line charts are ideal for showing trends.
Pie charts make it easy to compare proportions.
Bar charts work just like Column charts, but they use horizontal bars instead of vertical bars.

4.3 Modify a chart

You can do one or more of the followings:
a) Change the display of chart axes
You can specify the scale of axes and adjust the interval between the values or categories that
are displayed.
b) Add titles and data labels to a chart
To help clarify the information that appears in your chart, you can add a chart title, axis titles,
and data labels.
c) Add a legend or data table
You can show or hide a legend, change its location, or modify the legend entries. In some
charts, you can also show a data table that displays the legend keys and the values that are
presented in the chart.

Page :8 / 9
Spreadsheet Microsoft Excel 2013
Activity 4: 9-day Weather Forecast
Open the activity 1 spreadsheet file that you have done.
Apply the skills you have learnt to display the chart shown in the figure below.
9-day Weather Forecast
Maximum temperature Minimum temperature
30

25

20

15

10

0
7/3/2015 8/3/2015 9/3/2015 10/3/2015 11/3/2015 12/3/2015 13/3/2015 14/3/2015 15/3/2015
You should:
Use Line chart to show both maximum temperatures and minimum temperatures.
Change the chart title to -day Weather Forecast .
Move the Legend to the top of the chart area.
The vertical axis should be bounded from 0 to 30.
Minor grid lines should be added every 1 degree.

Exercise 4:
Open exercise 3 spreadsheet file that you have completed in the previous chapter.
Create a Column chart to show the average marks of the students.

100 Average marks of a group of 16 students

80

60

40

20

You should follow the requirements below:

Use Column chart (1)
Vertical axis should be bounded from 0 to 100. (1)
Minor grid line should be added every 5 marks. (1)
Title should be Average marks of a group of students . (1)
Submit the file with the filename format ex4_1CNN.xlsx e.g. ex A .xlsx)

Page :9 / 9