Introduction to Excel

Mathematical Functions

1. SUM(range)

   Adds all numbers in a specified range.
   =SUM(range)
   

   Example:
   =SUM(A1:A5)
   If A1:A5 contains [2, 4, 6, 8, 10], the result is 30.

   

2. SUMIF(range, criteria, [sum_range])

   Adds numbers in a range that meet a specific condition. The optional sum_range specifies what to sum if different from the evaluated range.
   SUMIF(range, criteria, [sum_range])
   

   Example:
   =SUMIF(A1:A5, ">5", B1:B5)
   A1:A5 = [2, 6, 8, 4, 10]
   B1:B5 = [10, 20, 30, 40, 50]
   Sums values in B1:B5 where A1:A5 > 5: 20 + 30 + 50 = 100

   

3. SUMPRODUCT(array1, array2, …)

   Multiplies corresponding elements of arrays and then sums the products.
   SUMPRODUCT(array1, array2, …)
   

   Example:
   =SUMPRODUCT(A1:A3, B1:B3)
   A1:A3 = [2, 3, 4]
   B1:B3 = [5, 6, 7]
   (2×5) + (3×6) + (4×7) = 10 + 18 + 28 = 56.
   

   

4. ROUND(number, num_digits)

   Rounds a number to a specified number of decimal places.
   =ROUND(number, num_digits)
   

   Example:
   ROUND(3.14159, 2) → 3.14
   ROUND(5.678, 1) → 5.7
   (Rounds 5.678 to 1 decimal place.)
   ROUND(123.45, 0) → 123
   (Rounds to the nearest whole number.)

   

5. ROUNDUP()

   Rounds a number up (away from zero) to a specified number of decimal places, regardless of the value.
   =ROUNDUP(number, num_digits)
   

   Example:
   ROUNDUP(3.14159, 2) → 3.15
   (Rounds up to 2 decimal places.)
   ROUNDUP(5.612, 1) → 5.7
   (Rounds up to 1 decimal place.)
   ROUNDUP(-3.14159, 2) → -3.15
   (Rounds away from zero, so up in magnitude for negatives.)

   

6. ROUNDDOWN()

   Rounds a number down (toward zero) to a specified number of decimal places, regardless of the value.
   =ROUNDDOWN(number, num_digits)
   

   Example:
   ROUNDDOWN(3.14159, 2) → 3.14
   (Rounds down to 2 decimal places.)
   ROUNDDOWN(5.678, 1) → 5.6
   (Rounds down to 1 decimal place.)
   ROUNDDOWN(-3.14159, 2) → -3.14
   (Rounds toward zero, so down in magnitude for negatives.)

   

7. MOD()

   Returns the remainder after dividing a number by a divisor.
   =MOD(number, divisor)
   

   Example:
   =MOD(A1, 4) where A1 = 15 → 3 (15 ÷ 4 = 3 remainder 3.)

   

8. ABS()

   Returns the absolute value of a number.
   =ABS(number)
   

   Example:
   =ABS(A1) where A1 = -3.14 → 3.14

   

9. SQRT()

   Returns the absolute value of a number.
   =SQRT(number)
   

   Example:
   =SQRT(A1) where A1 = 16 → 4 (Square root of 16 is 4)

   

10. POWER()

    Raises a number to a specified power.
    =POWER(number, power)
    

    Example:
    =POWER(A1, 2) where A1 = 5 → 25 (5² = 25.)

    

11. EXP()

    Returns e (≈ 2.718281828) raised to the specified power.
    =EXP(number)
    

    Example:
    =EXP(A1) where A1 = 0 → 1 (e⁰ = 1.)

    

12. LN()

    Returns the natural logarithm (base e) of a number (must be positive).
    =LN(number)
    

    Example:
    =LN(A1) where A1 = 10 → ~2.302585093 (Natural log of 10.)

    

13. LOG()

    Returns the logarithm of a number to a specified base (default base 10 if omitted).
    =LOG(number, [base])
    

    Example:
    =LOG(A1, 2) where A1 = 8 → 3 (Log base 2 of 8, since 2³ = 8.)

    

14. PI()

    Returns the value of π (≈ 3.141592654).
    =PI()
    

    Example:
    =PI() → ~3.141592654

15. SIN(), COS(), TAN()

    Returns the sine, cosine, or tangent of an angle in radians.
    =SIN(number), =COS(number), =TAN(number)
    

    Example:
    =SIN(PI()/2) → 1
    (Sine of π/2 radians or 90°.)
    =COS(0) → 1
    (Cosine of 0 radians.)
    =TAN(PI()/4) → ~1
    (Tangent of π/4 radians or 45°, approximate due to floating-point precision.)

    

16. DEGREES()

    Converts radians to degrees.
    =DEGREES(number)
    

    Example:
    =DEGREES(A1) where A1 = PI()/2 → 90 (π/2 radians = 90°.)

    

17. RADIANS()

    Converts degrees to radians.
    =RADIANS(number)
    

    Example:
    =RADIANS(A1) where A1 = 90 → ~1.570796327 (90° = π/2 radians.)

    

Logical Functions

1. IF()

   Performs a conditional check and returns one value if the condition is true, and another if it’s false.
   =IF(logical_test, value_if_true, value_if_false)
   

   Example:
   =IF(A1>10, "Yes", "No")
   If A1 is greater than 10, returns "Yes"; otherwise, "No".

   

2. IFS()

   Evaluates multiple conditions in sequence and returns the result for the first true condition.(Multiple IF conditions)
   IFS(condition1, result1, condition2, result2, …)
   

   Example:
   =IFS(A1>90, "A", A1>75, "B", A1>60, "C")
   Returns "A" if A1>90, "B" if A1>75, or "C" if A1>60. If none are true, it returns an error (#N/A).

   

3. AND()

   Checks if all listed conditions are true. Returns TRUE only if every condition evaluates to true; otherwise, it returns FALSE.
   =AND(condition1, condition2, …)
   

   Example:
   =AND(A1>10, B1<20)
   Returns TRUE if A1>10 AND B1<20; otherwise, FALSE.

   

4. OR()

   Checks if at least one of the conditions is true. Returns TRUE if any condition is true; returns FALSE only if all conditions are false.
   =OR(condition1, condition2, …)
   

   Example:
   =OR(A1>10, B1<5)
   Returns TRUE if A1>10 OR B1<5; otherwise, FALSE.

   

5. NOT()

   Reverses a logical value.
   =NOT(logical)
   

   Example:
   =NOT(A1>10)
   If A1 = 15, A1>10 is true, so returns FALSE.
   If A1 = 8, A1>10 is false, so returns TRUE.

   

6. IFERROR()

   Handles errors by returning a specified value if an error occurs.
   =IFERROR(value, value_if_error)
   

   Example:
   =IFERROR(A1/B1, "Error")
   If A1 = 10 and B1 = 2, returns 5.
   If A1 = 10 and B1 = 0, returns "Error" (instead of #DIV/0!).

   

7. IFNA()

   Handles #N/A errors specifically.
   =IFNA(value, value_if_na)
   

   Example:
   =IFNA(VLOOKUP(A1, B:C, 2, FALSE), "Not Found")
   If the VLOOKUP returns #N/A, it returns "Not Found"; otherwise, it returns the lookup result.

   

Text Functions

1. LEN()

   Returns the length of a text string.
   =LEN(text)
   

   Example:
   =LEN('IPLTS')
   Returns 5

   

2. LOWER(), UPPER(), PROPER()

   Change the case of text
   =LOWER(text)
   =UPPER(text)
   =PROPERR(text)

   Example:
   =LOWER('IPLTS')
   Returns iplts
   =UPPER('iplts')
   Returns IPLTS
   =PROPER('SCIENCE AND TECHNOLOGY')
   Returns Science and Technology

   

3. TRIM()

   Remove extra spaces from text, leaving only single spaces between words.
   =TRIM(text)
   

   Example:
   =TRIM('Sharing   Knowledge')
   Output: Sharing Knowledge

   

4. SUBSTITUTE()

   Replaces a specific text from string.
   =SUBSTITUTE(text, old_text, new_text, [instance_num])
   

   Example:
   =SUBSTITUTE('Information is sharable', 'Sharable', 'Powerful')
   Returns Informations is Powerful

   

5. REPLACE()

   Replaces a part of text from string based on position.
   =REPLACE(text, old_text, new_text, [instance_num])
   

   Example:
   =REPLACE("Hello", 2, 3, "xyz")
   Returns Hxyzlo

   

6. FIND()

   Finds text position (case sensitive)
   =FIND(find_text, within_text, [start_num])
   

   Example:
   =FIND("l", "Hello")
   Returns 3

   

7. SEARH()

   Finds text position (NOT case sensitive)
   =SEARCH(find_text, within_text, [start_num])
   

   Example:
   =SEARCH("L", "Hello")
   Returns 3

   

Date & Time Functions

1. TODAY()

   Returns today’s date
   =TODAY()
   

   Example:
   =TODAY()
   Returns 3/17/25

2. NOW()

   Returns current date & time
   =NOW()
   

   Example:
   =NOW()
   Returns 3/17/25 14:33

3. DATE()

   Creates a date
   DATE(year, month, day)
   

   Example:
   =DATE(2025,3,17)
   Returns 3/17/25

4. YAER(), MONTH(), DAY()

   Extracts the year, month, or day from a given date.
   =YEAR(date)
   

   Example:
   =YEAR(3/17/25)
   Returns 2025
   =MONTH(3/17/25)
   Returns 3
   =DAY(3/17/25)
   Returns 17
   

5. HOUR(), MINUTE(), SECOND()

   Extracts hour, minute, or second from a given time.
   =YEAR(date)
   

   Example:
   if A1 contains 2:30:45 PM
   =HOUR(A1)
   Returns 2
   =MINUTE(A1)
   Returns 30
   =SECOND(A1)
   Returns 45
   

6. DATEDIF()

   Calculates the difference between two dates in years, months, or days
   =DATEDIF(start_date, end_date, unit)
   

   Example:
   =DATEDIF(DATE(2022,1,1), TODAY(), "Y")
   Returns 3
   =DATEDIF(DATE(2022,1,1), TODAY(), "M")
   Returns 38
   =DATEDIF(DATE(2022,1,1), TODAY(), "D")
   Returns 1171
   

7. EOMONTH()

   Returns the last day of a month after adding a specified number of months.
   =EOMONTH(TODAY(), 1)
   

   Example:
   =EOMONTH(TODAY(), 1)
   Returns
   

8. WEEKDAY()

   Returns the day of the week as a number (1 = Sunday, 2 = Monday, etc.).
   =WEEKDAY(TODAY(), 1)
   

   Example:
   If A1 contains 17-Mar-2025: =WEEKDAY(A1)
   Returns 2
   

Statistical Functions

1. AVERAGE()

   Returns the average (arithmetic mean) of the values in a range.
   =AVERAGE(range)
   

   Example:
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 20, 25
   =AVERAGE(A1:A5)
   Output: 15
   

   

2. AVERAGEIF()

   Returns the average of cells that meet a specified condition.
   =AVERAGEIF(range, criteria, [average_range])
   

   Example:
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 20, 25 and you want to calculate the average of values greater than 10.
   =AVERAGE(A1:A5,">10")
   Output: 20
   

   

3. MEDIAN()

   Returns the middle value in a sorted list of numbers.
   =MEDIAN(range)
   

   Example:
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 20, 25
   =MEDIAN(A1:A5)
   Output: 20
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 20, 25,30
   =MEDIAN(A1:A6)
   Output: 17.5
   

   

4. MODE()

   Returns the most frequently occurring value in a range.
   =MODE(range)
   

   Example:
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 15, 25
   =MODE(A1:A5)
   Output: 15
   If no value repeats, it returns #N/A.

   

5. MIN(), MAX()

   MIN(): Returns the smallest or largest value in a range
   MAX(): Returns the largest or largest value in a range
   =MIN(range)
   = MAX(range)

   Example:
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 15, 25
   =MIN(A1:A5)
   Output: 5
   =MAX(A1:A5)
   Output: 25

   

6. COUNT(), COUNTNA()

   COUNT(): Counts the number of numeric values in a range.
   COUNTNA(): Counts the number of non-empty cells (both numbers and text)
   =COUNT(range)
   =COUNTNA(range)

   Example:
   If A1, A2, A3, A4, A5 contains 5, 10, 15, "IPLTS", ""
   =COUNT(A1:A5)
   Output: 3
   =COUNTNA(A1:A5)
   Output: 4 (Ignores blank)

   

7. COUNTIF(), COUNTIFS()

   COUNTIF(): Counts the number of cells that meet a single condition.
   COUNTIFS(): Counts the number of cells that meet multiple conditions.)
   =COUNTIF(range, criteria)
   =COUNTIFS(range1, criteria1, range2, criteria2, ...)

   Example:
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 10, 20,25
   =COUNTIF(A1:A5,10)
   Output: 3
   =COUNTIF(A1:A5, ">10")
   Output: 3

   If A1:A5 contains {5, 10, 15, 10, 20} and B1:B5 contains {A, B, A, B, A}:
   =COUNTIFS(A1:A5, ">10", B1:B5, "A")
   Output: 1 (because only 15 in column A meets the criteria of being greater than 10 and paired with "A" in column B)
   

   

8. RANK()

   Returns the rank of a number in a list.
   =RANK(number, ref, [order])
   order = 0 → Descending order
   order = 1 → Ascending order

   Example:
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 20,25
   =RANK(15,A1:A5, 0)
   Output: 3
   =RANK(15,A1:A5, 1)
   Output: 3

   

9. STDEV(),VAR()

   STDDEV(): Returns the sample standard deviation of a data set.
   VAR(): Returns the variance of a data set.
   =STDEV(range)
   =VAR(range)

   Example:
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 20,25
   =STDEV(A1:A5)
   Output: 7.9057
   If A1, A2, A3, A4, A5 contains 5, 10, 15, 20,25
   =VAR(A1:A5)
   Output: 62.5
   

   

   

Lookup Functions

1. VLOOKUP()

   The VLOOKUP function is used to search for a value in the first column of a table and return a corresponding value from another column.
   =VLOOKUP(lookup_value, table, col_index, [range_lookup])

   • lookup_value: What you’re looking for (e.g., a product ID)
   • table: The range of cells where the first column has the lookup value and other columns have data (e.g., A2:C10).
   • col_index: Which column number in the table to pull data from (1 = first column, 2 = second, etc.)
   • [range_lookup]: Optional. TRUE (or omitted) = approximate match; FALSE = exact match.

   Example:
   =VLOOKUP("Orange", A2:C4, 2, FALSE)
   

   

2. HLOOKUP()

   The HLOOKUP function is used to search for a value in the first row of a table and return a corresponding value from another row.
   =VLOOKUP(value, table, row_index, [range_lookup])

   • value: What you’re looking for (e.g., a product ID)
   • table: The range where the first row has the lookup value (e.g., A1:D3).
   • col_index: Which ROW number in the table to pull data from (1 = first row, 2 = second row, etc.)
   • [range_lookup]: Optional. TRUE (or omitted) = approximate match; FALSE = exact match.

   Example:
   =VLOOKUP("Orange", A2:C4, 2, FALSE)
   

   

3. XLOOKUP()

   A modern, flexible replacement for VLOOKUP and HLOOKUP. It searches for a value in one range and returns a corresponding value from another range, with more options.
   =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

   • lookup_value The value you want to search for.
   • lookup_array The range of cells where you search.
   • return_array The range of cells from which to return the value.
   • if_not_found (Optional) Value to return if not found.
   • match_mode – (Optional)
     0 → Exact match (default)
     -1 → Exact or next smaller
     1 → Exact or next larger
     2 → Wildcard match
   • search_mode – (Optional)
     1 → Search from first to last
     -1 → Search from last to first

   Example:1
   Let’s say you want to look up the Total Marks for a student using their Student ID. For example, you want to find the Total Marks for Student ID 103.
   =XLOOKUP(103, A2:A9, G2:G9, "Not Found")
   Output: 234

   

   Example:2
   Let’s reverse the lookup: you want to find the name of the student who scored a specific Total Marks, say 256.
   =XLOOKUP(256, G2:G9, B2:B9, "Not Found")
   Output: 234

   

4. INDEX()

   Returns the value of a cell at a specific row and column in a range.
   =INDEX(array, row_num, [column_num])

   Example:
   =INDEX(B2:B4, 2)
   Output:

   

5. MATCH()

   Returns the position of a value in a row or column.
   =MATCH(lookup_value, lookup_array, [match_type])

   Example:
   If A1:A5 contains {10, 20, 30, 40, 50}, =MATCH(30, A1:A5, 0)
   Output: 3 (Finds the position of 30)

   

6. CHOOSE()

   Returns a value from a list based on an index number.
   =CHOOSE(index_num, value1, value2, …)

   Example:
   =CHOOSE(3, 100, 200, 300, 400, 500)
   Output: 300

   

Filtering Functions

1. FILTER()

   Filters data based on a condition and returns matching results.
   =FILTER(array, include, [if_empty])

   Example:
   =FILTER(A2:B10, A2:A10>100, "None")
   Output: Returns rows from A2:B10 where values in A2:A10 are greater than 100; shows "None" if no matches.

   

2. SORT()

   Sorts a range dynamically in ascending or descending order.
   SORT(array, [sort_index], [sort_order], [by_column])

   Example 1:
   =SORT(A2:C10, 2, -1)
   Output: Sorts A2:C10 based on the second column in descending order.

   

   ---

   Example 2:
   =SORT(A2:C10, 2)
   Output: Sorts A2:C10 based on the second column in ascending order.

   

3. SORTBY()

   Sorts data based on values in another range or array.
   =SORTBY(array, by_array1, [sort_order1], …)

   Example:
   =SORTBY(A2:B10, C2:C10, -1)
   Output: Sorts A2:B10 based on values in C2:C10 in descending order.

   

4. UNIQUE()

   Extracts unique values from a range.
   =UNIQUE(array, [by_col], [exactly_once])

   Example 1:
   =UNIQUE(A2:A10)
   Output: Returns a list of unique values from A2:A10.
   =UNIQUE(A2:A10, FALSE, TRUE)
   Output: Returns only values that appear exactly once in A2:A10.

   

   Example 2:
   =UNIQUE(C2:C9, FALSE, TRUE)
   Output: Returns a list of unique values from A2:A10.
   

   

Arrray & Data Analysis Functions

1. SEQUENCE()

   Generates a sequence of numbers.
   =SEQUENCE(rows, [columns], [start], [step])

   Example 1:
   =SEQUENCE(4)
   Output: Returns 1, 2, 3, 4 in a vertical list.
   

   

   Example 2:
   =SEQUENCE(2, 3, 10, 2)
   Output: Returns a 2x3 grid starting at 10, increasing by 2:
   10, 12, 14
   16, 18, 20

   

2. RANDARRAY()

   Creates an array of random numbers.
   =RANDARRAY([rows], [columns], [min], [max], [whole_number])

   Example 1:
   =RANDARRAY(3, 2)
   Output: Returns a 3x2 array of random decimals between 0 and 1.
   

   

   Example 2:
   =RANDARRAY(2, 2, 1, 10, TRUE)
   Output: Returns a 2x2 array of random integers between 1 and 10, like:
   4, 7
   9, 2

   

3. TRANSPOSE()

   Converts rows to columns and vice versa
   =RANDARRAY([rows], [columns], [min], [max], [whole_number])

   Example:
   If A1:C2 contains:
   1, 2, 3
   4, 5, 6
   =TRANSPOSE(A1:C2)
   Output:
   1, 4
   2, 5
   3, 6

   

Chart Creation

Line Chart

Displays data points connected by lines, showing trends over a continuous interval

1. Select all columns except Sudent ID.
2. Go to the "Insert" tab. Click on Insert Line or Area Chart and select Line Chart.
3. Add a Chart Title (e.g., "Line Chart Example").
4. Label X-axis as "Students"
5. Label Y-axis as "Total Marks".
6. Add Markers to highlight points.

---

---

Bar Chart

Similar to a column chart but with horizontal bars.

1. Select the "Name" column.
2. Hold Ctrl and select the "Total Marks" column.
3. Go to the "Insert" tab. Click Insert Column or Bar Chart (it looks like vertical bars) and select Clustered Bar Chart (for horizontal bars) or Clustered Column Chart (for vertical bars).
4. Add a Chart Title (e.g., "Total Marks Comparison").
5. Label X-axis as "Total Marks".
6. Label Y-axis as "Students".
7. Change bar colors for better visibility.

Pie Chart

A circular chart divided into slices, showing proportions of a whole.

1. Select Data (For example, for "Ram")
2. Select the Marks in Math, Marks in Science, and Marks in English values for a single student.
3. Exclude the total marks.
4. Go to the "Insert" tab. Click on Pie Chart under the Charts section and select "2D Pie" or "3D Pie".
5. Add a Chart Title
6. Add Data Labels to show percentage values.
7. Change colors for better visibility.

Scatter Plot

Displays individual data points on X and Y axes without connecting lines.

1. Select the "Marks in Math" column.
2. Go to the "Insert" tab in Excel. Click on "Insert Scatter (X, Y) or Bubble Chart" and Choose "Scatter"
3. Add a Chart Title (e.g., "Marks in Math").
4. Label X-axis as "Student ID".
5. Label Y-axis as "Marks in Math".
6. Format the dots (change color, size, etc.)

Histogram

1. Select the "Total Marks" column since we want to analyze its distribution.
2. Go to the "Insert" tab in the Ribbon. Click on "Insert Statistic Chart" and select Histogram from the dropdown.
3. Click on the X-axis (horizontal axis).
4. Right-click and choose "Format Axis".
5. Under Axis Options, adjust:
   Bin Width (e.g., 20 or 30 for better grouping)
   Number of Bins (adjust as needed)
   
6. Add a Chart Title (e.g., "Distribution of Total Marks").
7. Format the bars (change color, add data labels).

A column chart showing the frequency distribution of data in bins (ranges).