Overview
It’s January of 2013 and your firm, Brad’s Barely Legitimate consulting services, has been hired by Fred’s Fairly Reliable car dealership. Fred’s son, Tom, took control of the family business in 2012 and led the dealership to one of the worst years it has seen in decades. Fred attributes the steep downturn to Tom’s “million-dollar idea”—parking Toyota Priuses, exclusively, on the lot. Tom disagrees and chalks it up to bad luck. Per Fred’s desperate request, it’s your job to settle this dispute and identify a better selection of cars to show at the dealership. You’ll be tasked with recommending the top 5 cars based on the following metrics:
Most popular
Dealership margin
Fuel efficiency
Prior to starting your analysis, Fred mentions that he’s partial to reading spreadsheets, but Tom will understand only if colors are used. To accommodate, you’ll be brushing the dust off your conditional formatting skills in excel. Don’t let Fred’s Fairly Reliable become Fred’s Fairly Out of Business!
Instructions
Download the excel data.
Tom typically struggles to distinguish one excel row from another. Poor guy! To help him see better, you decide to highlight alternating rows of the data. After selecting A2:L137, create a new rule that highlights every other row with a faint shade of grey, beginning with row 2.
The “Sales_in_thousands” column provides information about how many cars were sold during the last four years. You decide that this is a fair indication of popularity. Set a rule that highlights the top 10 best-selling cars in bright green, and another rule that puts a dark blue border around the best-selling car.
To ensure that they’re excluded from your report, you also decide to identify the least-popular cars. Set a rule that highlights the bottom 5 worst-selling cars in light red, and another rule that puts a dark red border around the worst-selling car.
To maximize dealership margins, you decide to draw attention some of the most lucrative cars. Using the Icon Sets tool, place a green check next to margins that are greater than or equal to 35%, a yellow exclamation symbol next to margins that are less than 35% but greater than or equal to 20%, and a red ‘X’ next to margins that are less than 20%.
Fred is interested in qualifying for a tax credit offered by his state for selling fuel-efficient cars. Using the Color Scales tool, select and apply the “Blue – White” color scale to showcase the best mileage per gallon. Any car above 27mpg is a great prospect.
Create a rule that will highlight the car’s entire row if it falls within the top tier of each parameter.
Present your finished sheet to Fred and Tom, and make a recommendation based on your analysis regarding which five cars they should begin selling on the lot.
Nice challenge! This really helped me to explore more of what conditional formatting can do outside of just highlighting the top 10 items. I hadn’t used the MOD formula before. Thank you for teaching me how it can be used!
I found this challenge to be very difficult, yet rewarding. I’ve learned some conditional formatting in information systems courses before, but my skills have definitely gotten rusty. This was a good way to sharpen them up, but not without some help from our friend Google.
For anyone else struggling, here are some hints:
2. Use the Mod() and row() functions together to find where rows are even.
6. You may not find a “Blue - White” color scale. But you can select a color scale and then go to “Manage rules” and edit this rule so that the colors are blue and white.
7. I couldn’t find an easy way to do this relying on the work I had already done. You’ll need to use a formula to determine which cells to highlight. The AND() formula is what I used. Don’t be afraid to hard-code some values (Ex. if the 10th best selling car had 145 in thousands of sales, use 145 as your comparison value rather than trying to use the “Top 10” feature.) Lastly, you’ll need to use mixed references for this to work fully. Keep the columns constant but the rows free, like this: $A1
Great challenge! Good to learn some of these conditional formatting tricks that will really benefit me in the future. I really think blocking out errors makes an excel sheet look very nice. I also think highlight the 10 best or 10 worst items is great for presenting data.
Great Challenge! I feel like conditional formatting is a rewarding skill to have in excel. I definitely learn a lot from being able to look at the data differently. Thanks for the challenge!
Sometimes I forget how powerful conditional formatting is. I struggled highlighting a entire row based on multiple parameters so I ended up using filters to accomplish the same thing. Thank you for this challenge!
Great Challenge, I always struggle with creating rules so it was awesome practice!
For step 2 I just converted the table into an actual data table so that i could skip the rule and just change the design to the specifications. Here is a snippet of my finished report.
I accidentally Ctrl Z instead of Ctrl+shift all my work…but I still really enjoyed this challenge and “Conditional Formatting” is my new favorite tool!
Glad I reviewed this! I haven’t messed with conditional formatting and there’s a lot I didn’t realize was possible with conditional formatting.
I came to the conclusino that they should definitely continue selling these five cars(in no particular order):
Challenge9_Dataset_FredsFairlyReliable.xlsx (24.1 KB)
This activity was really helpful! I didn’t know you could conditional format and it was good to explore that side of excel. I struggled with conditional highlighting the entire row.
