Excel Concepts and Functions Study Guide

These flashcards cover key concepts, terminology, functions, and features related to Excel, providing a comprehensive review for the exam.

Key/Unique Field

A field that uniquely identifies each record in a table; no two rows can have the same value.

Common Field

A field that appears in two or more tables and is used to relate/link them together.

Data Series

A set of related data points plotted in a chart, usually coming from a single row or column.

KPI (Key Performance Indicator)

A measurable value that shows how effectively a goal is being met.

Flat File

A single-table database or spreadsheet with no relational links to other tables.

Absolute Reference

A reference in a formula that stays locked on a specific cell regardless of where the formula is copied (e.g., $A$1).

Relative Reference

A reference in a formula that changes automatically when copied to a different cell (e.g., A1).

PivotTable

An interactive tool that summarizes, groups, and analyzes large datasets quickly without altering the original data.

Filter (PivotTables)

A field placed in the Filters area to narrow the data displayed in a PivotTable.

IF Function

Performs a logical test and returns one value if true and another value if false.

SUMIF Function

Adds values only where specified criteria are met.

NPV Function (Net Present Value)

Calculates the present value of a series of future cash flows based on a discount rate.

DATE Function

Constructs a specific date from given year, month, and day values.

Histogram

Shows distribution/frequency of data across ranges (bins).

Radar Chart

Displays multivariate data on axes radiating from a central point for comparison.

Legend

Identifies which color/pattern corresponds to which data series in a chart.

AutoFit

Automatically adjusts the width or height of a column or row to fit the content.

Conditional Formatting

Applies formatting to cells based on rules you set.

VBA (Visual Basic for Applications)

A programming language built into Excel used to write custom automation scripts.

Macro

A recorded or written sequence of actions/commands that automates repetitive tasks in Excel.

ActiveX Mode

Enables more advanced, interactive controls in Excel that can be tied to VBA code.

Combo Box

A dropdown list control that allows users to select a value from a predefined list.

Option Button

A control that lets users select one option from a group of options.

Data Model

A collection of tables and queries in a workbook.

Error Alert (Excel)

Notifies the user of invalid data but may allow them to proceed.

Stop Alert (Excel)

Prevents the user from entering invalid data entirely.

TEXTJOIN Function

Joins text strings together with a specified delimiter.

Module (Macros)

A container in the VBA editor where macro code is written and stored.