# Excel Exam (60 Mins) UAlbany

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

an electronic file used to organize related data and perform calculations
2. Worksheet
A spreadsheet that contains formulas, values, text and visual aids
3. Workbook
a file containing related worksheets
4. What are the two basic components of a spreadsheet?
workbook and worksheets
5. Input Area
range of cells containing values
6. Output Area
range of cells containing results
7. Cell
intersection of a column and a row
8. Which way do worksheet columns lie?
Vertically
9. Heavy Border around a cell indicates?
it is the active cell
10. Contents of an active cell are displayed in the?
Formula bar
11. Text Data
combination of letters, numbers, and some symbols
12. Number Data
is any numerical value that can be used in a mathematical calculation
13. Date and Time Data
commonly recognized formats for date and time values
14. Formulas
combinations of cell addresses, math operators, values and/or functions (a formula begins with an = sign) return a value.. uses operators
15. The best way to use cell addresses in formulas vs actual data is...
Cell Referencing
16. example
of a valid formula for Excel?
=(A1+A2)^2
17. 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
18. Cell Range
a group of cells
19. Order of Precedence
A set of predefined rules that excel follows to calculate the result of a formula (PEMDAS)
20. Auto Fill
copy the contents of a cell or cell range or to continue a series using the fill handle
21. Moving a Worksheet
Changes its order among sheet tabs
22. Copying a Worksheet
makes a duplicate sheet at the new location
23. Hiding and Unhiding a Column or Row
• Hiding prevents from displaying and printing
• Unhiding returns it to view
• Range is a rectangular group of cells
• Nonadjacent range a group of ranges that are not next to each other
25. Moving/Copying a Range
Preserves text and values, but cell addresses in the formulas will be altered in the pasted location
26. Paste Special Command
used to paste data from the clipboard using a different format
27. Formatting
accentuates and draws attention to meaningful portions of a worksheet
• Header is content appearing at the top of each printed page
• Footer is content appearing at the bottom of each printed page
29. Absolute Reference
• Remains constant throughout a copy operation
• (specified with a dollar sign before the column and row i.e \$B\$4)
30. Relative Reference
• Adjusts during a copy operation
• specified without dollar signs, I.E B4
31. 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
32. 3 types of cell references
• Absolute \$A\$4
• Relative A1
• Mixed \$A1 or A\$1
33. What does the \$ indicate in Cell referencing
Indicates that the row number or column letter will not be modified during a copy
34. Semi-Selection
• Uses the mouse pointer to build a formula containing cell references or ranges
• Technique also called pointing
35. How do Identify an Absolute Reference
Use a \$
36. Relative Cell- Reference
Indicates a cells relative location from the call containing the formula
37. When would a Circular error warning occur?
• If a formula refers to itself
• results in a popup warning
38. Syntax
The set of rules that govern correct formation of a function
39. Argument
is an input, such as a cell or range
40. 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
41. 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)
42. Common Statistical functions
AVERAGE, MEDIAN, MIN, MAX, COUNT, COUNTA, COUNTBLANK, ABS, FREQUENCY, INT, MODE.SNGL,  PI, PRODUCT, RANDBETWEEN and much much more!!
43. TODAY/NOW Functions
• TODAY displays the current date
• NOW function displays the current date and time
44. 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
45. AND function
Returns a TRUE value if all arguments are TRUE... maximum of 30 arguments
46. OR function
returns a TRUE value if any of the logical conditions are true and a FALSE value if all logical conditions are false
47. 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
48. Nested Function
occurs when one function is embedded as an argument to another function
49. Lookup table
• table that organizes data you want to retrieve into different categories
• categories for the lookup table are called Compare values
50. Breakpoint
Lowest value for a category or series
51. 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
52. PMT financial function
Calculates the periodic payment for a loan with a fixed interest rate and term length
53. Range Name
a word or phrase used to identify a cell or cell range
54. PRESENTATION COUNTS! in other words
How you say something is often as important as what you say
55. Chart
Graphical or visual representation of data in a worksheet
56. Data points
Numeric values
57. Data series
a grouping of data points
58. Chart elements include...
Chart area, plot area, x-axis, y-axis, category axis, value axis, legend
59. What is the word used to identify the guide for understanding a charts contents?
Legend
60. 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
61. 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
62. Bar Charts
show numbers horizontally
63. Line Chart
• This type of chart uses a line to connect data points in order to show trends over a period of time (x-axis displays time units, y-axis displays values for each point)
• best for categories that follow sequential order with a constant time interval
64. Area Chart
Emphasizes the magnitude of changes over time by filling in the space B/W lines with a color
65. 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
66. Stock Chart
shows the high, low, opening, and closing prices for individual stocks over time
67. Surface Chart
Displays trends using two dimensions on a continuous curve
68. Doughnut Chart
Displays values as percentages of a whole, but may contain more than one data series
69. Bubble Chart
Shows the relationship among three values by using bubbles
70. What are line charts best used for?
Showing changes in time
71. KISS Principle in designing charts
KEEP IT SIMPLE
72. Pie, column, bar and line charts assume?
numbers are plotted against categories
73. If you change the cell values in the tables related to the charts, do the values in the charts automatically change?
YES
74. Data Source
Is the range that contains the data you want to display in the chart
75. Embedded Chart
Chart is placed in a worksheet next to its data source
76. Sparkline
A miniature chart displayed in a single cell
77. Chart Filter
Controls which data series and categories are visible in a chart
78. Column
Fields
79. Row
Records
80. Freezing Rows and Columns
Lets you keep headings visible as you work with the data in a large worksheet
81. Excel Table
Structured range of related data formatted to enable data management and analysis
82. Field
An individual piece of data
83. Record
Complete set of data for an entity
84. Can table names contain spaces?
NO
85. Table style
Controls the fill color of the row, columns, and records
86. Sorting Data
Rearrange, or sort, the records in a table or range based on the data in one or more fields
87. Structured Reference
A tag or table field name used in a formula or function
88. Filtering
• The process of displaying only records that meet specific conditions
• Ex) Numeric, Date, "Equals",
89. What is the subtotal function used for?
Provides a sum for a range of data
90. Rule
Specifies the type of condition (such as formatting cells greater than a specified value), the type of formatting when that condition occurs
91. When is the New Formatting Rule dialog box used?
To create a customized rule
92. Can file Names have Blanks?
YES
93. Page Break
an indication where data will start on another printed page
94. Conditional Formatting
A set of rules that applies specific formatting to highlight or emphasize cells that meet specifications
95. Indenting
Helps others see the hierarchal structure of data
96. Wrap Text
makes the data appear on multiple times by adjusting the row height to fit the cell contents within the column width
97. COUNTA Function
tallies the numbers of cells in a range that are not blank
98. Value Axis
displays incremental numbers to identify the worksheet values (such as number of jobs or revenue) used to create the chart.
99. Chart Element
component that completes or helps clarify the chart
100. 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.
101. Axis Titles
Labels that describe the category and value exes
102. 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
103. Outline
A hierarchical structure of data organized so that groups can be expanded to show details or collapsed to show high-level structure.
104. Data Mining
Process of identifying trends and patterns in data
105. 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
106. 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
107. What process to PivotTables support?
Data mining
108. Type of Sorts and Filters for a PivotTable
• Sorts- Smallest to largest and Vice Versa
• Filters- report and group
109. Slicer
• Graphical
• provide buttons that you can click for quick filtering
• indicate the current filtering state
110. What are Slicers used for in Excel
Filtering PivotTable data
111. PivotChart
Graphical representation of a PivotTable
112. Binding Constraint
A constraint in Solver enforces to reach a target value
113. Variable
A value that you can change
114. What-If-analysis
process of changing variables to see how changes will affect the calculated results
115. Substitution Value
Value that replaces the original value of a variable in a data table
116. Scenario Manager
• Helps to perform more sophisticated what-if analysis
• Best Case, Most likely, Worst case
• Creates a scenario summary report
A program that can be added into excel to provide enhanced functionality
118. Scenario
A set of values that represent a possible situation
119. One variable data table
A data analysis tool that provides various results based on changing one variable
120. Goal Seek
Method for finding solutions in What-If analysis by working backwards from the end result to determine the input to produce that result
121. Solver
An add in application that manipulates variables based on constraints to find the optimal solution to a problem
122. INDEX Function
A lookup and reference value or reference to a value within a range
123. What What-if analysis tool is best for manipulating one variable to determine one result?
Goal seek
124. Once a slicer is inserted into a worksheet, which of the following actions cannot be performed?
Append the Slicer
125. True or False: Histogram is a tool of the Analysis tool pack?
TRUE
126. Nested Logical function
Logical function embedded in another logical function
127. True or False: the not function can only contain one argument
TRUE
128. Match function
Returns the position of a value on a list
129. Index Function
Returns a value or the reference to a value within a range based on x and y coordinates
130. Autofilter
• Set criteria for fields using the drop-down list for the field
• Can set criteria for multiple fields
131. 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(*)
132. Same Row
And
133. Different Row
OR
134. Database Functions
• similar to statistical functions. their names are preceded with a D
• Three arguments: database,field,criteria
135. Profit Loss/Income Statement
How much money businesses make or lose over a period of time
136. Interpolation
Determining values that do not appear in a given set of values
137. Extrapolation
Inferring values that do not appear in a given value set
138. Depreciation Functions
• SLN,DB,DDB,VBD
• Cost, salvage value, life
139. Amortization
• Process of decreasing or accounting for an amount; over a period
• PMT,IPMT(Interest), PPMT(principal)
140. Analysis Tool
• Add in, just like solver
• Helps create Amortization tables
 Author: jrad95 ID: 300037 Card Set: Excel Exam (60 Mins) UAlbany Updated: 2015-04-12 18:05:51 Tags: Excel Folders: Description: Key terms for the BITM Excel Test at the University at Albany Show Answers: