Although we have been looking at some alternatives to the mythical Microsoft tables program for several years, such as Google Sheets, Microsoft Excel is still the king of kings. It is because of its ease of use and the amount of functions and formulas it integrates, allowing us to perform from simple calculations to complex operations. Also for its commands and keyboard shortcuts, as well as for its compatibility with third-party templates. This time we have made a compilation of more than 30 Excel formulas to take full advantage of Microsoft's spreadsheets and tables program.
Basic Excel formulas: addition, subtraction, multiplication ...
If we do not demand an excessively advanced use of Excel, the simplest mathematical formulas, such as addition, division and multiplication, can do the trick for us if we require simple formulas. Of course, we can combine them with each other to make complex formulas.
- Sum : SUM (Cell 1: Cell 2). For example, SUM (A1: A2) or SUM (A1: A2: A4: A5)
- Subtraction : Cell 1 - Cell 2. For example, A1 - A2 or A2 - A3 - A4
- Multiplication : Cell 1 * Cell 2. For example, A1 * A2 or A2 * A3 * A4
- Division : Cell 1 / Cell 2. For example, A1 / A2 or A2 / A3 / A4
Excel formulas with logical operations: greater than, less than ...
In the case of making comparisons between different cells to verify that a certain condition is met, it is best to resort to logical operators, such as greater than, less than and equal to. The result in this case will show the word True if the condition is met and False if it is not met.
- Greater than : Cell 1> Cell 2. For example, A1> A2 or A2> A3> A4
- Less than : Cell 1 <Cell 2. For example, A1 <A2 or A2 <A3 <A4
- Equal to : Cell 1 = Cell 2. For example, A1 = A2 or A2 = A3 = A4
- Other than : Cell 1 Cell 2. For example, A1 A2 or A2 A3 A4
We can also make concatenations to join different operations. As simple as adding the respective signs to the main operation.
- Greater than or equal to : Cell 1> = Cell 2. For example, A1> = A2 or A2> = A3> = A4
- Less than or equal to : Cell 1 <= Cell 2. For example, A1 <= A2 or A2 <= A3 <= A4
Advanced Excel formulas: odd or even, add if ...
We enter into swampy terrain with advanced Excel formulas, in which we can find operations related to matrices, addition conditions or even or odd numbers.
- Add if a condition is met : ADDIF (condition). For example, ADDIF (A1
- Add if a set of conditions is met : ADDIF.SET (conditions). For example, ADDIF.SET (A21: A2, B1: B2, ”= A *”, C2: C9, ”Pepe”)
- Even number : PAR (Cell 1). For example, PAR (A2)
- Odd number : ODD (Cell 1). For example, ODD (A2)
- Random number within a range : RANDOM.INTER (Cell 1; Cell2). For example, RANDOM.BETWEEN (A1, A2)
- Arithmetic mean of a range : AVERAGE (Cell 1, Cell 2). For example, AVERAGE (A1, B1) or AVERAGE (A1: B5)
- Maximum of a range : MAX (Cell 1, Cell 2). For example, MAX (A1, A2) or MAX (A4: A6)
- Minimum of a range : MIN (Cell 1, Cell 2). For example, MIN (A1, A2) or MIN (A4: A6)
Excel text formulas: concatenate, replace ...
In addition to numbers, Microsoft Excel allows you to operate on text or text strings with certain automation operations, such as string concatenation, text extraction, or replacement.
- Text concatenation : CONCATENATE (Cell 1, Cell 2). For example, CONCATENATE (A2: A5)
- Text substitution : SUBSTITUTE ("Original text", "Text to be inserted", Position where it is inserted). For example, SUBSTITUTE ("Good morning", "Hello everyone", 1)
- Text replacement : REPLACE ("Original text", Position where it is inserted, Number of characters to be removed from the original text, "Text to replace"). For example, = REPLACE ("Hello, how are you my dear followers?", 1, 6, "I'm pretty good, really")
- Uppercase text : UPPER (Cell 1, Cell 2). For example, UPPER (A1: A2) or UPPER (A2, A3, A4)
- Lowercase text : MINUSC (Cell 1, Cell 2). For example, MINUSC (A1: A2) or MINUSC (A2, A3, A4)
- First capital letter : PROPER NAME (Cell 1, Cell 2). For example, NAME (A1: A2) or NAME (A2, A3, A4)
- Convert numbers into numeric text : TEXT (Number that we want to convert, ”Number format”). For example, TEXT (TODAY (), ”DD / MM / YY”) or TEXT (0.289, ”0.0%”)
- Convert numeric text into numbers : VALUE ("Text we want to convert"). For example, VALUE ("23.45 €")
- Remove more than one space in text : SPACE (Cell 1). For example, SPACE (A1, A2) or SPACE ("I would like to remove the double space from this text")
Excel formulas for dates and times: days, minutes, seconds ...
If we want to operate with dates, hours and days in general, Excel integrates a multitude of functions oriented exclusively to this type of data, although this depends on the date format that we have previously configured in the configuration, as we saw in the article that we just finished. to bind.
- Difference in days of two dates : DAYS (Cell 1, Cell 2). For example, DAYS (A1, A2) or DAYS (“11/11/2011 ″,” 4,4,2012 ”)
- Number of days of the week (Monday 1st, Tuesday 2nd…) : WEEK NUMBER (Cell 1). For example, WEEKNUMBER (A3) or WEEKNUMBER (“11/11/2011)
- Day of the month of a date in number : DAY (Cell 1). For example, DAY (A3) or DAY (“11/12/2019”)
- Month of a date in number : MONTH (Cell 1). For example, MONTH (A3) or MONTH (“12/11/2019”)
- Year of a date in number: YEAR (Cell 1). For example, YEAR (A3) or YEAR (“11/12/2019”)
- Time of day of a date in number : TIME (Cell 1). For example, TIME (A3) or TIME (“13:14:16”)
- Minute of the day of a date in number : MINUTE (Cell 1). For example, MINUTE (A3) or HOUR (“13:14:16”)
- Second of the day of a date in number : SECOND (Cell 1). For example, SECOND (A3) or SECOND (“13:14:16”)
- Working days of two specific dates : DIASLAB (Cell 1, Cell 2). For example, DIASLAB (A3, A4) or DIASLAB (“12/11/2019”, “12/19/2019”)
- Current date and time : NOW ()