Skip to main content
11.7: Chapter Review - Summary
-
-
Last updated
-
-
Save as PDF
-
Summary
11.1
Understanding Data, Data Validation, and Data Tables
-
Data can be qualitative or quantitative.
-
There are four levels of measurement for data.
-
Data verification and the Data Validation tool can ensure the quality of data in your spreadsheets.
-
Excel Data Tables can be used to quickly format information in a professional manner.
-
Data Tables can be used to filter and better understand the information contained in a spreadsheet.
11.2
Statistical Functions
-
Data analysis is critical for any business to be successful.
-
Excel’s built-in statistical functions, such as SUM, AVERAGE, MODE, RANGE, and MEDIAN, provide basic information about a dataset.
-
The Analysis ToolPak add-in provides a collection of statistical tools for data analysis such as regression and ANOVA.
-
Descriptive Statistics is a quick way to summarize a dataset by giving you values such as the average, the range, and the maximum number.
-
Regression is used for forecasting and budgeting.
-
The Solver add-in is used to find optimal solutions given a set of predetermined constraints.
11.3
What-If Analysis
-
Scenario Manager and Goal Seek are part of the What-If Analysis tool group.
-
The Scenario Manager is a tool that allows you to investigate the impact of various changes to input cells on target cells.
-
The Scenario Manager can give you a summary of all scenarios to help with decision making.
-
Goal Seek is used when you have a single input variable.
-
Goal Seek is used to set a target value for a cell by changing the values in another cell.
11.4
PivotTables/Charts
-
The PivotTable Analyze tab offers tools to make data analysis and filtering easier.
-
Timelines can be inserted to filter the PivotTable data by date.
-
Slicers can be used in PivotTables and Data Tables to filter data with visual buttons.
-
Calculated fields can be added to PivotTables for additional analysis using formulas.
-
The List Formulas tool creates a separate worksheet that includes all formulas used in the PivotTable. It is a valuable tool when there are multiple users of a single workbook.
11.5
Data Analysis Charts
-
There are many types of charts to visually present your data.
-
Some options for charts or graphs include bar charts, pie charts, column charts, line graphs, and scatter plots.
-
A column chart can be used to show comparisons between groups.
-
A line chart is useful to show changes over time.
-
Scatter plots visually show the relationship between two variables and are often used with regression.
-
Chart templates can be created for chart formats you use frequently such as those with company colors or logos.
-
The Quick Analysis tool gives you access to several options such as conditional formatting, adding totals to the rows or columns, or quick links to creating a chart of the data.
-
Sparklines added to your Data Table can be used to quickly assess trends.