Part II: Use of Excel$$s conditional formatting and basic Excel functions

You are provided a worksheet tab containing data on $1,345$ makeup purchase transactions of products from a large makeup company by retail customers.

The sales leader is interested in understanding which products are top and bottom sales items in each region. They are also interested in insights on any extremely large transactions both in terms of sales dollars and order size. You are to use conditionally formatting in the sales worksheet to highlight transactions and sales to bring attention to these aspects of the sales data.

The following information is provided: Transaction number, salesperson name, transaction date, product sold, units sold, sales region, and associated sales revenues.

i$.$ Format the data as a table with the first row identified as a header row.

ii$.$ Sort the data so that it is sorted first by region and then by product.

iii. Use a Highlight Cells rule to assign a format to the transactions for orders between $1$ and $25$ units.

iv$.$ Apply the Top$/$Bottom Rule to assign a format to the largest sales dollars for each region.

v$.$ Finally, use an Icon Set to visually differentiate retail orders with small vs$.$ moderate vs$.$ large dollar orders.

vi$.$ Obtain some summary measures using basic Excel functions. Create a summary table$($s$)$ that contains:

$1.$ The total units sold of each product across all regions using the $=$sumif$()$ Excel function.

$2.$ Average dollar revenues for each region using the $=$averageif$()$ function in Excel.

$3.$ Average dollar revenues for each product type.

Remember, simple formatting is more appropriate than $3$D or too many colors. The goal is to focus attention, not to create complexity and unnecessary cognitive load for the audience.