Free Printable MS Advanced Excel Quiz

1.

If a column has joint-data (such as last name, first name), you can automatically "pull" sections of the data into a new column by using:

a)

Flashfill

b)

Autofill

c)

Merge & Center

d)

Copy and Paste

2.

The shortcut key for AutoSUM is:

a)

Ctrl + =

b)

Alt =

c)

= SUM

d)

Shift +

3.

To quickly highlight an entire range within a column, you can click on the first cell of that range and:

a)

Press Ctrl + Shift + ↓

b)

Press Ctrl + Alt + ↓

c)

Double-click cell border

d)

Hold the shift key

4.

The formula that adds values in a specified range that meet a certain condition or criteria is called:

a)

SUMIFS

b)

SUMIF

c)

COUNTIF

d)

SUBTOTAL

5.

Under the order of arithmetic operators, Excel will calculate division before exponents.

a)

TRUE

b)

FALSE

6.

When creating a VLOOKUP function, the table array area can be pre-named to make it easier to type into the formula. To name this range, you would go to the Formulas tab and select:

a)

Name Manager

b)

Create from selection

c)

Define name

d)

Data validation

7.

The formula used to make the error return value of a function look "pretty" or clean and non-confusing is:

a)

IFERROR

b)

IF

c)

ERROR

d)

VLOOKUP

8.

The absolute cell reference uses which symbol:

a)

$

b)

%

c)

*

d)

&

9.

When using the SUBTOTAL command, you must sort by the column you are wanting to subtotal.

a)

True

b)

False

10.

To combine text that is in two different cells, use the formula:

a)

COMBINE

b)

MATCH

c)

CONCATENATE

d)

SUBTOTAL

11.

In formulas, spaces count as characters.

a)

TRUE

b)

FALSE

12.

To add a special sort order to a “Sort By” list, you would use:

a)

SORT A to Z

b)

SUBTOTAL

c)

FILTERS

d)

CUSTOM SORT

13.

In VLOOKUP, the column # refers to the column in the:

a)

table array

b)

worksheet

c)

workbook

d)

list

14.

To create a drop-down list in a cell or cell range, you would use:

a)

the LIST function

b)

DATA VALIDATION

c)

NAME MANAGER

d)

a VLOOKUP function

15.

Unlocked cells are NOT editable if a worksheet or workbook is protected.

a)

TRUE

b)

FALSE

16.

A table can have blank columns and rows in it, if they are selected when the table is created.

a)

TRUE

b)

FALSE

17.

In Excel 2016 mini toolbar is an efficient formatting tool that can be accessed by:

a)

Clicking the view tab on the ribbon

b)

Right clicking a cell selection

c)

Clicking the office button

d)

Clicking the status bar

18.

Which conditional formatting feature can be used to identify duplicate values on a worksheet?

a)

Data Bars

b)

Highlight Cell Rules

c)

Top / Bottom Rules

d)

Color Scales

19.

Cell A2 contains the following e-mail address: support@excel-skills.com

Cell B2 contains the following formula: =MID(A2,FIND(“@”,A2)+1,LEN(A2)-FIND(“@”,A2)+1)

What is the result of the formula in cell B2?

a)

@

b)

excel-skills.com

c)

support

d)

#VALUE

20.

To count how many cells have text in them, use the function:

a)

COUNT

b)

COUNTIF

c)

COUNTA

d)

SUMIF

21.

What is the function that allow us to remove unwanted spaces in a text?

a)

=SUBSTITUTE

b)

=REPLACE

c)

=VALUE

d)

=TRIM

22.

What functions help us changing what is inside a text string in Excel? Select all that apply.

a)

=LEFT

b)

=SUBSTITUTE

c)

=RIGHT

d)

=REPLACE

23.

What are the advantages of XLOOKUP over VLOOKUP?

a)

It has an "If error" option built in

b)

The look-up value doesn't need to be at the left of the dataset.

c)

XLOOKUP has the "Exact match" option set as default.

d)

We don't need to count columns in large datasets.

24.

The function to call an specific amount of characters at the beginning of a text string is:

(a)

25.

If I have the text "123456 - 010" in cell A1, what is the formula that allows me to call the last 3 characters?

a)

=LEFT(A1,3)

b)

=RIGHT(A1,3)

c)

=LEFT(A1,6)

d)

=RIGHT(A1,6)

26.

If I have the text "123456 - 010" in cell A1, what is the formula that allows me to call the first 6 characters?

a)

=LEFT(A1,3)

b)

=RIGHT(A1,6)

c)

=LEFT(A1,6)

d)

=RIGHT(A1,3)

27.

If we have a table with 100 columns, and we want a new table only with columns 1, 50, 2, 10, 99 (in that order), what option is faster?

a)

Copy/pasting each column manually

b)

Data>Data tools>Text to columns

c)

Data>Sort&Filter>Advanced Filter

d)

Stop working, surrender to the void

28.

What function allow us to remove trash characters from a text string? (example: "Text with garbage chars" > Text with garbage chars)

a)

=FORM

b)

=CLEAN

c)

=REMOVE

d)

=WASH

29.

In cell A1 we have the following "CHN_123456", what function help us removing the "CHN_" from that text?

a)

=SUBSTITUTE

(A1,"CHN_","")

b)

=REPLACE

(A1,"CHN_","")

c)

=CHANGE

(A1,"CHN_","")

d)

=LEFT

(A1,"CHN_","")

30.

XLOOKUP can search for values horizontally.

a)

TRUE

b)

FALSE

31.

Microsoft Excel Sheet တစ်ခုတွင် cells အရေအတွက်---------------- ရှိသည်။

a)

(a) 104857 X 1638

b)

(b) 1048576 X 16384

c)

(c) 16384 X 1048

32.

Microsoft Excel Work Sheet တစ်ခုတွင် File Name ဖော်ပြထားသည့် Bar ကို ---------- ဟုခေါ်သည်။

a)

(‌a) Title Bar

b)

(b) Status Bar

c)

(c) Quick Access tool Bar

33.

Microsoft Excel File တစ်ခုတွင် Sheet အရေအတွက် ------------ ရှိသည်။

a)

(a) 255 Sheet

b)

(b) 552 Sheet

c)

(c) 225 Sheet

34.

Microsoft Excel Work Sheet တစ်ခုအား စာမျက်နှာတပ်ရန်(Eg: 1,2,3,…)--------------- ကို အသုံးပြုသည်။

a)

(a) Number of Page

b)

(b) current Page

c)

(c) Page Number

35.

Microsoft Excel File သိမ်းဆည်းထားသည့် လမ်းကြောင်းထည့်ရန်အတွက် ----------- ကို အသုံးပြုသည်။

a)

(a) File Path

b)

(b) File Name

c)

(c) Sheet Name

36.

Pivot Table အားအသုံးပြု၍ Pivot Chart ဆွဲနိုင်သည်။

a)

(a) False

b)

(b) True

37.

Cell တစ်ခုတွင် “AVERAGE(Number1:Number2)” formula ကို အသုံးပြုနိုင်ရန် အတွက် formula ၏ ရှေ့တွင် ------------- ထည့် အသုံးပြုရသည်။

a)

(a) =

b)

(b) -

c)

(c) ==

38.

Cell တစ်ခုအတွင်း နောက်တစ်လိုင်း ဆင်းချင်သောအခါ အသုံးပြုနိုင်သော Shortcut မှာ------------- ဖြစ်သည်။

a)

(a) Ctrl + Enter

b)

(b) Shift + Enter

c)

(c) Alt + Enter

39.

A1 နှင့် A2 ကို ပေါင်းလိုပါက အသုံးပြုရမည့် ပုံသေနည်းမှာ-

a)

(a) =A1+A2

b)

(b) =add(A1:A2)

c)

(c) SUM(A1:A2)

40.

အကယ်၍ “A1=23, A2=20” ဆိုပါစို့၊ A1 နှင့် A2 ကို မြှောက်ချင်သောအခါ အသုံးပြုရမည့် ပုံသေနည်းမှာ-

a)

(a) Multiply A:A2

b)

(b) =A1*A2

c)

(c) =23*20

41.

Chart တစ်ခုတွင် ပါရှိသော အကြောင်းအရာတစ်ခုတစ်ခုချင်စီ၏ ရည်ညွှန်းချက်ကို------------- ဟုခေါ်သည်။

a)

(a) Title

b)

(b) Legend

c)

(c) Axis

42.

Excel တွင် ကိန်းဂဏန်းများသည်-

a)

(‌a) Left aligned

b)

(b) Right aligned

c)

(c) Justify aligned

43.

“B7:B10” ဟု ဖော်ပြခြင်းသည်-

a)

(a) Cells B7 နှင့် cell B10 သာလျှင်

b)

(b) cells B7 မှ B10 အထိ

c)

(c) တစ်ခုမှမဟုတ်ပါ။

44.

အကြီးဆုံး ဂဏန်းကို ရှာသောအခါ အသုံးပြုရမည့် Function မှာ-

a)

(a) =MAX(B1:B3)

b)

(b) =MAXIMUN(B1:B3)

c)

(c) =HIGH(B1:B3)

45.

C1 ထဲမှ C2 ကို နှုတ်လိုပါက အသုံးပြုရမည့် ပုံသေနည်းမှာ-

a)

(a) =C1-C2

b)

(b) C1-C2

c)

(c) MIN(C1:C2)

46.

Sheet တစ်ခုအား ပြင်ဆင်ဖြည့်စွက်၍ မရအောင် Protect Sheet ပြုလုပ်ထားနိုင်သည်။

a)

(a) True

b)

(b) False

47.

Excel Workbook တစ်ခုအား Password ထား၍ အသုံးပြုနိုင်သည်။

a)

(a) True

b)

(b) False

48.

columm A နှင့် columm D အား selection မှတ်ချင်သောအခါ ------------ Key ဖြင့် တွဲဖက် အသုံးပြုရသည်။

a)

(‌a) Ctrl

b)

(b) Shift

c)

(c) Alt

49.

Now() Function အသုံးပြုသောအခါ လက်ရှိ နေ့ နှင့် အချိန်ကို ဖော်ပြပေးသည်။

a)

(a) False

b)

(b) True

50.

Sheet တစ်ခုတွင် Row, Columm များကို Hide /Unhide ပြုလုပ်၍ အသုံးပြုနိုင်သည်။

a)

(a) True

b)

(b) False

51.

The extension of the Excel is ...

a)

a. .xlsx

b)

b. .xlxs

c)

c. .excl

52.

The space that you write within in MS excel is called ...

a)

a. Document

b)

b. Worksheet

c)

c. Slide

53.

To make a column or a row fixed, you can use ...

a)

a. fixed row tab

b)

b. fixed column tab

c)

c. freeze tab

54.

#DIV/0 refers to ...

a)

a. Divide a document error

b)

b. Divide a number by zero

c)

c. Divide zero by a number

55.

To type your phone number, the field must be ...

a)

a. number

b)

b. general

c)

c. text

56.

$B$2 this cell is ...

a)

a. relative to others

b)

b. absolute cell

c)

c. none of the above

57.

... function calculate cells that contain numbers only

a)

a. COUNT()

b)

b. COUNTA()

c)

c. SUM()

58.

... function gives the highest value.

a)

a. MIN()

b)

b. AVG()

c)

c. COUNT()

d)

d. none of the above

59.

IF(10>=10, "OK", "No") this will output ...

a)

a. 10

b)

b. OK

c)

c. NO

60.

When you have a condition you can use ...

a)

a. WHILE()

b)

b. IF()

c)

c. DO()

61.

... function calculate cells that contain numbers only

a)

a. COUNT()

b)

b. COUNTA()

c)

c. SUM()

62.

The IF Statement takes a logical test and determines a result that is either True or False.

a)

True

b)

False

63.

If the cell B2 is 68, what will this IF function give you as a result? =IF (B2>60, 'pass', 'fail')

a)

60

b)

pass

c)

fail

64.

Which of the formulas below contain the correct syntax (formula arguments) for the VLOOKUP function?

a)

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

b)

=VLOOKUP(table_array, lookup_value, col_index_num, range_lookup)

c)

=VLOOKUP(lookup_value, table_array, col_index_num, value)

65.

Which of the following is NOT possible with VLOOKUP?

a)

You can lookup values located in a different worksheet.

b)

You can lookup values located in a column to the right of the column that contains the lookup value.

c)

You can lookup values located in a column to the left of the column that contains the lookup value.

66.

What is a Pivot Table?

a)

A table containing data that is organized horizontally.

b)

A table used to calculate financial pivot values.

c)

A tool used to summarize data.

d)

A table containing only black, grey and white formatting

67.

The formula that adds values in a specified range that meet a certain condition or criteria is called:

a)

SUMIFS

b)

SUMIF

c)

COUNTIF

d)

SUBTOTAL

68.

The formula used to make the error return value of a function look "pretty" or clean and non-confusing is:

a)

IFERROR

b)

IF

c)

ERROR

d)

VLOOKUP

69.

To combine text that is in two different cells, use the formula:

a)

COMBINE

b)

MATCH

c)

CONCATENATE

d)

SUBTOTAL

70.

Which conditional formatting feature can be used to identify duplicate values on a worksheet?

a)

Data Bars

b)

Highlight Cell Rules

c)

Top / Bottom Rules

d)

Color Scales

71.

To count how many cells have text in them, use the function:

a)

COUNT

b)

COUNTIF

c)

COUNTA

d)

TEXT

72.

You can have multiple sheets within the same Google Sheet document.

a)

False

b)

True

73.

A sales manager has requested the highest sale for the first quarter. Which function will be used?

a)

SUM

b)

HIGHEST

c)

MAX

d)

TOTAL

74.

Samantha needs to total the cell range A1 to A10. What is the MOST efficient method to find the answer?

a)

Label

b)

Value

c)

Formula

d)

Function

75.

Adding two cells

a)

SUM(A1:A9)

b)

ADD(A1:A9)

c)

SUMIF(A1:A9)

d)

NONE OF THE ABOVE

76.

In the picture, why is D2 through D5 placed in paranthesis?

a)

So Excel knows to total the cells before performing the next operation.

b)

So the total of the cells can be multiplied rather than added.

77.

Excel follows the ___________________ and first adds the values inside the parentheses.

a)

Order of Operations

b)

Formula Guidelines

78.

By default, all cell references are _________ references.

a)

Absolute

b)

Relative

79.

In order to create a single formula to copy to the other rows, use ______ references so the formula calculates the total for each item correctly.

a)

relative

b)

relational

80.

If you don't want a cell reference to change when copied to other cells, use _________ references

a)

Absolute

b)

relative

81.

What does the Average formula return?

a)

Returns the lowest argument.

b)

Returns the middle argument.

c)

Returns the three highest arguments.

d)

Returns the mean of all its arguments.

82.

What does the Max formula do?

a)

Returns the highest three values in a set of values and ignores text.

b)

Returns the largest value in a set of values and ignores text.

c)

Returns the text that you are searching for.

d)

Returns the smallest value in a set of values and ignores text.

83.

What does the Min formula return?

a)

Returns the smallest three numbers form a set and ignores text.

b)

Returns the smallest value in a set of values and ignores text.

c)

Returns the largest three numbers form a set and ignores text.

d)

Returns the largest value in a set of values and ignores text.

84.

What does the Countif formula return?

a)

Counts the number of cells within a range that meets the given condition.

b)

Counts the number of words within a range that meets the given condition.

c)

Counts the number of columns within a range that meets the given condition.

d)

Counts the number of letters within a range that meets the given condition.

85.

Which operator is used to multiply in Excel?

a)

+

b)

-

c)

/

d)

*

86.

All formulas begin with a(n) _______________.

a)

&

b)

=

c)

+

d)

#

87.

Excel will display _______________________ if the cell is not wide enough.

a)

******

b)

^^^^^

c)

######

d)

>>>>>

88.

Formulas can be copied to adjacent cells with the ____________.

a)

Fill handle

b)

PgDn Key

c)

Function key F4

d)

None of the above

89.

To edit a formula you

a)

Double-click the cell

b)

Click in the formula bar

c)

Press Esc

d)

Both A & B

90.

Answer =2+3*3-2

a)

9

b)

10

c)

11

d)

12

91.

Answer =3+5*(6-2)

a)

23

b)

32

c)

31

d)

64

92.

__________________is an alphanumeric value used to identify a specific cell in a spreadsheet.

a)

Formula

b)

Calculation

c)

Cell address

d)

None of the above

93.

There are two types of references.

a)

Relative and Dependent

b)

Relative and Proportionate

c)

Absolute and Dominant

d)

Relative and Absolute

94.

A predefined formula that performs calculations using specific values in a particular order.

a)

Function

b)

Reference

c)

Chart

95.

Multiple arguments must be separated by a ______________.

a)

Space

b)

Comma

c)

= Sign

d)

!

96.

True or False: You can view a formula by double-clicking the cell that contains the formula.

a)

True

b)

False

97.

Which of the formulas below are valid? Select all that apply.

a)

=F2+F3+F4-53

b)

=R2*D2

c)

=5B+6B

d)

A3+100

98.

A group of cells is called a ________.

a)

cell range

b)

cell cluster

c)

column

d)

row

99.

Where is the fill handle located?

a)

In the bottom-right corner of the selected cell

b)

On the right side of the Home tab on the Ribbon

c)

At the beginning of any formula or function

d)

In Backstage view

100.

You can click the tabs at the bottom of a workbook to switch between ________.

a)

formulas

b)

number formats

c)

worksheets

d)

permissions

MS Advanced Excel Quiz

Total questions: 100

Worksheet time: 52mins

Similar Resources on Wayground