What is Excel?
Excel is a spreadsheet application that allows organizing, manipulating, calculating and visualizing data. Uses grid od rows and columns. Each grid is a cell that holds data.
How is Excel Used in the Real world
Excel is used across industries including finance, healthcare, marketing and retail. Common real-world applications;
Sales analysis: Tracking product performance, revenue and discounts.
Financial reporting: Budgeting, forecasting and expense tracking
Project management and planning: Create timelines, track tasks, and allocate resources.
Customer relationship management: Small businesses use excel to store, organize and analyze customer information and sales inquiries.
Data Cleaning
This involves removing inconsistencies, duplicates and errors to prepare data for analysis.Real world data features missing values, duplicates and inconsistent formatting. Here are the steps that I worked through:
Back up data: Created a copy of the original dataset
Removing duplicates: Using data tab and removed duplicates
Fixing data types: converting price columns from texts to numbers
Handling missing values: on the ribbon is the find and replace(Ctrl + H) where you replace blank cells with the appropriate data
Extracting numbers from texts (e.g. “4.5 out of 5” to 4.5)
Use TRIM to remove spaces
Formulas and Features learnt for Data Transformation
Excel formulas are essential for transforming raw data into usable formats.
Aggregate functions (SUM, AVERAGE, MIN, MAX, COUNT)
Categorizing data with IF function
=IF(b2>4, “excellent”, IF(b2>2, “average”, “POOR”))
LOOK UP functions. (VLOOK UP, XLOOK UP): They search for a specific value in a table and return corresponding data.
Pivot Tables for Data Analysis
Pivot tables are used to summarize and analyze data.
With pivot tables, you can compare categories such as discount category vs number of reviews, count products and calculate averages.
For Example; you can create a pivot table to find average rating by discount category
Excel allows users to create interactive dashboards using charts and slicers.
Common charts used: Bar charts, Column charts, Pie charts, Donut charts, scatter plots.
Example;
Analyze the relationship between discount percentage and number of reviews.
Adding a trendline helps identify whether it’s a positive, negative or no relationship between the variables.
Building Interactive Dashboards
A dashboard is a visualization tool for summarizing key performance indicators (KPIs) and data trends.
Key elements of an excel dashboard
KPIs: Total products, Average rating, Average discount percentage, Total reviews
Pivot tables
Pivot charts
Slicers; allows users to filter data dynamically, making the dashboard interactive
Conclusion
Learning excel has changed my approach to data, from overwhelming intimidating numbers to a clear, structured one. I now understand that data must be cleaned, organized and standardized before analyzing. Excel has trained my mind to think logically, like why did this happen. Tools like conditional formatting helps to spot outliers, while pivot tables help summarize large datasets. Understanding function has increased my confidence in data accuracy.
Top comments (0)