Microsoft Excel is a spreadsheet software that was developed by Microsoft to help users to organize their data in rows and columns and do calculations on the data. Excel also allows the users to come up with charts in order to visualize large amounts of data which can then be used to analyze the data.
It is the very first tool that most data analysts learn as it enables them to efficiently handle large datasets, expose insights and automate repetitive tasks using its features such as Power Query, Pivot Tables and conditional formatting.
How is Excel used by analysts?
- Track sales and performance
Microsoft Excel is used to track leads and monitor the sales targets for individuals in a company or the whole company in general. It enables the analysts to check the monthly/ quarterly performance and also generating the reports. Excel helps to compare the targets versus the achievements and helps in identifying the high performing products, individuals, branches and regions.
- Make financial decisions
Excel is used to prepare budgets, track organizational expenses, predict revenue and also create financial models. The financial models enable the companies to predict future growth and forecasting which products have better profitability than others. This enables companies to make better decisions with confidence.
- Manage employee records
Excel is commonly used by the Human Resource Department in managing employee attendance, the payroll and in tracking the performance of every individual employee. This enables the department to recognize those employees who are productive and award them accordingly.
Most Common Functions Used by Analysts in Excel
SUM()
This is used to calculate the total of the values in a range. =SUM(B3:B7)
MAX()
This is used to find the maximum value in a given range. =MAX(B3:B7)
MIN()
This is used to calculate the minimum value in a given range.
=MIN(B3:B7)
SUMIF()
This gives the total value of numbers in a range under a specified criteria.
=SUMIF(C3:C7, "Jersey", B3:B7) This gives the total given by all Jerseys.
AVERAGE()
This calculates the mean of number values within a ranges.
=AVERAGE(B3:B7)
AVERAGEIF()
This calculates the mean of values within a range that meet a specified criteria.
=AVERAGEIF(C3:C7, "Jersey", B3:B7) This gives the average production by the Jersey breed.
MEDIAN()
This calculates the median of a numbers in a range of cells.
=MEDIAN(B3:B7)
TODAY()
This gives the current date. =TODAY()
DATEDIF()
This is a hidden formula in Excel that is used to calculate the difference between two dates.
the syntax is =DATEDIF(Start_date, End_date, Unit)
Example Find the difference between two dates. 1/1/2000 and 1/1/2026
=DATEDIF(C16,C17, "d") Gives 9497days
=DATEDIF(C16,C17, "m") Gives 312months
=DATEDIF(C16,C17, "y") Gives 26years
NETWORKDAYS()
This calculates the difference between two dates while omitting the weekends.
=NETWORKDAYS(C16,C17) which gives 6784
My take:
Microsoft Excel created a way for people to collect, organize and present data in a way that can be easily understood. Given a dataset of 1000 rows, it is difficult to understand the data that you are working with, but when you can use functions to answer the specific questions that you have, then it is converted to very useful information that helps you to make decisions going forward.
Follow me on my journey while we learn Data Analysis together.
Top comments (1)
Good stuff. The article was very direct, as it briefly covered how excel works. I would have loved to to be introduced to what you do, or what your career is about, whay are you passionate about data, why did you choose excel for data and not python or R? Can you take me through how data helps people make financial decisions eg, when you receive a dirty dataset, having financial records, how would you use them to make a decision?I hope to see more articles. The images were spot on as guides to how different formulas work.