UNESCO ALS LS6 DIGICIT M03 (V1.1.C) PDF

SECONDARY JHS LEARNING STRAND 6 DIGITAL CITIZENSHIP MODULE 3: DIGITAL APPLICATIONS – SPREADSHEETS ALS Accreditation and Equivalency Program: Junior High School...

SECONDARY JHS LEARNING STRAND 6 DIGITAL CITIZENSHIP MODULE 3: DIGITAL APPLICATIONS – SPREADSHEETS ALS Accreditation and Equivalency Program: Junior High School RTY PE O PR LE T SA EN R M FO N T E R V NO O G  LEARNING STRAND 6 DIGITAL APPLICATIONS SPREADSHEETS 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 Jenelyn Marasigan Baylon Master Teacher I, ALS Task Force (On-detail) Kristine Lee S. Lumanog Education Program Specialist II, ALS Task Force (On-detail) Judy R. Mendoza Project Development Officer III, Bureau of Learning Resources Reyangie V. Sandoval Education Program Specialist II, Bureau of Learning Resources Josephine C. Intino Senior Education Program Specialist, Bureau of Curriculum Development Eric U. Labre Senior Education Program Specialist, Bureau of Learning Resources Roderick P. Corpuz Supervising Education Program Specialist, ALS Task Force Daisy Asuncion O. Santos Chief Education Program Specialist, Bureau of Learning Resources Marilette R. Almayda Director III/Head, ALS Task Force Ariz Delson Acay D. Cawilan Officer-In-Charge, Office of the Director IV, Bureau of Learning Resources G. H. S. Ambat Assistant Secretary for Alternative Learning System Program and Task Force Tonisito M. C. Umali Undersecretary for Legislative Liaison Office, External Partnership Service and Project Management Service Leonor Magtolis Briones Secretary Heiden Chan Author Adelina Calub Content Expert Bernadette Sison Admin and Finance Staff Mildred Parbo Project Lead Ma. Teresita Medado President Content and Language Evaluators and Instructional Design Reviewer Edward C. Jimenez Schools Division Office of Meycauayan City, Department of Education Melvin Lazaro Regional Office III – Central Luzon, Department of Education Ma. Jessamine Anne R. Verzosa Freelance Language Editor Ade Sandra Admin and Finance Assistant Rusyda Djamhur Project Assistant Marmon Abutas Pagunsan National Project Consultant Remegio Alquitran National Project Officer Maria Karisma Bea Agarao National Programme Coordinator Mee Young Choi Head of Education Unit Shahbaz Khan Director 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: This will give you an idea of the skills or competencies you are expected to Let’s Get to Know learn in the module. This part includes an activity that aims to check what you already know Pre-assessment about the lesson. If you get all the answers correct (100%), you may decide to skip this module. This section provides a brief discussion of the lesson. This aims to help you Setting the Path discover and understand new concepts and skills. This comprises activities for independent practice to solidify your Trying This Out 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 This includes questions that process what you learned from the lesson. What You Did Sharpening This section provides an activity that will help you transfer your new Your Skills knowledge or skill in real-life situations or concerns. Treading the This is a task which aims to evaluate your level of mastery in achieving the Road to Mastery given learning competency. Don’t Forget This part serves as a summary of the lessons in the module. In this portion, another activity will be given to you to enrich your knowledge Explore More or skill of the lesson learned. This also tends retention of learned concepts. This part will assess your level of mastery in achieving the learning Reach the Top competencies in each lesson in the module. Answer Key This contains answers to all activities in the module. This portion gives information about the meanings of the specialized words Glossary used in the module. At the end of this module you will also find: References This 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!  Let’s Get to Know 1 Pre-Assessment 2 LESSON 1: Understanding Spreadsheets 3 Setting the Path 3 Trying This Out 5 Understanding What You Did 6 Sharpening Your Skills 51 Treading the Road to Mastery 53 LESSON 2: Using Formulas and 54 Formatting Cells Setting the Path 54 Trying This Out 55 contents Understanding What You Did 56 Sharpening Your Skills 98 Treading the Road to Mastery 99 LESSON 3: Inserting Charts and 103 Finalizing Spreadsheets Setting the Path 103 Trying This Out 104 Understanding What You Did 105 Sharpening Your Skills 125  Treading the Road to Mastery 126 Don’t Forget 129 Explore More 131 Reach the Top 132 Answer Key 134 Glossary 138 References 140 contents  MODULE 3 Let’s get to know % X - + W e deal with numbers every day. For example, we budget our money for our food, fare, clothing, and other expenses. Calculating numbers is a skill we can easily do and apply in our daily lives, but what if you need to calculate larger numbers and bigger sums such as in banks, department stores, supermarkets, grades, or payrolls? This is where a spreadsheet program take part as it helps in computing and organizing data. This module will walk you through how to use spreadsheets to help you get familiar with it. The following are the three lessons in this module: Lesson 1 – Understanding Spreadsheets Lesson 2 – Using Formula and Formatting Cells Lesson 3 – Inserting Charts and Finalizing Spreadsheets SPREADSHEETS 1  MODULE 3 PRE-ASSESSMENT Directions: Choose the correct answer in the parenthesis to complete the statement. Write your answers on a separate sheet of paper. 1. Rows are a range of (horizontally, vertically) aligned cells represented by numbers in a spreadsheet. 2. Columns are a range of (horizontally, vertically) aligned cells represented by letters in a spreadsheet. 3. In moving a cell content, use the (copy, cut) command. 4. (Box, Cell) is the individual intersection between rows and columns. 5. Bold, Italic, Bold Italic, and Underline are (font styles, font types). 6. Arial, Times New Roman, and Verdana are examples of (font styles, font types). 7. Spreadsheet or worksheet is an arrangement of (boxes, cells) in columns and rows. 8. To put a background color for a cell, you need to use the (Fill Color, Font Color) command. 9. Left, center, and right are (horizontal, vertical) alignments. 10. Addition, subtraction, multiplication, and division are (mathematical, logical) operations. 11. The (Line Chart, Pie Chart) is used for easy comparison of proportions. 12. (Formulas, Functions) are predefined formula in Microsoft Excel. 13. (Comma, Point) is the separator used in accounting number format. 14. A function that is used to compute the total of all items in the specified range is called (Sum, Total). 15. A command called (Fill Cell, Fill Color) is used to change the background color of a cell. Did you get all the answers correct? It is okay if you did not, especially if this is the first time you encountered those words and statements. This module will help you understand spreadsheets and their uses. 2 DIGITAL APPLICATIONS  lesson 1 Setting the Path UNDERSTANDING SPREADSHEETS After this lesson, learners should be able to use the spreadsheet application to generate different documents; create a new spreadsheet based on the different available templates; demonstrate how to save a spreadsheet under another name, as another file type, and to a location on a drive; distinguish the different uses of magnification or zoom tools; determine the use of the different toolbars; SPREADSHEETS 3 Lesson 1 demonstrate understanding of using cells in a spreadsheet; apply the process of modifying rows and columns; and customize worksheets. 4 DIGITAL APPLICATIONS  Lesson 1 TRYING THIS OUT Directions: Identify and locate the contents in the cell references below. Write your answers on a separate sheet of paper. 1. A1 6. A4 2. C2 7. A7 3. B6 8. E8 4. E3 9. D4 5. C5 to C11 10. D6 Were you able to locate the contents? If yes, this is how you will be working with cells in a spreadsheet. This is one of the basic ways to familiarize yourself to spreadsheets. SPREADSHEETS 5  lesson 1 Understanding What You Did SPREADSHEET A spreadsheet or worksheet is an arrangement of cells in columns and rows used to organize, analyze, calculate, and report information, usually in numerical form. Spreadsheet software programs are considered unique because they can calculate values using mathematical formulas and data in the cells. Like word processors, many applications are used to create spreadsheets. Below is a list of the most common spreadsheet applications and their operating system where these programs can be opened. SPREADSHEET ICONS OPERATING SYSTEM PROGRAM Microsoft Excel Windows 6 DIGITAL APPLICATIONS Lesson 1 SPREADSHEET ICONS OPERATING SYSTEM PROGRAM Quattro Pro Windows iWork Numbers Apple MacOS OpenOffice Calc Any Google Sheets Any 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. SPREADSHEETS 7 Lesson 1 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. Sheet Tab 8 DIGITAL APPLICATIONS Lesson 1 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. SPREADSHEETS 9  Lesson 1 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. 2. 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. 3. To open a new workbook, click New on the right side of the screen then select Blank workbook. 4. To close a spreadsheet application, click the Close button on the upper right corner of the screen. 10 DIGITAL APPLICATIONS Lesson 1 SPREADSHEETS 11  Lesson 1 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. 12 DIGITAL APPLICATIONS Lesson 1 1. Click Personal in the suggested searches. 2. Select Personal Monthly Budget. Once it is selected, a window will appear with details about the template. Click Create. SPREADSHEETS 13  Lesson 1 3. Once Excel loads the selected template, you will see two sheets: Start and Personal Monthly Budget. Click the Personal Monthly Budget sheet. 4. To edit, click the cells and change to your desired contents. Loaded Personal Monthly Budget Template 14 DIGITAL APPLICATIONS Lesson 1 Personal Monthly Budget Sheet 5. Templates can still be modified according to the preferred data. 6. If you do not like the templates, you can always go back and use a blank workbook. SPREADSHEETS 15  Lesson 1 Blank Workbook 16 DIGITAL APPLICATIONS Lesson 1 SAVING A WORKBOOK 1. To save a new workbook, press Ctrl+S on the keyboard. You can also click the Save button  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. SPREADSHEETS 17  Lesson 1 3. Notice that the file name on the upper left corner has changed from “Book1” to “My First Workbook.” Spreadsheets in Microsoft Excel are usually saved in the Documents folder as XLSX. 4. 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. 5. 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. 18 DIGITAL APPLICATIONS Lesson 1 It can be saved as a template, Portable Document Format (PDF), web page, rich text format, or any file type you want to use. Different Available File Types 6. 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. SPREADSHEETS 19  Lesson 1 20 DIGITAL APPLICATIONS Lesson 1 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. 2. 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. SPREADSHEETS 21  Lesson 1 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. 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. 22 DIGITAL APPLICATIONS Lesson 1 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. SPREADSHEETS 23  Lesson 1 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 View Tab Zoom Group in Excel under the View tab of the application. 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. 24 DIGITAL APPLICATIONS Lesson 1 Zoom to Selection View 3. Alternatively, the zoom slider is available for instant zoom in and zoom out. It is located at the lower right corner of the workbook. Zoom Slider SPREADSHEETS 25  Lesson 1 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. When you hide the ribbon, only the tabs will be displayed. Ribbons will only appear if you select a tab. 26 DIGITAL APPLICATIONS Lesson 1 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. Ribbon Display Options Quick Access Toolbar 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. SPREADSHEETS 27  Lesson 1 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. To organize your workbook, starting at the left corner of the sheet is recommended. 2. Let us put names as text beside each number in cells B1 to B5. 3. Then, insert birthdates on C1 to C5 in mm/dd/yyyy format. 28 DIGITAL APPLICATIONS Lesson 1 Notice that the inserted date can be seen in the formula bar. 4. 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. SPREADSHEETS 29  Lesson 1 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. 2. 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. 3. 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. 30 DIGITAL APPLICATIONS Lesson 1 Step 2. Insert a Blank Row Before the Total Row Step 3. Ensure Cell Bordering Lists are Blank. SPREADSHEETS 31  Lesson 1 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. 3. To select a nonadjacent cell or cells, click each cell while holding the Ctrl key on the keyboard. Selecting Nonadjacent Cells 4. If you want to select the entire worksheet, click the triangle at the corner of A1. 32 DIGITAL APPLICATIONS Lesson 1 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. SPREADSHEETS 33  Lesson 1 2. 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. Pasting content Successful paste 3. 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. 4. Select the cells where you want to move your content then click Paste. 5. 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. 34 DIGITAL APPLICATIONS Lesson 1 Step 3. Moving Contents in a Cell (Cutting Contents) Step 4. Successful Paste Step 5. Copying or Moving Cell contents from One Sheet to Another or Between Open Workbooks SPREADSHEETS 35  Lesson 1 6. 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. 2. Point your mouse on the lower right corner of the cell. You should see a solid cross pointer; this is called the fill handle. 36 DIGITAL APPLICATIONS Lesson 1 3. Hold the mouse’s left button and drag down the pointer. You will be guided by a tool tip indicating the next content. 4. AutoFill Options will appear after your use the fill handle. When you click the button, the options for AutoFill will be displayed. 5. The default selection is Fill Series. Copy Cells will not follow a pattern; it will only copy the selected word. AutoFill Options SPREADSHEETS 37  Lesson 1 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. Copy Cells AutoFill Option Fill Formatting Only Fill Without Formatting 38 DIGITAL APPLICATIONS Lesson 1 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.  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. SPREADSHEETS 39  Lesson 1 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. 3. To select nonadjacent rows and columns, click the headers of the desired rows and columns while pressing the Ctrl key on the keyboard. 40 DIGITAL APPLICATIONS Lesson 1 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. 2. 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. SCREENSHOT 07 SPREADSHEETS 41  Lesson 1 INSERTING AND DELETING ROWS AND COLUMNS 1. To insert a row 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. 42 DIGITAL APPLICATIONS Lesson 1 3. To insert a column in between, right-click the column header and click Insert. 4. 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. SPREADSHEETS 43  Lesson 1 5. To delete cells, do the same procedure in inserting rows or columns, but instead of Insert, click Delete. 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. 2. 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. 44 DIGITAL APPLICATIONS Lesson 1 3. 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. SPREADSHEETS 45  Lesson 1 4. 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. 5. To unfreeze the panes, simply click Unfreeze Panes. 46 DIGITAL APPLICATIONS 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. ADDING WORKSHEETS 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. SPREADSHEETS 47  Lesson 1 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.” 2. Do the same procedure to all the worksheets that you will add. 3. To switch between worksheets, just click the sheet that you want to work on. 48 DIGITAL APPLICATIONS Lesson 1 COPYING, MOVING, AND DELETING WORKSHEETS 1. To copy a worksheet, right-click the worksheet to be copied then click Move or Copy... 2. A dialog box will appear with a list of existing worksheets. Check Create a copy. 3. 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.  SPREADSHEETS 49  Lesson 1 4. To move worksheets, right- click the sheet to be moved then select Move or Copy and choose (move to end). Click OK. 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. 5. To delete a worksheet, click the worksheet to be deleted, right-click, and then choose Delete. Microsoft Excel has a Graphical User Interface (GUI) that facilitates easy navigation among cells and worksheets. 50 DIGITAL APPLICATIONS  lesson 1 Sharpening Your Skills I. Directions: Identify the parts of Microsoft Excel. Write your answers on a separate sheet of paper. 1 2 3 4 7 6 5 8 9 10 SPREADSHEETS 51  Lesson 1 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 1. 2. 3. Inserting and Deleting Rows and Columns 1. 2. 3. 4. 5. Adding a Worksheet 1. 2. 52 DIGITAL APPLICATIONS  lesson 1 TREADING THE road to mastery Directions: Give the steps on the following task. Write your answer on a separate sheet of paper. What do you do if you want the entire you want to add you want to enter text displayed but a sheet and data in a cell? it is too long to fit rename it? inside the cell? 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. SPREADSHEETS 53  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. 54 DIGITAL APPLICATIONS  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 6. 4 4 = True 2. 7 3=4 7. 5 2 = True 3. 10 2=5 8. 7 3 = False 4. 9 3 = 27 9. 1 8 = False 5. 5 5 = 25 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. SPREADSHEETS 55  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. 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. 56 DIGITAL APPLICATIONS Lesson 2 2. 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. 3. 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 ( * ). SPREADSHEETS 57  Lesson 2 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. #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. 58 DIGITAL APPLICATIONS Lesson 2 ERROR VALUES #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. SPREADSHEETS 59  Lesson 2 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. 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. 60 DIGITAL APPLICATIONS Lesson 2 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. 2. Type the formula in the output cell. Here, the formula is typed on C2 to get the sum of the two numbers. SPREADSHEETS 61  Lesson 2 3. 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. 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. 7 62 DIGITAL APPLICATIONS Lesson 2 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. 2. Enter a new formula in D2, =C2*E7. 3. To copy the formula, use the fill handle and drag down until D6. SPREADSHEETS 63  Lesson 2 After using the fill handle tool, you will notice a zero (0) result. It is because a relative reference was used. 4. 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. 5. 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. 64 DIGITAL APPLICATIONS Lesson 2 6. 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. 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. SPREADSHEETS 65  Lesson 2 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. 66 DIGITAL APPLICATIONS Lesson 2 Step 1. Sample Data Step 2. Sum Function 3. Use relative referencing to copy the formula to the rest of cells. 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. SPREADSHEETS 67  Lesson 2 Let us enter scores for each student. 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. 68 DIGITAL APPLICATIONS Lesson 2 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 Drop-down list of functions quizzes. Use the fill handle to copy the formula to the rest of the cells. In the example on the next page, some results Automatic entry of COUNT function are not equal to five because the student missed some quizzes. SPREADSHEETS 69  Lesson 2 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. 2. =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. 70 DIGITAL APPLICATIONS 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. Round a Number to Three Decimal Places Round a Number to Two Decimal Places Round a Number to One Decimal Place Round a Number to the Nearest Integer SPREADSHEETS 71  Lesson 2 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). 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. 72 DIGITAL APPLICATIONS Lesson 2 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.” 2. Enter the IF function in the output cell. Here, type in =IF(B2>=75, “Passed”, “Failed”) in C2. 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. SPREADSHEETS 73  Lesson 2 The remark for C2 will be “Passed” because the statement is true for C2; the grade 75 is equal to the value set. 3. Use the fill handle to copy the formula. C4 is “Failed” because the grade 71 is not greater than or equal to 75. 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. 74 DIGITAL APPLICATIONS Lesson 2 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. You can find the full set of number formatting options if you click the Dialog Box Launcher in the corner of the Number group. SPREADSHEETS 75  Lesson 2 Let us try to format numbers with decimal places. Excel will display the decimal places you entered in the number. 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. 76 DIGITAL APPLICATIONS Lesson 2 Here, the decimal places of the prices in cell range B2:B4 will be displayed with two decimals. 2. Next, let us calculate first. Here, we compute for the total price of each item by multiplying the price by the quantity. Use the fill handle to copy the formula to D3 and D4. 3. To change the format to two decimal places, change the number format to Number in the Number group. SPREADSHEETS 77  Lesson 2 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. 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. 78 DIGITAL APPLICATIONS Lesson 2 The default decimal place is 2. Change it to 3 by entering 3 or clicking the arrow up. Click OK. The values will now display three decimal places rounded to the nearest thousandths. 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. SPREADSHEETS 79  Lesson 2 Then, the number format will become Accounting, and a thousands separator will be displayed in D4. Let us change the quantities and prices to see other results. 80 DIGITAL APPLICATIONS Lesson 2 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. SPREADSHEETS 81  Lesson 2 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. 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. 82 DIGITAL APPLICATIONS Lesson 2 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. 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. SPREADSHEETS 83  Lesson 2 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. For the first method, click Percent Style in the Number group. 84 DIGITAL APPLICATIONS Lesson 2 But the percentage displayed two additional decimal places. To solve this problem, simply reenter the original numbers 5, 10, and 50. The second method is to enter the numbers followed by a percent sign (%) to avoid reentering the numbers. 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. SPREADSHEETS 85  Lesson 2 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. For the total amount, multiply the discounted price to the quantity. First, freeze columns A, B, and C. Lastly, compute for the total price on all items, both with original prices and discounted prices. Use the SUM function in D5 and I5. 86 DIGITAL APPLICATIONS Lesson 2 Now let us change the appearance of cells in the spreadsheet and add colors and lines. 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. FONT GROUP SPREADSHEETS 87  Lesson 2 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. 88 DIGITAL APPLICATIONS Lesson 2 2. Apply the font Arial Narrow to all the cells. You can also apply font styles. Font style can be bold, italic, underline, or double underline. 3. Let us make all the headings bold. Click the B icon in the Font group. 4. Next, make A2:A4 italic. Click the I icon in the Font group. SPREADSHEETS 89  Lesson 2 5. Apply underline on D4 and I4, the last prices in the items. Click U in the Font group. 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. 6. 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. 90 DIGITAL APPLICATIONS Lesson 2 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. 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. 2. Apply a light blue color for the items under Items. SPREADSHEETS 91  Lesson 2 3. Next, color B2:E4 yellow and F2:I4 red. 4. For D5 and I5, let us make the background black and the text white. 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. 92 DIGITAL APPLICATIONS Lesson 2 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. 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. SPREADSHEETS 93  Lesson 2 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. 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. 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. 94 DIGITAL APPLICATIONS 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. 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. Insert a column beside column A and highlight the two cells. Click Merge & Center. SPREADSHEETS 95  Lesson 2 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. 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. 96 DIGITAL APPLICATIONS Lesson 2 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. SPREADSHEETS 97  lesson 2 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 1 B. Formula to get the remaining balance of each budget 2 3 C. Formula to get the total less price of each item in 20% discount (use absolute cell referencing) 4 5 98 DIGITAL APPLICATIONS  lesson 2 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. 3. Wrap Text A3 then adjust column widths. Then, highlight A3:D3, apply middle align vertically and align center horizontally. 4. Merge cell range B2:D2. SPREADSHEETS 99  Lesson 2 5. Type “Monthly Budget Planner” in A1. Highlight A1:D1 then apply the Merge & Center command. 6. Apply the same format below. Include TOTAL in A11. 100 DIGITAL APPLICATIONS Lesson 2 7. 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. 8. Enter a formula for the Total Budget, Total Actual, and Total Difference. Use the SUM function. 9. Enter a formula for the difference of each expense. If the answer is negative, color the font red. SPREADSHEETS 101  Lesson 2 10. Include the Balance of Total Income in A12:B12, which should be merged. 11. Enter a formula for the Balance of Total Income in C12. Total Income should be subtracted from the Total Actual. 12. 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. 102 DIGITAL APPLICATIONS  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. SPREADSHEETS 103  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. 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. 104 DIGITAL APPLICATIONS  lesson 3 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: Column charts use vertical bars to represent data. They are typically used to compare several items in a specific range of values. Bar charts work just like column charts, but they use horizontal bars instead of vertical bars. 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. SPREADSHEETS 105  Lesson 3 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 Learners Learner1 Learner2 To create a chart, you must Learner3 first have your data in the Learner4 worksheet. Let us create a Learner5 Learner6 dummy grading sheet for first Learner7 quarter. Learner8 Learner9 Learner10 Learner11 Learner12 Learner13 Learner14 Learner15 106 DIGITAL APPLICATIONS Lesson 3 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. 3. 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. Learners Learner1 Learner2 Learner3 Learner4 Learner5 Learner6 Learner7 Learner8 Learner9 Learner10 Le r10 ar 1 ar 2 ar 3 ar 4 5 Le er1 Le er2 Le er3 Le er4 Le er5 Le er6 Le er7 Le er8 Le er9 Le er1 Le er1 Le er1 Le er1 r1 ne ne n n n n n n n n n n n n n ar ar ar ar ar ar ar ar ar ar ar Le SPREADSHEETS 107  Lesson 3 To change a chart type, select the chart, click the Line Chart icon in the Charts group. Select Line in 2-D Line. To resize, simply select any corner of the chart then hold the mouse and resize. ar 0 ar 1 ar 2 ar 3 ar 4 5 Le er1 Le er2 Le er3 Le er4 Le er5 Le er6 Le er7 Le er8 Le er9 Le er1 Le er1 Le er1 Le er1 Le er1 r1 ne n n n n n n n n n n n n n n ar ar ar ar ar ar ar ar ar ar Le 108 DIGITAL APPLICATIONS Lesson 3 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. Learners Learner1 Learner2 4. Let us delete the first chart and Learner3 update our data. Learner4 Learner5 Learner6 5. Click Line Chart in the Charts Learner7 group and choose Line with Learner8 Learner9 Markers. Learner10 Learner11 Learner12 Learner13 Learner14 Learner15 SPREADSHEETS 109  Lesson 3 Since the data was updated, you will now see two lines: one for the First Quarter Grade and one for the Second Quarter Grade. Le r10 ar 1 ar 2 ar 3 ar 4 5 Le r7 Le r8 Le er1 Le er2 Le er3 Le er4 Le r5 Le r6 Le er9 Le er1 Le er1 Le er1 Le er1 r1 ne ne ne ne ne ne n n n n n n n n n ar ar ar ar ar ar ar ar ar ar ar Le 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.” 110 DIGITAL APPLICATIONS Lesson 3 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. 5 ar 4 ar 3 Le er12 Le r11 Le r10 Le er9 r1 Le r8 Le er1 Le r7 Le er1 Le r6 Le er5 Le er3 Le r4 r1 Le er2 ne ne ne ne ne ne ne ne n n n n n n n ar ar ar ar ar ar ar ar ar ar ar ar ar Le Le To edit the legend at the bottom, right-click the legend then choose Select Data. ar 4 5 Le er6 ar 5 Le er3 ar 4 ar 1 Le er2 Le e r 1 r1 Le ner Le ner r ne ne n n n n rn ar ar ar ar ea Le Le Then, choose the legend to be edited. Click Edit on the dialog box. SPREADSHEETS 111  Lesson 3 Learner1 Learner2 Learner3 In the Series Name, type First Quarter. Repeat the same procedure for Series 2. Then, click OK. The legend is now changed. 112 DIGITAL APPLICATIONS Lesson 3 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. ar 0 ar 1 ar 2 ar 3 ar 4 5 Le er1 Le er2 Le er3 Le er4 Le er5 Le er6 Le er7 Le er8 Le er9 Le r1 Le er1 Le er1 Le er1 Le er1 r1 ne ne n n n n n n n n n n n n n ar ar ar ar ar ar ar ar ar ar Le 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. SPREADSHEETS 113  Lesson 3 0 1 2 3 ar 14 5 r7 r8 r2 r3 r4 r5 r6 r9 r1 r1 r1 r1 r1 r1 r ne ne ne ne ne ne ne ne ne ne ne ne ne ne ne ar ar ar ar ar ar ar ar ar ar ar ar ar ar Le Le Le Le Le Le Le Le Le Le Le Le Le Le Le 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. 114 DIGITAL APPLICATIONS Lesson 3 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. PAGE SETUP GROUP 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. SPREADSHEETS 115  Lesson 3 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. Margins will now be visible for manual adjustment. Just click and drag the lines to apply changes. 116 DIGITAL APPLICATIONS Lesson 3 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. ar 1 ne 0 ar 2 ar 3 ar 4 5 Le er2 Le er3 Le r11 Le ner Le er1 Le er1 Le er1 Le er1 r1 Le r4 Le er5 Le r6 Le er7 Le r8 Le r9 ne ne ne ne ne n n ar n n n n n n ar ar ar ar ar ar ar ar ar Le ar SPREADSHEETS 117  Lesson 3 To fix this problem, you can put the chart after the data above or simply change the orientation from Portrait to Landscape. 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. 118 DIGITAL APPLICATIONS Lesson 3 You may use Show Margins to enlarge the scale. Scaling Options SPREADSHEETS 119  Lesson 3 HEADER AND FOOTER To add a header and footer, open the Page Setup dialog box. 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.