MODULE 3: EXCEL FOR DATA

ANALYTICS
COMPLETE REFERENCE MANUAL & TECHNICAL GUIDE

1. Excel Basics

Worksheets & Formatting

A worksheet is a single spreadsheet page containing a grid of cells structured in columns (letters) and rows
(numbers). Professional formatting is crucial in data analytics to ensure readability, structure, and
presentation of raw numbers into clean, translatable metrics. Key analytical formatting tools include setting
specific data categories (Currency, Accounting, Short Date, Long Date, Percentage) and applying alignment
styles to build highly readable summaries.

Cell Referencing (Relative vs. Absolute)

Cell referencing determines how cell addresses behave when a formula is copied or dragged to other cells:

• Relative Referencing ( A1 ): The cell reference automatically adjusts based on its relative position when
dragged across columns or rows. For example, dragging =A1+B1 down one row transforms it into
=A2+B2 .

• Absolute Referencing ( $A$1 ): Lock both the column and row coordinate completely using the anchor
symbol ( $ ). Pressing F4 toggles these states. No matter where you copy the formula, the cell position
remains locked. Useful for referencing tax rates, static thresholds, or fixed dates.
• Mixed Referencing ( $A1 or A$1 ): Locks either the column or row. For example, A$1 locks row 1 but
lets columns shift; $A1 locks column A but allows rows to advance.

Text to Columns
A powerful data parsed utility located under the Data Tab. It breaks down unified strings inside a single cell
column into distinct, separate columns based on specific rules:

• Delimited: Splits fields based on characters such as commas, semicolons, tabs, spaces, or custom
symbols (e.g., separating "John,Doe,Finance" into three individual cells).
• Fixed Width: Splits information aligned perfectly in aligned columns with uniform spaces between each
field.

Text Functions
Text functions allow analysts to programmatically extract, clean, and combine text strings without destroying
source records.

Module 3: Excel for Data Analytics Page 1 of 6

 CONCAT
=CONCAT(text1, [text2], ...)

Joins multiple text strings or cell ranges together seamlessly into a singular consolidated cell block.

Example: =CONCAT(A2, " ", B2) combines a First Name and Last Name with a space separator.

LEFT & RIGHT

=LEFT(text, [num_chars]) | =RIGHT(text, [num_chars])

Extracts a specific quantity of characters starting exactly from the far left or far right position of a target
string.

Example: =LEFT("EMP1024", 3) returns "EMP" . =RIGHT("EMP1024", 4) returns "1024" .

MID
=MID(text, start_num, num_chars)

Extracts a character substring out of a target string, beginning at a designated character position for a
specific character length count.

Example: Extraction of region codes from string "US-NY-001" using =MID("US-NY-001", 4, 2)

extracts exactly "NY" .

2. Data Cleaning in Excel

Data cleaning is the initial foundational component of any analytics workflow. Unstructured, duplicated, and
dirty datasets result in flawed downstream calculations and misleading visualizations.

TRIM
=TRIM(text)

Removes all leading, trailing, and unnecessary internal spaces from text, leaving exactly one single
space between adjacent words. Essential for cleaning up misaligned strings or imported text files.

CLEAN
=CLEAN(text)

Removes all non-printable characters (such as line breaks or ASCII control codes 0 to 31) that often slip
into datasets when importing text from legacy enterprise software or web scrapers.

Module 3: Excel for Data Analytics Page 2 of 6

 SUBSTITUTE
=SUBSTITUTE(text, old_text, new_text, [instance_num])

Replaces explicit matching occurrences of old text with updated text inside an target cell sequence
string.

Example: Standardizing hyphens =SUBSTITUTE("2026/06/19", "/", "-") returns "2026-06-19" .

Remove Duplicates Tool

Located on the Data Tab -> Data Tools. This operation checks selected column headers to identify rows with
matching values. Analysts can toggle structural parameters to match duplicates across single columns (e.g.,
unique customer IDs) or across complete rows to strip out redundancies.

Data Validation
A mechanism used to restrict user entries inside targeted spreadsheet cells to ensure data integrity before
formulas evaluate them. Found on the Data Tab -> Data Validation. You can restrict input criteria to Whole
Numbers, Decimals, Date ranges, Time values, Text lengths, or standard custom drop-down listings via List
parameters.

3. Important Excel Functions

Basic Aggregations
• =SUM(range) : Tallies all quantitative value entries within a defined data field.
• =AVERAGE(range) : Computes the arithmetic mean of the selected numerical cells.
• =COUNT(range) : Counts only the cells containing numeric values. (Note: Use COUNTA to count non-
empty text/numeric cells).

Logical Operations

IF, AND, OR Statements

=IF(logical_test, value_if_true, value_if_false)

Evaluates a condition and returns one value if true, and another if false. Can be combined with AND()
(requires all checks to pass) or OR() (requires any individual check to pass).

Example: =IF(AND(A2>50, B2="Pass"), "Approved", "Rejected")

Module 3: Excel for Data Analytics Page 3 of 6

Lookup Utilities

VLOOKUP vs. XLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Searches for a value in the first column of a table array and returns a value in the same row from a
specified column index. Set FALSE for an exact match.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],

[match_mode])

The modern successor to VLOOKUP. It searches an independent lookup array and returns matching
entries from a separate return array. It handles leftward lookups, defaults to exact matches, and
eliminates index dependencies.

Conditional Aggregations

Function Syntax Core Analytical Application Purpose

=COUNTIF(range, criteria) Counts the total number of cells within a range that meet a specific
condition (e.g., COUNTIF(A1:A10, ">100") ).

=SUMIF(range, criteria, Adds numeric entries within a specific range that meet a single criteria
[sum_range]) (e.g., summing total sales for a specific region).

=AVERAGEIF(range, criteria, Calculates the average of cells in a range that meet a single criteria.
[avg_range])

Module 3: Excel for Data Analytics Page 4 of 6

 4. Deep Dive: Pivot Tables & Charts
A Pivot Table is Excel's core interactive summarization engine. It allows analysts to transform thousands of
rows of unorganized individual transactional data points into clear, multi-dimensional tables within seconds,
without writing a single line of code or formula.

The Four Core Architecture Quadrants

When creating a Pivot Table (Insert -> PivotTable), Excel opens an empty layout and presents a
configuration field split into four distinct structural quadrants:

1. Rows (Dimension Builder): Dragging a data column header here displays its unique values as vertical
labels along the left side of the table. This acts as the structural breakdown category for your data
analysis.
2. Columns (Cross-Tabulation Labels): Dragging a data header here displays unique values horizontally
across the top of the table. This creates a matrix layout to compare variables over time or across
categories.
3. Values (The Calculation Core): The quantitative engine. Dragging data elements here applies
mathematical aggregations (such as SUM , COUNT , AVERAGE , MIN , or MAX ) to generate your summary
metrics. You can also right-click these values to show metrics as percentages of totals.
4. Filters (Global Restrictions): Places a top-level drop-down box over the entire summary grid, allowing
users to isolate specific subgroups of data (e.g., viewing the entire table for a single country or year).

Grouping Capabilities
Grouping lets you dynamically consolidate dates, numbers, or text fields into clean, structured categories
without changing the source data:

• Date Grouping: Right-click a date field inside the Rows or Columns panel and choose Group. Excel can
automatically bundle daily transaction lines into Months, Quarters, or Fiscal Years.
• Numeric Grouping: Group numerical ranges into defined buckets (e.g., grouping age metrics into 10-year
intervals or monetary values into custom brackets).

Pivot Charts
A dynamic, visual extension of your summarized Pivot Table data, found under PivotTable Analyze ->
PivotChart. As the structural filtering options, row breakdowns, or layout arrangements of the underlying
Pivot Table change, the linked Pivot Chart automatically refreshes and updates its presentation in real time.

5. In-Depth Focus: Slicers

A Slicer is a visual, interactive filtering tool for Pivot Tables and Pivot Charts. Instead of clicking through
traditional drop-down filter menus, Slicers provide clean, clickable floating dashboard buttons that show
current filter states at a glance.

Module 3: Excel for Data Analytics Page 5 of 6

Core Characteristics & Layout Elements
• Visual Buttons: Every unique item within a selected data column is rendered as a distinct, clear button
tile. Clicking a button instantly filters the entire connected report.
• Multi-Select Capability: By holding down the Ctrl key while clicking or toggling the Multi-Select toggle
icon ( Alt + S ), users can easily filter by multiple items at once.
• Dynamic Visual States: Slicer button options change appearance based on context: active selected
items appear highlighted in deep accent tones, unselected elements appear light/neutral, and items with
zero matching data points are visually dimmed.

Connecting Slicers to Multiple Pivot Tables

The true power of Slicers lies in their ability to orchestrate multi-component dashboards across different
datasets or worksheets through Report Connections:

1. Insert a Slicer on your primary Pivot Table summary block via PivotTable Analyze -> Insert Slicer.
2. Right-click the floating Slicer container and select Report Connections... from the context menu.
3. A checkbox layout menu will open, displaying every individual Pivot Table built across the active workbook.
4. Check the boxes for all target Pivot Tables to establish a unified link. Now, clicking a single button on that
Slicer will filter all connected reports, summaries, and charts simultaneously.

Summary Checklist for Building a Data Analytics Dashboard:

1. Ensure your source data contains clean column headers and no blank rows.

2. Convert raw inputs into an official Excel Table ( Ctrl + T ) so any new rows are automatically
included in your analysis.

3. Generate your Pivot Tables to summarize key metrics across dimensions.

4. Add Pivot Charts to visually convey your findings.

5. Insert Slicers and use Report Connections to link them to all your Pivot Tables, creating an interactive,
synchronized analytics dashboard.

Module 3: Excel for Data Analytics Page 6 of 6