Shelly Cashman Excel 365

Shelly Cashman Excel 365 | Module 7: SAM Critical Thinking Project C CleanTec Corporation

IMPORT DATA AND INSERT SMARTART, SHAPES, AND CHARTS

September 24, 2026 · Excel Projects Help

GETTING STARTED

1. Save the file SC_EX365_CT7C_FirstLastName_1.xlsx as SC_EX365_CT7C_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. To complete this Project, you will also need the following files:

Support_EX365_CT7C_2027.png

Support_EX365_CT7C_2028.png

Support_EX365_CT7C_2029.png

Support_EX365_CT7C_Examples.docx

Support_EX365_CT7C_Project.html

3. With the file SC_EX365_CT7C_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Salma Dalvari is an associate project manager at CleanTec Corporation, an environmental engineering firm. She is preparing a workbook that will be given to prospective clients to describe CleanTec's services and performance. She asks for your help in importing data and adding other content to the workbook. Go to the Project Team worksheet. Add CleanTec logo as the alt text for the leaf picture at the top of the worksheet.

2. Apply the Pattern Fill: White; Dark Upward Diagonal Stripe; Shadow WordArt Style (1st column, 4th row in the WordArt Styles gallery) to the shape containing the worksheet title "CleanTec Corporation" to coordinate it with the other worksheet titles in the workbook.

3. The worksheet should list information about the engineering project team, which is contained in a webpage. Import data from the webpage as follows:

a. Open the webpage Support_EX365_CT7C_Project.html in a browser.

b. In the Project Team worksheet, import the data from the CleanTec_Engineering_Project_Team part of the Support_EX365_CT7C_Project webpage as a table in cell H12 of the existing worksheet. If necessary, resize the table to remove the blank row from the bottom. [Mac Hint: Import the data from HTML. Open the file Support_EX365_CT7C_Project.html, copy and paste the data from the range A3:G9 to the range beginning in cell H12.]

c. Clear the formats from the imported data in the range H12:N18 to remove the webpage formatting.

d. Apply the Green, Table Style Medium 2 style to the imported table. [Mac Hint: Format the imported data in as a table with headers, and apply the Green, Table Style Medium 2 style.]

e. Rename the table using Project_Team as the name.

4. Salma wants you to list the team information in the range B5:F10. The webpage table separates the first and last names, but Salma wants to list the full name on the Project Team worksheet. List the first and last names of each team member in a single cell. To display the first member, enter a formula in the appropriate cell using a function that displays the first employee's first and last names in the imported list, with a space in between them. Then fill the name column with the formula you just entered to list the full names of the remaining team members.

5. Incorporate the imported data in the range C5:F10 as follows:

a. Copy the Role data from the imported table and paste only the values in the column next to the employee's full names.

b. In the Department column, enter a formula using a function to capitalize the first letter in each word in the Department text in the imported table. Fill the column down with the formula to list the departments of the remaining team members.

c. For the Tenure column, enter a formula using a function to insert the first 2 characters on the left of the Tenure column of the imported table. Copy the formula to fill the rest of the column.

d. For the Location column, enter a formula using a function to insert the last 2 characters on the right of the Location column of the imported table. Copy the formula to fill the rest of the column.

e. Hide rows 12 to 18 so that the worksheet does not display duplicated data.

6. On the Company Awards worksheet, Salma already imported data summarizing the recent awards the company received but wants to display the data in the range H5:I10 of the Project Team worksheet. She asks you to switch the rows and columns when you insert the data to fit in the range H5:I10. Insert the imported data as follows:

a. Go to the Company Awards worksheet. Copy the data, and then transpose the rows and columns as you paste the data on the Project Team worksheet in the appropriate cell.

b. Resize column H to its best fit.

7. On the Project Team worksheet, Salma asks you to provide one diagram with an organization chart to show the hierarchy of the engineering project team and another diagram illustrating the company awards. Insert an organization chart as follows:

a. Insert the Organization Chart SmartArt from the Hierarchy section of the SmartArt gallery.

b. Move and resize the SmartArt so that the upper-left corner is in cell B19 and the lower-right corner is in cell E29.

8. Add text to the SmartArt as follows, using Figure 1 as a guide:

a. Enter Devi in the top shape.

b. Enter Casal in the second shape.

c. Add a shape after the "Casal" shape so that it appears to the right of the "Casal" shape.

d. Enter Mata in the new shape.

e. In the bottom row, enter Barr in the left shape.

f. Enter Rizzo in the middle shape.

g. Enter Chu in the right shape.

Figure 1: SmartArt Text

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

9. Insert a diagram to illustrate the company awards as follows:

a. Insert the Circular Picture Callout SmartArt from the Picture section of the SmartArt gallery.

b. Move and resize the SmartArt so that the upper-left corner is in cell F19 and the lower-right corner is in cell J31.

10. Add text and pictures to the SmartArt as follows, using Figure 2 as a guide:

a. Enter Awards in the large circle on the left.

b. Enter 2029 next to the top shape on the right.

c. Enter 2028 next to the middle shape on the right.

d. Enter 2027 next to the bottom shape on the right.

e. Insert the picture Support_EX365_CT7C_2029.png in the top shape on the right.

f. Insert the picture Support_EX365_CT7C_2028.png in the middle shape on the right.

g. Insert the picture Support_EX365_CT7C_2027.png in the bottom shape on the right.

Figure 2: SmartArt Text and Pictures

11. Go to the Cost Benefit worksheet, which Salma asks you to finish. The worksheet shows sample data for fictional projects to illustrate the benefits clients typically receive when contracting with CleanTec Corporation. The data for the range B20:D28 is stored as text in a Word document. Include the text in the Cost Benefit worksheet as follows:

a. Open the Word document Support_EX365_CT7C_Examples.docx, copy all the paragraphs, and then paste the text in cell B20 of the Cost Benefit worksheet to match destination formatting.

b. Convert the text you just pasted to columns using a comma delimiter. Use the General column data format and replace the data already in the range.

12. Salma asks you to help clients visualize the data in the range B20:G28. Create a conditional formatting rule that adds Solid Fill Blue Data Bars to the % Gain (Loss) column. (Hint: Use the Quick Analysis tool, if possible.)

13. Insert a chart to compare the annual costs with the annual income for each project.

a. Based on the nonadjacent data in the Project, Annual Costs, and Annual Income columns, insert the first type of chart that Excel recommends, which is a Clustered Bar chart.

b. Move and resize the chart so that its upper-left corner is in cell B5 and its lower-right corner is in cell G19.

14. Modify the bar chart as follows to make it easier to interpret:

a. Remove the chart title because the legend identifies the data clearly.

b. For the Annual Income data series only, add data labels to the outside end of each bar.

15. Salma wants to make sure that clients reviewing the worksheet understand it displays sample data. Add a shape to the worksheet as follows to provide this information:

a. To the right of the % Gain (Loss) data at the top of the page, insert an Arrow: Left shape from the Block Arrows section of the Shapes gallery.

b. Resize the arrow to a width of 1.4" without changing the height.

c. Move the arrow so that its tip is on the right border of the range F3:F4.

d. Type Sample data in the arrow.

e. Apply the Subtle Effect – Green, Accent 1 shape style to the arrow.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the website to submit your completed project.

Final Figure 1: Project Team Worksheet

Final Figure 2: Company Awards Worksheet

Final Figure 3: Cost Benefit Worksheet

Need help with this project?