Excel Questions

Question 1: Which one of the following is true for Conditional Formatting technique in Excel?

a) Conditional Formatting is a quick tool to visualize data when certain conditions are met.

b) We can apply multiple Conditional Formatting to a single column.

c) "Data bars" and "Cell Rules" are two techniques used in Conditional Formatting.

d) Order of rules for Conditional Formatting is important.

e) All of the above

Question 2: The following picture shows sales data for 4 companies. They produce two products, coffee and cocoa. Determine for each part how we can obtain the highlighted results using Conditional Formatting.

For the column " Product" we used conditional formatting to highlight coffee from two products.

i) Which "rule type" is used to create the result?

For the column " Amount" we used conditional formatting to highlight some values from others.

ii) If we used "Format values where this formula is true", which one the following formulas can produce the highlighted results?

i) Rule type:

a) Format only unique or duplicate values

b) Format only values that are above or below average

c) Format only cells that contain

d) Format cells based on their values

ii) Formula for formatting rule:

a) =$2D2<>300

b) =$D2<900

c) =$D2=540

d) =$D2>500

Question 3: GPA of 100 students can be found  here. Answer the following questions based on the table below.

Hint: 

1- Use if condition to assign a letter grade for each student based on the table below

2- Use conditional formatting to highlight the cells for each part

Note: For this question you do  not need to submit your excel file.

Percentage Letter Grade

0 – 59 F

60 – 69 D

70 – 79 C

80 – 89 B

90 – 100 A

What percentage of students received a F GPA?

a) 40

b) 26

c) 20

d) 30

What percentage of students received a D GPA?

a) 43

b) 21

c) 15

d) 33

What percentage of students received a C GPA?

a) 17

b) 25

c) 33

d) 20

What percentage of students received a B GPA?

a) 17

b) 16

c) 15

d) 12

What percentage of students received an A GPA?

a) 10

b) 9

c) 12

d) 22