Excel Concepts and Functions Quiz

Excel Concepts

• VLOOKUP Function:

  • Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: The value you want to find.
  • table_array: The range containing the data. The lookup value must be in the first column.
  • col_index_num: The column number in the table from which to retrieve the value (1 for the first column, 2 for the second, etc.).
  • range_lookup: Optional argument. TRUE (default) for approximate match; FALSE for exact match.
  • VLOOKUP searches the first column and returns a value from the specified column in the same row.

• IF Function:

  • Syntax: =IF(logical_test, value_if_true, value_if_false)
  • logical_test: The condition to evaluate (e.g., A1>10).
  • value_if_true: The value or action if the condition is TRUE.
  • value_if_false: The value or action if the condition is FALSE.
  • Ideal for logical tests and decision-making.

Excel Basics

• Name Box: Displays the name of the active cell.
• Formula Bar: Displays the formula in the active cell.
• Active Cell: The cell currently selected.

Excel Formatting

• Number Formats:
  • Accounting: Aligns currency symbols (e.g., $, €) to the left, and decimal points. Makes columns of numbers easier to read.
  • Percent: Multiplies the number by 100 and adds a percent sign (%).
  • Number: Allows you to specify the number of decimal places.
  • Currency: Like accounting, but adds a currency symbol. Formats don't change the value, only how it is displayed.

Excel Formulas and Functions

• Formulas and functions start with an equal sign (=). (e.g., =A1+B1)

Cell References

• Relative: Changes based on the cell's position relative to the formula.
• Absolute: Locked with dollar signs ($). The cell reference will not change when the formula is copied or dragged. (e.g., $A$1)
• Mixed: Combines both relative and absolute references. (e.g., $A1 or A$1)
• Use F4 to switch between reference types

Common Excel Functions

• MIN: Returns the smallest value in a given range of numbers.
• MAX: Returns the largest value in a given range of numbers.
• SUM: Adds up all the numbers in a given range or set of numbers.
• AVERAGE: Calculates the average of a set of numbers.
• LARGE: Returns the nth largest value (e.g., the 5th largest) in a data set.
• SMALL: Returns the nth smallest value in a data set.

PowerPoint Concepts

• Animations: Add motion to elements within a slide.
• Transitions: Used to move between slides.
• Notes Pane: Used by the presenter for notes.
• Presenter View: Provides tools for the presenter only.
• .pptx: Editable presentation.
• .ppsx: Presentation-only mode.

Ethics Review

• Information Ethics: Identifying what is right and wrong in Information Systems.
• Individuals: Are the ethical drivers in IS, as their choices either harm or protect data.

Workplace Monitoring

• Monitoring employees is not illegal, but it raises privacy concerns.

Special Concepts

• Nested Function: A function within another function, often to test multiple conditions.
• Cell Range: A range of cells specified by the first and last cells (e.g., A1:C5).
• Colon (:) Used when defining a cell range (e.g., A1:C5).

Logical Operators

• = (equal to)
• > (greater than)
• >= (greater than or equal to)
• < (less than)
• <= (less than or equal to)
• <> (not equal to)

Syntax Rules

• Arguments in functions are separated by commas (,).
• Optional arguments will be in square brackets [].

Charts and Cells

• 4 Most Common Chart Types: Column, Bar, Pie, Line chart.
• Non-Adjacent Cells: Cells not directly next to each other. Use Ctrl to select.
• Chart Elements: Axes, Titles, Data Labels, Data Table, Gridlines, Legend.

Freezing & Sorting

• Freeze: Locks rows and/or columns in place when scrolling.
• Sort: Arranges data in a specific order.
• Filter: Narrow results based on criteria.

Formatted Tables

• Formatted Tables turn a range of cells into a table.
• Table format Options (that can be turned on or off):
  • Header Row
  • First column
  • Last column
  • Banded rows
  • Banded columns
    • Total row