Excel skills description

In this section my goal is to provide a good insight regarding what I can do using Excel in a data analyst role at a large organization. I do hope that for the potential hiring manager this section carries sufficient amount of information to form a general idea about my skills level in utilizing Excel tackling with complex data problems. 

 

EXCEL PROJECTS DELIVERED:

 

BASF - BIT aplication (Excel component).  This tool was designed using input from SAP staged and stored in Access database files and text files and fed into the Excel Power Pivots and DAX formula language to for sales team to track their performance.

 

Wells Fargo/Trust Services - System testing scheduling tool for the testing team to track weekly UAT and SIT testing activities.

 

MHFA (Minnesota Housing Finance Agency) - Developed custom tool in Excel working with Access to gather incoming data sets from the field and feed housing financing database to track the program status.

 

Prime Therapeutics - Maintained and improved the existing tool tracking activities for quality control and management for the online prescription delivery.  It was interacting with the monthly quality reporting system via VBA based structure. 

 

UHG/Optum (Care Solutions) - Developed a reporting dashboard for the senior leadership team. This tool was interacting with Access database files via ODBC and Excel query functions via pivots and VBA routines to update the dashboard. 

 

GMAC (currently dba Ally Financial) - Developed pivot based reporting system which was producing 300 pages long performance reports tracking the activites in asset based investements in the mortgage investment area.

 

NCR (National Car Rental) - Created a tool in support of Access based system feeding the OROS ABC (activity based accounting) software to monitor and analyze the financial data and tying it directly to individual car rental activities using unit based metrics.

 

Excel Skills Rating:  

 

My Opinion on Standardized Excel Skills Tests:

 

Standardized tests are designed to measure one's ability to use the

built-in Excel functions that are provided through the menu options

on the ribbon.  On the other hand, there is not a single test that

can even remotely measure one's ability to be fully productive in

Excel by creating VBA procedures, user defined functions, complex

and lengthy formulas and creative solutions.

 

If you need the candidate to bring more comprehensive solutions to complex data issues in Excel try to gauge his or her capabilities by providing a real life problem as a project and ask to resolve this in a

real life time frame whether it is couple hours, a day or a week 

instead of putting that person through a standardized, timed test

module where test taking abilities are mostly tested instead of true

skills and capabilities. 

 

I was tested on my Excel skills numerous times on standardized

Excel skills test.  On the average my scores hover around the

neighborhood of 90% at mid to advance level tests.  I’d place

myself at 9 out of 10 for my Excel skill and that is very satisfactory

for the most part.  I understand that by using this test they gauge candidates and narrow down their options as anyone can claim

expertise in Excel and even if they sincerely think they know a lot

about it without even realizing what they are not even aware of.  

 

Most of the client needs fall into the category that can be handled

without leveraging the true power of Excel. In this case it makes

sense that they use standardized tests. On the other hand, it

certainly does not measure the more advanced skills, abilities

and creativity of the power user or developer in order to create highly

complex spreadsheets nor it shows how the user tackles a

certain problem for data manipulation as there are literally

numerous ways to resolve a problem. For more complex tasks

such as creating a pricing decision tool in already complex

setting like a trade room, or complex rate sheet development or a

scorecard development working with large backend databasess

typical Excel skills simply do not cut.

 

There are two dimensions to Excel, one is that most people  are

familiar with and that is Excel’s user interface and spreadsheet

along with some functions and formulas utilizing the formula line,

pivot tables and MS Query and for a lot of job descriptions this

might constitute advance skills and this indeed would be

classified as such for the average user. Even Advance Excel

classes that they cover in technical colleges do not go any further

than that.

 

The other dimension of Excel is wide open and if one does a little

bit internet research it is possible to see the possibilities and

capabilities are literally endless there.  This dimension is in the

space of Visual Basic for Applications programming and using

Excel only as a platform by creating UDF modules and routines.

 

There is simply no standardized Excel test to measure the kinds

of capabilities that I am referring to here.  The best way to gauge

would be to put the person on a task or send him or her a complex

problem with sample data set and the requirements and see how

he or she works towards a resolution.

 

 

Sample Excel Problems that I worked with utilizing VBA:

 

  1. Identifying unique values in a range
  2. Dynamic range determination
  3. Calling subroutine from a different file
  4. Populating List boxes and Combo boxes on user forms
  5. Advance Find and Replace
  6. VLOOKUP on multiple columns
  7. Changing cell colors based on the day of the week
  8. Getting totals based on a cell color
  9. Linking checkbox to a formula
  10. Replace function (not under Find menu option)
  11. Message Box prompt
  12. Retrieve data from Access into Excel
  13. Outlook email from within Excel
  14. Creating a range object
  15. Macro that sets up an email
  16. Lock cells based on a value in another cell
  17. Cell contents as Strings
  18. Macro to move from cell to cell after entering data
  19. Using input box to populate arguments in a formula
  20. Changing cell values in macro
  21. List Box and Combo Box
  22. LOOKUP with condition
  23. Array formulas
  24. Loop function
  25. Selecting next blank cell
  26. Check boxes, command buttons and other controls
  27. Functions and sub procedures
  28. Event handling such as an active cell changes its color
  29. Conditional copy of a cell
  30. Data source changes
  31. Counting by range
  32. Class Modules
  33. Import large delimited file
  34. Cell=textbox value
  35. How to recognize string variable in an array
  36. Printing out range names
  37. Extracting Word info into Excel
  38. Select current cell row
  39. Data entry form
  40. Several workbooks into one summary
  41. Print range macro
  42. Separating columns
  43. Fill combo boxes
  44. Run SQL statement in VBA module 
  45. Prevent a cell from being selected
  46. Combo box populating
  47. Option buttons
  48. Finding a blank cell in a column
  49. Changing query references
  50. Extracting the nth element from a string
  51. Does the text match a pattern
  52. Returning an acronym
  53. A user defined functions for commission, discount or interest calculations.

 

 

 ADVANCEd   Excel                                     Access                 databases                                       VBA macros                                       SQL scripts                                       BI Reports                                         data   integrity                                          big data                                             data science                                   data mining                                     data mapping                                   data profiling                                 data   visualization                                   data modeling                                 financial     analysis                                           SCORE CARDS                                     BENCHMARKS                                     PERFORMANCE   KPI METRICS

Print Print | Sitemap Recommend this page Recommend this page
© Ray Erden

This website was created using 1&1 IONOS MyWebsite.