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:
The Contents at a Glance
The first and foremost basic formulas are the simple arithmetic expressions – adding, subtracting, multiplying, or dividing any of your numerical values.
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)
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.
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.
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.
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).
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.
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.
Like VLOOKUP, the INDEX and MATCH functions pull in data from another dataset into one central location. Here are the main differences:
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:
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.
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:
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.
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: