What is Excel and who needs to know it?
Excel is a popular software that allows users to organize, format, and perform various calculations on data in a spreadsheet. Data in Excel is usually stored in rows or records that run horizontally from left to right and columns or fields that run vertically from top to bottom. The intersection of a row and column forms a cell wherein data is entered.
Excel is widely used in almost all industries to store, process, analyze, and visualize data. In finance, sales, and project management, for instance, it’s used to track invoices and forecast revenue. It’s even more important for those in careers that work daily with data, such as data analysts and scientists, marketing managers and business owners, in their day-to-day work.
What makes Excel even more popular are its collaboration tools like shared workbooks, which are valuable in business settings as they transform a single spreadsheet into dynamic and shared workspaces and improve operational efficiency.
Excel for Practical Data Analysis.
Having known what Excel is, we can now explore how it is used in real-world data analysis in 3 different industries and the most common features in Excel that facilitate that process.
- Supply Chain and Logistics Management.
Managers in this industry can rely on Excel for inventory control to prevent product stockouts and track the shipping of products. In this case, conditional formatting can be used to automatically highlight a product in a certain color if the current stock falls below the preset threshold. Functions such as NETWORKDAYS() can be used to calculate the arrival dates for products on order.
- Project Management.
Project managers can use Excel to make sure that complex projects are staffed adequately, executed within the budget, and delivered on time. Excel can be used to create a visual project timeline chart with fixed start and end dates. The function SUMIF() can be used, for instance, to calculate the total time an employee has been assigned across multiple projects and prevent overburdening one individual.
- Human Resources Management.
HR managers can use Excel to manage employee data, analyze organizational costs, track overtime work, and use features such as PivotTables to ensure employee salaries across various departments are correct.
Formulas and Functions in Excel.
Formulas and functions are used in Excel to analyze data and manipulate text. Although technically different, the two terms are often used interchangeably.
Function: A built-in tool that is pre-programmed, e.g., SUM() or COUNT().
Formula: An equation written by the user to calculate a value in Excel. It always begins with an equal sign (=), e.g., =C4+B2
The following are the most common functions in Excel:
- Math and Trigonometry functions.
Some functions in this category include the following;
SUM(): Adds its arguments.
SQRT(): Returns a positive square root.
ROUND(): Rounds a number to a specified number of digits.
The following image illustrates the use of the ROUND() function.
- Text functions
These are the functions used to extract, clean, format, and manipulate text data.
Some functions in this category include the following:
TRIM(): Removes spaces from text.
PROPER(): Capitalizes the first letter in each word of a text value
UPPER(): Converts text to uppercase
The following image shows the use of the UPPER() function
- Statistical functions
These functions are used to summarize, analyze and interpret data.
Some functions in this category include the following:
MIN()Returns the minimum value in a list of arguments
COUNT() Counts how many numbers are in the list of arguments
AVERAGEIF() Returns the average of all the cells in a range that meet a given criteria
AVERAGE() Returns the average of its arguments
The following image shows the use of the AVERAGE() function to calculate the average current price of 10 products
Conclusion
Having embarked on a journey of learning Excel as a tool for data analysis, I no longer see data just as a collection of numbers and texts with little meaning in real life. If analyzed well using tools such as Excel, data proves to have power to answer important questions and solve many operational challenges that businesses face from day to day. As I continue to develop my skills in Data Science, Analytics and AI, I now appreciate that even though there are more advanced tools like Python and Power BI, Excel will remain to be a tool that is widely used in the data field.
Top comments (2)
The article is good. It makes me a reader a bit curious of how excel is used in our day to day lives. I like that their is flow and structure in the article. I hope to see how you expand on your ideas in the next ones to come.
Good work. The article is well-structured and gives strong practical examples from supply chain, project management, and HR.