Microsoft Office includes MS-EXCEL. Its a spreadsheet program that helps us organize data, calculates the data in a very fast and efficient manner and helps us show the data graphically. Excel contains multiple rows and columns. Each sheet is made up of 1048576 rows and 16383 columns.
It assists you in analyzing all of the facts in your spreadsheet so that you may make more informed business judgments. Excel may assist you by recommending and then constructing PivotTables, which are an excellent method to summarise, analyze, explore, and present data.
In Microsoft Excel, a formula is a mathematical expression that is applied on values contained in a range of cells. These formulas produce a result even if there is a mistake. Excel formulae can do addition, subtraction, multiplication, division, and a range of other calculations.
Write the formula in front of fx. Have a look at the image. We need to write the formula at the place where the arrow is pointing.
As the name implies, the SUM() method returns the sum of the all the values present in given range of cells. It performs addition just like it is done in mathematics i.e. adding all the values present in the selected range. As an example, consider the following:
When we typed “=SUM(C2:C4)” we were able to add all the values present in the cell of column C containing values of sales i.e. 180 + 320 + 1625 = 2125. The outcome was saved in C5.
The AVERAGE() function computes the average of cell values within a certain range. Simply put “AVERAGE(C2:C4)” to get the average total sales, as illustrated in the example below.
COUNT() returns the total number of cells in a number-filled range. It does not count any cell that is not containing numeric data.
COUNT function starts counting from cell C1 and ends its search at C4. During its counting, it includes only cells containing numerical values. It is giving output 2 because it is omitting cells C1 and C4 as C1 is having text values and cell C4 is blank.
To count cells containing numerical values, text values or any other values then use COUNTA().
The SUBTOTAL() function returns subtotal of a table. You can choose between min, max, sum, count, average, and others, depending on your needs. Let’s look at two examples of this.
In the above example, we have selected cells starting from A2 and ending at A4. The function used is “=SUBTOTAL(1, A2: A4)”. “1” is used to find average in the subtotal list. As a result, the aforementioned code will return the average of A2: A4, with the response 20 being put in C5.
MOD() function returns the remainder of a given number divided by a divisor. To understand this MOD() function better let’s look at an example
We divided all the numbers from cell A2 to A4 by 5 in this case. The remainder is calculated with the “=MOD(A2:A4,5)” function. The outcome is saved in column B.
The POWER() function is used to find the power of a given number. We need two parameters for this function first is the range of cells on which function is to be applied and the numerical value which is power raised. To understand better below is the implementation.
In the above example, we have typed POWER(A2:A4, 3). Here Power function is returning storing power of cells between A2 to A4 in column B i.e. B2 to B4.
The CEILING() function rounds a value to the next significant integer multiple.
As we can see in the above example we have provided two parameters to the function CEILING function, First is the range of cells to be selected i.e. A2 to A4 and another is numerical values from which all the numbers in the cell will be divided.
This function returns nearest integer multiple.
As we can see in the above example we have provided two parameters to the function FLOOR function, First select range of cells on which function should be applied i.e. A2 to A4 and another is numerical values from which all the numbers in the cell will be divided.
Count of all the characters in a string is returned by LEFT() function. Counting starts from the left side of the string. Meanwhile, the MID() method, given a starting location and length, retrieves the characters that starts from the middle of a text string. The RIGHT() function returns the length of a text string in characters.
Let’s look at a few instances to better comprehend these functions.
The function left is used in the example below to get the leftmost word in the statement in cell A1.
An example of how to use the mid function can be seen here.
This is an illustration of the right function.
The UPPER() method raises the case of any text string. The LOWER() function, on the other hand, lowercases the given string consisting of text characters. The PROPER() function changes the text into proper form, which means that the first letter in each word is capital and the rest is lowercase.
Let’s look at a few examples to help you understand:
The text in A1 has been transformed to a full uppercase text in B1.
The text in A1 has now been transformed to a complete lowercase version, as seen in B1.
Finally, the improper text in A1 has been transformed into a clean and accurate format in B1.
In Excel, the NOW() function displays the current machine time and date.
The output of the NOW() function differs depending on the date and time on your system.
In Excel, the TODAY() function returns the system date.
The role The month’s day is returned using DAY(). A number between 1 and 31 will be chosen. The month begins on January 1 and ends on December 31.
The MONTH() method returns the month as a number between one and twelve, with one being January and twelve being December.
YEAR() returns the year from a date value, as its name suggests.
The TIME() function turns integers such as hours, minutes, and seconds into a time format in Excel.
Total number of characters present in a string is returned by LEN() function. It special characters, text, numbers and also includes spaces. Implementation is shown below.
This function connects or merges multiple text strings into a single string. The method for performing this function is listed below.
Excel is a fantastic spreadsheet program for the analysis of data and forming a great report. You have learned the basic Excel formulas and functions which will help you in performing your task efficiently after reading this article. We talked about Data-time, text, Numeric, and advanced Excel formulas and functions in this article. Excel knowledge helps you shape many vocations. With the help of excel, you can become a great data analyst as an effective Excel spreadsheet will organize raw data into a digestible format, making observations easier to extract. When working with more sophisticated data, Excel allows you to alter fields and functions that conduct calculations for you. To learn all the concepts above in a more detailed and efficient manner SkilloVilla is here to help you out. Here you can learn the concepts from experts and will be solving real-life problems under the guidance of experts. Learn in real-time lectures, solve real-world case studies, and be mentored by top 1% professionals. Be the talent that every