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.

QUESTION

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:

ANSWER

Creating a Table with Conditional Formatting for Sport Physical Therapy

Introduction

In this module, we will be working with Excel to create a table for Sport Physical Therapy, including billing codes, times, and therapists. We will also incorporate conditional formatting based on the unit of time billed to insurance. By following the given instructions and completing the assigned tasks, we will optimize the functionality and presentation of the worksheet. Let’s get started!

Task 1: Opening and Saving the Workbook

We begin by opening the provided file named “SC_EX_6-2.xlsx” in Excel. Upon opening, we save the file as “SC_EX6_PhysicalTherapyTableComplete” to indicate that we have made modifications.

Task 2: Formatting the Range as a Table

Next, we select the range A6:D36 and click the “Format as Table” button in the Home tab’s Styles group. From the Format as Table gallery, we choose the “Red, Table Style Medium 3” option. In the Format As Table dialog box, we ensure the “My table has headers” checkbox is selected to format the table with headers.

Task 3: Naming the Table

To enhance the table’s manageability, we name it “Therapies” using the Table Name text box found in the Table Tools Design tab’s Properties group.

Task 4: Removing Duplicates

We eliminate any duplicate entries within the table by clicking the “Remove Duplicates” button in the Table Tools Design tab’s Tools group. In the Remove Duplicates dialog box, we click the “Select All” button and then click “OK” to complete the removal process.

Task 5: Inserting a Last Name Column

To incorporate additional information, we insert a new column (column E) in the table. The column heading for this new column is “Last Name.”

Task 6: Inserting a First Name Column

Similar to the previous task, we insert another new column (column F) in the table. This column will have the heading “First Name.” Additionally, we adjust the width of both newly inserted columns to 14.57 for better visibility.

Task 7: Formatting the Title and Row Height

To improve the worksheet’s aesthetics and clarity, we modify the title’s and row height’s appearance. First, we change the row height of row 5 to 39. Then, we click cell A5 and apply the “Title” cell style, set the font to Rockwell, and adjust the font size to 28. Finally, we center the title across the selection A5:F5 using the Format Cells dialog box and the “Center Across Selection” command.

Task 8: Creating the Lookup Table

We create a lookup table by entering the data from Table 6-6 on page EX 6-57. Starting with the label “Lookup Table” in cell H5, we use the format painter to copy the format from cell A5 to cell H5. Next, we copy the headings from cells D6:F6 to cells H6:J6. This ensures consistency in formatting and labeling.

Task 9: Adjusting Column Width and Heading Alignment

To accommodate the data in the lookup table, we change the column width of columns H, I, and J to 13. Additionally, we center the heading “Lookup Table” across cells H6:J6 for better visual presentation.

Task 10: Applying VLOOKUP Formula

In cell E7, we enter the formula “=VLOOKUP(D7, $H$7:$I$11, 2)” to populate the Last Name column in the main table using the lookup table. Similarly, we enter the formula “=VLOOKUP(D7, $H$7:$I$11, 3)” in cell F7 to populate the First Name column. By utilizing VLOOKUP, we can automatically retrieve the respective therapist names based on the therapy codes.

Task 11: Implementing Conditional Formatting

We apply conditional formatting to highlight the unit of time billed to insurance. First, we select the range C7:C35 and click the “Conditional Formatting” button in the Home tab’s Styles group. From the options displayed, we choose “New Rule” to open the New Formatting Rule dialog box. Within this dialog box, we click the “Format Style” button to access the Format Style list. From there, we select “Icon Sets” and choose the “5 Quarters” icon style, which consists of five different black and white circles. Next, we assign numeric values to each type by selecting the corresponding Type button and entering the respective values (60, 45, 30, 15). Once completed, we click “OK” to apply the formatting rule, resulting in icons being displayed in each row of the table according to the conditions.

Task 12: Saving the File

After implementing all the modifications and formatting, we save the file again to preserve our progress.

Task 13: Sorting by Description (Ascending Order)

Using the Sort Ascending button on the Data tab, we sort the table in ascending order based on the description column. This allows for easier navigation and analysis of the therapy entries.

Task 14: Sorting by Unit of Time (Descending Order)

We utilize the “Sort & Filter” button on the Home tab to sort the table in descending order by the unit of time column. By arranging the data in this manner, we can identify the therapies with the highest billed times more efficiently.

Task 15: Creating a Custom Sort

Lastly, we create a custom sort by Therapist ID in ascending order, followed by Therapy Code in ascending order. This custom sort allows for a more organized and logical arrangement of the data, facilitating easier analysis and comparison.

Conclusion

Through the completion of the assigned tasks, we have successfully created a table for Sport Physical Therapy in Excel. By incorporating conditional formatting and following the provided instructions, we have enhanced the functionality and visual appeal of the worksheet. The resulting table provides a clear overview of therapy details, including therapist names, billing codes, times, and other relevant information. The conditional formatting helps to identify the unit of time billed to insurance, allowing for quick analysis and decision-making. By saving the file and implementing sorting options, we ensure the longevity and usability of the worksheet.