Lesson 1_ Familiarizing with Spreadsheet Application_0.pdf

Unit 4: Spreadsheet Skills Lesson 1: Familiarizing with Spreadsheet Application Contents Engage 1 Introduction 1 Objectives 2 Explore 2 Explain and Elaborate...

Unit 4: Spreadsheet Skills Lesson 1: Familiarizing with Spreadsheet Application Contents Engage 1 Introduction 1 Objectives 2 Explore 2 Explain and Elaborate 5 Introduction to Microsoft Excel 5 Excel Tabs and Ribbon 8 Spreadsheet Options 10 Working with Cells in Excel 12 Basic Formatting 14 Extend 16 Activity 1 16 Activity 2 17 Evaluate 18 Wrap Up 21 Photo Credits 22 Bibliography 23 Unit 4: Spreadsheet Skills Engage Introduction Fig. 1. Manual computation and representation of data printed on a paper. Are you familiar with spreadsheets? What do you think is the use of such a tool? Have you tried using it before? For what purpose did you use it? Do you think it makes data encoding and data representation easier? How so? Data can be represented and computed manually. You can create visuals and drawings of charts and graphs to show your classmate or teacher a specific information or a set of data but that process takes so much time and effort. Today’s rise of technology gave us so much power in terms of data manipulation and representation. 1 Unit 4: Spreadsheet Skills Microsoft Excel is the most popular tool used by students and professionals alike for data comparison and calculations. It is easier to represent data using a spreadsheet because you can also place graphs and charts on it. It is a powerful tool but oftentimes intimidating to use especially for beginners. Even so, it is good to learn using spreadsheets for the industry’s standards. Another alternative that you can use is Google Sheets. Objectives In this lesson, you should be able to do the following: Define basic terms related to Excel. Identify the different parts of an Excel document. Discuss the different tabs found on the ribbon. Demonstrate the basics in using cells. Perform basic formatting. DepEd Competency Uses common productivity tools effectively by maximizing advanced application techniques. (CS_ICT11/12-ICTPT-Ic-d-4) Explore 15 minutes Data is everywhere and is used to draw conclusions and comparisons with other data available. With the growing trend in technology and growing amount of data, it is necessary to use tools such as Excel and Google Sheets. But even before these technologies, people are able to represent data using drawings for better understanding. Form a group of three and fill in the table below. You will gather the data needed to show one’s daily allowance and break it down according to the table below. Compare these data 2 Unit 4: Spreadsheet Skills from the data you have gathered from your other classmates. Compute the daily total expenses. Student Name Food Transportation Savings Others TOTAL Expenses Cost After gathering data and filling out the table above, create a simple bar graph to compare your expenses with your classmates. Make sure to place proper legends to avoid confusion. Guide Questions 1. Who among the three of you has the biggest savings from your daily allowance? Who among you loves to eat thus having bigger expenses on food? Who among you has the biggest expenses on miscellaneous? 3 Unit 4: Spreadsheet Skills 2. How did you compute the daily total expenses for you and your classmates? Was it easy? How about if you need to gather and compute the total expenses of twenty (20) of your classmates, will it be easy? Why or why not? 3. Do you think the data was easier to understand with a graph? Why or why not? Do you think using Microsoft Excel or Google Sheets in representing data like the one in your activity will make any difference? 4 Unit 4: Spreadsheet Skills Explain and Elaborate Introduction to Microsoft Excel Excel ​is a spreadsheet software used to input and analyze data. It contains grids where you can place values. Although it is commonly used for computations, you can also place images, graphs, and charts to represent various data in a document for easier understanding and presentation. Basic Excel Terms Before starting with Excel, you should know some basic terms that are often used in the program. Being familiar with these terms will help you throughout this lesson. Workbook and Spreadsheet Workbook is the document itself when you open your Excel. ​Spreadsheet on the other hand is just one portion of your document. A workbook can contain many spreadsheets. Think of it as a simple book. Workbook is the book itself and the spreadsheets are the pages of that book containing lessons and activities. Formulas and Functions Formulas in Excel works as your normal arithmetic operation but you need to place an equal sign (=) before performing any operation between two or more values. ​Functions on the other hand are specialized operations with assigned names that use predefined formulas and return a value. Are you familiar with the terms discussed above? Can you give a real life example to differentiate each term from one another? 5 Unit 4: Spreadsheet Skills Parts of an Excel Document Open Microsoft Excel in your computer and compare it to the image below to know the different parts. Fig. 2. Microsoft Excel with labeled parts. Cell Cell​, in spreadsheet context, is a rectangular box where you can input your values. Each cell can contain texts, numbers, images, or formulas. Row Number Row Number shows the number in a row of the currently selected cell. These numbers are arranged in numeric order. 6 Unit 4: Spreadsheet Skills Column Letter Column Letter ​shows the letter in a column of the currently selected cell. These letters are in alphabetical order from A-Z and will have a combination afterwards such as AA, AB, AC, and so on. Name Box Name Box ​shows the cell currently selected if you are selecting only one ​cell​. It also shows the number of rows and columns if you are trying to select a big portion of your document which contains several cells. The name of a cell is composed of a ​column letter and a ​row number​. Examples are A1, B2, J2, and MJ218 for a bigger data set. Formula Bar Formula Bar ​is the long rectangle beside the name box where you can input your formulas for your data. You can also use this to input a value in a cell. Ribbon Ribbon is found on the top of the grid in Excel. You can find different buttons and tools to edit and customize your data. There are different tools in each tab. For example, in the Home tab you can find the tools to customize the font size, font color, and the alignment. Quick Access Toolbar Quick Access Toolbar allows you to access frequently used tools such as ​save​, ​undo​, and redo​. You can customize this to add more tools depending on your needs. Spreadsheet Tab Spreadsheet Tab​, also known as sheets, is where you can select a different sheet to look at its contents. The current sheet you are working on is highlighted to avoid confusion. This also allows you to add more sheets. 7 Unit 4: Spreadsheet Skills Status Bar Status Bar ​shows different information about the document. You can set the layout of how you like your spreadsheet to appear on your screen. You can also zoom in and out the document especially if you have a lot of data needed to be shown in just one screen. Excel Tabs and Ribbon The different tabs found on the top of the Excel application contain different tools to customize your data and table. You can check each tab for different tools depending on your needs. You can also customize ribbons to remove or add hidden tools. Ribbons and tabs can also be found on other Microsoft Office applications such as Word and Powerpoint. Fig. 3. Different tabs and tools found on the ribbon of Excel. What are common tools you are familiar with? What are the tools you usually see with other spreadsheet applications like Excel? With the use of tabs, it is easier to find the tools you need since they are grouped according to their functions. It might take a while to get familiarize yourself with all the tools especially for beginners but it will get easier as you practice. Read below the description of each tab for an easier reference as you go through your Excel application. You can check each tab to familiarize yourself. 8 Unit 4: Spreadsheet Skills File Tab File Tab ​shows tools for opening an existing workbook, creating a new workbook, saving a workbook, printing a workbook, and other functional tools that are not necessarily connected to data manipulation or formatting. Home Tab Home Tab ​gives you tools to format your table and its data You can change the font size, font color, alignment, and other basic formatting options. You can also copy, paste, insert, and delete cells using the tools in this tab. Insert Tab Insert Tab ​gives you tools to insert objects such as images, shapes, tables, graphs, and charts. There are predefined templates available for graphs and charts that can be found on this tab. Draw Tab Draw Tab ​gives you tools so that you can draw flexibly on your table or charts when discussing it. It contains different drawing tools such as pen tool, eraser tool, highlighter, and lasso selection tool. Page Layout Tab Page Layout Tab ​gives you tools and commands to format the orientation of your spreadsheet on the screen and the paper after printing. These tools allow you to edit the print area, margins, colors, etc. Formulas Tab Formulas Tab ​gives you commands to edit, define, audit, and control formulas. You can easily find functions and formulas here because they are grouped depending on the use. 9 Unit 4: Spreadsheet Skills Data Tab Data Tab ​gives you commands to connect to external data or databases to import pre-existing data tables. There are also commands that can help you sort data; this is particularly useful for large data sets. Review Tab Review Tab ​gives you tools and commands for spell checking, sheet protection, workbook protection, and changes tracking. It helps you to protect the data and check the accuracy of the input in your spreadsheet through proofreading. View Tab View Tab ​gives you commands to control the view of your spreadsheet and how you would interact with it. Remembering the warm up activity earlier, what tab do you need to check in order to solve the total expenses of each of your classmates? What about for the creation of the graph? Spreadsheet Options The function of a spreadsheet is to store and analyze data in an organized manner so that you can retrieve it easier for future use. It can be used for a small data set but oftentimes it is used by companies with large amounts of data to be evaluated. It is also used by your teachers in computing your grades which requires more complex functions than simple arithmetic formulas. Excel is a great tool in these situations because there are options and ways to make the spreadsheet more organized. 10 Unit 4: Spreadsheet Skills Fig. 4. Spreadsheets in a workbook and the different options for the sheet. The following are some actions you can find useful when working in Excel: To ​add a spreadsheet​, just click the plus sign at the bottom beside the last sheet. To ​rearrange spreadsheets​, just hold the sheet tab and drag to the position you prefer in the workbook. To ​rename a spreadsheet​, just double-click the sheet tab and type the new title of your sheet. The default name is Sheet1, Sheet2, Sheet3, and so on. To ​color a spreadsheet tab​, just right-click the sheet tab and go to the ​Tab Color option then select a color to apply on your sheet. To ​protect a spreadsheet​, just right-click the sheet tab and go to the ​Protect Sheet option and select all the options you want that apply to the sheet you are working on. To ​move or copy a spreadsheet​, just right-click the sheet tab and go to the ​Move or Copy ​option. You can then move the sheet to a different position in the workbook, move it to a different workbook, or make a copy. To ​delete a spreadsheet​, just right-click the sheet tab and click D ​ elete​. 11 Unit 4: Spreadsheet Skills Working with Cells in Excel As we have discussed, cells contain the data you have input. There are instances where you want to select several cells for data formatting or performing functions, which we will discuss how you can do those things. Select an Entire Row or Column Try to move your mouse pointer along the letters for the columns and along the numbers for the rows. A black arrow pointing downward or sideward will appear for the column and row respectively. Click on your selected letter or number to select an entire column or row. Fig. 5. An entire column is selected in the spreadsheet. Select a Group of Cells There are different ways to select a group of cells and you can use these following actions depending on the data you need. If you want to ​select adjacent rows​, just click the number of the first row you need, selecting an entire row, and drag it until you reach the last row you need. 12 Unit 4: Spreadsheet Skills If you want to ​select adjacent columns​, just click the letter of the first column you need, selecting an entire column, and drag it until you reach the last column you need. If you want to ​select a group in the middle of the table​, just click the start cell of the group and drag it until the portion that contains the values. If you want to ​select scattered cells​, just press ​Ctrl on your keyboard and select the cells you need. It will show a grey selection area for all the cells that you have selected so far. Insert or Delete a Row or Column It is easy to add or remove rows or columns in Excel. First, select an entire row or column using the instruction for selecting rows and columns. Then, right-click and go to ​Insert to add or ​Delete ​to remove. The cells below the row or beside the column you selected will move after performing either one. Adjusting the Size of a Row or Column Adjusting the cell size is important so that you can see clearly the input values in your spreadsheet. There are two ways to do this: The first way is to hover over the lines in between either on the column letters or row numbers. The cursor should turn into the resize cursor, which looks like a cross with a double arrow. When the resize cursor appears, drag the line based on your preferred width or height. Fig. 6. One way of resizing a row using the resize mouse cursor. 13 Unit 4: Spreadsheet Skills The second way is to select the row or column you want to resize. Right-click after the selection and go to either ​Column Width or ​Row Height​. A pop-up will appear and you can place the height or width that you like. Fig. 7. Resizing the column using the command Column Width with an input. Have you tried other spreadsheet tools, aside from Excel before? What are those? Do they have the same functionalities as discussed? Basic Formatting You can format your data inputs in Excel to make it more understandable. Just like in Microsoft Word or Powerpoint, you can edit text and fill colors to emphasize specific categories or groups in your spreadsheet. You can also format numbers. Font and Colors You can find the tools to format the looks of your font or cell on the ​Home tab. You can change the font size, font color, alignment, and even the fill color of the cells here. 14 Unit 4: Spreadsheet Skills Fig. 8. Formatting options found on the Home tab for changing the look of the spreadsheet. Number Formats You can also find ​Number Formats in the Home tab. These formatting options are useful if you have a date that you want to match to a specific format like Month-Day-Year, a decimal point that only shows two (2) decimal values for a scientific computation, or show the currency for a company’s expenses in a month. Fig. 9. Number formattings available in Excel. 15 Unit 4: Spreadsheet Skills Extend Activity 1 Open Microsoft Excel on your computer. The tools listed below are tools found on different tabs on the ​Ribbon​. Check each tab on your spreadsheet application and find the specified tool below. Categorize each tool based on the tab where you can find it. Write your answers in the table provided below. Print Table Font Size Thesaurus Lasso Save As Lookup & Calculate Selection Get Data Reference Now Insert Bar From Access Translate Page Break Chart Sort Adjust Preview Cell Styles Margins Print Area File Tab Home Tab Insert Tab Draw Tab Page Layout Tab Formulas Tab Data Tab Review Tab View Tab 16 Unit 4: Spreadsheet Skills ​ uide G Click the tab you want to check (e.g. ​View Tab​) and the different tools will show up. Hover your mouse pointer to the icon of the tool and a tooltip will appear showing the name of the tool and its function. Activity 2 Give at least five (5) fields where Spreadsheet is useful. Explain why and how it is useful in those fields. ​ uide G You may use the internet to research different fields that use Spreadsheets. 17 Unit 4: Spreadsheet Skills Evaluate A. Study the image below. Identify each part and explain its use. 1. What part of Excel is being shown in number 1? What is its function? 18 Unit 4: Spreadsheet Skills 2. What part of Excel is being shown in number 2? What is its function? 3. What part of Excel is being shown in number 3? What is its function? 4. What part of Excel is being shown in number 4? What is its function? 5. What part of Excel is being shown in number 5? What is its function? B. In this activity, you will need to open Microsoft Excel or any spreadsheet application you have. With your knowledge in adjusting cells and basic formatting, create a simple board game using your chosen spreadsheet application. Some suggestions that you can start from are ​Chess​, ​BattleShips​, or ​Scrabble​. Use the space provided below for drawing your idea. After finalizing your idea, you can start creating your own game. Save a soft 19 Unit 4: Spreadsheet Skills copy and present your game in front of the class. Wrap Up ___________________________________________________________________________________________ Workbook is the document itself when you open your Excel. ​Spreadsheet on the other hand is just one portion of your document. Formulas in Excel works as your normal arithmetic operations but you need to place an equal sign (=) before performing any operation between two or more values. 20 Unit 4: Spreadsheet Skills Functions on the other hand are specialized operations with assigned names that use predefined formulas and return a value. As you open an Excel document, you will see the following parts: ○ Cell​ that contains the input values ○ Row Number ○ Column Letter ○ Name Box that shows the name of the selected cell which contains a column letter and a row number ○ Formula Bar​ for the computations ○ Ribbon​ which contains tools ○ Quick Access Toolbar that shows the commonly used tools such as Undo, Redo, and Save ○ Spreadsheet Tab where you can see the different sheets available on your workbook ○ Status Bar where you can see the current status of your document and the view shortcuts The ​Ribbon contains different ​Tabs that contain the different tools depending on your needs. The different tabs you can check are ​File​, Home​, Insert​, Draw​, Page Layout​,​ Formulas​,​ Data​,​ ​and​ Review​. Spreadsheets are important because they contain the data. You can add, delete, move, customize, protect, and edit these sheets through S ​ preadsheet Options​. Cells contain the values you have input. There are ways to adjust, select, add, or delete cells based on your needs. Basic formatting​ allows you to change the look of your data tables. ___________________________________________________________________________________________ Photo Credits Hands working with office items Free Photo by ​osaba is licensed under ​Freepik License via Freepik​. 21 Unit 4: Spreadsheet Skills Bibliography Alexander, Michael. “Working with Excel 2013's Ribbon Menus.” InformIT, June 19, 2013. https://www.informit.com/articles/article.aspx?p=2067634 “Excel: Using the Draw Tab.” GCFGlobal.org. Accessed March 30, 2020. https://edu.gcfglobal.org/en/excel/using-the-draw-tab/1/ “Getting Started with Excel 2010.” University of Michigan Library, March 8, 2011. https://guides.lib.umich.edu/ld.php?content_id=11412292 ScholarSpace. “Using Microsoft Excel: Getting Started.” Research Guides. University of Michigan Library, June 17, 2019. https://guides.lib.umich.edu/c.php?g=283162&p=1886441 Writtenhouse, Sandy. “The Beginner's Guide to Microsoft Excel.” MakeUseOf, April 2, 2018. https://www.makeuseof.com/tag/beginners-guide-microsoft-excel/ 22