Powerful Excel Troubleshooting Tricks For Analyzing Data

Immense power of excel formulas is always admirable. The Microsoft office suit’s excel software is not only capable of doing basic data computing but you can also perform data analysis using it. It is widely used for many purposes including the financial modeling and business planning.

Excel with it’s wide range of functions, visualization, arrays empowers you to quickly generate insights from data which would be hard to see normally. Excel has numerous functions, so sometime it gets confusing to choose the right one. This post will surely help you in upgrading your data analysis skill.

In order to find someone's current age

Now you can easily calculate some one’s age from their date of birth with excel formula. To calculate this you have two functions 1. TODAY(), as you may give the current date. 2. DATEDIF() gives the difference between two dates in units of years(“y”), months ("m") or days ("d"), using the syntax:
=DATEDIF(Date1, Date2, Unit of measure)
So, to get current age in years, use the formula:
=DATEDIF(CellWithBirthday,TODAY(), "y")

CONCATINATE(): 

It is very helpful excel function as by using this you can combine text from two or more cells. Suppose that you want to create a URL based on input of host name and request path.
Syntax: =Concatenate(Text1, Text2,.....Textn)
So to do this, use formula = concatenate(B3,C3) and copy it.
Tip: you can also write formula as “= B3&C3” instead of writing full “concatenate” formula.

Also Read: How to recover damaged excel xlsx/xls files?

COUNTIF Function

Instead of manual counting how often a certain value or number appears, so let’s excel do the work for you. With the COUNTIF function, Excel counts the number of times a number or word appear in any range of cells.
For example, let’s say I want to count the number of times the word "Gryffindor" appears in the dataset.
The formula: =COUNTIF(range, criteria)
The formula with variables from our example below: =COUNTIF(D:D,"Gryffindor")

Paste Specials and transpose

This function is used to copy-paste formula, values or a combination of those. The only annoying thing is that these options are several clicks away. Here the advices will be to put these buttons on your custom toolbar, so when you are doing a lot of copying they are available in a single click. Transpose functions will switch rows to columns and vice versa.

That’s just a few tips on how to become a power excel user. Don’t forget about numerous keyboard shortcuts and powerful functions that helps to speed up your work and leverage your analytics.

For More Information, Read:

11 Simple Yet Powerful Excel Troubleshooting Tricks for Analyzing Data