If there was any skill I wished I had been taught during my schooling years, that would be how to master the art of Excel. Excel is a very undervalued tool, especially in high school and university. Often only a few courses will require students to use this, and even then only at a very basic level. However what we are not told is that, by the time we get a job and enter the workforce, we are using Excel almost everyday (in every way)!
Excel is a power data analytical tool but also has a variety of functions and calculations that make number crunching and data analysis much much more simpler. When used correctly it enables the user to create power automated spreadsheets, calculate complex scenarios and even manipulate numbers in all sorts of ways.
In my professional career I use Excel everyday, and everyday I am learning something new about it, the functions are boundless. I encourage all of you, especially if you are thinking of applying for a job soon, get competent with the following 5 Excel skills and stand out from the rest of the other graduates! [Please Note this post does not go into details on how to uses these functions. There are many great resources out there on the interenet and I have also shared below a few]
*In consultation with senior managers at my firm, they have deemed the following Excel skills to be highly desirable in a employee
IF Function, including SUMIF
IF functions are conditions for calculating a cell and the condition must meet whatever you set it to be. For example: IF functions can be as basic as IF A1 = 1, then return a text “YES”. The benefits of having IF functions allows you to quickly apply rules to certain cells or tables and filter on specific conditions and if these conditions hold true, you can return a certain value or text.
The basic description of a IF function is
- =IF(Something is True, then do something, otherwise do something else)
SUMIF, has the same principal as the IF function, with an addition function. SUMIF allows you to add up numbers or values in given cells only if the (IF function) condition holds true. Having the ability to quickly apply IF functions to cells on large data sets gives you the ability to filter out any background noise and only concentrate on the data set of interest.
VLOOKUP is the quick search function of excel. In the presence of large data sets, its only time consuming and inefficient to manually scroll through and check for specific cells. With the VLOOKUP function, it allows quick and easy search of a table or cells for a particular value or text. In addition we can use VLOOKUP to search up one value and return another. For example if you have a spreadsheet of peoples names and grades, you can quickly find and return the grade of a particular student by using the VLOOKUP function.
The basic description of VLOOKUP is:
- =VLOOKUP(Value you want to look up, range where you want to lookup the value, the column number in the range containing the return value, Exact Match or Approximate Match – indicated as 0/FALSE or 1/TRUE).
Tables and charts
You might be asked one day to give a presentation, and in that presentation you will asked to give feedback on the progress of sales over the last month, current market share and maybe even month-on-month growth figures. The best way to present these data is obviously in the format of charts or graphs. It’s not complicated to develop these charts and graphs but the challenge is how you can make it stand out and convey the message you want to get across. In this section formatting is especially important, the type of visual (line graph vs bar chart) you choose is also critical. Remember a picture paints a thousand words.
There are great info-graphic ideas other on the internet, and available for downloading.
Pivot tables are by far the most powerful tool of Excel. A pivot table allows you to extract the significant from a mass spreadsheet full of data and numbers. Pivot tables are customizable, with the users able to drag and drop certain parameters they are interested in and with the convince of inbuilt, mathematics functions.
The key message here is simple, understand and know how to use pivot tables!
Converting between different formats (e.g. TEXT to NUMBER)
Finally we have the converting functions of excel. Excel, although powerful, it is also very sensitive (especially with its cell functions) and as such there will be from time to time required to convert cells from one measurement unit to another measurement unit. Not only does this safe time but also ensures your flow on formulas do not return any errors.