MS Excel Tutorial PDF

CSS 9 CLASS Teacher: Ms. Mary Rose Llamas 1  Lord, we thank You for this wonderful day that You gave. We thank You for another beautiful morning. As we go on through our lessons today, may You make us instruments to do good things....

CSS 9 CLASS Teacher: Ms. Mary Rose Llamas 1  Lord, we thank You for this wonderful day that You gave. We thank You for another beautiful morning. As we go on through our lessons today, may You make us instruments to do good things. Please enlighten our minds, give us strength to participate in our subject today. In Jesus' Name, we pray, Amen! 2  Prepare a short game and quiz 3 MS Excel 4  What is MS Excel? It is a spreadsheet program developed by Microsoft. Excel is a spreadsheet program that allows you to store, organize, and analyze information. Excel organizes data in columns and rows and allows you to do mathematical functions. 5  What is MS Excel? It runs on Windows, macOS, Android and iOS. The first version was released in 1985 and has gone through several changes over the years. An MS Excel file is saved with an extension of.xls 6  Benefits of Using MS Excel 1. It is the most popular spreadsheet program in the world. 2. Easy To Store Data: Since there is no limit to the amount of information that can be saved in a spreadsheet, MS Excel is widely used to save data or to analyse data. Filtering information in Excel is easy and convenient. 7  Benefits of Using MS Excel 3. Easy To Recover Data: If the information is written on a piece of paper, finding it may take longer, however, this is not the case with excel spreadsheets. Finding and recovering data is easy. 8  Benefits of Using MS Excel 4. Application of Mathematical Formulas: Doing calculations has become easier and less time- taking with the formulas option in MS excel. 9  Benefits of Using MS Excel 5. More Secure: These spreadsheets can be password secured in a laptop or personal computer and the probability of losing them is way lesser in comparison to data written in registers or piece of paper. 10  Benefits of Using MS Excel 6. Data at One Place: Earlier, data was to be kept in different files and registers when the paperwork was done. Now, this has become convenient as more than one worksheet can be added in a single MS Excel file. 11  Benefits of Using MS Excel 7. Neater and Clearer Visibility of Information: When the data is saved in the form of a table, analyzing it becomes easier. Thus, information is a spreadsheet that is more readable and understandable. 12  Excel is typically used for: 1.Data Organization − Excel allows users to store and organize large data. It provides various options such as filtering, sorting, and many others to manage the data. 13  Excel is typically used for: 2. Data Analysis − Excel provides many different tools to perform statistical analysis few of them are pivot tables, formulas, and data modeling. It allows users to perform the mathematical calculations. 14  Excel is typically used for: 3.Automation − Excel allows users to use VBA and macro. This helps in automating the task by reducing the possibility of human error. 15 Excel is typically used for: 4.Reporting − Excel allows users to create professional-looking reports with tables, charts, and text. 16  Excel is typically used for: 5. Data Sharing and Collaboration − Excel makes file sharing an easy task. Multiple users can work on the same document which increases data integrity. 17 PARTS OF MS EXCEL 18 The Excel interface When you open Excel 2016 for the first time, the Excel Start Screen will appear. From here, you'll be able to create a new workbook, choose a template, and access your recently edited workbooks. 19 20 A. The Backstage view (File Tab) gives you various options for saving, opening a file, printing, and sharing your document. 21 B. The following are the TABS in MS Excel: 22 1. Home Comprises options like font size, font styles, font color, background color, alignment, formatting options and styles, insertion and deletion of cells and editing options. 23 2. Insert Comprises options like table format and style, inserting images and figures, adding graphs, charts and sparklines, header and footer option, equation and symbols. 24 3. Page Layout Themes, orientation and page setup options are available under the page layout option. 25 4. Formulas Since tables with a large amount of data can be created in MS excel, under this feature, you can add formulas to your table and get quicker solutions. 26 5. Data Adding external data (from the web), filtering options and data tools are available under this category. 27 6. Review Proofreading can be done for an excel sheet (like spell check) in the review category and a reader can add comments in this part. 28 7. View Different views in which we want the spreadsheet to be displayed can be edited here. Options to zoom in and out and pane arrangement are available under this category. 29 C. The Ribbon It contains multiple tabs, each with several groups of commands. 30 D. The Quick Access Toolbar By default, it includes the Save, Undo, and Redo commands. You can add other commands depending on your preference. 31 E. Title bar Displays the name of the spreadsheet and the application 32 F. Cells Where data and formulas are entered in the spreadsheet Each cell has a cell address 33 G. Name box A small box located to the left of the formula bar 34 H. Formula bar is an entry bar located beneath the ribbon. It displays the current cell's information and may also be used to 35 insert a formula. I. Column headings Letters that identify the columns in the worksheet. From A to XFD and have a total of 16,384 columns. 36 J. Row headings Numbers that identify the rows in the worksheet. Starts from 1 to 1,048,576 rows. 37 K. Spreadsheet area It is the place where we enter our data. It includes all the rows, cells, columns. 38 L. Scroll bars Tools that enable us to move the document's vertical and horizontal views. 39 M. Sheet tabs / Leaf bar Tabs that display the name of the worksheet 40 N. Status bar Located at the bottom of the Excel window, it displays information about the current status of Excel. 41 O. Worksheet views To change worksheet views, locate the commands in the bottom-right corner of the Excel window and select Normal view, Page Layout view, or Page Break view. 42 O. Worksheet views 1. Normal view is the default view for all worksheets in Excel. 43 O. Worksheet views 2. Page Layout view displays how your worksheets will appear when printed. You can also add headers and footers in this 44 view. O. Worksheet views 3. Page Break view allows you to change the location of page breaks, which is especially helpful when printing a lot 45 of data from P. Zoom Control It enables us to ZOOM-IN or ZOOM-OUT a specific area of the spreadsheet. 46  M R Q. Control buttons Symbols that are present in the upper-right side of the window, to change the labels, minimize, maximize, share, and close the sheet. 47 Introduction Excel files are called workbooks. Whenever you start a new project in Excel, you'll need to create a new workbook. The sheet on which you work is called a Worksheet. 48 Introduction Multiple worksheets can be added in a single Excel file. There are several ways to start working with a workbook in Excel. You can choose to create a new workbook —either with a blank workbook or a predesigned template—or open an existing workbook. 49 Understanding cells Every worksheet is made up of thousands of rectangles, which are called cells. A cell is the intersection of a row and a column —in other words, where a row and column meet. 50 Understanding cells Columns are identified by letters (A, B, C), while rows are identified by numbers (1, 2, 3). Each cell has its own name—or cell address—based on its column and row. In the example, the selected cell intersects column C 51 and row 5, so the cell address is C5. Understanding cells Note that the cell address also appears in the Name box in the top-left corner, and that a cell's column and row headings are highlighted when the cell is selected. 52 Understanding cells You can also select multiple cells at the same time. A group of cells is known as a cell range. Rather than a single cell address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, separated by a colon. For example, a cell range that included cells A1, A2, A3, A4, and A5 would be 53 written as A1:A5. Cell content a. Text: Cells can contain Any information text, such as letters, you enter into a numbers, and dates. spreadsheet will be stored in a cell. Each cell can contain different types of content, including text, formatting, formulas, and functions. 54 b. Formatting attributes: Cells can contain formatting attributes that change the way letters, numbers, and dates are displayed. For example, percentages can appear as 0.15 or 15%. You can even change a cell's text or 55 background color. c. Formulas and functions: Cells can contain formulas and functions that calculate cell values. In our example, SUM(B2:B8) adds the value of each cell in the cell range B2:B8 and displays the total in cell B9. 56  Formulas A formula in Excel is used to do mathematical calculations. Formulas always start with the equal sign (=) typed in the cell, followed by your calculation. It can also be used to calculate values using cells as input. Formulas can be used for calculations such as: =1+1 =2*2 =4/2=2 57  Creating formulas Select a cell Type the equal sign (=) Select a cell or type value Enter an arithmetic operator Select another cell or type value Press enter For example =1+1 is the formula to calculate 1+1=2 58  Formatting Why format and style your spreadsheet? 1. Colors 1. Make it easier to read and understand 2. Fonts 2. Make it more delicate 3. Borders 4. Number formats 5. Grids The most used styling functions are: 59  Format Painter The format painter is a command which lets you copy formatting from one cell to another. The Format painter can be used to copy to single cells or ranges. Format Painter is used by clicking on its button in the Ribbon, found in the Clipboard group. 60 https://www.w3schools.com/excel/  Format Painter How To Use the Format Painter 1. Select the cell that you want to copy 2. Click the Format Painter button 3. Select a cell or range 61 https://www.w3schools.com/excel/  Format Colors Colors are specified by selection or by using Hexadecimal and RGB codes. A hexadecimal color is specified with: #RRGGBB. RR (red), GG (green) and BB (blue) are hexadecimal integers between 00 and FF specifying the intensity of the color. For example, #0000FF is displayed as blue, because the blue component is set to its highest value (FF) and the others are set to 00. 62  Format Colors An RGB color value is specified with: rgb( RED , GREEN , BLUE ). Each parameter defines the intensity of the color as an integer between 0 and 255. For example, rgb(0,0,255) is rendered as blue, because the blue parameter is set to its highest value (255) and the others are set to 0. 63  Format Borders The Borders menu is accessed in the Ribbon, in the Font group. Borders are added by clicking the Borders button. The default border is black underline. Changing the border type, style or color is a choice you make. The option button next to the Border command gives options for more types of borders. Clicking the option button gives an overview of the different border options. 64  Number Formats The default Number format is General. Number formats can be changed by clicking the Number format dropdown, accessed in the Ribbon, found in the Numbers group. Examples of number formats: 1. General 2. Number 3. Currency 4. Time 65 https://www.w3schools.com/excel/