$13.$ Insert a picture in a worksheet.

a$.$ Select cell C$15$ in

the Consolidated sheet and insert

the CMPLogo downloaded from the Resources link.

b$.$ Change the image Color to Washout in the Recolor group.

c$.$ Position the top left corner of the image in cell A$1$ and set the shape height to $1.$

d$.$ Position the image so that its top and bottom borders are visible.

e$.$ Select cell A$15.$

f$.$ Save the workbook $($Figure $3).$

$14.$ Build a custom template workbook.

a$.$ Copy the Billing sheet to the end of

the workbook and rename the copied

sheet Billing Template.

b$.$ Hide the original Billing sheet and

the Calls$1,$ Calls$2,$

and Consolidated sheets.

c$.$ Delete columns I:J$.$ The names array

moves to column J$.$

d$.$ Delete the contents of

cells A$4$:B$33$ and cells E$4$:F$33.$ Cell

formats and the formula remain.

e$.$ Cut cells D$3$:D$33,$ right$-$click cell C$3,$

and insert the cut cells to rearrange the

columns. The physician$$s name now

precedes the department name.

$15.$ Convert a dynamic array to a range and

prepare lookup data.

a$.$ Select and copy cells J$4$:J$17$ to the

Clipboard.

FIGURE $2$

COMPLETED BILLING WORKSHEET

FIGURE $3$

b$.$ Select cell J$4$ and paste Values to

convert the array to labels. Remove the

moving border and select cell J$4.$ Individual labels replace the array $($look in Formula bar$).$

c$.$ Sort cells C$4$:D$33$ by physician name in ascending order.

d$.$ Type the department name for each physician in column K by referring to columns C:D $($Figure

$4).$

$16.$ Create a data validation list.

a$.$ Delete the contents of cells C$4$:D$33.$

b$.$ Select cells C$4$:C$33$ and create a data validation list

using the physician names in column J$.$

c$.$ Test your data validation list and choose any name

for cell C$4.$

$17.$ Build an XLOOKUP formula to display the department

name.

a$.$ Select cell D$4$ and start an XLOOKUP formula.

b$.$ Lookup the label in C$4$ and use absolute references

to the lookup and return arrays. Note that both arrays

have the same dimension.

c$.$ Copy the formula to reach cell D$33$ and preserve

borders. When there is no entry in column C$,$ the

standard #N$/$A error message displays.

$18.$ Nest an IFERROR formula for the XLOOKUP function.

a$.$ Select cell D$4$ and click after the $=$ sign in

the Formula bar.

b$.$ Type ife and insert IFERROR.

c$.$ Click after the closing parenthesis and type a comma

$(,)$ to move to the value$\_$if$\_$error argument

for IFERROR. The value argument is

the XLOOKUP function.

d$.$ Type $"$ and press Spacebar, type $"$ for the closing quotation mark, and type $)$ for the

closing parenthesis $($Figure $5).$ This argument will display a blank cell instead of the standard error

message.

e$.$ Copy the edited formula in cell D$4$ to row $33$ and preserve borders.

$19.$ Insert check box form controls.

a$.$ Insert a row at row $2$ and set its height at $48.$

b$.$ Type Administered By$?$ in cell B$2$ including the question mark and set the font size

to $16.$

COMPLETED CALLS AND CONSOLIDATED WORKSHEETS

FIGURE $4$

PHYSICIAN AND DEPARTMENT NAMES

FIGURE $5$

NESTED IFERROR FUNCTION

c$.$ Insert a check box form control in cell D$2.$

d$.$ Edit the label to display CMP $,$ include $3-$D shading for the control, and turn off

its Locked property.

e$.$ Copy the control, paste the copy in cell F$2,$ and edit the label to Carrier $.$

f$.$ Move the copied control to cell E$2.$ Select both controls and align them. Fine$-$tune the position

of the controls as needed.

g$.$ Uncheck a control that has been accidentally checked.

$20.$ Check accessibility and add alt text.

a$.$ Check accessibility for the worksheet.

b$.$ Add alt text for the logo image that says This logo includes addresses,

phone, and web site information. including the period.

c$.$ Hide columns J:K$.$

d$.$ Delete names in column C$,$ if any, that you entered to complete the sheet.

$21.$ Unlock cells and protect the sheet.

a$.$ Unlock cells A$5$:C$34$ and cells E$5$:F$34.$ Cells with formulas remain locked.

b$.$ Select cell A$5.$

c$.$ Protect the worksheet without a password. Allow all cells to be selected and allow columns

and rows to be formatted.

d$.$ Save the template worksheet $($Figure $6).$

$22.$ Complete the billing template by

completing the data shown in Figure $7.$

a$.$ Select the Carrier checkbox form

control to select it$.$

b$.$ Enter the following data starting in

row $5.$ You need not type currency

symbols because the column is

formatted.