DEV Community

Godfrey Babu
Godfrey Babu

Posted on

Introduction to Ms Excel for Data Analytics

#datascience #programming

Microsoft Excel is a powerful tool for data analysis, which is perfect for beginners who want to start their journey into data analytics. This guide will take you through the fundamentals of Excel.

What is Data Analytics?

Data analytics is the process of cleaning, transforming, and modeling data to discover useful information and support decision making.

Getting Started With Excel for Data Analysis

1. Understanding the basics of Excel

  • Workbook: The entire Excel file

  • Worksheet: Individual tabs within a workbook

  • Cells: Individual data containers organized in rows and columns

  • Ribbon: The menu system containing all tools and functions

Essential Excel Features for Data Analytics

2. Data cleaning and preparation

Removing Duplicates

  • Go to Data -> Remove Duplicates

  • Choose which columns to check for duplicates

3. Basic Data Analysis

SUM Function

=SUM(A1:A10)
Enter fullscreen mode Exit fullscreen mode

AVERAGE Function

=AVERAGE(B1:B50)
Enter fullscreen mode Exit fullscreen mode

COUNT and COUNTIF Functions

=COUNT(C1:C100)        // Counts cells with numbers
=COUNTIF(D1:D100,">50") // Counts cells meeting criteria
Enter fullscreen mode Exit fullscreen mode

MIN and MAX Function

=MIN(A1:A10)  // Finds smallest value
=MAX(A1:A10)  // Finds largest value
Enter fullscreen mode Exit fullscreen mode

4. Sorting and Filtering Data

Sorting Data

  1. Select your data range
  2. Then go to sort
  3. Given that you already chose a column sort (ascending/ descending)

Filtering Data

  1. Select your data
  2. Then go to data on your ribbon
  3. Select filter
  4. Use dropdown arrows in headers to filter

5. Creating a PivotTable

  1. Select the required data
  2. Go to Insert then select PivotTable
  3. Place the PivotTable on anew sheet
  4. Select the different areas how you want your table to show (Rows, Columns, Values, and Filters)

6. Conditional Formatting for quick insight
Select the specific column that you want to have a breakdown of t, then go to home on your ribbon the select conditional formatting and choose how you want the data in the column to be simplified.
You can choose the formatting rules like changing the colour of data distribution

7. Creating Pivot Charts
After creating a PivotTable select the table then go to Insert the go to pivot charts choose the type of chart you want (Column chart, Line chart, Pie chart)

Top comments (0)