Start Excel. Download and open the file named Exp$22\_$Excel$\_$Ch$01\_$Cumulative$\_$Medical.xlsx$.$ Grader has automatically added your last name to the beginning of the filename.

$0$

$2$ You notice that the $3/21/2024$ Vision expense on Row $10$ is a duplicate of the expense on Row $8.$ You will delete the duplicate row.

Select and delete row $10$ that contains the duplicate $3/21/2024$ Vision data.

$3$

$3$ You want to move the $3/2/2024$ Pharmacy expense to be listed in chronological order.

Select and cut row $11$ that contains the $3/2/2024$ Pharmacy expense and insert cut cells on row $5.$

$4$

$4$ You decide to insert a column for additional data.

Insert a new column A and type Expense # in cell A$3.$

$4$

$5$ In the new column, you want to create a coding system consisting of the year, month, and sequential numbering for the expenses.

Type $2024-03-001$ in cell A$4$ and use Auto Fill to complete the expense numbers to the range A$5$:A$11.$

$5$

$6$ After filling in the expense numbers, you will increase the column width to display the data.

Change the width of column A to $12.$

$0$

$7$ The worksheet should have a title above the data.

Type March $2024$ Medical Expenses in cell A$1$ and merge and center the title over the range A$1$:J$1.$

$6$

$8$ The title would look more professional with some formatting.

Apply bold, Green font color, and $14-$pt font size to the title in cell A$1.$

$3$

$9$ You decide to replace Lab with Laboratory to provide a more descriptive expense name.

Find all occurrences of Lab and replace them with Laboratory.

$5$

$10$ The worksheet contains spelling errors that need to be identified and corrected.

Check the spelling in the worksheet and correct all spelling errors.

$5$

$11$ You are ready to enter the first formula to calculate the adjusted rate.

Calculate the Adjusted Bill in cell F$4$ by subtracting the Amt Not Covered from the Amount Billed. Copy the formula to the range F$5$:F$11.$

$5$

$12$ Next, you will calculate the amount owed.

Calculate the Amount Owed in cell I$4$ by subtracting the Insurance Paid and Copay from the Adjusted Bill. Copy the formula to the range I$5$:I$11.$

$5$

$13$ Finally, you will calculate the percentage paid of the adjusted bill.

Calculate the $\%$ Pd of Adj Bill in cell J$4$ by adding the Copay and Amount Owed and then dividing that amount by the Adjusted Bill. Copy the formula to the range J$5$:J$11.$

$5$

$14$ The first row of monetary values should be formatted with dollar signs.

Apply Accounting Number Format to the range D$4$:I$4.$

$4$

$15$ For the remaining rows of monetary values, you decide to apply Comma Style.

Apply Comma Style to the range D$5$:I$11.$

$4$

$16$ The percentage paid column needs to be formatted with percent signs.

Apply Percent Style with one decimal place to the range J$4$:J$11.$

$4$

$17$ Formatting the column headings on the third row will provide a professional appearance.

Wrap text, bold, and horizontally center the labels in the range A$3$:J$3.$

$3$

$18$ You want to continue formatting the column headings.

Apply Green fill and apply Thick Bottom Border to the range A$3$:J$3.$

$4$

$19$ You will apply a cell style for the last three columns that represent your costs.

Apply the Good cell style to the range H$4$:J$11.$

$2$

$20$ For the last column, you want to format the percentages below the column heading.

Apply Align Right and indent once the data in the range J$4$:J$11.$

$3$

$21$ Changing the page orientation will enable the data to better fit on a printout.

Select landscape orientation.

$3$

$22$ Next, you are ready to set the top margin and center data on the page.

Set a $1-$inch top margin and center the worksheet horizontally between the left and right margins.

$4$

$23$ To make the data easier to read, you will increase the scaling.

Set $110\%$ scaling.

$3$

$24$ Insert a footer with the text Exploring Series on the left side, the sheet name code in the center, and the file name code on the right side of the worksheet.

$6$

$25$ You will rename the sheet tab and copy the worksheet to start creating a template for the next month.

Rename Sheet$1$ as March. Copy the worksheet, place the duplicate to the right, and then rename it as April.

$5$

$26$ You will make some changes on the April worksheet so that it will be ready for data entry.

Change the title to April $2024$ Medical Expenses in the April worksheet. Delete data in ranges A$4$:E$11$ and G$4$:H$11.$ The formula results in column J display #DIV$/0!$ because you deleted the data, which generates a divide by zero error. Type $1$ in cell D$4$ and copy the value to the range D$5$:D$11$ as placeholder values to avoid displaying the error results