EXCEL SKILLS_VIRTUAL_EXPERIENCE_PROGRAM
Tool used: Microsoft EXCEL
Skills Gaine: Excel Keyboard Shortcuts Conditional Formatting Visual Basic for Applications (VBA) Macros Data Visualization in Excel Data-Driven Storytelling
Task 1: The task is to use Excel’s conditional formatting tools to explore and then, use the conditional formatting tools (either the menu-based tools or write your own conditional formatting formulas, whichever you prefer) to do the following explorations of the data:
Highlight any cells with formula errors in purple with white text.
Highlight any cells with missing values in yellow.
Identify accounts that have not been cross-sold with Product 2 by highlighting the appropriate Product 2 cells in orange.
Identify accounts that have a 5-year sales CAGR of at least 100% by highlighting the appropriate CAGR cells in green and any accounts with a negative CAGR in red with white text.
Identify accounts in the top 10% of unit sales for 2021 by highlighting the appropriate 2021 unit sales cells in blue.
Task 2: Create two macros and associated buttons:
A macro to sort the entire spreadsheet by 5 YR CAGR in descending order to see which accounts have the highest overall 5-year sales growth
A macro to sort the entire spreadsheet by 2021 unit sales in descending order to see which accounts have the highest overall unit sales in 2021
Task 3: Create a simple dashboard using the account sales dataset you worked with in prior tasks