Data Analytics Lab

Part- A: Spreadsheet (Excel)

Data preprocessing, interpretation and analytical functions

Note: Download the sample data file from the open sources (Kaggle.com, etc.,) to
apply & practice all these functions.
1. CONDITIONAL FORMATTING, IF, COUNTIF, SUMIF,
AVERAGE, CONCAT
2. INDEX, MATCH, UNIQUE, IFS, COUNTIFS, SUMIFS, AVERAGEIFS
3. VLOOKUP, HLOOKUP, XLOOKUP, COUNT, COUNTA
4. LEFT, MID, RIGHT, LEN, SUBSTITUTE, SEARCH, ISNUMBER

5. TODAY, NOW, YEAR, MONTH, NETWORKDAYS, EOMONTH

5. OFFSET, CHOOSE, LET, MAX, SORT, SORTBY, RANK
6. FILTER, FRQUENCY, SEQUENCE, RANDARRAY, IFERROR
7. PIVOT TABLES, WHAT-IF ANALYSIS, DATA VALIDATION,
SUBTOTALS WITH RANGES
8. Develop an interactive dashboard for the Financial Sample Excel
workbook (https://learn.microsoft.com/en-us/power-bi/create-reports/sample-
financial- download) or Sample-Superstore Excel data

KLESNC Rajajinagar
 LAB PROGRAM -1

1. CONDITIONAL FORMATTING, IF, COUNTIF, SUMIF, AVERAGE,

CONCAT using Excel Formulas.

1. Apply conditional formatting to highlight employees with high salary greater than
50,000
2. Use the IF function to categorize employees into "High Performers" or
"Average Performers" based on their sales.
3. Use COUNTIF, SUMIF, and AVERAGE to calculate statistics like no of
employees working in each department and sum of their salaries and average of
their Salaries, average Salary of total employees.
4. Create a new column using CONCAT to combine the employee's name
and department.

Conditional formatting rule:

Conditional formatting is used to change the appearance of cells in a range based on
your specified conditions.
IF Function

The IF function is a premade function in Excel, which returns values based on

true or false condition. It is typed =IF and has 3 parts:

=IF(logical test, [value_if_true], [value_if_false])

COUNTIF Function

The COUNTIF function is a predefined function in Excel, which counts cells as

specified. It is typed =COUNTIF

KLESNC Rajajinagar
 SUMIF Function

The SUMIF function is a predefined function in Excel, which calculates the

sum of values in a range based on condition.
It is typed =SUMIF:
Syntax:
=SUMIF (range, criteria, [sum range])

The condition is referred to as criteria, which can check things like: >, <, >=,
<=,==, !=
AVERAGE Function

The AVERAGE function is a premade function in Excel, which calculates the

average (arithmetic mean). It is typed =AVERAGE

It adds the range and divides it by the number of observations.

CONCAT Function

To concatenate is to link something together.

CONCAT is a function in Excel and is short for concatenate.
The CONCAT function is used to link multiple cells without adding any
delimiters between the combined cell values.
It is typed =CONCAT(cell1, delimiter, cell2)

Example of delimiters
comma, semicolon ; quotes " or ' braces {} pipes | slashes / \

KLESNC Rajajinagar
 1) Conditional formatting rule:
Conditional formatting is used to change the appearance of cells in a range based
on your specified conditions.

1. Apply conditional formatting to highlight employees with high salary greater

than 50,000

HomeTab -> Conditional Formatting -> Highlight All -> Greater

than -> option -> Enter the value -> Click Ok. (Salary > 50,000).

KLESNC Rajajinagar
 2. Use the IF function to categorize employees into "High Performers" or
"Average Performers" based on their sales.

=IF(E2>50000;"HIGH Performers";"Average_performers")

3. Use COUNTIF, SUMIF, and AVERAGE to calculate statistics like no of

employees working in each department and sum of their salaries, average Salary
of total employees.

Enter all the department names in a separate column called

Details. Sales, Marketing, Finance, HR.
To count the number of employees working in each department.
1. To calculate the total number of employees in each department.
=COUNTIF (D$2:D$22;F$2:F$8)

2. To calculate the sum of Salaries in Department wise

=SUMIF (D$2:D$22;F$2:F$8;E$2:E$22)

3. To calculate the average Salary in all department.

=AVERAGE (E2:E22)

KLESNC Rajajinagar
 4. Calculate the average salaries in each department.
=AVERAGEIF (D$2:D$22;F2;E$2:E$22)

5. Create a new column using CONCAT to combine the employee's name and
SALARY
=CONCATENATE (A2;"---"; E2)

KLESNC Rajajinagar
 LAB PROGRAM 2

INDEX, MATCH, UNIQUE, IFS, COUNTIFS, SUMIFS, AVERAGEIFS

 Calculate Value at the intersection of the 2nd row and 2nd column in the range
A3:D12 & Value at the intersection of the 3rd row and 4th column in the range
A3:D12 &use Index and match function to retrieve the sales of the specific employee
based upon their name.
 Match function is used to find out Order value is present in the table or not
 Using Ifs Statement to Categorize the Sales Performance
 Use Unique function to create the List Unique regions
 Use COUNTIFS, SUMIFS, AverageIFS, functions to calculate statistics based on
multiple Criteria, such as region, orders (>=50), sales.

KLESNC Rajajinagar
 INDEX:
The INDEX function returns a value or the reference to a value from within a table or range.
Syntax: INDEX (array, row_num, [column_num])
The array form of the INDEX function has the following arguments:
 Array Required. A range of cells or an array constant.
 If array contains only one row or column, the corresponding row_num or column_num argument
is optional.
 If array has more than one row and more than one column, and only row_num or column_num is
used, INDEX returns an array of the entire row or column in array.
 row_num Required, unless column_num is present. Selects the row in array from which to
return a value. If row_num is omitted, column_num is required.
 column_num Optional. Selects the column in array from which to return a value. If
column_num is omitted, row_num is required.
1. Calculate Value at the intersection of the 2nd row and 2nd column in the range
A3:D12 & Value at the intersection of the 3rd row and 4th column in the range
A3:D12 &use Index and match function to retrieve the sales of the specific employee
based upon their name.
 =INDEX(A3:D12;2;2)
 =INDEX(A3:D12;3;4)
 =INDEX(D3:D12;MATCH("Lana";A3:A12;0))

MATCH function:
The MATCH function searches for a specified item in a range of cells, and then returns the relative
position of that item in the range.
Syntax: MATCH (lookup_value, lookup_array, [match_type])
 lookup_value Required. The value that you want to match in lookup_array.
 lookup_array Required. The range of cells being searched.
 match_type Optional. The number -1, 0, or 1.

KLESNC Rajajinagar
 Match_type DESCRIPTION
1 or omitted MATCH finds the largest value that is less than or equal to lookup_value.
The values in the lookup_array argument must be placed in ascending order, for example:
1, 0, 1, 2, ..., A-Z, FALSE, TRUE.
0 MATCH finds the first value that is exactly equal to lookup_value. The values in
the lookup_array argument can be in any order.
-1 MATCH finds the smallest value that is greater than or equal to lookup_value.
The values in the lookup_array argument must be placed in descending order, for examp
TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.
2. Match is used to find out Order value is present in the table or not
 =MATCH(99;C3:C12;1)
 =MATCH(140;C3:C12;0)
 =MATCH(33000;D3:D12;-1)

IFS Function:
 Use the IFS function to check whether one or more conditions are met and
returns a value that corresponds to the first TRUE condition.
 IFS can take the place of multiple nestedIF statements, and is much easier
to read with multiple conditions.
Syntax:
= IFS(logical_test1, Value1 [logical_test2, Value2] …, [logical_test127, Value127])
3. Using Ifs Statement to Categorize the Sales Performance
=IFS (C3>90, "A", C3>80, "B", C3>70, "C",C3>60, "D")

KLESNC Rajajinagar
 UNIQUE:

The UNIQUE function returns a list of unique values in a list or range.

Syntax: =UNIQUE(array,[by_col],[exactly_once])
 Array (required) - the range or array from which to return unique values.
 By_col (optional) - a logical value indicating how to compare data:
o TRUE - compares data across columns.
o FALSE or omitted (default) - compares data across rows.
 Exactly_once (optional) - a logical value that defines what values are considered
unique:
o TRUE - returns values that occur only once, which is the database notion
of unique.
o FALSE or omitted (default) - returns all distinct (different) values in the
range or array.
4. Use Unique function to create the List Unique regions
=UNIQUE(B3:B12)
=unique(B3:D4,false) /* Compress data across rows*/
=unique(C9:D9,True) /* Compress data across Columns*

KLESNC Rajajinagar
 COUNTIFS:
 The COUNTIFS function is a premade function in Excel, which counts cells in a range
based on one or more true or false condition.
Syntax =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
 The criteria_range1, criteria_range2, and so on, are the ranges where the function check
for the conditions.
 If a number is greater than another number >
 If a number is smaller than another number <
 If a number or text is equal to something =

SUMIFS Function :
 The SUMIFS function is a premade function in Excel, which calculates the sum of a
range based on one or more true or false condition.
 It is typed =SUMIFS:
Syntax=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2] ...)
 The criteria_range1, criteria_range2, and so on, are the ranges where the function check
for the conditions.
 If a number is greater than another number >
 If a number is smaller than another number <
 If a number or text is equal to something =
 The [sum_range] is the range where the function calculates the sum.

AVERAGEIFS Function:
The AVERAGEIFS function is a premade function in Excel, which calculates the average of a
range based on one or more true or false condition.
It is typed =AVERAGEIFS:
=AVERAGEIFS (average_range, criteria_range1, criteria1, ...)
The criteria_range1, criteria_range2, and so on, are the ranges where the function check for the
conditions.
 If a number is greater than another number >
 If a number is smaller than another number <

KLESNC Rajajinagar
  If a number or text is equal to something =
The average_range is the range where the function calculates the average.
5. Use COUNTIFS, SUMIFS, AverageIFS, functions to calculate statistics based on
multiple Criteria, such as region, orders (>=50), sales.
 =COUNTIFS(B$3:B$10;E3;C$3:C$10;F3)
 =SUMIFS(D$3:D$10;B$3:B$10;E3;C$3:C$10;F3
 =AVERAGEIFS(D$3:D$23;B$3:B$23;E3;C$3:C$23;F3)

KLESNC Rajajinagar
 LAB PROGRAM 3
VLOOKUP, HLOOKUP, XLOOKUP, COUNT, COUNTA
VLookup In Excel :

 This stands for the vertical lookup that is responsible for looking for a particular value in the
leftmost column of a table. It then returns a value in the same row from a column you specify.
 Syntax: VLOOKUP( lookup_value, table_array, col_index_num,[range_lookup]
 lookup_value - This is the value that you have to look for in the first column of a table.
 table - This indicates the table from which the value is retrieved.
 col_index - The column in the table from the value is to be retrieved. The Count Of first
Column is always 1.
 range_lookup - [optional] to find an Exact match enter False To find an appropriate
match enter True.
 TRUE = approximate match (default). FALSE = exact match.

1. Now use VlookUp to find Orders of a Specific Employee Based on their name.
 =VLOOKUP(A2;A1:E8;3)
 =VlookUp (“Agatha”;A1:H22;6)

XLOOKUP :
The XLOOKUP function searches a range or an array, and then returns the item corresponding to
the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate)
match.
Syntax=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])

 lookup_value = The value to be searched

 lookup_array =The array or range of cells to search for values
 return_array= The array or range of Sales from which a corresponding Value to be
returned, based on the position of value in the look_up array.
if_not_found] Where a valid match is not found, return the [if_not_found] text you supply.

KLESNC Rajajinagar
 Optional If a valid match is not found, and [if_not_found] is missing, #N/A is returned.
[match_mode] Specify the match type:

Optional 0 - Exact match. If none found, return #N/A. This is the default.

-1 - Exact match. If none found, return the next smaller item.

1 - Exact match. If none found, return the next larger item.

2 - A wildcard match where *, ?, and ~ have special meaning.

2. Use Xlookup to find the ORDER ID of an particular product based on their
product name for vertical table
=XLOOKUP (“Orange”, B2:B6, A2:A6)

3. Use Xlookup to find the Unit Price of a product based on their order id in
Horizontal Table.
=XLOOKUP(“105,B1:F1,B3:F3)

KLESNC Rajajinagar
 COUNT Explanation:
 The COUNT function is generally used to count a range of cells containing numbers or
dates excluding blanks.
Syntax: COUNT (value1, [value2], ...)
 The COUNT function syntax has the following arguments:
 Value1 Required. The first item, cell reference, or range within which you want to count
numbers.
 Value2, ... Optional. Up to 255 additional items, cell references, or ranges within which
you want to count numbers.
 =COUNT(B2:B14)

CountA Function :
 The COUNTA function counts the number of cells that are not empty in a range.

Syntax:
COUNTA(value1, [value2], ...)
The COUNTA function syntax has the following arguments:
 value1 Required. The first argument representing the values that you want to count.
 value2, ... Optional. Additional arguments representing the values that
you want tocount, up to a maximum of 255 arguments.
 The COUNTA function counts cells containing any type of information,
including error values and empty text (""). For example, if the range
contains a formula that returns an empty string, the COUNTA function
counts that value.
The COUNTA function does not count empty cells.
 =COUNTA(B2:B14)

KLESNC Rajajinagar
 Hlookup Function:
 HLOOKUP in Excel stands for ‘Horizontal Lookup’. It is a function that makes Excel
search for a certain value in a row (the so called ‘table array’), in order to return a value
from a different row in the same column.
Syntax: HLOOKUP([value], [range], [row number], [false or true])
 The value you want to look up;
 The range in which you want to find the value and the return value;
 The number of the row within your defined range, that contains the return value;
 0 or FALSE for an exact match with the value your are looking for; 1 or TRUE for an
approximate match.
4. Hlookup Function is used to find out Department of a Specific Employee based
on their name.

KLESNC Rajajinagar
 Lab Program: 4

LEFT, MID, RIGHT, LEN, SUBSTITUTE, SEARCH, ISNUMBER

LEFT Function:
 The LEFT function is used to retrieve a chosen amount of characters, counting from the
left side of an Excel cell. The chosen number has to be greater than 0 and is set to 1 by
default.
Syntax: =LEFT (text,[num_chars])
 The LEFT function uses the following arguments:
 Text (required argument) – This is the original text string.
 Num_chars (optional argument) – Specifies the number of characters to be returned from
the start of the given text. It is the number of characters to be extracted, starting from the
left side of the text. If we omit the argument, the default value of 1 is taken.
 If you want to use the function on a single cell, write:
o =LEFT (cell)
 If you want to use the function on a range of cells, write:
o =LEFT(start cell:end cell)
1. How to Use LEFT Function:
 To retrieve values from the left side of an Excel cell, use LEFT

Step 1) Start and Enter the Values by using LEFT function

 Select a cell E2
 Type =LEFT
 Select a cell (A2)
 Hit enter
 =LEFT(A2)

2. How to Use the LEFT Function with a Defined Length of Characters

1) Select cells and set the number of characters by using LEFT function
1. Select a cell E2
2. Type =LEFT
2) Enter the cell name (A2) and define the length of characters you want to retrieve, using a , as
a delimiter (A2,3)
3. Hit enter
 =LEFT(A2;3)
 The function returns the first 3 characters from cell A2.

KLESNC Rajajinagar
 RIGHT Function:
 The function will return a specified number of characters from the end of a given text
string.
 In doing financial analysis, the RIGHT function can be useful if we wish to extract
characters from the right side of a text string. Generally, it is used by combining it with
other functions such as VALUE, SUM, COUNT, DATE, DAY, etc.
Syntax:
=RIGHT (text,[num_chars])

The RIGHT function uses the following arguments:

1. Text (required argument) – This is the original text string.

2. Num_chars (optional argument) – Specifies the number of characters to be returned from
the end of the given text. It is the number of characters to be extracted starting on the
right side of the text. If we omit the argument, the default value of 1 is taken.
Example 1:
Suppose we wish to find certain characters from the data below:
 =RIGHT(A2;3)
Example 2 :
 The RIGHT function always returns a text string, even though it contains digits and looks
like a number. This is important if we wish to use the result of the function within other
functions or formulas.
 Using the VALUE function, we can ensure the number format is kept and the result is not
converted to text. we wish to separate numbers from the data below:

 BEN-2345
 JOHN-5432
 BHT-5645

1. We wish to extract the last 4 characters, the order number. The formula to use will be
 =VALUE(RIGHT(C2;4))

MID Function:
 Generally speaking, the MID function in Excel is designed to pull a substring
from the middle of the original text string.
 The Excel MID function has the following arguments:
 = MID(text, start_num, num_chars)
Where:
 Text is the original text string.
 Start_num is the position of the first character that you want to extract.
 Num_chars is the number of characters to extract.

KLESNC Rajajinagar
 All 3 arguments are required.
1. For example, to pull 3 characters from the text string in A2, starting with the 2nd
character, use this formula:
=MID (A2;2;3)

2. MID formula to get the first name and Last name:

1. =MID(D2;1;SEARCH(" ";D2))
2. =(MID(D2;SEARCH(" ";D2);LEN(D2)))

LEN Function:
LEN returns the number of characters in a text string.
Syntax:
LEN (text)
 Text Required. The text whose length you want to find. Spaces count as characters.

=LEN (A2)

SEARCH Function:

 The SEARCH functions locate one text string within a second text string, and return the
number of the starting position of the first text string from the first character of the
second text string.
 Syntax: SEARCH(find_text,within_text,[start_num])
o find_text Required. The text that you want to find.
o within_text Required. The text in which you want to search for the value of
the find_text argument.
o start_num Optional. The character number in the within_text argument at
which you want to start searching.
 For example, to find the position of the letter "n" in the word "printer", you can use the
following function:
o =SEARCH("n","printer")
 This function returns 4 because "n" is the fourth character in the word "printer."
 You can also search for words within other words. For example, the function
o =SEARCH("base","database")

KLESNC Rajajinagar
 ISNUMBER Function:
 The function checks if a cell in Excel contains a number or not. It will return
TRUE if the value is a number and if not, a FALSE value.
 For example, if the given value is a text, date, or time, it will return FALSE.
o Syntax: =ISNUMBER (value)
 The Excel ISNUMBER function uses the following arguments:
o Value (required argument) – This is the expression or value that
needs to be tested. It is generally provided as a cell address.
o The ISNUMBER Excel function will return a logical value, which
is TRUE or FALSE.

Example 1: Let’s first understand how the function behaves using the following set of data:

=ISNUMBER (A2)

SUBSTITUTE Function:
 Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to
replace specific text in a text string; use REPLACE when you want to replace any text
that occurs in a specific location in a text string.
Syntax :
=SUBSTITUTE(text, old_text, new_text, [instance_num])
The SUBSTITUTE function syntax has the following arguments:
Text Required. The text or the reference to a cell containing text for which
you want to substitute characters.
Old_text Required. The text you want to replace.

KLESNC Rajajinagar
 New_text Required. The text you want to replace old_text with.
Instance_num Optional. Specifies which occurrence of old_text you want
to replace with new_text. If you specify instance_num, only that instance of
old_text is replaced. Otherwise, every occurrence of old_text in text is
changed to new_text.
 Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet.
o Sales Data
o Quarter 1, 2008
o Quarter 1, 2011

FORMULA DISCRIPTION RESULT

=SUBSTITUTE(D2, "Sales", "Cost") Substitutes Cost for Sales (Cost Data) Cost Data

=SUBSTITUTE(D3, "1", "2", 1) Substitutes first instance of "1" with "2" (Quarter 2, 2008) Quarter 2, 2008

=SUBSTITUTE(D4, "1", "2", 3) Substitutes third instance of "1" with "2" (Quarter 1, 2012) Quarter 1, 2012

KLESNC Rajajinagar
 Lab Program -5
TODAY, NOW, YEAR, MONTH, NETWORKDAYS, EOMONTH

Today Function:

 The TODAY function returns the serial number of the current date in Excel. The TODAY
function updates automatically whenever the worksheet is opened, or when the formulas are
manually recalculated.
Syntax
 The TODAY function has no arguments.
 The format is: = TODAY()
NOW Function:
 The NOW function in Excel returns the serial number of the current date and time. The
NOW function is considered ‘volatile’, meaning that it updates automatically whenever the
worksheet is opened, or when the formulas are manually recalculated.
 Syntax
 The NOW function has no arguments.
 The format is: =NOW()
 And ADD days to current date by using =NOW()+7
YEAR Funmction:
 The YEAR function returns the year corresponding to a date. This can be useful for
isolating the year element of a date.
 Syntax
 The syntax of the YEAR function is: =YEAR(Serial_number)
 Serial_number is the Excel-assigned number of the date you want to query. The serial
number is usually obtained as the result of another Excel function, such as TODAY, DATE,
DATEVALUE, etc.
DATE FORMULA RESULT
12-Jan-98 =YEAR(A2) 1998
12/09/23 =year(A3) 2023
20,Jan,2005 =year(DATEVALUE(A4)) 2005
03/12/23 =year(today()) 2023
2005,6,12 =year(date(2005,6,12)) 2005

KLESNC Rajajinagar
 MONTH Function:
 The MONTH function returns the nth month of the year ranging from 1 to 12. This can be
useful for isolating the month element of a date. Or Returns the month of the year a specific
date falls in, in numeric format.

Syntax
The syntax of the MONTH function is: =MONTH (Serial_number)
 Serial_number is the Excel-assigned number of the date you want to query. The serial number is
usually obtained as the result of another Excel function, such as TODAY, DATE, DATEVALUE, etc.

DATE FORMULA RESULT

12-Jan-98 =MONTH(A2) 1
12/09/23 =MONTH(A3) 12
20,Jan,2005 =MONTH(DATEVALUE(A4)) 1
03/12/23 =MONTH(today()) 12
2005,6,12 =MONTH(date(2005,6,12)) 6

Note : Month And Year do in google sheet:

 NETWORKDAYS Function :
 NETWORKDAYS is used to determine the number of working days between two dates.
The formula calculates the days in an inclusive manner, meaning that the start and end days
are included in the count, and Saturdays and Sundays are automatically excluded from the
calculation. Additional or ad hoc days may also be excluded using an optional argument.

The syntax of the NETWORKDAYS function is:

o NETWORKDAYS (start_date, end_date, [holidays])

o Start_date is a date that represents the start date.

o End_date is a date that represents the end date.
o Holidays is an optional argument. It can refer to a range of dates to be excluded from the
working days calculation. Alternatively, holidays may be explicitly listed within the
formula using an array of date serial numbers.
The basic application of NETWORKDAYS is shown below:
1. =NETWORKDAYS (13-1-2023;31-1-2023;A8:A10) # no of working days b/w
13/01/23 to 31/1/23 and 15/01/23 and 26/01/23 are holidays as a non working days.

KLESNC Rajajinagar
 2. =NETWORKDAYS(D9;E9) # no of working days b/w 1/12/23 to 31/12/23
3. =NETWORKDAYS(D9;E9;A13) # no of working days b/w 1/12/23 to 31/12/23 and
25/12/23 is holiday as Non working day.

EOMONTH FUNCTION:
 The EOMONTH function determines the last day of an earlier or later month than the
month being referenced.
 The EOMONTH function may be used to determine maturity, expiry, or due dates when we
want to force these to fall on the last day of the month.
 This function returns the serial number of the calculated date, which can then be formatted
using the date format of choice.

Syntax

o EOMONTH(start_date,months)
o Start_date will be used as the reference date.
o Months is the number of months to be calculated before or after the start_date.
 =EOMONTH(A13;36)
o In the above example, the investment is set to mature on the last date of the month,
three years from the investment date.
 =EOMONTH(A14;0.9)
o If a number other than an integer is entered in months, EOMONTH truncates the
decimal and uses the whole number value only (see below).
 =EOMONTH(A18;-1)
o Months can also be entered as a negative value, which will result in subtracting that
number of months from the start date, as shown below.

KLESNC Rajajinagar
KLESNC Rajajinagar
 LAB PROGRAM 6: OFFSET, CHOOSE, LET, MAX, SORT, SORTBY, RANK
OFFSET Function: The OFFSET function in Excel returns a cell or range of cells that is a given
number of rows and columns from a given cell or range.
The syntax of the OFFSET function is as follows:
OFFSET (reference, rows, cols, [height], [width])
The first 3 arguments are required and the last 2 are optional.
Required arguments:
Reference - a cell or a range of adjacent cells from which you base the offset. You can think of it
as the starting point.
Rows - The number of rows to move from the starting point, up or down. If rows is a positive
number, the formula moves below the starting reference, in case of a negative number it goes
above the starting reference.
Cols - The number of columns you want the formula to move from the starting point. As well as
rows, cols can be positive (to the right of the starting reference) or negative (to the left of the
starting reference).
Optional arguments:
Height - the number of rows to return.
Width - the number of columns to return.
Both the height and width arguments must always be positive numbers. If either is omitted, it
defaults to the height or width of reference.
OFFSET formula example:

=OFFSET(A1,3,1)

KLESNC Rajajinagar
 1. =offset (A1,4,1)
2. =offset (A1,E2,1)
3. =offset (A9,-1,1)
4. =offset (A12,3,1,1,3)
5. =sum(offset(A12,3,1,1,3))

Choose function:
The Excel CHOOSE function returns a value from a list using a given position or index.
Syntax
=CHOOSE (index_num,value1,[value2],...)
index_num - The value to choose. A number between 1 and 254.
value1 - The first value from which to choose.
value2 - [optional] The second value from which to choose.
Choose formula example:
=choose(if(A2=5,1,if(A2=4,2,if(A2=3,3,if(A2=2,4,5)))),"Excellent","GOOD","Average","Poor","Terrible")

KLESNC Rajajinagar
 LET FUNCTION:
The Excel LET function allows you to assign names to calculation results and define variables
inside a formula, so that the formula looks clearer and works faster.
Syntax
LET has the following syntax and arguments:
LET (name1, name_value1, [name2], [name_value2], …, calculation)
Where:
Name1 (required) - the first name to assign. It must begin with a letter.
Name_value1 (required) - the value or calculation assigned to name1.
Name2 / name_value2 (optional) - the second name and value.
Calculation (required) - a calculation that uses assigned names and values.
The function can process up to 126 name/value pairs.
LET Function Formula:
=let(x,B2,y,B3,x*y) or =LET(x, 2, y, 5, x*y)

MAX Function:
 The MAX function is a premade function in Excel, which finds the
highest number in a range. The function ignores cells with text. It will
only work for cells with numbers.
Note: There is another function called MIN, which finds the lowest value in a
range, the opposite of MAX.
 How to use the =MAX function:
 Type =MAX
 Double click the MAX command
 Select a range (C2:C7)
 Hit enter
Formula : = Max(C2:C7)

KLESNC Rajajinagar
 Excel Sorting:
Ranges can be sorted using the Sort Ascending and Sort Descending commands.
Sort Ascending: from smallest to largest.
Sort Descending: from largest to smallest.
The sort commands work for text too, using A-Z order.
Note: To sort a range that has more than one column, the whole range has to be selected.
Sorting just one can breaks the relationship between columns.

Sort in the range A2:D22 by their Name, ascending from smallest to largest (A-Z).

1. Select A2:D22
2. Open the Sort & Filter menu
3. Click Sort Ascending

Note: A1 is not included as it is the header for the column. This is the row that is dedicated to
the filter. Including it will blend it with the rest.

=sort (A2:D22)

KLESNC Rajajinagar
 Sortby Function :
The SORTBY function in Excel is designed to sort one range or array based on the values in
another range or array. Sorting can be done by one or multiple columns.
SYNTAX:
SORTBY (array, by_array1, [sort_order1], [by_array2, sort_order2],…)
Array (required) - the range of cells or array of values to be sorted.
By_array1 (required) - the range or array to sort by.
Sort_order1 (optional) - the sorting order:
1 or omitted (default) - ascending
-1 – descending
Important note! Currently the SORTBY function is only available with Microsoft 365
subscriptions and Excel 2021. In Excel 2019, Excel 2016 and earlier versions the SORTBY
function is not available.
Formula:
=SORTBY (A2:B10, B2:B10, -1)

 Array is A2:A10 - since you do not wish the Value column to be displayed in the results, you
leave it out of the array.
 By_array1 is B2:B10 - sort by Value.
 Sort_order1 is -1 - descending, i.e. from highest to lowest.

Rank Function:

Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values
in a list. (If you were to sort the list, the rank of the number would be its position.)

KLESNC Rajajinagar
 Syntax : RANK(number, ref, [order])

The RANK function syntax has the following arguments:

 Number Required. The number whose rank you want to find.

 Ref Required. An array of, or a reference to, a list of numbers. Nonnumeric values in ref are
ignored.
 Order Optional. A number specifying how to rank number.

If order is 0 (zero) or omitted, Microsoft Excel ranks number as if ref were a list sorted in descending
order. If order is any nonzero value, Microsoft Excel ranks number as if ref were a list sorted in
ascending order.

Formula:

=Rank(A2,$A$2:$A$8,1)

=Rank(A2,$A$2:$A$8,0)

Data
7
3.5
3.5
1
2
Formula Description (Result) Result
=RANK(A3,A2:A6,1) Rank of 3.5 in the list above (3) 3
=RANK(A2,A2:A6,1) Rank of 7 in the list above (5) 5

KLESNC Rajajinagar
 Lab Program 7

FILTER, FRQUENCY, SEQUENCE, RANDARRAY, IFERROR

Filter function: The FILTER function allows you to filter a range of data based
on criteria you define.

The FILTER function filters an array based on a Boolean (True/False)

array.
=FILTER(array,include,[if_empty])

Argument Description

array The array, or range to filter

Required

include A Boolean array whose height or width is the same as the array

Required

[if_empty] The value to return if all values in the included array are empty (filter returns nothing)

Optional

Step1: In home tab click data in data dropdown select data validation.

Step2: Now data validation rules window will be open, once it opened add the rule
and Click Done.

KLESNC Rajajinagar
KLESNC Rajajinagar
 Step 3: now use the filter function to filter the given data by using data validation.

Formula:

=filter (A2:D22,C2:C22=E1)
By using above formula it can filter the data by respective departments.

KLESNC Rajajinagar
 FREQUENCY function :

The FREQUENCY function calculates how often values occur within a range of
values, and then returns a vertical array of numbers. For example, use
FREQUENCY to count the number of test scores that fall within ranges of scores.
Because FREQUENCY returns an array, it must be entered as an array formula.

Syntax :
FREQUENCY (data_array, bins_array)
The FREQUENCY function syntax has the following arguments:
 data_array Required. An array of or reference to a set of values for which
you want to count frequencies. If data_array contains no values,
FREQUENCY returns an array of zeros.
 bins_array Required. An array of or reference to intervals into which you
want to group the values in data_array. If bins_array contains no values,
FREQUENCY returns the number of elements in data_array.

KLESNC Rajajinagar
 SEQUENCE Function:

The SEQUENCE function allows you to generate a list of sequential numbers in an

array, such as 1, 2, 3, 4.

In the following example, we created an array that's 4 rows tall by 5 columns wide
with =SEQUENCE (4,5).

=SEQUENCE(rows,[columns],[start],[step])

Argument Description

rows The number of rows to return

Required

[columns] The number of columns to return

KLESNC Rajajinagar
 Argument Description

Optional

[start] The first number in the sequence

Optional

[step] The amount to increment each subsequent value in the array

Optional

Formula: 1. =SEQUENCE(4,5)

2. =SEQUENCE(6,3)

3. =SEQUENCE(2,3,3,2)

KLESNC Rajajinagar
 RANDARRAY function :

The RANDARRAY function returns an array of random numbers. You can specify the number of
rows and columns to fill, minimum and maximum values, and whether to return whole numbers
or decimal values.

Syntax :

=RANDARRAY([rows],[columns],[min],[max],[whole_number])

Argument Description
[rows] The number of rows to be returned

Optional
[columns] The number of columns to be returned

Optional
[min] The minimum number you would like returned

Optional
[max] The maximum number you would like returned

Optional
[whole_number] Return a whole number or a decimal value

Optional  TRUE for a whole number

 FALSE for a decimal number.

The RANDARRAY function takes five arguments, none of which are

required: rows, columns, min, max, and integer. By default, rows, columns,
and max default to 1, while min defaults to zero and integer defaults to FALSE.
Without any arguments, RANDARRAY will return a decimal value between 0 and 1:
Note: In Google sheet randarray () supports only two arguments. But in excel
version 2021 and Microsoft 365 supports 5 arguments.

KLESNC Rajajinagar
 Formula:
RANDARRAY() // returns number like 0.098419132

2. Use rows and columns to control the number of values returned:

=RANDARRAY(5,1) // 5 random values in rows

=RANDARRAY(1,5) // 5 random values in columns

IFERROR function:
You can use the IFERROR function to trap and handle errors in a formula. IFERROR returns a
value you specify if a formula evaluates to an error; otherwise, it returns the result of the
formula.

Syntax :

IFERROR(value, value_if_error)

The IFERROR function syntax has the following arguments:

 value Required. The argument that is checked for an error.

KLESNC Rajajinagar
  value_if_error Required. The value to return if the formula evaluates to an
error. The following error types are evaluated: #N/A, #VALUE!, #REF!,
#DIV/0!, #NUM!, #NAME?, or #NULL!.

Remarks :

 If value or value_if_error is an empty cell, IFERROR treats it as an empty string value ("").
 If value is an array formula, IFERROR returns an array of results for each cell in the range
specified in value. See the second example below.

Quota Units Sold

210 35
55 0
23
Formula Description Result
=IFERROR(A2/B2, "Error Checks for an error in the formula in the first argument 6
in calculation") (divide 210 by 35), finds no error, and then returns the
results of the formula
=IFERROR(A3/B3, "Error Checks for an error in the formula in the first argument Error in
in calculation") (divide 55 by 0), finds a division by 0 error, and then calculation
returns value_if_error
=IFERROR(A4/B4, "Error Checks for an error in the formula in the first argument 0
in calculation") (divide "" by 23), finds no error, and then returns the results
of the formula.

Formula :

=IFERROR(A2/B2)
=IFERROR(A3/B3,"Value in error")
=IFERROR(A4/B4)

KLESNC Rajajinagar
 Example 2
Quota Units Sold Ratio
210 35 6
55 0 Error in
calculation
23 0
Formula Description Result
=C2 Checks for an error in the formula in the first argument in the first element of 6
the array (A2/B2 or divide 210 by 35), finds no error, and then returns the
result of the formula
=C3 Checks for an error in the formula in the first argument in the second element Error in
of the array (A3/B3 or divide 55 by 0), finds a division by 0 error, and then calculation
returns value_if_error
=C4 Checks for an error in the formula in the first argument in the third element of 0
the array (A4/B4 or divide "" by 23), finds no error, and then returns the result
of the formula
Note: If you have a current version of Microsoft 365, then you can input the
formula in the top-left-cell of the output range, then press ENTER to confirm
the formula as a dynamic array formula. Otherwise, the formula must be
entered as a legacy array formula by first selecting the output range, input the
formula in the top-left-cell of the output range, then press
CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning
and end of the formula for you. For more information on array formulas,
see Guidelines and examples of array formulas.

KLESNC Rajajinagar
 Lab Program – 7:
PIVOT TABLES, WHAT-IF ANALYSIS, DATA VALIDATION, SUBTOTALS
WITH RANGES

PIVOT TABLES:

A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets
you see comparisons, patterns, and trends in your data. PivotTables work a little bit
differently depending on what platform you are using to run Excel.

Step 1: Enter the below data in Google Sheet.

KLESNC Rajajinagar
 Step 2: Go to insert tab and Click on Pivot Table then it displays the dialogue box.

Step 3: select the rows and columns and Values

KLESNC Rajajinagar
 WHAT-IF ANALYSIS :

 In what-if analysis, various situations or “scenarios” are contemplated through the

manipulation of variables. Among others, there are two very popular types of what-if
analysis: scenario and sensitivity analysis.
 In sensitivity analysis, the focus is on the effects of changes to one specific variable; this
type of analysis explains how variables (dependent) are affected based on changes
made in another variable (independent).
 In scenario analysis, the focus is on the effects of changes to multiple variables; here,
the different scenarios correspond to these changing variables. For example, a worst-
case scenario could involve higher-than-average costs and minimum sales.

KLESNC Rajajinagar
 How to Use Goal Seek in Google Sheets :

Install the Goal Seek Add-on in Google Sheets

 Follow the steps below to find and install Goal Seek in Google Sheets.
o Open Google Sheets and go to Extension > Add-ons > Get add-ons.

o You will see the Goal Seek sidebar on the left of your spreadsheet, including
“Settings”, “Solve Status”, and “History”.

KLESNC Rajajinagar
 o Click on the cell containing the profit formula, then click on the grid icon beside
“Set Cell” in the sidebar. If you place your cursor over the grid icon, you will see
the text “Capture selected cell”, as in the screenshot below.

o Next, type in the “Target value” for the “Set Cell”. Here, “20000”.

o Click on the cell containing the value you want Goal Seek to change, then click on
the grid icon beside “By Changing Cell” in the sidebar. Here, the selling price for
Scenario 1, cell B14.

o To change the number of iterations, the tolerance, and/or the time limit, click
on “Options”. Here, I will use the default values.

KLESNC Rajajinagar
 o Once everything is ready, click “Solve.” With the default options used above, it
will take a maximum of two minutes to calculate the solution. If Goal Seek
cannot find a solution, try increasing the tolerance or the time limit and number
of iterations.

How to use Data Validation in Google Sheets

 Go to the “Data” tab, click “Data Validation”, and get a dialog box on the right side
of the sheet.
 Click “+ Add rule”.
 Select a range where you want to make Data Validation effective.
 Choose one of the criteria and input values according to the criterion.
 Check the advanced option of “Show help text for a selected cell”, if you want to
leave a hint to an editor who makes an invalid data input to make it valid.
 Choose one of two options* against invalid data input.
 Click “Done” to make the setting effective.

KLESNC Rajajinagar
 SUBTOTALS WITH RANGES

Explanation: You can automatically calculate subtotals and grand totals in a

list for a column by using the Subtotal command.

Steps 1) Sort the data in descending order using sort option in Data tab. Sort
on region data.

2) Click on subtotal option from data tab→ select region in at each value
change field

3) Use function → Select sum

4) Add subtotal to→ Select Sum

5) You will get subtotal region wise

KLESNC Rajajinagar
KLESNC Rajajinagar
 Part-B: DA Programs:

1. Probability

Program -1:

# Simple probability

# Probability of rolling a 4 on a six-sided die

total_outcomes = 6

favorable_outcomes = 1 # Rolling a 4

probability_4 = favorable_outcomes / total_outcomes

print("Probability of rolling a 4:”, probability_4)

output:

Probability of rolling a 4: 0.16666666666666666

1. A) Calculating the simple probabilities

import random

num_trials = int(input("enter_no_of_trials"))

rolls_per_trial =int(input("for Each trail how many rolls"))

roll_up_value=int(input(" Enter rollup value"))

poss_outcomes=0

for i in range(num_trials):

for j in range(rolls_per_trial):

result = random.randint(1,6)

print(result)

if result == roll_up_value:

KLESNC Rajajinagar
 poss_outcomes += 1

print("--------")

total_outcomes = num_trials * rolls_per_trial

print(f"Number of times 6 appeared in {num_trials} trials of {rolls_per_trial} rolls each:

{poss_outcomes}")

print("probability=",poss_outcomes / total_outcomes)

output:

KLESNC Rajajinagar
 1. b) Applications of Probability distributions to real life problems

# Binomial Distribution - Decision Making example

# estimating probability of success or failure in fixed number of trials

from scipy.stats import binom

n = 10 # Number of trials

p = 0.5 # Probability of success

k_success = 2# Number of successes

prob_2_success = binom.pmf(k_success, n, p)

print(f"Probability of 2 successes out of 10 trials: {prob_2_success}")

output:

Probability of 2 successes out of 10 trials: 0.04394531250000005

Program 2: Test of significance

import pandas as pd

from scipy import stats

titanic_data = pd.read_csv('train.csv')

# One Sample T-Test: Checking mean age against a hypothetical mean

hypothetical_mean_age = 30

ttest_one_sample = stats.ttest_1samp(titanic_data['Age'].dropna(),

hypothetical_mean_age)

print("One Sample T-Test:")

print("T-statistic:", ttest_one_sample.statistic)

print("p-value:", ttest_one_sample.pvalue)

KLESNC Rajajinagar
 # Two Independent Samples T-Test: Comparing ages of male and female passengers

male_ages = titanic_data[titanic_data['Sex'] == 'male']['Age'].dropna()

female_ages = titanic_data[titanic_data['Sex'] == 'female']['Age'].dropna()

ttest_two_ind_samples = stats.ttest_ind(male_ages, female_ages)

print("\nTwo Independent Samples T-Test:")

print("T-statistic:", ttest_two_ind_samples.statistic)

print("p-value:", ttest_two_ind_samples.pvalue)

# Paired T-Test: Comparing fares before and after

before_fares = titanic_data['Fare'].dropna()

after_fares = before_fares * 1.2 # Assuming a 20% increase in fares

ttest_paired = stats.ttest_rel(before_fares, after_fares)

print("\nPaired T-Test:")

print("T-statistic:", ttest_paired.statistic)

print("p-value:", ttest_paired.pvalue)

# ANOVA Test: Impact of passenger class on fares

anova_result = stats.f_oneway(titanic_data[titanic_data['Pclass'] == 1]['Fare'].dropna(),

titanic_data[titanic_data['Pclass'] == 2]['Fare'].dropna(),

titanic_data[titanic_data['Pclass'] == 3]['Fare'].dropna())

print("\nANOVA Test Result:")

print("F-statistic:", anova_result.statistic)

print("p-value:", anova_result.pvalue)

# Chi-Square Test: Relationship between survival status and passenger class

KLESNC Rajajinagar
 chi2_table = pd.crosstab(titanic_data['Survived'], titanic_data['Pclass'])

chi2_result = stats.chi2_contingency(chi2_table)

print("\nChi-Square Test Result:")

print("Chi-Square statistic:", chi2_result[0])

print("p-value:", chi2_result[1])

output:

One Sample T-Test:

T-statistic: -0.5534583115970276

p-value: 0.5801231230388639

Two Independent Samples T-Test:

T-statistic: 2.499206354920835

p-value: 0.012671296797013709

Paired T-Test:

T-statistic: -19.344277455944212

p-value: 7.255925461999272e-70

ANOVA Test Result:

F-statistic: 242.34415651744814

p-value: 1.0313763209141171e-84

Chi-Square Test Result:

Chi-Square statistic: 102.88898875696056

p-value: 4.549251711298793e-23

KLESNC Rajajinagar
 program 3: Correlation and Regression analysis

a. Scattered diagram, calculating of correlation coefficient

b. Linear regression: fitting, testing model adequacy and prediction

c. Fitting of logistic regression.

Source Code:

import numpy as np

import pandas as pd

import matplotlib.pyplot as plt

from sklearn.linear_model import LinearRegression

from sklearn.model_selection import train_test_split

from sklearn.metrics import mean_squared_error, r2_score

from sklearn.linear_model import LogisticRegression

from sklearn.datasets import load_iris

# Generating sample data

np.random.seed(42)

X = np.random.rand(100, 1) * 10

y = 2 * X.squeeze() + np.random.randn(100) * 2

# Scatter plot and correlation coefficient

plt.figure(figsize=(8, 4))

plt.scatter(X, y)

plt.title('Scatter Plot')

plt.xlabel('X')

KLESNC Rajajinagar
 plt.ylabel('Y')

plt.grid(True)

correlation_coefficient = np.corrcoef(X.squeeze(), y)[0, 1]

print(f"Correlation Coefficient: {correlation_coefficient}")

# Linear regression fitting

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2,

random_state=42)

lin_reg = LinearRegression()

lin_reg.fit(X_train, y_train)

# Testing model adequacy and prediction

y_pred = lin_reg.predict(X_test)

mse = mean_squared_error(y_test, y_pred)

r2 = r2_score(y_test, y_pred)

print(f"Mean Squared Error: {mse}")

print(f"R-squared Score: {r2}")

plt.figure(figsize=(8, 4))

plt.scatter(X_test, y_test, color='black')

plt.plot(X_test, y_pred, color='blue', linewidth=3)

plt.title('Linear Regression Prediction')

plt.xlabel('X')

plt.ylabel('Y')

plt.grid(True)

KLESNC Rajajinagar
 # Fitting logistic regression (using Iris dataset as an example)

iris = load_iris()

X_iris = iris.data[:, :2] # Using only the first two features for simplicity

y_iris = iris.target

log_reg = LogisticRegression()

log_reg.fit(X_iris, y_iris)

# Generating a meshgrid for decision boundary visualization

x_min, x_max = X_iris[:, 0].min() - 1, X_iris[:, 0].max() + 1

y_min, y_max = X_iris[:, 1].min() - 1, X_iris[:, 1].max() + 1

xx, yy = np.meshgrid(np.arange(x_min, x_max, 0.1), np.arange(y_min, y_max, 0.1))

Z = log_reg.predict(np.c_[xx.ravel(), yy.ravel()])

Z = Z.reshape(xx.shape)

plt.figure(figsize=(8, 6))

plt.contourf(xx, yy, Z, alpha=0.4)

plt.scatter(X_iris[:, 0], X_iris[:, 1], c=y_iris, s=20, edgecolor='k')

plt.title('Logistic Regression (Iris dataset)')

plt.xlabel('Sepal Length')

plt.ylabel('Sepal Width')

plt.grid(True)

plt.show()

output:

Correlation Coefficient: 0.9529657473628446

KLESNC Rajajinagar
 Mean Squared Error: 2.6147980548680088

R-squared Score: 0.9287298556395622

KLESNC Rajajinagar
KLESNC Rajajinagar
 Part- C: Power BI

1. Introduc�on to Power BI- Get Started with Power BI - Sign up for Power BI - Overview:
Power BI data sources - Connect to a SaaS solu�on - Upload a local CSV file - Connect to
Excel data that can be refreshed - Create a Report with Visualiza�ons

1.Introduc�on to Power BI

Power BI is a powerful business analy�cs tool developed by Microso� that enables users to visualize
and analyze data effec�vely, helping organiza�ons make data-driven decisions.

Power BI Desktop:

This is a free applica�on for Windows that allows users to create reports and data visualiza�ons. It
includes tools for data modelling, transforming data using Power Query, crea�ng rela�onships, and
designing interac�ve reports and dashboards.

2.Get Started with Power BI

• Go to the official Power BI Desktop download page (htps://powerbi.microso�.com/en-

us/desktop/).
• Download Power BI by clicking on the buton indica�ng "Download free".
• Once the download is complete, follow the on-screen instruc�ons provided by the installa�on
wizard to install Power BI Desktop on your computer.
• A�er successful installa�on, you can launch Power BI Desktop.

3. Sign up for Power BI:

• Go to the Power BI website (powerbi.microso�.com).

• Sign up for a Power BI account using your work or personal email.

KLESNC Rajajinagar
 4. Overview : Power BI Data Sources:

Power BI supports various data sources, including but not limited to:

• SaaS Solu�ons: Connect to cloud-based services like Microso� Dynamics 365, Google Analy�cs,
Salesforce, etc.

• Local Files: Upload files from your computer, such as CSV, Excel, JSON, etc.

• Databases: Connect to databases like SQL Server, MySQL, PostgreSQL, etc.

• Web Sources: Extract data from web pages or APIs.

• Azure Services: U�lize Azure data services like Azure SQL Database, Azure Blob Storage, etc.

5. Connect to a SaaS Solu�on:

• Open Power BI Desktop or sign in to the Power BI service online.

• Click on "Get Data" from the Home tab.

• Choose the type of SaaS solu�on you want to connect to (e.g., Salesforce, Google Analy�cs).

• Follow the prompts to sign in and connect to your SaaS solu�on. You'll likely need to provide
creden�als or permissions to access the data.

6.Upload a local CSV file:

• Click on "Get Data" from the Home tab.

• Select "Text/CSV" from the available data sources.

• Browse to locate and select the CSV file from your computer.

• Power BI will import the data from the CSV file.

KLESNC Rajajinagar
 7. Connect to Excel dthat can be refreshed:

• Click on "Get Data" from the Home tab.

• Choose "Excel" as the data source.

• Browse to select the Excel file or specify the file path.

• Power BI will import data from the Excel file.

• To refresh the data, go to "Home" > "Queries" > "Refresh."

KLESNC Rajajinagar
 8. Create a Report with Visualiza�ons:

• Select the appropriate type of visualiza�on (e.g., bar chart, line chart, pie chart) from the
Visualiza�ons pane on the right-hand side.

• Drag and drop fields from your datasets onto the report canvas to create visualiza�ons such as
charts, graphs, tables, etc.

• Customize the visualiza�ons by forma�ng, sor�ng, and adding addi�onal elements like slicers,
text boxes, etc.

• Repeat the process to create mul�ple visualiza�ons for various insights.

• Save your report.

2. Using visualiza�ons - Create a new report - Create and arrange visualiza�ons - Format a
visualiza�on - Use text, map, and gauge visualiza�ons and save a report - Use a slicer to
filter visualiza�ons - Sort, copy, and paste visualiza�ons

1. Create a New Report:

• Open Microso� Power BI Desktop.

• Click on "File" > "New"
• Import your data into Power BI by connec�ng to a data source.

KLESNC Rajajinagar
 2. Create and Arrange Visualiza�ons:

• Select the appropriate type of visualiza�on (e.g., bar chart, line chart, pie chart) from the
Visualiza�ons pane on the right-hand side.
• Drag and drop fields from your dataset onto the visual to create different visualiza�ons like
bar charts, line graphs, etc.
• Arrange the visualiza�ons by clicking and dragging them to desired loca�ons on the canvas.

3. Format a Visualiza�on:

• Click on a visualiza�on to select it.

• Use the "Format" or "Visualiza�ons" pane to modify the appearance, colors, labels, and
other se�ngs of the selected visualiza�on.

KLESNC Rajajinagar
 4.Use text, map, and gauge visualiza�ons and save a report:

• Include a text visualiza�on by selec�ng "Card" from the "Visualiza�ons" pane and drag and drop
fields from your dataset onto the visual
• Add a map visualiza�on using the map visual from the visualiza�on pane and dragging a
geographical field onto the visual.
• Include a gauge visualiza�on by selec�ng "Gauge" from the "Visualiza�ons" pane and configuring
it with appropriate data.
• Click on "File" > "Save" to save the report in your desired loca�on and format.

6. Use a Slicer to Filter Visualiza�ons:

KLESNC Rajajinagar
 • Select “Slicer (new)” from the Visualiza�on pane and drag a field you want to use as a filter into
the slicer visual.
• Use the slicer to interac�vely filter the other visualiza�ons on the report by selec�ng specific
values.

7. Sort, Copy, and Paste Visualiza�ons:

• To sort visualiza�ons, select a visualiza�on and use the sort op�ons available in the "Format" or
"Visualiza�ons" pane.
• To copy and paste visualiza�ons, select the visualiza�on, right-click, and choose "Copy." Then,
right-click on the canvas and select "Paste."
3. Modify and Print a Report - Rename and delete report pages - Add a filter to a page or
report Set visualiza�on interac�ons - Send a report to PowerPoint

1. Modify and Print a Report:

To modify a report in Power BI, you can edit exis�ng visuals, add new visuals, change forma�ng, and
adjust data connec�ons.

• Open your report in Power BI Desktop or Power BI Service.

• Make the necessary modifica�ons to the visuals by edi�ng their proper�es, forma�ng, or data.

• To print the report, click on "File" and then select "Print" to configure the prin�ng se�ngs and
print the report.

2. Rename and Delete Report Pages:

• In Power BI Desktop or Service, navigate to the report page.

• To rename a report page, right-click on the page name in the Pages pane, select "Rename," and
enter the new name.

• To delete a report page, right-click on the page name in the Pages pane and select "Delete."

KLESNC Rajajinagar
 3. Add a Filter to a Page or Report:

• In Power BI Desktop, select the page or visual you want to apply the filter to.

• Go to the "Filters" pane and click on "Add a filter."

• Choose the field you want to use as a filter and configure its se�ngs (e.g., filter type, values).

4. Set Visualiza�on Interac�ons:

• Click on the visual you want to set interac�ons for.

• Go to the "Format" pane (or right-click and select "Format") and navigate to the "Edit
interac�ons" op�on.

KLESNC Rajajinagar
 • Adjust the interac�ons between visuals by selec�ng the interac�on type (e.g., highligh�ng,
filtering) for each visual.

5. Send a Report to PowerPoint:

• In Power BI Service, open the report you want to export.

• Click on "File" and select "Export" > "PowerPoint (.pptx)."

• Configure the export se�ngs and click "Export" to generate the PowerPoint file with the report
visuals.

KLESNC Rajajinagar