Microsoft Excel Formulas and hacks [2020 Ultimate guide]

Microsoft Excel Formulas

Table of Contents

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.

Microsoft Excel shortcuts book ccover

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?

Microsoft Excel formulas are many – Image by WallpaperAccess

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.

There is a slight difference between equation and formula

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:

We use data to better understand the philosophy of excel formulas

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.

Microsoft excel uses a formal of codes to declare cells.

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.

You can name a cell a custom name

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”:

Define cell name

Give it a name, add comments (If any) and click OK:

Rename a cell

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.

This is a formula bar

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.
Summation Formula in Excel
Summation Formula in Excel

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.

Subtraction in Excel formulas

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 .

Multiplication in Excel formulas

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.

Division formula structure in excel

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.

Power Formula

How to formulate Power formula in Excel formulas

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.

Multiple operators in excel formulas

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.

Share this article:

Share on facebook
Share on twitter
Share on linkedin
Share on tumblr
Share on pinterest
YOU MAY ALSO LIKE

ABOUT KORD BLOG

TECH, LIFESTYLE AND EDUCATION BLOGGER

Welcome To Kord Blog, A blog where you can find reliable answers to your questions, enjoy the read and we like to hear from you.

If you like things like tech, lifestyle and education this is your blog. More to come!

SPONSORED LINKS
RECENT POSTS
CATEGORIES

Leave a Reply

Your email address will not be published. Required fields are marked *

Microsoft Excel shortcuts book ccover

GET FREE ULTIMATE MICROSOFT EXCEL SHORTCUTS CHEAT SHEET

Stop wasting 64 hours every year. Use keyboard shortcuts instead – They make it very easy.

Get full access to a cheat sheet of 50+ Microsoft Excel shortcuts with illustrations into your inbox.