Rating                
Total Score                
               
               
 
               
 
               
       
           
 
  Thanks for visiting our MS Excel Skills Assessment page  
  By completing this questionnaire, you will learn not only what your skill level is  
  but also what your job type needs to know in MS Excel  
 
  Please complete all the boxes marked in yellow (on all pages)  
  The assessment should take about 30 minutes  
  YOU ARE NOT EXPECTED TO KNOW ALL THE ANSWERS. If you don't know choose the 'don't know' option  
  Often you will see questions with multiple correct answers. Choose the one you use or Don't know if you don't know  
  For the What You Do in Excel section, have a quick look and focus on the items that you have seen before  
 
  To start click on the NEXT PAGE button below  
 
   
       
  Welcome to the AuditExcel.co.za MS Excel skill and needs assessment.  
  Note the page names above. This is another way to navigate through the form.  
  For detailed instruction click the link below to see how this works. Below a  
  summary.  
  https://www.auditexcel.co.za/excel-skills-assessment/  
     
  Summary: To complete the Excel Skills Assessment.  
 
1) The NEXT PAGE button (at the bottom of page) will be important to progress from page to page.
  You can use the page names above as well, but we recommend the NEXT PAGE button.  
     
2) The beginning pages (Req1 to Req4) are the skills assessment. In each case complete the  
  question by answering one of 'a' to 'e' (if you don't know choose 'e'- don't  
  guess). These are Required (hence Req1 etc) as shown below. You must complete them.  
 
 
   
   
   
   
   
   
   
   
   
   
   
   
  At the bottom of each page, there is a 'NEXT PAGE' button. Use this to  
  go to the next page.  
 
3) Once you have completed the skills assessment, the next pages (Opt1 to Opt4) are   
  opinion questions. Each situation explains a common job performed in Excel.  
  All you need to do is use the slider to indicate how often you need to know  
  this (0- Never, 5- Always) as shown below.  
 
 
   
   
   
   
   
   
   
   
   
   
   
   
   
   
 
4) The last page (REPORT) requires a bit of information. Most important is your email  
  address so that you can get your report (takes a few minutes).  
  Once complete, click the SUBMIT button (give it a few seconds to process).  
 
 
   
   
   
   
   
   
   
   
   
   
   
   
   
   
 
5) Click the NEXT PAGE button below and begin  
 
   
             
  'Skill Level' Questionnaire- 20 Questions        
  - Questions go from easy to hard        
  - This is not a test, it is to ascertain the best course to attend. Don't guess answers      
  - You must answer all questions (even with just a 'Don't know') otherwise you will be sent back at the end to complete  
1- To create a formula you start by typing            
a) @            
b) +            
c) -            
d) =            
e) Don't know            
ANS>>
           
               
2- To link a cell to another sheet or spreadsheet you            
a) Type = and click on the other sheet or spreadsheet cell            
b) Type the name of the sheet and the cell reference            
c) Type the name of the sheet, a ' and the cell reference            
d) Type the name of the sheet, a ! and the cell reference            
e) Don't know            
ANS>>
           
               
3- If you copy the cell in B4 to C5, what will the formula look like and what will the answer be            
 
 
a) '=B$1' and 0            
b) '=B1' and 0            
c) '=A$1' and OK            
d) '=B$1' and OK            
e) Don't know            
ANS>>
           
               
4- In order to change the name of a excel file the best option would be to            
a) Change the name shown in cell A1 of Sheet 1            
b) In folder change name of the file while the file is closed            
c) Click the Save icon (or use CTRL S)            
d) Use the 'Save As'            
e) Don't know            
ANS>>
           
               
5- In order to change the way the printer prints (portrait to landscape) you would            
a) Go to Print Preview and choose page setup and choose the landscape option          
b) Go to Page Setup and choose the Landscape option            
c) Use the Print Option and then change the Printers Properties            
d) Choose the Print Area option and Set the print Area to be landscape            
e) Don't Know            
ANS>>
           
               
6- If you've copied this cell (A1 with =1/3 in it) and you pasted as values into cell B1 (which has no formatting) what could it look like            
 
 
   
   
   
 
a) 0.33            
b) 1/3            
c) =1/3            
d) 0.333333333333            
e) Don't Know            
ANS>>
           
               
Already know your level and don't want to finish the test? See the available options  
Beginner Course  
Intermediate Course  
Advanced Course  
 
or  
 
Click to email request for more information  
               
  Click the NEXT PAGE button to continue  
   
7-  
  Pic A  
 
 
   
   
   
   
   
   
   
  Pic B  
 
 
   
   
   
   
   
   
   
   
   
 
a) 39  
b) 18  
c) 25  
d) 33  
e) 31  
ANS>>
 
     
8- How do I make cell A1 look like cell A3  
 
 
   
   
   
   
   
   
   
   
a) Go to the Format Cells option and Wrap Text  
b) Go to the Format Cells option and Shrink to Fit  
c) Go to the Format Cells option and Merge Cells  
d) Hold ALT down and click enter at each new line point  
e) Don't Know  
ANS>>
 
     
9- If a row is hidden e.g. rows 5 and 6 in the picture, and you copy the formula in C4 by dragging it down from C4 to C7 the following will happen  
 
 
   
   
   
   
   
   
   
   
 
a) The formula will be included in the hidden cells  
b) The hidden cells will be overwritten with the new formula  
c) The formula will be copied into C7 only  
d) The formula in C7 will say = B4+C4  
e) Don't Know  
ANS>>
 
     
10- If you sort the following list in ascending order what will be the result  
 
 
   
   
   
   
   
   
   
   
 
a) A1, A2, A10, A11, A20, A100  
b) A1, A10, A100, A11, A2, A20  
c) A100, A10, A1, A11, A20, A2  
d) A20, A2, A11, A100, A10, A1  
e) Don't Know  
ANS>>
 
     
  Click the NEXT PAGE button to continue  
   
11-  
 
 
a) Set the filter in column A, B, and E to show all  
b) Set the filter in column C and D to show all  
c) Use the menu items to Show All or Clear all  
d) Set the filter in column C to show all  
e) Don't know  
ANS>>
 
     
12- Which of the following comments makes the most sense to you  
a) Separating the areas where your inputs, calculations and reports are is the most important thing you can do when working with spreadsheets  
b) Ensuring that you are consistent with your formula is critical especially with regards hardcoding of numbers into formula (specifically not doing it)  
c) The structure of your spreadsheet (columns, rows and sheets) is critical in order to enable easy and safe changes to be made on your spreadsheet  
d) Keeping a history of the different versions of your spreadsheet will enable you to easily go back to a version that meets your requirements  
e) Linking spreadsheets is only sensible when no other option exists. In this case you must make sure that all the linked spreadsheets are open when you make changes to any of them.  
ANS>>
 
     
13- The result of the formula in cell B6 will be  
 
 
a) 0.08  
b) 0.04  
c) 8  
d) 4  
e) Don't know  
ANS>>
 
     
14- The VLOOKUP function in D3 will have the following answer (note that the last '1' could also say True)  
 
 
a) Anna  
b) Toni  
c) Adrian  
d) Trevor  
e) Don't Know  
ANS>>
 
     
  Click the NEXT PAGE button to continue  
   
15-  
 
 
a) 6 & 30  
b) 6 & 20  
c) 8 & 30  
d) 8 & 20  
e) Don't know  
ANS>>
 
     
16- In order to make the TRANSPOSE function work (NOT the Transpose via  Paste Special) you need to do the following. Highlight the areas where you want the information to appear, create a TRANSPOSE formula and  
a) Highlight the cells where it is coming from and click ENTER  
b) Highlight the cells where it is coming from and click SHIFT & ENTER  
c) Highlight the cells where it is coming from and click SHIFT & CTRL & ENTER  
d) The TRANSPOSE function does not exist  
e) Don't Know  
ANS>>
 
     
17- If the result of a formula is #N/A, you can improve the formula by using a combination of which functions  
a) IF & ISERROR  
b) IF & CELL  
c) IF & ERROR.TYPE  
d) IF & NA  
e) Don't Know  
ANS>>
 
     
18- To build a spreadsheet that automatically removes duplicates (another way to describe it would be to extract unique items)  from a list the following functions should be considered (either as one formula or over multiple cells)  
a) VLOOKUP & RANK  
b) COUNTIF & VLOOKUP & RANK  
c) SORT  
d) SUMIF & RANK  
e) Don't Know  
ANS>>
 
     
19- In the below Pivot Table, what is the best way to calculate the percentage each item makes up of the total e.g. MTN is 21.6% i.e. 484.23 / 2 237.93  
 
 
   
   
   
   
   
   
   
   
   
   
   
   
 
a) In cell D4 type the formula =B4/$B$9 and copy it down to the other cells  
b) Click in the Pivot Table, go to 'Value Field Settings', click on 'Show Value as' and choose the appropriate option  
c) In cell D4 type equals and click on cell B4, type / and click on B9 so that the formula says =GETPIVOTDATA("Bill",$A$3,"Destination Network","Cell C")/GETPIVOTDATA("Bill",$A$3). Copy it down  
d) Copy and paste (as values) the Pivot Table to another part of the spreadsheet and then create the formula  
e) Don't know  
ANS>>
 
 
20- To determine if a record is unique in a list you can use the following tool or function  
a) ADVANCED FILTER tool  
b) DATA AUTOFILTER tool  
c) COUNTIF  
d) DATA SORT tool  
e) Don't know  
ANS>>
 
     
  Click the NEXT PAGE button to continue  
   
     
  'What  you do in MS Excel' Questionnaire  
  Only 10 minutes to complete  
  This is an optional section but it would be very useful if you can complete it.  
 
  All you need to indicate is how often you do these types of tasks in MS Excel.  
  Have a quick look and only note the situations that you have seen in your work environment
 
  0 means never and 5 means always or very important to you.  
  Move the sliders (see below) to show how often you do this.  
 
 
 
   
   
   
   
   
   
   
   
   
   
   
   
 
  Click the NEXT PAGE button to continue  
   
     
  Only 10 minutes to complete    
  This is an optional section but it would be very useful if you can complete it.  
     
  All you need to indicate is how often you do these types of tasks in MS Excel.  
  0 means never and 5 means all the time.  
  Move the sliders to show how often you do this.  
     
1- Remove unwanted cells/ rows/ columns
  - You spend time cleaning up a spreadsheet by removing cells, rows or columns  
  - You find yourself deleting the same information again and again  
       
  So in the spreadsheet below we need to remove all the headers and footers and only leave the red block
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you remove unwanted cells, rows or columns?
Never >>
<< Always
       
       
2- Work with Dates
  - You do any work involving dates or time in Excel.    
  - You have to convert dates from mm/dd/yyyy to dd/mm/yyyy or vica versa  
  - You need to work out the number of working days between 2 dates  
  - You need to work out the day of the week    
       
  In the spreadsheets below we have various conversion from 'strange' dates in Excel to Excel acceptable dates
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you work with dates/ times in Excel  
Never >>
<< Always
       
       
3- Summarise/ Report Data
  - Take lots of data and turn it quickly into summarised reports    
  - Take transaction data and make monthly report    
  - Report by division, geo location, product etc    
       
  In the spreadsheets below, we need to take thousands of rows data and create reports that summarise or extract useful information
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to summarise lots of data or extract information out of large data sets?
Never >>
<< Always
       
       
4- Simple Graphing
  - Create simple charts and graphs to get your point across    
  - Show a line chart of monthly transactions    
  - Split expenses into categories    
       
  Below examples of some simple graphs that are created in Excel  
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to create simple charts/ graphs to visualise your data
Never >>
<< Always
       
       
5- Dashboards
  - Create dashboards involving tables of data, charts and things like heat maps to get your message across
  - Allow for easy changes to the information being reported in the dashboard  
  - Allow the source data to be changed so that we can report weekly/ monthly/ quarterly etc
       
  In the spreadsheet below, we create a dashboard based on a changeable data set with tables, graphs and heat maps. 
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to create working dashboards that provide management reports
Never >>
<< Always
       
  Click the NEXT PAGE button to continue    
     
6-
  - You need to create forward looking reports estimating what may happen in the near future based on a set of assumptions
  - Create budgets per division to be consolidated    
  - Create longer term forecasts to assess future years    
  - Compare actuals to budget and re-forecast till the end of the year  
       
  Below a summary of a proposed budget with the actuals for comparative purposes  
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to create budgets or forecasts  
Never >>
<< Always
       
       
7- Decision/ Financial Models
  Create spreadsheets that help make a decision e.g.    
  - should we lease or buy    
  - what price should we charge    
  - which supplier should we use    
  - should we proceed with the project    
  - how much should we pay for the business    
  - how much debt do we need/ can we handle    
       
  Below we have created a decision model to decide whether a property can handle the required debt.
       
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
  How often do you need to build spreadsheets to help you make a decision
Never >>
<< Always
       
       
8- Fill in Gaps
  - You receive reports that have gaps in them but in order to be useable, you need to have the data filled in the gaps.
  - You find yourself copy and pasting data again and again to make the spreadsheet useful to use in other reports
       
  So in the spreadsheet below, we need to fill in the spaces so that it shows the information from the cell above
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to fill in gaps in data?  
Never >>
<< Always
       
       
9- Remove Duplicates
  - You remove/ change data, based on a criteria e.g. it is a duplicate, it contains unnecessary data
  - You want to extract the first/ last time you see the data    
       
  In the spreadsheet below we need to identify and remove the red block as it is a duplicate of the first line
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to remove duplicates  
Never >>
<< Always
       
       
10- Split/ join cells/ rows/ Columns
  - You need to split/ join cells in different columns/ rows    
  - You want to extract say the first 3 characters, or    
  - all the characters up to a special character like a comma or hash  
  - Split full names into first name/ surname    
  - Join cells together to make a sentence, unique reference, full names etc  
       
  In the spreadsheets below we need to split these columns into separate columns based on a set position or set character
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to split cells into many columns
Never >>
<< Always
       
  Click the NEXT PAGE button to continue    
     
11-
  - Change the structure of a report so that the rows appear in columns, and columns appear in rows
  - Manipulate a report so that the data repeats itself    
  - Change the look of a report to match the look of another report  
       
  In the spreadsheet below a simple transpose to move a row into a column  
     
       
       
       
       
       
       
       
  In the spreadsheets below we need to pivot a report so that the rows are in columns and the columns in rows
     
       
       
       
       
       
       
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you have to move data around to get it into a specific row/ column format
Never >>
<< Always
       
       
12- Merge Data sources together
  - Join data together e.g. add client details to a sales report    
  - Merge spreadsheets/ databases to create a big database    
  - Consolidate databases to be able to run business intelligence queries  
       
  In the spreadsheet below, we need to get the outstanding expenses from one list and put it into a separate list based on the matching employee number.
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to merge databases/ spreadsheets
Never >>
<< Always
       
       
13- Reconcile Lists
  Compare lists/ databases in order to    
  - reconcile reports    
  - identify missing items    
  - analyse anomalies    
  - compare supplier information to our information    
       
  In the spreadsheet below, we need to compare 2 lists and identify the items that are missing, exist once or appear too many times based on the transaction ID
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to reconcile lists  
Never >>
<< Always
       
       
14- Debt Calculations
  - You do any calculations involving interest rates    
  - Use functions like present value, future value, interest rate, number of periods, beginning or end period calculations.
  - Need to determine what the repayments will be on a loan based on a set IRR  
       
  Below detailed debt calculations with repayments terms and interest capital splits  
       
 
 
   
   
   
   
   
       
     
       
       
       
       
       
       
       
       
       
       
  How often do you need to create debt calculations  
Never >>
<< Always
       
       
15- NPV/ IRR/ Payback type calculations
  - You need to determine the returns on cash flows using NPV, IRR, payback periods etc  
  - You need to compare 2 businesses/ projects and choose 1 based on a common metric  
  - Compare pricing options to see which one is more beneficial to you  
       
  Below a financial model with a section indicating the NPV and IRR of the proposed project.
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to create NPV, IRR or payback based spreadsheets
Never >>
<< Always
       
  Click the NEXT PAGE button to continue    
     
16-
  - You create templates that will be used by other people to capture/ calculate information  
  - You need to aggregate all the information from many spreadsheets  
  - You need to use dropdown boxes so that users can choose out of a pre defined list  
  - The templates will be used to consolidate the information into a single report  
       
  Below a template to be distributed to users where they have free entry and drop down lists to capture date
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to build spreadsheets to be used by other people. 
Never >>
<< Always
       
       
17- Review/ Audit spreadsheet models
  - You receive spreadsheets from other people and need to check their accuracy, integrity etc
  - You often have to point out errors you find in a spreadsheet    
  - You need to get your spreadsheets audited to get debt from a bank  
  - You create a bankable feasibility spreadsheet    
       
  Below the results of a review of the formulas in a spreadsheet to identify an error.  
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to review spreadsheets?  
Never >>
<< Always
       
       
18- Track Data- GANTT charts, Project plans, tracking information
  - You track events in a spreadsheet based on a time line e.g. project timelines, tracking documents flow
  - You itemise data e.g. fixed assets registers, sales lead lists, production scheduling etc  
  - You use Microsoft Project but need to combine it with Excel    
       
  Below a spreadsheet that tracks a project over time.    
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  Below a spreadsheet that tracks the  flow of documents between departments to work out turnaround times
       
       
       
     
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to build spreadsheets to track items, events or timelines
Never >>
<< Always
       
       
19- Running What Ifs
  - You need to run any sensitivities, what-ifs or scenarios    
  - You are asked to re-run a calculation based on different inputs    
  - You need to provide a report based on the impact of a recent event  
  - You are not sure which scenario is the most likely    
       
  Below the possible repayments at different levels of interest rates and repayment periods
       
       
     
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
       
  How often do you need to answer What-If type questions
Never >>
<< Always
       
       
20- Identify Trends / Opportunities
  - You need to work with data to spot problems/ trends    
  - You are doing forensic work    
  - You are involved with Business Intelligence    
  - Data mining is one of your tasks    
  - You are trying to identify clients that have stopped buying, buying more etc  
       
  Below a chart that helps identify a day when unusual amounts of transaction were processed that needs to be investigated
       
 
 
   
   
   
   
   
   
   
   
   
   
   
   
   
   
  How often do you need to analyse data to find trends/ risks/ opportunities (data mining)
Never >>
<< Always
       
  Click the NEXT PAGE button to finalize    
     
     
Thanks for completing the questionnaire  
       
To get the detailed breakdown and recommendations, fill in your details in the yellow below    
and click the SUBMIT button (below) to score your questionnaire. You will receive an      
email with the scores and a link to your report.      
Your Name      
     
Your Email Address      
     
Company Code (if you have been provided with one- fill it in here otherwise ignore)      
     
Location (In South Africa, close to South Africa or further away)  
   
You agree to receive this report via email and 1 more email with customised tips and tricks, after which you can unsubscribe
   
Now Click SUBMIT below (wait 10 sec while it submits- don't click more than once)    
You will receive an email within 5 minutes (please check in your junk mail as well).   
 
   
 
Please note the T&C's