DEV Community

Cover image for How Excel Is Used in Real-World Data Analysis
Mwai Victor Brian
Mwai Victor Brian

Posted on

How Excel Is Used in Real-World Data Analysis

Introduction

Excel is an application to store, organise, and analyse data in a spreadsheet. Whether you're a small business or a large corporation, Excel is a tool that you use daily to make sense of numbers and make decisions.

I used to believe that data analysis was complicated and required some sort of complex software or coding until I started using Excel. So far in one week, I've learned that MS Excel is one of the most powerful and widely-used tools in the world with data, and it's much more easy to use than I ever thought.

3 Ways Excel Is Used in Real-World Data Analysis

  1. Business Decision-Making.Businesses use Excel to measure the performance of their sales, monitor inventory and compare how things are doing from one period to the next. Excel could be used by a business manager to rapidly determine which product line is not a success and to determine where resources will be directed.
  2. Financial Reporting. Accountants and finance teams use Excel to create budgets, income statements and to predict future income. You can use functions such as SUM, SUMIF, and SUMIFS to sum thousands of rows of data in an instant, for example, to calculate the total sales for a selected region or product category.
  3. Marketing & Operational Performance. Marketing teams use Excel to analyse campaign data which tracks clicks, conversions and costs. The operations teams are responsible for sorting and filtering massive amounts of data to identify trends, identify any duplicate records and to clean up messy records before they're presented to leadership.

3 Excel Features I've Learned and How They Apply

1. Data Cleaning & Validation. Rarely are data in the real world perfect. I have been able to learn how to remove duplicates, correct inconsistencies and also how to implement data validation to limit the values that may be entered into a cell. This is essential as decisions are only as effective as the data upon which they are based.

2. Sorting & Filtering. Sorting data alphabetically or by value, and filtering to show only relevant rows, are skills I now use constantly.

3. Statistical Formulas: AVERAGE, MEDIAN, MODE. These three and more formulas convey different stories about a dataset. AVERAGE returns the average (i.e. midpoint), but when there are outliers, for example, in salary data, a few numbers that are very high, then MEDIAN is more useful. In customer surveys or inventory management, the most common value will be identified using the MODE function.

Personal Reflection

learning Excel has altered my perception of data. Once I used to see a spreadsheet as a table of numbers only. I now view it as a tale to be discovered. For a small business, even such a basic SUMIF function can show which product is really bringing in the bucks and the information could alter a company choice.
I'm just one week in and already I'm feeling more comfortable tackling real world data challenges. So I'm excited about continuing to develop these skills.

> This article was written as part of my Data Science & Analytics journey at LuxDevHQ. I'll be posting more articles and documenting my progress and projects on my GitHub as well feel free to follow along!

Top comments (0)