Computing has advanced over time and is now essential across industries to help organizations make better decisions and boost profits through data analysis. A simple tool like Microsoft Excel serves as an excellent starting point for analyzing various datasets, including medical records, sales data, student performance, and social media metrics.
Data Analysis Basics
Data analysis involves examining, cleaning, organizing, and interpreting data to uncover useful insights, identify patterns, and support decision-making.
Why Clean Your Data?
- Ensures high integrity by removing biases.
- Enables correct use of formulas.
- Aligns with the problem statement.
- Maximizes use of complete sample size.
Types of Data Analysis
- Prescriptive - Uses historical data to recommend actions.
- Predictive - Analyzes past patterns to forecast the future.
- Diagnostic - Explains why something is happening.
Common Excel Terms
Workbook - The entire Excel file containing multiple sheets.
Cell - The smallest "box" where data is entered (e.g., A1).
Column - Vertical groups of cells, labeled A, B, C... at the top.
Row - Horizontal groups of cells, labeled 1, 2, 3... on the left.
- Dataset - A collection of related data organized for analysis.
Basic Excel Functions
Formulas must start with =. They can include numbers, cell references (A1, B2), operators (+, -, *, /), and functions.
MAX & MIN - Find maximum/minimum values in a range.
LEN - Returns the length of text.
TRIM - Removes leading/trailing spaces.
CONCATENATE (or
CONCAT) - Joins text strings.
AVERAGE - Calculates the mean of a range.
Logical Functions
Logical functions test conditions and return results based on TRUE/FALSE.
1. IF & IFS
-
IF:
=IF(logical_test, value_if_true, value_if_false) -
IFS: Multiple conditions
=IFS(condition1, result1, condition2, result2, ...)
Examples:
=IF(G2>30,"OLD","YOUNG")
=IFS(G2>50,"OLD", G2>40,"ABOVE-MEDIUM", G2>30,"MEDIUM","YOUNG")
2. AND & OR
- AND: All conditions must be true.
- OR: Any condition can be true.
Examples:
=IF(AND(G2>40,E2>70000),"H--R","OTHERS")
=IF(OR(G2>40,E2>70000),"H--R","OTHERS")
3. NOT
Negates a condition: =NOT(logical_test) or use <>.
Other operators: <, >, =, <>, <=, >=.
Data Filtering & Sorting
- Sorting: Arranges data (A-Z, newest-oldest, etc.).
- Filtering: Shows data meeting specific conditions.
Filter Function Example:
=FILTER(A2:F10, D2:D10="HR")
Pivot Tables
Pivot tables summarize, sort, count, and reorganize data. They convert rows to columns and perform calculations (Max, Min, Average, etc.).
Data Visualization
Data visualization uses charts (bar, line, pie, scatter plots) to reveal patterns and relationships.
Benefits:
- Quick pattern identification
- Better storytelling with data
- Enhanced decision-making
Excel Dashboards
An Excel Dashboard consolidates key visuals (PivotTables, charts, slicers) into one interactive screen for at-a-glance insights.
Key Components:
- PivotTables for dynamic summaries
- Charts for visual impact
- Slicers for interactivity
- Clean layout design
Top comments (0)