Excel Questions and Answers

Microsoft Excel level 1:
Training File 1 – Questions

Q1: How many people work in the China Division?
“Simple Sort”.

Q2: What is the total Annual Pay of everyone working in IT?
“Simple Sort”.

Q3: How many people work in Accounts, Cleaner and HR?
“Simple Sort”.

Q4: How many Female Accountants work in Sector B?
“Multi column Sort”.

Q5: How many Junior Managers earn less than £50,000?
“Multi column Sort”.

Q6: What is the average Hourly Rate of all the Junior Managers in Sector A, who work in USA and China?
“Filter”.

Q7: How many people have a Surname that begins with “A” OR begins with “B”?
“Filter”.

Q8: How many people were born in the first 10 days of November and December of 1980?
“Filter”.

Q9: How many people have a birthday in January?
“Filter”.


Microsoft Excel level 2:
Training File 2 – Questions

Q1: What are the total “October Sales” of “Ed Carcash”
“Simple Sort”.

Q2: Calculate and display the total “Nov Sales” of “Scheduled” “Project Management” courses sold by “Alan Hansen”.
“Filter”.

Q3: Display the total “October and November Sales” for “IT” and “Project Management” and state the category and value that has the largest sales.
“Subtotal”.

Q4: Create a simple Pivot Table that displays “October and November Sales” of all “Excel courses” sold by “Jim Splash”.
Using the resultant data. What is the difference between the two grand totals.
Pivot Table”.

Q5: Using the Sales list data, apply conditional formatting to all Sales data that  is greater than £3000 = Green, equal to zero = Red, between 1 and 3000 = Yellow.
“Conditional Formatting”.

Q6: Using the Sales list data quickly create unique “Range Names” of all the data.
“Range Names”.

Q7: Create an IF statement to record if the Sales in Nov are greater than the Sales in Oct. If they are, return “Greater” and if not return “Less”. Apply Conditional formatting to the data so “Greater” is Green and “Less” is Red.
“IF Function”.

Q8: Create a Vlookup to add a 5% “Margin” for all “IT courses” and 10% “Margin” for all “Project Management courses”.
“Vlookup Function”.

Q9: Create a simple Inserted Horizontal bar chart object displaying “Staff Names” and their “total sales for October and November”.
“Chart”.

 

Microsoft Excel level 3:
Training File 3 – Questions

Q1: coming soon
“Validation”.

Q2: coming soon
“Protection”.

Q3: coming soon
“Formula Auditing”.

Q4: coming soon
“Concatenation”.

Q5: coming soon
“Left – Right – Mid – Find – LEN”.

Q6: coming soon
“Goal Seek”.

Q7: coming soon
“Solver + Scenario”.

Q8: coming soon
“Nested IF’s”.

Q9: coming soon
“Formula Arrays”.

 

Microsoft Excel level 1:
Training File 1 – Answers

A1: 14

A2: £336,960

A3: 11

A4: 2

A5: 3

A6: £27

A7: 21

A8: 11

A9: 13

 

 

 

 

 

 

 

Microsoft Excel level 2:
Training File 2 – Answers

A1: £86,208

A2: £28,179

A3: “IT”. Total Sales £1,149,004

A4: [416] Row: Excel 1,2,3, VBA1. Values: Oct Sales, Nov Sales. Filter: Staff Name

A5: Select all Sales data. Select Conditional Formatting icon then Highlight cell rules and in turn Equal too, Greater than and Between.

A6: Click in the table of data then Ctrl + A, to select everything. Formulas tab, Defined names group then Create from selection icon. Choose Top Row option then OK. Select data names from Names box drop down list.

A7: Insert a new column. =IF(E2>D2,”Greater”,”Less”).

A8: Create a separate table with first column of data, IT then Project Management, second column 5% then 10%. Name the table “MarginRate”, Insert a blank column within the list then create a formula =VLOOKUP(H2,MarginRate,2,False). Convert the decimal to percentage using the % icon.

A9: Create a Subtotal sorting by “Staff Name” and selecting “Oct and Nov Sales data”. Highlight the Staff Name and Sales data, then from the Insert tab select Horizontal Bar Chart.

 

 

 

 

 

 

 

 

Microsoft Excel level 3:
Training File 3 – Answers

A1: coming soon

A2: coming soon

A3: coming soon

A4: coming soon

A5: coming soon

A6: coming soon

A7: coming soon

A8: coming soon

A9: coming soon