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/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&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 (Beck.Wang@george
own.ca)
© 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 cu
ent grade point averages. You are to create a table to include lette
grades and summary data as shown in Figure 6–77. The conditional formatting is
ased on a green circle for 3.0 GPA or above, yellow for 2.0 or above, and red fo
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/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&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
utton (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 co
esponding 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/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&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/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&sna… 4/5
Select the range G3:G12, click the Conditional Formatting
utton (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
ox) 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/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&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 (Beck.Wang@george
own.ca)
© 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/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&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 (Beck.Wang@george
own.ca)
© 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 XXXXXXXXXXconsolidated 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/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&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. Colo
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
ox.
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/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&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/n
ui/evo/index.html?deploymentId=58905322 XXXXXXXXXX&eISBN= XXXXXXXXXX&id= XXXXXXXXXX&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
eview the license to ensure you can comply with any copyright
estrictions.
15.
Save the file again.
16