164|EXCEL – Keyboard Shortcuts

[Benjamin_Lau]

BYU Student Author: @Benjamin_Lau
Reviewers: @Carter_Lee @Dallin_Gardner
Estimated Time to Solve: 30 Minutes

This is an intro challenge that is part of the Excel Learning Path.

We provide the solution to this challenge using:

• Excel

Need a program? Click here.

Overview
SmartHome Co. sells smart home systems of three levels (Base, Plus, and Full). SmartHome has an international salesforce and generates millions of sales each year. You are given the dataset of SmartHome employees including their information, performance, and satisfaction scores. In this challenge, you will use keyboard shortcuts, text manipulation, lookup, statistics, and solver functions in Excel to uncover interesting facts about SmartHome, its employees, and sales mix analysis.

Instructions
NOTE: This is a keyboard challenge related to shortcuts within Excel. Prior to starting the challenge, follow the steps below to ensure that you do not move your mouse when completing the challenge.

• Download this new Excel spreadsheet file even if you finished previous topics using the same Excel spreadsheet.
• Import the Analysis Toolpak and Solver. Go to File > Options > Add-ins > Go. Check the boxes for Analysis Toolpak and Solver. Press OK.
• Import the mouse tracker
  • When the Challenge163_Data.xlsm is downloaded, make sure to enable macros because your senior has created a VBA program to track your mouse usage.
  • If VBA is disabled, go to File > Options > Trust Center > Trust Center Settings > Macro Settings > Enable VBA macros.
  • If “Developer” tab does not appear in your Excel, right click anywhere on the tool bar. Click “Customize the Ribbon”. Check the “Developer” box when the new window pops up.
  • Make sure to unblock the file by right-clicking the file icon in the saved folder > choosing Properties from the menu > selecting the Unblock checkbox and OK at the bottom of the General tab. (more guidance on A potentially dangerous macro has been blocked - Microsoft Support)
• Make sure to download If_You_Use_Your_Mouse.bas file. In the opened ChallengeX_InvoiceData.xlsm file, go to Developer > Visual Basic (or press ALT + F11). On the right panel of the Visual Basic menu, right click on “ThisWorkbook”, then press Import File. Select the If_You_Use_Your_Mouse.bas file to import. The Modules folder should show up below after importing the file.
• Set up a keyboard shortcut key for this newly imported macro! Go to Developer > Macros. Click on If_You_Use_Your_Mouse (*if there are more than one module shown, choose Module 1), then click Options (*if Options does not show up, please refer to step 1 to fix the issue) . Set the shortcut key as CTRL + SHIFT + D (You can choose other buttons too but CTRL + SHIFT ensures no original shortcuts will be overridden.) Click OK after that and close the Macro box. Press the hotkeys for the macro when you are ready to start! (Sidenote: To end the mouse tracker program, press CTRL + ENTER)

You are now set to complete the challenge below while tracking your mouse movement. Go to sheet “Employee Data” to start the analysis. (Use Ctrl + Page Up/Down to navigate from sheet to sheet)

• Text Manipulation and Lookups

  • Open Microsoft excel and open the dataset. Go to the sheet titled “Employee Data”.
  • Find the location of each employee in Column A by using a text manipulation function like left to split the location which is the first three letters of their Employee code to Column D titled “Location”.
  • Find the Employee ID of each employee in Column A by using a text manipulation function like mid separate the employee which is the first four letters of their Employee code to Column D titled “EmpID”.
  • Find the Branch ID of each employee in Column A by using a text manipulation function to pull the last 2 digits.
  • Find the Pay period which is the last letter in the employee code.
  • Find the Location Branch ID which is the Location followed by a dash and branch ID which can be found in the Employee Code in Column A.

• Statistics

  • Create a new column to check if the employee generated more than or equal to $1,000,000 of sales or not. Use the IF function. Highlight the ones that generated more than or equal to $1M of sales using Conditional Formatting. (Check row 2)
  • Create a new column to check if any of the JAPANESE (JA) employee generated more than or equal to $1,000,000 of sales or not. Use the IF function. Highlight the ones that generated more than or equal to $1M of sales and from Japan. (Check row 100)
  • Create a new column to check if any of the JAPANESE (JA) or AUSTRALIAN (AUS) employee generated more than or equal to $1,000,000 of sales or not. Use the IF function. Highlight the ones that generated more than or equal to $1M of sales and from Japan.
  • Find the total sales for SmartHome Co. and total cost of sales. Calculate the gross margin.
  • Plot a scatterplot between employee satisfaction score and sales. Use Employee Satisfaction Score as the Y-axis and sales as the X-axis.
  • Go to Data > Data Analysis. Find Regression and click on it. Put the Y range as the employee satisfaction score and X range as the sales. Now selected your output range on the sheet. Click OK when finish the setup.

• Solver Optimization

  • Go to Data > Solver. Set the objective to maximize the profit. Set the # Ordered cells as variables.

WHEN YOU FINISH:
Press CTRL + ENTER to stop the macro in the background. A message box should pop up showing you how many times you have moved your mouse. The program is extremely sensitive, so even if you just move a pixel, it counts as a move.

Data Files

• Challenge164_Data.xlsx
• Challenge164_Data_Dictionary

Solution

Challenge164_Solution.xlsx
Solution Video: Challenge 164|EXCEL – Keyboard Shortcuts

[Trenton_Slavens]

[Alexander_Klebingat]

Text Manipulation Solution:
=left(a2,3)
=mid(a2,1,4)
=right(a2,2)
=right(a2,1)
=d2& “-” & F2

[Ethan_Brady]

Time - about one hour.

Tried to mirror the solution page pretty closely. Used a lot of new functions and shortcuts which was fun to learn.

[Ben_Shaw]

Time to complete: 45 min.
Difficutly: Easy-Intermediate
Wasn’t unbelievably difficult. Took me a minute to figure out some of the formulas and needed a little help to get the final result. Overall a fun challenge adding and using some add-ins I’ve never personally used.

[Jesse_Lorance]

Time to Complete: 25 Min
Easy Difficulty
Used many shortcuts that made this pretty simple.

[Amy_Ackerman]

1 hour
Easy difficulty.
Learned new shortcuts!

[Eric_Arredondo]

[Preston_Perlmutter]

[Josh_Telford]

[Alex_Chong]

Time- 1 hour
Difficulty: Medium

[Andrea_Ginder]

Time: 30 min
Difficulty: Easy
Comments: It was fun to work with shortcuts. The process was simple.

[ChrisPetro]

Time to complete: 40 min
Easy-moderate difficulty
Have used solver before but not analysis. Getting more familiar with shortcuts.

[Connor_Morrissey]

Time to complete: 1 Hour
Difficulty: Medium
Was interesting seeing how many times I touched my mouse during this challenge. Learned more about effective shortcuts.

[Marcus_Ku]

Time complete: 30min
Difficulty: Medium

[Kallie_Hiller]

time to complete: 45 minutes
difficulty: moderate

[Wesley_Chan]

Time to complete: 1 Hour
Difficulty: Medium
It was interesting to see how many excel shortcuts I learned throughout the challenge. Before I did not know many excel shortcuts, but now I know a lot.

[Zach_Selbach]

Time to complete 30min.
Difficulty moderate.

[Megan_Gordon]

Time to complete : 50 minutes
This was great! I am new to learning excel shortcuts, so it was super educational.

[Austin_Wheeler]

It took me about 40 minutes to do this challenge, I believe it was easy. I got a slightly different number for my answer than the solution provided, but mathematically they both output the same profit.