Imagine you are planning for a trip with your friends. What would you do now? The first thing you would do is, you would create a budget plan for the trip and share it with your friends. For this you need to track all the expenses for the trip from food to lodging and entertainment. This is where excel comes in. You can create a spreadsheet, write all the details and calculate the cost for the trip easily using excel. In this blog we would understand what is excel and go through some of the excel formulas. Why late? Let's get started with the topic.
Excel is a great tool for anyone who works with data. Whether you are a student or business owner or data analyst, it helps you to store, organize and analyze data. We can handle huge amounts of data in excel. You can store data in thousands of rows and columns, manipulate it and gain insights. Using excel we can also sort, filter and group data making it easy to identify patterns and trends. We can even share the excel sheets with others setting access permissions like, only view the data, edit, etc.
Excel has a number of applications in various sectors. It is used by individuals, businesses, organizations of all sizes for data analysis. You can create graphs, charts, reports and tables very easily. Using excel you can also perform calculations, automate the tasks and create your own formulas to solve complicated problems. So learning some of the basic excel formulas can help you master data analysis using excel. In excel we use several formulas to perform operations easily and effectively. Users can use these formulas to automate calculations, minimize errors, and obtain valuable insights from the data.
Want to gain knowledge in Excel? Then visit here to learn Excel Training !
Now let's go through the basic excel formulas list:
Purpose: It is used to add values in the cells.
Formula: If you want to add 4 cells in a row then you can write =SUM(C3:F3). You can also use this formula as an excel addition formula.
How to use the SUM formula in excel?
Purpose: It is used to find the average of the values in the cells.
Formula: If you want to find the average of cells C3 to F3 then you can write =AVERAGE(C3:F3)
How to use the AVERAGE formula in excel?
Purpose: It is used to count the number of cells within the specified range of cells.
Formula: If you want to count the numbers in range F3 to F17 then you can write =COUNT(F3:F17)
How to use the COUNT function?
Purpose: It returns the maximum value from the specified range of cells.
Formula: If you want to find the maximum value in range F3 to F17 then you can write =MAX(F3:F17)
How to use the MAX function?
Purpose: It returns the minimum value from the specified range of cells.
Formula: If you want to find the minimum value in range F3 to F17 then you can write =MAX(F3:F17)
How to use the MIN function?
Purpose: It returns a difference of two specific cell values.
Formula: If you want to find the difference value of two cells D3 and E3 then you can write =D3-E3
How to use subtraction formula in Excel?
Purpose: It multiplies two or more numbers .
Formula: If you want to multiply the values in the cells C3 to E3 then you can write =PRODUCT(C3:E3)
How to multiply in Excel?
Purpose: It is used to divide one number by another.
Formula: If you want to divide one value C3 by another value D3 then you can write =C3/D3. You can also use the same formula as the percentage formula in excel.
How to use the division formula in Excel?
Get ahead in your career by learning Power BI Vs Excel !
Purpose: It will calculate the square root of a number .
Formula: If you want to find the square root of a number B3 then you can write =SQRT(B3)
How to calculate square root in Excel?
Purpose: It will return the current date.
Formula: If you want to write the current date then you can write =TODAY(). We need not select any cell.
How to use the TODAY function in Excel?
Purpose: It will return the current date and the time.
Formula: If you want to write the current date and time in a cell then you can write =NOW(). We need not select any cell.
How to use the NOW function in Excel?
Purpose: Excel date formula will return a date based on the year, month and day specified.
Formula: If you want to write the date with a specific date D3, month C3, year B3, then you can write =DATE(B3,C3,D3). It supports year, month and day format.
How to use the DATE function in Excel?
Purpose: It will return the year from a date specified.
Formula: If you want to write only the year from a specified date (in the cell B4), then you can write =YEAR(B4).
How to use the YEAR function in Excel?
Purpose: It will return the month from a date specified.
Formula: If you want to write only the month from a specified date (in the cell B4), then you can write =MONTH(B4).
How to use the MONTH function in Excel?
Purpose: It will return the day from a date specified.
Formula: If you want to write only the day of the month from a specified date (in the cell B5), then you can write =DAY(B5).
How to use the DAY function in Excel?
Purpose: It is used to test a logical condition. If the condition is true it returns one value and if the condition is false it returns another value.
Formula: IF(condition, “value_if_condition_is_true”, “value_if_condition_is_false”)
How to use the IF condition in Excel?
Purpose: It is used to return true if the arguments(conditions) specified are true or else it returns false. AND will accept up to 225 arguments.
Formula: AND(condition_1, condition2, …… )
How to use AND function in Excel?
Purpose: It is used to return true if any of the specified conditions is true or else it returns false. OR will accept up to 225 conditions.
Formula: OR(condition_1, condition2, …… )
How to use OR function in Excel?
Purpose: It will return the opposite value of the condition. If the condition is true, NOT will return false. If the condition is false, NOT will return true.
Formula: NOT(condition)
How to use the NOT function in Excel?
Become a Master in Excel by going through this HKR Excel Tutorial !
Purpose: Concatenate formula in excel will combine two or more text strings.
Formula: CONCATENATE(text1,text2,text3)
How to use the CONCATENATE function in Excel?
Purpose: LEFT function will return the specified number of characters from the beginning of the text.
Formula: LEFT(cell, number_of_chars_to_display)
How to use the LEFT function in Excel?
Purpose: RIGHT function will return the specified number of characters from the ending of the text.
Formula: RIGHT(cell, number_of_chars_to_display)
How to use the RIGHT function in Excel?
Purpose: MID function will return the specified number of characters from the middle of the text, starting from the specified position.
Formula: MID(cell, starting_number, number_of_chars_to_display)
How to use the MID function in Excel?
Purpose: LEN function will return the length of the text in number of characters.
Formula: LEN(select_the_cell_to_find_ length_of_text)
How to use the LEN function in Excel?
Purpose: UPPER function will convert all the characters in the text to uppercase.
Formula: UPPER(select_the_text)
How to use the UPPER function in Excel?
Purpose: LOWER function will convert all the characters in the text to Lowercase.
Formula: LOWER(select_the_text)
How to use the LOWER function in Excel?
Purpose: TRIM function will remove all the extra spaces from the text string.
Formula: TRIM(select_the_text)
How to use the TRIM function in Excel?
Purpose: PROPER function will capitalize the first character of each word and converts the remaining characters to lower case.
Formula: PROPER(select_the_text)
How to use the PROPER function in Excel?
Top 30 frequently asked Excel Interview Questions !
Purpose: SUMIF function will add all the values in a range of cells that satisfies a specific condition.
Formula: SUMIF(range, criteria, sum_range)
Here range is the range of the cell you want to evaluate, criteria is the condition you want to check and sum_range is the range of cells you wanted to add. If you skip the sum_range, the function will add the values in range.
How to use the SUMIF function in Excel?
Purpose: COUNTIF function in excel will count the number of cells in the range that satisfies a specific condition.
Formula: COUNTIF(range, criteria)
Here range is the range of the cell you want to evaluate and criteria is the condition you want to check.
How to use the COUNTIF formula in Excel?
Purpose: AVERAGEIF function will calculate the arithmetic mean of the values in the range of cells that satisfies a specific condition.
Formula: AVERAGEIF(range, criteria, average_range)
Here range is the range of the cell you want to evaluate, criteria is the condition you want to check and average_range is the range of cells you want to calculate the mean. If you skip the average_range, the function will calculate the mean of the values in range.
How to use the AVERAGEIF function in Excel?
Purpose: VLOOKUP function will search for a particular value in the first column of the table or range and returns a corresponding value from a particular column in the same row.
Formula: VLOOKUP(lookup_value, table array, column_index_number, range_lookup)
Purpose: HLOOKUP function will search for a particular value in the first row of the table or range and returns a corresponding value from a particular column in the same column.
Formula: HLOOKUP(lookup_value, table array, row_index_number, range_lookup)
Purpose: INDEX function will return the value of a cell in a specified row and column within a range or table
Formula: INDEX(cells_range, row_number, col_number)
Purpose: MATCH function will search for a particular value within a range of cells and will return a relative position of the value within the range
Formula: MATCH(lookup_value, cells_range, match_type)
Purpose: CHOOSE function will return a value from the values list according to the specified index number.
Formula: CHOOSE(index_num, value1, value2,....)
Purpose: INDIRECT function will return a value from the values list according to the specified text string that contains a cell reference.
Formula: INDIRECT(reference_text, A1)
Purpose: PMT function will calculate the periodic payment for a loan or investment according to a fixed interest rate, term and loan amount.
Formula: PMT(rate, nper, pv, [fv], [type])
Purpose: FV function will calculate the future value of the investment according to a fixed interest rate, term and initial investment amount.
Formula: FV(rate, nper, pmt, [pv], [type])
Purpose: NPV function will calculate the net present value of a series of cash flows according to a specified discount rate
Formula: NPV(rate, value1, value2, ….)
Purpose: IRR function will be the internal rate of return for a series of cash flows.
Formula: IRR(values, [guess])
In this article we have discussed the basic excel formulas and their purpose with examples. We hope you found this article informative. For more articles stay tuned.
Related Articles:
Batch starts on 2nd Jun 2023, Fast Track batch
Batch starts on 6th Jun 2023, Weekday batch
Batch starts on 10th Jun 2023, Weekend batch
.