Statistics for Data Science & Analytics

Test your MS Excel skills with this 20-question Trend-Based Conditional Formatting Excel Quiz! Learn how to highlight data trends, use icon sets, color scales, and sparklines, and master custom formatting rules. Ideal for Excel users who want to visualize patterns, analyze data efficiently, and automate formatting tasks. Can you answer all questions correctly? Take the Conditional Formatting Excel Quiz now and level up your spreadsheet expertise! Let us start with the Trend-Based Conditional Formatting Excel Quiz now.

Online conditional formatting excel quiz with Answers

1. When using color scales for trend analysis, what does a red-yellow-green color scale typically represent?

 High values are green, medium yellow, and low red

 Only positive values get colors

 Random color assignments

 High values are red, medium yellow, and low green

2. Conditional formatting by using a formula is advantageous over other conditional formatting because:

 We can easily change the value that the conditional formatting is based on

 We can choose from a greater range of formatting options

 We can more easily see the rule that the conditional formatting is based on

 You can check for more complex conditions, like checking if two criteria are met

3. What does trend-based formatting do?

 Trend based formatting filters the data to only show values that meet specific criteria

 Trend based formatting ranks the data set against itself and applies some form of visual scale

 Trend based formatting highlights values that are over a specified value

 Trend based formatting sorts the data to reveal the trend

4. Conditional formatting can be used to highlight duplicate values.

 True

 False

5. If a cell contains the value 0, what will be displayed when we apply the following custom number format:
[Red]▲;[Green]▼;

 Nothing

 A green down triangle and a green 0

 A zero in the default text colour

 A red up triangle only

6. What type of brackets should we use when specifying colours for our custom number formats?

 Square brackets [ ]

 Angle brackets

 Curly brackets {}

 Round brackets ( )

7. Custom number formats can be accessed in which of the following ways:

 Right-click a cell and select Format Cells

 Go to the Number group in the Home tab in the ribbon, click the dropdown, select More Number Formats

 Go to the Data tab and select Custom Number Format

 Go to Conditional Formatting and select Custom Number Format

8. When customising a number format, which character acts as the separator for specifying different formats for different types of values?

 A dash/hyphen “-“

 A semi-colon “;”

 A colon “:”

 A comma “,”

9. What is the primary purpose of trend-based conditional formatting in Excel?

 To sort data in ascending or descending order

 To protect worksheets from editing

 To create charts automatically

 To visualize data patterns and trends through formatting

10. Sparklines are a useful data visualisation tool for which of the following cases (more than one may apply):

 Observe a trend for a row or column

 Observe multiple trends for various rows or columns in one dashboard

 Observe a detailed display of a trend that allows a user to hover over each data point

 Observe an overlay of trends for comparison

11. If you use the fill handle to create multiple sparklines, the sparklines will form a set of connected charts, meaning if you change one, you change all of them. It is possible to unlink sparklines if you do not wish them to behave this way.

 False

 True

12. Conditional formatting allows you to automatically apply formatting to one or more cells based on the cell value. This means that conditional formatting can automatically change the cell colour, font, border, or even show a different number of decimal places.

 True

 False

13. When applying Icon Sets, the original values still appear in the cell.

 False

 True

14. What is the key difference between a sparkline and a chart in Excel?

 A sparkline does not change after being created even if you change your data

 A sparkline sits within a single cell, unlike a chart which does not sit within a cell

 A sparkline only works in macro-enabled workbooks, unlike charts

 A sparkline is not customisable, unlike a chart which is customisable

15. Which of the following methods allows us to remove conditional formatting (more than one may apply)?

 Selecting the cells and choosing Home > Conditional Formatting > New Rule

 Selecting the cells and choosing Home > Conditional Formatting > Clear Rules

 Selecting the cells and pressing the Delete key

 Selecting the cells and choosing Home > Clear > Clear Formats

16. Which would be the appropriate Conditional Formatting set if you wanted to highlight all values over 300?

 Top/ Bottom Rules

 Highlight Cell Rules

17. Excel allows us to record macros to automate difficult or repetitive procedures. Which of the following is true of recording macros?

 When recording a macro, Excel cannot record your mouse clicks but it can record your keystrokes

 When recording a macro, Excel records your mouse clicks and your keystrokes

 When recording a macro, Excel records your mouse clicks but it cannot record your keystrokes

18. Which conditional formatting option is specifically designed to show trends?

 Data Bars

 Highlight Cells Rules

 Icon Sets

 Color Scales

19. After creating a sparkline, we can drag the fill handle to create multiple sparklines.

 True

 False

20. When customising a number format:

 We first specify how negative values should look, secondly how zero values should look, and thirdly how positive values should look and lastly, how text should look

 We first specify how negative values should look, secondly how positive values should look, and thirdly how zero values should look and lastly, how text should look

 We first specify how zero values should look, secondly how positive values should look, and thirdly how negative values should look and lastly, how text should look

 We first specify how positive values should look, secondly how negative values should look, and thirdly how zero values should look and lastly, how text should look

 Loading …

Question 1 of 20

Trend Based Conditional Formatting Excel Quiz

• Conditional formatting allows you to automatically apply formatting to one or more cells based on the cell value. This means that conditional formatting can automatically change the cell colour, font, border, or even show a different number of decimal places.
• What does trend-based formatting do?
• When applying Icon Sets, the original values still appear in the cell.
• Which of the following methods allows us to remove conditional formatting (more than one may apply)?
• Which would be the appropriate Conditional Formatting set if you wanted to highlight all values over 300?
• Conditional formatting can be used to highlight duplicate values.
• Conditional formatting by using a formula is advantageous over other conditional formatting because:
• Excel allows us to record macros to automate difficult or repetitive procedures. Which of the following is true of recording macros?
• What is the key difference between a sparkline and a chart in Excel?
• Sparklines are a useful data visualisation tool for which of the following cases (more than one may apply):
• After creating a sparkline, we can drag the fill handle to create multiple sparklines.
• If you use the fill handle to create multiple sparklines, the sparklines will form a set of connected charts, meaning if you change one, you change all of them. It is possible to unlink sparklines if you do not wish them to behave this way.
• Custom number formats can be accessed in which of the following ways:
• When customising a number format:
• When customising a number format, which character acts as the separator for specifying different formats for different types of values?
• What type of brackets should we use when specifying colours for our custom number formats?
• If a cell contains the value 0, what will be displayed when we apply the following custom number format: [Red]▲;[Green]▼;
• What is the primary purpose of trend-based conditional formatting in Excel?
• Which conditional formatting option is specifically designed to show trends?
• When using color scales for trend analysis, what does a red-yellow-green color scale typically represent?

Statistics For Data Analyst