As a data analyst you cannot but admire the immense power packed by the apparently simple piece of software, MS Excel. This piece of computer program is extremely versatile as it can not only to basic data computations but also complex data analysis. The application is used for multiple purposes including complex tasks like of financial modelling and business planning. Learning Excel can be a great stepping stone for people who are new to the world of data analysis.
Most data analytics experts recommend that even before learning dedicated data analysis software like R or SAS one must try their hands on MS Excel. Excel is a piece of software that contains ample visualizations, functions and array features which can be used to generate insights from data that would be otherwise hard to obtain. But it is still not perfect and comes with its own set of drawbacks. Like it cannot handle large datasets very well, for instance if one has to compute 200,000 data sets in excel you will find its beginning struggle. While there still might be ways to handle this problem, but Excel is not known to be a big data tool. The best big data tools are R and Python for handling large data sets.
Here are a few tips to develop a good understanding of Excel:
Using Vlookup: this function helps to search a value in a table and returns with a corresponding value. You can use this Vlookup function in the following manner:
Syntax = VLOOKUP (key to lookup, Source_table, column of source table, are you ok with relative match?)
Note, always remember to lock the range of second table using a ‘$’ sign, this is a very common error associated with this function and is known as relative referencing.
LEN (): one can use this function to know the length of a cell. By length means the number of characters in a cell including the spaces and special characters.
Syntax = Len (Text)
Output example = Len (B3) = 20
CONCATENATE (): this is a highly useful tool that helps us to combine text from two or more cells into a common cell.
Syntax = Concatenate (Text1, text2, text3....)
One can also use the above formula as – Concatenate (B3,C3)
Pro tip: many people prefer using the ‘&’ symbol as they find it more convenient and performs the same function.
TRIM (): this is another handy function in Excel that helps to clean up data which has padding of unnecessary spaces. These unclean space padded data are often treated as unique entries when are copied from internet sources, and it becomes a painstaking process to clean them up. With this function cleaning such data is a breeze.
Syntax = Trim (text)
End notes:
Arguably for a data scientist or an analyst Excel is one of the best programs ever created. All businesses worldwide revere this software which maintains a gold standard in terms of data management. Excel is dynamic software that offers something new to learn for both newbie as well power users.
If you liked what you just read and would like to further improve your Excel skills to increase you analytical grey matter, then follow our regular blog updates and like us on FaceBook. Together we can create a better data driven world with smarter insights...
Interested in a career in Data Analyst?
To learn more about Advanced excel course in Kolkata – click the Link.
To learn more about SAS courses in Kolkata – click the Link.
To learn more about R Programming course in Kolkata – click the Link.
Powerful and Simple Excel Tools That Every Analyst Must Know
4/
5
Oleh
TechTree India
