ALS Learning Strand 6 Digital Citizenship Module 3: Digital Applications – Spreadsheets

ALS Junior High School Learning Strand 6 module on spreadsheets: Excel basics, formulas and functions, cell formatting, charts, and printing.

This is Module 3, Digital Applications – Spreadsheets, under Learning Strand 6 (Digital Citizenship) of the ALS Accreditation and Equivalency Program for Junior High School. UNESCO and DepEd published it in 2020 for ALS learners who study at their own pace with help from an ALS teacher or learning facilitator. After the module, learners can build, calculate, format, chart, and print a spreadsheet, using Microsoft Excel 2019 as the example program.

On this page 7 sections

What learners will learn

The module has three lessons, each with its own objectives.

Lesson 1 – Understanding Spreadsheets

  • Use the spreadsheet application to generate different documents
  • Create a new spreadsheet from the available templates
  • Save a spreadsheet under another name, as another file type, and to a location on a drive
  • Tell apart the uses of the magnification or zoom tools
  • Explain the use of the different toolbars
  • Use cells in a spreadsheet
  • Modify rows and columns, and customize worksheets

Lesson 2 – Using Formulas and Formatting Cells

  • Follow good practices in using arithmetic formulas and functions
  • Create formulas using cell references and arithmetic operators
  • Identify errors in formulas
  • Use relative and absolute cell referencing in formulas
  • Use spreadsheet functions and logical functions

Lesson 3 – Inserting Charts and Finalizing Spreadsheets

  • Create different types of charts in a spreadsheet
  • Edit charts
  • Prepare spreadsheet outputs for printing

Lessons in this module

  1. Understanding Spreadsheets. Introduces spreadsheet programs (such as OpenOffice Calc, Google Sheets, and Microsoft Excel), their everyday uses (budgets, grades, inventories), and the basic skills: opening Excel, using templates, saving, switching files, zoom, ribbons, entering data, AutoFill, rows and columns, freezing panes, and managing worksheets.
  2. Using Formulas and Formatting Cells. Covers writing formulas, fixing error values, relative and absolute references, common functions, the IF function, number formats, and making cells easier to read.
  3. Inserting Charts and Finalizing Spreadsheets. Shows how to build and edit charts and how to set up, preview, and print a spreadsheet.

Key ideas in Lessons 1 and 2

Parts of a spreadsheet. A workbook is the file that holds spreadsheets. Rows run horizontally and are numbered; columns run vertically and are lettered. A cell is where a row and column meet, and its cell address (for example, C5) identifies it. The active cell has a thick black border, the formula bar shows its contents, and sheet tabs list the worksheets.

Working with data. The module walks through entering numbers, names, and dates, copying (Ctrl+C), cutting (Ctrl+X), and pasting (Ctrl+V). The fill handle in the lower right corner of a cell powers AutoFill: type January and drag to get the months, or type 12 and 24 to continue 36, 48, 60. Freeze Panes keeps headings visible while you scroll.

Formulas. Every formula starts with an equal sign. Typing =A1+A2 in A3 adds the two cells, and the answer updates when the inputs change. The module explains three error values:

ErrorCause
#NAME?Excel does not recognize text in the formula, such as =sume instead of =sum
#DIV/0!A number is divided by zero or by an empty cell
#REF!A cell reference is no longer valid, for example after a row is deleted

Relative and absolute references. A relative reference changes as you copy a formula down. An absolute reference, written with dollar signs (=C2*$E$7), stays fixed. The module shows how copying =C2*E7 gives zeros, and how $E$7 fixes it.

Functions. SUM, AVERAGE, COUNT, COUNTA, ROUND, MAX, and MIN are explained with a quiz-score example, such as =SUM(B2:F2) for a total. The IF function gives remarks: =IF(B2>=75, “Passed”, “Failed”).

Formatting. Learners set decimal places, add the comma style, and apply date, currency (peso sign), and percent formats using a small clothing-store example with discounts. They also change fonts, fill colors, borders, alignment, orientation, wrap text, and merge cells.

Key ideas in Lesson 3

  • Chart types: column charts (vertical bars), bar charts (horizontal bars), line charts (trends over time), and pie charts (parts of a whole).
  • Building a chart: select the data, open the Insert tab, and pick a chart from the Charts group. The example charts first- and second-quarter grades of 15 learners.
  • Editing a chart: change the chart type, title, legend names, colors, and fonts.
  • Finalizing: set margins, paper size, and orientation in Page Layout, use Fit Sheet on One Page, add headers and footers, and check Print Preview (Ctrl+P) before printing to avoid wasting paper.

How to use this module

  • Learners: Answer the Pre-assessment first; if you get every item right, the module says you may skip it. Then work through each lesson in order: Setting the Path, Trying This Out, Understanding What You Did, Sharpening Your Skills, and Treading the Road to Mastery. Write answers on a separate sheet, not in the module. Finish with Don’t Forget (summary), Explore More, and Reach the Top, a 15-item final check.
  • Hands-on tasks: Lesson 2 asks you to build a Monthly Budget Planner and save it as Monthly Budget Planner_[lastname]. Lesson 3 asks you to make a 3-D pie chart of a recommended diet and save it as PieChart_[lastname]. If you have no computer, ask your mobile teacher for help.
  • Checking answers: An answer key and a glossary are at the end. Use the key honestly, after you finish each task.
  • Teachers and facilitators: The module tells learners to consult you when stuck and to return the module once they are done. The budget planner and pie chart tasks work well as practical outputs to review.

Frequently asked questions

Who is this spreadsheet module for?

It is for learners in the ALS Accreditation and Equivalency Program at the Junior High School level. It is Module 3 of Learning Strand 6, Digital Citizenship.

What spreadsheet program does the module use?

It uses Microsoft Excel 2019 for its steps and examples. It notes that other programs, such as Google Sheets and OpenOffice Calc, have almost the same features.

Does the module have an answer key?

Yes. The answer key at the end covers the pre-assessment, the lesson activities, and the Reach the Top final assessment.

What is the difference between a relative and an absolute reference?

A relative reference changes as you copy a formula to other cells. An absolute reference uses dollar signs, like $E$7, so it stays the same when the formula is copied.

What if I do not have a computer?

The module tells learners without a computer to ask their mobile teacher for assistance with the hands-on activities.

Full text of the module

The text below is a transcription of the PDF above, as printed.

LEARNING STRAND 6 DIGITAL CITIZENSHIP

SECONDARY JHS

MODULE 3: DIGITAL APPLICATIONS – SPREADSHEETS

ALS Accreditation and Equivalency Program: Junior High School

[illustration: computer monitor with a Wi-Fi symbol, surrounded by a folder, a globe, cloud icons, a magnifying glass, a shield with a check mark, a document with a check mark, a mobile phone, an email envelope with @, and light bulbs]

LEARNING STRAND 6

DIGITAL APPLICATIONS SPREADSHEETS

[illustration: open book beside a computer monitor with a cursor]

DIGITAL CITIZENSHIP MODULE 3

ALS Accreditation and Equivalency Program: Junior High School Learning Strand 6: Digital Citizenship Module 3: Digital Applications – Spreadsheets

Published in 2020 by the United Nations Educational, Scientific and Cultural Organization UNESCO Office, Jakarta Jalan Galuh II No. 5, Kebayoran Baru, Jakarta, Indonesia

and

Department of Education DepEd Complex, Meralco Avenue, Pasig City, Philippines

Copyright © UNESCO and DepEd 2020

This publication is available in Open Access under the Attribution-Share Alike 3.0 IGO (CC-BY-SA) 3.0 IGO) license (http://creativecommons.org/licenses/by-sa/3.0/igo/). By using the content of this publication, the users accept to be bound by the terms of use of the UNESCO Open Access Repository (http://www.unesco.org/open-access/terms-use-ccbysa-en).

The designations employed and the presentation of material throughout this publication do not imply the expression of any opinion whatsoever on the part of UNESCO concerning the legal status of any country, territory, city or area or of its authorities, or concerning the delimitation of its frontiers or boundaries.

The selection and presentation of the material contained in this publication, as well as the opinions expressed herein are the sole responsibility of the authors and not necessarily those of UNESCO, nor do they commit the organization in any way.

This educational resource material was developed and printed through the project “Better Life for Out-of-School Girls to Fight Against Poverty and Injustice in the Philippines” with financial support from Korea International Cooperation Agency (KOICA).

Printed by APC Printers Corporation Printed in Makati City, Philippines

ISBN 888-888-8888-88-8

DEVELOPMENT TEAM

NameRole
Jenelyn Marasigan BaylonMaster Teacher I, ALS Task Force (On-detail)
Kristine Lee S. LumanogEducation Program Specialist II, ALS Task Force (On-detail)
Judy R. MendozaProject Development Officer III, Bureau of Learning Resources
Reyangie V. SandovalEducation Program Specialist II, Bureau of Learning Resources
Josephine C. IntinoSenior Education Program Specialist, Bureau of Curriculum Development
Eric U. LabreSenior Education Program Specialist, Bureau of Learning Resources
Roderick P. CorpuzSupervising Education Program Specialist, ALS Task Force
Daisy Asuncion O. SantosChief Education Program Specialist, Bureau of Learning Resources
Marilette R. AlmaydaDirector III/Head, ALS Task Force
Ariz Delson Acay D. CawilanOfficer-In-Charge, Office of the Director IV, Bureau of Learning Resources
G. H. S. AmbatAssistant Secretary for Alternative Learning System Program and Task Force
Tonisito M. C. UmaliUndersecretary for Legislative Liaison Office, External Partnership Service and Project Management Service
Leonor Magtolis BrionesSecretary
NameRole
Heiden ChanAuthor
Adelina CalubContent Expert
Bernadette SisonAdmin and Finance Staff
Mildred ParboProject Lead
Ma. Teresita MedadoPresident

Content and Language Evaluators and Instructional Design Reviewer

NameRole
Edward C. JimenezSchools Division Office of Meycauayan City, Department of Education
Melvin LazaroRegional Office III – Central Luzon, Department of Education
Ma. Jessamine Anne R. VerzosaFreelance Language Editor
NameRole
Ade SandraAdmin and Finance Assistant
Rusyda DjamhurProject Assistant
Marmon Abutas PagunsanNational Project Consultant
Remegio AlquitranNational Project Officer
Maria Karisma Bea AgaraoNational Programme Coordinator
Mee Young ChoiHead of Education Unit
Shahbaz KhanDirector and Representative

User’s Guide

For the ALS Learner:

Welcome to this Module entitled Digital Applications – Spreadsheets under Learning Strand 6 Digital Citizenship of the ALS K to 12 Basic Education (BEC).

This module was designed to provide you with fun and meaningful opportunities for guided and independent learning at your own pace and time. You will be enabled to process the contents of the learning resource while being an active learner.

This module has the following parts and corresponding icons:

PartDescription
Let’s Get to KnowThis will give you an idea of the skills or competencies you are expected to learn in the module.
Pre-assessmentThis part includes an activity that aims to check what you already know about the lesson. If you get all the answers correct (100%), you may decide to skip this module.
Setting the PathThis section provides a brief discussion of the lesson. This aims to help you discover and understand new concepts and skills.
Trying This OutThis comprises activities for independent practice to solidify your understanding and skills of the topic. You may check the answers to the exercises using the Answer Key at the end of the module.
Understanding What You DidThis includes questions that process what you learned from the lesson.
Sharpening Your SkillsThis section provides an activity that will help you transfer your new knowledge or skill in real-life situations or concerns.
Treading the Road to MasteryThis is a task which aims to evaluate your level of mastery in achieving the given learning competency.
Don’t ForgetThis part serves as a summary of the lessons in the module.
Explore MoreIn this portion, another activity will be given to you to enrich your knowledge or skill of the lesson learned. This also tends retention of learned concepts.
Reach the TopThis part will assess your level of mastery in achieving the learning competencies in each lesson in the module.
Answer KeyThis contains answers to all activities in the module.
GlossaryThis portion gives information about the meanings of the specialized words used in the module.

At the end of this module you will also find:

PartDescription
ReferencesThis is a list of all sources used in developing this module.

The following are some reminders in using this module:

  1. Use the module with care. Do not put unnecessary mark/s on any part of the module. Use a separate sheet of paper in answering the exercises.
  2. Don’t forget to answer the Pre-assessment before moving on to the other activities included in the module.
  3. Read the instruction carefully before doing each task.
  4. Observe honesty and integrity in doing the tasks and checking your answers.
  5. Finish the task at hand before proceeding to the next.
  6. Return this module to your ALS Teacher/Instructional Manager/Learning Facilitator once you are through with it.

If you encounter any difficulty in answering the tasks in this module, do not hesitate to consult your ALS Teacher/Instructional Manager/Learning Facilitator. Always bear in mind that you are not alone.

We hope that through this material, you will experience meaningful learning and gain deep understanding of the relevant competencies. You can do it!

Contents

[illustration: vertical “CONTENTS” label]

SectionPage
Let’s Get to Know1
Pre-Assessment2
LESSON 1: Understanding Spreadsheets3
Setting the Path3
Trying This Out5
Understanding What You Did6
Sharpening Your Skills51
Treading the Road to Mastery53
LESSON 2: Using Formulas and Formatting Cells54
Setting the Path54
Trying This Out55
Understanding What You Did56
Sharpening Your Skills98
Treading the Road to Mastery99
LESSON 3: Inserting Charts and Finalizing Spreadsheets103
Setting the Path103
Trying This Out104
Understanding What You Did105
Sharpening Your Skills125

I can’t transcribe pages 9 to 16 because they aren’t in this message. The only page images attached are pages 1 to 8 of the PDF, and those were already transcribed separately. Nothing I write for pages 9 to 16 would come from the document, so I’m not producing any output for them.

Please re-send the page images for pages 9 to 16. I’ll transcribe them with the page markers <!-- page 9 --> through <!-- page 16 -->, following the same structure and rules as before.

Lesson 1

ICONSSPREADSHEET PROGRAMOPERATING SYSTEM
[illustration: calculator-style icon]Quattro ProWindows
[illustration: bar-chart icon]iWork NumbersApple MacOS
[illustration: OpenOffice Calc icon]OpenOffice CalcAny
[illustration: Google Sheets icon]Google SheetsAny
USES OF SPREADSHEET

Spreadsheets are mostly used in accounting and recording data, such as preparing budgets, recording students’ grades, preparing financial statements, analyzing numbers, managing inventory, or making forecasts. Spreadsheets can be used at home, in school, in work, or in business.

GETTING FAMILIAR WITH THE SPREADSHEET

One of the most used spreadsheet programs is Microsoft Excel (MS Excel) from the Microsoft Office productivity tools. Though spreadsheets vary, their features are almost the same.

This module will use Microsoft Excel 2019 in getting familiar with spreadsheet applications.

PARTS OF A SPREADSHEET

Let us get to know the parts of Microsoft Excel before creating spreadsheets. To open Microsoft Excel, look for the Excel logo in your desktop or search using the search bar and then double-click. Spreadsheets are contained in a file called workbook. In Microsoft Excel, Book with a number beside it (in this case “Book1”) refers to the spreadsheet’s file name. The basic parts of the Microsoft Excel are row, column, cell, active cell, cell address, and formula bar.

[illustration: Microsoft Excel worksheet window labeled Column Header, Rows, Row Header, Columns, and Sheet Tab; the Sheet Tab label points to the tab named Sheet1]

[illustration: Excel ribbon and formula area labeled Cell Address and Formula Bar; the label Cells/ Active when selected points to the active cell]

PARTS OF A SPREADSHEET

PARTS OF A SPREADSHEET

Rows. The horizontally aligned cells in a spreadsheet, identified with numbers (row header).

Columns. The vertically aligned cells in a spreadsheet, identified with letters (column header).

Cell. An individual intersection between rows and columns, labeled by the row number and column letter.

Active cell. The cell currently being edited, marked by a thick black border around the cell.

Cell address. Also called cell reference, an alphanumeric value used to identify a specific cell.

Formula bar. Shows the contents of the active cell and allows you to create and view formulas.

Sheet tabs. Contains the names of the available worksheets in the current workbook.

OPENING AND CLOSING A SPREADSHEET APPLICATION
  1. To launch or open a spreadsheet application, double-click the spreadsheet icon in the desktop if it is available. For example, you will see the Excel icon is in the desktop.

[photo: Windows desktop with the Excel icon highlighted by a circle and an arrow]

  1. You may also use the search bar on the taskbar. Type the name of the spreadsheet application you are looking for. For example, type Excel; if there is a result, click it to launch.


  2. To open a new workbook, click New on the right side of the screen then select Blank workbook.


  3. To close a spreadsheet application, click the Close button on the upper right corner of the screen.


[illustration: Windows search results for “Excel” with the Excel app listed as best match]

[illustration: Excel start screen with the New option highlighted and Blank workbook selected]

[illustration: Excel workbook window with the Close (X) button at the upper right corner highlighted by a cursor]

CREATING A NEW SPREADSHEET BASED ON THE AVAILABLE TEMPLATES

Microsoft Excel allows users to use available templates with formatting and predefined formulas. You can browse different templates, depending on the type of spreadsheet you want to create, such as record lists, budgets, calendars, schedules, inventory lists, and plans.

You can also click on the suggested searches to display more templates or search online templates if you are connected to the internet; just type the word related to the template you are looking for.

[illustration: Excel New screen showing the Blank workbook option and tutorial templates such as Take a tour, Create a Drop-down list, Get started with Formulas, and Make your first PivotTable]

  1. Click Personal in the suggested searches.

[illustration: Search for online templates bar with Suggested searches: Business, Personal (highlighted), Planners and Trackers, Lists, Budgets, Charts, Calendars]

  1. Select Personal Monthly Budget. Once it is selected, a window will appear with details about the template. Click Create.

[illustration: Excel New screen showing template results with Personal Monthly Budget highlighted]

  1. Once Excel loads the selected template, you will see two sheets: Start and Personal Monthly Budget. Click the Personal Monthly Budget sheet.


  2. To edit, click the cells and change to your desired contents.


[illustration: Personal Monthly Budget template details window with the Create button highlighted]

[illustration: Excel window showing the “About this Template” sheet with the sheet tabs Start and Personal Monthly Budget]

Loaded Personal Monthly Budget Template

[illustration: Excel screenshot of the Personal Monthly Budget worksheet, with Projected and Actual Monthly Income, Projected and Actual Balance, and Housing and Entertainment tables]

Personal Monthly Budget Sheet

  1. Templates can still be modified according to the preferred data.
  2. If you do not like the templates, you can always go back and use a blank workbook.

[illustration: Excel “New” screen showing the Blank workbook and Personal Monthly Budget templates]

[illustration: Excel blank workbook window named Book1 – Excel, with an empty grid of columns A to W and rows 1 to 27]

Blank Workbook

SAVING A WORKBOOK

  1. To save a new workbook, press Ctrl+S on the keyboard. You can also click the Save button [icon] on the upper left corner of the workbook, or click the File tab then select Save.
  2. A dialog box will appear, asking for a File Name and Choose a Location where the file will be stored. If you are going to locate a folder, click the drop-down arrow on Choose a Location or click More save options to look for other locations. Enter your desired file name and press Save.

Let us save the file as My First Workbook.

[illustration: “Save this file” dialog box with File Name “My First Workbook” (.xlsx), Choose a Location set to Documents, a “More save options” link, and Save and Cancel buttons]

  1. Notice that the file name on the upper left corner has changed from “Book1” to “My First Workbook.”

[illustration: Excel title bar showing My First Workbook.xlsx with Saved status]

Spreadsheets in Microsoft Excel are usually saved in the Documents folder as XLSX.

  1. You can also save the file in a different location, with a different file name, and file type. Just click on the File tab then select Save As.

[illustration: Excel “Save As” screen listing Recent, OneDrive – Personal, This PC, Add a Place, and Browse, with the Documents folder contents]

  1. To save under a different file name, delete the current filename and type a new one. You can also save it as a different file format, just click the drop-down arrow on the file type and select the desired file type.

It can be saved as a template, Portable Document Format (PDF), web page, rich text format, or any file type you want to use.

[illustration: drop-down list of file types in the Excel Save As window]

Available file types in the list, in printed order:

  • Excel Workbook (*.xlsx)
  • Excel Macro-Enabled Workbook (*.xlsm)
  • Excel Binary Workbook (*.xlsb)
  • Excel 97-2003 Workbook (*.xls)
  • CSV UTF-8 (Comma delimited) (*.csv)
  • XML Data (*.xml)
  • Single File Web Page (*.mht, *.mhtml)
  • Web Page (*.htm, *.html)
  • Excel Template (*.xltx)
  • Excel Macro-Enabled Template (*.xltm)
  • Excel 97-2003 Template (*.xlt)
  • Text (Tab delimited) (*.txt)
  • Unicode Text (*.txt)
  • XML Spreadsheet 2003 (*.xml)
  • Microsoft Excel 5.0/95 Workbook (*.xls)
  • CSV (Comma delimited) (*.csv)
  • Formatted Text (Space delimited) (*.prn)
  • Text (Macintosh) (*.txt)
  • Text (MS-DOS) (*.txt)
  • CSV (Macintosh) (*.csv)
  • CSV (MS-DOS) (*.csv)
  • DIF (Data Interchange Format) (*.dif)
  • SYLK (Symbolic Link) (*.slk)
  • Excel Add-in (*.xlam)
  • Excel 97-2003 Add-in (*.xla)
  • PDF (*.pdf)
  • XPS Document (*.xps)

Different Available File Types

  1. Click Browse to choose the folder or drive where you want to save the file. A window will appear where you can locate your folder or drive. Choose a location then click Save to finish the process.

[illustration: Excel “Save As” screen with the Browse option highlighted under Other locations, and a Windows Save As window showing the Documents folder with File name “Workbook Version 1” and Save as type “Excel Workbook (.xlsx)”]*

SWITCHING BETWEEN OPEN SPREADSHEETS

There are several ways to switch between files.

USING THE SWITCH WINDOWS BUTTON
  1. One option to switch between the open Excel files is to use the View tab on the ribbon. Go to the View tab and click on Switch Windows.

[illustration: Excel ribbon with the View tab selected, showing the Window group and the Switch Windows button]

  1. Pick the workbook you would like to move from the list of available open files. There will be a check next to the file that you are currently viewing for easy reference. In the example below, there are two open spreadsheets: Book 1 and Book 2. Choose among the open spreadsheets you want to switch with by clicking it.

[illustration: Switch Windows drop-down menu in the Window group]

  • ✓ 1 Book2
  • 2 Book1
USING KEYBOARD SHORTCUTS

To move back and forth between any open windows (of all file types and browsers), use the combination Alt+Tab. Hold Alt and press Tab to browse through the files until you get to the file you are looking for.

CLICKING THROUGH THE TASKBAR

Click the icon of the spreadsheet application on the taskbar. A small window will appear. Then, select the file you want to open.

[illustration: Windows Alt+Tab task switcher showing two open windows, Book1 – Excel and Book2 – Excel]

USING AVAILABLE HELP FUNCTIONS

The help feature in Microsoft Office applications is usually the fastest and easiest way to get help. In Windows, access it by pressing F1 in the application or just click the Help tab.

[illustration: Excel Help tab ribbon group with Help, Contact Support, Feedback, Show Training, What’s New, Community, Excel Blog, and Suggest a Feature buttons]

Once the help feature opens, use the search feature in the right-side task pane to find answers to your questions related to the use of spreadsheet programs. To browse for topics, open Recommended Topics.

[illustration: Excel Help task pane on the right side of the window, showing Recommended Topics]

ZOOM TOOLS

Zoom tools are available in most applications such as Microsoft Word, Microsoft Excel, and Microsoft PowerPoint. Zoom tools let you zoom in or out your documents, workbooks, or presentations while working on them. You can find the zoom tools under the View tab of the application.

[illustration: View tab Zoom group in Excel, showing Zoom, 100%, and Zoom to Selection buttons]

View Tab Zoom Group in Excel

[illustration: Zoom dialog box with Magnification options 200%, 100%, 75%, 50%, 25%, Fit selection, and Custom set to 400%]

  1. The default view is 100%, but you can adjust this by clicking Zoom. A window with different levels of magnification to choose from will pop out, including Fit Selection and Custom.
  2. Zoom to Selection will allow you to zoom the document up to 400%, which is the maximum magnification. This helps you focus on a specific area of the sheet.

[illustration: Excel window in Zoom to Selection view, showing columns A to F and rows 23 to 28]

Zoom to Selection View

  1. Alternatively, the zoom slider is available for instant zoom in and zoom out. It is located at the lower right corner of the workbook.

[illustration: lower right corner of the workbook showing the zoom slider set at 100%]

Zoom Slider

USING RIBBONS, TABS, AND THE QUICK ACCESS TOOLBAR

Ribbons are designed to help you quickly find the command that you want to execute in Microsoft Office. Ribbons are divided into logical groups called tabs, and each tab has its own set of groups with unique functions to perform. There are various tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View.

Ribbons can be hidden when you collapse or minimize ribbons. Right-click on the ribbon area and choose Collapse the Ribbon.

[illustration: Excel ribbon with the right-click menu open, showing the Collapse the Ribbon option selected]

When you hide the ribbon, only the tabs will be displayed. Ribbons will only appear if you select a tab.

[illustration: Excel window with the ribbon collapsed, showing only the tab names File, Home, Insert, Page Layout, Formulas, Data, Review, View, Help, and Search]

To restore the Ribbons, click the Ribbon Display Options at the upper right of the workbook then select Show Tabs and Commands.

The Quick Access Toolbar is a universal toolbar that is always visible and is not dependent on the tab that you are working with. It lets you execute commands easily.

[illustration: Ribbon Display Options menu with Auto-hide Ribbon, Show Tabs, and Show Tabs and Commands choices]

Ribbon Display Options

[illustration: Quick Access Toolbar showing AutoSave, Save, Undo, Redo, and Print buttons above the File, Home, Insert, Page Layout, and Formulas tabs]

Quick Access Toolbar

[illustration: Customize Quick Access Toolbar drop-down menu with checked commands such as Automatically Save, Save, Quick Print, Print Preview and Print, Undo, and Redo]

The default commands are the save, undo, and redo buttons. You can customize the toolbar by clicking on the drop-down button at the right of the toolbar and then checking the desired commands to be included on the Quick Access Toolbar.

USING SPREADSHEET CELLS

As aforementioned, a cell is made up of a row and a column. Each cell should only contain one element of data. If you are ready to insert data or content, click a cell.

  1. Click the cell to insert data such as numbers, dates, and texts. Let us put data in cells A1 to A5.

[illustration: spreadsheet with the numbers 1, 2, 3, 4, and 5 entered in cells A1 to A5, with cell A5 selected]

To organize your workbook, starting at the left corner of the sheet is recommended.

  1. Let us put names as text beside each number in cells B1 to B5.

[illustration: spreadsheet with names Anna, Carmen, Robert, Jose, and Melody entered beside the numbers in cells B1 to B5]

  1. Then, insert birthdates on C1 to C5 in mm/dd/yyyy format.

[illustration: spreadsheet with the date 12/04/1992 in cell C1, shown in the formula bar]

Notice that the inserted date can be seen in the formula bar.

  1. If the data is longer than the width, put your cursor on the right side of the header and drag to adjust the column headers. Use the same process to adjust the rows.

[illustration: spreadsheet with the widened column C showing the full date 12/04/1992 in cell C1]

CREATING LISTS

When creating lists of data in a spreadsheet, remember the basics to for better results. Examine the sample illustrations below.

  1. Avoid blank rows and columns in the main body of the list. It may look unpleasing to the eye, and the data may look disorganized.

[illustration: spreadsheet list of Items and Price, with blank rows marked by X symbols between the entries]

  1. If your list needs to have a total row like the example on the next page, it is best to put a blank row before the total row to separate the final data from the main list.
  2. Ensure that the cell bordering lists are blank. It is better not to put borders to main lists to avoid confusion, especially if you are going to insert rows between data in the future. Apply No Border found on the Font group to remove the cell borders.

[illustration: Excel worksheet with Items and Price columns, Total row at row 8, cell G12 selected]

Step 2. Insert a Blank Row Before the Total Row

[illustration: Excel Home tab Borders menu open, showing Bottom Border, Top Border, Left Border, Right Border, No Border, All Borders, Outside Borders, Thick Outside Borders, Bottom Double Border and Thick Bottom Border options]

Step 3. Ensure Cell Bordering Lists are Blank.

cells, click each cell while holding the Ctrl key on the keyboard.

SELECTING CELLS AND WORKSHEETS
  1. To select a single cell, click the cell using your mouse or use the arrows keys on the keyboard.
  2. To select a range of cells, hold the left button of your mouse and drag from the start of the range up to the end. You may also use the arrow keys while holding the Shift key in the keyboard.

[illustration: Excel worksheet with a range of cells A1 to C5 selected, showing numbers 1 to 5, names Anna to Melody and dates]

  1. To select a nonadjacent cell or cells, click each cell while holding the Ctrl key on the keyboard.

[illustration: Excel worksheet with nonadjacent cells selected in columns A, B and C, rows 1 to 5]

Selecting Nonadjacent Cells

  1. If you want to select the entire worksheet, click the triangle at the corner of A1.

[illustration: Excel worksheet with the entire worksheet selected, showing names and dates in columns A to C]

Selecting the Entire Worksheet

COPYING AND MOVING CELL CONTENTS
  1. To copy cell content, follow the steps we previously discussed on selecting cells then click Copy in the Clipboard group on the Home tab, or press Ctrl+C on your keyboard. You can also right-click on the selected cells and choose Copy.

[illustration: Excel Home tab Clipboard group with the Copy command highlighted, and a right-click context menu showing Cut and Copy]

  1. Select the cell or cells where you want to paste the content then click the Paste command on the Home tab, or press Ctrl+V on your keyboard. Alternatively, right-click on a blank cell, choose Paste Options, and click Paste.

[illustration: Excel Home tab with the Paste command highlighted and a right-click menu showing Paste Options]

Pasting content

[illustration: Excel worksheet showing the copied names and dates pasted into columns F and G beside the original data]

Successful paste

  1. To move contents in a cell, select the cell or cells to be moved then click Cut on the Home tab. You may also press Ctrl+X on the keyboard, or right-click on the selected cells and choose Cut. The Cut command will remove the contents from its origin and transfer them to your chosen cell.
  2. Select the cells where you want to move your content then click Paste.
  3. You can also copy or move cell contents from one sheet to another or between open workbooks. Do the same procedure in copying contents in a worksheet.

[illustration: Excel Home tab with the Cut command highlighted and a right-click menu showing Cut, Copy and Paste Options]

Step 3. Moving Contents in a Cell (Cutting Contents)

[illustration: Excel worksheet with the cut contents moved to columns E to G, leaving columns A to D empty]

Step 4. Successful Paste

[illustration: Excel workbook with Sheet2 selected and the names and dates copied into it]

Step 5. Copying or Moving Cell contents from One Sheet to Another or Between Open Workbooks

  1. To remove or delete cell contents, select the cells to be deleted then press Del on the keyboard.
USING THE AUTOFILL TOOL

Instead of entering data manually, Microsoft Excel has a feature called AutoFill that lets you fill cells automatically. It may either follow a pattern or base on data from other cells.

  1. To use AutoFill, let us enter a text in a cell. In this example, let us type a month, say, January.

[illustration: Excel worksheet with the word January typed in cell B2]

  1. Point your mouse on the lower right corner of the cell. You should see a solid cross pointer; this is called the fill handle.

[illustration: Excel cell B2 containing January with the fill handle pointer shown at its lower right corner]

  1. Hold the mouse’s left button and drag down the pointer. You will be guided by a tool tip indicating the next content.

[illustration: Excel column of cells filled downward from January, with a tool tip showing December]

  1. AutoFill Options will appear after your use the fill handle. When you click the button, the options for AutoFill will be displayed.

[illustration: AutoFill Options menu with the options Copy Cells, Fill Series (selected), Fill Formatting Only, Fill Without Formatting, Fill Months and Flash Fill]

AutoFill Options

  1. The default selection is Fill Series. Copy Cells will not follow a pattern; it will only copy the selected word.

If you want to copy the format of the cell but not the content, choose Fill Formatting Only. If you want to copy the content of the cell but not the format, choose Fill Without Formatting.

Autofill can also be done horizontally; just follow the same steps, but this time, drag your mouse to the right.

[illustration: Excel column filled with January, with the AutoFill Options menu showing Copy Cells selected]

Copy Cells AutoFill Option

[illustration: Excel column with Monday in the first cell and the AutoFill Options menu showing Fill Formatting Only selected]

Fill Formatting Only

[illustration: Excel column filled with the days of the week from Monday to Sunday, with the AutoFill Options menu showing Fill Without Formatting selected]

Fill Without Formatting

AutoFill can add incremental data entries if the data is numerical or alphanumeric. Enter any starting value in a cell. Enter the next value in the cell below it to establish a pattern. Select those two cells and drag the bottom fill handle down the column to create a series of incremental numbers.

[illustration: AutoFill steps in column A: cells 1 and 2 hold 1 and 2 and are selected, then the fill handle extends the series to 1 through 7; a second example shows 12 and 24 extended to 12, 24, 36, 48, 60]

For example, entering 12 and 24 in cells A1 and A2 would create the series 12, 24, 36, 48, 60 when copied down to cell A5.

For alphanumeric, the fill handle will create incremental data. Enter a starting value in any cell and use the fill handle vertically or horizontally.

ABCDE
1Sample1Sample2Sample3Sample4Sample5
2Sample2
3Sample3
4Sample4
5Sample5
SELECTING ROWS AND COLUMNS
  1. To choose a row or a column, click the row header (number) or column header (letter).
  2. To click more than one row or column, click and hold the headers, hold the left button, and drag the mouse vertically or horizontally.

[illustration: spreadsheet showing rows 1 to 5 selected by dragging down the row headers, with the Sample1 to Sample5 data in row 1 and Sample2 to Sample5 in column A]

[illustration: spreadsheet showing columns A to E selected by dragging across the column headers]

  1. To select nonadjacent rows and columns, click the headers of the desired rows and columns while pressing the Ctrl key on the keyboard.

[illustration: spreadsheet with nonadjacent columns B and D and rows 2 and 4 selected]

ADJUSTING ROW HEIGHT AND COLUMN WIDTH
  1. To adjust the row height, select the row headers to be adjusted and then point your mouse to the last selected row header. Double-click or resize it manually by holding the left button while dragging down the headers.

[illustration: spreadsheet before and after row heights are adjusted for rows 1 to 5]

  1. To adjust the column width, select the column headers to be adjusted and then point your mouse to the last selected column header. Double-click or resize it manually by holding the left click while dragging the headers to the left or to the right.

[illustration: spreadsheet columns A to E being resized by dragging the column header border]

SCREENSHOT 07

INSERTING AND DELETING ROWS AND COLUMNS
  1. To insert a row in between, right-click on the row header and click Insert.

[illustration: right-click context menu on row 2 of the spreadsheet with the Insert option highlighted]

  1. To insert more than one row, select the desired number of row headers and click Insert. The selected rows correspond to the number of new rows.

[illustration: spreadsheet with rows 2 and 3 selected, and the result showing two blank rows inserted above Sample2 to Sample5]

  1. To insert a column in between, right-click the column header and click Insert.

[illustration: right-click context menu on column C with the Insert option highlighted, and the result showing a blank column inserted between columns A and C]

  1. To insert more than one column, select the desired number of column headers then click Insert. The selected columns correspond to the number of new columns.

[illustration: spreadsheet with columns B and C selected, and the result showing two blank columns inserted]

  1. To delete cells, do the same procedure in inserting rows or columns, but instead of Insert, click Delete.

[illustration: right-click context menu on column C with the Delete option highlighted]

FREEZING AND UNFREEZING PANES
  1. To keep an area of a worksheet visible while you scroll to another area of the worksheet, go to the View tab and click Freeze Panes to lock specific rows and columns in place.

[illustration: View tab of the spreadsheet ribbon with the Freeze Panes menu open, showing the options Freeze Panes, Freeze Top Row, and Freeze First Column]

  1. To freeze the first column, click Freeze First Column; to freeze the top row, click Freeze Top Row. You will see a faint line between the first column and the second column. It indicates that the first column was locked or frozen.


  2. Aside from the first column, you can also freeze two or more columns and rows. Example if you want to freeze the first and second column, you need to choose the third column and then click Freeze Panes on the View tab.


[illustration: spreadsheet with column A to be frozen and the Month list in rows 1 to 13, and a second view showing rows 1 and 2 frozen at the top]

  1. The same procedure will be applied for the rows. Freezing panes is very helpful if you are working with big or long worksheets. The effect will be seen upon scrolling from left to right or from up to down.

[illustration: spreadsheet with columns A and B frozen, showing the Month and Day headings]

  1. To unfreeze the panes, simply click Unfreeze Panes.

[illustration: Freeze Panes menu with the Unfreeze Panes option highlighted]

Lesson 1

Customizing Worksheets

You can only see one worksheet upon opening a workbook, and its default name is “Sheet1.” You can add more sheets, and each sheet added will have their names change incrementally.

[illustration: spreadsheet grid with a bar chart, with a dashed arrow pointing to a sheet tab bar showing “Sheet 1” and a plus sign button]

Adding Worksheets
  1. To add another sheet, click the plus sign button beside Sheet1.

[illustration: tab bar showing “Sheet1” with the plus sign button selected]

  1. A new sheet will appear with Sheet2 as the name. You can continue doing this if you need more worksheets.

[illustration: tab bar showing “Sheet1” and “Sheet2”]

Renaming a Worksheet
  1. To rename a worksheet, right-click on the worksheet to be renamed then select Rename. Type a meaningful worksheet name that is related to your task. Let us rename our worksheet to “Sample 1” and “Sample 2.”

[illustration: right-click context menu with “Rename” selected]

  1. Do the same procedure to all the worksheets that you will add.

[illustration: tab bar showing “Sample 1” and “Sample 2”]

  1. To switch between worksheets, just click the sheet that you want to work on.
Copying, Moving, and Deleting Worksheets
  1. To copy a worksheet, right-click the worksheet to be copied then click Move or Copy…

[illustration: right-click context menu with “Move or Copy…” selected]

  1. A dialog box will appear with a list of existing worksheets. Check Create a copy.

[illustration: “Move or Copy” dialog box with “Create a copy” checked]

  1. A duplicate worksheet will appear with the same name but with “(2)” because Excel does not accept worksheets with the same names. You can also change it to your desired name.

[illustration: tab bar showing “Sample 1 (2)” next to “Sample”]

[illustration: tab bar showing “Sample 3” and “Sample” with a return arrow]

  1. To move worksheets, right-click the sheet to be moved then select Move or Copy and choose (move to end). Click OK.

[illustration: “Move or Copy” dialog box with “(move to end)” selected]

Here, Sample 3 was moved to the end after all the existing sheets. Moving can also be as easy as dragging the worksheet to your desired place.

  1. To delete a worksheet, click the worksheet to be deleted, right-click, and then choose Delete.

[illustration: right-click context menu with “Delete” selected, shown over tabs “Sample 1”, “Sample 2”, and “Sample 3”]

Microsoft Excel has a Graphical User Interface (GUI) that facilitates easy navigation among cells and worksheets.

Lesson 1

Sharpening Your Skills

I. Directions: Identify the parts of Microsoft Excel. Write your answers on a separate sheet of paper.

[illustration: Microsoft Excel window with callouts numbered 1 to 10 pointing to its parts; the callout labels are not printed]

Lesson 1

Sharpening Your Skills

II. Directions: List down the steps of the following commands in the spreadsheet program. Write your answer on a separate piece of paper.

Selecting Rows and Columns




Inserting and Deleting Rows and Columns






Adding a Worksheet



Lesson 1

Treading the Road to Mastery

Directions: Give the steps on the following task. Write your answer on a separate sheet of paper.

[chart: flowchart; Treading the Road to Mastery]

What do you do if

  • you want to enter data in a cell?
  • you want the entire text displayed but it is too long to fit inside the cell?
  • you want to add a sheet and rename it?

The three answer boxes below these questions are blank.

Spreadsheet programs such as Microsoft Excel have features that will help you use the program efficiently. But their true power is to handle simple and complex mathematical calculations. The next lesson will focus on the use of formulas in spreadsheets.

Lesson 2

Setting the Path
Using Formulas and Formatting Cells

After this lesson, learners should be able to

  • employ good practices in using arithmetic formulas and functions in spreadsheets;
  • create formulas using cell references and arithmetic operators;
  • identify errors in formulas;
  • use relative and absolute cell referencing in formulas;
  • employ spreadsheet functions; and
  • use logical functions.

LESSON 2

TRYING THIS OUT

Directions: Supply the missing mathematical and logical operators. Write your answers on a separate sheet of paper.

  1. 5 _____ 5 = 10
  2. 7 _____ 3 = 4
  3. 10 _____ 2 = 5
  4. 9 _____ 3 = 27
  5. 5 _____ 5 = 25
  6. 4 _____ 4 = True
  7. 5 _____ 2 = True
  8. 7 _____ 3 = False
  9. 1 _____ 8 = False
  10. 6 _____ 1 = True

Did you find it hard to identify the missing operators? Did you know that we can also solve mathematical and logical problems using a computer? Spreadsheet applications can calculate numbers and solve different problems in preparing a workbook.

LESSON 2

UNDERSTANDING WHAT YOU DID

Formulas in a spreadsheet are expressions that operate or calculate the values in a range of cells, while functions are predefined formulas that are already available in spreadsheet applications.

The most practical way of entering formulas and functions in Excel is to type the equal sign (=) first, followed by the constants and operators.

For example, if you want to get the sum of two numbers entered in A1+A2, you will put your formula in A3 as =A1+A2.

[illustration: Excel screenshot; cell A3 selected showing formula =A1+A2; A1 = 5, A2 = 3, A3 = 8]

ENTERING A FORMULA
  1. To enter a formula, select a cell then type an equal sign (=), followed by the cell address of the numbers to be calculated. For example, A3 is the cell where the formula will be inserted to calculate the input numbers in cells A1 and A2.

[illustration: Excel screenshot; SUM function box showing =A1+A2 typed in cell A3; A1 = 5, A2 = 3]

  1. Instead of typing the cell labels, simply the first select cell, type the operator, and then select the second cell.

If you change the value in the input cells, the output cell will change as well because it has a formula.

[illustration: Excel screenshot; cell A3 with formula =A1+A2; A1 = 7, A2 = 3, A3 = 10]

  1. To edit the formula, click the cell on the formula bar and press Enter. Alternatively, you can double-click on the cell to change the formula.

You can change the arithmetic operators, such as the division sign ( / ) and the multiplication sign ( * ).

[illustration: two Excel screenshots; left, cell A3 with formula =A1/A2, A1 = 10, A2 = 3; right, cell A3 showing 3.33333 with A1 = 10, A2 = 3]

IDENTIFYING ERROR VALUES IN FORMULAS

If the intended result is not met after entering a formula, it may sometimes lead to error values. This includes errors such as #NAME?, #DIV/0!, and #REF!.

ERROR VALUES

#NAME? displays when Excel does not recognize the text in a formula. These texts are built-in functions in Excel.

In the example, instead of the word sum, sume was entered. To remove the error, simply correct the spelling of the word.

[illustration: Excel screenshot; cell A3 with formula =sume(A1:A2) showing #NAME?; A1 = 10, A2 = 3]

#DIV/0! displays when a number is divided either by zero or by a cell that contains no value.

In the example shown on the next page, instead of A2, the cell reference that was entered in the formula was B2, which has no value entered. To remove the error, do not divide the other number by zero or correct the cell reference in the formula.

ERROR VALUES

[illustration: Excel screenshot; cell A3 with formula =A1/B2 showing #DIV/0!; A1 = 10, A2 = 3]

#REF! displays when a cell reference is not valid.

For example, you may have deleted the row of cell A2. To remove the error, press Undo or Ctrl+Z to undo the deletion.

[illustration: Excel screenshot; cell A2 with formula =A1+#REF! showing #REF!; A1 = 10]

USING RELATIVE AND ABSOLUTE CELL REFERENCING

Cell reference or cell address is a combination of a column letter and a row number that identifies a cell on a worksheet. You can see your cell reference at the cell address bar. Cell references help Excel find the values the formula should calculate.

[illustration: Excel screenshot; cell C5 selected and shown in the cell address bar, with column C and row 5 highlighted]

In Excel, a range is a block of two or more cells. A range reference is represented by the address of the upper left cell and the lower right cell separated with a colon. For example, the range A1:D3 contains 12 cells from A1 to D3.

[illustration: Excel screenshot; range A1:D3 highlighted in a worksheet]

There are different types of cell references for creating a formula. These are the relative and absolute references. They function differently, so it is important to use the appropriate address type.

A relative reference is the default cell references in copying a formula. When you use relative, as you copy the formula, the cell reference will change according to the position of the cell.

  1. To use a relative reference, enter all the data first.

[illustration: Excel screenshot; cell C2 selected under a “Formula” column; Data1 values 1 to 5 in column A, Data2 values 6 to 10 in column B]

  1. Type the formula in the output cell. Here, the formula is typed on C2 to get the sum of the two numbers.

[illustration: Excel screenshot; SUM box showing =A2+B2 typed in cell C2; A2 = 1, B2 = 6]

  1. To automatically insert a formula for a range, say C3:C6, use relative referencing. Use the fill handle tool on the lower right corner of the cell, drag it down until you reach the last cell, and then release the left button.

[illustration: Excel screenshot; cell C2 showing 7 with the fill handle cursor; Data1 values 1 to 5 in column A, Data2 values 6 to 10 in column B]

In the example, notice that even if the formula in C2 was copied to the cell range C3:C6, there are different answers. This is because in relative reference, the position of the cell will change automatically.

[illustration: Excel screenshot; formula =A3+B3 in cell C3; column C shows 7, 11, 13, 15 for rows 2 to 6; Data1 values 1 to 5 in column A, Data2 values 6 to 10 in column B]

On the other hand, an absolute reference will not change as you copy the formula. Absolute reference is especially useful when you want to perform multiple calculations with a value in a specific cell or when you need to copy a formula to other cells without changing references.

  1. Let us create an additional calculation to the previous example. Let us multiply all the answers in C2:C6 by a single number placed in E7.
ABCDEF
1Data1Data2FormulaMultiplied by 3
2167
3279
43811
54913
651015
73
8
  1. Enter a new formula in D2, =C2*E7.
ABCDEF
1Data1Data2FormulaMultiplied by 3
2167=C2*E7
3279
43811
54913
651015
73
8
  1. To copy the formula, use the fill handle and drag down until D6.

After using the fill handle tool, you will notice a zero (0) result. It is because a relative reference was used.

ABCDEF
1Data1Data2FormulaMultiplied by 3
216721
32790
438110
549130
6510150
73
  1. Let us check the result. Click each cell reference and you will notice that it follows the next cell reference after E7, which is E8, then E9, and so on.
ABCDEF
1Data1Data2FormulaMultiplied by 3
216721
3279=C3*E8
438110
549130
6510150
73
8
9
  1. To make the formula absolute, you need to put a dollar sign ($) in the cell reference E7 in the formula. To execute the dollar sign, press Shift+4.


  2. After putting a dollar sign, easily apply the formula to the other cells by dragging the fill handle until D6. Notice that the only thing that changed is the position of the first cell reference in the formula, which is C2 to C6.


ABCDEF
1Data1Data2FormulaMultiplied by 3
2167=C2*$E$7
32790
438110
549130
6510150
73
ABCDEF
1Data1Data2FormulaMultiplied by 3
216721
327927
4381133
5491339
65101545
73

Let us check the formula of the other cell references. It is absolute because the E7 cell reference did not change for the rest of the formulas when a dollar sign was placed.

[transcriber’s note: partial screenshot showing rows 3 and 4 of the formula column, with the formulas =C3*$E$7 (row 3, value 9) and =C4*$E$7 (row 4, value 11)]

USING FUNCTIONS

Microsoft Excel offers many built-in functions that allow you to perform a variety of mathematical operations on the selected cells.

FUNCTIONS

SUM is used to compute the total of all items in the specified range.

AVERAGE is used to get the average of the items in a range.

COUNT is used to count the values found in the range.

COUNTA is used to count cells that contain numbers, texts, logical values, error values, and empty texts returned in formulas.

ROUND is used to round a number to a specified number of digits.

MAX is used to get the largest value within the range.

MIN is used to get the smallest value within the range.

One of the ways to enter a function directly to the formula bar is to type the function name and its arguments, usually a range of cells.

THE SUM FUNCTION
  1. Let us prepare sample data to work with.


  2. To get the total score, you need to use a sum function. In this example, you need to enter the formula =SUM(B2:F2) in G2. To get the range of the cells, simply highlight the range to be inserted in the formula.


  3. Use relative referencing to copy the formula to the rest of cells.


Step 1. Sample Data

ABCDEFG
1Quiz NumberQuiz 1Quiz 2Quiz 3Quiz 4Quiz 5Total
2Highest Possible Score1015201025
3Student1
4Student2
5Student3
6Student4
7Student5

Step 2. Sum Function

ABCDEFG
1Quiz NumberQuiz 1Quiz 2Quiz 3Quiz 4Quiz 5Total
2Highest Possible Score1015201025=SUM(B2:F2)
3Student1
4Student2
5Student3
6Student4
7Student5
ABCDEFG
1Quiz NumberQuiz 1Quiz 2Quiz 3Quiz 4Quiz 5Total
2Highest Possible Score101520102580
3Student10
4Student20
5Student30
6Student40
7Student50

The result is 0 because there is no data in the cells yet.

To use other functions, simply type the name of the function you want to use.

THE AVERAGE FUNCTION

Let us get the average, total number of quizzes, largest score, and smallest score in the quizzes of the previous example.

  1. To get the average, enter =AVERAGE(B2:F2).
  2. Use the fill handle to copy the function to other cells.

Let us enter scores for each student.

ABCDEFGH
1Quiz NumberQuiz 1Quiz 2Quiz 3Quiz 4Quiz 5TotalAverage
2Highest Possible Score10152010258016
3Student11012155216312.6
4Student2515107236012
5Student3715204206613.2
6Student4811198257114.2
7Student5681310225911.8

[transcriber’s note: the first screenshot shows =AVERAGE(B2:F2) typed in H2 before the fill handle was used; the table above is the completed result]

When you type a function name, a drop-down list of functions appears. It means that Excel recognizes the function. You can just double-click the function name you want to use from the list.

THE COUNT FUNCTION
  1. To use the count function, type =COUNT([range of cells]).


    You can also double-click COUNT from the drop-down list. Here, we type =COUNT(B2:F2)


  2. When you double-click the function, it will automatically be selected, and the next entry should be the range of the cell.


    The result will be 5, which means there are five quizzes. Use the fill handle to copy the formula to the rest of the cells.


[illustration: drop-down list of functions after typing =COU]

Drop-down list of functions

[illustration: automatic entry of =COUNT( with tooltip COUNT(value1, [value2], …)]

Automatic entry of COUNT function

In the example on the next page, some results are not equal to five because the student missed some quizzes.

ABCDEFGH
1Quiz NumberQuiz 1Quiz 2Quiz 3Quiz 4Quiz 5TotalCount
2Highest Possible Score1015201025805
3Student11015521514
4Student2515723504
5Student371520423
6Student481119825715
7Student568131022595

COUNT Function Example

THE COUNTA FUNCTION
  1. If you wish to return the number of non-blank cells in one cell range, say A2:A7, use the COUNTA function. In this example, you can see that the formula returns the number of non-blank cells in the range A2:A7.

[illustration: COUNTA formula =COUNTA(A2:A7) entered in B2 beside the Values column]

  1. =COUNTA(A2:A7) counts the number of data-filled cells in A2 through A7 and returns a value of 5 because cell A5 is blank. So all values are counted except the value in cell A5, which is blank.
ABC
1ValuesResult
225
33
4text
5
6#N/A
7""

Lesson 2

THE ROUND FUNCTION

The ROUND function in Excel rounds a number to a specified number of digits. Numbers 1, 2, 3, and 4 get rounded down, while 5, 6, 7, 8, and 9 get rounded up. Here are examples on how to use the ROUND function.

[illustration: Excel worksheet with formula bar showing =ROUND(A1,3) for cell B1; A1 contains 114.7261 and B1 shows 114.726]

Round a Number to Three Decimal Places

[illustration: Excel worksheet with formula bar showing =ROUND(A1,2) for cell B1; A1 contains 114.7261 and B1 shows 114.73]

Round a Number to Two Decimal Places

[illustration: Excel worksheet with formula bar showing =ROUND(A1,1) for cell B1; A1 contains 114.7261 and B1 shows 114.7]

Round a Number to One Decimal Place

[illustration: Excel worksheet with formula bar showing =ROUND(A1,0) for cell B1; A1 contains 114.7261 and B1 shows 115]

Round a Number to the Nearest Integer

THE MAX AND MIN FUNCTION

To use the max or min function, type in the formula =MAX([range of cells]) or =MIN([range of cells]). In these examples, we type =MAX(B2:F2) and =MIN(B2:F2).

[illustration: Excel worksheet with formula =MAX(B2:F2) entered in cell H2 beside the Total column]

Quiz NumberQuiz 1Quiz 2Quiz 3Quiz 4Quiz 5Total
Highest Possible Score101520102580
Student1101552151
Student251572350
Student37152042
Student48111982571
Student56813102259

[illustration: Excel worksheet with formula =MIN(B2:F2) entered in cell I2; cell H2 shows 25]

The maximum number should be 25, and the minimum number should be 10. Use the fill handle to copy the formulas to the rest of the cells.

USING THE LOGICAL FUNCTION IF

The logical function If is useful in decision-making statements to determine if the result is a success or not to the standard being set.

Let us use grades of students as an example.

  1. Prepare a sample workbook containing students’ grades in the first quarter. The remarks will only display “Passed” or “Failed.”
GradesFirst QuarterRemarks
Student175
Student282
Student371
Student484
Student577
  1. Enter the IF function in the output cell. Here, type in =IF(B2>=75, “Passed”, “Failed”) in C2.

[illustration: Excel worksheet with formula =IF(B2>=75, “Passed”, “Failed”) entered in cell C2; B2 is 75]

If the value in B2 is greater than or equal to 75, it will result in a passed remark. Otherwise, if the value in B2 is 74 and below, it will result in a failed remark.

The remark for C2 will be “Passed” because the statement is true for C2; the grade 75 is equal to the value set.

[illustration: Excel worksheet showing formula =IF(B2>=75, “Passed”, “Failed”) in the formula bar and cell C2 displaying “Passed”]

  1. Use the fill handle to copy the formula. C4 is “Failed” because the grade 71 is not greater than or equal to 75.
GradesFirst QuarterRemarks
Student175Passed
Student282Passed
Student371Failed
Student484Passed
Student577Passed

Formulas and functions are the two most essential features of Microsoft Excel and other spreadsheet applications. After all, they were built to organize and calculate numerical data.

FORMATTING NUMERICAL DATA

Formatting is different in a spreadsheet when it comes to numerical data entered in a cell. When you enter data with numbers, Excel will recognize what type of numerical data you are referring to, but you can always change it with your desired format.

DECIMAL PLACES AND SEPARATOR

To edit the number format of cells, look for the Number group in the Home tab.

[illustration: Excel Home tab ribbon with the Number group highlighted; the Number format box shows General]

You can find the full set of number formatting options if you click the Dialog Box Launcher in the corner of the Number group.

[illustration: close-up of the Number group showing the General format box, percent, comma, and decimal buttons, and the Dialog Box Launcher circled]

[illustration: Category list showing General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, and Custom]

[illustration: Format Cells dialog box with the Number tab selected; Category list shows General selected and the note “General format cells have no specific number format.”]

Let us try to format numbers with decimal places. Excel will display the decimal places you entered in the number.

ItemsPriceQuantityTotal
Shirts149.752
Shorts89.51
Shoes5003

Some have two decimal places and some only have one because Excel displays the zeroes that end a decimal. But for the purpose of uniformity and organization, you can choose to format decimal places.

  1. To format decimal places, click the cell or cell ranges, then click the Number Format drop-down list, and then choose Number.

[illustration: Excel Home tab with the Number Format drop-down list open, showing General, Number (149.75), Currency, Accounting, Short Date, Long Date, Time, Percentage, and Fraction options; the Number option is selected]

Here, the decimal places of the prices in cell range B2:B4 will be displayed with two decimals.

ItemsPriceQuantityTotal
Shirts149.752
Shorts89.501
Shoes500.003
  1. Next, let us calculate first. Here, we compute for the total price of each item by multiplying the price by the quantity.

[illustration: Excel worksheet with formula =B2C2 entered in cell D2]*

Use the fill handle to copy the formula to D3 and D4.

ItemsPriceQuantityTotal
Shirts149.752299.5
Shorts89.50189.5
Shoes500.0031500
  1. To change the format to two decimal places, change the number format to Number in the Number group.

[illustration: Excel Home tab with the Number format box set to Number, and column D showing Total values formatted as 299.50, 89.50, and 1500.00]

However, this quick access is not applicable if you want a decimal place that is more than two.

To format it, click More Number Formats… or the dialog box launcher. For example, when you use the mathematical operator divide (/) in a formula, it is expected to have results with decimal places.

[illustration: Excel worksheet with formula =A2/B2 entered in cell C2]

Data1Data2Total
1061.66667
25122.08333
32310.6667

Let us change the decimal places to three. Select the range of cells, say C2:C4, then click More Number Formats. A Format Cells dialog box will appear. Then, choose Number.

[illustration: Format Cells dialog box with the Number category selected, Sample showing 1.67, Decimal places box, and Negative numbers options listed as -1234.10, 1234.10, -1234.10, -1234.10 in red]

[transcriber’s note: the value in the Decimal places box in the screenshot is too small to read reliably]

The default decimal place is 2. Change it to 3 by entering 3 or clicking the arrow up. Click OK.

[illustration: Format Cells dialog box, Number category selected, Decimal places set to 3]

The values will now display three decimal places rounded to the nearest thousandths.

Data1Data2Total
1061.667
25122.083
32310.667

Let us go back to the first example. Total prices in thousands do not have a separator because they were entered as numbers with a General format.

To add a comma or a thousands separator, select the cell range to be formatted then click Comma Style in the Number group.

[illustration: Home tab Number group with Comma Style button highlighted]

Total
299.50
89.50
1500.00

Then, the number format will become Accounting, and a thousands separator will be displayed in D4.

[illustration: Home tab ribbon with Accounting number format selected; worksheet showing D2 formula =B2C2]*

ItemsPriceQuantityTotal
Shirts149.752299.50
Shorts89.50189.50
Shoes500.0031,500.00

Let us change the quantities and prices to see other results.

[illustration: worksheet with formula bar showing 5 in cell C4]

ItemsPriceQuantityTotal
Shirts299.5041,198.00
Shorts125.00101,250.00
Shoes500.0052,500.00
ADDING DATE TYPE, CURRENCY SYMBOL, AND PERCENTAGE

There are other numerical data that need special formatting. For example, you can choose among the available date formats, depend on the currency of a specific country when entering money, and display numbers as percentage.

DATE TYPE

From the example in our previous topic, let us put a date when the items were purchased or bought. The default format for dates is dd/mm/yyyy.

To change, select E2 then click More Number Formats dialog box launcher. Select the type that you prefer. For this example, let us select the format date/short month/year; just scroll down the selection.

[illustration: Home tab Number group with Date format selected; worksheet column E headed Date Purchased, showing 15/10/2019, 30/01/2020, 15/03/2020]

Date Purchased
15/10/2019
30/01/2020
15/03/2020

[illustration: Format Cells dialog box, Number tab, Date category selected, with a list of date types]

The date will be changed to the type assigned. You can do this to other dates in the cells. Do not use the fill handle, as it will create a series of dates following the first date entered.

[illustration: worksheet with formula bar showing 15/10/2019 in E2; column E shows 15-Oct-19, 30/01/2020, 15/03/2020]

[illustration: worksheet with formula bar showing 15/03/2020 in E4; column E shows 15-Oct-19, 30-Jan-20, 15-Mar-20]

ItemsPriceQuantityTotalDate Purchased
Shirts299.5041,198.0015-Oct-19
Shorts125.00101,250.0030-Jan-20
Shoes500.0052,500.0015-Mar-20
CURRENCY SYMBOL

Let us put a currency symbol on the price and total prices. Select the cell range to be formatted. In this example, we will select B2:B4 and D2:D4. To select nonadjacent cells and format cells easily and quickly, press the Ctrl key while selecting the cell ranges.

[illustration: worksheet with cell ranges B2:B4 and D2:D4 selected]

ItemsPriceQuantityTotalDate Purchased
Shirts299.5041,198.0015-Oct-19
Shorts125.00101,250.0030-Jan-20
Shoes500.0052,500.0015-Mar-20

Click the Currency format in the Number group. You can see a Peso sign if you are using Microsoft Excel in countries with a Peso currency such as the Philippines.

[illustration: Home tab Number format list with Currency option highlighted, showing ₱1,198.00]

The format will be applied to the selected cell ranges.

If the data is displayed as a series of number signs or hashtags (#)—also known as hash or pound sign—it means that the data is longer than the default width of the column. Adjust the column width by double-clicking on the right side of the header.

[illustration: worksheet with Total column displaying number signs (#######) and the column border being double-clicked]

ItemsPriceQuantityTotalDate Purchased
Shirts₱299.504#######15-Oct-19
Shorts₱125.0010#######30-Jan-20
Shoes₱500.005#######15-Mar-20
PERCENTAGE

How about discounting the price of the items? Let us put a percentage in each price. There are two ways to do it. First, enter the numbers.

ItemsPriceQuantityTotalDate PurchasedDiscount
Shirts₱299.504₱1,198.0015-Oct-195
Shorts₱125.0010₱1,250.0030-Jan-2010
Shoes₱500.005₱2,500.0015-Mar-2050

For the first method, click Percent Style in the Number group.

[illustration: Home tab Number group with Percent Style button highlighted; tooltip reads “Percent Style (Ctrl+Shift+%) Format as a percent.”]

But the percentage displayed two additional decimal places. To solve this problem, simply reenter the original numbers 5, 10, and 50.

ItemsPriceQuantityTotalDate PurchasedDiscount
Shirts₱299.504₱1,198.0015-Oct-19500%
Shorts₱125.0010₱1,250.0030-Jan-201000%
Shoes₱500.005₱2,500.0015-Mar-205000%
ItemsPriceQuantityTotalDate PurchasedDiscount
Shirts₱299.504₱1,198.0015-Oct-195%
Shorts₱125.0010₱1,250.0030-Jan-2010%
Shoes₱500.005₱2,500.0015-Mar-2050%

The second method is to enter the numbers followed by a percent sign (%) to avoid reentering the numbers.

ItemsPriceQuantityTotalDate PurchasedDiscount
Shirts₱299.504₱1,198.0015-Oct-195%
Shorts₱125.0010₱1,250.0030-Jan-20
Shoes₱500.005₱2,500.0015-Mar-20

To complete our example, let us compute for the less price, discounted price, and the total amount due. Create a formula on G2:G4 for the less price by multiplying the original price to the percentage discount.

[illustration: worksheet with formula bar showing =B2F2 in cell G2, column headed Less]*

Next, for the discounted price, create a formula in H2:H4 by subtracting the original price to the less price. Freeze columns A and B for a while.

[illustration: worksheet with formula bar showing =B2-G2 in H2; second screen shows H2:H4 with 284.53, 112.50, 250.00 and formula bar =H4-G4]

ItemsPriceQuantityTotalDate PurchasedDiscountLessDiscounted Price
Shirts₱299.504₱1,198.0015-Oct-195%₱14.98₱284.53
Shorts₱125.0010₱1,250.0030-Jan-2010%₱12.50₱112.50
Shoes₱500.005₱2,500.0015-Mar-2050%₱250.00₱250.00

For the total amount, multiply the discounted price to the quantity. First, freeze columns A, B, and C.

[illustration: worksheet with formula bar showing =H2C2 in cell I2, column headed Total Amount]*

ItemsPriceQuantityDiscounted PriceTotal Amount
Shirts₱299.504₱284.53₱1,138.10
Shorts₱125.0010₱112.50₱1,125.00
Shoes₱500.005₱250.00₱1,250.00

Lastly, compute for the total price on all items, both with original prices and discounted prices. Use the SUM function in D5 and I5.

FORMATTING CELL APPEARANCE

Cells use the same format as default. However, a workbook can have a lot of information, so drawing attention to a specific section can be difficult. Basic formatting can help you customize your workbook’s appearance to make it easier to view and understand.

CHANGING FONT SIZE, FONT, AND FONT STYLE

Let us change the font size and font type of the sample workbook about the small clothing business. The Font group can be found in the Home tab.

[illustration: two Excel screenshots, one showing cells D1:D5 with Total values ₱1,198.00, ₱1,250.00, ₱2,500.00 and the formula =SUM(D2:D4) in D5, the other showing columns H–I with Unit Price values ₱284.53, ₱112.50, ₱250.00 and Total Amount values ₱1,138.10, ₱1,125.00, ₱1,250.00 and the formula =SUM(I2:I4) in I5]

Now let us change the appearance of cells in the spreadsheet and add colors and lines.

[illustration: Home tab of the Excel ribbon with the Font group highlighted; label “FONT GROUP”]

To change the font size, click the drop-down arrow and choose the desired size, or click the Increase Font Size or Decrease Font Size button. You can also type a number in the text box.

Font is located beside the Font Size drop-down menu. Click the drop-down arrow to choose from the variety of fonts or type the font if you know the name.

  1. In this sample, let us change the font size of the headings to 12 and the rest of the cells to 10.

[illustration: Font Size drop-down list showing 8, 9, 10 with 10 selected; Font drop-down list showing Theme Fonts Calibri Light (Headings) and Calibri (Body) and All Fonts 1HoonMakdaeyunpil R and 210 Milkyway B]

[illustration: two Excel screenshots of the sample workbook after resizing, showing headings Items, Price, Quantity, Total, Date Purchased and rows for Shirts, Shorts, Shoes]

ItemsPriceQuantityTotalDate Purchased
Shirts₱299.504₱1,198.0015-Oct-19
Shorts₱125.0010₱1,250.0030-Jan-20
Shoes₱500.005₱2,500.0015-Mar-20
₱4,948.00
  1. Apply the font Arial Narrow to all the cells.

[illustration: Excel screenshot with the font box set to Arial Narrow and the sample workbook data visible]

You can also apply font styles. Font style can be bold, italic, underline, or double underline.

  1. Let us make all the headings bold. Click the B icon in the Font group.

[illustration: Excel screenshot with heading row set in bold and the B button highlighted]

  1. Next, make A2:A4 italic. Click the I icon in the Font group.

[illustration: Excel screenshot with the item names in A2:A4 in italics and the I button highlighted]

  1. Apply underline on D4 and I4, the last prices in the items. Click U in the Font group.

[illustration: Excel screenshot with the U button highlighted and D4 and I4 underlined; row 4 shows Shoes, ₱500.00, 5, ₱2,500.00, ₱1,250.00]

There are other font styles like double underline. It can be seen if you click the arrow on the lower right corner of the Font group.

  1. Apply double underline to D5 and I5, the sum of each total amount. A Format Cells dialog box will appear where there are options for Underline. Click the drop-down arrow then select Double Underline.

Alternatively, you can click on the drop-down arrow beside the U button.

[illustration: Format Cells dialog box, Font tab, with the Underline drop-down open showing None, Single, Double, Single Accounting, Double Accounting]

[illustration: Font group of the Excel ribbon with the Underline drop-down open showing “Underline” and “Double Underline”]

ABCDI
5₱4,948.00₱3,513.10

transcriber’s note: the screenshot printed only columns A, B, C, D and I of the sheet; columns E to H are not shown.

APPLYING CELL BACKGROUND AND CONTENT COLOR

To apply colors, click the Fill Color icon for the background and the Font Color icon for text or content. Both icons can be found beside each other on the Font group of the Home tab.

[illustration: Home tab of the Excel ribbon with the Fill Color and Font Color buttons highlighted in the Font group]

  1. Apply color green to the background of all the headings. Highlight the cells first then click the drop-down arrow in the Fill Color command and choose green in the Standard Colors. You can also choose among Theme Colors or click More Colors.

[illustration: Fill Color drop-down palette showing Theme Colors and Standard Colors, with the green swatch under the cursor labeled “Green”; options No Fill and More Colors]

  1. Apply a light blue color for the items under Items.


  2. Next, color B2:E4 yellow and F2:I4 red.


[illustration: Excel screenshot of the full sample workbook with colored cell ranges; columns A to I visible]

ItemsPriceQuantityTotalDate PurchasedDiscountLessDiscounted PriceTotal Amount
Shirts₱299.504₱1,198.0015-Oct-195%₱14.98₱284.53₱1,138.10
Shorts₱125.0010₱1,250.0030-Jan-2010%₱12.50₱112.50₱1,125.00
Shoes₱500.005₱2,500.0015-Mar-2050%₱250.00₱250.00₱1,250.00
₱4,948.00₱3,513.10

transcriber’s note: the screenshot of this sheet shows the color fills described in the step above; the values are as printed in the screenshot.

  1. For D5 and I5, let us make the background black and the text white.

[illustration: Excel screenshot of the sample workbook with D5 and I5 shown with black background and white text]

To copy formats without changing the content, use Format Painter. Select the cell or cell range you want to copy the format then click Format Painter in the Clipboard group in the Home tab. Then, apply it to your desired cells. Remember that it will copy both the text format and the cell appearance format.

WRAPPING TEXT CONTENT

Because Excel cells have a default width, long text contents overlap with other cells, but you can easily fix this by adjusting the column widths.

However, if you want to keep the width as is or shorter so that all the information is there when you print the file, use the Wrap Text command. Wrap Text can display long texts by wrapping it into multiple lines in a single cell.

[illustration: Home tab Alignment group with the Wrap Text button highlighted and a tooltip reading “Wrap Text: Wrap extra-long text into multiple lines so you can see all of it.”]

In our example, Date Purchased and Discounted Price are cropped, and Total Amount is overlapping with the J column. To wrap text, click the cells and select Wrap Text in the Alignment group in the Home tab.

[illustration: Excel screenshot of the sample sheet with column headers wrapped onto multiple lines: Date Purchased, Discount Price, Total Amount]

ItemsPriceQuantityTotalDate PurchasedDiscountLessDiscounted PriceTotal Amount
Shirts₱299.504₱1,198.0015-Oct-195%₱14.98₱284.53₱1,138.10
Shorts₱125.0010₱1,250.0030-Jan-2010%₱12.50₱112.50₱1,125.00
Shoes₱500.005₱2,500.0015-Mar-2050%₱250.00₱250.00₱1,250.00
₱4,948.00₱3,513.10

The row will automatically adjust its height depending on the font size and text entered. Adjust the column widths and row height to arrange the wrapped text.

ItemsPriceQuantityTotalDate PurchasedDiscountLessDiscounted PriceTotal Amount
Shirts₱299.504₱1,198.0015-Oct-195%₱14.98₱284.53₱1,138.10
ALIGNING CELL CONTENTS HORIZONTALLY AND VERTICALLY

To apply horizontal alignment, select the cells then select align left, which is the default alignment; center; or align right. For the example, let us align the headings to the center.

[illustration: Home tab Alignment group with the center alignment button highlighted; Excel screenshot showing the headings Items, Price, Quantity, Total, Date Purchased, Discount, Less, Discounted Price, Total Amount centered]

For vertical alignment, the default is bottom alignment. You can apply top align, middle align, or bottom align. Let us align all the headings in the middle of the cell.

[illustration: Home tab Alignment group with the middle align button highlighted; Excel screenshot showing the headings vertically centered in row 1]

[transcriber’s note: the printed page numbers on these pages are 95 to 102, and the page markers follow the requested 105 to 112.]

Lesson 2

USING CELL CONTENT ORIENTATION

The Orientation command lets you rotate texts vertically or diagonally. It is a great way to label narrow columns. Simply click the Orientation command in the Alignment group then choose your preferred orientation.

[illustration: Excel Home ribbon with the Orientation menu open; the menu lists Wrap Text, Angle Counterclockwise, Angle Clockwise, Vertical Text, Rotate Text Up, Rotate Text Down, and Format Cell Alignment. Below it, row 1 shows rotated headers: Items, Price, Quantity, Total, Date Purchased, Discount, Less, Discounted Price, Total Amount]

MERGING CELLS

Merging cells is to combine two or more cells. This can be useful if you want to clarify that a label in Excel applies to multiple columns. The Merge & Center command is found in the Alignment group.

[illustration: Excel Home ribbon with the Merge & Center command highlighted; tooltip reads “Merge & Center / Pick how to merge cells.”]

Insert a column beside column A and highlight the two cells. Click Merge & Center.

[illustration: Excel worksheet with columns A to E; row 1 shows Items, Price, Quantity, Total; rows 2 to 4 show Shirts ₱299.50 4 ₱1,198.00; Shorts ₱125.00 10 ₱1,250.00; Shoes ₱500.00 5 ₱2,500.00; columns A and B are highlighted]

A1 and B1 were combined without affecting the other cells. To unmerge cells, just click the drop-down arrow on Merge & Center command then select Unmerge Cells.

APPLYING CELL BORDERS

Cell borders allow you to create a clear boundary among the different cells of your worksheet.

First, select the cells to be modified then click Borders in the Font group and choose what border to apply.

[illustration: Excel Home ribbon with the Borders menu open over a worksheet; menu items: Bottom Border, Top Border, Left Border, Right Border, No Border, All Borders, Outside Borders, Thick Outside Borders, Bottom Double Border, Thick Bottom Border, Top and Bottom Border, Top and Thick Bottom Border, Top and Double Bottom Border]

[illustration: Borders menu with Draw Borders section showing Draw Border, Draw Border Grid, Erase Border, Line Color (submenu open), Line Style, and More Borders; Line Color submenu shows Automatic, Theme Colors, and the tooltip “Light Gray, Background 2, Darker 10%”]

You can also modify the line color and line style. Click the drop-down arrow again and select Line Color. Select Light Gray, Background 2, Darker 10%, and then select the style at the bottom of the Line Style drop-down list.

You will notice a pencil pointer ready to apply the chosen color and style.

[illustration: Borders menu with Line Style submenu open, showing a list of line styles from None to the solid line at the bottom]

[illustration: worksheet closeup showing a pencil pointer at the border between cells A1 and B1 (Items and Price)]

[illustration: worksheet with cells A1 to B4 showing bordered headers Items and Price, and rows 2 to 4 with Shirts ₱299.50, Shorts ₱125.00, Shoes ₱500.00]

Apply this to the borders one by one. This lets you personalize your worksheets more.

There is so much you can do in preparing your workbook. You can always improve your spreadsheets and create different finance applications, tabular records, as well as calculate numbers, by using the best features of the software program, formulas and formatting cells.

Sharpening Your Skills

Directions: Determine the formula needed for the following situations. Write your answers on a separate sheet of paper.

A. Formula to get the total number of male and female

MaleFemaleTotal
2526

[transcriber’s note: the callout circled 1 points to the empty Total cell]

B. Formula to get the remaining balance of each budget

ExpensesBudgetUsedBalance
Food500300
Fare1000250

[transcriber’s note: the callouts circled 2 and 3 point to the empty Balance cells in the Food and Fare rows]

C. Formula to get the total less price of each item in 20% discount (use absolute cell referencing)

ItemsPriceLess Price
Chair550.00
Table2,500.00
Discount
20%

[transcriber’s note: the callouts circled 4 and 5 point to the empty Less Price cells for Chair and Table]

Treading the Road to Mastery

Directions: Follow the procedure in creating a monthly budget planner in Microsoft Excel. If you do not have a computer, please ask your mobile teacher for assistance.

  1. Open a blank workbook.
  2. Enter the following data starting from A2 to D3.
#ABCDEF
1
2Total Income
3Monthly EBudgetActualDifference
4Electricity
5Water
6Food
7Internet
8House Rent
9Fare/Gas
10Personal
11

[transcriber’s note: the text in cell A3 is cut off in the printed image and reads “Monthly E”; the cell is intended to read “Monthly Expenses” as shown in step 6]

  1. Wrap Text A3 then adjust column widths. Then, highlight A3:D3, apply middle align vertically and align center horizontally.
  2. Merge cell range B2:D2.
#ABCDEF
1
2Total Income
3Monthly ExpensesBudgetActualDifference

[transcriber’s note: the merged range B2:D2 is shown as one empty box]

  1. Type “Monthly Budget Planner” in A1. Highlight A1:D1 then apply the Merge & Center command.

[illustration: worksheet with formula bar showing MONTHLY BUDGET PLANNER; cell A1 merged across A1 to D1 with the text centered]

  1. Apply the same format below. Include TOTAL in A11.

MONTHLY BUDGET PLANNER

Monthly ExpensesBudgetActualDifference
Total Income
Electricity
Water
Food
Internet
House Rent
Fare/Gas
Personal
TOTAL

[transcriber’s note: the Total Income row is shown in the printed format above the header row of the planner; the header row is black with white text in the printed image]

  1. Fill in the cells with the one below. All the numbers should be separated by a comma, have two decimal places, and with an Accounting number format.

[illustration: Excel Home ribbon with the Accounting number format selected, and the formula bar showing 2000 in cell C10]

#ABCD
1MONTHLY BUDGET PLANNER
2Total Income20,000.00
3Monthly ExpensesBudgetActualDifference
4Electricity1,200.001,275.00
5Water500.00800.00
6Food3,000.002,450.00
7Internet2,000.001,999.00
8House Rent4,000.004,000.00
9Fare/Gas2,500.002,000.00
10Personal1,500.002,000.00
11TOTAL

[illustration: completed planner with the formula bar showing =B11-C11 in cell D11]

#ABCD
1MONTHLY BUDGET PLANNER
2Total Income20,000.00
3Monthly ExpensesBudgetActualDifference
4Electricity1,200.001,275.00-75.00
5Water500.00800.00-300.00
6Food3,000.002,450.00550.00
7Internet2,000.001,999.001.00
8House Rent4,000.004,000.00–
9Fare/Gas2,500.002,000.00500.00
10Personal1,500.002,000.00-500.00
11TOTAL14,700.0014,524.00176.00
  1. Enter a formula for the Total Budget, Total Actual, and Total Difference. Use the SUM function.


  2. Enter a formula for the difference of each expense. If the answer is negative, color the font red.


  3. Include the Balance of Total Income in A12:B12, which should be merged.


[illustration: worksheet with row 12 labeled “Balance of Total Income” in A12, the merged cell A12:B12]

  1. Enter a formula for the Balance of Total Income in C12. Total Income should be subtracted from the Total Actual.

[illustration: completed planner with row 12 labeled “Balance of Income” and the value 5,476.00 in C12; the formula bar shows =B2-C11]

  1. Save your workbook as XLSX in the Documents folder with the file name: Monthly Budget Planner_[lastname] (e.g., Monthly Budget Planner_Cruz).

How was the activity? Did you find it easy? You can also make your own budget plan according to your personal or family income, or create other finance planners. Were you able to start analyzing the data in the activity? If yes, that is good because spreadsheets should also be used for analyzing results after carefully organizing data. Data can also be analyzed visually using charts in spreadsheet.

Lesson 3: Setting the Path

Inserting Charts and Finalizing Spreadsheet

After this lesson, learners should be able to

  • create different types of charts in a spreadsheet;
  • edit charts in a spreadsheet; and
  • demonstrate the preparation of outputs in a spreadsheet.

Lesson 3: Trying This Out

Directions: Answer the following questions based on the given pie chart of job classifications below. Write your answers on a separate sheet of paper.

[chart: pie; Pie Chart of Job Classifications]

Printed valuePosition on chart
8.3%top, right of centre
33.1%right
17.2%bottom
24.8%left
16.6%top left

Legend (printed title: Category): Janitorial; Manufacturing; Marketing; R&D; Sales

transcriber’s note: the chart does not print which legend category belongs to each slice, so the values are listed by position only.

  1. How many job classifications are presented in the chart?
  2. Which is likely the largest to be applied by the people?
  3. What percentage does “Janitorial” have in the chart?
  4. Which job category has a percentage of 24.8%?
  5. What is the overall percentage of the job classifications?

Were you able to analyze the pie chart properly? If yes, that is excellent because it means you are knowledgeable in visual analysis. If not, do not worry because this lesson will show you how to present and interpret data using charts in a spreadsheet.

Understanding What You Did

Workbooks that contain a lot of data are sometimes difficult to analyze and interpret. Charts allow you to illustrate your workbook data graphically, which makes it easy to visualize comparisons and interpret results.

Microsoft Excel has different types of charts that allow you to choose what is best for your data. The most frequently used types of charts are as follows:

[illustration: column chart icon]

Column charts use vertical bars to represent data. They are typically used to compare several items in a specific range of values.

[illustration: bar chart icon]

Bar charts work just like column charts, but they use horizontal bars instead of vertical bars.

[illustration: line chart icon]

Line charts are ideal for showing trends. The data points are connected with lines, making it easy to see whether values are increasing or decreasing over time.

[illustration: pie chart icon]

Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it is easy to see which values make up the percentage of a whole.

To present data graphically, you should also know how to interpret data according to its parts. A chart is usually composed of a chart title, legends, vertical axis, and horizontal axis. You will be able to understand each one once you start creating charts in Microsoft Excel.

Creating a Chart

To create a chart, you must first have your data in the worksheet. Let us create a dummy grading sheet for first quarter.

LearnersFirst Quarter GradeRemarks
Learner185Passed
Learner287Passed
Learner383Passed
Learner477Passed
Learner575Passed
Learner673Failed
Learner788Passed
Learner884Passed
Learner985Passed
Learner1091Passed
Learner1187Passed
Learner1281Passed
Learner1385Passed
Learner1479Passed
Learner1572Failed

transcriber’s note: the table is a screenshot of a spreadsheet; the formula bar shows “Remarks” in cell C1.

  1. To insert a chart, select the data you will need. In our example, select A1:B17. The remarks column is not included.
  2. Click the Insert tab and find the Charts group.

[illustration: Excel Insert ribbon with the Charts group]

  1. Choose the column and bar chart type and click the drop-down arrow to choose Clustered Column under 2-D Column as the subtype. A column chart will be displayed on the right side of the worksheet.

[illustration: Excel 2-D Column chart menu with Clustered Column tooltip, and worksheet with data A1:B17]

[chart: clustered column; First Quarter Grade]

  • Chart title: First Quarter Grade
  • Vertical axis: 0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100 (callout: Vertical Axis)
  • Horizontal axis categories: Learner1, Learner2, Learner3, Learner4, Learner5, Learner6, Learner7, Learner8, Learner9, Learner10, Learner11, Learner12, Learner13, Learner14, Learner15 (callout: Horizontal Axis)
  • Callout: Chart Title

Values are not labeled on the chart.

To change a chart type, select the chart, click the Line Chart icon in the Charts group. Select Line in 2-D Line.

[illustration: Excel Charts group with 2-D Line options; Line tooltip]

To resize, simply select any corner of the chart then hold the mouse and resize.

[chart: line; First Quarter Grade]

  • Chart title: First Quarter Grade
  • Vertical axis: 0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100
  • Horizontal axis categories: Learner1, Learner2, Learner3, Learner4, Learner5, Learner6, Learner7, Learner8, Learner9, Learner10, Learner11, Learner12, Learner13, Learner14, Learner15

Values are not labeled on the chart.

You can move a chart by dragging it to any location in a worksheet. To delete a chart, just select the chart then press the Del key on the keyboard.

LearnersFirst Quarter GradeSecond Quarter GradeRemarks
Learner18583Passed
Learner28790Passed
Learner38385Passed
Learner47781Passed
Learner57577Passed
Learner67376Failed
Learner78882Passed
Learner88486Passed
Learner98591Passed
Learner109192Passed
Learner118787Passed
Learner128188Passed
Learner138589Passed
Learner147984Passed
Learner157278Failed

transcriber’s note: the table is a screenshot of a spreadsheet; the cell reference box shows H3.

  1. Let us delete the first chart and update our data.
  2. Click Line Chart in the Charts group and choose Line with Markers.

[illustration: Excel 2-D Line menu with Line with Markers highlighted]

Since the data was updated, you will now see two lines: one for the First Quarter Grade and one for the Second Quarter Grade.

[chart: line with markers; Chart Title (placeholder)]

  • Chart title: Chart Title
  • Vertical axis: 0, 10, 20, 30, 40, 50, 60, 70, 80, 90, 100 (partly shown)
  • Horizontal axis categories: Learner1, Learner2, Learner3, Learner4, Learner5, Learner6, Learner7, Learner8, Learner9, Learner10, Learner11, Learner12, Learner13, Learner14, Learner15
  • Legend: First Quarter Grade; Second Quarter Grade (callout: Legend)

Values are not labeled on the chart.

You will see that another part of the chart appears because of the additional data, but there was no chart title assigned; instead, it just has a “Chart Title” caption. To edit the chart title, click on the area of the chart title then replace it with “First and Second Quarter Grades.”

[chart: line with markers; First and Second Quarter Grades]

  • Chart title: First and Second Quarter Grades
  • Vertical axis: 30 to 100 (partly shown)

Values are not labeled on the chart.

To remove a chart, click the plus sign button or Chart Elements at the right corner of the chart then uncheck Chart Title. If you want to add a title, check Chart Title.

[illustration: Excel chart titled “First and Second Quarter Grades” with the Chart Elements menu open; checked items are Axes, Chart Title, Gridlines, and Legend; unchecked items are Axis Titles, Data Labels, Data Table, Error Bars, Trendline, and Up/Down Bars]

To edit the legend at the bottom, right-click the legend then choose Select Data.

[illustration: right-click menu on the chart with Delete, Reset to Match Style, Font, Change Chart Type, Select Data (highlighted), 3-D Rotation, and Format Legend]

Then, choose the legend to be edited. Click Edit on the dialog box.

[illustration: Select Data Source dialog box showing Chart data range “=Sheet1!$A$3:$C$17”, Legend Entries (Series) list with Series1 and Series2 (Edit button highlighted), and Horizontal (Category) Axis Labels Learner1 to Learner3]

In the Series Name, type First Quarter. Repeat the same procedure for Series 2. Then, click OK.

The legend is now changed.

[illustration: chart legend showing “First Quarter” and “Second Quarter”]

CHANGING COLORS IN A CHART

To change colors, select the chart and click the Chart Design tab. Choose Change Colors then pick a set of colors.

[illustration: Excel ribbon with the Chart Design tab selected and the Change Colors palette open, showing Colorful and Monochromatic color sets; chart titled “First and Second Quarter Grades” with legend First Quarter and Second Quarter]

To change the background color and text color of the chart, select the parts that you want to change the color, then select Format beside Chart Design.

To change the color of the background, choose Shape Fill in the Shape Styles group; for the text color, choose Text Fill in the WordArt Styles group.

[illustration: Excel Format tab ribbon with Shape Fill, Shape Outline, Shape Effects, and WordArt Styles groups; below it, a chart with a dark background and light text titled “First and Second Quarter Grades”]

To change the font, font size, font color, or font style on the text of each part, simply use the Font group in the Home tab.

These formats can be applied to different types of chart available in Microsoft Excel.

FINALIZING A SPREADSHEET

Before printing the spreadsheet, check its setup.

Let us begin with the page setup of the spreadsheet. Page Setup is in the Page Layout tab, which includes the margins, paper size, and document orientation.

[illustration: Page Layout tab ribbon labeled “PAGE SETUP GROUP”, showing Themes, Margins, Orientation, Size, Print Area, Breaks, Background, and Print Titles]

MARGINS

[illustration: Margins drop-down menu with options Last Custom Setting, Normal (selected), Wide, Narrow, and Custom Margins…]

To edit the margins, click Margins. Decide which option you need for your spreadsheet. Normal is the default, but if you like none of the options, customize with Custom Margins.

When you apply changes in the margin, you will not be able to see the effect unless you go to Print Preview. From there, click Show Margin in the lower right corner of the preview to adjust the margins manually.

[illustration: Print Preview lower-right corner with the Show Margins button selected]

Margins will now be visible for manual adjustment. Just click and drag the lines to apply changes.

[illustration: Print Preview of a worksheet showing a table of Learners, First Quarter Grade, Second Quarter Grade, and Remarks, with a dark chart beside it, and margin lines displayed; page indicator “1 of 2”]

SIZE AND ORIENTATION

Paper Size can be set up before you begin doing your spreadsheet. The default size is Letter size, but you can always choose which one you need.

Unlike word processors, you are unable to see the paper size in spreadsheets until you set up the size or go to Print Preview.

Once you have set up the size, you will see a marquee line indicating the boundary of the page.

Notice that the half of the chart overlapped the marquee line. It means that only the first half of the chart will be included on the first page. Printing spreadsheets is challenging, especially if you are only limited to common paper sizes.

[illustration: Size drop-down menu listing paper sizes, shown in the table below]

Paper sizeDimensions
Letter21.59 cm x 27.94 cm
Tabloid27.94 cm x 43.18 cm
Legal21.59 cm x 35.56 cm
Statement13.97 cm x 21.59 cm
Executive18.41 cm x 26.67 cm
A329.7 cm x 42 cm
A421 cm x 29.7 cm
A514.8 cm x 21 cm
B4 (JIS)25.7 cm x 36.4 cm
B5 (JIS)18.2 cm x 25.7 cm

[illustration: More Paper Sizes… option at the bottom of the Size menu]

[illustration: worksheet with a table of Students, First Quarter Grade, Second Quarter Grade, and Remarks, and a dark chart titled “First and Second Quarter Grades” overlapping a vertical page boundary line]

But if you are aiming for a portrait orientation, adjust the margins or scale of the spreadsheet. Click the Scaling option on Print Preview then choose Fit Sheet on One Page.

To fix this problem, you can put the chart after the data above or simply change the orientation from Portrait to Landscape.

[illustration: Print Preview with two pages, the first showing the table and half of the chart and the second showing the rest of the chart]

[illustration: Orientation drop-down menu with Portrait and Landscape (Landscape being selected), beside a worksheet with the chart placed beside the data]

You may use Show Margins to enlarge the scale.

[illustration: Print menu with Scaling Options list, showing Fit Sheet on One Page highlighted]

Scaling Options

[illustration: Excel Print pane showing Copies, Printer, Settings, and a preview of the worksheet with its grades table and line chart]

HEADER AND FOOTER

To add a header and footer, open the Page Setup dialog box.

[illustration: Page Layout ribbon with the Page Setup group and its dialog launcher circled, next to the Page Setup dialog box with its Header/Footer tab]

In the Header/Footer tab, you will see that Excel already has a number of presets based on your worksheet when you click on the Header or Footer drop-down. You may choose from the list or customize your own.

To customize your header or footer, click Custom Header or Custom Footer. A header or footer dialog box will open. Click the left, center, or right section and type your own header or footer.

[illustration: Header dialog box with Left section, Center section, and Right section boxes and formatting buttons]

Custom Header Dialog Box

Customize your header or footer with the different available formatting options just above the text boxes.

[chart: diagram of header and footer formatting buttons; unnumbered]

  • Format Text
  • Insert Number of Pages
  • Insert Time
  • Insert File Name
  • Insert Picture
  • Insert Page Number
  • Insert Date
  • Insert File Path
  • Insert Sheet Name
  • Format Picture

Let us write “Alternative Learning System” for our header and add some text formatting.

[illustration: Page Setup and Header dialog boxes over a worksheet, with the center section showing “Alternative Learning” in bold text]

Alternatively, you can also find Header & Footer in the Insert tab of the Text group.

The display will change to the Page Layout view where you will see a Header panel on top and a Footer panel at the bottom. The Header & Footer Design tab will also appear on the Ribbon.

[illustration: Insert tab of the Text group with Header & Footer selected]

[illustration: Page Layout view of the worksheet with the Header & Footer Design tab on the Ribbon]

Click on the text box and type your new header or footer.

[illustration: Page Layout view of the worksheet with an empty header box labeled “Add header” and a footer area]

[illustration: empty footer box labeled “Add footer” below the worksheet grid]

Edit your header and footer with the formatting tools available on the Header & Footer Design tab.

[illustration: Header & Footer Tools Design tab on the Ribbon]

Header Preview through Print Preview

[illustration: print preview of the grading sheet showing the header text and the grades table and line chart]

Header Preview through Print Preview

Header text as printed:

Alternative Learning System Center Grading Sheets

StudentsFirst Quarter GradeSecond Quarter GradeRemarks
Student18583Passed
Student28790Passed
Student38385Passed
Student47781Passed
Student57577Passed
Student67376Failed
Student78882Passed
Student88486Passed
Student98591Passed
Student109192Passed
Student118787Passed
Student128188Passed
Student138589Passed
Student147984Passed
Student157278Failed

[chart: line chart; First and Second Quarter Grades] Values are not labeled on the chart.

QuarterPassedFailed
1st Quarter114
2nd Quarter105

Besides the marquee lines that indicate the boundary of the page, you cannot see the worksheet’s margins, size, orientation, header, and footer in Normal view. Go to Print Preview or the Page Layout view to view them.

PREVIEWING AND PRINTING SPREADSHEETS

It is important to preview your output first to avoid errors and waste of paper.

  1. Select the Print Preview button on the Quick Access Toolbar or press Ctrl+P on the keyboard.

[illustration: Quick Access Toolbar with the Print Preview and Print button selected]

  1. Choose the active printer on Printer, and then indicate the number of copies to be printed on Copies.

To print specific pages, type in the pages you want to include. If there are five pages but you only want to print pages 1 to 3, then type 1 on the first box and 3 on the second box. If you only want page 2, then type 2 on the first box. If you are going to print the entire workbook, leave it blank.

[illustration: Print pane showing Copies, Printer, and Pages boxes]

Charts are used to visually present and interpret data. You will probably encounter charts on other office productivity tools and the same procedure in creating charts can be applied on those tools.

SHARPENING YOUR SKILLS

Directions: Using a Venn diagram, compare and contrast the different types of charts used in a spreadsheet by listing down their characteristics and features. Do this activity on a separate sheet of paper.

Column Chart Bar Chart

[illustration: blank Venn diagram of two overlapping circles]

Line Chart Pie Chart

[illustration: blank Venn diagram of two overlapping circles]

TREADING THE ROAD TO MASTERY

Directions: Follow the procedure to practice creating a pie chart. If you do not have a computer, please ask for assistance from your mobile teacher.

  1. Open a new blank workbook in Microsoft Excel.
  2. Use some data about the recommended diet. Enter the following data in the worksheet.

Recommended Diet

FoodPercentage
Fruit36%
Vegetables14%
Dairy13%
Protein28%
Grains9%
  1. Select the data from A2:B7.
  2. Click the Insert tab and go to the Charts group. Select 3-D Pie on Pie Chart.

[illustration: Insert tab of the Ribbon with the Pie Chart menu open and 3-D Pie highlighted]

  1. Change the chart title “Percentage” to “Recommended Diet.”

[chart: pie chart; Recommended Diet]

Legend: Fruit, Vegetables, Dairy, Protein, Grains Values are not labeled on the chart.

  1. Change the chart style of the pie chart. Select Style 3 on Chart Style on the Chart Design tab.

[illustration: Excel window with the Chart Design tab open, the Chart Styles gallery showing Style 3 selected, and the worksheet with a pie chart titled “Recommended Diet”]

FoodPercentage
Fruit36%
Vegetables14%
Dairy13%
Protein28%
Grains9%

[transcriber’s note: the worksheet title “Recommended Diet” is in cell A1 above the Food and Percentage header row; the pie chart in the screenshot shows the labels 36%, 14%, 13%, 28%, and 9%]

  1. Save the worksheet with file name: PieChart_[lastname] (e.g., PieChart_Mendoza.xlsx).

Spreadsheet applications are effective and convenient tools that help make tasks easier and more accurate.

Don’t Forget

  • Spreadsheet, or worksheet, is an arrangement of cells in columns and rows used to organize, analyze, calculate, and report information, usually in numerical form.


  • The basic parts of a spreadsheet are rows, columns, cell, active cell, cell address, formula bar, and sheet tabs.


  • Workbook is a file where spreadsheets are contained.


  • A cell is made up of rows and columns.


  • AutoFill allows automatic entering of data based on a pattern.


  • The row height and column width can be adjusted through its headers.


  • You can add two or more sheets in a workbook.


  • Formulas in a spreadsheet are an expression that operates or calculates the values in a range of cells or a cell.


  • Functions (sum, average, count, max, and min) are predefined formulas that are already available in Excel.


  • Cell reference or cell address is a combination of a column letter and a row number that identifies a cell on a worksheet.


  • There are two types of cell references, the relative and absolute references.


  • The logical function If is useful in decision-making statements to determine if the result is a success to the standard being set.


  • Microsoft Excel has a command called Number Format to change the format of numerical data.


  • Wrap Text allows long texts to be displayed on multiple lines but in a single cell.


  • Charts allow you to illustrate your workbook data graphically, which makes it easy to visualize comparisons and interpret results.


  • The four basic types of charts are column chart, bar chart, line chart, and pie chart.


  • The header and footer in a spreadsheet can be seen in Print Preview.


Explore More

To learn more about spreadsheets, visit the following websites:

https://www.edu.gcfglobal.org
https://www.excel-easy.com

Reach The Top

Directions: Identify the word being described in each statement. Choose your answer from the word pool and write it on a separate sheet of paper.

[transcriber’s note: the word pool is a table printed without a header row; its first printed row is used as the header below]

$Ctrl keyShift keyPie ChartCount
Relative ReferenceFormula BarGoogle SheetsSpreadsheetColumns
Formula&AverageFill HandleSum
Cell Reference.docXSLXAbsolute Reference#
Line Chart#REF!Wrap TextWorkbook=
Fill Color#DIV/0!Functions#NAME?Charts
RibbonsCommaCellIfRows
  1. This is where spreadsheets are contained.


  2. It is the individual intersections between rows and columns.


  3. It shows the contents of the active cell and allows you to create and view formulas.


  4. It is a free online spreadsheet program.


  5. This is the default file type of Microsoft Excel.


  6. This is a tool to use in copying cell contents automatically.


  7. These are vertical lines for data in a spreadsheet.


  8. It is used to start a formula in the formula bar.


  9. This error displays when a number is divided either by zero or by a cell that contains no value.


  10. It is the arrangement of cells in columns and rows used to organize, analyze, calculate, and report information.


  11. This is an expression that operates or calculates the values in a range of cells or a cell.


  12. These are divided into logical groups called tabs, and each tab has its own set of unique functions to perform.


  13. This is the key to press in the keyboard when selecting nonadjacent cell or cells.


  14. This error displays when a cell reference is not valid or deleted.


  15. These are horizontal lines for data in a spreadsheet.


Answer Key

PRE-ASSESSMENT

Page 2

  1. horizontal
  2. vertical
  3. cut
  4. cell
  5. font styles
  6. font type
  7. cells
  8. fill color
  9. horizontal
  10. mathematical
  11. pie chart
  12. functions
  13. comma
  14. sum
  15. fill color
LESSON 1: UNDERSTANDING SPREADSHEETS
TRYING THIS OUT

Page 5

  1. 10
  2. Spreadsheet
  3. 40%
  4. 4
  5. Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday
  6. =
  7. 10:00 AM
  8. 88.5
  9. 02/09/2020
  10. 1,000
SHARPENING YOUR SKILLS

Page 51

ACTIVITY I

  1. Tabs
  2. Ribbons
  3. Quick Access Toolbar
  4. Cell Address
  5. Rows
  6. Columns
  7. Formula Bar
  8. Cell
  9. Sheet Number
  10. Add Sheet

ACTIVITY II

Selecting Rows and Columns

  1. To choose a row or column, click the row header (number) or column header (letter).


  2. To click more than one row or column, hold your mouse from the headers then hold the left click and drag the mouse vertically or horizontally.


  3. To select nonadjacent rows and columns, click the desired rows and columns header while pressing the Ctrl key on the keyboard.


Inserting and Deleting Rows and Columns
  1. To insert row or rows in between, right click on the row header and click Insert.
  2. To insert more than one row, select the desired number of row headers and click Insert. The selected rows correspond to the number of new rows.
  3. To insert column or columns in between, right click the column header click Insert.
  4. To insert more than one column, select desired number of column headers then click Insert. The selected columns correspond to the number of new columns.
  5. To delete cell or cells, do the same procedure in inserting rows or columns, but instead of Insert, click the Delete command.
Adding Worksheet
  1. To add another sheet, click the plus sign button beside Sheet1.
  2. A new sheet will appear with Sheet2 as the name. You can continue doing this if you need more worksheets.
TREADING THE ROAD TO MASTERY

PAGE 53

  1. Select the cell and type the desired data.
  2. Double-click the cell width after the cell box where the text was entered.
  3. Click the add button sheet then right-click the Choose Rename command then change to the desired sheet name.

LESSON 2: USING FORMULAS AND FORMATTING CELLS

TRYING THIS OUT

PAGE 55

  1. −
  2. ×
  3. ×
  4. =

  5. <


SHARPENING YOUR SKILLS

PAGE 98

  1. =A2+B2
  2. =B2−C2
  3. =B3−C3
  4. =B2*$A$6
  5. =B3*$A$6

LESSON 3: INSERTING CHARTS AND FINALIZING SPREADSHEETS

TRYING THIS OUT

PAGE 104

  1. 5
  2. Manufacturing
  3. 8.3%
  4. R & D
  5. 100%
REACH THE TOP

PAGE 132

  1. Workbook
  2. Cell
  3. Formula Bar
  4. Google Sheets
  5. XSLX
  6. Fill Handle
  7. Columns
  8. =
  9. #DIV/0!
  10. Spreadsheet
  11. Formula
  12. Ribbons
  13. Ctrl key
  14. #REF!
  15. Rows

GLOSSARY

[transcriber’s note: the column headings are not printed on this page; the header row below is added for the table structure]

TermDefinition
AbsoluteViewed or existing independently and not related to others; not relative or comparative.
AccountingThe action or process of keeping financial accounts.
AnalyzeTo examine something to find out what it is or what makes it work.
CalculateTo determine the amount or number of something mathematically.
CurrencyA system of money in general use in a particular country.
DataFacts and statistics collected together for reference or analysis.
Decimal PlaceThe position of a digit to the right of a decimal point.
InformationThe summarization of data.
InterfaceA device or program enabling a user to communicate with a computer.

[transcriber’s note: table continued from the previous page]

TermDefinition
InterpretTo describe the meaning of something.
OrganizeTo arrange into a structured whole.
ProportionThe size, number, or amount of one thing or group of things as compared to that of another.
RelativeConsidered in relation or in proportion to something else.
TemplatesA preset format for a document or file, used so that the format does not have to be recreated each time it is used.

REFERENCES

“Spreadsheet.” Computer Hope. Accessed March 15, 2020. https:// https://www.computerhope.com/jargon/s/spreadsheet.htm

Alexander, Michael. “10 Excel Chart Types and When to Use Them.” Dummies. Accessed March 20, 2020. https://www. https://www.dummies.com/software/microsoft-office/excel/10-excel-chart-types-and-when-to-use-them/.

Lee, Gabriela, Alexander Maximo, Stevenson Lee, Ralsley Christopher Ramos, Kim Enriquez, Joy De Jesus, Lesley Abe, and Jaime Caro. Desktop Productivity Second Edition. Quezon City: TechFactors, Inc., 2013.

The development and printing of this learning resource was made possible with the cooperation of Asia Pacific College. This is a component of the project “Better Life for Out-of-School Girls to Fight Against Poverty and Injustice in the Philippines” implemented by UNESCO Office, Jakarta in partnership with the Department of Education. This initiative received a generous financial support from Korea International Cooperation Agency (KOICA).

For inquiries, please contact:

Department of Education, Bureau of Learning Resources (DepEd BLR)

Office Address : Ground Floor, Bonifacio Building, DepEd Complex, Meralco Avenue, Pasig City, Philippines 1600

Telefax : +63-2-8631-1072; +63-2-8634-1054; +63-2-8631-4985

Email Address : blr.qad@deped.gov.ph; blr.lrpd@deped.gov.ph

Leave a Reply

Your email address will not be published. Required fields are marked *

For students and teachers

Looking for a school?

Look up the enrollment, teachers, classrooms and facilities of more than 60,000 schools across the Philippines, from DepEd’s public data. Free to use.

Philippine Schools Directory by TeacherPH · every region, province, division and district