Microsoft Excel knowledge assessment Questions
This test measures your knowledge of Microsoft Excel. For each question you
will number of possible answers, and only one of those answers will be
correct. To get your excel knowledge level you will need to work both quickly
and accurately.
1. Write a short description for the following questions
a) Outline five functions used in excel giving examples to explain
them.
b) Describe how excel makes doing calculations efficient.
c) What is Auto fill and how is it used in excel.
d) Differentiate the following: -
(i) Worksheet and workbook
(ii) Formulae and function
(iii) Relative cell reference and absolute cell reference
e) Outline steps of adding and renaming a worksheet
f) Save and save as are used in excel, distinguish the two
2. The AutoAdd function adds up numbers in a column or row you specify.
a) True
b) False
3. "AAA" is an example of a ____ in Excel.
a) cell reference c) name box
b) column heading d) row heading
4. _______ quickly highlight(s) important information in a spreadsheet that
match your criteria by applying formatting options, data bars, color
scales, or icon sets.
a) cell references c) Excel tables
b) conditional formatting d) pivot tables
Page | 1
5. The best formula to calculate Profits for January is:
a) =SUM(B2:B3) d) =A4-(A2+A3)
b) =B2-B3 e) =SUBTRACT(B2:B3)
c) =B4-(B2+B3)
6. The best formula to calculate the Average for Profits
is:
a) =(B4+C4+D4)/3 c) =AVERAGE(B4:D4)
b) =MEAN(B4:E4) d) =AVERAGE(B4:E4)
7. As a general rule, Excel will _____-align numbers.
a) right c) top
b) left d) bottom
8. Cell D4 contains the formula =C1+C2. What formula will see when
you paste this into cell E5?
a) =C1+C2 c) =D2+D3
b) =$C1+$C2 d) =E6+E7
9. When you copy a formula that contains an absolute reference to a new
location, the reference ____. a) is updated automatically
a) does not change c) has a dotted outline in its cell
b) becomes bold
10. Which of the following is a logical function?
a) AVERAGE c) SUMPRODUCT
b) IF d) VLOOKUP
Page | 2
11. The image above shows a:
a) Column chart c) Line Chart
b) Bar graph d) Pie Chart
12. Excel will reference the chart’s data source
above as:
a) A1:B7 c) A$1:$B7
b) $A1:B7$ d) $A$1:$B$7
Page | 3
13. The words "Pre-sales" and “Conference Attendance” in the image
above are called the chart ______:
a) accent c) labels
b) key d) legend
14. ____ order arranges content in reverse alphabetical order, from Z to A.
a) Reverse c) Ascending
b) Major d) Descending
15. _______ are a powerful tool to quickly group, summarize, and
rearrange larger datasets.
a) Cell references c) Pivot tables
b) Functions d) Ranges
16. Selecting the “Food” button on the slicer on the right will mean that
the data table on the left will:
a) Show items that match “Food” only.
b) Show items that match everything except for
“Food”.
c) Not be impacted by the “Food” slicer at all.
d) All of the above.
Page | 4
17. A worksheet ____ is a collection of two or more selected worksheets.
a) index c) group
b) roster d) cluster
18. A reference that refers to the same cell or range on multiple
sheets is called a(n) __________ reference.
a) 3-D c) Indexed
b) Clustered d) Pivotal
19. A(n) _______________ helps automate repetitive tasks in Excel
by recording your steps.
a) formula c) solver
b) macro d) process
20. You can use data ___________ to restrict the type of data or the values
that users enter into a cell.
a) auditing c) tracking
b) checking d) validation
21. The ______ function returns TRUE if any of its arguments evaluate to
TRUE and returns FALSE if all of its arguments are evaluated to FALSE.
a) AND c) SOME
b) OR d) LOOKUP
22. To determine the number of salespeople by region who have 50 or
more orders, we use the:
a) COUNT c) COUNTIF
b) COUNTA d) COUNTIFS
23. _____________ calculates the payment for a loan based on constant
payments and a constant interest rate.
a) CPMT c) PMT
b) LOAN d) PPMT
Page | 5
24. A ________ analysis is the process of changing the values in cells to
see how those changes will affect the outcome of formulas on the
worksheet.
a) Change c) Pivotal
b) Performance d) What-if
25. Returns the number of characters you specify from the right side of a
string.
a) CHAR c) RIGHT
b) LEN d) RSTRING
26.Which of the following functions would be used to create a True within a cell? or
False answer
a) IF d) OTHER
b) AND e) WHY
c) NOT
27.To multiply items in Excel, you would use:
a) / c) *
b) X d) _
28.If A1 contains the value of 25, which of these formulas are correct for
increasing the value of A1 by 20%?
a) =1*(1+20%) d) =A1(1+20%)
b) SAUM=A1*(A1+ 20%) e) =A1+20%
c) A2=(A1+20%)
29. Removes all spaces from text except for single
spaces between words.
a) CODE c) TRIM
b) CLEAN d) SUBSTITUTE
Page | 6
30. The table below shows a list of students and the marks they obtained
in each examination
Required: Write a formula using cell names to compute the following
expressions stating the most appropriate cell where the formula is placed:
a) Total marks for each student
b) Average marks for each student
c) Highest total mark
d) Lowest total mark
e) If the pass mark is an average of 60 marks and above, write a
formula to indicate the RESULT as “pass” or “fail” using a logical
function for each student. Briefly explain data types found in Ms excel.
Page | 7
