Table of Contents
Microsoft Excel is the most preferred program for people who work in data-related careers like financial analysis, investment banking, data processing and financial modelling and presentation. It is very popular and one of the basics taught in computer classes. Microsoft Excel is often considered to be the best industry standard piece of software when it comes to data analysis. It is equipped to generate reports and business insights and hence is widely used in the industry. It even has built-in applications that make it more user-friendly. Excel is the best when it comes to doing calculations solving math and engineering problems etc. Therefore, learning basic Excel formulas and functions and mastering them is a very important step for people who aim to attain high proficiency in financial analysis.
Take your data science skills to the next level! Sign up for a free demo today!
What are Basic Excel Formulas and Functions?
Excel formulas are expressions which operate on values in a cell or over a range of cell addresses and operators. For example, the formula =A1+A2+A3 will give you the sum of values from cells A1, A2 and A3.
Predefined formulas in Excel are known as functions. Using them, we can skip the tedious process of manually entering formulas by giving them user-friendly names. For example, the same formula in the above example can be expressed as the function =SUM (A1: A3). Just like in the example above, this function sums all the values from cell A1 to cell A3.
There are many functions in excel that you can choose according to the operation you want to perform on the mentioned data set. Several basic Excel formulas and functions are listed by their categories below.
Attend our data science and machine learning course for free – schedule a demo today!
Common Functions
1: Which of the following algorithms is most suitable for classification tasks?
These are the most used among basic Excel formulas and functions.
| Function | Explanation | Group | Examples |
| SUM | Perform addition on all the values in the mentioned range of cells | Maths and Trigonometry | =SUM(E1:E8) |
| MIN | Finds the smallest value in the mentioned range of cells | Statistical | =MIN(E1:E8) |
| MAX | Finds the highest value in the mentioned range of cells | Statistical | =MAX(E1:E8) |
| AVERAGE | Computes the average of the value in the mentioned range of cells | Statistical | =AVERAGE(E1:E8) |
| COUNT | Counts and return the number of cells in the mentioned range of cells | Statistical | =COUNT(E1:E8) |
| LEN | Finds and Returns the number of characters in a string text on which the function is performed | Text | =LEN(B7) |
| SUMIF | Perform addition on all the values in the mentioned range of cells that meet specific criteria.
=SUMIF(range,criteria,[sum_range]) |
Maths and Trigonometry | =SUMIF(D1:D8,”>=1000″,C1:C8) |
| AVERAGEIF | Computes the average value in a range of cells that meet some specific criteria.
=AVERAGEIF(range,criteria,[average_range]) |
Statistical | =AVERAGEIF(F1:F8,”Yes”,E1:E8) |
| DAYS | Gives the number of days between two dates | Date and Time | =DAYS(D1,C1) |
| NOW | Gives the current system date and time | Date and Time | =NOW() |
Looking for a Data Science Career? Explore Here!
Numeric Function
These are the Excel functions that are used to operate on numeric data.
| Function | Explanation | Group | Example |
| ISNUMBER | Gives True if the mentioned value is numerical and False if it is not | Information | =ISNUMBER(A4) |
| RAND | Produces a random numeral between 0 and 1 | Maths and Trigonometry | =RAND() |
| ROUND | Rounds off a decimal value to the mentioned number of decimal points | Maths and Trigonometry | =ROUND(3.34543,2) |
| MEDIAN | Gives the number in the middle of the set of mentioned numbers | Statistical | =MEDIAN(1,4,5,2,4) |
| PI | Gives the value of Maths Function PI(π) | Maths and Trigonometry | =PI() |
| POWER | Gives the result of a number raised to a power. POWER( number, power ) | Maths and Trigonometry | =POWER(2,6) |
| MOD | Gives the remainder when you divide 2 numbers | Maths and Trigonometry | =MOD(10,2) |
| ROMAN | Transforms the mentioned number to roman numerals | Maths and Trigonometry | =ROMAN(19) |
Sign up to upgrade your data science skills!
String Functions
Basic Excel formulas and functions used to handle text data are called string functions. Some string functions are given below.
| Function | Explanation | Group | Example | Comment |
| LEFT | Gives a specific number of characters from the start (left-hand side) of mentioned string | Text | =LEFT(“CATS85”,4) | Left 4 Characters of “CATS85” |
| RIGHT | Gives a specific number of characters from the end (right-hand side) of mentioned string | Text | =RIGHT(“CATS85”,2) | Right 2 Characters of “CATS85” |
| MID | Recovers a number of characters from the middle of a string from a specific start position and length.
=MID (text, start_num, num_chars) |
Text | =MID(“CATS85”,2,3) | Recovering Characters 2 to 5 |
| ISTEXT | Gives True if the provided parameter is Text | Information | =ISTEXT(value) | Value – The value to check. |
| FIND | Gives the beginning position of a mentioned text string within another text string. The find function is case-sensitive.
=FIND(find_text, within_text, [start_num]) |
Text | =FIND(“oo”, “Roofing”,1) | Find ‘oo’ in “Roofing”, gives the result 2 |
| REPLACE | Substitutes part of a string with another specific string.
=REPLACE (old_text, start_num, num_chars, new_text) |
Text | =REPLACE(“Roofing”,2,2, “ss”) | Replace “oo” with “ss” |
VLOOKUP and HLOOKUP Functions
The purpose of the VLOOKUP function is to execute a vertical look-up in the leftmost column and then return a value in the same row from a column that you mention. The function HLOOKUP has the purpose of searching for a value in the top row of the table. It returns the value in the same column from a row you mention.
Study data science! Secure your dream career!
Date Time Functions
These are the basic excel function used for the manipulation of date values.
| Function | Explanation | Group | Example |
| DATE | Gives the number that denotes the date in the excel code | Date and Time | =DATE(2023,2,4) |
| DAYS | Find the number of days between the given two dates | Date and Time | =DAYS(D4,C4) |
| MONTH | Gives the month from a date value | Date and Time | =MONTH(“4/2/2023”) |
| MINUTE | Gives the minutes from a time value | Date and Time | =MINUTE(“12:31”) |
| YEAR | Gives the year from a date value | Date and Time | =YEAR(“04/02/2023”) |
10 basic Excel Formulas for Beginners
- SUM: Calculates the sum of a range of cells. Example: =SUM(A1:A10)
- AVERAGE: Calculates the average of a range of cells. Example: =AVERAGE(A1:A10)
- MAX: Returns the maximum value in a range of cells. Example: =MAX(A1:A10)
- MIN: Returns the minimum value in a range of cells. Example: =MIN(A1:A10)
- COUNT: Counts the number of cells in a range that contain a value. Example: =COUNT(A1:A10)
- IF: Returns one value if a condition is true and another value if it is false. Example: =IF(A1>10, “Greater than 10”, “Less than or equal to 10”)
- VLOOKUP: Searches for a value in the first column of a table and returns a corresponding value in the same row from another column. Example: =VLOOKUP(A1, B1:C10, 2, FALSE)
- CONCATENATE: Combines two or more text strings into one. Example: =CONCATENATE(“John”, ” “, “Doe”)
- LEFT: Returns a specified number of characters from the beginning of a text string. Example: =LEFT(A1, 5)
- RIGHT: Returns a specified number of characters from the end of a text string. Example: =RIGHT(A1, 5)
Steps to Avoid Mistakes while using Basic Excel Formulas and Functions
- Make sure the formula you entered is accurate according to BODMAS.
- Make sure the cell address is correct by pressing F2 and then crosschecking the cell address.
- The formula should always start with an equal sign. If not, you will get an error message.
- Make sure to switch the cell format to general before entering the function. Formulas will not work if text format is chosen.
- Space is counted as a character and hence you have to realise that a cell containing space is not the same as a blank cell.
- Text value should be provided inside double quotes [inverted comma] if we enter text value instead of the cell address.
- The numbers should not be enclosed in double-quotes.
- All opening and closing parenthesis should match.
- Copy the formula instead of retyping it.
Excel is one of the best spreadsheet applications. In this article, we have discussed the basic Excel formulas and functions with examples. It will be more productive to use the above-listed functions instead of writing formulas. Functions also have high accuracy as the chances of making mistakes are low.
Basic Excel Formulas and Functions FAQs
1. Which is the Excel function that performs addition?
2. What does the LEN function do?
3. Which function key is used for cross-checking cell addresses?
