Finish 3 Excel files per instructions
Use the files(the 3 PDF files and other data files) I provided to complete this work. The instructions are in the picture below. (Complete module 6, module 7 and module 8. Specific steps are in the 3 PDF files I provided. After you finish all the work, there should be 3 Excel files to give me) My name: Beck Wang (Do not forget to include my name per instructions as my teacher will give zero if missing my name. Follow the instructions.) 4/19/22, 7:29 PM Print Preview https://ng.cengage.com/static/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236977&sna… 1/5 Chapter 6: Creating, Sorting, and Querying a Table: 6-14c Apply Your Knowledge Book Title: Microsoft® Office 365™ & Excel ®2016: Comprehensive Printed By: Beck Wang (
[email protected]) © 2017 Cengage Learning, Cengage Learning Review 6-14c Apply Your Knowledge Reinforce the skills and apply the concepts you learned in this module. Creating a Table with Conditional Formatting Note: To complete this assignment, you will be required to use the Data Files. Please contact your instructor for information about accessing the Data Files. Instructions: The Dean’s office has provided a list of student scholarship recipients and their current grade point averages. You are to create a table to include letter grades and summary data as shown in Figure 6–77. The conditional formatting is based on a green circle for 3.0 GPA or above, yellow for 2.0 or above, and red for students below 2.0 that are in danger of losing their scholarships. Figure 6–77 Perform the following tasks: 1. javascript:// 4/19/22, 7:29 PM Print Preview https://ng.cengage.com/static/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236977&sna… 2/5 Run Excel and open the file named Apply 6–1 Scholarship Table. Save the file on your storage device with the name, Apply 6–1 Scholarship Table Complete. 2. Select the range, A2:G13. Click the ‘Format as Table’ button (Home tab | Styles group) and then click ‘Table Style Medium 7’ in the Format as Table gallery. When Excel displays the Format As Table dialog box, if necessary, click the ‘My table has headers’ check box to select the option to format the table with headers. 3. Name the table, Scholarships, by using the Table Name text box (Table Tools Design tab | Properties group). 4. Remove duplicates in the table by clicking the Remove Duplicates button (Table Tools Design tab | Tools group). When Excel displays the Remove Duplicates dialog box, click the Select All button and then click the OK button. 5. If requested to do so by your instructor, add your name as a scholarship winner and fill in the corresponding fields of data. 6. Insert a new column in the table (column H), with the column heading, Grade. 7. Change the row height of row 1 to 39. Click cell A1. Apply the Title cell formatting, the Algerian font, and a font size of 28. Center the title across the selection, A1:H1, using the Format Cells dialog box. 8. Add dollar signs with no decimal places to the scholarship amounts and format column widths as necessary. Wrap the text in cell F2. Format the GPA figures to have two decimal places. 9. 4/19/22, 7:29 PM Print Preview https://ng.cengage.com/static/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236977&sna… 3/5 To create the lookup table, enter the data from Table 6–5, beginning with Grade Table in cell J1. Format cell J1 with the Algerian font at size 14. Use the format painter to copy the table column heading format to cells J2 and K2. Right-align the GPA amounts and format with two decimal places. Left-align the grades. Table 6–5 Scholarship Grade Table Grade Table GPA Grade 0.00 F 1.00 D– 1.25 D 1.75 D+ 2.00 C– 2.25 C 2.75 C+ 3.00 B- 3.25 B 3.50 B+ 3.90 A- 4.00 A 4.25 A+ 10. In cell H3, type = vlookup(g3, $j$3:$k$15, 2) to enter the calculated column in the main table. 11. To apply conditional formatting: javascript:// 4/19/22, 7:29 PM Print Preview https://ng.cengage.com/static/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236977&sna… 4/5 Select the range G3:G12, click the Conditional Formatting button (Home tab | Styles group), and then click New Rule to display the New Formatting Rule dialog box. Click the Format Style button (New Formatting Rule dialog box) to display the Format Style list. Click Icon Sets in the Format Style list (New Formatting Rule dialog box) to display the Icon area. Click the Icon Style button and then click ‘3 Traffic Lights (Unrimmed)’ in the Icon Style list (New Formatting Rule dialog box) to select an icon style that includes three different colored circles. Click the first Type button and then click Number in the list to select a numeric value. Click the second Type button and then click Number in the list to select a numeric value. Type 3 in the first Value box, type 2 in the second Value box, and then press the ��� key to complete the conditions. Click the OK button (New Formatting Rule dialog box) to display icons in each row of the table. 12. Display the total row by clicking the Total Row check box (Table Tools Design tab | Table Style Options group). Average the Age column, and sum the Scholarship Amount column. 13. Save the file again. 14. Use the Sort button on the Data tab to sort in ascending order by last name. 15. Use the ‘Sort & Filter’ button on the Home tab to sort in descending order by scholarship amount. 16. 4/19/22, 7:29 PM Print Preview https://ng.cengage.com/static/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443236977&sna… 5/5 Use the Sort command on the filter menu to sort by grade point, with the highest grade point first. 17. Submit the workbook in the format specified by your instructor. 18. What other kind of criteria, filter, or output might be helpful if the table were larger? When might you use some of the database and statistical functions on this kind of data? Why? Chapter 6: Creating, Sorting, and Querying a Table: 6-14c Apply Your Knowledge Book Title: Microsoft® Office 365™ & Excel ®2016: Comprehensive Printed By: Beck Wang (
[email protected]) © 2017 Cengage Learning, Cengage Learning © 2022 Cengage Learning Inc. All rights reserved. No part of this work may by reproduced or used in any form or by any means - graphic, electronic, or mechanical, or in any other manner - without the written permission of the copyright holder. 4/19/22, 7:31 PM Print Preview https://ng.cengage.com/static/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443237008&nbId… 1/5 Chapter 7: Creating Templates, Importing Data, and Working with SmartArt, Images, and Screenshots: 7-9c Apply Your Knowledge Book Title: Microsoft® Office 365™ & Excel ®2016: Comprehensive Printed By: Beck Wang (
[email protected]) © 2017 Cengage Learning, Cengage Learning Review 7-9c Apply Your Knowledge Reinforce the skills and apply the concepts you learned in this module. Using a Template to Create a Consolidated Workbook Note: To complete these steps, you will be required to use the Data Files. Please contact your instructor for information about accessing the Data Files. Instructions: You will create the 2016-2017 consolidated workbook and SmartArt graphic for Prototype Labs shown in Figure 7–91. Figure 7–91 Perform the following tasks: javascript:// 4/19/22, 7:31 PM Print Preview https://ng.cengage.com/static/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443237008&nbId… 2/5 1. Open a File Explorer window and double-click the file named Apply 7- 1 Prototype Labs Template from the Data Files. Save the template as a workbook using the file name, Apply 7-1 Prototype Labs Contract Analysis. 2. Add a second sheet to the workbook, named SmartArt Graphic. Color the tab white. 3. To import a text file: With the Contract Analysis worksheet active, select cell A3. Import the text file named, Apply 7-1 Prototype Labs Missouri from the Data Files. In the wizard dialog boxes, choose the Delimited format with commas. When Excel displays the Import Data dialog box, click the Properties button to display the External Data Range Properties dialog box. Remove the check mark in the ‘Adjust column width’ check box (External Data Range Properties dialog box). Click the ‘Overwrite existing cells with new data, clear unused cells’ option button to select it. Click the OK button to close the External Data Range Properties dialog box. Click the OK button to close the Import Data dialog box. 4. Use a separate area of the worksheet to trim the data from cells A3 through A7. Copy the trimmed data back to the range. Delete the data you no longer need. 5. To import an Access table: 4/19/22, 7:31 PM Print Preview https://ng.cengage.com/static/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443237008&nbId… 3/5 Select cell A8. Import the Access file named, Apply 7-1 Prototype Labs Iowa, from the Data Files. Convert the table to a range. Delete the headings in row 8. Use the format painter to copy the formatting from cells A7:D7 to the range A8:D12. 6. To paste data from Word: Select cell A20. Run Word and open the file named, Apply 7-1 Prototype Labs Kansas. In the Word table, copy the data in columns 2 through 6. Return to Excel and then use the Paste Special command to paste the data as text. Copy the Excel range, A20:E22. Click cell A13 and transpose the data while pasting it. Delete the original imported data in cells A20:E22. Cut the data in cells B13:C17 and paste it to cell C13 to move it one column to the right. Select cells A13:A17. Click the ‘Text to Columns’ button (Data tab | Data Tools group). In the wizard dialog boxes, choose the Delimited format with commas. Use the format painter to copy the formatting from cells A12:D12 to the range A13:D17. 7. Use the fill handle to replicate cells H3:J3 to H4:J5. 8. Enter the word, Totals, in cell A18. Use the Quick Analysis gallery to sum the range C3:D17. 9. Replicate the formula in cell E3 down through cell E18. 10. 4/19/22, 7:31 PM Print Preview https://ng.cengage.com/static/nb/ui/evo/index.html?deploymentId=5890532292517200538869018264&eISBN=9781305870741&id=1443237008&nbId… 4/5 Insert your name and course number in cell A21. 11. Go to the SmartArt Graphic sheet. Remove the gridlines. Insert the Continuous Picture list SmartArt graphic. Resize the SmartArt graphic as necessary. 12. One at a time, replace the word, Text, with the words, Missouri, Iowa, and Kansas, respectively. 13. Change the color scheme to ‘Colorful - Accent Colors’ in the Change Colors gallery. 14. One at a time, click the picture icon in each part of the graphic, and search the web for a graphic related to the state. Make sure you review the license to ensure you can comply with any copyright restrictions. 15. Save the file again. 16