Microsoft Excel Assignment | Get Paper Help
Please look at both attachments. You will need the book to complete the excel spreadsheet
Module 6 Apply Your Knowledge: Creating a Table with Conditional Formatting
Complete Module 6 Apply Your Knowledge on pages EX 6-56 and EX 6-57 in your textbook.
Instructions: Start Excel. Open the workbook named SC_EX_6-2.xlsx, which is located in the Module 6 Student Data Files link in Module 6 in Canvas. The workbook you open contains a list from Sport Physical Therapy, including billing codes, times, and therapists. You are to create a table to include the name of the therapists based on a lookup table as shown in Figure 6-77. The conditional formatting is based on the unit of time billed to insurance.
Make the following modifications to this assignment:
- Add your name and course identifier to the worksheet header.
- Omit #17.
Perform the following tasks:
| Task | Points |
| #1. Open the file named SC_EX_6-2 and save it as SC_EX6_PhysicalTherapyTableComplete. | 1 |
| #2. Select the range, A6:D36. Click the ‘Format as Table’ button (Home tab | Styles group) and then click ‘Red, Table Style Medium 3’ 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. | 2 |
| #3. Name the table, Therapies, by using the Table Name text box (Table Tools Design tab | Properties group). | 1 |
| #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 OK> | 2 |
| #5. Insert a new column in the table (column E), with the column heading, Last Name. | 1 |
| #6. Insert a new column in the table (column F), with the column heading, First Name. Change both column widths to 14.57. | 2 |
| #7. Change the row height of row 5 to 39. Click cell A5. Apply the Title cell style, the Rockwell font, and a font size of 28. Center the title across the selection A5:F5, using the Format Cells dialog box and the ‘Center Across Selection’ command. | 3 |
| #8. To create the lookup table, enter the data from Table 6-6 on page EX 6-57, beginning with Lookup Table in cell H5. Use the format painter to copy the format from cell A5 to cell H5. Copy the headings from cells D6:F6 to cells H6:J6. | 3 |
| 9. Change the column width of columns H, I, and J to 13. Center the heading, Lookup Table, across cells H6:J6. | 2 |
| 10. In cell E7, type =vlookup(d7, $h$7:$i$11,2) to enter the Last Name column in the main table. Repeat the process to enter a function for the First Name in cell F7. (Hint: The third argument of the function will be 3, indicating column 3 in the lookup table. | 4 |
| 11. To apply conditional formatting:
a. Select the range C7:C35, click the Conditional Formatting button (Home tab | Styles group), and then click New Rule to display the New Formatting Rule dialog box. b. Click the Format Style button (New Formatting Rule dialog box) to display the Format Style list. c. Click Icon Sets in the Format Style list (New Formatting Rule dialog box) to display the Icon area. d. Click the Icon Style arrow and then click 5 Quarters in the Icon Style list (New Formatting Rule dialog box) to select an icon style that includes five different black and white circles. e. Click the first Type button and then click Number in the list to select a numeric value. Repeat the process for the other three Type buttons. f. Type 60 in the first Value box, 45 in the second Value box, type 30 in the third Value box, and type 15 in the fourth Value box. Press TAB to complete the conditions. g. Click OK (New Formatting Rule dialog box) to display icons in each row of the table. 12. Save the file again. |
4 |
| 13. Use the Sort Ascending button on the Data tab to sort the table in ascending order by description. | 1 |
| 14. Use the ‘Sort & Filter’ button on the Home tab to sort in descending order by unit of time. | 1 |
| 15. Use the Sort button on the Data tab to create a custom sort by Therapist ID in ascending order, and then within Therapist ID by Therapy Code in ascending order. | 2 |
| Add your name and course identifier to the worksheet header. Save the file. | 1 |
| Total | 30 |
When you have successfully completed and saved the assignment, return to this assignment link in the Canvas online classroom and submit the completed workbook file as a file upload.
Submit a File Upload
To upload a file from your computer and submit as your assignment, select the File Upload tab.
Click the Choose File button [1]. If you have already uploaded your assignment to Canvas and want to select it for your assignment submission, click the Click here…uploaded link [2].
Click the Submit Assignment button [3].
