MS EXCEL

Class 1
Date: 17-03-25

What is MS EXCEL?
Microsoft Excel (MS Excel) is a spreadsheet program that helps
users organize, format, and calculate data. It's part of the
Microsoft Office suite and is available for Windows, macOS,
Android, iOS, and iPadOS.
MS EXCEL KEYBOARD SHORTCUTS:
 Press and release the ALT key to display "KeyTips" (letters
in small images) next to tabs and options on the ribbon,
then combine these letters with the Alt key to access
commands.
 CTRL + down arrow - 1 million rows approx.
 CTRL + right arrow - approx. 70000
 It’s a good practice to only add 255 sheets
 to make cells align with accurate cell size double click the
+ on the Name box like A, B, C
 To select the entire data base - Select 1 cell + SHIFT +
CTRL + DOWN ARROW and SHIFT + CTRL + RIGHT ARROW
BASIC ARITHMATIC FUNCTIONS
1. Sum
2. Min
3. Max
4. Average
5. Count Numbers
Syntax:
=functin_name(number1,number2,…….) [Select the cells]
ex: =max(B2:B6)
 - Execute the function formulae in one cell and drag the cell
to replicate the same functions in other cells.
- If there is a gap between the cells it won’t calculate the
cells above or below gap
MS EXCEL TABS:
Number:
Use Currency Tab to data involving money
To format currency with commas in Excel, select the cells, go to
the "Home" tab, and click the "Comma Style" button
Conditional formatting in Excel allows you to automatically
format cells based on their values or content, highlighting
important data or trends, and making spreadsheets easier to
analyse.
Charts:
In Excel, charts are visual representations of data, like graphs,
that help you understand trends, comparisons, and patterns in
your data more easily. You can create various chart types,
including column, line, pie, bar, and area charts, among others.
Common Chart Types in Excel:
 Column Chart:
Displays data using vertical bars, useful for comparing values
across categories.
 Line Chart:
Shows data as a series of points connected by lines, ideal for
visualizing trends over time.
 Pie Chart:
Represents data as slices of a circle, good for showing
proportions of a whole.
 Bar Chart:
Similar to column charts but with horizontal bars, also used for
comparing values.
 Scatter Chart (XY Chart):
Displays data as a series of points, useful for showing
relationships between two variables.
 Stock Chart:
Designed for visualizing stock market data, showing high, low,
open, and close values.
How to Create Charts in Excel:
1. Select Data: Choose the cells containing the data you
want to chart.
2. Insert Chart: Go to the "Insert" tab and click on the
"Chart" button.
3. Choose Chart Type: Select the desired chart type from
the available options.
4. Customize (Optional): You can customize the chart by
changing colours, adding titles, labels, and legends.
SORT:
 Using the Sort Feature:
 Select the Data: Choose the cells containing the
data you want to sort.
 Go to the Data Tab: Click on the "Data" tab in the
Excel ribbon.
 Click Sort: In the "Sort & Filter" group, click the
"Sort" button.
 Choose Sorting Criteria: In the "Sort" dialog box,
select the column to sort by, the order (ascending or
descending), and any additional criteria.
 Confirm Sort: Click "OK" to apply the sort.
  Sort data using Customization: Navigate to SORT-
Add levels to customize data
FILTER:
The filter feature in Excel lets you display only data that meets
certain criteria. You can filter data by text, number, color, or
multiple criteria. Filters can be applied to a range of data or an
entire table.
How to use the filter:
1. Select the data
2. Click the Filter button
Other filter options:
 FILTER function: Filter a range of data based on criteria
you define
 Sort & Filter: Sort data by specific fields
INSERT FUNCTION IN EXCEL

 The Insert Function feature in Excel helps users add and

configure functions easily without manually typing
formulas.

How to Use the Insert Function in Excel?

Method 1: Using the "Insert Function" Button

1. Select a Cell where you want to insert the function.

2. Click on the "Insert Function" (fx) button next to the
formula bar.
3. A "Insert Function" dialog box appears.
4. Search for the function or choose a category (like
DSUM, DAVERAGE, DCOUNT, DCOUNTA, etc.).
5. Select the function and click OK.
6. A new dialog box appears where you can enter function
arguments.
 7. Click OK, and the formula is applied.
Syntax-
=DSUM(database,field,criteria)
EX: =DSUM(A1:H44,F1,K2:K3)

Tip: - To select the entire data base - Select 1 cell + SHIFT +

CTRL + DOWN ARROW and SHIFT + CTRL + RIGHT ARROW

Class 2
Date: 18-03-25

UNDERSTANDING SINGLE CRITERIA VS. MULTI-

CRITERIA IN EXCEL

Criteria refers to the conditions you apply when performing

calculations in Excel (like summing, counting, or averaging
values).

1. Single Criteria

When you apply only one condition to filter your data, it is

called Single Criteria.

Example - DSUM with Single Criteria

=DSUM(database,field,criteria)
EX: =DSUM(A1:H44,F1,K2:K3)
2. Multi-Criteria
When you apply two or more conditions, it is called Multi-
Criteria.

Example - DSUM with Multi Criteria

EX: =DSUM(A1:H44,F1,M2:N3)

IMPORT OR EXPORT TEXT (.TXT OR .CSV) FILES

To import a text/CSV file into Excel, go to the "Data" tab, then
"Get & Transform Data" and select "From Text/CSV", browse to
your file, and click "Import"

PIVOT TABLE IN EXCEL

A Pivot Table helps summarize and analyse large datasets by
rearranging and filtering data dynamically. Here’s how your
notes describe the process:
1. Inserting a Pivot Table
o Go to INSERT → Pivot Table
o Select your data range or table (Select from
Tables/Range)
o A dialog box appears → Click OK
o A new sheet opens with the PivotTable Analyze
menu
2. Building the Pivot Table
o The PivotTable Fields panel appears
o Drag and drop fields into different areas:
 Filters → Adds a filter option to refine data
 Columns → Data is displayed in columns
 Rows → Data is arranged in rows
 Values → Numeric data is aggregated (sum,
count, average, etc.)
3. Performing Additional Actions
o Calculations → You can apply Sum, Average, Count,
etc., on numeric data
 o Grouping → Grouping similar data (e.g., grouping
dates by months/years)
o Data Visualization → Convert Pivot Table data into
charts for better insights

POWER PIVOT IN EXCEL

Power Pivot allows you to work with large datasets and
relationships between multiple tables, similar to a database.
1. Combining Two Tables Using Power Pivot
o Navigate to Power Pivot in the Ribbon
o Select the table and click Add to Data Model
o A new tab opens with the Power Pivot window
2. Creating Relationships Between Tables
o Go to Diagram View
o Drag and link the common column between tables
o This establishes a relationship, allowing you to use
fields from both tables in one Pivot Table
 Class 3
Date: 19-03-25

HANDLING LARGE DATASETS:

1. Freeze Panes
Purpose: Keeps specific rows or columns visible while scrolling
through a large dataset.
Steps:
 Navigate to View → Freeze Panes (from the Window tab).
 Options available:
o Freeze Top Row → Keeps the first row visible while
scrolling.
o Freeze First Column → Keeps the first column
visible while scrolling.
o Freeze Panes → Allows you to freeze multiple rows
or columns by selecting a specific area before
applying the freeze.

2. Printing Table Headings on Every Page

Purpose: Ensures that table headings (row/column titles)
appear on all pages when printing large tables.
Steps:
 Navigate to Page Layout → Print Titles (from the Page
Setup tab).
  A Page Setup dialog box opens.
 Under the "Rows to repeat at top" section, select the
row that contains the table headers.
 Click OK to apply.

Tip: You can rename a single cell, row, or column from the
Name Box for better organization.

3. 3D Formulae in Excel
Purpose: Perform calculations using data from multiple sheets.
Steps:
 Navigate to the sheet where you want to display the
result.
 Select the cell and type = to start the formula.
 Switch to different sheets and select the desired cells
while applying operations like +, -, /, *, etc.
 Press Enter to see the calculated result.
Example:
If you want to sum values from Sheet1, Sheet2, and
Sheet3:
=Sheet1!A1 + Sheet2!A1 + Sheet3!A1
This formula adds the value in cell A1 from all three
sheets.

4. Grouping & Ungrouping Columns/Rows

Purpose: Organizes large datasets by grouping multiple rows
or columns together for better visibility.
Grouping Columns/Rows
 Go to Data → Group (from the Outline tab).
  Select multiple rows or columns and click OK to group
them.
Ungrouping Columns/Rows
 Go to Data → Ungroup (from the Outline tab).
 Select the grouped rows or columns and click OK to
ungroup them.

CONDITIONAL STATEMENTS:
1. What Are Conditional Statements?
Conditional statements allow you to apply logic-based formulas
in Excel. These formulas return results based on specific
conditions.
2. Why Are Conditional Statements Used?
They help automate decision-making in spreadsheets, such as:
 Checking if a value meets a criterion.
 Assigning values based on conditions.
 Applying multiple conditions using AND and OR functions.

3. Using the IF Function

The IF function performs a logical test and returns different
values based on whether the condition is met.
How to Use IF Statements?
1. Navigate to Formulas → Insert Function → Search for
"IF".
2. Provide the required syntax and criteria.
Syntax:
=IF(logical_test, value_if_true, value_if_false)
Example:
=IF(F6>=Goal, TRUE, FALSE)
 If F6 is greater than or equal to the Goal, it returns
TRUE; otherwise, it returns FALSE.

4. Handling Cell Dragging Issues

When dragging a formula across multiple cells, Excel updates
the cell references automatically. To prevent unwanted
changes, use absolute references:
Types of References:
1. Relative Reference (Changes when dragged):
=IF(F6>=Goal, TRUE, FALSE)
o When dragged, F6 changes to F7, F8, F9… Hence
naming the cell name helps.
2. Absolute Reference (Does not change when
dragged):
=IF(F6>=$I$2, TRUE, FALSE)
o $I$2 remains fixed while F6 updates.
Absolute Reference Variations:
 $I$2 → Keeps both row and column fixed.
 I$2 → Keeps row fixed but allows column to change.
 $I2 → Keeps column fixed but allows row to change.
5. Using AND & OR with IF
These functions allow checking multiple conditions within an IF
statement.
AND Function
 Returns TRUE if all conditions are true.
=AND(H4:H9)
 Checks if all values in H4:H9 are TRUE.
OR Function
 Returns TRUE if at least one condition is true.
=OR(H4:H9)
 If any value in H4:H9 is TRUE, the formula returns TRUE.

5. Using IF with AND

Example: Bonus Calculation
=IF(AND(H9=TRUE, MIN(B9:E9)>8000), "Bonus", "No
Bonus")
 H9=TRUE → Checks if the condition in H9 is true.
 MIN(B9:E9)>8000 → Checks if the minimum value in the
range B9:E9 is greater than 8000.
 If both conditions are true, it returns "Bonus"; otherwise,
"No Bonus".
 Class 4
Date: 20-03-25

6. COUNTIF:
The COUNTIF function counts the number of cells in a range
that meet a single condition.
Example: Counting Salespeople Who Have Reached Their Goal
If column H5:H9 contains TRUE for salespeople who have met
their sales goal, the formula:
EX: Current number of sales people who have reached their
goal:
=COUNTIF(H5:H9,TRUE)
Explanation:
 H5:H9 → The range of cells being evaluated.
 TRUE → The condition that must be met.
 The function will count how many cells in H5:H9 contain
TRUE.
So, if 3 out of 5 salespeople have TRUE in their respective cells,
the function will return 3.
7. COUNTIFS:
The COUNTIFS function allows counting based on multiple
conditions across different ranges.
Example: Counting Salespeople Who Met Their Goal & Received
a Bonus
If column H5:H9 tracks whether a salesperson met their goal
(TRUE/FALSE) and column I5:I9 contains "BONUS" for those
eligible, the formula:
EX: Current Number of Sales people who have reachedtheir
goal AND got BONUS
=COUNTIFS(H5:H9,TRUE,I5:I9,"BONUS")
Explanation:
 H5:H9, TRUE → Counts only those salespeople who have
TRUE in column H (goal achieved).
 I5:I9, "BONUS" → Further filters the count to include only
those who have "BONUS" in column I.
 The function will return the count of salespeople who meet
both conditions.
For example, if two salespeople in H5:H9 have TRUE and also
have "BONUS" in I5:I9, the function will return 2.

SUMIF :
The SUMIF function sums a range of values only if they meet a
specified condition.
Syntax:
=SUMIF(criteria_range, criteria, sum_range)
 criteria_range → The range where the condition is
checked.
 criteria → The specific condition to match.
  sum_range → The range of values to sum when the
condition is met.
EX: Query1: For store num 3000 count total no of units

=SUMIF(B3:B272,G3,E3:E272)
Query1: For store num 3000 count total no of sales

=SUMIF(B3:B272,G3,D3:D272)

VLOOKUP (Vertical Lookup)

 Used when searching for values in a vertical column.
 The VLOOKUP function is used to search for a value in the
first column of a table and return a corresponding value
from a specified column in the same row.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
 lookup_value → The value to search for.
 table_array → The range of cells containing the data.
The first column must contain the lookup value.
 col_index_num → The column number in the table
from which to return the value.
 range_lookup → TRUE for an approximate match,
FALSE for an exact match.
Example:
=VLOOKUP(B5, 'Master Emp List'!A3:I40, 3, FALSE)
 This searches for the value in B5 within the first column of
the range A3:I40 in the Master Emp List sheet.
 It then returns the value from the 3rd column of the
same row where the match was found.
 The FALSE argument ensures an exact match.
VLOOKUP LIMITATIONS:
❌ Can only look for values in columns to the right of the lookup column.
❌ Slower when working with large datasets.
❌ If columns are added or removed, the col_index_num must be manually updated.

HLOOKUP (Horizontal Lookup)

 Used when searching for values in a horizontal row.
 The HLOOKUP function works similarly to VLOOKUP but
searches for values in the top row of a table instead of
the first column. It returns a value from a specified row
below the matched value.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num,
[range_lookup])
 lookup_value → The value to search for in the first row of
the table.
 table_array → The range of data where the lookup will be
performed.
 row_index_num → The row number from which to return
the value.
 range_lookup → TRUE for an approximate match, FALSE
for an exact match.
Example:
=HLOOKUP(B3,'Master Inventory List'!A2:G5,3,FALSE)
 This looks for B3 in the top row of the range A2:G5 in the
Master Inventory List sheet.
 It then returns the value from the 3rd row under the
matched column.
 The FALSE argument ensures an exact match.
Limitations of HLOOKUP:
❌ Only searches horizontally across rows.
❌ Requires the lookup value to be in the first row of the table.
The same vlookup operation can perform index and match
function
They perform the same functions as index and match function
but are slow
index and match function are faster combined

INDEX: RETURNS VALUE AT A SPECIFIC POSITION

MATCH: RETURNS NUMERIC POSITION OF A VALUE

Class 5
Date: 21-03-25

INDEX: RETURNS VALUE AT A SPECIFIC POSITION

MATCH: RETURNS NUMERIC POSITION OF A VALUE
Combined Index Match