Data analysing tools

Topics

Sorting and filtering

Tables and structured referencing
Pivot tables and pivot charts
VLOOKUP and HLOOKUP
 Sorting and filtering

Sorting allows the arrangement of data alphabetically ,numerically or by date. This

makes data easier to read and analyse for patterns

• To sort data , select any cell within your ramge and use data>sort on the ribbon
• You can sort by one or multiple columns and in ascending and descending order

Sort function syntax

Text= SORT(array,[sort-index],[sort_order],[bycol])

• Array: the data range to sort

• Sort_index: (optional) the column to sort by (1 for the rst,2 fo rthe second….)
• Sort_order(optional): 1 for ascending,-1 for descending
• By_col(optional): sort by columns instead of rows ;TRUE or FALSE

Example
=SORT(B3:D11,2,-1) sorts range B3:D11 by the second column in descending order

Filtering allows users view only the data that matches speci c criteria,hiding the rest

• to lter
Select your data range,go to data> lter and use drop down arrows in the
column header

Filter function syntax

=FILTER(array,include,[if empty])

• array: the data to lter

• Include: conditions for including rows
• If empty(optional): value to show if nothing matches

Example

=FILTER (B3:D11,D3:D11>90) lters to show only

rows where column D is graeter than 90
fi
fi
fi
fi
fi
 Combine sort and filter

=SORT(FILTER(B3:D11,D3:D11>90),3,-1)

This filters for amounts over 90,then sorts by the third column descending

VLOOKUP

The VLOOKUP function is a premade function in Excel, which allows searches across columns.
It is typed =VLOOKUP and has the following parts:

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

Lookup_value: Select the cell where search values will be entered.

Table_array: The table range, including all cells in the table.
Col_index_num: The data which is being looked up. The input is the number of the column,
counted from the left:
Range_lookup: TRUE if numbers (1) or FALSE if text (0).
 What will be the syntax to find total if you enter ID 5

=vlookup(5,A1:E9,5,0)
If ID 5 is already mentioned

HlOOKUP

We use HLOOKUP to search for a value in the top row of a table and retrieve data from a
speci ed row below. It's ideal for horizontally organized data, complementing VLOOKUP
for vertical layouts.

When to Use the HOOKUP Function in Excel

The HOOKUP Function is most useful when:
• Our data is organized horizontally.
• We need to fetch information from a specific row based on a top-fow value.
• We require exact or approximate matches.

HlOOKUP Syntax and Parameters

-

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

fi
 lookup_value: The value we want to search for in the top row of the table.

table_array: The range of cells that contains the data. The top row of this range will be
searched for the lookup_value.

row_index_num: The row number in the table_array from which to return a value. The
top row is row 1.

[range_lookup]: An optional argument. Use TRUE for an approximate match (default),

or FALSE for an exact match.

Structured reference
Structured reference is a special way to reference data in excel tables using table and column names
instead of cell addresses. This makes formulas easier to read,more dynamic and less prome to errors

When crealing a table. Excel assigns a name lo. the table and each column header
• In formulas, instead of referencing a cell range (like B2: B/10), a structured.
reference uses the table
and column name for eg: = SUM (Sales Table (Amount])

• structured reference are automatically created when a formula is entered in a table.

Selecting table cells for formulas automatically inserts the structured reference

References adjust automatically when rows or columns are added,removed or renamed

, keeping formulas up to date.

• Structured references can be used both inside and outside the table.

• When a formula is entered in a column within the table, Excel uses a format like [@
(column Name I→ which refers to the value in the same vow of the speci ed column.

• Common structured reference syntax includes :

* Table Name [ColumnName) - references an entire column.
* [@ ColumnName - references the value in the same row.
* [EColumn 1]: [ Column 3] - references multiple columns.
* [# Totals], [# Headers], [# Data] - special items for totals, header and data rows.
 • Structured references simplify copying formulas, make formulas
resilient to data changes and improve formula readability
• Eg: To multiply the values in the "Sales" and "Commission" columns for
for each row, insert a formula like =[Q[Sales]l* (@ (Commission]] , and excel will auto- the
formula down the column.

table formatting

• Tables provide built-in formatting options that enhance the appearance and
functionality of data.

• Formatting is automatically applied when a range is converted to a table (using

"Format as Table" or Ctrl + T).

• Table formatting includes:

- Banded rows or columns
- Header rows with filter buttons
- Bolder headers
- Borders
- Color themes

• Options are available to toggle features such as:

- Header row
- Total row
- Banded rows
- First/last column styling
- Filter buttons

• Users can:
- Select from a gallery of table styles
- Create custom styles
- Clear styles to revert to plain formatting

• Table formatting:
- Improves readability
- Highlights important information
- Provides quick access to sorting and filtering
• Converting a data range to a table:
- Applies formatting
- Enables structured referencing
- Makes data management more efficient

Difference Between Structured and Standard Referencing

• Structured referencing uses descriptive table and column names, making formulas easier to
track and automatically resilient to table changes.

• Standard referencing uses cell addresses (like A1:B15), which can break or become confusing
when data is changed or ranges shift.

Applications of structured referencing

Financial modeling: Using table column names for income, expenses, and projections for
transparent calculations.

Sales analysis: Quick summing, averaging, and ltering sales data across months/regions
with table formulas.

Inventory tracking: Referencing product tables and quantities for re-order

Project management: Tracking tasks, dates, and statuses using structured references for
progress and reporting.

• Data validation: Ensuring formulas pull trom correct columns, especially in large
datasets with frequent updates.
fi