164|EXCEL – Keyboard Shortcuts

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

Solution

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

1 Like

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.

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.

Capture