Advance Excel

MS-EXCEL is a part of Microsoft Office suite software. It is an electronic spreadsheet with

numerous rows and columns, used for organizing data, graphically represent data(s), and
performing different calculations. It consists of 1048576 rows and 16384 columns; a row
and column together make a cell.

BENEFITS OF MICROSOFT EXCEL

✓ Best way to store data
✓ You can perform calculations
✓ All the tools for data analysis
✓ Easy to data visualizations with charts
✓ You can print reports easily
✓ So many free templates to use
✓ You can code to automate
✓ Transform and clean data
✓ Store data with millions of rows
TERMS USED IN EXCEL
✓ WORKBOOK: A File that contains 1 or more worksheets
✓ WORKSHEET: Worksheet is a single page spreadsheet containing information
✓ ROW: A row is a horizontal alignment of data
✓ COLUMN: A column is vertical alignment of data
✓ CELL: Combination of row and column

MATHEMATICAL FUNCTIONS
Mathematical Formulas in Excel are used to perform various arithmetic operations like sum,
average, count, max, min etc. Here is a list of most frequently used mathematical formulas
in excel.
1. SUM (): This function is used to adds all the values within a cell range.
➢ Syntax: sum(cell address : cell address)
2. SUMIF ():The SUMIF function is a premade function in Excel, which calculates the
sum of values in a range based on a true or false condition.
➢ Syntax: SUMIF(range, criteria)
3. SUMIFS (): 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.
➢ Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2] ...).
4. COUNT (): The COUNT function is a premade function in Excel, which counts cells
with numbers in a range.
➢ Syntax: =count(range)

By: Archana Mishra

5. COUNTA (): The COUNTA function is a premade function in Excel, which counts all
cells in a range that has values, both numbers and letters.
➢ Syntax: =counta(range)
6. COUNTBLANK: The COUNTBLANK function is a premade function in Excel, which
counts blank cells in a range.
➢ Syntax: =countblank(range).
7. COUNTIF (): The COUNTIF function is a premade function in Excel, which counts cells
as specified.
➢ Syntax: =countif(range, criteria).
8. 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], ...)
9. AVERAGE (): The AVERAGE function is a premade function in Excel, which calculates
the average (arithmetic mean)
➢ Syntax:=AVERAGE(range).
10. AVERAGE IF(): The AVERAGEIF function is a premade function in Excel, which
calculates the average of a range based on a true or false condition.
➢ Syntax: =AVERAGEIF(range, criteria, [average_range])
11. AVERAGE IFS(): 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.
➢ Syntax: =AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
12. SUBTOTAL(): Subtotals are calculated with a summary function, such as Sum or
Average, by using the SUBTOTAL function. You can display more than one type of
summary function for each column.
➢ Syntax: =SUBTOTAL(function_num,ref1…)
13. AGGREGATE(): The AGGREGATE function in Excel allows applying different aggregate
functions like AVERAGE, SUM, PRODUCT, COUNT, COUNTA, MAX, or MIN to a list of
data, with an option to ignore hidden rows and error values.
➢ Syntax: =AGGREGATE(function_num,option,array,ref1…)
14. RAND(): RAND returns an evenly distributed random real number greater than or
equal to 0 and less than 1. A new random real number is returned every time the
worksheet is calculated.
➢ Syntax:=RAND().
15. RANDBETWEEN(): Returns a random integer number between the numbers you
specify. A new random integer number is returned every time the worksheet is
calculated.
➢ Syntax: =RANDBETWEEN(BOTTOM,TOP).
16. ROUNDUP(): Rounds a number up, away from 0 (zero).
➢ Syntax:=ROUNDUP(number, num_digits)
17. ROUNDDOWN(): Rounds a number DOWN, toward 0 (zero).
➢ Syntax:=ROUNDDOWN(number, num_digits)
18. ROUND(): The ROUND function rounds a number to a specified number of digits.

By: Archana Mishra

 ➢ Syntax: =ROUND(number, num_digits)
19. SUMPRODUCT(): The SUMPRODUCT function returns the sum of the products of
corresponding ranges or arrays. The default operation is multiplication.
➢ Syntax: =SUMPRODUCT(array1, [array2], [array3], ...)
20. POWER: This function in Excel Returns the result of a number raised to a power
➢ Syntax: =POWER(Number,Power)
21. PRODUCT: This Function in Excel gives the Product of all the given numbers
➢ Syntax: =PRODUCT(Number1,Number2….)
22. QUOTIENT: Returns the integer portion of a division.
➢ Syntax: =QUOTIENT(Numerator,Denominator)
23. ROMAN: Returns the Arabic Numeral to Roman
➢ Syntax =ROMAN(Number,Form)
24. Subtotal(): It returns the subtotal or a range or database.
or
In other words, the subtotal function in excel works only on the active cells of the database
and does not include the hidden rows in the calculation of the database or range.
Syntax: =subtotal (function_num, reference 1, reference 2…)
Where function number includes the following:
Function number (includes hidden rows) Function number (ignores hidden rows) Function
(works on filter) (works on Filter and hide rows)
1 101 Average
2 102 Count
3 103 Counta
4 104 Max
5 105 Min
6 106 Product
7 107 Stdev
8 108 Stdevp
9 109 Sum
10 110 Var
11 111 Varp
Example:

LOGICAL FUNCTIONS
1. IF(): - If function in excel check whether the condition is met or not, return true if
value is met and false if the value is not met.
a. Syntax: =if( logical test, value_if_true, value_if_false)
2. AND (): - And function in Excel checks whether all the arguments are True.

By: Archana Mishra

 a. Syntax: =and(logical1,logical2…)
3. OR(): - OR function in excel checks whether the arguments are true or not. OR
function only shows the false value when all the given conditions didn’t complete.
a. Syntax: = Or(logical1,logical2…)
4. IF ERROR (): If an Expression is error, we can change the value with another.
a. Syntax: iferror(Value,value_if_error)

LOOKUP FUNCTIONS
1. MATCH: Match function find the position of any lookup value from the given array or
list.
a. Syntax: =MATCH(lookup_value,Lookup_array,Match_Type)
i. Where Lookup value: the value to look for in the list
ii. Lookup array : list of the particular word/lookup value. We can only
select either 1 row or 1 column.
iii. Match type : -1 for less than value, 0 for exact match and 1 for greater
match

2. VLOOKUP: Vlookup is an excel function and retrieve data from a specific column in
table . The “v” stands for “vertical”. Lookup values must appear in the first column of
the table, with lookup column to the right.
a. Purpose: - Lookup a value in a table by matching on the first column. It works
on left to right direction.
b. Syntax: - =vlookup(value, table_array, col_index, [range_lookup])
c. Arguments: -
i. Lookup_Value - the value to look for in the first column of a table.
ii. Table - the table from which to retrieve a value.
iii. Col_index - the column in the table from which to retrieve a value.
iv. Range_lookup - [optional] true = approximate match (default)/1. False
= exact match/0 .
Various Situations on Vlookup Functions:
1) Simple Vlookup

By: Archana Mishra

2) Double Vlookup :Double Vlookup is a formula used when you have at least two or
more tables and the data have only one common field.
a. SYNTAX: = VLOOKUP (VLOOKUP (VALUE, TABLE_ARRAY, COL_INDEX,
[RANGE_LOOKUP], TABLE_ARRAY) COl_INDEX,[RANGE_LOOKUP])

3) Vlookup with multiple criteria : Situation can be used when we have 1 lookup value
in 2 or more different cells and we need to combine than according to the situation.
# Rows and Columns function can be used to drag the formula without changing the
column index number
• Columns : Columns function count the number of selected columns.
Syntax: =Columns(array)
• Rows : Rows functions count the number of selected rows.
Syntax: =Rows(array)

→ Syntax: =VLOOKUP(first_name &”

“&last_name,Table_Array,Rows(Required_rows),0)
4) Vlookup with Condition (using If Function): This situation can be used when we need
to check any condition and then it’s need to find its value.
For example: we have a table of product name and available qty. We need check
whether the product is available or not ,If Available than we need the qty and if it’s
not available than we need out of stock

Syntax: IF(Vlookup(lookup_value,table_array,col_no,type_of match)>0, vlookup

(lookup_value, table_array, col_no, type_of match), ”Out of Stock”)
5) Vlookup with match Function (Dynamic lookup): when we need to find the all values
at 1 single cell according to its heading.

By: Archana Mishra

 For example:

Syntax: =Vlookup(lookup_value, Table_array, Match( lookup_value, lookup_array,

match_type), type_of_match
→ Vlookup is used to find the value
→ Match is helpful to change the column index number
6) Vlookup with If Function (Different Data on All Sheets ): this situation can help when
we have a very large data on different sheets.
For example : on sheet 1 we have data from product id 1 to 10, on sheet 2 we have
data from 11 to 20 and the other data is on sheet 3. We are required to find the all
data based on product Id on sheet 4.

Syntax: =Iferror(Vlookup(Lookup_Value , If(Lookup_Value

<=10,Table1,If(Lookup_value <=20 , Table2,Table3)),
Columns(No._of_required_columns),0),”Not Found”)
→ Where: Iferror : It is used to hide the error in any function with the text “Not
Found”
→ Vlookup : used to find the value of product name, Qty, Rate and Price
→ If : to define the Place from where we will get the table array, if the Id is less
than 10 table 1 will be used, if Id is less than 20 than Table2 otherwise table 3.
→ Columns : to drag the column index number.
7) Reverse Vlookup (Vlookup with Choose Function): This situation is helpful when the
data is not in a chronological manner and we need to set the table array according to
the situation.

Syntax:
=Vlookup(Lookup_value,Choose({1,2,3,4},Value1,Value2,Value3,Value4),Columns(No_of_req
uired_columns),Type_of_match)

→ Where Vlookup is used to find the value

→ Choose Function is used to set the table array
o {1,2,3,4} refers to the index values or the number of headings in the
table

By: Archana Mishra

 o Value 1 , value 2 … refers to headings, as on index 1 we need item name
heading than value 1 refers to item name array.
3. HLOOKUP: Hlookup is an excel function and retrieve data from a specific row in table
. The “H” stands for “Horizontal”. Lookup values must appear in the first row of the
table, with lookup Row to the right.
a. Syntax:
=HLOOKUP( VALUE, TABLE, INDEX_NUMBER, [APPROXIMATE_MATCH] )
b. Parameters or arguments: -
i. value:- the value to search for in the first row of the table.
ii. Table : -two or more rows of data that is sorted in ascending order.
iii. Index_number : -the row number in table from which the matching
value must be returned. The first row is 1.
iv. Approximate_match : - optional. Enter false to find an exact match.
Enter true to find an approximate match. If this parameter is omitted,
true is the default.

Note : To convert any vertical table to horizontal we can use the following steps:
➢ Method 1 using Paste Special
o Copy the vertical table
o Put your cursor where you need the horizontal table
o Press Ctrl + Alt + V (For Paste Special ) > Select Transpose and click ok
➢ Method 2 using Transpose Function
o Select the range where you want the horizontal data
o Type =Transpose(array) > select the vertical table you want to transpose
o Press Ctrl + Shift + Enter
4. INDEX : Returns the value or a reference of the cell at the intersection of a particular
row and column, in a given range. We can use index function with a single row and or
a complete array both.
a. Syntax: =Index(Array, Row_number, Column_Number)

Situation for Index function:

By: Archana Mishra

 ➢ Index + Match Function: This situation can be used when we need to find the
values in a dynamic manner where row and column number is not fixed.

→ Syntax: =Index(Array, Match(Lookup_value,Array,Match_lookup),

Match(Lookup_value,Array,Match_lookup))
→ Where Index Function is used to find the value of given product id and
column heading
→ First Match function is used to make the row number dynamic according
to product id
→ Second Match function is used to make dynamic the column heading

DATE AND TIME FUNCTION

1. Date: Enters a date in the cell. Example: DATE(2019,5,1) equals May 1, 2019.
a. Syntax: =DATE(year, month, day)
2. Time: Enters a time in the cell. Uses a 24-hour (military) time system. Example
: TIME(14,30,0) equals 2:30 PM
a. Syntax: =TIME(hour, minute, second).
3. Today: A special version of the DATE function. The DATE function returns the
value of any date; the TODAY function returns the value of the current date.
a. Syntax: =TODAY( )
4. Now: A special version of the TIME function. The TIME function returns the
value of any time; the NOW function returns the value of the current time.
a. Syntax: =NOW( )
5. Year: Returns a value of the year for a specific date. The serial number
argument is a date value (or reference to one). Example: YEAR("3/15/2019")
equals 2019.
a. Syntax: =YEAR(serial_number)
6. Month: Returns a value of the month for a specific date. The serial_number
argument is a date value (or reference to one). Example: MONTH("3/15/2019")
equals 3.
a. Syntax: =MONTH(serial_number)
7. Day: Returns a value of the day for a specific date. The serial_number
argument is a date value (or reference to one). Example: DAY("3/15/2019")
equals 15.

By: Archana Mishra

 a. Syntax: =DAY(serial_number)
8. Edate: (End date) The EDATE is a date and time function in Excel which adds a
given number of months into a date. It provides us with a date in a numerical
format of a date. This function’s arguments take date and integers, date as the
start_date. The number of months is an integer to add to the given start date.
a. Syntax: =EDATE(“10/15/20”,-5) returns 5/15/20.
9. Eomonth: (End of Month) Returns the serial number for the last day of the
month that is the indicated number of months before or after start_date. Use
EOMONTH to calculate maturity dates or due dates that fall on the last day of
the month.
a. Syntax: EOMONTH(start_date, months)
10. NETWORKDAYS: The NETWORKDAYS Function[1] calculates the number of
workdays between two dates in Excel. When using the function, the number of
weekends are automatically excluded. It also allows you to skip specified
holidays and only count business days. It is categorized in Excel as
a Date/Time Function.
a. Syntax: =NETWORKDAYS(start_date, end_date, [holidays])
11. [Link]: The NETWORKDAYS Function[1] calculates the number of
workdays between two dates with custom weekend parameters in Excel. When
using the function, the number of weekends are automatically excluded.
a. Syntax: =[Link](Start_date, End_date, [weekend], [Holidays])
12. WORKDAY: Returns a number that represents a date that is the indicated
number of working days before or after a date (the starting date). Working
days exclude weekends and any dates identified as holidays.
a. Syntax:=WORKDAY(start_date, days, [holidays])
13. [Link]: This function in excel return the serial number of the date before or
after a specified number of workdays with custom weekend parameters.
a. Syntax: [Link](Start _date, days, weekend, holidays)
14. WEEKNUM: Returns the week number of a specific date. For example, the week
containing January 1 is the first week of the year, and is numbered week 1.
a. Syntax: =WEEKNUM(serial_number,[return_type])
15. WEEKDAY: Day of the week, with numbers 1 (Sunday) through 7 (Saturday)
a. Syntax: =WEEKDAY(serial_number,[return_type])
16. HOUR: Returns the hour of a time value. The hour is given as an integer,
ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).
a. Syntax: =HOUR(serial_number)
17. MINUTE: The Excel MINUTE function returns the minute integer, ranging from
0 to 59, from a time serial number.
a. Syntax: =MINUTE(serial_number)

By: Archana Mishra

 18. SECOND: When given the time "10:45:17 AM", the SECOND function will return
17:
a. Syntax: =SECOND("10:45:17 AM")
DATA BASE FUNCTIONS
A database is an organised collection of structured information, or data, typically stored
electronically in a computer system.
List of database functions in MS excel
➢ DAVERAGE
➢ DCOUNT
➢ DCOUNTA
➢ DGET
➢ DMAX
➢ DMIN
➢ DPRODUCT
➢ DSUM

1. DAVERAGE: Averages the values in a column in a list or database that matches the
condition we specify. It finds the average values from the selected data table with given
criteria.
a. Syntax: DAVERAGE(DATABASE,FIELD,CRITERIA)
i. Here: DATABASE is the complete table
ii. FIELD: The title or heading you want to calculate
iii. CRITERIA: Condition which includes the title of that condition and
condition itself.
2. DCOUNT: counts the cells containing numbers in a field or range in any database that
matches the condition you specify.
a. Syntax: =DCOUNT(DATABASE,FIELD,CRITERIA)

3. DCOUNTA: It counts the nonblank cell from the database.

a. Syntax: DCOUNTA(DATABASE,FIELD,CRITERIA)

By: Archana Mishra

4. DGET: It extracts from the database a single record that matches the condition you specify
or in other words it works as a lookup function to find the data from your database with
given condition.
a. Syntax: DGET(DATABASE,FIELD,CRITERIA)

5. DMAX: Find the maximum value from the database as given criteria.
a. Syntax: DMAX(DATABASE,FIELD,CRITERIA)
6. DMIN: Find the Minimum value from the database as given criteria.
a. Syntax: DMIN(DATABASE,FIELD,CRITERIA)
7. DPRODUCT: Find the product value from the database as given criteria.
a. Syntax: DPRODUCT(DATABASE,FIELD,CRITERIA)
8. DSUM: Finds the sum value as per given condition. This function works as an SUM, SUMIF
and SUMIFS function.
a. Syntax: DSUM(DATABASE, FILED, CRITERIA)

TEXT FUNCTIONS
1. CHAR: Returns the character specified by the code number from the character set of
your computer.
a. Syntax: =CHAR(NUMBER)
2. CLEAN: This function in excel remove all the Non Printable character from text.
a. Syntax: CLEAN(TEXT)
3. EXACT: This function in excel checks whether two text strings are exactly the same or
not, and return TRUE or FALSE. This function is case sensitive.
a. Syntax: =EXACT(TEXT1,TEXT2)
4. FIND: This function in excel return the starting position of one text string from
another text String. This function is also case sensitive.
a. Syntax: FIND(FIND_TEXT,WITHIN_TEXT,START_NUMBER)
5. LEFT: It finds the number of characters from the selected cell from left direction.
a. Syntax: =LEFT(TEXT,NUM_CHAR)
b. NUM_CHAR is the total number of characters you want to find.
6. RIGHT: It finds the number of characters from the selected cell from right direction.
a. Syntax: =RIGHT(TEXT,NUM_CHAR)

By: Archana Mishra

 7. MID: Mid function in excel returns the number of characters from the middle of a
text.
a. Syntax: =MID(Text,Starting_Number,Num_Char)
i. Starting number from where you want the middle value.
ii. Num_Char the total number of characters you want
8. LEN: Len Function in excel finds the total number of characters in a text string or a
cell.
a. Syntax: =LEN(Text)
9. LOWER: Lower function in excel converts the text into lowercase.
a. Syntax: =LOWER(Text)
10. UPPER: Upper function in excel converts the text into uppercase.
a. Syntax: =UPPER(Text)
11. PROPER: Proper function in excel converts the text into proper case. The First letter
of the text will be in uppercase and the other in lowercase.
a. Syntax: =Proper(Text)
12. SUBSTITUTE: Replace the old text value with a next text value in a cell or reference.
a. Syntax: =SUBSTITUTE(Text,Old_text,New_text)
13. TRIM: Trim function in excel replace all the extra space from the text or a cell
reference.
a. Syntax: =TRIM(text)
14. CONCATENATE: Concatenate function in excel joints the several text values into 1
text string.
a. Syntax: =CONCATENATE(Text1,Text2….)
15. TEXT: Converts a value of text in a specific number format.
a. Syntax: =TEXT(Value,Format_Text)

FINANCIAL FUNCTIONS
1. PMT: - PMT, one of the financial function, calculates the payment for a loan based on
constant payments and a constant interest rate
a. SYNTAX: - =PMT(RATE, NPER, PV, [FV], [TYPE])
b. The PMT function syntax has the following arguments:
i. Rate: required. The interest rate for the loan.
ii. NPER: required. The total number of payments for the loan.
iii. PV: required. The present value, or the total amount that a series of
future payments is worth now; also known as the principal.
iv. FV optional. The future value, or a cash balance you want to attain
after the last payment is made. If FV is omitted, it is assumed to be 0
(zero), that is, the future value of a loan is 0.
v. Type optional. The number 0 (zero) or 1 and indicates when payments
are due.
vi. 0 or omitted (at the end of the period)
vii. 1 (at the beginning of the period)

By: Archana Mishra

2. PPMT function: - returns the payment on the principal for a given period for an
investment based on periodic, constant payments and a constant interest rate.
a. SYNTAX =PPMT(RATE, PER, NPER, PV, [FV], [TYPE])
b. The PPMT function syntax has the following arguments:
i. Rate: required
ii. Per: number of months for which you are paying.
iii. NPER: required
iv. PV: required
v. FV: optional
vi. Type: optional
3. IPMT function: - returns the interest payment for a given period for an investment
based on periodic, constant payments and a constant interest rate.
a. SYNTAX :- IPMT(RATE, PER, NPER, PV, [FV], [TYPE])
b. The IPMT function syntax has the following arguments
i. Rate: required
ii. Per: number of months for which you are paying.
iii. NPER: required
iv. PV: required
v. FV: optional
vi. Type: optional
vii. FV function: optional
4. FV function: - FV, one of the financial functions, calculates the future value of an
investment based on a constant interest rate. You can use FV with either periodic,
constant payments, or a single lump sum payment.
a. SYNTAX : =FV(RATE,NPER,PMT,[PV],[TYPE])
b. The FV function syntax has the following arguments
i. Rate: required
ii. NPER: required
iii. PMT: required
iv. PV: optional
v. Type : optional
5. PV function: PV, one of the financial functions, calculates the present value of a loan
or an investment, based on a constant interest rate. You can use pv with either
periodic, constant payments (such as a mortgage or other loan), or a future value
that's your investment goal.
a. SYNTAX : =PV(RATE,NPER,PMT,[FV],[TYPE])
b. The PV function syntax has the following arguments
i. Rate : required
NPER: required
ii. PMT: required
iii. FV: optional

By: Archana Mishra

 iv. Type : optional
6. RATE function: returns the interest rate per period of an annuity.
a. SYNTAX : RATE(NPER, PMT, PV, [FV], [TYPE], [GUESS])
b. The RATE function syntax has the following arguments
i. NPER: required
ii. PMT: required
iii. PV: required
iv. FV: optional
v. Type : optional
vi. Guess : optional
7. NPER function: - returns the number of periods for an investment based on periodic,
constant payments and a constant interest rate.
a. Syntax : NPER(RATE,PMT,PV,[FV],[TYPE])
b. The NPER function syntax has the following arguments
i. Rate: required
ii. PMT: required
iii. PV: required
iv. FV: optional
v. Type: optional

TEXT TO COLUMNS
Text to column method separates the single cell value to multiple cells. For example we have
data like S no., Student Name, Course, Duration and Total Fees in a Single Column.

We need to convert the Text into multiple cells

i. Put your cursor on cell > Go to Data Tab > text to columns >
ii. Select Delimited as File type

iii.
iv. Press next to continue > Select the Separator used in your data to separate
the various Columns headings > In our example comma “,” is that separator
v. Select comma > Click on next > select the cell format you want to provide.

REMOVE DUPLICATES
This feature in excel deletes all the duplicate data in your spreadsheet from the selected
data or list range.
i. Select the range of cells containing the duplicate values that should be removed.

By: Archana Mishra

 ii. Go to Data Tab > Select Remove Duplicates > and then check on all the titles you
want to compare and remove the duplicate value from > select my data has headers
option for better results.

FLASH FILL
Flash Fill feature automatically fill the data when it senses the pattern or a series. For
example, you can use flash fill to separate first and last names from a single cell named as
Full name, or we can also combine the First and last Names from 2 different Columns.
i. Enter the Data

ii.
iii. Provide the first name and last of first data manually.
iv. Put your cursor below the Name or on cell B3
v. Goto Data tab > Flash Fill or directly press Ctrl + E key to apply Flash Fill.

DATA VALIDATION
In Excel, data validation is a feature using which we can validate the input in the selected
cells and range of cells. Using this, we can restrict which type of data can be entered in the
specific cell(s). The allowed values can be whole number, decimal numbers, list, date, time,
text (with specific length).
While validating the data input, we can also give the input message so that the user is
informed in advance that which type of value should be entered in that cell and we can also
give an error alert message so that the user will be informed that what wrong he had done.
To apply data validation, follow the simple steps:
→ Select the cell or cell range.
→ Go to “Data” tab and select “Data Validation” under “Data Tools” group.
→ This will open a dialog box with three tabs namely Settings, Input message, Error alert.
→ Select the type of data which you want to permit/allow and then specify its range in
“Settings” tab.
→ Then switch to next tabs and mention the input message and the error alert message.
→ The data validation is done.
Steps to create a data list:
→ Select the cell where you want to create the data list.
→ Go to “Data” tab and select “Data Validation” under “Data Tools” group.
→ Select “List” from the Allow any value drop down in “Settings” tab.
→ Then select the unique values from the sheet in the “Source”.
→ And finally click Ok.

By: Archana Mishra

FILTER
Filter in Excel is a valuable tool to analyse data quickly. Filter in Excel is an essential tool that
helps to display relevant data. It eliminates the irrelevant entries temporarily from the view.
This tool filters data according to the criteria to help analyse the critical data points.

Keyboard Shortcut Key: Ctrl + Shift + L

Steps: Data tab > Sort and Filter group > Filter

ADVANCE FILTER

While the regular data filter will filter the existing dataset, you can use Excel advanced filter
to extract the data set to some other location as well.
Excel Advanced Filter allows you to use complex criteria. For example, if you have sales data,
you can filter data on a criterion where the sales rep is Bob and the region is either North or
South (we will see how to do this in examples). Office support has some good explanation
on this.
You can use the Excel Advanced Filter to extract unique records from your data (more on
this in a second).

MACROS
MEANING: In Excel, macros are like smart helpers that do repetitive work for you. You can tell
them what to do, and they’ll do it over and over, saving you time! Think of them as tiny little
computer programs for Excel. (Alt + F8 to open view macros dialog box)
You can make them by recording what you do or by writing instructions in a special language
called Visual Basic for Applications (VBA).
Then, you can make them work by clicking buttons or using shortcuts.
A macro feature can be used from View Tab as well as Developer Tab.
To enable the Developer Option, we can use the following process:
• Go to File Tab > Options > Customize The Ribbon > select the Developer Option > press
ok to save the screen
• Or we can simply right Click on Ribbon > Select Customize the Ribbon > select the
Developer Option > press ok to save the screen.

Steps to Record a macro recording in excel file:

i. Go to Developer / View Tab > Click on record macro > Provide the macro name > Select
where you want this macro
ii. Provide the Shortcut key If Required > Click on OK button >
iii. Perform the action you want through your macro recording
iv. Click o Stop Recording Macro Button
Steps to Assign a Macro to Button
i. Select a Shape from Insert Tab, Illustration group > Draw the Shape

By: Archana Mishra

 ii. Right Click on the shape > select the option assign macro > select your macro recording
> Click Ok to save
iii. We can also use the developer tab control feature : select a button from developer tab
> draw > select the macro recording > save the screen by pressing OK
For example : in a student database we need to create 3 buttons to show different task 1 for
pass students 1 for fail students and 3 for all students
Steps for Simple filter
➢ Create your data list > select all the heading of data
➢ Goto data tab > filter >
➢ Now start the macro recording to create the button
o Goto view tab > macros > record macro
➢ Put your cursor on remark field ( only for example)
➢ Creating button for pass students
o Select the filter option > select pass
➢ Now stop the macro recording by
o View tab > macros > stop recording
➢ Now go to insert tab > shapes >select a shape a write pass in it
➢ Right click on shape > assign macro > pass > click ok
Do the same process for fail and all students button
Steps for Advance filter

➢ Create Your Data table. Copy the heading Remark and paste it on another cell.
➢ Write the criteria value below the remark cell
➢ Go to View / Developer tab > under macros group select record macro > provide the
name of your macro
➢ Go to data tab > under sort and filter group select Advance >
➢ In list range select the complete table > Criteria range select your condition (Remark
and 1 another cell)
➢ In copy to select the blank cells (The number of blank cells should be same as your
total columns in data table)
➢ Select unique records only.

➢
➢ Go to view tab > under macros group select stop recording.
➢ Go to insert tab > under illustrations group select shape > right click on the shape >
select assign macro > select your macro and click on ok.

By: Archana Mishra

 DATA TRANSFER USING MACROS
Method 1
1. Create sheet 1 data as item name , qty, price
2. Create the sheet 2 and transpose the data > select the heading + 2 more rows > press
ctrl + t to convert it into table format
3. Put your cursor outside the data table
4. Go to sheet 1 > go to view / developer tab > start macro recording >
5. Copy the data of all headings > go to sheet 2
6. Put your cursor on first blank cell of table > transpose the value > right click on cell >
select insert > table row above >
7. Again, put your cursor on first blank cell of data > go to sheet 1
8. Delete the values > go to macro > stop recording
9. Go to insert tab > take a shape > right click on it > assign macro

Method 2
1. Create sheet 1 data as item name , qty, price (provide the data value)
2. Copy the data of sheet 1 and transpose it on sheet 2 ( alt+ h,v,t)
3. Keep your cursor outside the data
4. Goto sheet 1 > developer tab > macro > start recording >
5. Copy item name , qty , price ( data)
6. Go to sheet 2 > put your cursor on first heading
7. Press ctrl + down arrow > go to developer tab > enable the option use relative reference
> press down arrow
8. Transpose the data by alt + h, v, t
9. Press right arrow > go to sheet 1 delete the data > disable the option use relative
reference > disable the macro recording
10. Go to insert tab > take a shape > right click on it > assign macro

Method 3
1. Create sheet 1 data as item name , qty, price (provide the data value)
2. Copy the data of sheet 1 and transpose it on sheet 2 ( alt+ H,V,T)
3. Keep your cursor outside the data
4. Goto sheet 1 > developer tab > macro > start recording >
5. Copy item name ( data ) >
6. Go to sheet 2 put your cursor on first heading ( i.e. item name)
7. Press ctrl+ down arrow > go to developer tab > enable use relative reference
8. Press down arrow > paste the data value > press right arrow
9. Disable use relative reference
10. Again, go to sheet 1 copy qty (data)
11. Go to sheet 2 > enable use relative reference > paste the value > press right arrow>
disable use relative reference

By: Archana Mishra

12. Again, go to sheet 1 copy price (data)
13. Go to sheet 2 > enable use relative reference > paste the value > press right arrow>
14. Go to sheet 1 delete all the value which you want
15. Disable the use relative reference > disable the macro
16. Go to insert tab > take a shape > right click on it > assign macro

DYNAMIC CHART
➢ Create the Data list > Press Ctrl + T to convert it into table
➢ To find the unique records of item name
o Select the item name list > Go to data Tab > Advance >
▪ Select copy to another location
▪ Provide the list range
▪ Skip the criteria range and provide the cell reference of copy location
▪ Click on copy to another location
➢ To find the unique records of country
o Select the country range > Go to data Tab > Advance >
▪ Select copy to another location
▪ Provide the list range
▪ Skip the criteria range and provide the cell reference of copy location
▪ Click on unique records only > click ok
➢ Go to Sheet 2 > Write down the headings such as Country, Item name and Amount
➢ For country list Go to Data tab > Data validation > select list instead of any value >
provide Source of Country (Select the cells where the Unique record of country is)
➢ For Item Name Copy the Item Name unique record and paste them on Sheet 2
➢ Use formula to find the total sales / Amount
➢ =SUMIFS(Table1[[#All],[PRICE]],Table1[[#All],[COUNTRY]],'DYNAMIC
CHART'!$B$3,Table1[[#All],[PRODUCT NAME]],'DYNAMIC CHART'!C6)

DEPENDENT DROPDOWN LIST

Steps to create dependent dropdown list are: -

➢ Create a list of states and provide the heading in the top row as Indian states
➢ Many of excel version does not accept space while defining name

By: Archana Mishra

➢ Here you are required to use “_” instead of space
➢ To write “_” in place of space select all > press Ctrl + H for find & replace option
➢ Find what : provide a single space
➢ Replace with : use underscore “_”
➢ To transpose data > select all states name > press Ctrl + C to copy > put your cursor
where you want to transpose > press keyboard shortcut > Alt + h, V, T or press Ctrl +
Alt + V and select transpose
➢ Provide all cities name
➢ Now select the Indian states list > go to formula tab > under defined name go to
create from selection
➢ Select top row > click ok
➢ Put your cursor where you want the list of Indian states > go to data tab > data
validation > select list instead of any value >
➢ When you are on source press f3 key to select the list name > select “Indian_states “
➢ Select the complete data where you have entered the states and cities list
➢ Go to formula tab > create from selection > select top row only > click ok
➢ Put your cursor where you want to enter the list of cities >
➢ Go to data tab > data validations > select list instead of any value > on source option
type a formula
➢ =indirect(cell reference of the cell where you have created a list for states )

By: Archana Mishra