Summary

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.

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.

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.

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.

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.