What-if Analysis
What-If Analysis is the process of changing the values in cells to see how those changes will affect the outcome of formulas on the worksheet.
- Goal Seek: Used when you know the result you want from a formula, but you are not sure what input value the formula needs to get that result.
- Scenario Manager: Allows you to create, save, and switch between multiple sets of input values (scenarios) to see different possible outcomes.
- Data Tables: Shows how changing one or two variables in a formula will affect the results.
Pivot Tables
A Pivot Table is a powerful data summarization tool. It can automatically sort, count, total, or average the data stored in a large table, displaying the summarized data in a new, compact table.
It allows users to "pivot" or reorganize data rows into columns, or columns into rows, to analyze data from different perspectives without altering the original dataset.
Charts (Visualizing Data)
| Chart Type | Best Used For |
|---|---|
| Column / Bar Chart | Comparing values across categories (e.g., sales per region). Column is vertical; Bar is horizontal. |
| Line Chart | Showing trends over a period of time (e.g., stock prices over a month). |
| Pie Chart | Showing proportions of a whole (e.g., market share percentages). Total should add up to 100%. |
| Scatter Plot | Showing the relationship or correlation between two numerical variables. |
Data Validation
Data Validation is a feature used to restrict what data can be entered into a cell. It helps prevent data entry errors.
Drop-down Lists
Create a dropdown menu in a cell so the user can only select from predefined options (e.g., Yes/No, Departments).
Number Limits
Restrict entry to a specific range (e.g., student marks must be between 0 and 100).
Date Restrictions
Ensure only dates within a specific timeframe can be entered.