Formulas For Excel Spreadsheets – With this mega-guide of Excel formulas you can take a great part of your potential from Microsoft Excel. Getting Excel as a powerful tool is much simpler than you think.
Stop thinking about Excel as a calculator and start taking advantage of all of the Excel 2007 formulas or the Excel 2010 formulas (which are practically the same as in the later versions of Excel 2013 formulas and Excel 2016 formulas). Surely you may also be interested in our complete guide to dynamic tables or concatenate .
1 Excel Formulas – Practical examples
1.1 How to make a formula in Excel
1.2 Basic Excel Formulas
1.3 Excel Formulas with Logical Operators
1.4 Mathematical Excel Formulas
1.5 Text Formulas
1.6 Date and time formulas
1.7 Excel Logical Functions
1.8 Reference formulas
1.9 Statistic Excel Formulas
2 Excel main formulas
3 Excel Cheats
3.1 Excel Cheats: The +
3.2 Excel Cheats: The &
3.3 Excel tricks: formula SIFECHA
4 Create your own Excel formulas
4.1 Other users have read …
EXCEL FORMULAS – PRACTICAL EXAMPLES
HOW TO MAKE A FORMULA IN EXCEL
To make a formula in Excel we must select a cell where we want to include the formula and we will start by typing the equal symbol (=) that in the keyboard is normally located where 0. To include the same symbol we must click on the Shift key and then on The 0 as you can see in the following image:
Once we have started our formula in Excel with the symbol = we will select the cell or cells with which we want to operate.
BASIC EXCEL FORMULAS
In Excel there are certain mathematical operations that do not require complex formulas or any additional functions that we have to learn from memory. This is what is known as basic formulas and are based on the simplest arithmetic operations learned in school: addition, subtraction, multiplication and division.
Excel Formula Excel
The sum formula in Excel is the simplest there can be. It is used to add numbers of all kinds, either integers or decimals. To use this formula we need to use the + symbol. For example, we can add two numbers as in the following image.
In addition, Excel is kind enough to color the cells that you are using in different colors so that it is much more intuitive and you can avoid errors in a visual way getting very well mark the cells used in our Excel formulas.
But you can not only do the sum of two numbers, but you can enter the + symbol several times in the Excel formulas so you can do sums of more sums (worth the redundancy) as you can see in the following image.
Resta Excel Formula
As in the previous case, we can also do subtasks in Excel and do not necessarily have to be only two numbers, but also can be as many as you want as in the following example I show you Excel formulas.
In addition, we can combine both the addition and subtraction Excel formulas and even use the parentheses to create our much more complex Excel formulas.
Excel formulas: Multiplication and division
On the other hand, to make multiplications and divisions we can use simple Excel formulas.
To do a multiplication we will use the mathematical operator whose symbol is the asterisk (*) and for the division the slash (/).
In the following image we can see the Excel formula of multiplication and the formula Excel of division.
A practical example: Formula in Excel to calculate VAT
For example, the formula in Excel to calculate VAT is much simpler than one thinks and can be done with the formulas of Excel that we have explained above.
Basically, to calculate VAT with Excel what we need to know is the% VAT that is applied for the product or service (in Spain it is common to use 21% for most products) and the price of the product we want to add The VAT.
That is, the formula would be:
Price with VAT = Product price x (1 + VAT)
Suppose we have a product whose value is 50 € and that VAT is 21%. This formula in Excel would be:
But if we had the VAT saved in another cell with a percentage format we would have the following Excel formula.
Where as you can see, we have replaced the fraction 21/100 by the cell in which is the percentage value of the VAT.
EXCEL FORMULAS WITH LOGICAL OPERATORS
We can also create Excel formulas with logical operators, but what are logical operators? The formulas in Excel with logical operators are those that bear the symbols:
- Greater than (>)
- Less than (<)
- Other than (<>)
- Same as (=)
Although all this may seem like abbreviations for writing smiley faces in Excel are not. Knowing how it works in Excel formulas is fundamental. These operators use us to compare values or texts … in short, the content of two cells. Its result is very simple. If the statement is true then the cell will display the TRUE value and if it is not met, the Excel formula will return FALSE.
In the following image you can see some examples of these logical operators and their result.
The result of this statement is false because 10 is not greater than 20.
In addition, we can also use the combination of two logical operators in Excel formulas. For example, we can use> = or <= as shown in the following image.
In this case, the result of the Excel formula will be TRUE because 10 is equal to 10.
EXCEL MATH FORMULAS
Microsoft Excel has many formulas that we can use to do mathematical calculations. Many of these formulas are very advanced or are useful for making trigonometric calculations such as SEN, COS and TAN that return the sine, cosine, or tangent of an angle. But I want to pause in explaining the main Excel math formulas that are used more often in a less technical Excel.
- RANDOM: returns a random number between 0 and 1
- RANDOM.ENTER: Returns a random number between two numbers previously indicated. For example: = RANDOM.ENTRE (10; 20) will return a random value greater than or equal to 10 and less than or equal to 20.
- PAR: checks if a number is even, if it is, it returns the value TRUE, if not FALSE.
- ODD: checks if an integer is odd. It also returns TRUE or FALSE.
- SUM : adds a range of cells without using the + mathematical operator.
- SUMIF.SI: This Excel formula returns the sum of a set of cells that satisfy a condition.
- JOIN SUM : Sets the sum of a set of cells that satisfy several conditions.
- SUMAPRODUCT: this function returns the result of multiplying two equal cell ranges one by one.
- MMULT: This Excel formula gives us the result of multiplying one or more arrays.
There are many Excel formulas that are used to work with texts and although in principle Excel is intended as a data processor and one may think that the data are just numbers there is also a lot of text type data. Think of a database in which we have Name, Surnames, Address, Sex … I explain? We are not always going to deal with numbers in Excel, we also need Excel formulas to be able to process texts.
The main Excel formulas for processing texts are:
- CONCATENATE: it serves to join different texts that are in a cell in a single cell.
- RIGHT: it serves to obtain, from a cell with a string of text, the number of characters that we want starting from the right. For example, if in a cell we had the word “Excel formulas” and we used the Excel formula RIGHT (“Excel formulas”; 4) the result would be “ulas”. That is, the last 4 characters of the contents of the cell.
- LEFT: Same as RIGHT but beginning with the beginning of the contents of the cell.
- EXTRACT: Returns a portion of a text string of a given cell beginning and ending.
- LENGTH: Returns the number of characters in a cell with the beginning and ending spaces.
- SPACES: removes any spaces that may be inside a cell at the beginning or at the end. For example, if we had a cell with the following content “hello” the result of using the SPACES formula would be “hello”, ie without the two spaces at the beginning and at the end.
- MAYUSC: This Excel formula returns a string of text in uppercase letters.
- MINUSC: same as MAYUSC but in lower case.
- NOMPROPY: This superformula Excel is used to put a capital letter at the beginning of each word of a text string. It is very useful when we have name and surnames and people write with lowercase.
- REPLACE: This Excel formula allows you to replace one or more characters in a text string.
- TEXT: given an Excel cell with a number, allows us to convert that number to text type with a specific text format. Ideal for use with dates and decimal numbers.
- VALUE: Converts a text string that represents a number and returns it as a numeric type.
DATE AND TIME FORMULAS
- NOW: Returns the date and time of day on which the formula is entered.
- TODAY: returns the date when the formulas are refreshed (for example, when you open a workbook or when you insert a new Excel formula).
- YEAR: Returns the year number of a date.
- DAY: Returns the day of the month of a date.
- MONTH: Returns the month number of a date.
- DIASLAB: given two dates this formula returns the number of working days between the two. You can add holidays to make it more accurate.
- FIN.MES: This Excel formula tells you the last day of the month given a date and a number of months
- TIME: Returns the time number of a cell that has an “hour” format.
- MINUTE: Returns the minute number of a cell that has an “hour” format.
- SECOND: Returns the second number of a cell with an hour value.
- DIASEM: Returns the day number of the week according to different calendars. In our calendar, the number 1 would be on Monday, 2 on Tuesday and so on.
- SYMPTOM NUMBER: Returns the week number of a date. That is, by the date of February 9, 2016 I would return 7 (you can try if you do not believe it, lol).
EXCEL LOGICAL FUNCTIONS
Apparently these small Excel formulas are not very valuable, but the reality is that they are superfluous when working with Excel and with conditions. The main Excel formulas of logical type are:
- Formula SI excel: This function performs a TRUE / FALSE type test using a logical test (for example, 5 <10). If the result of the logical test is TRUE then it returns a result or another formula defined by the user, and if FALSE returns another result or formula.
- Formula and excel: this function allows to join two or more logical tests. If the logic tests result in TRUE then the Y function will return TRUE. In case a single logic test is FALSE then the function will return FALSE.
- Formula O : This function allows you to check various logic tests. The difference with the Y function is that with one of the logic tests being TRUE then the whole function will return the TRUE value.
- SI.ERROR : This function will return a result or formula determined by the user if the value of a cell or formula is an error of type NA () or # DIV / 0 or any of the typical Excel errors.
The combination of these Excel formulas can make your functionality increase significantly.
Excel reference formulas are undoubtedly the most complex to understand and use. However, its usefulness is huge when working with tables, data … in short, they have thousands of applications . Each of these Excel formulas you can find good documentation on this page and some exercises Excel to help you better understand and use.
- FINDV: search a numeric or text value in the first column of a table and return a value in the same row of a given column.
- MATCH: returns the position of a value in a range of cells when it finds a match.
- DESREF: Given a cell that acts as the initial coordinate and a position value of a column and row, the formula returns the value of the indicated cell. Somehow it is to indicate a cell and want to get the value of another cell knowing how many columns and rows of distance it is.
- INDEX: given the position of a row and a column returns the value of a cell within a range.
- DIRECTION: Returns the coordinates of a cell in text format given the row number and column in which it is located. It is to translate the coordinates (2, 7) to the reference B7.
- INDIRECT: Given a reference of a text type cell it returns the value of that cell.
- TRANSPONER: This formula is of matrix type. Transpose a matrix.
- DYNAMICALLYARDS: Allows you to get a value from a PivotTable.
- COLUMN: allows to obtain the number of a column of a reference.
- ROW: allows you to obtain the number of a row in a reference.
STATISTIC EXCEL FORMULAS
Excel’s statistical formulas are not widely used in general, but it’s worth noting the following that are very very important if you want to become an expert Excel formulas:
- COUNT: Returns the number of cells in a range with numeric values.
- CONTAR.SI: Returns the number of cells in a range that meet a condition.
- COUNTIF: COUNTER: Returns the number of cells in a range that meet various conditions given by the user.
- COUNTER: Returns the number of cells in a non-empty range. That is, it does not distinguish between text values and numerical values.
EXCEL MAIN FORMULAS
If I had to do a brief summary of the main Excel formulas I would choose the following four functions which I think are the key functions to start:
- Formula SI
- CONTAR formula
- SUMA Formula
- Formula SEARCHCARV
But if you want to go a little deeper in Excel I recommend that you learn very well also the following functions:
- Formula CONTAR.SI
- Formula CONTAR.SI.CONJUNTO
- Formula SUMAR.SI
- Formula SUMAR.SI.CONJUNTO
- COINCIDIR Formula
- Formula INDEX
- Formula Excel Percentage
Although this is very questionable and each one can think about which Excel formulas will suit you better depending on the needs of the work you have.
EXCEL CHEATS: THE +
One of my favorite tricks of Excel formulas is to start Excel formulas instead of the = symbol with the + symbol. And you ask yourself, why? Because to insert the plus symbol (+) you only have to make a single click on the keyboard and because the result is exactly the same. If you do not want to dislocate your little finger by entering formulas, I recommend you do this. You will soon realize that it is much easier, more comfortable and that you save a second in each operation. This, at the end of the day, may be a lot of time.
EXCEL CHEATS: THE &
This Excel trick I love. Basically you can use it to join numbers or texts that are in different cells. With this Excel trick you can forget the CONCATENATE function because it does practically the same thing.
EXCEL CHEATS: FORMULA SIFECHA
This formula is very curious. It is not in the official Excel manuals as you can check it on the Microsoft Excel website . However it is a very, very useful function when working with dates because it allows you to calculate the elapsed time between two dates. As it is a complex function I leave here a very useful tutorial that may interest you: SIFECHA formula .
CREATE YOUR OWN EXCEL FORMULAS
Creating your own Excel formulas can greatly increase Excel’s ability. It is true that it requires a greater knowledge of this tool but nothing that with a little practice you can not get. If you want to see this section of Excel formulas created by the user, unlock it by subscribing to our Facebook or Twitter page.