In an office, the best way to survive is being good with Microsoft Office and one of the primary tools to learn and adapt is Microsoft’s Excel. This is a must learner for any employee if he/she wishes to grow in the career as excel is far beyond just summation and subtraction.

Excel is just the god tools for anything and everything that is needed for a decision. Whether it is finance, accounting, merchandising or materials control, Inventory Management just the formula, and with magic, everything manual is made auto and instant decisions possible. You need consolidated or final grouped data – excel is there, you need to create a balance sheet linked from income statement – excel is your helping hand. Basically, excel will be your go-to tool for your daily work needs.

To work better and to become a tiny master in excel, whilst making thing auto I would like to share my thoughts and experiences with excel to get you started.If you’re just starting out with Excel, there are a few basic commands that we suggest you become familiar with. These are things like:

• Creating a new spreadsheet from scratch.
• Executing basic computations in a spreadsheet, like adding, subtracting, multiplying, and dividing in a spreadsheet.
• Writing and formatting column text and titles.
• Excel’s auto-fill features.
• Adding or deleting single columns, rows, and spreadsheets. Below, we’ll get into how to add things like multiple columns and rows.
• Keeping column and row titles visible as you scroll past them in a spreadsheet, so that you know what data you’re filling as you move further down the document.

The Contents at a Glance

• Excel Formulas
• 1) Simple Calculations
• 2) Conditional Formatting Formula
• 3) IF Statement
• 4) Locking Cells
• Excel Advanced Formula
• 1) VLOOKUP Function
• 2) INDEX MATCH
• 3) COUNTIF Function
• 4) Sumif Function

Excel Formulas

1) Simple Calculations

The first and foremost basic formulas are the simple arithmetic expressions – adding, subtracting, multiplying, or dividing any of your numerical values.

• To add, use the + sign.
• To subtract, use the – sign.
• To multiply, use the * sign.
• To divide, use the / sign.

Extra: if you are working with a lot of numbers you can use the formula =Sum(Cell Range) to get sum of all the numbers in the range and to find average of that range you can use =Average(Cell Range)

2) Conditional Formatting Formula

Conditional formatting allows you to change a cell’s color based on the information within the cell. For example, if you want to flag certain numbers that are above greater or less than some number of the data in your spreadsheet, you can do that. If you want to color code commonalities between different rows in Excel, you can do that. This will help you quickly see the information the is important to you.

To get started, highlight the group of cells you want to use conditional formatting on. Then, choose “Conditional Formatting” from the Home menu and select your logic from the dropdown. (You can also create your own rule if you want something different.) A window will pop up that prompts you to provide more information about your formatting rule. Select “OK” when you’re done, and you should see your results automatically appear.

In the bottom is a small gif example showing conditional formatting based on day and temperature.

3) IF Statement

Link to formula: If Statement

At times there comes a situation where we need to show something if something else is happening or is in work. For this sole purpose, Excel has IF statement a lifesaver that tests data and displays the value when the test is true or false.

In our example we used names of some random students (Nudrat, Taylor Swift, Ratz, Ridwan, Zakir, Richi, Tarek, Swift, Max Landis) took their age and tested if they are adult or not.

Ads Disable Temporarily

The formula: IF(logical_test, value_if_true, value of false)

Example Shown Below: =IF(B2<20,”Not Adult”,”Adult”). You can also nest other formulas instead of text: =IF(B2<25,(C1+D1),(C1+E1)

In general terms, the formula would be IF(Logical Test, the value of the true, value of false). Let’s dig into each of these variables.

• Logical_Test: The logical test is the “IF” part of the statement. In this case, the logic is B2<20 because we want to make sure that the cell corresponding with the student has aged less than 20.
• Value_if_True: This is what we want the cell to show if the value is true. In this case, we want the cell to show “Not Adult” to indicate that the student was awarded the 10 points. Only use quotation marks if you want the result to be text instead of a number.
• Value_if_False: This is what we want the cell to show if the value is false. In this case, for any student’s age not above 20, we want the cell to show “Adult” to show adult text. Only use quotation marks if you want the result to be text instead of a number.

4) Locking Cells

Have you ever seen a dollar sign in an Excel formula? When used in a formula, it isn’t representing an American dollar; instead, it makes sure that the exact column and row are held the same even if you copy the same formula in adjacent rows.

You see, a cell reference — when you refer to cell A5 from cell C5, for example — is relative by default. In that case, you’re actually referring to a cell that’s five columns to the left (C minus A) and in the same row (5). This is called a relative formula. When you copy a relative formula from one cell to another, it’ll adjust the values in the formula based on where it’s moved. But sometimes, we want those values to stay the same no matter whether they’re moved around or not — and we can do that by making the formula in the cell into what’s called an absolute formula.

To change the relative formula (=A5+C5) into an absolute formula, we’d precede the row and column values by dollar signs, like this: (=$A$5+$C$5).

Excel Advanced Formula

1) VLOOKUP Function

Link to Formula: VLookup

Have you ever had two sets of data on two different spreadsheets that you want to combine into a single spreadsheet?

For example, you might have a list of people’s names next to their email addresses in one spreadsheet, and a list of those same people’s email addresses next to their company names in the other — but you want the names, email addresses, and company names of those people to appear in one place.

I have to combine data sets like this a lot — and when I do, the VLOOKUP is my go-to formula. Before you use the formula, though, be absolutely sure that you have at least one column that appears identically in both places. Scour your data sets to make sure the column of data you’re using to combine your information is exactly the same, including no extra spaces.

The formula : =VLOOKUP(lookup value, table array, column number, [range lookup])

The formula with variables from our example below:=VLOOKUP(C2,Sheet2!A:B,2,FALSE)

In this formula, there are several variables. The following is true when you want to combine the information in Sheet 1 and Sheet 2 onto Sheet 1.

Ads Disable Temporarily

• Lookup Value: This is the identical value you have in both spreadsheets. Choose the first value in your first spreadsheet. In the example that follows, this means the first email address on the list, or cell 2 (C2).
• Table Array: The range of columns on Sheet 2 you’re going to pull your data from, including the column of data identical to your lookup value (in our example, email addresses) in Sheet 1 as well as the column of data you’re trying to copy to Sheet 1. In our example, this is “Sheet2!A:B.” “A” means Column A in Sheet 2, which is the column in Sheet 2 where the data identical to our lookup value (email) in Sheet 1 is listed. The “B” means Column B, which contains the information that’s only available in Sheet 2 that you want to translate to Sheet 1.
• Column Number: If the table array (the range of columns you just indicated) this tells Excel which column the new data you want to copy to Sheet 1 is located in. In our example, this would be the column that “House” is located in. “House” is the second column in our range of columns (table array), so our column number is 2. [Note: Your range can be more than two columns. For example, if there are three columns on Sheet 2 — Email, Age, and House — and you still want to bring House onto Sheet 1, you can still use a VLOOKUP. You just need to change the “2” to a “3” so it pulls back the value in the third column: =VLOOKUP(C2:Sheet2!A:C,3,false).]
• Range Lookup: Use FALSE to ensure you pull in only exact value matches.

In the example below, Sheet 1 and Sheet 2 contain lists describing different information about the same people, and the common thread between the two is their email addresses. Let’s say we want to combine both datasets so that all the house information from Sheet 2 translates over to Sheet 1.

2) INDEX MATCH

Link to formula : Index, Match

Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:

1. VLOOKUP is a much simpler formula. If you’re working with large data sets that would require thousands of lookups, using the INDEX MATCH function will significantly decrease load time in Excel.
2. INDEX MATCH formulas work right-to-left, whereas VLOOKUP formulas only work as a left-to-right lookup. In other words, if you need to do a lookup that has a lookup column to the right of the results column, then you’d have to rearrange those columns in order to do a VLOOKUP. This can be tedious with large datasets and/or lead to errors.

So if I want to combine the information in Sheet 1 and Sheet 2 onto Sheet 1, but the column values in Sheets 1 and 2 aren’t the same, then to do a VLOOKUP, I would need to switch around my columns. In this case, I’d choose to do an INDEX MATCH instead.

Let’s look at an example. Let’s say Sheet 1 contains a list of people’s names and their Hogwarts email addresses, and Sheet 2 contains a list of people’s email addresses and the Patronus that each student has. (For the non-Harry Potter fans out there, every witch or wizard has an animal guardian called a “Patronus” associated with him or her.) The information that lives in both sheets is the column containing email addresses, but this email address column is in different column numbers on each sheet. I’d use the INDEX MATCH formula instead of VLOOKUP so I wouldn’t have to switch any columns around.

So what’s the formula, then? The INDEX MATCH formula is actually the MATCH formula nested inside the INDEX formula. You’ll see I differentiated the MATCH formula using a different color here.

The formula: =INDEX(table array, MATCH formula)

This becomes: =INDEX(table array, MATCH (lookup_value, lookup_array))

The formula with variables from our example below: =INDEX(Sheet2!A:A,(MATCH(Sheet1!C:C,Sheet2!C:C,0)))

Here are the variables:

• Table Array: The range of columns on Sheet 2 containing the new data you want to bring over to Sheet 1. In our example, “A” means Column A, which contains the “Patronus” information for each person.
• Lookup Value: This is the column in Sheet 1 that contains identical values in both spreadsheets. In the example that follows, this means the “email” column on Sheet 1, which is Column C. So: Sheet1!C:C.
• Lookup Array: This is the column in Sheet 2 that contains identical values in both spreadsheets. In the example that follows, this refers to the “email” column on Sheet 2, which happens to also be Column C. So: Sheet2!C:C.

Once you have your variables straight, type in the INDEX MATCH formula in the top-most cell of the blank Patronus column on Sheet 1, where you want the combined information to live.

Ads Disable Temporarily

3) COUNTIF Function

Link to Formula: Link

Instead of manually counting how often a certain value or number appears, let Excel do the work for you. With the COUNTIF function, Excel can count the number of times a word or number appears in any range of cells.

For example, let’s say I want to count the number of times the word “Gryffindor” appears in my data set.

The formula: =COUNTIF(range, criteria)

The formula with variables from our example below: =COUNTIF(D:D,”Gryffindor”)

In this formula, there are several variables:

• Range: The range that we want the formula to cover. In this case, since we’re only focusing on one column, we use “D:D” to indicate that the first and last column are both D. If I were looking at columns C and D, I would use “C:D.”
• Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is “Gryffindor.”

Simply typing in the COUNTIF formula in any cell and pressing “Enter” will show me how many times the word “Gryffindor” appears in the dataset.

4) Sumif Function

Similar to Countif, the formula as per title sum a range of field once it matches the criteria.

In this formula, there are three variables:

• Range: The range that we want the formula to cover. In this case, since we’re only focusing on one column, we use “D:D” to indicate that the first and last column are both D. If I were looking at columns C and D, I would use “C:D.”
• Criteria: Whatever number or piece of text you want Excel to count. Only use quotation marks if you want the result to be text instead of a number. In our example, the criteria is “Gryffindor.”
• SumRange: The range that we want the formula to do summation on. In this case, since we’re only focusing on one column, we use “D:D” to indicate that the first and last column are both D. If I were looking at columns C and D, I would use “C:D.”

Ads Temporarily Disabled