excel exam 2

3D address

Includes row address, ws address, and column address. Used when reference a cell from a different worksheet.

3D address format

sheetname!columnrow

format for referencing several of worksheets

firstsheet:lastsheet!columnrow

Wildcard

A symbol that represents any character

• ? represent single character

• * represents string of characters

Ex - if you had a lot of sheets that started with Grill then you could type grill* to reference every sheet that starts with grill.

Ex 2 - if you wanted to reference every ws with 3 charcacters then could use ???

External references

Establish a link between source workbook containing the data and the destination workbook receiving the data. Allows you to reference cells in other workbooks

External reference formate

[workbookname]sheetname!range

What info does the edit links dialog box show?

Source, type, update, status

T/F you can undo the break link action

False

Hyperlink

text string or graphic image connected to a wide variety of sources

Examples of outside sources a hyperlink could lead to

websites, files, cells and cell ranges, emails, new documents created as the source of the hyperlink

Screentip

gives more info about hyperlink before clicking

Named range

replacing the name of a reference with a more descriptive name

How does excel treat named ranges by default

absolute references

t/f you can use a named range in a formula

true

How to create a workbook template

delete all data, leaving only the formulas and elements and save sheet as a template

purpose of charts

show trends or relationships in data that are easier to see in a graphic representation

How many chart types does excel have and how many categories?

over 60 chart types in 10 different categories

chart subtypes

chart variations within each chart category

Three steps in creating a chart

1. select that data to displat

2. choose the chart type best fit for the data

3. format the chart appearance to max the chart’s impact

What are some examples of chart categories that compare values from different categories

column, bar, line, area

format pane

the thing that pops up on the right when you need to edit a chart

column chart

display values in different cateogies as columns where the height of each colum is based on its value

bar chart

column chart turned on its side, length of each bar based on its value

When should you use a column and bar chart over a pie chart

• the number of categories is large

• the data values are close in value

• easier to compare height or length than area

• column charts can include several data series

clustered column chart

displays data series in separate columns side-by side so you can compare relative height

stacked column chart

places that data series values within the combined columns showing how much is contributed by each series

Difference between stacked column and 100% stacked column

100% displays percentage for the stacks and stacked column displays numerical

Steps to creating a clustered column chart

• select data source

• select chart type

• move and resize chart

• change chart design

gap width

the space between one category value and the next

• can be modified in format pane

when are line chartes used

when the data consists of values drawn from categories that follow a sequential order at evenly spaced interval

similarity between line chart and column chart

can be used with one or more data series

data marker

individual pue slices in pie chart, columns in column chartm points connected vy line in a line char

combination chart

combines two chart types within a single chart.

benefit of combo chart

can show two sets of data using the chart type that is best for each data set

scatter plot

plots two data series of numeric values against ecah other, typically centeret so representing a between the two

steps to modifying a chart’s data source

• click on the chart to select it

• on chart tools design tab in the data group click select data button

• in the legen entries series section click the add button or the remove button

• click the edit button in the horizontal axis labels section to select the category values for the chart

data bars

conditional format that afds a horizontal bar to background of a cell containing a number. the length of the bar is based on value of each cell in the range (relative)

what does it mean when you say that the data bars are dynamic

they automatically update when cell value changed

goal of a sparkline is to…

convey the max amount of info within a very small space

when would you want to use a sparkline

when you don’t want charts to overwhelm the rest of your ws or take valuable page space

names of the 3 sparklines and their purpose

line sparkline: highlight trends

column sparkline: for column charts

win/loss sparkline: for highlighting positive and negative values

field

iin a data range, information stored in a column

record

in a data range, each row contains a group of related fields

header row

first row of a data range, containing field names (cheese)

what’s a good practcie for data range

to include a data definition table

data definition table

lists the fields included with each record, the type of data stored in each field, and a short description of each field

common error when there is a lot of data in the ws

creating a duplicated record

two ways to remove duplicates

conditional formatting (highlight duplicates) and remove duplicates button in the data tab

ascending order

a to z, dates old to new, numbers small to large

descending

z to a = numbers large to small, dates from new to old

primary sort field

the first sort field when sorting by multiple fields

secondary sort field

the second sort condition when sorting by multiple fields

subtotals

summary functions that are applied to part of a data range

subtotal outline view

lets you control the level of detail displayed in the ws

2 purposes of find and select

can locate cells that match a specified criterion and list multipl cells that satisfy criterion

t/f filtering rows removes them from the ws

false, it hides the rows that do match search criteria

criteria filters

expressions involving dates, times, numeric values, and texr strings

advanced filtering

way of writing a more complicated filter criteria that involve expression that combines fields using the AND and OR logical operators

• create a new table and add crtieria ranges. first add field names in the first row, criterion for each field are listed in subsequent rows, criteria within the same row are combined using and. criteria in a different row are combined using logical or operator

how does excel treat an excel table

a single object that can be managed independently from other data in the workbook

what are some features that are available a table that are not available with a data range

• sorting and filtering tools built into the table itself

• table styles to format features - includes banded rows and colums

• autonomic insertion of a toals row containing summary stats for wach field which update as table edited

• named references to different parts of the table structure, inclduigind table columns, rows, and header rows

structural elements of an excel table

header row, total row, first column, last column, banded rows, banded columns, filter buttons

calculated field

a field that contains a formula that references other fields in the table

t/f calcuated fields update automatically

true

how are calculated fields different from cell references

use field names encolsed in brackets instead of cell referencesst

ructural references

refernces to fields and elemtns in an excel table and enclosed in square brackets

purpose of @ symbol in structured reference

refers to current record or row within table

purpose of & in structured reference

join fields together

ex - make full name by joining first name and last name column

dashboarf

screen providing informative visuals of data, key performance indicators, and stats

slicers

often used in dashboards because they provide a simple and intuitive way to filter the data

t/f stat functions are applied to whole table regardless of filter crtieria

true

how to use stat functions that change based on filter criteria

subtotal function

vlookup

allows you to retrieve a value from a vertical table

match lookup

a lookup in which the lookup value is acompared to ranges of values rather than a single, specific value

approximate match lookups

can reference a vertial or horizontal lookup table and finds ——- by going down the frist column of the table until it locates the row containing the largest value less than or equal to the lookup value. then it retrieves the corresponding values from that row

esact match lookup

occurs when the lookup value must match one of the values in the first column or row of lookup table

Hlookup

same as vlookup but for horizontal tables

logical functions

used to set data values based n whether a condition is true or false

IF function

tests for one condition if(logical test, value if true, value if false)

IFS function

tests for multiple conditions without nesting (IFS(logical test, value if true, logical test 2, value if true…) can use TRUE at the end to write a condition if the data does not meet crtieria of other tests

OR function

combines multiple conditions, returning value of true if any of the conditions are true or(logical test 1, logical test 3…)

and function

return value of true if all conditions are true and(logical 1, logical 2)

t/f and and or only return true or false

true

conditional sums, averages, count, etc

will perform function based on certain conditions sumif(range criteria, criteria, sum range)

ex - if you want to calcilate the average score of students who were premed in an excel sheet of all students in all majors then you could say average(range containing majors, major cell with premed range with graes)