ADVANCED MS-EXCEL for Data Analysis
We are familiar with the old saying “Jack of all trades, Master of none”, but when we
discuss the capabilities of the famous Microsoft tool of Excel on various forums, we
increasingly realise that this wonder tool is indeed a “Jack of all trades and Master of
most” – specifically for non-programmers!!
Very few other products are used almost daily by almost 1 billion people. So Excel is still
very much relevant (even with the advent of other spreadsheets like Google sheets) and
appropriately loved and difficult to replace and has been increasingly used by
professionals, businesses, students and portfolio managers for decision-making.
It is truly an omnipresent tool!!! But the question arises, is it getting utilized to its full
Most professionals and business owners use only a few set of functions for their daily
There are many wonderful functions and tools available within the software that allow a
user to perform a wide variety of tasks from statistical and mathematical calculations to
preparing reports, automating data analysis, creating data bases and lists, sorting data
in a variety of ways and creating future projections. Its powerful visualization tools make
it extremely useful for making business projections, charts, graphs and predicting future
trends that support decision-making. It capability to handle text as well as numerical data
makes it extremely useful.
We claim that knowledge of advanced functionalities of excel is what you need to unlock
the true value of this powerful tool and let it make your life easy!
Still in doubt?
An insight on how advanced excel is been used by professionals to perform critical
activities across key industries will validate this claim…
If you are Banker or a Finance Wizard…….
- For accurate record-keeping of financial assets banks use advance LOOK UP functions. Learn about these functions here.
- Conditional formatting is used for a swift but valuable insight on data since this
operation quickly takes a list of values and formats each cell by colour code, by
data bars, or by any one of a number of icon sets.
- Conditional statements and mathematical functions are used to data mine bank’s
existing loan portfolio to prevent loan prepayments and protect existing loans
from being poached by competitors
- Knowledge of financial functions like XNPV, RATE, PMT is very important to
perform financial analysis on accounts data. Also applicable to calculate lifetime
value of loans, Emi , cash flows discounting etc
Application of Visual Basic for Applications, which allows automation of routine
tasks, such as end of business day copying of data from workstations to a
centralized accounting check point are implemented by most banks on top of
Excel’s functionality. Knowledge of VBA is essential for carving out a niche in the
If you are in HR function…..
Following are some of the critical tasks that can be performed with the knowledge of
advanced excel functionalities
- Calculate the tax deductible amount based on the different salary range,
Display first name, last name with prefix in one single column from three different
- Use advanced functions to find years of service of employees, total salary
expenses in different regions, different departments , use of advance PIVOT
TABLES is rampant in these kind of tasks
- Calculate the total salary given to employees of a particular department or
designation in a particular month
- Find number of employees joined on particular date/month in any department in
- Calculate the bonus based on overall performance of an employee,
Find the number of leaves taken by any employee, find the designation of an
employee from his salary,
Set the status, department of an employee through data validation
Compare employee’s involvement in department from the working hours using
- Here are the functions and tables, you might want to learn.
IF you are an Entrepreneur……
Excel is still the go-to option for most business owners. The ability to store data,
manage it and sort it in many different ways has lots of applications for entrepreneurs.
- Finance and accounting : Excel has many advanced formulas that can mean
something that could take hours to do manually can be done in a few minutes.
But even without the complicated stuff, it is ideal for simple bookkeeping, adding
or subtracts costs or expenses and keeping track of earnings.
- Marketing: Advance excel functionalities could help gain customer insights from
the sales data and set sales targets. It can be used to help plan for the future,
decide on the cost of a new product and easily produce reports to help see what
is working and what needs changing. Summarise customer revenue by product to
help find where your business could build strong customer relationships and
Work out customer discounts based on monthly purchase volume could be
another area where advance excel knowledge comes handy
- Budgeting : Create a budget for a small product by listing expenses in a sheet
and updating it monthly then creating a chart to monitor
- Human resources : Advanced excel formulas can be used to look at trends,
summarise expenses and look at pay periods in detail to help monitor business expenses and staff working patterns.
- Learn about entrepreneurial functions and tools of Excel here.