BIL125_all copy.pdf

BIL125 – INFORMATION TECHNOLOGIES - II MS EXCEL Asst. Prof. Dr. Alper FIDAN 1 BIL125 – INFORMATION TECHNOLOGIES - II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected]...

BIL125 – INFORMATION TECHNOLOGIES - II MS EXCEL Asst. Prof. Dr. Alper FIDAN 1 BIL125 – INFORMATION TECHNOLOGIES - II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1 BIL125 – INFORMATION TECHNOLOGIES – II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1 BIL125 – INFORMATION TECHNOLOGIES – II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1  Contents Spell Check Page Layout & Printing 2  1. Introduction Before sharing a workbook, you'll want to make sure it doesn't include any spelling errors. Fortunately, Excel includes a spell check tool you can use to make sure everything in your workbook is spelled correctly. If you've used the spell check feature in Microsoft Word, just be aware that the spell check tool in Excel, while helpful, is not as powerful. For example, it won't check for grammar issues or check spelling as you type. 3  2. To use spell check: 1. From the Review tab, click the Spelling command. 2. The Spelling dialog box will appear. For each spelling error in your worksheet, it will try to offer suggestions for the correct spelling. Choose a suggestion, then click Change to correct the error. 4 2.To use spell check: 5  2.To use spell check: 3. A dialog box will appear after reviewing all spelling errors. Click OK to close spell check. If there are no appropriate suggestions, you can also enter the correct spelling manually. 6  2.To use spell check: 4. Spell check isn't always correct. It will sometimes mark certain words as incorrect even if they're spelled correctly. This often happens with names, which may not be in the dictionary. You can choose not to change a spelling "error" using one of the following three options: Ignore Once: This will skip the word without changing it. Ignore All: This will skip the word without changing it and also skip all other instances of the word in your worksheet. Add to Dictionary: This adds the word to the dictionary so it will never appear as an error again. Make sure the word is spelled correctly before choosing this option. 7 Page Layout and Printing 8  3. Page Layout and Printing There may be times when you want to print a workbook to view and share your data offline. Once you've chosen your page layout settings, it's easy to preview and print a workbook from Excel using the Print pane. 9  3.1 To access the Print pane 1. Select the File tab. Backstage view will appear. 2. Select Print. The Print pane will appear. 10  3.2 To print a workbook 1. Navigate to the Print pane, then select the desired printer. 2. Enter the number of copies you want to print. 3. Select any additional settings if needed (see above interactive). 4. Click Print. 11  4. Choosing a print area Before you print an Excel workbook, it's important to decide exactly what information you want to print. For example, if you have multiple worksheets in your workbook, you will need to decide if you want to print the entire workbook or only active worksheets. There may also be times when you want to print only a selection of content from your workbook. 12  5. Print active sheets: Worksheets are considered active when selected. 1. Select the worksheet you want to print. To print multiple worksheets, click the first worksheet, hold the Ctrl key on your keyboard, then click any other worksheets you want to select. 2. Navigate to the Print pane. 3. Select Print Active Sheets from the Print Range drop-down menu. 4. Click the Print button. 13  6. To print the entire workbook 1.Navigate to the Print pane. 2.Select Print Entire Workbook from the Print Range drop-down menu. 3.Click the Print button. 14  7.Printing a selection In our example, we'll print the records for the top 40 salespeople on the Central worksheet. 1. Select the cells you want to print. 15  7.Printing a selection 2. Navigate to the Print pane. 3. Select Print Selection from the Print Range drop-down menu. 16  7.Printing a selection 4. A preview of your selection will appear in the Preview pane. 5. Click the Print button to print the selection. If you prefer, you can also set the print area in advance so you'll be able to visualize which cells will be printed as you work in Excel. Simply select the cells you want to print, click the Page Layout tab, select the Print Area command, then choose Set Print Area. Keep in mind that if you ever need to print the entire workbook, you'll need to clear the print area. 17  8. Adjusting content On occasion, you may need to make small adjustments from the Print pane to fit your workbook content neatly onto a printed page. The Print pane includes several tools to help fit and scale your content, including scaling and page margins. 18  8.1 To change page orientation Excel offers two page orientation options: Landscape orients the page horizontally, while portrait orients the page vertically. In our example, we'll set the page orientation to landscape. 1.Navigate to the Print pane. 2.Select the desired orientation from the Page Orientation drop-down menu. In our example, we'll select Landscape Orientation. 19  8.1 To change page orientation 3. The new page orientation will be displayed in the Preview pane. 20  8.2 To fit content before printing If some of your content is being cut off by the printer, you can use scaling to fit your workbook to the page automatically. 1. Navigate to the Print pane. In our example, we can see in the Preview pane that our content will be cut off when printed. 21  8.2 To fit content before printing 2. Select the desired option from the Scaling drop-down menu. In our example, we'll select Fit All Columns on One Page. 3. The worksheet will be condensed to fit onto a single page. 22  Attention!!! Keep in mind that worksheets will become more difficult to read as they are scaled down, so you may not want to use this option when printing a worksheet with a lot of information. In our example, we'll change the scaling setting back to No Scaling. 23  To include Print Titles: If your worksheet uses title headings, it's important to include these headings on each page of your printed worksheet. It would be difficult to read a printed workbook if the title headings appeared only on the first page. The Print Titles command allows you to select specific rows and columns to appear on each page. 24  To include Print Titles 1. Click the Page Layout tab on the Ribbon, then select the Print Titles command. The Page Setup dialog box will appear. From here, you can choose rows or columns to repeat on each page. In our example, we'll repeat a row first. 25  To include Print Titles The Page Setup dialog box will appear. From here, you can choose rows or columns to repeat on each page. In our example, we'll repeat a row first. Click the Collapse Dialog button next to the Rows to repeat at top: field. 26  To include Print Titles The cursor will become a small selection arrow, and the Page Setup dialog box will be collapsed. Select the row(s) you want to repeat at the top of each printed page. In our example, we'll select row 1. Row 1 will be added to the Rows to repeat at top: field. Click the Collapse Dialog button again. 27  To include Print Titles 1.The Page Setup dialog box will expand. To repeat a column as well, use the same process shown in steps 4 and 5. In our example, we've selected to repeat row 1 and column A. 2.When you're satisfied with your selections, click OK. 28  To include Print Titles In our example, row 1 appears at the top of every page, and column A appears at the left of every page. 29  To adjust page breaks: Click the Page Break Preview command to change to Page Break view. Vertical and horizontal blue dotted lines denote the page breaks. Click and drag one of these lines to adjust that page break. 30  To adjust page breaks: In our example, we've set the horizontal page break between rows 21 and 22. 31  To adjust page breaks: In our example, all the pages now show the same number of rows due to the change in the page break. 32 To modify margins in the Preview pane: A margin is the space between your content and the edge of the page. Sometimes you may need to adjust the margins to make your data fit more comfortably. You can modify page margins from the Print pane. Left Margin Right Margin 33 To modify margins in the Preview pane: 1.Navigate to the Print pane. 2.Select the desired margin size from the Page Margins drop-down menu. In our example, we'll select Narrow Margins. 34 To modify margins in the Preview pane: The new page margins will be displayed in the Preview pane. You can adjust the margins manually by clicking the Show Margins button in the lower-right corner, then dragging the margin markers in the Preview pane. 35 SOURCES: https://edu.gcfglobal.org/en/excel2016/ https://www.excel-easy.com/ 36 BIL125 – INFORMATION TECHNOLOGIES – II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1  CONTENTS About OneDrive Saving and opening files Save and Save As AutoRecover Exporting workbooks Sharing workbooks  1. Introduction Whenever you create a new workbook in Excel, you'll need to know how to save it in order to access and edit it later. As with previous versions of Excel, you can save files locally to your computer. You can also save a workbook to the cloud using OneDrive, as well as export and share workbooks with others directly from Excel. 3  2. About OneDrive Whenever you're opening or saving a workbook, you'll have the option of using your OneDrive, which is the online file storage service included with your Microsoft account. To enable this option, you'll need to sign in to Office. 4  2. About OneDrive Many of the features in Microsoft Office are geared toward saving and sharing files online. OneDrive is Microsoft’s online storage space that you can use to save, edit, and share your documents and other files. You can access OneDrive from your computer, smartphone, or any of the devices you use. Once you have a Microsoft account, you'll be able to sign in to Office. Just click Sign in in the upper-right corner of the Excel window. 5  2.1 Benefits of using OneDrive Once you’re signed in to your Microsoft account, there are a few of the things you’ll be able to do with OneDrive: Access your files anywhere: When you save your files to OneDrive, you’ll be able to access them from any computer, tablet, or smartphone that has an Internet connection. You'll also be able to create new documents from OneDrive. Back up your files: Saving files to OneDrive gives them an extra layer of protection. Even if something happens to your computer, OneDrive will keep your files safe and accessible. Share files: It’s easy to share your OneDrive files with friends and coworkers. You can choose whether they can edit or simply read files. This option is great for collaboration because multiple people can edit a document at the same time (this is also known as co-authoring). 6  3. Saving and opening files When you’re signed in to your Microsoft account, OneDrive will appear as an option whenever you save or open a file. You still have the option of saving files to your computer. However, saving files to your OneDrive allows you to access them from any other computer, and it also allows you to share files with friends and coworkers. For example, when you click Save As, you can select either OneDrive or This PC as the save location. 7  4. Save and Save As Excel offers two ways to save a file: Save and Save As. These options work in similar ways, with a few important differences: Save: When you create or edit a workbook, you'll use the Save command to save your changes. You'll use this command most of the time. When you save a file, you'll only need to choose a file name and location the first time. Then you can just click the Save command to save it with the same name and location. Save As: You'll use this command to create a copy of a workbook while keeping the original. When you use Save As, you'll need to choose a different name and/or location for the copied version. 8  4.1 To save a workbook: It's important to save your workbook whenever you start a new project or make changes to an existing one. Saving early and often can prevent your work from being lost. You'll also need to pay close attention to where you save the workbook so it will be easy to find later. 1. Locate and select the Save command on the Quick Access Toolbar. 9  4.2 To save a workbook: 2. If you're saving the file for the first time, the Save As pane will appear in Backstage view. 3. You'll then need to choose where to save the file and give it a file name. To save the workbook to your computer, select Computer, then click Browse. You can also click OneDrive to save the file to your OneDrive. 10  4.2 To save a workbook: 4. The Save As dialog box will appear. Select the location where you want to save the workbook. 5. Enter a file name for the workbook, then click Save. You can also access the Save command by pressing Ctrl+S on your keyboard. 11  4.3 Using Save As to make a copy If you want to save a different version of a workbook while keeping the original, you can create a copy. For example, if you have a file named Sales Data, you could save it as Sales Data 2 so you'll be able to edit the new file and still refer back to the original version. To do this, click the Save As command in Backstage view. Just like when saving a file for the first time, you'll need to choose where to save the file and give it a new file name. ue.aydin.edu.tr 12  4.4 To change the default save location If you don't want to use OneDrive, you may be frustrated that OneDrive is selected as the default location when saving. If you find it inconvenient to select Computer each time, you can change the default save location so Computer is selected by default. 13  4.4 To change the default save location 1. Click the File tab to access Backstage view. 2. Click Options. 3. The Excel Options dialog box will appear. Select Save, check the box next to Save to Computer by default, then click OK. The default save location will be changed. 14  5. AutoRecover Excel automatically saves your workbooks to a temporary folder while you are working on them. If you forget to save your changes or if Excel crashes, you can restore the file using AutoRecover. 15  5.1 To use AutoRecover: 1.Open Excel. If autosaved versions of a file are found, the Document Recovery pane will appear. 2.Click to open an available file. The workbook will be recovered. By default, Excel autosaves every 10 minutes. If you are editing a workbook for less than 10 minutes, Excel may not create an autosaved version. 16  5.2 To use AutoRecover: If you don't see the file you need, you can browse all autosaved files from Backstage view. Just select the File tab, click Manage Workbook, then choose Recover Unsaved Workbooks. 17  6. Exporting workbooks By default, Excel workbooks are saved in the.xlsx file type. However, there may be times when you need to use another file type, like a PDF or Excel 97-2003 workbook. It's easy to export your workbook from Excel to a variety of file types. Exporting your workbook as an Adobe Acrobat document, commonly known as a PDF file, can be especially useful if you're sharing a workbook with someone who does not have Excel. A PDF will make it possible for recipients to view but not edit the content of your workbook. 18  6.1 To export a workbook as a PDF file: 1.Click the File tab to access Backstage view. 2.Click Export, then select Create PDF/XPS. 19  6.2 To export a workbook as a PDF file: 3. The Save As dialog box will appear. Select the location where you want to export the workbook, enter a file name, then click Publish. 20  6.2 To export a workbook as a PDF file: By default, Excel will only export the active worksheet. If you have multiple worksheets and want to save all of them in the same PDF file, click Options in the Save As dialog box. The Options dialog box will appear. Select Entire workbook, then click OK. 21  6.3 To export a workbook to other file types: You may also find it helpful to export your workbook to other file types, like an Excel 97-2003 workbook if you need to share with people using an older version of Excel, or a.CSV file if you need a plain-text version of your workbook. 22 6.3 To export a workbook to other file types: 1.Click the File tab to access Backstage view. 2.Click Export, then select Change File Type. 23 6.3 To export a workbook to other file types: 3. Select a common file type, then click Save As. 24  6.3 To export a workbook to other file types: 4. The Save As dialog box will appear. Select the location where you want to export the workbook, enter a file name, then click Save. You can also use the Save as type: drop-down menu in the Save As dialog box to save workbooks in a variety of file types. 25  7. Sharing workbooks Excel makes it easy to share and collaborate on workbooks using OneDrive. In the past, if you wanted to share a file with someone, you could send it as an email attachment. While convenient, this system also creates multiple versions of the same file, which can be difficult to organize. When you share a workbook from Excel, you're actually giving others access to the exact same file. This lets you and the people you share with edit the same workbook without having to keep track of multiple versions. In order to share a workbook, it must first be saved to your OneDrive. 26  7.1 To share a workbook: 1. Click the File tab to access Backstage view, then click Share. 2. Excel will return to Normal view and open the Share panel on the right side of the window. From here, you can invite people to share your document, see a list of who has access to the document, and set whether they can edit or only view the document. 27 SOURCES: https://edu.gcfglobal.org/en/excel2016/ https://www.excel-easy.com/ 28 BIL125 – INFORMATION TECHNOLOGIES – II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1  Contents Introduction to Formulas Mathematical operators Understanding cell references Creating a formula Modifying values with cell references Creating a formula using the point-and-click method Copying formulas with the fill handle Editing a formula 2  1. Introduction One of the most powerful features in Excel is the ability to calculate numerical information using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. In this lesson, we'll show you how to use cell references to create simple formulas. 3  2. Mathematical operators Excel uses standard operators for formulas: a plus sign for addition (+), minus sign for subtraction (-), asterisk for multiplication (*), forward slash for division (/), and caret (^) for exponents. All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates. 4  3.1 Understanding cell references While you can create simple formulas in Excel using numbers (for example, =2+2 or =5*5), most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of referenced cells without having to rewrite the formula. In the formula below, cell A3 adds the values of cells A1 and A2 by making cell references: 5  3.2 Understanding cell references When you press Enter, the formula calculates and displays the answer in cell A3: If the values in the referenced cells change, the formula automatically recalculates: 6  3.3 Understanding cell references By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below: 7  3.4 To create a formula In our example below, we'll use a simple formula and cell references to calculate a budget. 1. Select the cell that will contain the formula. In our example, we'll select cell D12. 2. Type the equals sign (=). Notice how it appears in both the cell and the formula bar. 8  3.5 To create a formula 3. Type the cell address of the cell you want to reference first in the formula: cell D10 in our example. A blue border will appear around the referenced cell. 4. Type the mathematical operator you want to use. In our example, we'll type the addition sign (+). 5. Type the cell address of the cell you want to reference second in the formula: cell D11 in our example. A red border will appear around the referenced cell. 9  3.6 To create a formula 6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell. If you select the cell again, notice that the cell displays the result, while the formula bar displays the formula. If the result of a formula is too large to be displayed in a cell, it may appear as pound signs (#######) instead of a value. This means the column is not wide enough to display the cell content. Simply increase the column width to show the cell content. 10  4. Modifying values with cell references The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we've modified the value of cell D10 from $1,200 to $1,800. The formula in D12 will automatically recalculate and display the new value in cell D12. 11 5.1 To create a formula using the point-and-click method: Instead of typing cell addresses manually, you can point and click the cells you want to include in your formula. This method can save a lot of time and effort when creating formulas. In our example below, we'll create a formula to calculate the cost of ordering several boxes of plastic silverware. 1. Select the cell that will contain the formula. In our example, we'll select cell D4. 12 5.2 To create a formula using the point-and-click method: 2. Type the equals sign (=). 3. Select the cell you want to reference first in the formula: cell B4 in our example. The cell address will appear in the formula. 13 5.3 To create a formula using the point-and-click method: 4. Type the mathematical operator you want to use. In our example, we'll type the multiplication sign (*). 5. Select the cell you want to reference second in the formula: cell C4 in our example. The cell address will appear in the formula. 14 5.4 To create a formula using the point-and-click method: 6. Press Enter on your keyboard. The formula will be calculated, and the value will be displayed in the cell. 15 6.1 Copying formulas with the fill handle Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet. The fill handle is the small square at the bottom-right corner of the selected cell(s). 16 6.2 Copying formulas with the fill handle After you release the mouse, the formula will be copied to the selected cells. 17  7. Editing a formula: Sometimes you may want to modify an existing formula. In the example below, we've entered an incorrect cell address in our formula, so we'll need to correct it. Select the cell containing the formula you want to edit. In our example, we'll select cell D12. 18  7.1 Editing a formula: Click the formula bar to edit the formula. You can also double-click the cell to view and edit the formula directly within the cell. 19  7.2 Editing a formula: A border will appear around any referenced cells. In our example, we'll change the first part of the formula to reference cell D10 instead of cell D9. 20  7.3 Editing a formula: When you're finished, press Enter on your keyboard or select the Enter command in the formula bar. 21  7.4 Editing a formula: The formula will be updated, and the new value will be displayed in the cell. 22  7.5 Editing a formula: If you change your mind, you can press the Esc key on your keyboard or click the Cancel command in the formula bar to avoid accidentally making changes to your formula. 23 SOURCES: https://edu.gcfglobal.org/en/excel2016/ https://www.excel-easy.com/ 24 BIL125 – INFORMATION TECHNOLOGIES – II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1  Contents Introduction to Tables Formating data as a table Modifying tables Adding rows or columns to a table Changing the table style Modifying table style options Removing a table 2  1. Introduction Once you've entered information into your worksheet, you may want to format your data as a table. Just like regular formatting, tables can improve the look and feel of your workbook, and they'll also help organize your content and make your data easier to use. Excel includes several tools and predefined table styles, allowing you to create tables quickly and easily. 3  2.1 Formatting data as a table Select the cells you want to format as a table. In our example, we'll select the cell range A2:D9. 4  2.2 Formatting data as a table From the Home tab, click the Format as Table command in the Styles group. 5  2.3 Formatting data as a table Select a table style from the drop-down menu. 6  2.4 Formatting data as a table A dialog box will appear, confirming the selected cell range for the table. If your table has headers, check the box next to My table has headers, then click OK. 7  2.5 Formatting data as a table The cell range will be formatted in the selected table style. Tables include filtering by default. You can filter your data at any time using the drop- down arrows in the header cells. 8  3. Modifying tables It's easy to modify the look and feel of any table after adding it to a worksheet. Excel includes several options for customizing tables, including adding rows or columns and changing the table style. 9  3.1 Adding rows or columns to a table If you need to fit more content into your table, you can modify the table size by including additional rows and columns. There are two simple ways to change the table size: Enter new content into any adjacent row or column. The row or column will be roped into the table automatically. 10  3.2 Adding rows or columns to a table Click and drag the bottom-right corner of the table to create additional rows or columns. 11  3.3 Changing the table style Select any cell in your table, then click the Design tab.. 12  3.4 Changing the table style Locate the Table Styles group, then click the More drop-down arrow to see all available table styles... 13 3.5 Changing the table style Select the desired table style. 14 3.6 Changing the table style The table style will be applied. 15  4. Modifying table style options You can turn various options on or off to change the appearance of any table. There are several options: Header Row, Total Row, Banded Rows, First Column, Last Column, Banded Columns, and Filter Button. 16  4.1 Modifying table style options Select any cell in your table, then click the Design tab. Check or uncheck the desired options in the Table Style Options group. In our example, we'll check Total Row to automatically include a total for our table. 17  4.2 Modifying table style options The table style will be modified. In our example, a new row has been added to the table with a formula that automatically calculates the total value of the cells in column D. 18  4.3 Modifying table style options Depending on the type of content you have—and the table style you've chosen—these options can affect your table's appearance in various ways. You may need to experiment with a few options to find the exact style you want. 19  5. Removing a table It's possible to remove a table from your workbook without losing any of your data. However, this can cause issues with certain types of formatting, including colors, fonts, and banded rows. Before using this option, be prepared to reformat your cells if necessary. 20  5.1 Removing a table Select any cell in your table, then click the Design tab. Click the Convert to Range command in the Tools group. 21  5.2 Removing a table A dialog box will appear. Click Yes. 22  5.3 Removing a table The range will no longer be a table, but the cells will retain their data and formatting. 23  5.4 Removing a table To restart your formatting from scratch, click the Clear command on the Home tab. Next, choose Clear Formats from the menu. 24 SOURCES: https://edu.gcfglobal.org/en/excel2016/ https://www.excel-easy.com/ 25 BIL125 – INFORMATION TECHNOLOGIES – II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1  CONTENTS Introduction to View Options Freezing rows Freezing columns Unfreezing panes Opening a new window for the current workbook Splitting a worksheet Grouping 2  1. Introduction Whenever you're working with a lot of data, it can be difficult to compare information in your workbook. Fortunately, Excel includes several tools that make it easier to view content from different parts of your workbook at the same time, including the ability to freeze panes and split your worksheet. 3  2.1 Freezing rows You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you'll be able to scroll through your content while continuing to view the frozen cells. 1. Select the row below the row(s) you want to freeze. In our example, we want to freeze rows 1 and 2, so we'll select row 3. 4  2.2 Freezing rows 2. On the View tab, select the Freeze Panes command, then choose Freeze Panes from the drop-down menu. 5  2.3 Freezing rows 3. The rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet while continuing to view the frozen rows at the top. In our example, we've scrolled down to row 18. 6  2.4 Freezing columns 1. Select the column to the right of the column(s) you want to freeze. In our example, we want to freeze column A, so we'll select column B. 7  2.5 Freezing columns 2. On the View tab, select the Freeze Panes command, then choose Freeze Panes from the drop-down menu. 8  2.6 Freezing columns 3. The column will be frozen in place, as indicated by the gray line. You can scroll across the worksheet while continuing to view the frozen column on the left. In our example, we've scrolled across to column E. 9  2.7 Freezing columns 4. If you only need to freeze the top row (row 1) or first column (column A) in the worksheet, you can simply select Freeze Top Row or Freeze First Column from the drop- down menu. 10  2.8 Unfreezing panes If you want to select a different view option, you may first need to reset the spreadsheet by unfreezing panes. To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu. 11  3. Other view options If your workbook contains a lot of content, it can sometimes be difficult to compare different sections. Excel includes additional options to make your workbooks easier to view and compare. For example, you can choose to open a new window for your workbook or split a worksheet into separate panes. 12 3.1 Opening a new window for the current workbook Excel allows you to open multiple windows for a single workbook at the same time. In our example, we'll use this feature to compare two different worksheets from the same workbook. 1. Click the View tab on the Ribbon, then select the New Window command. 13 3.2 Opening a new window for the current workbook 2. A new window for the workbook will appear. 14 3.3 Opening a new window for the current workbook 3. You can now compare different worksheets from the same workbook across windows. In our example, we'll select the 2013 Sales Detailed View worksheet to compare 2012 and 2013 sales. 15 3.4 Opening a new window for the current workbook 4. If you have several windows open at the same time, you can use the Arrange All command to rearrange them quickly. 16  4.1 Splitting a worksheet Sometimes you may want to compare different sections of the same workbook without creating a new window. The Split command allows you to divide the worksheet into multiple panes that scroll separately. 1. Select the cell where you want to split the worksheet. In our example, we'll select cell D6. 17  4.2 Splitting a worksheet 2. Click the View tab on the Ribbon, then select the Split command. 18  4.3 Splitting a worksheet 3. The workbook will be split into different panes. You can scroll through each pane separately using the scroll bars, allowing you to compare different sections of the workbook. 19  4.4 Splitting a worksheet After creating a split, you can click and drag the vertical and horizontal dividers to change the size of each section. To remove the split, click the Split command again. 20  5. Grouping Worksheets with a lot of content can sometimes feel overwhelming and can even become difficult to read. Fortunately, Excel can organize data into groups, allowing you to easily show and hide different sections of your worksheet. 21  5.1 Grouping rows or columns 1. Select the rows or columns you want to group. In this example, we'll select columns B, C, and D. 22  5.2 Grouping rows or columns 2. Select the Data tab on the Ribbon, then click the Group command. 23  5.3 Grouping rows or columns 3. The selected rows or columns will be grouped. In our example, columns B, C, and D are grouped. 24  5.4 Grouping rows or columns 4. To ungroup data, select the grouped rows or columns, then click the Ungroup command. 25  5.5 Hiding and showing groups 1. To hide a group, click the minus sign, also known as the Hide Detail button. 26  5.6 Hiding and showing groups 2. The group will be hidden. To show a hidden group, click the plus sign, also known as the Show Detail button. 27  Creating subtotals The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data. For example, the Subtotal command could help to calculate the cost of office supplies by type from a large inventory order. It will create a hierarchy of groups, known as an outline, to help organize your worksheet. Your data must be correctly sorted before using the Subtotal command, so you may want to review our lesson on Sorting Data to learn more. 28  Creating a subtotal In our example, we'll use the Subtotal command with a T-shirt order form to determine how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create an outline for our worksheet with a group for each T-shirt size and then count the total number of shirts in each group. 29  To create a subtotal First, sort your worksheet by the data you want to subtotal. In this example, we'll create a subtotal for each T-shirt size, so our worksheet has been sorted by T-shirt size from smallest to largest. 30  To create a subtotal Select the Data tab, then click the Subtotal command. 1.The Subtotal dialog box will appear. Click the drop-down arrow for the At each change in: field to select the column you want to subtotal. In our example, we'll select T-Shirt Size. Click the drop-down arrow for the Use function: field to select the function you want to use. In our example, we'll select COUNT to count the number of shirts ordered in each size. 31  To create a subtotal In the Add subtotal to: field, select the column where you want the calculated subtotal to appear. In our example, we'll select T-Shirt Size. When you're satisfied with your selections, click OK. 32  To create a subtotal The worksheet will be outlined into groups, and the subtotal will be listed below each group. In our example, the data is now grouped by T-shirt size, and the number of shirts ordered in that size appears below each group. 33  Viewing groups by level When you create subtotals, your worksheet is divided into different levels. You can switch between these levels to quickly control how much information is displayed in the worksheet by clicking the Level buttons to the left of the worksheet. In our example, we'll switch between all three levels in our outline. While this example contains only three levels, Excel can accommodate up to eight. 34  Viewing groups by level Click the lowest level to display the least detail. In our example, we'll select level 1, which contains only the grand count, or total number of T-shirts ordered. Click the next level to expand the detail. In our example, we'll select level 2, which contains each subtotal row but hides all other data from the worksheet. 35  Viewing groups by level Click the highest level to view and expand all of your worksheet data. In our example, we'll select level 3. 36  Viewing groups by level You can also use the Show and Hide Detail buttons to show and hide the groups within the outline. 37  Removing subtotals Sometimes you may not want to keep subtotals in your worksheet, especially if you want to reorganize data in different ways. If you no longer want to use subtotaling, you'll need remove it from your worksheet. Select the Data tab, then click the Subtotal command. 38  Removing subtotals The Subtotal dialog box will appear. Click Remove All. All worksheet data will be ungrouped, and the subtotals will be removed. To remove all groups without deleting the subtotals, click the Ungroup command drop- down arrow, then choose Clear Outline. 39 SOURCES: https://edu.gcfglobal.org/en/excel2016/ https://www.excel-easy.com/ 40 BIL125 – INFORMATION TECHNOLOGIES – II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1  CONTENTS Introduction to Pivot Tables Creating a PivotTable Pivoting data Adding a filter Slicers Pivot Charts 2  1. Introduction When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing data and allowing you to manipulate it in different ways. 3  2.1 Using PivotTables to answer questions Consider the example below. Let's say we wanted to answer the question What is the amount sold by each salesperson? Answering it could be time consuming and difficult; each salesperson appears on multiple rows, and we would need to total all of their different orders individually. We could use the Subtotal command to help find the total for each salesperson, but we would still have a lot of data to work with. 4  2.2 Using PivotTables to answer questions Fortunately, a PivotTable can instantly calculate and summarize the data in a way that will make it much easier to read. When we're done, the PivotTable will look something like this: 5 2.3 Using PivotTables to answer questions Once you've created a PivotTable, you can use it to answer different questions by rearranging—or pivoting—the data. For example, let's say we wanted to answer What is the total amount sold in each month? We could modify our PivotTable to look like this: 6  2.4 Creating a PivotTable 1. Select the table or cells (including column headers) you want to include in your PivotTable. 7  2.5 Creating a PivotTable 2. From the Insert tab, click the PivotTable command. 8  2.6 Creating a PivotTable 3. The Create PivotTable dialog box will appear. Choose your settings, then click OK. In our example, we'll use Table1 as our source data and place the PivotTable in a new worksheet. 9  2.7 Creating a PivotTable 4. A blank PivotTable and Field List will appear in a new worksheet. 10  2.8 Creating a PivotTable 5. Once you create a PivotTable, you'll need to decide which fields to add. Each field is simply a column header from the source data. In the PivotTable Fields list, check the box for each field you want to add. In our example, we want to know the total amount sold by each salesperson, so we'll check the Salesperson and Order Amount fields. 11  2.9 Creating a PivotTable 6. The selected fields will be added to one of the four areas below. In our example, the Salesperson field has been added to the Rows area, while Order Amount has been added to Values. You can also drag and drop fields directly into the desired area. 12  2.10 Creating a PivotTable 7. The PivotTable will calculate and summarize the selected fields. In our example, the PivotTable shows the amount sold by each salesperson. 13  2.11 Creating a PivotTable Just like with normal spreadsheets, you can sort the data in a PivotTable using the Sort & Filter command on the Home tab. You can also apply any type of number formatting you want. For example, you may want to change the number format to Currency. However, be aware that some types of formatting may disappear when you modify the PivotTable. 14  3.1 Pivoting data One of the best things about PivotTables is that they can quickly pivot—or reorganize—your data, allowing you to examine your worksheet in several ways. Pivoting data can help you answer different questions and even experiment with your data to discover new trends and patterns. 15  3.2 Adding Columns So far, our PivotTable has only shown one column of data at a time. To show multiple columns, you'll need to add a field to the Columns area. 1. Drag a field from the Field List into the Columns area. In our example, we'll use the Month field. 16  3.3 Adding Columns 2. The PivotTable will include multiple columns. In our example, there is now a column for each person's monthly sales, in addition to the grand total. 17  3.4 Changing a row or column Changing a row or column can give you a completely different perspective on your data. All you have to do is remove the field in question, then replace it with another. 1. Drag the field you want to remove out of its current area. You can also uncheck the appropriate box in the Field List. In this example, we've removed the Month and Salesperson fields. 18  3.5 Changing a row or column 2. Drag a new field into the desired area. In our example, we'll place the Region field under Rows. 19  3.6 Changing a row or column 3. The PivotTable will adjust—or pivot—to show the new data. In our example, it now shows the amount sold by each region. 20  4.1 Adding a filter In the example below, we'll filter out certain salespeople to determine how their individual sales are impacting each region. 1.Drag a field from the Field List to the Filters area. In this example, we'll use the Salesperson field. 21  4.2 Adding a filter 2. The filter will appear above the PivotTable. Click the drop- down arrow, then check the box next to Select Multiple Items. 22  4.3 Adding a filter 3. Uncheck the box next to any item you don't want to include in the PivotTable. In our example, we'll uncheck the boxes for a few salespeople, then click OK. 23  4.4 Adding a filter 4. The PivotTable will adjust to reflect the changes. 24  5. Slicers Slicers make filtering data in PivotTables even easier. Slicers are basically just filters but are easier and faster to use, allowing you to instantly pivot your data. If you frequently filter your PivotTables, you may want to consider using slicers instead of filters. 25  5.1 Adding a slicer 1. Select any cell in the PivotTable. 2.From the Analyze tab, click the Insert Slicer command. 26  5.2 Adding a slicer 3. A dialog box will appear. Check the box next to the desired field. In our example, we'll select Salesperson, then click OK. 27  5.3 Adding a slicer 3. The slicer will appear next to the PivotTable. Each selected item will be highlighted in blue. In the example below, the slicer contains all eight salespeople, but only five of them are currently selected. 28  5.4 Adding a slicer 4. Just like filters, only selected items are used in the PivotTable. When you select or deselect an item, the PivotTable will instantly reflect the change. Try selecting different items to see how they affect the PivotTable. Press and hold the Ctrl key on your keyboard to select multiple items at once. 29  6. Pivot Charts PivotCharts are like regular charts, except they display data from a PivotTable. Just like regular charts, you'll be able to select a chart type, layout, and style that will best represent the data. 30  6.1 Creating a PivotChart In the example below, our PivotTable is showing a portion of each region's sales figures. We'll use a PivotChart so we can see the information more clearly. 1.Select any cell in your PivotTable. 2.From the Insert tab, click the PivotChart command. 31  6.2 Creating a PivotChart 3. The Insert Chart dialog box will appear. Select the desired chart type and layout, then click OK. 32  6.3 Creating a PivotChart 4. The PivotChart will appear. 33  6.4 Creating a PivotChart Try using filters or slicers to narrow down the data in your PivotChart. To view different subsets of information, change the columns or rows in your PivotTable. In the example below, we've changed the PivotTable to view the monthly sales for each salesperson. 34 SOURCES: https://edu.gcfglobal.org/en/excel2016/ https://www.excel-easy.com/ 35 BIL125 – INFORMATION TECHNOLOGIES – II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1  CONTENTS Introduction to Formulas Creating complex formulas The order of operations Creating a complex formula using the order of operations Introduction to Excel Functions The parts of a function Working with arguments Creating a function using the AutoSum command Columns Entering a function manually  1. Introduction You may have experience working with formulas that contain only one operator, like 7+9. More complex formulas can contain several mathematical operators, like 5+2*8. When there's more than one operation in a formula, the order of operations tells Excel which operation to calculate first. To write formulas that will give you the correct answer, you'll need to understand the order of operations.  2.1 Creating complex formulas In the example below, we'll demonstrate how Excel uses the order of operations to solve a more complex formula. Here, we want to calculate the cost of sales tax for a catering invoice. To do this, we'll write our formula as = (D3 + D4 + D5) * 0.075 in cell D6. This formula will add the prices of our items, then multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the answer.  2.2 Creating complex formulas Excel follows the order of operations and first adds the values inside the parentheses: (45.80+68.70+159.60) = 274.10. It then multiplies that value by the tax rate: 274.10*0.075. The result will show that the sales tax is $20.56.  2.3 Creating complex formulas It's especially important to follow the order of operations when creating a formula. Otherwise, Excel won't calculate the results accurately. In our example, if the parentheses are not included, the multiplication is calculated first and the result is incorrect. Parentheses are often the best way to define which calculations will be performed first in Excel.  2.4 The order of operations Excel calculates formulas based on the following order of operations: 1.Operations enclosed in parentheses 2.Exponential calculations (3^2, for example) 3.Multiplication and division, whichever comes first 4.Addition and subtraction, whichever comes first A mnemonic that can help you remember the order is PEMDAS, or Please Excuse My Dear Aunt Sally. 2.5 Creating a complex formula using the order of operations In the example below, we'll use cell references along with numerical values to create a complex formula that will calculate the subtotal for a catering invoice. The formula will calculate the cost of each menu item first, then add these values. 2.6 Creating a complex formula using the order of operations Select the cell that will contain the formula. In our example, we'll select cell C5. 2.7 Creating a complex formula using the order of operations Enter your formula. In our example, we'll type =B3*C3+B4*C4. This formula will follow the order of operations, first performing the multiplication: 2.79*35 = 97.65 and 2.29*20 = 45.80. It then will add these values to calculate the total: 97.65+45.80. 2.8 Creating a complex formula using the order of operations Double-check your formula for accuracy, then press Enter on your keyboard. The formula will calculate and display the result. In our example, the result shows that the subtotal for the order is $143.45. 2.9 Creating a complex formula using the order of operations You can add parentheses to any equation to make it easier to read. While it won't change the result of the formula in this example, we could enclose the multiplication operations within parentheses to clarify that they will be calculated before the addition.  3.1 Introduction to Excel Functions A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be used to quickly find the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you'll need to understand the different parts of a function and how to create arguments to calculate values and cell references.  3.2 The parts of a function In order to work correctly, a function must be written a specific way, which is called the syntax. The basic syntax for a function is the equals sign (=), the function name (SUM, for example), and one or more arguments. Arguments contain the information you want to calculate. The function in the example below would add the values of the cell range A1:A20.  3.3 Working with arguments Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function. For example, the function =AVERAGE(B1:B9) would calculate the average of the values in the cell range B1:B9. This function contains only one argument.  3.4 Working with arguments Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3, C1:C2, E1) will add the values of all of the cells in the three arguments.  3.5 Creating a function There are a variety of functions available in Excel. Here are some of the most common functions you'll use: SUM: This function adds all of the values of the cells in the argument. AVERAGE: This function determines the average of the values included in the argument. It calculates the sum of the cells and then divides that value by the number of cells in the argument. COUNT: This function counts the number of cells with numerical data in the argument. This function is useful for quickly counting items in a cell range. MAX: This function determines the highest cell value included in the argument. MIN: This function determines the lowest cell value included in the argument. 3.6 Creating a function using the AutoSum command Columns The AutoSum command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MAX, and MIN. In the example below, we'll use the SUM function to calculate the total cost for a list of recently ordered items. 3.7 Creating a function using the AutoSum command Columns Select the cell that will contain the function. In our example, we'll select cell D13.  3.8 Creating a function using the AutoSum command Columns In the Editing group on the Home tab, click the arrow next to the AutoSum command. Next, choose the desired function from the drop-down menu. In our example, we'll select Sum. 3.9 Creating a function using the AutoSum command Columns Excel will place the function in the cell and automatically select a cell range for the argument. In our example, cells D3:D12 were selected automatically; their values will be added to calculate the total cost. If Excel selects the wrong cell range, you can manually enter the desired cells into the argument. 3.10 Creating a function using the AutoSum command Columns Press Enter on your keyboard. The function will be calculated, and the result will appear in the cell. In our example, the sum of D3:D12 is $765.29. 3.11 Creating a function using the AutoSum command Columns The AutoSum command can also be accessed from the Formulas tab on the Ribbon. You can also use the Alt+= keyboard shortcut instead of the AutoSum command. To use this shortcut, hold down the Alt key and then press the equals sign.  4.1 Entering a function manually If you already know the function name, you can easily type it yourself. In the example below (a tally of cookie sales), we'll use the AVERAGE function to calculate the average number of units sold by each troop.  4.2 Entering a function manually Select the cell that will contain the function. In our example, we'll select cell C10.  4.3 Entering a function manually Type the equals sign (=), then enter the desired function name. You can also select the desired function from the list of suggested functions that appears below the cell as you type. In our example, we'll type =AVERAGE.  4.4 Entering a function manually Enter the cell range for the argument inside parentheses. In our example, we'll type (C3:C9). This formula will add the values of cells C3:C9, then divide that value by the total number of values in the range.  4.5 Entering a function manually Press Enter on your keyboard. The function will be calculated, and the result will appear in the cell. In our example, the average number of units sold by each troop is 849. SOURCES: https://edu.gcfglobal.org/en/excel2016/ https://www.excel-easy.com/ BIL125 – INFORMATION TECHNOLOGIES – II MS EXCEL Asst. Prof. Dr. Alper FIDAN [email protected] 1  MS EXCEL Functions COUNT COUNTIF COUNTIFS COUNTA COUNTBLANK SUM SUMIF SUMIFS SUMPRODUCT 2  Count and Sum Functions The most used functions in Excel are count and sum. You can use count and sum based on one criteria or multiple criteria 3  Count To count the number of cells that contain numbers, use the COUNT function. 4  Countif To count cells based on one criteria (for example, greater than 9), use the following COUNTIF function. 5  Countifs To count rows based on multiple criteria (for example, green and greater than 9), use the following COUNTIFS function. 6  COUNTA The COUNTA function below counts the number of nonblank cells in the range A1:A7. COUNTA stands for count all. 7  COUNTBLANK The COUNTBLANK function below counts the number of blank cells in the range A1:A7. 8  Sum To sum a range of cells, use the SUM function. 9  Sumif To sum cells based on one criteria (for example, greater than 9), use the following SUMIF function (two arguments). 10  Sumif To sum cells based on one criteria (for example, green), use the following SUMIF function (three arguments, last argument is the range to sum). 11  Sumifs To sum cells based on multiple criteria (for example, circle and red), use the following SUMIFS function (first argument is the range to sum). 12  Sumproduct I To calculate the sum of the products of corresponding numbers in one or more ranges, use Excel's powerful SUMPRODUCT function. 1. For example, the SUMPRODUCT function below calculates the total amount spent. Explanation: the SUMPRODUCT function performs this calculation: (2 * 1000) + (4 * 250) + (4 * 100) + (2 * 50) = 3500. 13  Sumproduct II 2. The ranges must have the same dimensions or Excel will display the #VALUE! error. 14  Sumproduct III 3. The SUMPRODUCT function treats any entries that are not numeric as if they were zeros. 15  If The IF function checks whether a condition is met, and returns one value if true and another value if false. 1. For example, take a look at the IF function in cell C2 below. Explanation: if the score is greater than or equal to 60, the IF function returns Pass, else it returns Fail. 16  Nested If The IF function in Excel can be nested, when you have multiple conditions to meet. The FALSE value is being replaced by another IF function to make a further test. 1. For example, take a look at the nested IF formula in cell C2 below. Explanation: if the score equals 1, the nested IF formula returns Bad, if the score equals 2, the nested IF formula returns Good, if the score equals 3, the nested IF formula returns Excellent, else it returns Not Valid. 17 2. For example, take a look at the nested IF formula in cell C2 below. Explanation: if the score is less than 60, the nested IF formula returns F, if the score is greater than or equal to 60 and less than 70, the formula returns D, if the score is greater than or equal to 70 and less than 80, the formula returns C, if the score is greater than or equal to 80 and less than 90, the formula returns B, else it returns A. 18 SOURCES: https://edu.gcfglobal.org/en/excel2016/ https://www.excel-easy.com/ 19 BIL125 Information Technologies II Logical Functions Asst. Prof. Dr. Alper FIDAN [email protected] 1  MS EXCEL Functions IF AND OR NOT COMPARISION OPERATORS NESTED IF 2  If The IF function checks whether a condition is met, and returns one value if true and another value if false. 1. For example, take a look at the IF function in cell C2 below. Explanation: if the score is greater than or equal to 60, the IF function returns Pass, else it returns Fail. 3  And The AND Function returns TRUE if all conditions are true and returns FALSE if any of the conditions are false. 1. For example, take a look at the AND function in cell D2 below. Explanation: the AND function returns TRUE if the first score is greater than or equal to 60 and the second score is greater than or equal to 90, else it returns FALSE. 4  Or Function The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all conditions are false. 1. For example, take a look at the OR function in cell D2 below. Explanation: the OR function returns TRUE if at least one score is greater than or equal to 60, else it returns FALSE. 5  Not Function The NOT function changes TRUE to FALSE, and FALSE to TRUE. 1. For example, take a look at the NOT function in cell D2 below. Explanation: In this example, the NOT function reverses the result of the OR function (see previous example). 6 COMPARISION OPERATORS EQUAL TO GREATER THAN LESS THAN GREATER THAN OR EQUAL TO LESS THAN OR EQUAL TO NOT EQUAL TO 7  Equal to The equal to operator (=) returns TRUE if two values are equal to each other. 1. For example, take a look at the formula in cell C1 below. Explanation: the formula returns TRUE because the value in cell A1 is equal to the value in cell B1. Always start a formula with an equal sign (=). 8  Equal to 2. The IF function below uses the equal to operator. Explanation: if the two values (numbers or text strings) are equal to each other, the IF function returns Yes, else it returns No. 9  Greater than The greater than operator (>) returns TRUE if the first value is greater than the second value. 1. For example, take a look at the formula in cell C1 below. Explanation: the formula returns TRUE because the value in cell A1 is greater than the value in cell B1. 10  Greater than 2. The OR function below uses the greater than operator. Explanation: this OR function returns TRUE if at least one value is greater than 50, else it returns FALSE. 11  Less than The less than operator (=) returns TRUE if the first value is greater than or equal to the second value. 1. For example, take a look at the formula in cell C1 below. Explanation: the formula returns TRUE because the value in cell A1 is greater than or equal to the value in cell B1. 14  Greater than or equal to 2. The COUNTIF function below uses the greater than or equal to operator. Explanation: this COUNTIF function counts the number of cells that are greater than or equal to 10. 15  Less than or equal to The less than or equal to operator (