In order to to be able to send tests to third parties and receive the results you do the following (or see the full process on the Excel Skills test for recruitment page):

• Purchase credits from the shop- https://excel-skills-assessment.com/shop/
• Go to the ‘Send Quiz Dashboard‘ and send your tests (you many need to login)
• When the person completes the test you will receive an email with the results and you can see a summary of all your results in the ‘View Results Dashboard‘

Miricle Solutions cc is a South African company that owns www.AuditExcel.co.za and www.Excel-Skills-Assessment.com.

Payments made from this website are linked to the www.AuditExcel.co.za accounts

The Excel Skills Assessment levels are determined based on the contents of our sister site (www.AuditExcel.co.za) training courses.

We expect an Intermediate Excel user to know all the learnings from the online Intermediate Excel course.

We expect an Advanced Excel user to know all the learnings from the online Advanced Excel course.

An intermediate Excel user should know all the skills taught by our sister sites Intermediate Excel course, i.e.

Filtering and Sorting

• Data sorting-standart sorting, by colour and icon sorting
• Sorting the columns instead of the rows
• Creatomg custom sorting lists
• Data Filtering
• Delete rows with the Filter
• Remove duplicate rows
• Using Advanced Filter for repeating filters

The Specials- GOTO and Paste Special

• GOTO Special- find cells with comments, values, formulas and more
• Find errors, conditional formatting and data validation
• Coping Visible Cells Only
• Fill in all the blank cells
• Paste Special as Values, Formulas with/without formatting
• Paste Special to convert negatives to positives
• Switch rows to columns and columns to rows
• Pasting without overwriting blank cells
• Re-using the Format Painter

Tools for text

• Find Replace- standard uses
• Find Replace to change the formatting on many cells
• Remove text between brackets or characters
• Convert single column to multiple columns
• Import CSV’s into the correct columns

Pivot Tables

• Required data format for Pivot tables
• Create a Pivot Table
• Inserting Values into a Pivot Table
• Customize the values e.g. percentage of another column
• Show the details of a number in the Pivot Table
• Changing the layout of Pivot Tables
• Working with the rows in a Pivot Table- dropdowns & field settings
• Create a flat file out of a Pivot Table
• Working with the Columns in a Pivot Table
• Working with the Filter in a Pivot Table
• Automatically duplicate your Pivot Tables with different selections on separate sheets
• Using slicers in a Pivot Table
• Control all Pivot Tables and Charts at once with a single input
• Using timelines in a Pivot Table
• Grouping Dates into days, months, quarters and years in a Pivot Table
• Grouping Dates into weeks in a Pivot Table
• Grouping Text in a Pivot Table
• Grouping Numbers in a Pivot Table
• Pivot Charts in Excel

Conditional Formatting

• Change cell color based on value
• Change color based on benchmark in another cell
• Highlight all above average cells and similar
• Add comparative bars into cells
• Create heat maps in Excel
• Add risk robots, milestone, up and down icons to Excel cells
• Control the heat maps, icons and data bars in conditional formatting

Function and Formula Basics

• Enter formula into many cells (at the same time)
• Using the Function Wizard
• Using the Formula Auditing Toolbar
• Referring to fixed cells ($ signs in Excel formula)
• The basic “IF” function

Text Functions

• Fitting text inside a cell
• Quick entry of rows of data
• Quick Drop Down List
• Join text from many cells into a single cell
• Extract parts of the text from cells
• Force Excel to see the value (instead of text)
• Change text to lower, UPPER or Proper case
• Remove unnecessary spaces

Key Lookup Functions

• Understanding VLOOKUP and similar functions
• Build your first VLOOKUP
• The many uses of VLOOKUP
• Handling Error Messages in Excel

Date functions and issues

• How Excel Handles Dates
• Safest way to capture a date in Excel
• Pull the day, month or year out of the date
• Create a VALID Excel date with the day, month and year
• Calculate the proper end of month
• Determine what day of the week a date is
• Calculate when is the next workday
• How many working days between dates
• What week number is a date
• Make Excel recognize a date

Charting In Excel

• Create a chart
• Align chart to the grid lines
• Customize the chart title, area, legend and more
• Vertical Axis- force the scale, reverse the order, labels and more
• Horizontal Axis- dates vs text, reverse order, show all labels
• Series- overlapping, big and small series, gaps
• Add more series to an existing chart
• Show big and small numbers on the same chart
• Show months, quarters AND years on the chart axis
• Use Column, Bar, Pie, Area and Radar Charts
• Show mini trend charts for large reports

An Advanced Excel user should know all the skills taught by our sister sites Intermediate Excel course, and the skills shown in the Advanced Excel course i.e.

Filtering and Sorting

• All the Intermediate Skills plus
• Creating an Automatic Filter for reuse

Tools for text

• All the Intermediate Skills plus
• Find partial names or between characters (fuzzy logic)
• Change the $ signs on multiple cells with FIND/ REPLACE
• Import CSV but correct for ‘numbers’ that start with zero e.g. credit card numbers

Pivot Tables

• All the Intermediate Skills plus
• Protect the source data of a pivot table
• Add a calculation INSIDE a Pivot Table
• Conditional Formatting within a Pivot
• Get information out of a Pivot into a cell
• Switching off the GETPIVOTDATA
• Add up ITEMs within a Pivot Table Field

Function and Formula Basics

• All the Intermediate Skills plus
• Trace where a formula comes from and goes to (even across sheets)
• Using Absolute and Relative Referencing ($ signs)
• How to (safely) build a nested IF formula
• IF this AND this AND this is True

Formula Tips and Tricks

• All the Intermediate Skills plus
• See the numbers behind a formula
• Change formula across many sheets at the same time
• Update the calculations on only one sheet (for large spreadsheets)

Text Functions

• All the Intermediate Skills plus
• Create a find/ replace formula
• Find where a certain character is in a cell
• Force excel to treat number as text

Key Lookup Functions

• All the Intermediate Skills plus
• Vlookup approximate match (True instead of False)
• Vlookup partial text from single cell- fuzzy lookup
• Handling Error Messages- Remove the impact of #NA, #DIV/0! and more
• Understand OFFSET
• MATCH and INDEX as an alternate VLOOKUP?
• Lookup across rows and columns (at the same time)
• Lookup to the left instead of right (like VLOOKUP)
• Get Excel to find the columns you want to bring back from a lookup
• MATCH, INDEX, HLOOKUP and OFFSET Combined
• Lookup on different sheets
• Transpose rows to columns but keep them linked

SUM, COUNT, AVERAGE IFS

• Sum, count or average if certain cells match
• Sum, count or average cells for ‘bigger than’ type situations
• Sum, count or average with many conditions
• Sum, count or average with partial matches

Date functions and issues

• All the Intermediate Skills plus
• How Excel Handles Times and Dates
• Create a VALID Excel date with a day, month and year number
• Calculate the proper end of month
• When is the next workday e.g. 30 days from invoice date
• How many working days between 2 dates
• How many months/years between two dates
• Force Excel to format a date to your version

Conditional Formatting

• All the Intermediate Skills plus
• Conditional Formatting- Linking to different cells
• Control what is entered in an Excel cell- Data Validation
• Control the inputs in a cell based on another cell

Charting In Excel

• All the Intermediate Skills plus
• Paste a linked picture of data onto a chart (or anywhere)
• Sparklines for trend analysis
• Copy the format of one chart to another chart
• Remove Zeros from chart labels
• Combine a column with a line chart (or other combinations)
• Stop charts stretching when the column width changes
• Customize the series marker to your own image
• Add linked commentary directly to the chart
• Add commentary to the axis
• Add commentary to labels and call outs
• Hiding a series with NA()
• Create a waterfall chart (pre Excel 2016)
• Create a waterfall chart (Excel 2016 and higher)
• Understand and use XY charts e.g. Risk Register
• Change the default color scheme in charts
• Create a chart template for re-use
• Add a trendline to a chart
• Allow charts to automatically grow as you add more data
• Link Excel chart to PowerPoint or Word

Protecting spreadsheet information and formulas

• How to protect an Excel Spreadsheet from opening
• Allow Spreadsheet to be viewed but not changed (read only)
• Protect only certain cells/ formula from everything except input changes
• Lock cells but allow color formatting or comments
• Lock Cells but allow insert or delete of columns/ rows
• Lock Cells but allow column/ row size change
• Protect cells but allow filter/ pivot table/ sort
• Hide formulas in Excel
• Hide and protect a sheet in a spreadsheet
• Allow only certain values into a cell
• Protect cells with different passwords to allow levels of authority

The focus of the Excel skills assessments is to determine your PRACTICAL Excel skills and NOT any book learnt skills. A random selection of questions is pulled from our question bank based on the categories we would expect you to know for this level.

The focus of the Excel skills assessments is to determine your PRACTICAL Excel skills and NOT any book learnt skills. A random selection of questions is pulled from our question bank based on the categories we would expect you to know for this level.

We do not provide the answers and the scoring on a question by question basis. This is to maintain the integrity of the quizzes.