Learn Python, Microsoft 365 and Google Workspace
| Connect with me: Youtube | WhatsApp Channel | Web |
For example, if you enter the formula =A1+B1 into cell C1, and then copy that formula to cell D2, the formula will automatically change to =A2+B2. This is because Excel assumes that you want to add the corresponding cells in the new location.
For example, if you enter the formula =$A$1+$B$1 into cell C1, and then copy that formula to cell D2, the formula will remain the same. This is because Excel knows that you want to add the specific cells A1 and B1, regardless of where the formula is copied.
arguments in a particular order or structure
Functions …. The normal order for a function is:-
Function Name,parenthesis (Arguments for the function separated by commas and closing parenthesis ).
For example:
=SUM(A2:A10) Adds the values in cells A2:10.
=SUM(A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10.
Tip: You can also type “ALT + =” into a cell, and Excel automatically inserts the SUM function. (windows)
Use AutoSum to sum numbers:
Tips: Click here to learn Copy values quickly from the status bar
See also:
use the SUMIF function to sum the values in a range that meet criteria that you specify.
For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5. You can use the following formula: =SUMIF(B2:B25,”>5”)
Syntax
SUMIF(range, criteria, [sum_range])
See also:
Returns the largest value in a set of values.
Syntax
MAX(number1, [number2], ...)
The MAX function syntax has the following arguments: Number1, number2, … Number1 is required, subsequent numbers are optional. 1 to 255 numbers for which you want to find the maximum value.
Returns the smallest number in a set of values.
Syntax
MIN(number1, [number2], ...)
The MIN function syntax has the following arguments: Number1, number2, … Number1 is optional, subsequent numbers are optional. 1 to 255 numbers for which you want to find the minimum value.
further reading:
For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5.
further reading:
The COUNTA function counts the number of cells that are not empty in a range.
See Also
Use COUNTIF, to count the number of cells that meet a criterion; for example, to count the number of times a particular city appears in a customer list.
Syntax
COUNTIF(range, criteria)
For example:
=COUNTIF(A2:A5,"London")
=COUNTIF(A2:A5,A4)
see also:
Returns the result of a number raised to a power.
Syntax
POWER(number, power)
The POWER function syntax has the following arguments:
Number Required. The base number. It can be any real number. Power Required. The exponent to which the base number is raised.
=POWER(5,2)
further reading:
For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to =A1 * A2 * A3 * C1 * C2 * C3.
=Product(5,2)
=Product(C6:E6)
further reading:
For example, if the range A1:A20 contains numbers, the formula =AVERAGE(A1:A20) returns the average of those numbers.
See also:
Syntax:
Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it’s false. [^1]
IF(logical_test, value_if_true, [value_if_false])
For example:
=IF(A2>B2,"Over Budget","OK")
=IF(A2=B2,B4-A4,"")
Formula that uses the IF function
logical_test (required): The condition you want to test.
value_if_true (required): The value that you want returned if the result of logical_test is TRUE.
value_if_false (optional): The value that you want returned if the result of logical_test is FALSE.
For example, =IF(C2=”Yes”,1,2) says IF(C2 = Yes, then return a 1, otherwise return a 2).
Example:
=IF(A1>10,"Greater than 10","Less than or equal to 10")
This formula checks if the value in cell A1 is greater than 10. If it is, the formula returns the text “Greater than 10”. If it’s not, the formula returns “Less than or equal to 10”.
Example:
=IF(B1="Yes","Paid","Not Paid")
This formula checks if the value in cell B1 is “Yes”. If it is, the formula returns “Paid”. If it’s not, the formula returns “Not Paid”.
Example:
=IF(C1>=100,"Pass","Fail")
This formula checks if the value in cell C1 is greater than or equal to 100. If it is, the formula returns “Pass”. If it’s not, the formula returns “Fail”.
Example:
the following formula will return “Over Budget” if the value in cell A2 is greater than the value in cell B2, otherwise it will return “OK”:
=IF(A2>B2,"Over Budget","OK")
Example:
the following formula will calculate the difference between the values in cell A2 and cell B2 if the value in cell A2 is greater than the value in cell B2, otherwise it will return 0:
=IF(A2>B2,A2-B2,0)
Here are some more examples of how to use the IF function:
Check if a cell is empty:
=IF(A2="","Empty","Not empty")
Check if a cell contains a specific value:
=IF(A2="Apple","Apple found","Apple not found")
Check if a date is before or after a certain date:
=IF(A2<B2,"Date is before", "Date is after")
Calculate a discount based on the amount of a purchase:
=IF(A2>100,A2*0.1,A2*0.05)
The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.
See also:
The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook.
For example, if you know that someone was born in 1963, you might use the following formula to find that person’s age as of this year’s birthday:
= YEAR(TODAY())-1963
further reading:
To extract the day and month from a date in Excel, you can use the following functions:
Day Function: This function extracts the day of the month from a date.
=DAY(date)
Replace date with the cell reference that contains the date (e.g., A1).
Month Function: This function extracts the month from a date.
=MONTH(date)
Similarly, replace date with the cell reference.
For example, if cell A1 contains the date 2025-02-06, you can use the formulas as follows:
=DAY(A1) will return 6.=MONTH(A1) will return 2.LOWER function: Converts all uppercase letters in a text string to lowercase.
Syntax:
LOWER(text)
The LOWER function syntax has the following arguments:
Text Required. The text you want to convert to lowercase. LOWER does not change characters in text that are not letters.
UPPER function: Converts text to uppercase.
Syntax:
UPPER(text)
The UPPER function syntax has the following arguments:
Text Required. The text you want converted to uppercase. Text can be a reference or text string.
PROPER function:
See also:
LEN returns the number of characters in a text string.
Syntax:
LEN(text)
LENB(text)
Text Required. The text whose length you want to find. Spaces count as characters.
See also:
Syntax:
CONCAT(text1, [text2],…)
For example, =CONCAT(“The”,” “,”sun”,” “,”will”,” “,”come”,” “,”up”,” “,”tomorrow.”) will return The sun will come up tomorrow.
Video Tutorial: Join First Name and Last Name in the Excel - CONCAT function
See also:
”# Ref” An invalid cell is referenced in the formula
Note: You can enter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value.
Video Tutorial: Append data from multiple sheets into one sheet - Microsoft Excel
| [Video Tutorial: Excel Mobile | TOP 25 Tips to use Excel Mobile App - Microsoft 365](https://youtu.be/y9m36XLI4v4?si=iRfz-u-Np3SdgE_J) |
Defined Names
What is the symbol for a cell reference in Excel?
(a) @ (b) $ (c) & (d) %
What is a function?
(a) A predefined formula that performs a specific calculation (b) A rule that tells Excel how to calculate something (c) A cell in a spreadsheet (d) The address of a cell
What is the purpose of a function?
(a) To perform a calculation (b) To create a chart (c) To filter data (d) None of the above
What is a range of cells?
(a) A group of adjacent cells (b) A single cell (c) A formula (d) The address of a cell
Beginner:
The IF function is used to perform conditional logic in Excel. It can be used to test a condition and return a different value depending on the outcome of the test.
Answer: A logical test is a condition that is evaluated to TRUE or FALSE. A comparison operator is a symbol that is used to compare two values.
=IF(A1>B1,"A1 is greater than B1","A1 is less than or equal to B1")
What is conditional formatting, and how can you use it to highlight specific data in Excel? (Windows only)
In Excel, write the formulas to calculate the following for the range of cells A1:A10, which contains the values {10, 20, 30, 40, 50, 60, 70, 80, 90, 100}:**
Excercise:
Exercise 1:
=SUM(A1:A10)The result, 550, should appear in cell A11.
Exercise 2:
=AVERAGE(A1:A10)The result, 55, should appear in cell A11.
Exercise 3:
=MAX(A1:A10)The result, 100, should appear in cell A11.
Exercise 4:
=MIN(A1:A10)The result, 10, should appear in cell A11.
Exercise 5:
=IF(A1>A2,"A1 is greater than A2","A1 is not greater than A2")The result, “A1 is not greater than A2”, should appear in cell A11.
Exercise 6: Power and Square Root
=A1^2.=SQRT(A1).SUM FUNCTIONS (SUM, SUMIF):
Exercise : Calculate the total marks for each students.
Sample Date:
| Student | Math | Science | History |
|---|---|---|---|
| Alice | 85 | 90 | 75 |
| Bob | 70 | 88 | 80 |
| Carol | 92 | 76 | 85 |
| David | 78 | 82 | 70 |
| Emma | 90 | 85 | 92 |
Exercise: Use the SUM function to calculate the total sales in the following table:
| Month | Sales |
|---|---|
| January | 100 |
| February | 120 |
| March | 150 |
| April | 180 |
| May | 210 |
Exercise: Use the SUMIF function to calculate the total sales for all products sold in the month of January.
Sample Date:
| Month | Product Category | Sales |
|---|---|---|
| January | Food | 10 |
| January | Clothing | 20 |
| January | Electronics | 30 |
| February | Food | 20 |
| February | Clothing | 30 |
| February | Electronics | 40 |
Exercise: Use the SUMIF function to calculate the total sales of Food in the following table:
| Month | Product Category | Sales |
|---|---|---|
| January | Food | 10 |
| January | Clothing | 20 |
| January | Electronics | 30 |
| February | Food | 20 |
| February | Clothing | 30 |
| February | Electronics | 40 |
Exercise: In the following table, calculate the total sales for all products sold on March 8th:
| Date | Product | Sales |
|---|---|---|
| March 7th | Product A | 100 |
| March 7th | Product B | 200 |
| March 8th | Product C | 300 |
| March 8th | Product D | 400 |
Exercise : In the following table, calculate the total sales for all products sold to customers in the Pakistan:
| Customer | Country | Sales |
|---|---|---|
| Customer A | Pakistan | 100 |
| Customer B | Canada | 200 |
| Customer C | United Kingdom | 300 |
| Customer D | Pakistan | 400 |
Exercise: Use the SUM function to calculate the total bonus for employees who have achieved a score of 90 or higher in a test.
| Employee | Test Score | Bonus |
|---|---|---|
| Ali | 85 | 100 |
| Ahmad | 92 | 150 |
| Nasir | 88 | 120 |
| Hamza | 95 | 180 |
| Muhammad | 90 | 160 |
Exercise : Use the SUMIF function to calculate the total sales for products whose names contain the word “Phone.”
| Product | Sales |
|---|---|
| SmartPhone 2020 | 1200 |
| PhoneCase | 1500 |
| Tablet | 1800 |
| Basic Phone | 1400 |
| iPhone X | 1600 |
Exercise : Calculate the total expenses for the “Food” category using the SUMIF function.
| Expense | Category | Amount |
|---|---|---|
| Rent | Housing | 1000 |
| Groceries | Food | 350 |
| Gas | Transport | 75 |
| Dining | Food | 200 |
| Internet | Utilities | 60 |
Exercise : Calculate the total sales for Product A and Product C using the SUMIF function with multiple criteria. Use the following data:
Sample Data:
| Product | Region | Sales |
|---|---|---|
| Product A | North | 250 |
| Product B | South | 300 |
| Product C | North | 150 |
| Product D | South | 200 |
| Product A | South | 300 |
| Product C | South | 125 |
Solution:
=SUMIF(A2:A7, "Product A", C2:C7) + SUMIF(A2:A7, "Product C", C2:C7)
COUNT FUNCTIONS (COUNT, COUNTIF):
Exercise : Counting Numbers in a Range
Exercise : Basic COUNT Function
=COUNT(A1:A5).Exercise : Counting Blank Cells
Exercise : Counting with Logical Operators
=COUNTIF(A1:A5, ">=90").Exercise : Use the COUNTIF function to count the number of “Apples” in the list:
| Column A |
|---|
| Bananas |
| Apples |
| Oranges |
| Apples |
| Grapes |
| Strawberries |
| Apples |
Exercise : In the following table, count the number of cells in column B that contain the value “100”.
| Column A | Column B |
|---|---|
| Product A | 100 |
| Product B | 200 |
| Product C | 300 |
| Product D | 100 |
Exercise : In the following table, count the number of cells in column B that contain a value that contains the letter “a”:
| Column A | Column B |
|---|---|
| Product A | Apple |
| Product B | Banana |
| Product C | Carrot |
| Product D | Orange |
Exercise : In the following table, count the number of cells in column B that contain a value that starts with the letter “a”:
| Column A | Column B |
|---|---|
| Product A | Apple |
| Product B | Banana |
| Product C | Carrot |
| Product D | Orange |
Exercise : In the following table, count the number of cells in column B that contain a value that ends with the letter “e”:
| Column A | Column B |
|---|---|
| Product A | Apple |
| Product B | Banana |
| Product C | Carrot |
| Product D | Orange |
STATISTICAL FUNCTIONS (AVERAGE, AVERAGEIF, MIN, MAX):
Exercise : Find the highest value in the following range of cells:
range: A1:A5
values:
10
20
30
40
50
Exercise : Find the highest value in the following range of cells, ignoring empty cells and text values:
A1:A5
10
Apple
Bananas
30
50
Exercise 1: Calculate the average of the following numbers:
1, 2, 3, 4, 5
Exercise : Calculate the average of the numbers in the following range:
A1:A10
Exercise : Find the minimum value in the following range of cells:
A1:A10 = {10, 20, 30, 40, 50, 60, 70, 80, 90, 100}
Excercise : Find the minimum value in the following range, but only include the values that are in the column B:
| A | B |
|---|---|
| 1 | 2 |
| 3 | 4 |
| 5 | 6 |
| 7 | 8 |
| 9 | 10 |
Exercise : Calculate the average of the numbers in the following range, excluding the first and last numbers:
A1:A10
Exercise 2: Calculate the average per month sales of the year, using the following data:
| A | B |
|---|---|
| Month | Sales |
| January | 1000 |
| February | 1200 |
| March | 1100 |
| April | 1300 |
| May | 1400 |
| June | 1500 |
| July | 1600 |
| August | 1700 |
| September | 1800 |
| October | 1900 |
| November | 2000 |
| December | 2100 |
Exercise 3: Calculate the average of the sales figures for all customers who have spent more than $1000, using the following data:
| Customer | Sales |
|---|---|
| Alice | 1000 |
| Bob | 1200 |
| Carol | 1100 |
| Dave | 1300 |
| Eve | 1400 |
| Frank | 1500 |
| George | 1600 |
| Henry | 1700 |
| Ian | 1800 |
| James | 1900 |
| Kate | 2000 |
| Lily | 2100 |
Exercise : Calculate the average of the positive numbers in the following range:
A1:A10
Exercise: Rounding
Exercise: Random Numbers
In cell A1, enter the formula =RAND(). This will generate a random number between 0 and 1. Drag the fill handle (the small square at the bottom-right corner of the cell) down to generate multiple random numbers.
LOGICIAL FUNCTIONS (IF, True/False, AND/OR):
TEXT MANIPULATION FUNCTIONS (CONCATE, TEXTJOIN, UPPER, POWER, PROPER):
Exercise : Lowercase Conversion
Exercise : Proper Case Conversion
Exercise : Change the following text to uppercase.
This is a sample text.
Exercise: Change the following text to lowercase.
THIS IS A SAMPLE TEXT.
Exercise : Change the following text to title case.
this is a sample text.
Exercise : Create a new column called “Title Case” and use the PROPER function to convert the text in Column A to title case.
Sample Data:
| Good Habits |
|---|
| regular exercise |
| healthy eating |
| adequate sleep |
| time management |
| mindfulness and meditation |
Exercise:
=CONCAT(A1, " ", B1, " is ", C1, " years old.")
Sample Data:
| Column A | Column B | Column C |
|---|---|---|
| Muhammad | Ali | 20 |
Exercise: Use the CONCAT function to combine the values in Column A and Column B into a single column, Col3, with a hyphen (-) in between.
Sample Data:
| Column A | Column B |
|---|---|
| Muhammad | Ali |
| Muhammad | Hamza |
| Nasir | Ahmad |
Output:
| Column A | Column B | Column C |
|---|---|---|
| Muhammad | Ali | Muhammad Ali |
| Muhammad | Hamza | Muhammad Hamza |
| Nasir | Ahmad | Nasir Ahmad |
Exercise: Write a formula using the CONCAT function to create a new column that contains the product name, quantity, and price, separated by a comma and a space, in the following format:
Product name, quantity, price
Sample Data:
| product Name | quantity | price |
|---|---|---|
| Apple | 100 | 5 |
| Banana | 200 | 4 |
| Orange | 300 | 3 |
DATE FUNCTIONS (TODAY, NOW, DATE):
Exercise: Calculate Age
=(TODAY() - A1) / 365.25
The result in cell B1 will be the age in years.
Exercise : Calculate Days Remaining
=A1 - TODAY()
The result in cell B1 will be the number of days remaining until the future date.
Exercise: Write the formula to find the date of the first day of the previous month.
=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)
This formula calculates the date of the first day of the previous month. The DATE() function takes three arguments: the year, the month, and the day.
In this case, we are using the YEAR() and MONTH() functions to get the current year and month, respectively. We then subtract 1 from the month to get the month of the previous month. Finally, we set the day to 1 to get the date of the first day of the previous month.
Exercise: Calculate Elapsed Time
You can use the NOW function to calculate the elapsed time between two events.
=TEXT(NOW()-B5, "hh:mm")
This formula subtracts the start time in cell B5 from the current time provided by NOW and formats the result as “hh:mm”.
What is the cell reference of the cell located at the intersection of column A and row 9?
What is the cell reference of the range of cells A1 to A10?
What is the cell reference of the cell located two rows below and one column to the right of cell B3?
Which of the following is a valid cell reference in Excel?
What does the $ symbol do when used in a cell reference in Excel?
What is the difference between a relative cell reference and an absolute cell reference?
Which of the following is a valid mixed cell reference in Excel?
What is the cell reference of the cell two rows down and one column to the right of cell A1?
What is the cell reference for the range of cells from A1 to B10?
Which of the following is a valid formula?
Which of the following formulas will return the value 10?
What is the function for finding the maximum value in a range of cells?
What is the function for counting the number of cells in a range that contain a specific value?
What is the function for rounding a number to a specified number of decimal places?
The COUNTIF function is used to:
What is the function for calculating the average of a range of cells?
What is the function for calculating the minimum value in a range of cells?
What is the function for counts the number of cells that are not empty in a range.
What is the function for concatenating two or more text strings into a single text string?
What is the syntax of the IF function?
What is the logical test in the IF function?
What is the value_if_true in the IF function?
What is the value_if_false in the IF function?
Which of the following is an example of an IF function?
Which of the following is the correct syntax for the SUMIF function?
What is the purpose of the SUMIF function?
Which of the following formulas will return the sum of all cells in the range A1:A100 that are greater than $100?
What is the purpose of the criteria argument in the SUMIF function?
What does the COUNTIF function return if no cells meet the specified criteria?
[^1] IF function - Excel Help & Training [^2] Excel Exercises [^3] COUNTIF function - Microsoft Support