DEV Community

Benta Okoth
Benta Okoth

Posted on • Edited on

Microsoft Excel in Data Analytics

Microsoft Excel is a spreadsheet program from Microsoft that lets you organize, calculate, analyze, and visualize data using rows, columns, formulas, and charts.
It’s widely used for everything from simple lists to complex financial mode

Key Features and Functions

  1. Data Organization: Users can enter data into cells, which are organized by columns (letters) and rows (numbers).
  2. Calculations & Formulas: It uses formulas and functions (e.g., SUM, VLOOKUP) to perform complex calculations and data manipulation.
  3. Data Visualization: Excel includes tools to create charts, graphs, and pivot tables to visualize data trends and insights.
  4. Automation: Visual Basic for Applications (VBA) allows users to create macros to automate repetitive tasks.
  5. Data Analysis: Features like PivotTables, sparklines, and conditional formatting help turn raw data into actionable information

Excel is Commonly used for :

  • Financial Modeling: Creating budgets, forecasts, and financial reports.
  • Data Management: Sorting, filtering, and organizing large datasets.
  • Reporting & Analytics: Analyzing data to drive business decision-making.

The Excel Structure

  • Excel data is arranged in Columns (vertical), Rows (horizontal) and Cells (where a row and column meet). Highlighted in orange is a cell

  • At the very top of the Excel window is the Ribbon. The Ribbon is a toolbar that organizes commands into tabs such as Home, Insert, Page Layout, Formulas, Data, Review, and View.
  • Each tab contains groups of related operations — for example, the Home tab includes formatting tools, while the Insert tab provides options for charts, tables, and illustrations.
  • The Ribbon makes it easy to find and use Excel’s features without having to memorize complex commands

Excel Data Types

There are varies data types used in excel:

  • Text (String)
  • Number
  • Date
  • Time
  • Boolean (Logical)
  • Percentage
  • Currency

Common Errors:

Error Meaning

#DIV/0! Division by zero
#N/A Value not found
#VALUE! Wrong data type
#REF! Invalid cell reference

Common Mathematical and Logical Operations

These are used to calculate values, totals, averages, and numeric comparisons.

a) Basic Arithmetic Operations
Excel uses standard math symbols.

  1. Operation Symbol Example
  2. Addition + =A1+B1
  3. Subtraction - =A1-B1
  4. Multiplication * =A1*B1
  5. Division / =A1/B1

b) Common Mathematical Functions
These are built-in Excel functions.

Function Purpose
SUM() Adds multiple values
AVERAGE() Finds the mean
MAX() Highest value
MIN() Lowest value
ROUND() Rounds a number
ROUNDUP() Rounds up
ROUNDDOWN() Rounds down
ABS() Absolute value
COUNT() Counts numbers
COUNTA() Counts non-empty cells

c) Logical Operations in Excel
i. Comparison Operators
Used to compare values.
Operator Meaning
= Equal to
<> Not equal

      Greater than

< Less than
= Greater than or equal
<= Less than or equal

ii. Logical Functions

  • Used for conditional logic. Function Purpose IF() Conditional result AND() All conditions must be TRUE OR() At least one condition TRUE NOT() Reverses logical result

Error-Handling Logical Functions

  • Used to avoid Excel errors. Function and the Purpose IFERROR() Handles errors gracefully ISBLANK() Checks empty cells ISNUMBER() Checks numeric values ISTEXT() Checks text ISERROR() Detects errors

CORE CAPABILITIES

Data Preparation
Importing data from CSV, databases, and external sources.
Cleaning datasets (removing duplicates, handling missing values).
Validating and standardizing formats.

Data Analysis
Functions like SUMIFS, VLOOKUP, INDEX-MATCH for calculations and lookups.
Pivot Tables for summarizing and aggregating large datasets.
What-If Analysis tools (Goal Seek, Scenario Manager) for forecasting.

Data Visualization
Charts and graphs (bar, line, scatter, pie, combo).
Conditional formatting to highlight trends and anomalies.
Interactive dashboards using slicers and pivot charts.

Decision Support
Turning raw numbers into actionable insights.
Tracking KPIs, forecasting sales, and evaluating performance.
Supporting finance, marketing, operations, and research

1. DATA PREPARATION

  • Data preparation is the process of cleaning, organizing, and transforming raw data into a usable format.
  • It ensures accuracy, consistency, and reliability, making analysis faster and insights more meaningful.

Data Cleaning

This can be achieved by creating a staging table of data you are working on then on a separate sheet and name it staging.
(i). Remove duplicates

  • You start by Freezing the column headers - Go to the View Tab on the Ribbon then in the Window Group click Freeze Pane then Freeze Top Row
  • Select the column with unique identifier for example CustomerID, OrderID. Then Go to Data tab in the ribbon then in the Data Tools group, click Remove Duplicates, a dialog box will appear-choose the columns you want Excel to check for duplicates then click on Remove Duplicate then click on OK as below.

Work on column Data Types

  • Click on the column header that contains date then right click and choose format cell and under category select date and choose the date type of your preference as below

  • Select the column with numeric values then right click and choose format cells and under category select number and choose the decimal places of your preference and ensure the date place is uniform for a given column, do the same for the columns containing text.

  • For columns that are expressed as a percentage you can format them to percentage and choose the preferred number of decimal places.

  • You can add Data Validation as well to certain columns. Select the column then Data>> Data Validation dropdown >>Data Validation >> Under settings Allow choose the preferred eg List and then under source list the entries you want Then click OK.( a dropdown appears at that column)

Handling of Missing Values
You start by filtering the column to check if missing values do exist then proceed to insert a column beside it .

  1. If say a column like city has missing data and you have the region and the county data. Apply a filter to the city column to know the columns with unfilled cities then proceed to use Nested IFs statement for example. =IF( ISBLANK(F2), SWITCH(E2, "India","New Delhi", "Nigeria","Abuja", "Brazil","Brasília", "China","Beijing", "United Kingdom","London", "Canada","Ottawa", "Unknown"), F2)

You can as well use formula like
=IF(
ISBLANK(k2),
IFERROR(VLOOKUP(F2, $X$2:$Y$632, 2, FALSE), "Unkown"),
k2
)
We can also replace the missing values with zero for numerical columns.

Top comments (0)