Home > Preview
The flashcards below were created by user
jrad95
on FreezingBlue Flashcards.

Spreadsheet
an electronic file used to organize related data and perform calculations

Worksheet
A spreadsheet that contains formulas, values, text and visual aids

Workbook
a file containing related worksheets

What are the two basic components of a spreadsheet?
workbook and worksheets

Input Area
range of cells containing values

Output Area
range of cells containing results

Cell
intersection of a column and a row

Which way do worksheet columns lie?
Vertically

Heavy Border around a cell indicates?
it is the active cell

Contents of an active cell are displayed in the?
Formula bar

Text Data
combination of letters, numbers, and some symbols

Number Data
is any numerical value that can be used in a mathematical calculation

Date and Time Data
commonly recognized formats for date and time values

Formulas
combinations of cell addresses, math operators, values and/or functions (a formula begins with an = sign) return a value.. uses operators

The best way to use cell addresses in formulas vs actual data is...
Cell Referencing

example
of a valid formula for Excel?
=(A1+A2)^2

Difference B/w Clearing and Deleting
 Clearing data from a worksheet removes the data but leaves the blank cells
 Deleting data from a worksheet removes both the data and the cells

Cell Range
a group of cells

Order of Precedence
A set of predefined rules that excel follows to calculate the result of a formula (PEMDAS)

Auto Fill
copy the contents of a cell or cell range or to continue a series using the fill handle

Moving a Worksheet
Changes its order among sheet tabs

Copying a Worksheet
makes a duplicate sheet at the new location

Hiding and Unhiding a Column or Row
 Hiding prevents from displaying and printing
 Unhiding returns it to view

Range and Nonadjacent range
 Range is a rectangular group of cells
 Nonadjacent range a group of ranges that are not next to each other

Moving/Copying a Range
Preserves text and values, but cell addresses in the formulas will be altered in the pasted location

Paste Special Command
used to paste data from the clipboard using a different format

Formatting
accentuates and draws attention to meaningful portions of a worksheet

Header/Footer
 Header is content appearing at the top of each printed page
 Footer is content appearing at the bottom of each printed page

Absolute Reference
 Remains constant throughout a copy operation
 (specified with a dollar sign before the column and row i.e $B$4)

Relative Reference
 Adjusts during a copy operation
 specified without dollar signs, I.E B4

Mixed Reference
 Either the Row or the Column is absolute; the other is relative
 Specified with a dollar sign before the absolute part of the reference, I.E B$4

3 types of cell references
 Absolute $A$4
 Relative A1
 Mixed $A1 or A$1

What does the $ indicate in Cell referencing
Indicates that the row number or column letter will not be modified during a copy

SemiSelection
 Uses the mouse pointer to build a formula containing cell references or ranges
 Technique also called pointing

How do Identify an Absolute Reference
Use a $

Relative Cell Reference
Indicates a cells relative location from the call containing the formula

When would a Circular error warning occur?
 If a formula refers to itself
 results in a popup warning

Syntax
The set of rules that govern correct formation of a function

Argument
is an input, such as a cell or range

When would you use the insert function dialog box?
 Search for a function or select it from a list
 a functions arguments dialog box offers help on each argument

SUM function
 Returns the mathematical sum for a number of cells or ranges; for example
 =SUM(A1:A3) or =SUM(A1,B3,C5) or =SUM(A1:B3,C5:E8)

Common Statistical functions
AVERAGE, MEDIAN, MIN, MAX, COUNT, COUNTA, COUNTBLANK, ABS, FREQUENCY, INT, MODE.SNGL, PI, PRODUCT, RANDBETWEEN and much much more!!

TODAY/NOW Functions
 TODAY displays the current date
 NOW function displays the current date and time

IF functions 3 arguments
 •A
 logical test or condition that is true or false
 •The
 resulting value if the condition is true
 •The
 resulting value if the condition is false

AND function
Returns a TRUE value if all arguments are TRUE... maximum of 30 arguments

OR function
returns a TRUE value if any of the logical conditions are true and a FALSE value if all logical conditions are false

NOT function
 Reverses the results of a logical test
 if the test has a true result, NOT returns a False, and if the test has a false result NoT returns a true

Nested Function
occurs when one function is embedded as an argument to another function

Lookup table
 table that organizes data you want to retrieve into different categories
 categories for the lookup table are called Compare values

Breakpoint
Lowest value for a category or series

VLOOKUP Function
 Requires three arguments:
 1) numeric Value (or cell) to look up
 2) the range of the table
 3) the column number containing the value you want to return

PMT financial function
Calculates the periodic payment for a loan with a fixed interest rate and term length

Range Name
a word or phrase used to identify a cell or cell range

PRESENTATION COUNTS! in other words
How you say something is often as important as what you say

Chart
Graphical or visual representation of data in a worksheet

Data points
Numeric values

Data series
a grouping of data points

Chart elements include...
Chart area, plot area, xaxis, yaxis, category axis, value axis, legend

What is the word used to identify the guide for understanding a charts contents?
Legend

Pie and Exploded Pie charts are effective for...
 Displaying proportional relationships
 only used when the number of categories is small and the relative sizes of the different slices is easily determined

Column Charts/Stacked Column Charts
 Used for displaying numbers
 displays data vertically also have clustered column chart
 depicts total by category instead of each individual data point
 useful when you want to compare totals by category

Bar Charts
show numbers horizontally

Line Chart
 This type of chart uses a line to connect data points in order to show trends over a period of time (xaxis displays time units, yaxis displays values for each point)
 best for categories that follow sequential order with a constant time interval

Area Chart
Emphasizes the magnitude of changes over time by filling in the space B/W lines with a color

XY (Scatter) Chart
 This type of chart shows the relationship B/W two variables
 show patterns or a relationship B/W 2 or more sets of value

Stock Chart
shows the high, low, opening, and closing prices for individual stocks over time

Surface Chart
Displays trends using two dimensions on a continuous curve

Doughnut Chart
Displays values as percentages of a whole, but may contain more than one data series

Bubble Chart
Shows the relationship among three values by using bubbles

What are line charts best used for?
Showing changes in time

KISS Principle in designing charts
KEEP IT SIMPLE

Pie, column, bar and line charts assume?
numbers are plotted against categories

If you change the cell values in the tables related to the charts, do the values in the charts automatically change?
YES

Data Source
Is the range that contains the data you want to display in the chart

Embedded Chart
Chart is placed in a worksheet next to its data source

Sparkline
A miniature chart displayed in a single cell

Chart Filter
Controls which data series and categories are visible in a chart



Freezing Rows and Columns
Lets you keep headings visible as you work with the data in a large worksheet

Excel Table
Structured range of related data formatted to enable data management and analysis

Field
An individual piece of data

Record
Complete set of data for an entity

Can table names contain spaces?
NO

Table style
Controls the fill color of the row, columns, and records

Sorting Data
Rearrange, or sort, the records in a table or range based on the data in one or more fields

Structured Reference
A tag or table field name used in a formula or function

Filtering
 The process of displaying only records that meet specific conditions
 Ex) Numeric, Date, "Equals",

What is the subtotal function used for?
Provides a sum for a range of data

Rule
Specifies the type of condition (such as formatting cells greater than a specified value), the type of formatting when that condition occurs

When is the New Formatting Rule dialog box used?
To create a customized rule

Can file Names have Blanks?
YES

Page Break
an indication where data will start on another printed page

Conditional Formatting
A set of rules that applies specific formatting to highlight or emphasize cells that meet specifications

Indenting
Helps others see the hierarchal structure of data

Wrap Text
makes the data appear on multiple times by adjusting the row height to fit the cell contents within the column width

COUNTA Function
tallies the numbers of cells in a range that are not blank

Value Axis
displays incremental numbers to identify the worksheet values (such as number of jobs or revenue) used to create the chart.

Chart Element
component that completes or helps clarify the chart

Gridlines
Horizontal and vertical lines than span across the chart to help people identity the values plotted by the visual elements, such as column, Excel displays horizontal gridlines for column, line, scatter, stock surface and bubble charts and vertical gridlines.

Axis Titles
Labels that describe the category and value exes

Subtotal
 An aggregate calculation, such as SUM or AVERAGE, that applies for a subcategory of related data within a larger dataset
 Subtotaling feature creates outlines

Outline
A hierarchical structure of data organized so that groups can be expanded to show details or collapsed to show highlevel structure.

Data Mining
Process of identifying trends and patterns in data

Pivot Table
 Excels Interactive data mining Feature
 Great way to summarize data in a professional looking report
 enables you to summarize a ray of data in a excel table into a concise, tabular format for easier reporting and analysis

Can you change the data directly in a PivotTable?
NO, you must edit the excel table then refresh or update, the pivot table to reflect the current state of art objects list

What process to PivotTables support?
Data mining

Type of Sorts and Filters for a PivotTable
 Sorts Smallest to largest and Vice Versa
 Filters report and group

Slicer
 Graphical
 provide buttons that you can click for quick filtering
 indicate the current filtering state

What are Slicers used for in Excel
Filtering PivotTable data

PivotChart
Graphical representation of a PivotTable

Binding Constraint
A constraint in Solver enforces to reach a target value

Variable
A value that you can change

WhatIfanalysis
process of changing variables to see how changes will affect the calculated results

Substitution Value
Value that replaces the original value of a variable in a data table

Scenario Manager
 Helps to perform more sophisticated whatif analysis
 Best Case, Most likely, Worst case
 Creates a scenario summary report

AddIn
A program that can be added into excel to provide enhanced functionality

Scenario
A set of values that represent a possible situation

One variable data table
A data analysis tool that provides various results based on changing one variable

Goal Seek
Method for finding solutions in WhatIf analysis by working backwards from the end result to determine the input to produce that result

Solver
An add in application that manipulates variables based on constraints to find the optimal solution to a problem

INDEX Function
A lookup and reference value or reference to a value within a range

What Whatif analysis tool is best for manipulating one variable to determine one result?
Goal seek

Once a slicer is inserted into a worksheet, which of the following actions cannot be performed?
Append the Slicer

True or False: Histogram is a tool of the Analysis tool pack?
TRUE

Nested Logical function
Logical function embedded in another logical function

True or False: the not function can only contain one argument
TRUE

Match function
Returns the position of a value on a list

Index Function
Returns a value or the reference to a value within a range based on x and y coordinates

Autofilter
 Set criteria for fields using the dropdown list for the field
 Can set criteria for multiple fields

Advanced Filter extends Autofiltr in 2 important ways
 Creates more complex criteria
 copies the rows to another section of the worksheet, leaving the original list intact
 Criteria are case sensitive
 Text entries treated as though they have wildcards(*)



Database Functions
 similar to statistical functions. their names are preceded with a D
 Three arguments: database,field,criteria

Profit Loss/Income Statement
How much money businesses make or lose over a period of time

Interpolation
Determining values that do not appear in a given set of values

Extrapolation
Inferring values that do not appear in a given value set

Depreciation Functions
 SLN,DB,DDB,VBD
 Cost, salvage value, life

Amortization
 Process of decreasing or accounting for an amount; over a period
 PMT,IPMT(Interest), PPMT(principal)

Analysis Tool
 Add in, just like solver
 Helps create Amortization tables

