Spreadsheets Flashcards
Spreadsheet
An electronic file that contains a grid of columns and rows
Displays results of calculations
Enables interpretation of quantitative data for decision making
Worksheet
A single spreadsheet that typically contains
Descriptive labels
Numeric Values
Formulas, Functions
Graphical representations of data, such as charts
Workbook
Collection of one or more related worksheets
Cells use a combination of a letter and a number
Excel Data Types
Text-combination of letters, numbers, symbols, and spaces.
Values-numbers used as the basis of calculations
Dates and Times-variety of formats
Auto Fill
Feature that completes a sequence of words of values
Clearing a Cell
Several options on tool bar or
Press delete
Formula
Combines cell references, arithmetic operations, values, and/or functions used to perform a calculation.
Controlled by order of operations.
Exponentiation - ^
Display Cell Formulas
Press Ctrl + ` to show all formulas
Insert Column or Row
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.
Hide Columns or Rows
Select object then click Format in the cells group on the Home tab.
Can also right click and Hide from menu.
Column Widths
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.
Row Heights
Click Format in the Cells group on the Home tab then Row Height.
Column widths are measured in points or pixels.
Range
A group of adjacent or contiguous cells and is indicated using a colon - G5:H10.
Copy or Cut a Range
Click Cut or Copy in the Clipboard group on the Home tab.
Paste Options
Table 1.5 in the textbook.
Cell Style
A collection of format settings that provide a consistent appearance. They control: Font Font Color and size Borders and fill colors Alignment Number formatting
Cell Styles Gallery
Cell Styles in the Styles group on the Home tab.
Default Alignments
Text - Left
Dates and Values - Right
Indent
Offset of data from the current alignment.
Alignment Group
Home tab
Several commands to align and format data.
Number Format
Table 1.7 in e-text
General, number, currency, accounting, comma, percent, short date, long date
Add Worksheet
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
Page Layout
Several groups to select page setup options. The two main groups are:
Page Setup
Scale to Fit
Table 1.8
Page Setup Group
Options to set margins, orientation, specify page size, and select the print area.
Scale to Fit Group
Options to adjust scaling of the spreadsheet on the printed page.
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.
Sheet Tab
Options for setting the print area, print title, print options, and page order.
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.
Relative Cell Reference
Default method of referencing
Absolute Cell Reference
Provides a constant reference to a specific cell.
Mixed Cell Reference
Combines absolute and relative cell references.
Toggle through Cell References
F4 key
Function
A predefined computation that simplifies creating a formula that performs a complex calculation.
Function’s Arguments
Identify the required inputs:
Cells, values, or arithmetic expressions.
Some require no arguments and some require multiple separated by commas.
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.
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.
Math & Statistics Functions
Sum Average Median -midpoint above/below 1/2 Min =min(A1,B10:C25) Count
Count Functions (3)
Count - tallies the number of cells
CountBlank - counts blank cells
CountA - tallies the number of cells that are not blank
Today
=Today()
Displays the current date in a cell.
Updates each time the workbook is opened or printed.
Now
=Now()
Uses the computer’s clock to display the current date and military time that the workbook was last opened.
If Function
First argument contains an expression that evaluates to true or false.
Second and Third arguments can contain text, cell references, or constants.
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.
PMT Function
Calculates payments for a loan
3 required and 2 optional arguments
=PMT(rate,nper,pv,fv,type)
Chart Parts - Elements
Chart area Plot area X-axis Y-axis Legend Gridlines Error bars Data table Data lables
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.
Data Point
A cell containing a value.
Data Series
A group of related data points.
Chart Area
A chart element
Container for chart and its elements.
Plot Area
The region containing the graphical representation.
Two axes form a border around the plot area.
A chart element
X-axis
The horizontal border that provides a frame of reference for measuring data from left to right.
A chart element
Y-axis
The vertical border that provides a frame of reference for measuring data up and down.
A chart element
Legend
A key that identifies the color, gradient, picture, texture, or pattern assigned to each data series.
A chart element
Category Axis
The axis that displays descriptive labels for the data points plotted in a chart.
Value Axis
The axis that displays incremental numbers to identify the approximate of data points in a chart.
Column Chart
Displays values in vertical columns
Height represents value
Categories display along the horizontal axis.
Compares values across categories (comp. job titles)
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.
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.
Pie Chart
Proportion of individual data points to the total or whole of all the data points.
Displays as a pie.
Combo Chart
Combines two chart types to plot different data types.
Area Chart
Similar to a line chart
Displays colors between the lines to help illustrate the magnitude of changes.
Scatter Chart
Shows a relationship between two numerical variables using their X and Y coordinates.
Display data in educational, scientific, and medical experiments.
Stock Chart
Shows fluctuations in stock prices. High Low Close Open High Low Close Volume High Low Close Volume Open High Low Close
Clustered Column Chart
Compares groups of columns set side by side.
Quick comparisons across data series to compare several data points among categories.
Sizing Boxes
Format tab in the Chart Tools,Format, Size group
Chart Elements
Completes or clarifies the chart Error Bars Gridlines Legend Trendline
Error Bars
Visuals that indicate statistics:
Standard error amount
A percentage
Standard deviation
Gridlines
Help identify the values plotted by the visual elements
Horizontal or vertical lines
Trendline
Depicts trends or helps forecast future data.
Editing/Formatting Chart Title
Improves readability
Select title & type
Format-Chart Elements, Chart Title
Most elements have a formatting pane
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
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
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
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.
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.
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.
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
Quick Analysis
Ctrl + Q
Insert a Chart
Insert on Insert Menu
Display Formula
Ctr + ` (Control plus grave)
Where is a New Row Inserted?
Above the active cell.
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.
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.
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.
What is the benefit of wrapping text in a cell?
To balance a long column heading over data in a column.
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.
Why would you add a fill color to a cell or range or cells?
To emphasize particular data.
Grouping
You can tell spreadsheets are grouped by [Group] after the file name.
Why would you select landscape orientation for a worksheet?
The worksheet has more columns than rows.
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.
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.
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.
Why would you use the sum function over manual calculation using relative references?
The sum function remains constant when copied.
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.
What does the Median function calculate?
The median function calculates the arithmetic midpoint of a range of cells.
What do True and False mean in VLookup?
In Range.
False=exact match
True=approximate match
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.
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
Quick Analysis
Recommends chart styles based on adjacent ranges.
Stacked Bar Chart
Makes it easier to visualize the total of both groups of data.
Categorical.
Format Tab (Charts)
Contains Shape Height & Shape Width boxes.
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.
What is the purpose of creating a combo chart?
You want to combine two different, but related data types.
Three buttons on the right of a chart are:
Chart elements (plus sign) Chart Styles (paint brush) Filter (funnel)
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.
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.
What does the chart style control and where?
Data labels, chart title, and category labels.
Controls fonts, capitalization, font color, and font size.
To temporarily remove data from being depicted in a chart, you should:
Apply chart filters.
Spark Line Markers
Show individual data points within the sparkline.
What is the purpose of creating a sparkline?
To create a simple visualization of data that is contained in one cell.
Benefits of Creating a Table:
Table style formatting
Structured references
Automatic aggregation
Benefits of Naming a Table
The entire table can be referenced using the name with certain features of Excel.
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.
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.
Total Row Calculation
Count
Average
Sum
Conditional Formatting
Data bars to highlight sales points as they compare to total sales.
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.
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.
Columns Area
Displays columns of summarized data for the selected fields.
Pivot table fields pane.
Data Mining
The process of analyzing large volumes of data, using advanced statistical techniques, and identifying trends and patterns in the data.
Data Model
A collection of related tables that contain structured data used to create a database.
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.
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
Pivot Table Fields List
Lists the fields in the data set.
PivotTable Fields pane.
PivotChart
An interactive graphical representation of the data in a PivotTable representing the consolidated data.
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.
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.
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.
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.
Relationship
An association created between two tables where both tables contain a common data field.
Similar to creating a relationship in Access.
Create a Pivot Table from Quick Analysis
- Right click within a dataset and select Quick Analysis on the shortcut menu.
- Click Tables in the Quick Analysis gallery.
- Point to a PivotTable thumbnail to see a preview of the recommendations.
- Click a Pivot Table thumbnail to create.
Slicer
A small window containing one button for each unique item in a field so you can filter a PivotTable quickly.
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
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.
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.
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.
To Modify a PivotTable
Click a value in the PivotTable
To Modify a PivotTable
Click a value in the PivotTable and click Field Settings in the Active Field group.
To Modify a PivotTable
Click a value in the PivotTable and click Field Settings in the Active Field group.
PivotTable Aggregate Default Functions
Sum for numerical fields;
Count for text fields.
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.
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.
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.
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.
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.
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.
Built In Custom Calculations
Displat relationships between values in rows and columns in the PivotTable.
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.
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.
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.
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.
To Create a Pivot Chart
Click inside the PivotTable;
Click PivotChart in the Analyze tab in Tools group.
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.
Filter Elements in PivotChart
Rows becomes Axis (Category)
Columns becomes Legend (Series)
Filters remains
Values remains
How do you show a select group in a PivotTable?
Place the selected data in the Filter area of the Pivot Table Fields pane.
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.
