Microsoft$$ Excel $365$

Practical $1$

DRF Plant Inventory and Summary

$1.$

Start Excel and open data$\_$DRFPlantInventory. Save the workbook using your own name as P$01\_$DRFPlantInventory$\_$ Lastname$\_$Firstname

$2.$

Change the theme to Organic. Rename Sheet$1$ as Trees Make the color tab Green Rename Sheet$2$ as Plants with the color tab Yellow Make the Trees worksheet the active sheet.

$3.$

Insert a column to the right of column B and type Catalog # in cell C$13.$ Use Flash Fill to split the data, placing the Catalog # in column C$.$ Delete column B$.$ Repeat this on the Plants worksheet to place the Catalog # data in column B$.$

$4.$

Group the two worksheets. Merge and center the title in cell A$1$ across the range A$1$:F$1$ and apply the Title cell style. Merge and center the subtitle in cell A$2$ across the range A$2$:F$2$ and apply the Heading $1$ cell style. Set the width of column A to $20/145$ pixels. Set the width of columns B:F to $15/110$ pixels.

$5.$

Ungroup the two worksheets. On both worksheets enter the formulas to calculate the Average, Median, Lowest, and Highest Wholesale Price in cells B$5$:B$8,$ and apply Accounting Number Format to the results.

$6.$

On the Trees worksheet, enter a formula in cell B$10$ to count the number of Coniferous Trees in the Category column. On the Plants worksheet, enter a formula in cell B$10$ to count the number of flowers sold as $6-$pack in the Unit column.

$7.$

On both worksheets, apply conditional formatting to the In$-$Stock column to display Gradient Fill Orange Data Bars. On the Trees sheet, format the range A$13$:F$23$ as a table with headers and apply Gold, Table Style Light $14.$ On the Plants sheet, format the range A$13$:F$33$ as a table with headers and apply Gold, Table Style Medium $7.$ Sort the Plants tables by Plant Name from A to Z in column A$.$ Sort the Trees Table by Tree Name from A to Z$.$

$8.$

On the Trees sheet, add a total row to the table and sum the In$-$Stock column. Enter the total in cell B$11$ and do NOT remove the total row. Repeat for the Plants sheet to find the total In$-$Stock, enter the value in cell B$11,$ but remove the total row.

$9.$

Insert a new sheet named Summary with sheet tab color Red, Accent $4$ and move it so it is the first sheet. Widen columns A:F to $15/110$ pixels. Copy cell A$1$ from the Trees sheet and paste into cell A$1$ on the Summary sheet. In cell A$2,$ type Combined Inventory merge and center cell A$2$ across the range A$2$:F$2,$ and apply the Heading $1$ style. Copy the range A$5$:A$8$ from the Trees sheet and paste into cell A$5$ on the Summary sheet.

Microsoft Office $365$

$10.$

On the Summary Sheet, in cell B$4,$ type Trees in cell C$4,$ type Plants and in cell D$4$ type Combined Format cells B$4$:D$4$ with the Heading $3$ style.

$11.$

In cells B$5$:B$8$ on the Summary sheet, enter references to cells B$5$:B$8$ on the Trees sheet. In cells C$5$:C$8,$ enter references to cells B$5$:B$8$ on the Plants sheet. In cells D$5$:D$8,$ enter Column sparklines for the row data in cells B$5$:C$8.$ Apply Red, Sparkline Style Accent $4,$ Lighter $40\%.$

$12.$

In cell A$9,$ type In Stock In cell B$9,$ enter a reference to cell B$11$ on the Trees sheet. Repeat in cell C$9$ to enter a reference to cell B$11$ on the Plants sheet. In cell D$9,$ enter Column sparklines for B$9$:C$9$ and format it with a different Sparkline Style.

$13.$

In cell A$13,$ enter the Preparers Name $($your first and last name$).$ In cell A$14,$ using the Now function, enter the date and time completed. Move the range A$4$:D$9$ to B$4$:E$9.$

$14.$

Make the Plants Sheet active. Convert the Table to a Range leaving the formatting. Freeze the Panes so that Rows $1$:$13$ remain as you scroll down.

$15.$

Insert a left footer in all worksheets with the file name. For the Tags, type inventory statistics For the Subject, type your course name and section. Be sure your name displays as the author.

$16.$

Group the worksheets and fit to one page. Preview to be sure all $3$ worksheets show. Spell Check the workbook and save it$.$ Exit Excel and submit your file as directed by your instructor. plase write this in excel