MIS

(MANAGEMENT INFORMATION SYSTEM)

Table of Contents
WHAT IS MIS? .............................................................................................................. 2
STRUCTURE OF MIS .................................................................................................... 2
IMPORTANCE OF MIS FOR BUSINESS ......................................................................... 2
ADVANTAGES OF MIS .................................................................................................. 2
ROLE OF MICROSOFT EXCEL IN MIS ........................................................................... 3
ROLE OF MICROSOFT ACCESS IN MIS ........................................................................ 3
ROLE OF SQL IN MIS.................................................................................................... 3
MICROSOFT EXCEL...................................................................................................... 4
BENEFITS OF MICROSOFT EXCEL ....................................................................................................... 4
TERMS USED IN EXCEL ............................................................................................................................. 4
MATHEMATICAL FUNCTIONS ................................................................................................................. 4
LOGICAL FUNCTIONS ................................................................................................................................. 6
LOOKUP FUNCTIONS.................................................................................................................................. 6
DATE AND TIME FUNCTION .................................................................................................................. 10
DATA BASE FUNCTIONS.......................................................................................................................... 11
TEXT FUNCTIONS....................................................................................................................................... 13
FINANCIAL FUNCTIONS .......................................................................................................................... 13
FINANCIAL FUNCTIONS .......................................................................................................................... 15
DATA VALIDATION ...................................................................................................................................... 17
FILTER .............................................................................................................................................................. 18
MACROS ........................................................................................................................................................... 18
DATA TRANSFER USING MACROS ..................................................................................................... 19
DYNAMIC CHART ........................................................................................................................................ 20
DEPENDENT DROPDOWN LIST ............................................................................................................ 21
STOCK MANAGEMENT ............................................................................................................................. 22
ATTENDANCE SHEET ................................................................................................................................ 24
PRODUCTION SHEET ................................................................................................................................ 27
GST INVOICE/ TAX INVOICE.................................................................................................................. 29
PIVOT TABLE ................................................................................................................................................. 33
DASHBOARD .................................................................................................................................................. 33

BY: ARCHANA MISHRA

CALENDAR ...................................................................................................................................................... 34

WHAT IS MIS?
Management Information Systems (MIS) is the study of people, technology, organizations, and the
relationships among them. MIS professionals help firms realize maximum benefit from investment
in personnel, equipment, and business processes. MIS is a people-oriented field with an emphasis
on service through technology. If you have an interest in technology and have the desire to use
technology to improve people’s lives, a degree in MIS may be for you.

STRUCTURE OF MIS
→ APPLICATION SOFTWARE
→ DATABASE
→ PROCEDURE
→ OPERATING PERSONAL
→ INPUT AND OUTPUT OF DATA
→ MEDIUM AND MESSAGE
→ HARDWARE SYSTEM

IMPORTANCE OF MIS FOR BUSINESS

✓ Provide you with information you need to make decisions.
✓ Can give you a competitive edge by providing timely, accurate information.
✓ Can help you improve operational efficiency and productivity.
✓ Allows you to keep track of customer activity and preferences.
✓ Enables you to develop targeted marketing campaigns and improve customer service.

ADVANTAGES OF MIS
✓ It allows the real time performance report
✓ It generates the Analytical reports
✓ It compares projections and performances.
✓ It aids the work allocation process
✓ It improves internal communication.

BY: ARCHANA MISHRA

ROLE OF MICROSOFT EXCEL IN MIS
An Excel MIS report is a valuable tool for organisations to gather, analyse, and present data in a
structured and meaningful format. It leverages the powerful features of Microsoft Excel, such as
formulas, functions, pivot tables, and charting tools, to provide insights and performance metrics.
Excel MIS reports enable businesses to track key performance indicators, monitor progress towards
goals, and make informed decisions.

ROLE OF MICROSOFT ACCESS IN MIS

MS Access is used to store large amounts of data in an organized and efficient manner. It allows you
to create tables, forms, queries, and reports to manage your data.

ROLE OF SQL IN MIS

It is used to interact with the database with some operations. It is also used to perform specific
tasks, functions, and queries of data. SQL can perform various tasks like creating a table, adding
data to tables, dropping the table, modifying the table, set permission for users.

BY: ARCHANA MISHRA

MICROSOFT 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)
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.
BY: ARCHANA MISHRA
➢ 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.
➢ 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. 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 function and hide rows)
1 101 Average
2 102 Count

BY: ARCHANA MISHRA

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.
a. Syntax: =and(logical1,logical2…)
3. OR(): - OR function in excel checks whether the is 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.
BY: ARCHANA MISHRA
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

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

BY: ARCHANA MISHRA

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.
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_required_c
olumns),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:

➢ 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.

BY: ARCHANA MISHRA

→ 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.
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)

BY: ARCHANA MISHRA

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. NETWORKDAYS.INTL: 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: =NETWORKDAYS.INTL(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. WORKDAY.INTL: 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: Workday.intl(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)
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

BY: ARCHANA MISHRA

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)

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)

BY: ARCHANA MISHRA

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.

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
✓ SYNTAX: - =PMT(RATE, NPER, PV, [FV], [TYPE])
✓ The PMT function syntax has the following arguments:
• Rate: required. The interest rate for the loan.
• NPER: required. The total number of payments for the loan.
• PV: required. The present value, or the total amount that a series of future
payments is worth now; also known as the principal.
• 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.
• Type: optional. The number 0 (zero) or 1 and indicates when payments are due.
• 0 or omitted (at the end of the period)
• 1 (at the beginning of the period)
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.
✓ SYNTAX =PPMT(RATE, PER, NPER, PV, [FV], [TYPE])
✓ The PPMT function syntax has the following arguments:
• Rate: required
• Per: number of months for which you are paying.
• NPER: required
• PV: required
• FV: optional
• 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.
✓ SYNTAX :- IPMT(RATE, PER, NPER, PV, [FV], [TYPE])
✓ The IPMT function syntax has the following arguments
• Rate : required
• Per: number of months for which you are paying.

BY: ARCHANA MISHRA

• NPER: required
• PV: required
• FV: optional
• Type: optional
• FV: 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.

✓ SYNTAX : =FV(RATE,NPER,PMT,[PV],[TYPE])
✓ The FV function syntax has the following arguments
• Rate : required
• NPER : required
• PMT : required
• PV : optional
• 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.

✓ SYNTAX : =PV(RATE,NPER,PMT,[FV],[TYPE])
✓ The PV function syntax has the following arguments
• Rate : required
• NPER : required
• PMT : required
• FV : optional
• Type : optional
6. RATE FUNCTION: RETURNS THE INTEREST RATE PER PERIOD OF AN ANNUITY.

✓ SYNTAX : RATE(NPER, PMT, PV, [FV], [TYPE], [GUESS])

✓ The rate function syntax has the following arguments
• NPER : required
• PMT : required
• PV : required
• FV : optional
• Type : optional
• Guess : optional

7. NPER FUNCTION: - returns the number of periods for an investment based on periodic,
constant payments and a constant interest rate.

✓ SYNTAX : NPER(RATE,PMT,PV,[FV],[TYPE])
✓ The NPER function syntax has the following arguments
• Rate : required
• PMT : required
• PV : required
• FV : optional
• Type : optional

BY: ARCHANA MISHRA

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)
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

BY: ARCHANA MISHRA

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
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.

BY: ARCHANA MISHRA

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.
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 + B 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.
BY: ARCHANA MISHRA
→ Then select the unique values from the sheet in the “Source”.
→ And finally click Ok.
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
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
BY: ARCHANA MISHRA
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.
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 >
BY: ARCHANA MISHRA
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
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
BY: ARCHANA MISHRA
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
➢ 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
STOCK MANAGEMENT
1. Create sheet 1 as Home page like

2. Copy Bill No, Date, Item, Qty, Rate and Amount than go to sheet 2 (Purchase Record).
Transpose the data by CTRL + ALT+ V or ALT +H, V, T and do the same process on sheet 3
(Sales Report).
3. Select the heading on purchase sheet with 2 or 3 more rows and press CTRL+T to provide it a
table format and do the same process for Sales Record.

4. Put your cursor outside the table on sheet 2 (Purchase Record) and Sheet 3 (Sales Record).
5. Go to sheet 1 home page, provide the data and after that start macro recording.
6. Copy the data of Bill no, Date, Item, Qty, and rate and amount.

7. Go to sheet 2 Purchase record. Put your cursor on first blank cell of your data i.e... Cell below
bill no. Transpose the Data, right click on it and select insert > table row above> put your on
the first blank cell again.

BY: ARCHANA MISHRA

8. Go to sheet 1 home page > delete the data from bill no. to rate (don’t delete amount as you
have entered formula on that cell.
9. Stop the macro recording and go to insert tab > select a shape > right click on it > assign
macro > select your macro name.
10. Do the same process for sales sheet.
11. Go to sheet 1 home page > put your cursor on entry type > create a data validation list for
Purchase and Sales.

12. To Calculate the Last Bill No.:

13. Go to sheet 4 (in stock ) and provide the same spelling and formatting of item word as in
sheet 2 (purchase)

14. Select the rows and convert it on table format by CTRL+T

15. To show 1 item name only one time write item on outside the table for criteria .provide non
blank non equal formula
16. Put your cursor outside the table and start the macro recording.
17. Go to data tab > advance >
a. In List range select the Complete Column of Item Name
b. In Criteria range select the cells we have created as Non blank
c. In copy to select the Title of Item Name on the same Sheet
d. Select unique records only.

BY: ARCHANA MISHRA

18. Stop the macro recording > and go to insert tab > select a shape > right click on it > assign
macro > select your macro name.
19. For calculation of purchase Qty:

20. For sales Qty:

21. For In Stock:

22. Go to sheet 1 home page > to show the qty of item is available or not:

23. Save the Workbook with Macro Enabled Workbook (.xlsm file extension).

ATTENDANCE SHEET
Steps to create attendance sheet are as follows:
1. Create a background list for months and years.
2. Go to sheet 1 (attendance sheet) > keep your cursor where you want the list of months > go to data
tab > data validations > select list instead of any value in allows option > provide the list of months in
source option.

BY: ARCHANA MISHRA

3. Do the same process for year.

4. For starting date apply date value function

• =datevalue(1&select_month&select_year)

5. Now change the cell format as date by right click > format cell > long date
6. For end date apply formula of eomonth:
• =eomonth(start_date,0)

7. Use the same date format for last date as well.

8. Provide the employee information such as emp id, emp name , contact number etc.
9. For first date in dates select the starting date cell.

10. For second date

• If(previous_date<end_date,previous_date+1,””)

11.
12. Drag the formula for all dates.
13. For day apply the formula of text.
• =text(select_date,”ddd”)

14. Drag for all.

BY: ARCHANA MISHRA

15. To highlight Sunday with red fill colour, select all the data (Date, Days, Present and absent) > go to
conditional formatting > new rule > use formula to determine which cell to highlight>
• =first cell[in which day is written]=”sun”
• Go to format > fill > select colour

16. To restrict the attendance marking in Sunday

• Go to data tab > data validations > select custom in place of any value > in formula
type :
▪ =first cell<>”sun”
▪ Set the error alert for the validations

17. Now select all the dates > go to format cell > more number format > custom > in type column >
erase the value and type >
• Dd

18.
19. Select all the dates columns by column header > double click to adjust the column.
20. For total present
• =countif(select_range,”P”)

21.
22. Do same for total absent and total leaves.
23. To find the total working days in the month
• =netwokday.intl(select _start date,select ending_date,select 11 for Sunday only)

BY: ARCHANA MISHRA

24. Provide the basic salary for all employees
25. Calculate the per day salary by
• =basic salary / total working days

26. Decrease the decimal value to 0

27. To calculate net salary
• =per day salary * total present

To transfer the sheet on another sheet

28. Start the macro recording > provide a short cut key to the macro
29. Right click on your attendance sheet name > select the option move or copy > select move to end
and click on create a copy > press okay >
30. Again, go to the attendance sheet > select the all present and absent > delete > put your cursor on
first cell
31. Stop the macro recording.
32.Save the Workbook with Macro Enabled Workbook (.xlsm file extension).

PRODUCTION SHEET
1. Create a background data for month and year.
2. Create a drop-down list for month and year through data validation.
3. Apply the formula for first date:
4. =datevalue(1&select month&select year)

5. For ending date apply the eomonth function

BY: ARCHANA MISHRA

6. =eomonth(select_the_starting_date,0)

7. Provide product id , product name , rate manually.

8. For first date select the starting date cell

9. For second date provide an if function’s condition
10. =if(previous_date < end_date,previous_date +1,””)

11. Drag the formula for all dates.

12. For day apply the formula =text(select_the_date,”ddd”) and then drag the formula
13. For total production apply the sum formula and select the range of product.

14. For amount total production * rate

15. To highlight sunday apply the conditional formatting

16. Select the cells where you want the conditional formatting > goto home tab > conditional
formatting > new rule > use fromula to determine which value to format >

17. To stop entry in sunday cell apply the data validation

BY: ARCHANA MISHRA
18. Goto data tab > data validation> select custom instead of any value > provide the
formula .

19. To save the sheet for future use keep your cursor on first sheet on data > start the macro
recording > provide the short cut key as per your need > click on ok
20. Right click on sheet name > select move or copy > select move to end > click on create a
copy > click on ok

21.
22. Automatically your cursor will be on last sheet > keep your cursor on first sheet again > delete the
data of production you have entered earlier. ( do not delete any formula ) > stop the macro
recording.
23. Save the workbook by excel macro enabled workbook.

GST INVOICE/ TAX INVOICE

1. Create format for Tax Invoice with the Following pattern.

BY: ARCHANA MISHRA

2. Create Background data on sheet 2 for the Tax Invoice to record the data of Item Name, HSN
Code, GST Tax % and Price.
3. Press Ctrl + T to give a Table format to data.

4. Go to Sheet 1 select all the cells for Item Name Column > Go to Data Tab > Data Validations >
Select the option List >in Source select the Item Name list of table 1
5. For first serial no. apply the following formula:

6. For second serial no:

7. Drag the Formula for all serial number.

8. To find the value of HSN code of Product, GST % and Price, apply VLOOKUP function

• Here Lookup value is product name

• Table Array is the complete table on Sheet 2 (Table 1)
• Columns functions is used to find the columns sequence number of HSN Code, GST%
and Price.
• 0 refers to the exact match type.
• IFERROR function is used to hide the error value on blank Product name.
9. Drag the Formula for all.
10. Provide the Qty of the product manually.
11. To calculate the Amount :

12. For Tax /GST value:

13. For Net Amount:

14. Create a data validations list for Sales Type: Go to Data tab > Data validations > List > In Source
type “ Inter State, Intra State”

BY: ARCHANA MISHRA

15. Create the format of summary with the following Pattern:

16. For value of Gross amount apply the sum function and select the column of Amount.

17. To type the IGST :

18. To calculate the IGST amount:

19. To Type CGST and SGST :

20. To calculate the CGST and SGST amount:

21. For Net Amount Apply the sum Function and Add the Gross amount, IGST, CGST, and SGST

BY: ARCHANA MISHRA

22. To validate the Customer name to only enter the Text in the cell apply data validation in the
cell.
• Select custom and apply the formula:
▪ =ISTEXT(select the cell of Customer name)
▪ Go to Input message column and type the message
▪ Go to error alert column and type the error alert text and save the screen to
proceed.

23. To set the validation on contact number:

• =AND(ISNUMBER(select_cell),LEN(Select_cell)=10)

•
• Set the error alert and input message.
24. To set the validation on GSTIN:
• LEN(Select_cell)=15

•
25. To save the record on different sheet:
I. To save the sheet for future use keep your cursor on first sheet (Invoice) > start the
macro recording > provide the short cut key as per your need > click on ok
II. Right click on sheet name > select move or copy > select move to end > click on
create a copy > click on ok

BY: ARCHANA MISHRA

III. Automatically your cursor will be on last sheet > keep your cursor on first sheet again
> delete the manually entered data like customer details, date, invoice number, sales
types, Item Name column and qty column. ( do not delete any formula ) > stop the
macro recording.
IV. Save the workbook by excel macro enabled workbook.

PIVOT TABLE
A pivot table is a statistics tool that summarizes and reorganizes selected columns and rows of data in
a spreadsheet or database table to obtain a desired report. The tool does not actually change the
spreadsheet or database itself, it simply “pivots” or turns the data to view it from different
perspectives.
Pivot tables are especially useful with large amounts of data that would be time-consuming to calculate
by hand. A few data processing functions a pivot table can perform include identifying sums, averages,
ranges or outliers. The table then arranges this information in a simple, meaningful layout that draws
attention to key values.
When users create a pivot table, there are four main components:
➢ Columns- when a field is chosen for the column area, only the unique values of the field are
listed across the top.
➢ Rows- when a field is chosen for the row area, it populates as the first column. Similar to the
columns, all row labels are the unique values and duplicates are removed.
➢ Values- each value is kept in a pivot table cell and display the summarized information. The
most common values are sum, average, minimum and maximum.
➢ Filters- filters apply a calculation or restriction to the entire table.

DASHBOARD
1. Create your data table and convert it into pivot table like:

2. Right click on sum of amount > click on show values as > select % of grand total

3. Provide the difference value for each stock item out of 100% by using
• =1-B4

• Drag the formula for other stock item as well.

4. To insert a blank doughnut: put your cursor on any blank cell > go to insert tab > charts > pie
chart> select doughnut.

BY: ARCHANA MISHRA

5. To add the values in your doughnut, right click on chart > select data or go to chart design >
select data > click on add > keep series name blank > in series name fill
=1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1…………... At least 20 times to divide the doughnut at
20 equal parts > click on ok.

6.
7. Remove the label values and chart title from your doughnut.
8. Select the doughnut bars > right click > format data series > in fill option select solid fill and
provide the color for doughnut.
9. Copy the doughnut > keep your cursor on blank cell > paste it for another product.
10. Change all doughnut color.
11. To link the doughnut with your actual data > right click on the doughnut bar > select data >
add > in series name select the product name > in series values select the % cells in front of
product.

12. Right click on your doughnut > select change series chart type > select the product name

13. Select the orange part of the doughnut > go to format data bar (by right click) > select solid
fill > fill with white color > increase the transparency of it.
14. Select the blue part of the doughnut > go to format data bar (by right click) > select no fill.
15. Take a text box > put your cursor on formula bar > select the cell of %
16. Again, take a text box > keep your cursor on formula bar > select the product name.
17. Do the same process for all doughnuts.
18. Provide the heading as:
• ="Total Sales Of "&cell of salesman.

CALENDAR
1. Create a format for calendar with a background data of months and years.

2. Create dropdown list for month and year.

3. To mention the month and year name as title apply the following formula:

BY: ARCHANA MISHRA

4. Provide the Days name manually
5. To Find the First date (In background) of the month and year apply formula

•
• =DATEVALUE(1&SELECT_YOUR_MONTH&SELECT_YOUR_YEAR)
6. Now define the to the starting date by
• Go to Formula Tab > Define Name > Provide a name as “START”.
7. To find the weekday Sequence:
• =WEEKDAY(STARTING_DATE,1)

•
8. To mention the first date of calendar:

•
• =START-(WEEKDAY(START)-1).
9. For Second date:

•
• Apply the Same formula for all dates.
10. To Highlight the dates of previous and Next month
• Select all the dates >
• Go to Conditional Formatting > New Rule > Use Formula to determine which value to
format >

▪
▪ =Month(First_Selected_cell)<>Month(Start)
▪ Provide the format and press ok to accept the screen.

BY: ARCHANA MISHRA