Who Needs These Skills?
Have you noticed that Excel Advanced courses generally aren’t about Formulas and Functions but instead about Analysing Data, creating repetitive set of instructions – Macros, collating and linking data to communicate between Excel Spreadsheets!
These concepts are not difficult, but skills needed by people in various job roles.
First thought would be Accountants and Auditors, dig deeper think broadly.
Secretaries, Administrative assistants, Customer Service Administration, Data Entry Operator, Office Clerks, Purchasing Officer.
What are the skills these jobs require? Organisations skills, schedule appointments, manage records, manage people data. Set out time sheets to calculate overtime and leave. See if data meets criteria. Perform end of month reconciliation. Interpret Sales orders. Prepare data for Mail outs.
In terms of Excel to get the job done. Organising lists, which need to be sorted or filtered. Email Excel Spreadsheets to be included in PowerPoint Presentations. Create Excel calculations to work out costs, profit, expenses, count quantities.
Meeting criteria might require understanding how to Sort and Filter data. Apply Conditional Formatting for example in the case of if a figure is over x amount then make it splash out in colour and make italics.
Reconciling data you could consider using the VLOOKUP Function or the recently new XLOOKUP function which replaces the inabilities of the VLOOKUP Function. Other wise you could still use the INDEX and MATCH Functions combined to achieve the same results of compare, find and extract data.
To prepare data for a Mail out perhaps you will use Mail Chip but before you can get there the data must be clean. What I mean by that is no spaces before text or after or extra spaces between words. Suburbs and States may need to be in Upper Case.
Perhaps a new code is needed for each customer so you may want to join the first three letters of their first name and the first three letters of their surname to make up the new code.
The function to take out extra spaces is the TRIM Function. It’s very simple =TRIM(A1) Enter.
To make text in Upper Case, the function is =UPPER(a1) Enter.
Making up new codes with the CONCATENATE Function can be done using two methods.
School Teachers constantly need student data to count the number of students attending courses. Running analysis to find out if you have enough resources in teacher, materials, equipment even total number of students to go ahead with any course.
Record lesson outcomes and lesson timetables. Create lesson plans with achievable requirements.
Incorporate some statistical data into a Microsoft Word document to create a report to present to the head teachers or Subject matter expert.
Teachers need to know functions like IF where they may need to say IF a student gets a score less than 10, they receive a D. A Score less than 50 is a C and those less than 80 receive a B anything else is an A. This is achieved with a Nested IF Function or a VLOOKUP Function.
Conditional Formatting could be used by Teachers/Educators to make any student score below 60 to stand out in a colour. COUNT Functions or Pivot Tables could be used to record the number of students attending specific courses.
It only takes a basic Excel spreadsheet to present student timetables.
Lesson plans with measurable outcomes can be easily achieved using Excel. In this instance I would ensure they use Wrap text and Press Alt Enter each time they want to stay in the same cell but go down one line. Use an Asterix system to give the feel of Bullet Points.
Managers and Analysts
Project managers and coordinators, plan and supervise developments, set expectations, manage costs. Microsoft Project or other software may be used for part of their job but so is Microsoft Excel.
Pivot tables to gather statistical data, the What If Analysis feature of Excel for predictions. Power Bi to create visualisations, Dashboards. Most of these features would be geared toward an Excel Advanced user or Business Data Analyst, Reporting Analyst.
Accounts Officer would use the VLOOKUP Function and PIVOT TABLE concepts in Excel. A Technical Business Analyst would be required to know INDEX and MATCH functions along with MACROS and complex nesting functions.
82% of Excel Skills are required for the majority of middle-skill positions. Those with Excel skills are 12% likely to increase their chances of a promotion.