Prepared by: Michael E.

Mananghaya
E-mail Address: [email protected]

Central Luzon State University

Science City of Muñoz 3120
Nueva Ecija, Philippines

Instructional Module for the Course

SED 3202 – Technology for Teaching and Learning I

Module 4
Topic 1 Microsoft Office | Spreadsheets (Microsoft Excel)

Overview

In this module, we will focus on Microsoft Excel

terminologies, types of data into a worksheet, how to
format cells and ranges, how to create, save, open and
close worksheets and workbook. Also, we will discuss a
variety of tools that help people accomplish many
personal and professional objectives.

Microsoft Excel is perhaps the most versatile and

widely used of all the Office applications. No matter which
career path you choose, you will likely need to use Excel
to accomplish your professional objectives, some of which
may occur daily. This chapter provides an overview of the
Excel application along with an orientation for accessing
the commands and features of an Excel workbook.
 SED 3202 – Technology for Teaching and Learning I

I. Objectives
Upon completion of this module, you are expected to:
1. Determine and identify different MS Excel terminologies
2. Identify the types of data into a worksheet
3. Format cells and ranges
4. Create worksheets and workbook
5. Save worksheets and workbook
6. Open and close worksheets and workbook

II. Learning Activities

SPREADSHEET TERMINOLOGIES

Commonly used spreadsheet terms:

Microsoft Excel is an electronic spreadsheet use to organize data into rows and
columns and to perform mathematical calculation quickly.
1. Worksheet - A worksheet is a single sheet (page) of rows and columns in
a workbook. A worksheet consists of all of the cells on that single sheet.
2. Workbook – A Workbook is a collection of worksheets which are stored
together in a single file and given a single name. Workbook can contains
link between different worksheets.
3. Rows – A row is the range of cells that go across (horizontal) the
spreadsheets/worksheets. Rows are identified as numbers e.g. Row 1, Row
5
4. Columns – A column is a range of cells that go down (Vertical) in
spreadsheets/worksheets. Columns are identified by letters, for example,
Column A, Column D.
5. Cell – A Cell is an individual box within a worksheet. Every cell on a
worksheet can be identified by its unique cell reference e.g. H7, B14. A cell
is used to hold text, number or formulae.
6. Range – A range is a group of selected cells which can be next to one
another (adjacent) or non-adjacent. Ranges are identified by cell
references e.g. A1:B19 or a name e.g. sales_table.

TYPES OF DATA INTO A WORKSHEET

It’s important to differentiate types of data because Excel treats each
differently. You tell Excel what kind of data is in a cell by how you type it into the
cell or by how you format the cell. Data in Excel can fall into one of four categories.

 Numbers—Numeric data that can be used for calculation purposes.

*mem1s2223 Page 2 of 24
 SED 3202 – Technology for Teaching and Learning I

 Text—Alphabetic or numeric data that is not used for calculation purposes.

Examples of numeric text are phone numbers or Social Security numbers.
 Dates and Times—Although dates and times may be considered
alphanumeric, there are occasions where you might want to perform
calculations on the values, so it is important to identify the data correctly to
Excel.
 Formulas and Functions—It’s important that Excel knows you’re entering
a formula or it will treat what you enter like text.

You can’t combine types of data in a cell. You can type “5 oranges,” but Excel will
see that as text. It won’t separate the “5” as a number and the “oranges” as text.
If you want to deal with the 5 as a number, then you need to enter it into its own
cell.

STARTING MICROSOFT EXCEL 2010

From the Start Menu, <click> on Microsoft Excel. Windows starts
loading the program, and shortly, the Excel program window appears and an Excel
button appears on the taskbar.

*mem1s2223 Page 3 of 24
 SED 3202 – Technology for Teaching and Learning I

OPENING AND CLOSING A WORKSHEET AND WORKBOOK

1. <Click> on the File Menu and Choose OPEN. The OPEN dialogue box
appears. <Click> on example1.xlsx to open the existing Microsoft Excel
file.

EXITING EXCEL
You should close the EXCEL Application and other program if you are
certain that you won’t be using them for some time. This will free up memory for
other programs.
1. <Click> on the File menu and then choose Close.
Tip: To close the entire application, go to file menu and <click> Exit
command. Or simple <click> on the Close button on the upper right
corner of the window.

CREATING A WORKSHEETS AND A WORKBOOK

1. Open the Excel Application.
2. When opening the application, this automatically creates a new workbook
which titled book1.xls. This is now a new workbook. Notice the tab on the
lower left corner of the window, these are the names of the worksheets.
Simply <click> on these tabs to access the worksheets.
3. To create another worksheet, <click> on the new worksheet tab.
4. To create another workbook, <click> on the file menu and choose new
command. New workbook will appears on the left side of the window.
5. <Click> on the blank workbook option for a blank workbook.
FORMAT CELLS AND RANGES
1. Open the Exercise2.xlsx from your Flash drive.
2. Access the sheet1 tab. From the File Menu, <click> Save As, instead
of the Save Option. (Note: The Save As option can also be used to make a
copy of a document by saving it under a new name or to a different location.
By pressing <F12> key also accesses the Save As option. Make a copy of

*mem1s2223 Page 4 of 24
 SED 3202 – Technology for Teaching and Learning I

your worksheet by saving it to your flash drive using the new filename,
Updated Exercise2.xlsx.
3. <click> cell A1 and <click> the down arrow from the Font List.

4. <Click> any font you like from the list. <Click> the down arrow from the
Font Size List and <Click> 16.
5. Select the range A3:E3 and <click> the Bold button from the
Formatting toolbar.
6. Select the range A3:E3 again and <click> the Center button from
the Formatting toolbar.
7. Save your worksheet.
APPLYING NUMBER FORMATS
1. <Click> Cell C4:C18, then Click the Comma Style button from
the formatting toolbar.
2. You can also edit cells through the Format Cells dialog box. Select C4:C18.
3. <Right Click> the cells selected and choose the option Format Cells from
the menu. Format Cells dialog box appears.

*mem1s2223 Page 5 of 24
 SED 3202 – Technology for Teaching and Learning I

4. <Click> the Number tab. From the Category list, choose Number.
<Click> the box next to the option Use 1000 Separator (,). Click OK.

5. Save your worksheet.

SAVING A WORKSHEET/WORKBOOK
1. Full down the File menu and select Save (or you may simple click the save
button from the quick access toolbar). The save as dialogue box appears.
2. Type the name you want to give in the File Name text box. Just like in
other Windows application, remember that the following characters are not
valid characters used in filenames: \? : , * < > |.
3. <Click> Save button or press <Enter> key. Excel automatically adds the
.xlsx extension to the filename.

*mem1s2223 Page 6 of 24
 SED 3202 – Technology for Teaching and Learning I

INSERTING AND DELETING COLUMNS AND ROWS

1. Open Exercise3.xlsx from your flash drive.
2. <Right Click> Row 10 then select insert. This insert a blank row above
and it became row 10.
3. <Right Click> Column C the n select insert. This insert a blank column
and it became column C.

4. To delete certain rows and columns, simply <right click> on a row and
column and choose delete command.

HIDING AND UNHIDING COLUMNS AND ROWS

1. To hid row <Right Click> the Row then select hide. This will hide the
entire selected row.
2. To hide column <Right Click> the Column then select hide. This will hide
the selected column
To unhide certain rows and columns, simply select the two rows or column
between the hidden rows/columns then <right click> on the selected rows
and columns and choose unhide command.
a. Example: Column C is hidden
1. Select/highlight Column B and D.
2. <Right Click> to show sub-menu, then click on the unhide
button. Column C will appear on the worksheet in between
Column B and D.

*mem1s2223 Page 7 of 24
 SED 3202 – Technology for Teaching and Learning I

FREEZE AND UNFREEZE ROWS AND COLUMNS

1. Position the pointer to the desired rows and columns to freeze, then
<click> on View Menu, then select freeze panes then select freeze panes.
This will keep your freeze rows and columns visible while you scroll through
the rest of the sheet.

2. To unfreeze, <click> on any cell then <click> on Unfreeze panes

command from View Menu.

*mem1s2223 Page 8 of 24
 SED 3202 – Technology for Teaching and Learning I

CHANGE THE FONT TYPE/COLOR/SIZE IN A CELL

1. Open Exercise 3.xlsx from your flash drive.
2. Click Cell A1 and <click> the down arrow from the font list and select font
type.

3. <Click> the down arrow from the font size and select size of the font.

*mem1s2223 Page 9 of 24
 SED 3202 – Technology for Teaching and Learning I

4. <Click> font color and choose.

WRAP TEXT
5. Go to cell A4, then select wrap text,
repeat the command at cell B4.

CHANGE THE BACKGROUND COLOR OF A CELL

6. Highlight the range A4:F4, then <click> the down arrow from background
color then select appropriate background color.

CENTER TEXT ACROSS MULTIPLE CELL

7. Highlight the range A1:F1, then <click> Merge and Center
This combine and center the contents of the selected
cells in a new larger cell

BORDER AROUND THE CELL/RANGE

8. Highlight the range A4:F11, then <click> the down arrow from Borders
button then select appropriate border.

*mem1s2223 Page 10 of 24
 SED 3202 – Technology for Teaching and Learning I

TEXT ALIGNMENT
Align Left – Align your content to the
left.
Center – Center your content
Align Right – Align your content to
the right
Top Align – Align text to the top
Middle Align – Centered between the
top and bottom of the cell.
Bottom Align – Align text to the
bottom.
FORMAT PAINTER
Like the look of a particular selection? You can apply that look to other content of
the worksheet.
To Get Started:
1. Select the content with the formatting you like
2. <Click> Format Painter

3. Select something else to automatically apply the formatting

Note: To apply the formatting in multiple places, double – click Format Painter.

ENTERING FORMULAS
To enter formulas in Microsoft Excel, start with equal sign (=).
1. Open Exercise 3.xlsx from your flash drive.
2. At cell C6, enter =A6+B6 and press <Enter>.

*mem1s2223 Page 11 of 24
 SED 3202 – Technology for Teaching and Learning I

3. <Click> on cell C6, and drag the fill handle up to cell C11 then observe the
result.
4. <Click> on cell D6, enter =A6-B6 and press <Enter>.
5. <Click> on cell D6, and drag the fill handle up to cell D11 then observe
the result.
6. <Click> on cell E6, enter =A6*B6 and press <Enter>.
7. <Click> on cell E6, and drag the fill handle up to cell E11 then observe the
result.
8. <Click> on cell F6, enter =A6/B6 and press <Enter>.
9. <Click> on cell F6, and drag the fill handle up to cell F11 then observe the
result.
Mathematical Operators:
Addition - Plus Sign ( + )
Subtraction - Minus Sign ( - )
Multiplication - Asterisk ( * )
Division - Slash ( / )
Exponentiation - Caret ( ^ )

MATHEMATICAL, STATISTICAL AND LOGICAL FUNCTION

SUM FUNCTION – Adds up all the values in a range
Syntax: =SUM(range)
=SUM(A1:A5)
ROUND FUNCTION – Round a number to a specified number of digits
Syntax: =ROUND(number,num_digit)
=ROUND(A1,0) - Whole number
=ROUND(A1,2) - Two decimal Places

AVERAGE FUNCTION – Calculates the average number from a range of values.

Syntax: =AVERAGE(range)
=AVERAGE(A1:A5)
COUNT FUNCTION - Counts all the values in a range
Syntax: =COUNT(range)
=COUNT(A1:A5)
COUNTIF FUNCTION – Counts all the cells in a range that meet specific criteria
Syntax: =COUNIF(range,criteria)
=COUNTIF(A1:A5,10)
RANK FUNCTION – Returns a rank or position of a number within a range of
numbers.
Syntax: =RANK(cell,reference,[order])
=RANK(A1,A1:A5,0) - Rank descending order (H to L)
=RANK(A1,A1La5,1)- Rank ascending order (L to H
(default))

*mem1s2223 Page 12 of 24
 SED 3202 – Technology for Teaching and Learning I

PROPER FUNCTION – Converts a text string to proper case; the first letter in
each word in uppercase, and all other letters to lowercase.
Syntax: = PROPER(text)
=PROPER(A10)
CONCATENATE FUNCTION – Joins several text strings into one text string.
Syntax: =CONCATENATE(text1, text2, text3. . .)
=CONCATENATE(A1,” “,A2,” “,A3)

IF FUNCTION – Test a condition and takes an alternative action depending on a

result.
SYNTAX: =IF(logical_test, value_if_true, value_if_false)
=IF(F5>=75,”Passed”,”Failed)

LOOKUP FUNCTION – looks up a value either from a one-row or one column

range or from any array. Provided for backward compatibility.
SYNTAX: =LOOKUP(lookup_value, lookup_vector, result vector)
Or
=LOOKUP(lookup_value, array)

ABSOLUTE AND RELATIVE REFERENCE

By default Excel uses relative referencing for cell addresses when
you enter a formula. This means that cell references in a formula will change
when you copy the formula to a new location; fill up a range of cells with a
copy of that formula, or when the cell address of that formula changes
because of inserted rows or columns elsewhere in the worksheet.
If a formula contains an absolute cell reference, that cell reference
will not change regardless of where the formula is copied to. The <F4> key is
used to change the reference from relative to absolute. The <F4> key is
actually like a toggle switch. Each time you press the key, the type of reference
changes from relative to absolute or from absolute to relative. If one of a cell
reference’s parts has been turned absolute, it will show a $ sign before it,
indicating that the cell reference part is now absolute.

*mem1s2223 Page 13 of 24
 SED 3202 – Technology for Teaching and Learning I

DATA MANAGEMENT
DATA VALIDATION
1. Launch MS Excel application and open the file Data Validation.xlsx
from your flash drive
2. Create Data Validation Rule
a. Select Cell B8.
b. On the Data tab click Data Validation.

c. On the setting tab:

i. In the Allow list, <Click> Whole Number
ii. In the data list, select between
iii. Enter the minimum and maximum value

d. On the Input Message tab:

1. Check “ Show input message when cell is selected
2. Enter a Title
3. Enter an input message.

*mem1s2223 Page 14 of 24
 SED 3202 – Technology for Teaching and Learning I

e. On the Error Alert tab:

1. Check “Show Error alert after invalid data is entered”.
2. Enter a title.
3. Enter an error message.

4. Click OK.
f. Data Validation Result.
1. Select Cell B8.
2. Try to enter a quiz score higher than 30.
3. Result.

*mem1s2223 Page 15 of 24
 SED 3202 – Technology for Teaching and Learning I

NOTE: To remove data validation from a cell, select the cell, on the Data tab, click
Data Validation and then click Clear All.
PROTECT A WORKSHEET
When you share a file with other users, you may want to protect a
worksheet to help prevent it from being changed.
1. <Right Click> a sheet tab.
2. <Click> Protect Sheet.

3. Enter a password.
4. Check the actions you allow the users of your worksheet to perform.
5. Click Ok

6. Confirm the password and Click OK.

NOTE: To Unprotect sheet, <right click> the Sheet then select the Unprotect
Sheet.

*mem1s2223 Page 16 of 24
 SED 3202 – Technology for Teaching and Learning I

SORT
You can sort your Excel data on one column or multiple columns. You can
sort in ascending or descending order.
ONE COLUMN
To sort on one column, execute the following steps:
1. Click any cell in the column you want to sort

2. To sort in ascending order, <Click> on the Sort and Filters Dropdown

button then select Sort A to Z.

*mem1s2223 Page 17 of 24
 SED 3202 – Technology for Teaching and Learning I

NOTE: To sort in Descending Order, select Sort Z to A.

MULTIPLE COLUMNS
To sort on multiple columns, execute the following steps
1. Highlight the range A8:E22, the <click> on the Sort and Filters
Dropdown button then select custom sort.

2. <Uncheck> My Data has header

3. Select column B from the Sort by
4. Click on Add level
5. Select Column A from Then by.

*mem1s2223 Page 18 of 24
 SED 3202 – Technology for Teaching and Learning I

6. Click OK.

Result: Records are sorted by Section and Student Name.

FILTER DATA
Filter your Excel Data if you want to display records that meet certain
criteria
1. <Click> any single cell inside a data set.
2. <Click> on the Sort and Filters Dropdown button then select Filter.

Arrows in the column header appear.

3. <Click> the arrow next in the Section Column

4. <Click> on Select All to clear all the check boxes.

*mem1s2223 Page 19 of 24
 SED 3202 – Technology for Teaching and Learning I

PREVIEWING WORKSHEETS
1. Launch MS Excel application and open the file Data Validation.xlsx from
your flash drive.
2. <Click> the Print Preview button from the quick access toolbar. This will
display your spreadsheet to be printed.
3. <Click> on the Show Margin Button to view the Margin handles. You can
drag these handles to adjust the margin of your document.
4. <Click> on the Page Setup button to display the Page Setup dialog box.
5. To set margins, type the following margin sizes on their respective text
boxes or <click> on the Increment/Decrement arrows on the right side of
each text box.
Top = 1: Bottom: = 1; Left = 0.75; Right = 0.75
6. <Click> on the OK button to view the changes you have made.
7. <Click> Print Button to print the document. Or the left arrow to return to
normal view.
PUBLISH AS WEB PAGE
8. Go to the File Menu, then <click> on the SAVE AS… option to open the
Save As Dialog box. <Click> on the Save as type and select Web page
(*.htm, *.html) then Click Publish to Open the Publish as Web Page. Check
the Open published web page in browser then <click> PUBLISH. This
will publish your document as web page in your default browser.
Result:

*mem1s2223 Page 20 of 24
 SED 3202 – Technology for Teaching and Learning I

SETTING PRINT AREAS

1. From the View Menu, Point to Workbook Views and <click> Page Break
Preview.
2. Select the following ranges: A1:E22, <Right Click> then select Set Print
Area. Notice that the Blue lines that surround the range of cells you
selected.

3. <Click> the Print Preview button to view the areas to be printed.

4. To remove the print areas you have set, <Right Click> then select Reset
Print Areas.

INSERTING AND REMOVING A PAGE BREAK

1. <Click> on cell A10, then <Right click> and select Insert Page Break.

2. To reset all page break, <Right Click> on cell A10 then select Reset All
Page Breaks.
3. Go to View Menu, then Point to Workbook Views and <click> Normal.

*mem1s2223 Page 21 of 24
 SED 3202 – Technology for Teaching and Learning I

ADD HEADERS AND FOOTERS

1. From Page Layout Menu, <Click> on Page Setup. The Page Setup dialog
box appears on the screen.
2. <Click> the Header/Footer tab.

3. <Click> the Custom Header, the header dialog box appear on your screen.
4. From the left section text box, type your full name.
5. Go to the Center Section text box, and type Microsoft Excel Activity.
6. Go to the Right Section text box, and click the Date button.

7. <Click> OK.
8. <Click> on the Custom Footer button, the footer dialog box appear on
your screen.
9. Go to the Right Section text box, and <click> the Page Number
button.
10. <Click> the Total Pages button.

11. <Click> the Print Preview Button to view the Header and Footer.

*mem1s2223 Page 22 of 24
 SED 3202 – Technology for Teaching and Learning I

III. Assessment

1. Laboratory Activity | Grading System

Prepare a Grading System with the following:

1. Grade Requirements:
- Quiz/Assignment = 25%
- Laboratory Activities = 30%
- Examination = 40%
- Attendance = 5%
2. Inputted data should validate once entered by the user.
3. Protect all formulas
4. Minimum of 25 students
5. Save your file, set password to edit the worksheet
- Password Format: First 3 character of your Lastname+First 2 character of
your Firstname+1400
- Example : Michael Mananghaya = manmi1400.xlsx

2. Laboratory Activity | Tabulation System

Prepare and submit Tabulation System using MS Excel

Requirements:

1.Minimum of 5 judges
2.Set your own Category and Event
3.Minimum of 15 Contestants
4.Compute for the total score and rank of the candidates.
5.Protect all formulas.
6.Save your file, set password to edit the worksheet
- Password Format: First 3 character of your Lastname+First 2 character of
your Firstname+1400
- Example : Michael Mananghaya = manmi1400.xlsx

*mem1s2223 Page 23 of 24
 SED 3202 – Technology for Teaching and Learning I

References

Discovering Computers 2008 by Gary B. Shelly, Thomas J. Cashman and

Misty E. Vermaat, February 21, 2007

O'Leary. (2011). Computing essentials 2011 introductory edition. McGraw

Hill.

Cable, S. and Morrison, C. (2011). Microsoft Office 2010, Advanced.

Course Technology.

Weverka, P. (2010). Office 2010 All-in-one for Dummies. Indiana, USA: Wiley
Publishing, INC.

Tyson, H. (2010). Microsoft 2010 Bible. Indiana, USA: Wiley Publishing, INC.

Online References

https://www.youtube.com/watch?v=DsZ7lsTaAYo&feature=emb_logo

https://openoregon.pressbooks.pub/beginningexcel/chapter/1-1-overview-
of-microsoft-excel/

https://www.guru99.com/introduction-to-microsoft-excel.html

https://blog.hubspot.com/marketing/microsoft-excel

*mem1s2223 Page 24 of 24