Excel Formulas and Data Analysis Course: Conditional formatting, data validation and duplicates

1. السؤال 1 من 17

   On which ribbon tab do you find the Conditional Formatting button?

   • AData
   • BFormulas
   • CHome
   • DInsert
   أظهر الإجابة

   الإجابة: C. Home

   Conditional Formatting is on the Home tab, in the Styles group.

2. السؤال 2 من 17

   You want each amount to show a coloured bar inside its cell, longer for larger numbers. Which conditional format do you pick?

   • AIcon Sets
   • BTop/Bottom Rules
   • CColor Scales
   • DData Bars
   أظهر الإجابة

   الإجابة: D. Data Bars

   Data Bars show each value's size as a coloured bar in the cell.

3. السؤال 3 من 17

   Icon Sets split a range into groups of values, each with its own icon. How many groups can an icon set have?

   • A2 only
   • B3 to 5
   • C10
   • DOne per cell
   أظهر الإجابة

   الإجابة: B. 3 to 5

   An icon set sorts values into three to five groups, and each group has its own threshold.

4. السؤال 4 من 17

   You want to quickly highlight the 10 largest sales in a column. Which menu do you use?

   • AConditional Formatting > Data Bars > Gradient Fill
   • BData > Data Validation > Whole Number > greater than
   • CConditional Formatting > Top/Bottom Rules > Top 10 Items
   • DData > Sort & Filter > Sort Largest to Smallest
   أظهر الإجابة

   الإجابة: C. Conditional Formatting > Top/Bottom Rules > Top 10 Items

   Top/Bottom Rules > Top 10 Items highlights the highest values in the range without changing the order of the data.

5. السؤال 5 من 17

   How do you highlight email addresses that appear more than once in a column?

   • AData > Data Validation > Allow: List, with the emails as Source
   • BData > Data Tools > Remove Duplicates, then check Email
   • CConditional Formatting > Highlight Cells Rules > Duplicate Values
   • DConditional Formatting > Icon Sets > 3 Arrows (Coloured)
   أظهر الإجابة

   الإجابة: C. Conditional Formatting > Highlight Cells Rules > Duplicate Values

   Duplicate Values highlights repeated entries so you can review them. Remove Duplicates deletes them instead.

6. السؤال 6 من 17

   You select B2:B20 (B2 is the active cell) and want to colour each cell when the value in column E of the same row is over 300. Which rule formula do you enter?

   • AE2>300
   • B=$E$2>300
   • C=E2>300
   • D=E20>300
   أظهر الإجابة

   الإجابة: C. =E2>300

   Write the formula for the active cell with a relative reference (E2), so it adjusts for every row. $E$2 would check only row 2.

7. السؤال 7 من 17

   You type A2>B2 (with no equal sign) as a conditional formatting formula. What happens?

   • AExcel adds quotes, so the rule is read as text and fails
   • BEvery cell in the range shows the #NAME? error
   • CExcel adds the equal sign for you and the rule works
   • DEvery cell in the range is formatted all the time
   أظهر الإجابة

   الإجابة: A. Excel adds quotes, so the rule is read as text and fails

   Microsoft warns that if you leave out =, Excel adds = and quotes, and you must remove the quotes before the formula works.

8. السؤال 8 من 17

   Two rules apply to B2:B10: "greater than 60,000: blue fill" is above "greater than 70,000: yellow fill" in the Rules Manager. B2 holds 75,000. What fill does B2 get?

   • AYellow
   • BBoth colours
   • CBlue
   • DNo fill
   أظهر الإجابة

   الإجابة: C. Blue

   When conflicting rules apply, the rule higher in the list has precedence. 75,000 is over 60,000, so the blue rule wins. Use Move Up/Move Down to change the order.

9. السؤال 9 من 17

   In the Conditional Formatting Rules Manager, what does the Stop If True box do?

   • AIt deletes the rule after it has been applied once
   • BIt turns off conditional formatting in the whole workbook
   • CLower rules are not checked when this rule is true
   • DIt locks the formatted cells so nobody can edit them
   أظهر الإجابة

   الإجابة: C. Lower rules are not checked when this rule is true

   Stop If True stops rule evaluation at that rule, so lower rules are not applied to those cells.

10. السؤال 10 من 17

    Which command lets you make a drop-down list in a cell?

    • AData > Data Validation, then Allow: List
    • BHome > Conditional Formatting > Icon Sets
    • CInsert > Slicer
    • DData > Remove Duplicates
    أظهر الإجابة

    الإجابة: A. Data > Data Validation, then Allow: List

    In the Data Validation box, choose List in Allow, then type the items separated by commas (for example Low,Average,High) in Source.

11. السؤال 11 من 17

    You want a cell to accept only whole numbers from 1 to 40. What do you choose in Data Validation?

    • AWhole Number, between, minimum 1 and maximum 40
    • BDecimal, greater than 1, with no maximum
    • CText Length, between a minimum of 1 and a maximum of 40
    • DList, with 1-40 typed in the Source box
    أظهر الإجابة

    الإجابة: A. Whole Number, between, minimum 1 and maximum 40

    Whole Number limits the cell to whole numbers, and "between" sets lower and upper limits.

12. السؤال 12 من 17

    Which Error Alert style stops people from entering a value that breaks the rule?

    • AStop
    • BInformation
    • CInput Message
    • DWarning
    أظهر الإجابة

    الإجابة: A. Stop

    Stop prevents invalid data; the user can only retry or cancel. Warning and Information let the user keep the value.

13. السؤال 13 من 17

    You added a validation rule to a column that already had data. How can you find old entries that break the rule?

    • AWait, because Excel marks them automatically
    • BUse Circle Invalid Data
    • CRun Data > Remove Duplicates
    • DPress F9 to recalculate the sheet
    أظهر الإجابة

    الإجابة: B. Use Circle Invalid Data

    Excel does not warn you about existing invalid data. Circle Invalid Data draws circles around those cells.

14. السؤال 14 من 17

    A co-worker pastes values into a cell that has data validation. What happens?

    • AThe validation rule is deleted from those cells for good
    • BExcel changes the pasted values so they fit the rule
    • CIt may get through, since validation checks typed entries
    • DExcel always rejects the pasted values with an error
    أظهر الإجابة

    الإجابة: C. It may get through, since validation checks typed entries

    Microsoft says data validation shows messages and blocks invalid entries only when users type directly in a cell. Copied or filled data can bypass it.

15. السؤال 15 من 17

    You select A1:C8 below and use Data > Remove Duplicates with all three columns checked. How many data rows are left?

    	A	B	C
    1	Name	Email	Shift
    2	Ana Diaz	ana@example.com	Day
    3	Ben Cole	ben@example.com	Night
    4	Ana Diaz	ana@example.com	Day
    5	Chen Wu	chen@example.com	Day
    6	Ben Cole	ben@example.com	Day
    7	Dina Roy	dina@example.com	Night
    8	Chen Wu	chen@example.com	Day

    • A4
    • B5
    • C3
    • D7
    أظهر الإجابة

    الإجابة: B. 5

    A row is removed only if all checked columns match an earlier row. Ana Diaz/Day and Chen Wu/Day repeat; Ben Cole has Night and Day, so both stay: 7 - 2 = 5.

16. السؤال 16 من 17

    You select A1:C8 below and use Remove Duplicates with only the Email column checked. How many data rows are left?

    	A	B	C
    1	Name	Email	Shift
    2	Ana Diaz	ana@example.com	Day
    3	Ben Cole	ben@example.com	Night
    4	Ana Diaz	ana@example.com	Day
    5	Chen Wu	chen@example.com	Day
    6	Ben Cole	ben@example.com	Day
    7	Dina Roy	dina@example.com	Night
    8	Chen Wu	chen@example.com	Day

    • A3
    • B4
    • C5
    • D7
    أظهر الإجابة

    الإجابة: B. 4

    Now only Email is compared, so each email address is kept once: ana, ben, chen and dina.

17. السؤال 17 من 17

    In the sheet below, what does =UNIQUE(B2:B8,FALSE,TRUE) return?

    	A	B	C
    1	Name	Email	Shift
    2	Ana Diaz	ana@example.com	Day
    3	Ben Cole	ben@example.com	Night
    4	Ana Diaz	ana@example.com	Day
    5	Chen Wu	chen@example.com	Day
    6	Ben Cole	ben@example.com	Day
    7	Dina Roy	dina@example.com	Night
    8	Chen Wu	chen@example.com	Day

    • Aana@example.com
    • Bben@example.com and dina@example.com
    • CAll four email addresses
    • Ddina@example.com
    أظهر الإجابة

    الإجابة: D. dina@example.com

    With exactly_once set to TRUE, UNIQUE returns only values that appear exactly once. Only dina@example.com appears once.

لوحة المفاتيح: 1–4 للإجابة، وEnter للسؤال التالي.