Spreadsheets Flashcards
1
Q
Spreadsheet
A
An electronic file that contains a grid of columns and rows
Displays results of calculations
Enables interpretation of quantitative data for decision making
2
Q
Worksheet
A
A single spreadsheet that typically contains
Descriptive labels
Numeric Values
Formulas, Functions
Graphical representations of data, such as charts
3
Q
Workbook
A
Collection of one or more related worksheets
Cells use a combination of a letter and a number
4
Q
Excel Data Types
A
Text-combination of letters, numbers, symbols, and spaces.
Values-numbers used as the basis of calculations
Dates and Times-variety of formats
5
Q
Auto Fill
A
Feature that completes a sequence of words of values
6
Q
Clearing a Cell
A
Several options on tool bar or
Press delete
7
Q
Formula
A
Combines cell references, arithmetic operations, values, and/or functions used to perform a calculation.
Controlled by order of operations.
Exponentiation - ^
8
Q
Display Cell Formulas
A
Press Ctrl + ` to show all formulas
9
Q
Insert Column or Row
A
Select the location of the new object and click Insert in the Cells group on the Home tab.
Select left or above the location to be inserted.
10
Q
Hide Columns or Rows
A
Select object then click Format in the cells group on the Home tab.
Can also right click and Hide from menu.
11
Q
Column Widths
A
Double click right column border or drag.
Click Format in the Cells group on the Home tab, then Column width.
Measured in pixels or characters.
12
Q
Row Heights
A
Click Format in the Cells group on the Home tab then Row Height.
Column widths are measured in points or pixels.
13
Q
Range
A
A group of adjacent or contiguous cells and is indicated using a colon - G5:H10.
14
Q
Copy or Cut a Range
A
Click Cut or Copy in the Clipboard group on the Home tab.
15
Q
Paste Options
A
Table 1.5 in the textbook.
16
Q
Cell Style
A
A collection of format settings that provide a consistent appearance. They control: Font Font Color and size Borders and fill colors Alignment Number formatting
17
Q
Cell Styles Gallery
A
Cell Styles in the Styles group on the Home tab.
18
Q
Default Alignments
A
Text - Left
Dates and Values - Right
19
Q
Indent
A
Offset of data from the current alignment.
20
Q
Alignment Group
A
Home tab
Several commands to align and format data.
21
Q
Number Format
A
Table 1.7 in e-text
General, number, currency, accounting, comma, percent, short date, long date
22
Q
Add Worksheet
A
Click New sheet and its tab is inserted to the right of the selected worksheet.
Delete a worksheet by right-clicking its tab and click delete.
Rename a worksheet right click its tab and click rename.
Move or copy-right-click its tab and click Move or Copy
23
Q
Page Layout
A
Several groups to select page setup options. The two main groups are:
Page Setup
Scale to Fit
Table 1.8
24
Q
Page Setup Group
A
Options to set margins, orientation, specify page size, and select the print area.
25
Scale to Fit Group
Options to adjust scaling of the spreadsheet on the printed page.
26
Page Setup Dialog Box Launcher
Several page setup options at once.
Access options not found on the Ribbon.
Header/Footer tab - table 1.9.
Margins tab-margins and centering.
27
Sheet Tab
Options for setting the print area, print title, print options, and page order.
28
Microsoft Office Backstage view
Displays print options and displays the worksheet in print preview mode.
Can configure to print formulas.
Bottom displays how many pages will print.
29
Relative Cell Reference
Default method of referencing
30
Absolute Cell Reference
Provides a constant reference to a specific cell.
31
Mixed Cell Reference
Combines absolute and relative cell references.
32
Toggle through Cell References
F4 key
33
Function
A predefined computation that simplifies creating a formula that performs a complex calculation.
34
Function's Arguments
Identify the required inputs:
Cells, values, or arithmetic expressions.
Some require no arguments and some require multiple separated by commas.
35
Formula AutoComplete
Displays a list of functions and denied names that match letters as you type the formula.
Displays a ScreenTip to display the arguments.
36
Insert Function Dialog Box
Select Insert Function.
Search, category,
syntax and description of function are listed.
Ok displays Function Arguments box; enter values in argument boxes, Excel constructs formula in Formula Box.
37
Math & Statistics Functions
```
Sum
Average
Median -midpoint above/below 1/2
Min =min(A1,B10:C25)
Count
```
38
Count Functions (3)
Count - tallies the number of cells
CountBlank - counts blank cells
CountA - tallies the number of cells that are not blank
39
Today
=Today()
Displays the current date in a cell.
Updates each time the workbook is opened or printed.
40
Now
=Now()
| Uses the computer's clock to display the current date and military time that the workbook was last opened.
41
If Function
First argument contains an expression that evaluates to true or false.
Second and Third arguments can contain text, cell references, or constants.
42
Vlookup
```
Looks for a value in the left column of a specified table array and returns another value located in the same row from a specified column.
=VLOOKUP(Value,Array,Index,Range)
=VLOOKUP(E3,$A$3:$B$7,2)
Absolute references required.
Column index contains return values.
Range is for inexact value.
```
43
PMT Function
Calculates payments for a loan
3 required and 2 optional arguments
=PMT(rate,nper,pv,fv,type)
44
Chart Parts - Elements
```
Chart area
Plot area
X-axis
Y-axis
Legend
Gridlines
Error bars
Data table
Data lables
```
45
Chart
A visual representation of numerical data
Compares data and reveals trends or patterns.
Composed of Chart Elements
Inserted as an embedded object.
Often advantageous to place in Chart Sheet.
46
Data Point
A cell containing a value.
47
Data Series
A group of related data points.
48
Chart Area
A chart element
| Container for chart and its elements.
49
Plot Area
The region containing the graphical representation.
Two axes form a border around the plot area.
A chart element
50
X-axis
The horizontal border that provides a frame of reference for measuring data from left to right.
A chart element
51
Y-axis
The vertical border that provides a frame of reference for measuring data up and down.
A chart element
52
Legend
A key that identifies the color, gradient, picture, texture, or pattern assigned to each data series.
A chart element
53
Category Axis
The axis that displays descriptive labels for the data points plotted in a chart.
54
Value Axis
The axis that displays incremental numbers to identify the approximate of data points in a chart.
55
Column Chart
Displays values in vertical columns
Height represents value
Categories display along the horizontal axis.
Compares values across categories (comp. job titles)
56
Bar Chart
Displays values in horizontal bars.
Length represents the value.
Categories display along vertical axis.
Similar to Column chart, preferable when category names are long.
57
Line Chart
Displays category data on the horizontal axis and value data on the vertical axis.
Used to show continuous data to depict trends over time.
The Category X axis represents time,
The Value Y axis represents a value.
58
Pie Chart
Proportion of individual data points to the total or whole of all the data points.
Displays as a pie.
59
Combo Chart
Combines two chart types to plot different data types.
60
Area Chart
Similar to a line chart
| Displays colors between the lines to help illustrate the magnitude of changes.
61
Scatter Chart
Shows a relationship between two numerical variables using their X and Y coordinates.
Display data in educational, scientific, and medical experiments.
62
Stock Chart
```
Shows fluctuations in stock prices.
High Low Close
Open High Low Close
Volume High Low Close
Volume Open High Low Close
```
63
Clustered Column Chart
Compares groups of columns set side by side.
| Quick comparisons across data series to compare several data points among categories.
64
Sizing Boxes
Format tab in the Chart Tools,Format, Size group
65
Chart Elements
```
Completes or clarifies the chart
Error Bars
Gridlines
Legend
Trendline
```
66
Error Bars
Visuals that indicate statistics:
Standard error amount
A percentage
Standard deviation
67
Gridlines
Help identify the values plotted by the visual elements
| Horizontal or vertical lines
68
Trendline
Depicts trends or helps forecast future data.
69
Editing/Formatting Chart Title
Improves readability
Select title & type
Format-Chart Elements, Chart Title
Most elements have a formatting pane
70
Axis Title
To add: Chart elements, Axis Titles or double click
To format: Double click to display Format Axis Title pane
Fill, line, effect, size, properties
71
Chart Style
A collection of formatting that controls color and effects applied to the chart area, plot area, and data series.
Click Chart Styles or Design tab
72
Modify Data Source
Select Chart Filters or
Open Select Data Source dialogue box in Data Group in Design tab
Change the chart data range or add/edit data
Filter the categories and data series
73
Chart Filter
Determines which data series and categories are displayed.
| By default, all the selected data are used to construct the data series and categories.
74
Sparkline
Small line, column, or win/loss chart in a single cell that displays a condensed, simple, and concise illustration
Click Insert tab and select sparkling type to open Create Sparklines dialog box.
75
Create & Customize Sparklines
Select data before clicking desired sparkling type or select the data range by entering the range in Data Range Box, which will also allow you to select where the sparkling is displayed.
76
Sparkling Tools Design Tab groups
```
Sparkline-edit location/data source
Type-sparkline type
Show-displays points or markers
Style-style, color or marker color
Group-specifies horizontal & vertical axis, groups, ungroups, and clears sparklines
```
77
Quick Analysis
Ctrl + Q
78
Insert a Chart
Insert on Insert Menu
79
Display Formula
Ctr + ` (Control plus grave)
80
Where is a New Row Inserted?
Above the active cell.
81
Why insert a blank row or column before moving data to a new location?
If you don't the data will overwrite existing data when you paste it.
82
What is the benefit of copying data?
Copying data helps eliminate data-entry errors.
| Copying data is more efficient than retyping data most of the time.
83
Why would you apply a cell style rather than individual formats?
To save time in applying several formats at one time.
| To make sure similar items have the same format.
84
What is the benefit of wrapping text in a cell?
To balance a long column heading over data in a column.
85
What is the benefit of intending labels?
Show the hierarchy of categories.
Provide visual space between categories.
Saves time to avoid pressing Spacebar within each cell.
86
Why would you add a fill color to a cell or range or cells?
To emphasize particular data.
87
Grouping
You can tell spreadsheets are grouped by [Group] after the file name.
88
Why would you select landscape orientation for a worksheet?
The worksheet has more columns than rows.
89
Why would you insert the sheet name field in a header or footer?
To identify what data is stored on what worksheet in a multiple worksheet workbook (for printing.)
So that if you change the worksheet name the header will be automatically updated.
90
What is the purpose of viewing a worksheet in print preview?
To make sure data looks good with the current margins and page orientation.
To make sure you won't print any blank pages.
To see if headings repeat on multiple page printouts.
91
What are the benefits of using relative cell referencing?
When you copy a formula using a relative cell reference the copied formula changes relative to the position of the copied formula.
When you modify the data in a cell used in a formula that contains relative references, the formula recalculates.
Relative cell references make formulas that contain large numerical values easier to read.
92
Why would you use the sum function over manual calculation using relative references?
The sum function remains constant when copied.
93
Why would you use the Average function?
Can be applied by clicking the average arrow in the editing group on the Home tab.
Can be copied using the fill handle.
Calculates the arithmetic mean of values in a range of cells.
94
What does the Median function calculate?
The median function calculates the arithmetic midpoint of a range of cells.
95
What do True and False mean in VLookup?
In Range.
False=exact match
True=approximate match
96
VLookup Function
The first column of the lookup table should contain the numerical break points.
Break points must be sorted in ascending order.
The VLookup function can look up approximate or exact values.
97
PMT Function Arguments
```
Rate-Divide APR by # of months in the year
NPer-# of months*# of years
PV-Make a negative number
FV-not used for mtg
Type-not used for mtg
```
98
Quick Analysis
Recommends chart styles based on adjacent ranges.
99
Stacked Bar Chart
Makes it easier to visualize the total of both groups of data.
Categorical.
100
Format Tab (Charts)
Contains Shape Height & Shape Width boxes.
101
Why would you create a pie chart instead of a clustered column or line chart?
You want to show proportion by category to the total.
102
What is the purpose of creating a combo chart?
You want to combine two different, but related data types.
103
Three buttons on the right of a chart are:
```
Chart elements (plus sign)
Chart Styles (paint brush)
Filter (funnel)
```
104
What is the purpose of adding axis titles to a chart?
To provide more clarity about the value or category axis.
To inform people when the numbers are abbreviated.
To conform to ADA compliance.
105
Why would you explode a slice of a Pie Chart?
When you want to draw attention to a slice in a pie chart.
| Value labels don't display by default.
106
What does the chart style control and where?
Data labels, chart title, and category labels.
| Controls fonts, capitalization, font color, and font size.
107
To temporarily remove data from being depicted in a chart, you should:
Apply chart filters.
108
Spark Line Markers
Show individual data points within the sparkline.
109
What is the purpose of creating a sparkline?
To create a simple visualization of data that is contained in one cell.
110
Benefits of Creating a Table:
Table style formatting
Structured references
Automatic aggregation
111
Benefits of Naming a Table
The entire table can be referenced using the name with certain features of Excel.
112
Structured Reference
Automatically created in data tables.
Benefit is that clearly indicate which type of data is used in calculations.
Displayed onscreen if the mouse is hovered over a cell containing the reference.
113
Benefits of Using Filtering
Display only the data you choose.
Multiple filters can be applied to a set.
Can be easily turned on and off.
114
Total Row Calculation
Count
Average
Sum
115
Conditional Formatting
Data bars to highlight sales points as they compare to total sales.
116
Subtotal Row
A row that contains at least one aggregate calculation, such as Sum or Average that applies for a group of sorted data within a dataset.
Saves time from adding subtotal lines manually or manually hiding/unhiding rows.
117
Calculated Field
A user defined field that derives its value based on performing calculations in other fields of the PivotTable.
Calculated field names must be unique.
118
Columns Area
Displays columns of summarized data for the selected fields.
| Pivot table fields pane.
119
Data Mining
The process of analyzing large volumes of data, using advanced statistical techniques, and identifying trends and patterns in the data.
120
Data Model
A collection of related tables that contain structured data used to create a database.
121
Grouping
Joining rows or columns of related data together so that groups can be collapsed or expanded for data analysis.
Hide raw data while focusing on calculated results.
122
Outline
Created by Subtotal command.
A hierarchical structure of data you can group to summarize.
1 Collapses outline to show grand totals only
2 Subtotals by main subtotal category
3 Displays entire list
123
Pivot Table Fields List
Lists the fields in the data set.
| PivotTable Fields pane.
124
PivotChart
An interactive graphical representation of the data in a PivotTable representing the consolidated data.
125
Rows Area
Groups the data into categories in the first column based on the selected fields.
Pivot table fields pane.
The sequence of fields dictates the hierarchy.
126
Pivot Table Report
An interactive table that uses calculations to consolidate and summarize data from a data source into a separate table.
Analyze data without altering the dataset.
Create from Quick Analysis Gallery or Recommended Pivot Tables command in the Tables group.
Changing underlying data doesn't automatically update pivot report.
127
Pivot Table Style
Controls bold formatting, font colors, shading colors, and border.
Click PivotTable Tools Design tab;
Click More in the PivotTable Styles group;
Point to a thumbnail to select a style.
128
Pivot Table Timeline
Filters data based on selected date range.
| Small window w/horizontal tiles that can be clicked to filter data by day, month, quarter, or year.
129
Relationship
An association created between two tables where both tables contain a common data field.
Similar to creating a relationship in Access.
130
Create a Pivot Table from Quick Analysis
1. Right click within a dataset and select Quick Analysis on the shortcut menu.
2. Click Tables in the Quick Analysis gallery.
3. Point to a PivotTable thumbnail to see a preview of the recommendations.
4. Click a Pivot Table thumbnail to create.
131
Slicer
A small window containing one button for each unique item in a field so you can filter a PivotTable quickly.
132
Create a Pivot Table from Recommended Tables Dialogue Box
Click inside the dataset.
Click the insert tab and click Recommended Pivot Tables.
Point to a thumbnail in the gallery, then OK
133
To Create a Blank PivotTable
Click PivotTable in the Insert tab in the Tables group tab.
Select the data to analyze
Select where you want to place the PivotTable - new or existing sheet - then OK.
134
To Add a Field as a Row
Click the field's check box in the fields to add to the report section.
Drag to the Rows area.
135
To Remove a Field from a PivotTable
Click the field name in the Drag Fields between areas below section and select Remove Field.
Deselect the field.
136
To Modify a PivotTable
Click a value in the PivotTable
137
To Modify a PivotTable
Click a value in the PivotTable and click Field Settings in the Active Field group.
138
To Modify a PivotTable
Click a value in the PivotTable and click Field Settings in the Active Field group.
139
PivotTable Aggregate Default Functions
Sum for numerical fields;
| Count for text fields.
140
To Filter a PivotTable
Drag a field to the Filters area in the PivotTable fields list.
Select the value in the list for one item or
Click the Select Multiple items checkbox to filter for more than one item.
141
To Insert a Slicer
Click Insert Slicer on the analyze tab in the filter group.
| Click one or more field check boxes to display one or more slicers, then OK.
142
Ways to Use a Slicer
Filter data by one value/field;
Filter data by multiple values;
Clear a filter.
Deselect a slicer hold down Ctrl and Button.
143
Slicer Tools Options tab
Slicer - change slicer caption
Styles - applies a style
Arrange - placement in relation to other groups;
Buttons - No., height & width in slicer
Size - height and width of slicer window.
144
To Insert a PivotTable Timeline
Insert Timeline on Analyze tab in Filter group;
Click a checkbox and ok.
In Timeline Tools option tab select time level.
145
To Create a Calculated Field
Select a cell in the PivotTable;
Click Fields, Items, and Sets in the Analyze tab in the Calculations group;
Type a narrative label in the Name box;
Build a formula starting with =.
Use Field names (not references) and operands.
146
Built In Custom Calculations
Displat relationships between values in rows and columns in the PivotTable.
147
To Display Values in Relation to Others
Click a field in the Values are of the fields list and click Value Field settings;
Click the Show Values As tab;
Select the desired calculation type;
Click number formats, then OK, OK.
148
To Display Values in Relation to Others
Click a field in the Values are of the fields list and click Value Field settings;
Click the Show Values As tab;
Select the desired calculation type;
Click number formats, then OK, OK.
149
To Create a Relationship in Excel
Click the data tab and Relationships in the Data Tools Group.
Click New to open the dialog.
Click the Table arrow and select the name of the primary table.
Click the Column (Foreign) arrow and select the name containing a relationship to the related or lookup table.
Click the related column that is related to the primary table.
150
To Create a PivotTable from Data Model
Click within the primary table;
Click Insert tab then PivotTable in the Tables group to open Create PivotTable dialog box;
Make sure the primary table name is displayed in the Table/Range box, click the Add this data to the Data Model check box, then OK.
151
To Create a Pivot Chart
Click inside the PivotTable;
| Click PivotChart in the Analyze tab in Tools group.
152
To Create a Pivot Chart
Click inside the PivotTable;
Click PivotChart in the Analyze tab in Tools group.
Changes to the PivotChart also affect the corresponding PivotTable.
153
Filter Elements in PivotChart
Rows becomes Axis (Category)
Columns becomes Legend (Series)
Filters remains
Values remains
154
How do you show a select group in a PivotTable?
Place the selected data in the Filter area of the Pivot Table Fields pane.
155
How would you show values in a PivotTable as a percent of the overall total?
Use the % of Grand Total in Show Values As settings.
