Microsoft Excel

Microsoft Excel (MS Excel) is a spreadsheet application developed by Microsoft that allows users to store, organize, analyze, and visualize data using tables, formulas, and various built-in tools. It is widely used in businesses, education, and personal projects for tasks such as:

🔹 Key Features of MS Excel:

1. Spreadsheet Structure: Data is arranged in rows and columns within worksheets.

2. Formulas & Functions: Users can perform calculations using built-in functions (SUM, AVERAGE, IF, VLOOKUP, etc.).

3. Charts & Graphs: Helps visualize data using bar charts, pie charts, line graphs, etc.

4. Pivot Tables: Summarizes and analyzes large datasets efficiently.

5. Conditional Formatting: Highlights cells based on conditions (e.g., color-coding values).

6. Data Sorting & Filtering: Helps organize and find specific information quickly.

7. Macros & Automation: Automates repetitive tasks using VBA (Visual Basic for Applications).

🔹 What is Data Analysis?

Data analysis is the process of collecting, organizing, cleaning, analyzing, and interpreting data to extract useful information, identify patterns, and make informed decisions.

Processes involved in data analysis: 

1. Data collection

The first step is gathering data from different sources. The data can be:
✔ Primary Data – Collected directly (e.g., surveys, experiments, observations).
✔ Secondary Data – Obtained from existing sources (e.g., reports, websites, databases).

Example: A teacher collects students' marks from exams.

2. Data cleaning

Raw data often contains errors, duplicates, missing values, or inconsistencies. Data cleaning helps ensure accuracy. 

2. • Key Cleaning Techniques in Excel:

✔ Conditional formatting: Used for highlighting data or cell according the condition given.

✔ Sort & Filter: Used for organizing data to find errors, duplicates, or missing values. 

✔ Data validation: Used for restricting incorrect entries.

3. Analysing using statistical tools and charts .

   • Statistical Tools

✔ Function: A function is a predefined formula in Excel that performs a specific task. Functions simplify complex calculations by following a structured format.

Example:

✅ MAX: =MAX(A1:A10) – Returns the highest value in the range A1 to A10.

✅ MIN: =MIN(A1:A10) – Returns the lowest value in the range A1 to A10.

✅ AVERAGE: =AVERAGE(A1:A10) – Finds the mean (average) of the values in A1 to A10.

✅ MEDIAN: =MEDIAN(A1:A10) – Returns the middle value in the range A1 to A10.

✅ MODE: =MODE.SNGL(A1:A10) – Returns the most frequently occurring value in A1 to A10.

✅ IF: =IF(A1>=40, "Pass", "Fail") – Returns "Pass" if A1 is 40 or more, otherwise returns "Fail".

✅ IFS: =IFS(A1>=80, "Excellent", A1>=60, "Good", A1>=40, "Average", A1<40, "Fail") – Evaluates multiple conditions and returns the corresponding result.

✅ SUM: =SUM(A1:A10) – Adds all the values in the range A1 to A10.

✅ SUMIF: =SUMIF(A1:A10, ">50") – Returns the sum of values in A1:A10 that are greater than 50.

✅ SUMIFS: =SUMIFS(A1:A10, B1:B10, ">50", C1:C10, "<30") – Returns the sum of values in A1:A10 where B1:B10 is greater than 50 and C1:C10 is less than 30.

✅ COUNT: =COUNT(A1:A10) – Counts the number of numeric values in A1 to A10.

✅ COUNTA: =COUNTA(A1:A10) – Counts the number of non-empty cells in A1 to A10 (including text and numbers).

✅ COUNTIF: =COUNTIF(A1:A10, ">50") – Counts the number of values in A1:A10 that are greater than 50.

✅ COUNTIFS: =COUNTIFS(A1:A10, ">50", B1:B10, "<30") – Counts the number of values in A1:A10 that are greater than 50 and in B1:B10 that are less than 30.

✅ RANK: =RANK(A1, A1:A10, 0) – Returns the rank of A1 within the range A1 to A10 (0 for descending order, 1 for ascending order).

✅ VLOOKUP: =VLOOKUP(75, A1:B10, 2, FALSE) – Searches for 75 in column A and returns the corresponding value from column B.

✅ HLOOKUP: =HLOOKUP(75, A1:J2, 2, FALSE) – Searches for 75 in row 1 and returns the corresponding value from row 2.

✅ CONCATENATE (Old Method): =CONCATENATE(A1, " ", B1) – Joins values from A1 and B1 with a space in between.

✅ TEXTJOIN (Newer Method): =TEXTJOIN(", ", TRUE, A1:A10) – Joins values from A1 to A10, separated by commas.

✔ Formula: A formula is a user-defined mathematical expression that performs calculations using operators (e.g., +, -, *, /). It can include cell references, numbers, and functions.

Example:

✅ =A1+A2+A3+A4+A5 – Adds the values in A1, A2, A3, A4, and A5 manually.

✅ =A1+A2*B1 – Adds A1 to the product of A2 and B1.

✅ =(A1+A2)/2 – Adds A1 and A2, then divides by 2. 

3. Data Visualization Using Charts 

Data visualization is the graphical representation of information to help in analysis, decision-making, and communication. Charts and graphs make complex data easy to understand.

🔹 Purpose of Data Visualization 

✅ Quick Insights – Helps in identifying patterns, trends, and outliers in data.
✅ Better Decision-Making – Simplifies complex data for informed decision-making.
✅ Comparing Data – Allows for side-by-side comparisons using different chart types.
✅ Enhanced Communication – Presents data visually for better understanding in reports.

🔹 Types of Charts & Their Elements

📌 1. Column & Bar Charts (For Comparisons)

✔ Column Chart – Best for comparing values across categories.
✔ Bar Chart – Similar to column charts but displayed horizontally.
✅ Example: Comparing monthly sales for different products.

📌 2. Line Chart (For Trends Over Time)

✔ Best for showing trends or progressions over time.
✅ Example: Tracking student performance over a semester.

📌 3. Pie Chart (For Proportions)

✔ Shows percentages or proportions of a whole.
✅ Example: Market share of different companies.

📌 4. Scatter Plot (For Relationships & Correlations)

✔ Used to analyze relationships between two numerical variables.
✅ Example: Relationship between study time and test scores.

📌 5. Combo Chart (For Mixed Data Types)

✔ A mix of two chart types, like bars and lines together.
✅ Example: Revenue (column) vs. profit percentage (line).

In MS Excel, cell references are used in formulas to refer to data from other cells. There are three main types of cell referencing styles: 

1. Relative Referencing: Changes when copied to another cell (default behavior).

✅ Example:
If cell B2 contains =A2+10 and you copy it to B3, it changes to =A3+10.

2. Absolute Referencing ($A$1): Does NOT change when copied. The dollar sign ($) locks the row and column.

✅ Example:

If C2 has =$A$1+10 and you copy it to C3, it remains =$A$1+10.

3. Mixed Referencing ($A1 or A$1): Locks either the row OR the column when copied.

✅ Example:

$A1 → The column (A) is fixed, but the row changes when copied.

A$1 → The row (1) is fixed, but the column changes when copied.

Formula in B2 Copied to C3 Explanation

=A2+10 =B3+10 Relative (both row & column change)

=$A$2+10 =$A$2+10 Absolute (does not change)

=$A2+10 =$A3+10 Mixed (column A is fixed, row changes)

=A$2+10 =B$2+10 Mixed (row 2 is fixed, column changes)

Google Sheets is a free, web-based spreadsheet application developed by Google. It works similarly to Microsoft Excel, but it runs entirely in your web browser. Here’s a quick breakdown:

🔹 Key Features of Google Sheets:

• Create and edit spreadsheets online – You can use it from any device with internet access.

• Real-time collaboration – Multiple people can work on the same sheet at the same time.

• Autosave – All changes are automatically saved to your Google Drive.

• Built-in functions and formulas – Like SUM, AVERAGE, IF, VLOOKUP, and more.

• Charts and graphs – You can visualize your data easily.

• Conditional formatting – Highlight cells based on specific rules.

• Google Apps Script – Automate tasks or create custom functions.

• Integration – Connects easily with other Google Workspace tools like Docs, Forms, Slides, etc.

Google Sheets Microsoft Excel

1. Free to use with a Google account Requires Microsoft 365 subscription

2. Web-based; works in browser Desktop-based (also has web version)

3. Auto-saves to Google Drive Auto-saves with OneDrive (if enabled)

4. Real-time collaboration built-in Collaboration available in Excel Online

5. Limited advanced features Powerful features for data analysis

6. Uses Google Apps Script for automation Uses VBA (Visual Basic for Applications)

7. Accessible from any device with internet Best experience on desktop

8. Good for small to medium data sets Handles large data sets efficiently

9. Easy sharing via link or email Can share via OneDrive or email

10. Ideal for team projects and cloud use Ideal for complex calculations and offline use