From school, Work, commerce and other activities in daily life formula is a basic concept for everyone as you should know your name. Microsoft Excel Formulas are a perfect for both people with ambitions of using it for personal use and business use.
Microsoft Excel is a spreadsheet application among tons of those containing Microsoft Office and Office 365 which process spreadsheet data.
In this post, we will deeply talk about Microsoft excel formulas that can be used in different situations and different data structure, it is about how and when to use those formulas.
Get Microsoft Excel Cheat sheet for free.
Improve the time you spend in Microsoft Excel by using shortcuts, you get 50+ shortcuts with keyboard mappings.
What is a formula?
By saying Formula, I mean a mathematical formula (psst!!… otherwise Google gives you Formula 1)
A definition of WordWeb says that a mathematical formula is a group of symbols combined together to create a mathematical statement.
Again, a formula is a rule or fact written with mathematical symbols.
This means that a formula explains a theory by using well known symbols. Another question rises, What is the difference between a formula and an equation?
What is the difference between formula and equation?
Not a big difference you can between the two terms and their nature yet there is a slight difference that can us understand this:
Let’s say the area of a circle is A=πr2
From the equation above you can see that there are two sides, the left-hand side symbol A which symbolizes Area and the right-hand side which explains area as Pi times Radius squared.
The whole group A=πr2 is an equation while only πr2 is a formula that explains area of a circle.
As I said not a significant factor that can tell the difference between formula and equation.
Hint: To understand better the difference between equation and formula, answer the following questions alone:
- Write down the equation of Area of rectangle?
- What is the formula of Area of rectangle?
Get started with Microsoft Excel formulas
If you tell me right now to do mathematics into my computer, I would immediately open Microsoft Excel as it is capable of doing what your eyes have never seen and what your ears have never heard.
Talking about formula and equation it is capable of doing both.
Excel has thousands of formulas that you can perform instantly, but we will stick to the basics of excel formulas which I will show you as we proceed in this article.
Here are the data to practice while we learn through this article:
This above is a table that shows a blog’s performance in September from 4 to 17, it is made of 3 columns with Date, Views and Visitors respectively and a supplement column of PV/V (Pageviews per visitor).
Essential elements
Cell names
Cell name/Codes: Excel work sheet is identified by the codes of cells where you can identify a specific cell by its code. These codes are based on columns and rows identifiers. Columns are identified by Alphabets while rows are identified by numbers.
Cells take codes in the format of Column, Row. For example, the first cell of sheet has a code of A1 which implies that it is in the first column and the first row.
Cell codes are not friendly and look hard to understand them sometimes. You can give cells the names of choice you want to avoid some bias.
Recalling the last images you can see that there is a difference between the names of two cells, one is A1 and other is TotalViews. This means that you can use both approaches within formulas.
This is how you rename a cell:
Select a cell and right click to select “Define name”:
Give it a name, add comments (If any) and click OK:
Excel formula bar: Talking about formula, there is a way every formula is displayed, but where? Formula bar is bar above the sheet that shows current excel formulas in a specific cell.
In the formula bar is where every formula can be found, on the left side there are three icons; A cross, a tick and function icons which mean Cancel, Enter and Insert a function respectively.
Dive into Excel formulas
Operators in Excel formulas
When performing excel formulas there are identifiers that should be used to execute a certain formula which are called operators. Some of common operators in Microsoft Excel:
- Equal Sign: This is an operator that initiates formula, you can’t write a formula without starting with it. It always start!
- Parentheses: It is an operator that encloses the values of a formula in some cases.
Basic Formulas of Excel
Microsoft Excel has formula that are frequently used in our daily that don’t require to be an excel professional, an account or any other expert.
Here we know that we use Addition, Subtraction, Multiplication and Division most of the time even in cash transaction.
With basic mathematics you can do things in excel either do some basic analysis or create records like I do. I usually record all performance of my blog with Excel to be able to make analysis of Let’s see those formulas:
Addition
In mathematics an addition of one or more numbers is called a sum, so this operation is called a “SUMMATION”. To Perform an addition in excel you use a function of SUM.
Note that addition takes place when you use two or more numbers.
- Select any cell within your sheet
- Write an equal sign (=) and start typing the word SUM, there will come a list of functions
- Locate SUM function and double click on it. It will come with an open parenthesis.
- Select all numbers you want you will a colored rectangle surrounding the numbers.
- After selecting all numbers close the bracket and hit enter.
Subtraction
Like we did in addition (Summation) it is the same case for subtracting with function SUM.
But unlike addition, to do subtraction you do it simply but you transform some numbers according to which one should subtract which one.
The transformation is to add a minus (-) sign before a number to subtract.
You can see that in the formula I have introduced a minus sign before a cell name. Trying to explain the logic behind this method it is like the following:
- Sum of C21 and minus C22 cells: C21 + (-C22)
- In Mathematics a minus and plus gives Minus: Minus + Plus = Minus
- Which finally gives a difference between C21 and C22.
Multiplication
Multiplication is a formula that also follows the same process that other formulas are formatted but the function name used within multiplying two or more numbers is PRODUCT .
Division
Division is another crucial thing to know regarding Microsoft Excel Formulas, it has a function name of QUOTIENT. Quotient is a division of two number – One is numerator, other is denominator.
In constructing a division formula, numerator comes first and denominator comes at last.
Exponential
I take exponential formula as a family of basic mathematics and calculations. Exponential is used when a number is powered by any number, i.e: Y = Xn
In excel there is a specific function to use in order to apply power to a number which is POWER. You take a number and you put it to the power of another number.
Looking at the formula and the image of numbers below, you can see that 15 will be powered by 2.
Custom excel formulas
It is not always necessary to write excel formulas default functions (That are known by excel system), you can build a formula based on ambition or a structure of calculation you have.
There are basic operators in custom excel formulas that reflect all basic formulas we mentioned above. Let’s take a look on those operators:
- + Denotes Addition
- – Denotes Subtraction
- * Denotes Multiplication
- / Denotes Division
- ^ Denotes Power
Formula with more than one operator
There are cases in excel when you need to combine more than one operator in a formula, in that case there is no known function for those cases except to manually create the formula.
What can be done is to formulate a formula identical to the one given. for example: 12*(26+15) in pageviews to see what how many pageviews you would have if the sum of two days were multiplied 12 times.
The answer is 492. Which means that the pageviews for would be 492 for the formula we had…
Test your understanding
From the table of data,
- Calculate PV/V which is how much views each user does on each date. How much views per visitor on 7, September?
- Use both function and custom formulas.
- Calculate the sum of all PV/V.
In Summary
Excel formulas are one of crucial components of Microsoft Excel because they are able to help in various ways including analysis, bookkeeping, accounting and so on. Simply if you have numbers, it is likely to end up doing maths.
You can’t master Excel formulas without mastering basic formulas as we have seen all of them in the above sections, that’s what you must do first.
Also, it is advisable to practice often that’s brings ability of memorization and acquaintance with all those formulas additionally with how you formulate custom ones.
Thanks for reading this, I hope this content was helpful to you.
