Excel Skill Test Study Notes

Pivot Tables:
- Summarize large data sets.
- Enable quick data analysis and reporting.
- Can group and filter data dynamically.
Data Tables:
- Used for sensitivity analysis.
- Show how changes in one or two variables affect outcomes.
What-If Analysis:
- Tools include Goal Seek, Scenario Manager, and Data Tables.
- Allows forecasting and decision-making based on varying inputs.
Charts:
- Visual representation of data trends.
- Types include bar, line, pie, and scatter plots.
Solver:
- Optimizes a value by changing multiple variables.
- Useful for maximizing or minimizing functions.

Basic Arithmetic:
- Addition (+), Subtraction (-), Multiplication (*), Division (/).
Common Functions:
- SUM: Adds a range of numbers.
- AVERAGE: Calculates the mean of a set of values.
- COUNT: Counts the number of cells with numerical entries.
- IF: Logical function that returns values based on conditions.
- VLOOKUP: Searches for a value in the first column of a table and returns a value in the same row from a specified column.
Nested Functions:
- Functions used within functions for complex calculations.
- Example: =IF(A1>10, "Over", "Under").
Cell Referencing:
- Relative: Changes when copied to another cell (e.g., A1).
- Absolute: Remains constant when copied (e.g., AAA1).
- Mixed: Either row or column is absolute (e.g., A1or1 or 1orA1).

Purpose:
- Automatically format cells based on specific criteria to highlight important information.
Common Rules:
- Greater than, less than, equal to specific values.
- Text contains, dates occurring, or duplicates.
Data Bars:
- Provides a graphical bar representation within cells based on value magnitude.
Color Scales:
- Applies a gradient of colors to represent the range of values in a range of cells.
Icon Sets:
- Displays icons to represent cell values, facilitating quick visual assessments of data.
Applying Conditional Formatting:
- Select cells > Home tab > Conditional Formatting > Choose Rule Type > Set formatting options.

Pivot Tables:
- Summarize and analyze large data sets efficiently.
- Allow dynamic grouping and filtering for in-depth insights.
Data Tables:
- Conduct sensitivity analysis by showing the impact of variable changes on outcomes.
What-If Analysis:
- Includes tools like Goal Seek, Scenario Manager, and Data Tables for predictive analytics.
- Facilitates decision-making under varying conditions.
Charts:
- Essential for visually representing data trends.
- Common types include bar, line, pie, and scatter plots, each serving different analytical purposes.
Solver:
- A tool designed to optimize outcomes by adjusting multiple variables.
- Useful in scenarios where maximizing or minimizing specific functions is required.

Basic Arithmetic:
- Fundamental operations: Addition (+), Subtraction (-), Multiplication (*), and Division (/).
Common Functions:
- SUM: Totals a specified range of values.
- AVERAGE: Computes the mean of selected data points.
- COUNT: Tallies the number of cells with numeric entries.
- IF: A logical function that returns different results based on condition evaluation.
- VLOOKUP: Locates a value in the first column of a data range and retrieves related information from a specified column.
Nested Functions:
- Involves embedding a function within another for advanced calculations. Example: =IF(A1>10, "Over", "Under").
Cell Referencing:
- Relative: References that adjust when moved or copied (e.g., A1).
- Absolute: References that remain fixed when copied (e.g., AAA1).
- Mixed: A combination where either column or row is fixed (e.g., A1or1 or 1orA1).

Purpose:
- Enhances visual data analysis by automatically applying formats based on specific cell content criteria.
Common Rules:
- Includes conditions like greater than, less than, or equal to specific figures.
- Can apply to text contents, date occurrences, or highlight duplicate values.
Data Bars:
- Renders a visual bar within a cell to represent the magnitude of values.
Color Scales:
- Uses a gradient color scheme to reflect the range of values in a selected cell range.
Icon Sets:
- Displays various icons to help quickly assess the status or value of cell data.
Applying Conditional Formatting:
- Activate by selecting the target cells, navigating to the Home tab, and selecting the desired rule type with formatting options.

Sorting and Filtering:
- Organize data by the ascending or descending order for easier analysis.
- Utilize filters to reveal specific subsets of data based on defined criteria.
PivotTables:
- Create summaries from extensive data sets, allowing for efficient data analysis.
- Dynamically group data according to categories and perform various calculations.
Conditional Formatting:
- Use to visually emphasize cells meeting specific criteria, enhancing data readability.
- Implement tools like color scales, data bars, and icon sets for effective visual representation.
Data Validation:
- Enforce rules on data entry to maintain integrity and accuracy.
- Common applications include dropdown lists for easy selection and numerical limits to guide input.
What-If Analysis:
- Includes tools like Goal Seek, Scenario Manager, and Data Tables for predictive analysis.
- Aid in forecasting outcomes based on the modification of variables.
Charts and Graphs:
- Leverage various chart types (bar, line, pie) to present data visually.
- Customize the visuals to enhance clarity and appeal of the data presentation.

Basic Formulas:
- Essential functions include =SUM() for totals, =AVERAGE() for mean values, =COUNT() for item counts, and =MIN() / =MAX() for minimum and maximum values respectively.
Logical Functions:
- =IF() function returns one value if a condition is true and a different value if false.
- Use =AND() and =OR() to evaluate multiple conditions simultaneously.
Lookup Functions:
- =VLOOKUP() searches the first column for a value and returns a corresponding value from the same row.
- =HLOOKUP() operates similarly but searches the first row for values instead.
- Combine =INDEX() and =MATCH() for advanced and flexible data lookups.
Text Functions:
- Use =CONCATENATE() or =TEXTJOIN() to merge text from various cells.
- =LEFT(), =RIGHT(), and =MID() help extract specified characters from text strings.
Date and Time Functions:
- =TODAY() and =NOW() provide the current date and time respectively.
- =DATEDIF() computes the difference between two specified dates.
Array Formulas:
- Execute multiple calculations across an array using a single formula.
- Enter these formulas with CTRL+SHIFT+ENTER to indicate their array nature.