bitm Flashcards
What a Spreadsheet is
an electronic file that contains grids made up of columns and rows
- to organize related data and to display results of calculations, enabling interpretation of quantitative data for decision making
Cell
the intersections of columns and rows
Cell reference and cell address
ref.: used in formulas to refer to a specific cell
add.: location of cells
Active cell
the current cell in a worksheet, found in the name box
Name box
displays the address(or name) of the active cell, selected chart, or selected table
used to go to cells and ranges or assign a name
Range
a group of adjacent or contiguous cells in a worksheet (close to each other)
formula
combines cell references, arithmetic operations, values, and/or functions used in calculation
What is a worksheet and a workbook
worksheet: spreadsheet that usually contains descriptive labels, numeric values, formulas, functions, and charts.
workbook: a collection of one or more related worksheets contained within a single file.
How is the sum function written
=SUM()
What is a fill handle
small green square in the bottom right corner of the active cell
What is the cause of ##### in a cell
the data in the cell is too large to fit in the cell, the column width needs to be increased
How many worksheets are available by default when you create a workbook
1
What does a double horizontal or vertical line indicate
hidden rows or columns
What is a way to change a cell without affecting the content?
cell style
What is merge and center and wrap text for
merge and center: to combine selected cells together only cells from the far left cell are remained
wrap text: word-wrapping data on multiple lines within a cell
How do negative numbers appear when using accounting or currency formatting
acc: number within the parenthesis, NO negative sign
cur: negative sign in front of the dollar sign
What can be present while using special format style
hypen or parenthases
Why might hiding cells be useful
data you wont want to display
What is a backstage view in terms of printing
displays print options and the worksheet in print preview mode.
Why might a header or footer be useful
additional information about the worksheet. You can include your name, the date the worksheet was prepared, and page numbers, for example.
What does a range finder do
Highlights and color codes each cell as you type its address
Excel follows PEMDAS but why
to control the sequence in which arithmetic operations are performed, which affects the result of the calculation.
What is a relative reference and an example of one
change when a formula is copied. A1
What is an absolute reference and an example of one
the opposite, remain constant, no matter where they are copied
$A$1
What is a mixed cell reference and an example of one
a combo, either the column letter or the row number that has the absolute reference remains fixed while the other part of the cell reference that is relative changes when the formula is copied.
A$1 or $A1
What is a 3D reference, an example of one, and what types of references can it use
includes the name of the worksheet and can be absolute, mixed, or relative.
Ex:
=Sheet2!$B$2
What are the 6 different category of functions
sum
average
median
min
mina (NA=0)
max
maxa (NA=)
count a(cells with some data)
count blank (blank cells)
What is a round function an example
used when you want to change the stored value to a specified number of decimal places
What function can you use to get today’s exact date and time
=NOW()
What is the difference between VLOOKUP and HLOOKUP formulas
VLOOKUP function looks for the value in the left column of a specified table array, and then returns a related value located in the same row
HLOOKUP function.This data must be arranged in ascending order from left to right.
XLOOKUP and its match modes
like vlookup XLOOKUP can also return approximate matches if no exact match is found, it can look up data on either the left or right of table array.
all of the following look for exact matches, if not found
0: #NA
-1:next smaller value
1: next larger value
2:wild card
=PMT(rate, nper, pv, [fv], [type]) what is this type of function for
calculates payments for a loan with a fixed amount at a fixed periodic rate for a fixed time period.
the IF conditional function
=IF(Logical Test, Value if True, Value if False)
What is the difference between column, bar, and line charts
column: compares values across categories
bar: same as column but displays category names in an easy-to-read format
line charts: over time
What is a combo chart
100% stacked column chart
combination of 2 charts such as, column and line charts
100%: converts individual data points (values) into percentages of the total value.
What is the difference between category axis & value axis
Category axis: displays descriptive labels for the data points
Value axis: displays incremental numbers to identify the approximate value of data points
What is a legend
it identifies the color or pattern assigned to each data series and displays when a multiple series chart is created
What happens when a combo chart contains two types of data
it turns into a dual-axis chart
What is a scatterplot chart
shows the relationship between two numeric variables using their X and Y coordinates.
Sunburst chart(2 in 1)
hierarchy chart illustrating category and subcategory relationship
has one data series no axes
What is alt text used for
a description displayed when the pointer is moved over a chart or image
good for people with disabilities
What is a sparkline
A small line, column, or win/loss chart contained in a single cell
What happens when you freeze column/row label
They stay visible no matter where you are on the worksheet
what is helpful when printing large datasets 3:
if every page contains descriptive ($) column and row labels using print title option
What is a table
structured range that contains related data organized to increase capability to read and analyze information
- column headings remain onscreen
What are structured, unqualified, and fully qualified structured references and some examples
Structured: uses table names and column headers to refer to data, they automatically adjust when the table structure changes
ex: =SUM(sales data[Quantity]*sales data[Price])
Unqualified:used within the same table and refer to columns directly
ex: =[Quantity]*[Price]
Fully qualified structured reference: Includes both and ensures clarity and specificity
ex: =SUM(Sheet1!sales data[Quantity]*Sheet1!sales data[Price]
What does a total row display and what function does it use
Summary stats
SUBTOTAL(function_num, ref1, …)
What does it mean to sort data provide examples
It means putting data into a certain sequence
Text, A-Z or Z-A
Dates, Oldest-Newest or Newest-Oldest
Values, Smallest-Largest
Color, by cell color or font color
What does it mean to filter data
the process of displaying only records that meet specific conditions
What is conditional formatting and examples
The process of cells changing formats after meeting specific conditions
Highlight cells rules, <,>, =, or duplicate, if met font, border, or fill color is changed
top/bottom rules, above average or below it
Data bars, width of bar determines value
Color scales, uses relative values and shades cells differently based on values
Icon sets. symbols that classify data in 3-5 categories based values
what happens when. you explode a slice of a pie chart
it literally stands out
what is quick layout used for
to apply predefined layouts to a chart
what is quick analysis
a set of analytical tools you can use to apply formatting, create charts or tables, and insert basic functions.
1900 date system
considers each new day as a plus 1 to the value
shortcut to create absolute references
f4
what is a data series
a collection of data points (An individual value in a cell that is plotted in a chart)
why is the recommended charts option or Quick analysis helpful
use if you are unsure which type of chart would effectively represent the data
