Beginner-friendly introduction to MS Excel for Data Analytics

Microsoft Excel is a spreadsheet application used to store, organize, and work with data. It helps turn raw data into useful information that supports decisions. It can be described as a digital table or grid for organizing, calculating, and analyzing information.

While professional data analysts often use more advanced tools for big projects, Excel remains popular because it's accessible, familiar, and powerful enough for most everyday data tasks.

In Excel, data is arranged in rows(horizontal groups of cells) and columns(vertical groups of cells). A cell is a single box in Excel's grid where you can store one piece of information.

Why use MS Excel for Data Analytics?

1. It's easy to learn and use, even for beginners
2. It quickly organizes large amounts of data
3. It performs fast calculations and basic statistical analysis
4. It helps identify trends through charts and pivot tables
5. It is widely available and accepted in most workplaces
6. It allows quick insights without complex software

Before analyzing data, it's important to understand the basic interface of Excel. This includes knowing the definition of the following:
Row: A horizontal group of cells labelled 1,2,3... on the left.
Column: A vertical group of cells labelled A,B,C... at the top.
Cell: Intersection of a row and a column.
Workbook: The entire Excel file that contains one or more sheets.
Worksheet: The individual page inside the workbook.

In this article, we will learn:

• Data cleaning: Removing duplicates, sorting data, filtering data
• Performing calculations using formulas
• Performing calculations using operators
• Data visualization: Using pivot tables, charts, and dashboards

Data Cleaning

Data cleaning is important because analysis is only as good as the data used.
Some of the cleaning tasks include:

Removing duplicates

This means identifying and deleting repeated entries in a dataset so that each record appears only once.
To do this:
Select the data or the column that may contain duplicates.
Click the Data tab.
Choose Remove Duplicates.
Tick the column(s) Excel should check.
Click OK.

How to clean text with Functions:
TRIM() removes extra spaces
UPPER() and LOWER() standardize text formatting

Sorting Data

Sorting data in Excel means arranging data in a specific order to make it easier to understand and analyze.

To do this:

• Select the column or the entire dataset.
• Go to the Data tab.
• Click Sort A to Z (ascending) or Sort Z to A (descending).

Filtering Data

Filtering data in Excel displays only the rows that meet specific conditions while hiding the rest, allowing you to focus on certain values, dates, or number ranges without deleting any data.

To do this:

• Select your dataset.
• Go to the Data tab and click Filter.
• Small dropdown arrows appear in the column headers.
• Click a column’s dropdown arrow.
• Choose the criteria you want (specific values, dates, or number ranges).
• Excel displays only the rows that meet the selected criteria, hiding the rest.

Performing calculations using formulas:

Click the cell where you want the result to appear.
Type an equal sign (=) to start the formula.
Enter the calculation using numbers, cell references, and operators (like +, -, *, /). For example: =A1+B1 or =C2*D2.
Press Enter to see the result.

=SUM(A2:A877) → Adds all numbers in the range
=AVERAGE(A2:A877) → Calculates the average
=MIN(A2:A877) → Finds the smallest value
=MAX(A2:A877) → Finds the largest value
=COUNT(A2:A877) → Counts numeric values
=COUNTA(A2:A877) → Counts all non-empty cells

Performing calculations using operators:

+ → Addition e.g., =A1+B1
- → Subtraction e.g., =A1-B1
* → Multiplication e.g., =A1*B1
/ → Division e.g., =A1/B1
^ → Exponentiation / Power e.g., =A1^B1

Data Visualization

Pivot Tables
A pivot table is an Excel tool used to quickly summarize and analyze large amounts of data.

It works by reorganizing data to show totals, averages, counts, or comparisons without changing the original dataset.

How to create a Pivot Table:

• Select your dataset.
• Go to the Insert tab and click PivotTable.
• Choose whether to place the pivot table in a new worksheet or the existing worksheet.
• Click OK.
• In the PivotTable Field List, drag fields into Rows, Columns, Values, or Filters to arrange and summarize your data.

Charts
A chart in Excel is a visual representation of data, like bars, lines, or pie slices, that makes patterns and trends easier to understand.

To create a chart:

• Select the data you want to visualize.
• Go to the Insert tab.
• Choose the chart type you want (e.g., Column, Line, Pie, Bar).
• Click the chart style, and Excel inserts it into your worksheet.
• Customize the chart using chart tools (titles, labels, colors) if needed.

Dashboards
A dashboard in Excel is a single screen or sheet that displays key information and metrics from your data in a clear, visual way using charts, tables, and pivot tables.

An Excel dashboard typically consists of:

• Charts – Bar, line, pie, or combo charts to visualize trends and comparisons.
• Pivot Tables – Summarize large datasets into totals, averages, counts, or other key metrics.
• Tables – Organized data or key figures for reference.
• Slicers and Filters – Interactive tools to quickly view specific data.
• Key Performance Indicators (KPIs) – Numbers, percentages, or symbols that show performance at a glance.
• Text Boxes / Labels – Titles, headings, or explanations for clarity.

With that, you now know the basics and can Excel in data analytics. Cheers! 😄