BYU Student Author: @Kylie_Larsen17
Reviewers: @Abraham_Reyes_Leon, @Keanu_Gauthier
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
- Excel
Need a program? Click here.
Overview
As an analyst at Summit Sales Co., you’ve been given a sales report that needs organization and analysis. The data, collected from sales representatives across multiple regions, is currently unformatted and difficult to interpret. Your manager is relying on you to clean, structure, and enhance the report to highlight key sales trends and performance insights. Your work will help leadership make informed, data-driven decisions.
Instructions
-
Download and open the Excel file. Take a look at the raw data so that you know what you’re working with. Format the data as a table.
-
Add a Total Row at the bottom of the table using the Table Design tab. In the Total Row, add a count under the “Order ID” column and a sum under the “Sale Amount” column.
-
Format the “Sale Amount” column as Currency and the “Order Date” column as Short Date.
-
Add a column in Column G titled “Salesperson Full”. This column will contain the full Salesperson name. Use a formula to concatenate Column B and Column C, with a space in between. For example, if “John” was in Column B and “Smith” was in Column C, Column G should look like “John Smith”.
-
Add a column in Column H titled “Days Since Order”. This column should utilize the TODAY() formula to calculate how many days it has been since the Order Date. Format the column as a General Number.
-
Apply conditional formatting to Column E. Any cell where Sale Amount is greater than $4,000 should be filled with light green.
-
You want to take a closer look at the North region. Filter the data for the North region. Post in the thread how many Order IDs were above $4,000 in the North region, as well as one new thing you learned by completing this challenge.
Data Files
Suggestions and Hints
- To concatenate, you can use the CONCAT() formula or the “&” sign
- To add conditional formatting, create a new rule and select “Format only cells that contain”. This allows you format cells above a certain threshold, such as $4,000.
Solution