DEV Community

Joy Maritim
Joy Maritim

Posted on

#Mastering Excel like a Pro: An Absolute Beginner's Guide to Understanding Excel for Data Analytics.

#data #analytics #beginners #tutorial

Introduction to MS Excel for Data Analytics

What is Data Analytics?

Data analytics is the process of collecting, cleaning, analyzing, and interpreting data to discover useful information and support decision-making.

Data can come from:

  • Sales records
  • Student results
  • Business transactions
  • Surveys

Microsoft Excel is one of the most popular tools used for basic data analysis, especially for beginners.It can be used for data entry,calculations, analysis and visualization.

What is Microsoft Excel?

Microsoft Excel is a spreadsheet application used to:

  • Store data in rows and columns
  • Perform calculations
  • Analyze data
  • Create charts and reports

Excel is widely used because it is:

  • Easy to learn
  • Powerful
  • Available in most offices and schools

Advantages of Using Excel for Data Analytics

  • Easy to use for beginners

  • No programming required

  • Fast analysis for small to medium datasets

- Widely accepted in organizations

How Excel is Used for Data Analytics

1. Data Entry and Data Organization

Excel allows users to enter data in a structured format using rows and columns.

Examples of data that can be entered:

  • Names
  • Dates
  • Sales amounts
  • Scores

Each column represents a variable, while each row represents a record.

📸 Screenshot 1:

2. Data Cleaning

Before analysis, data must be clean.

Excel helps in:

  • Removing duplicates
  • Correcting spelling errors
  • Filling missing values
  • Formatting numbers and dates

📸 Screenshot 2:

3. Sorting and Filtering Data

Sorting helps arrange data in a specific order (ascending or descending).

Filtering helps display only the data you need.

Examples:

  • Sorting sales from highest to lowest
  • Filtering students who scored above 60

STEPS on How to Filter Data:
Select the Header row
Go to Data --> Filter
Click the drop-down arrow to filter values

STEPS on How to Sort Data:
Select your Data range
Go to Data --> Sort A to Z OR Z to A

4. Basic Calculations Using Formulas

Excel supports formulas that help perform calculations automatically.

Common formulas include:

  • SUM() – Adds numbers in a range for example =SUM(L2:L633)

  • AVERAGE() – Finds the mean of values for example =AVERAGE(M2:M633)

  • COUNT() – Counts entries for example =count(o2:o633)

  • MAX() and MIN() – Finds highest and lowest values respectively for example =max(L2:L633)

=MIN(L2:L633)

Turning Numbers into insights using Pivot Tables and Charts

Numbers alone can be a little tricky to interpret, but with just a few clicks, you can turn raw data into clear visual insights.

Pivot Table

Pivot tables are helpful in summarizing large data sets quickly and efficiently.

How to Create a Pivot Table

Click on a cell(eg E4)
Select Insert --> Pivot Table
Choose Location (New Worksheet)
Click OK
Using Pivot Table Fields, Select fields to add to the report
Rows: Categories(eg Region)
Values: Numbers to Calculate eg (Count of OrderID)
Columns: Comparison Fields
Filters: Limit displayed data

Example: Count of OrderID by Region

Charts and Data Visualization

Charts help to present data visually.

Key Chart Types and Uses
Column Chart: Vertical bars used for comparing values across categories.

Bar Chart: Horizontal bars, ideal for comparing large sets of categories.

Line Chart: Connects data points to visualize trends over time (days, months, years).

Pie Chart: Displays data as slices of a circle, showing proportions of a total.

Scatter (XY) Chart: Plots individual data points to show relationships or correlations between two variables.

Area Chart: Similar to line charts, but filled below the line to emphasize the magnitude of change over time.

Combo Chart: Uses two or more chart types (e.g., column and line) in one, useful for comparing different data types.

Histogram/Pareto: Used for statistical analysis to show data distributions.

Radar Chart: Compares multiple quantitative variables.

How to Create a Chart

Select range of Data
Go to Insert --> Charts
Choose chart type
Customize the Titles and labels

For Example: Use PivotChart to Represent Count of OrderID By Region

Summary

In this guide, you will learn how to:

  • Basics of Data Analytics and Excel
  • Data Cleaning [Removing Duplicates]
  • Sorting and Filtering Data
  • Basic Calculations using Formulas in Excel
  • Creating Pivot Tables
  • Building Charts for Data Visualization

Conclusion

Microsoft Excel is a powerful and beginner-friendly tool for data analytics. It helps users organize data, clean it, perform calculations, and visualize insights using charts.

For anyone starting a career in data analysis, Excel is an essential skill.

Author

Joy Maritim
Data Analyst

Top comments (0)