Excel Skills Exam 2

Question 1

Freeze Panes

Answer 1

Freeze one cell below or to the right of the data you want to freeze
Click “View” and “Freeze Panes”

Question 2

Simple Sort

Answer 2

Click a cell in the column you want to sort
Click Data
Click Sort & Filter (A to Z or Z to A)

Question 3

Multilevel Sort

Answer 3

Click a cell in the column you want to sort
Click Sort & Filter
Select Custom Sort
Select Add Level
Select the column to sort by from the drop-down menu
Select the second column to sort by

Question 4

One day

Answer 4

24 hours

Question 5

One hour

Answer 5

1/24 = 0.041666

Question 6

Noon

Answer 6

Halfway through the day
0.5

Question 7

Week later

Answer 7

Add 7

Question 8

Year later

Answer 8

Add 365

Question 9

Net30

Answer 9

Add 30

Question 10

Period employed

Answer 10

Termination Date - Hiring Date

Question 11

Date Functions

Answer 11

=DATE(yr,mo,day)
=YEAR(datevalue)
=MONTH(datevalue)
=DAY(datevalue)

Question 12

1/1/1900

Answer 12

Assigned as 1

Question 13

AND

Answer 13

Checks if all conditions are TRUE
=AND(condition1, condition2)
=AND(A1>10,A1<20)

Question 14

OR

Answer 14

Checks if at least ONE of the conditions is TRUE
If all of them are false, it returns as FALSE
=OR(condition1, condition2)
=OR(A1>10,A1<5)

Question 15

Combining AND with IF

Answer 15

If both conditions have to be true for it to be valid
=IF(AND(A1>10,A1<20),”Valid”,”Invalid”)

Question 16

Combining OR with IF

Answer 16

If at least one condition has to be true for it to be valid
=IF(OR(A1>10,A1<5),”Out of Range”,”In Range”)

Question 17

SEARCH

Answer 17

NOT case-sensitive
=SEARCH(find_text, within_text, [start_num])
find_text -> The word or letter you’re looking for
within_text -> The cell it’s in
The result is the character number that the word or letter starts at

Question 18

LEFT

Answer 18

When you want to extract a specific number of characters starting from the left side (beginning) of a text string
=LEFT(text/cell, num_chars)

Question 19

MID

Answer 19

Extracts a specific number of characters from the middle of a text string
=MID(text/cell, start_num, num_chars)

Question 20

Sort order of Pivot Table

Answer 20

Right-click on the cell
Click Sort
A to Z or Z to A
or Custom Sorting