ms. advance excel notes FOR ADVANCE EXCEL.pdf

MICROSOFT EXCEL ADVANCE NOTES

1. M.S. Advance excel
Introduction to M.S. Excel
❖Ms. Excel is a spread sheet is a spread sheet program where one can record
data in the form of tables.
❖It is easy to analyse data in an excel spread sheet.
❖It is a spread sheet program , which contains number of rows and columns.
❖Micro soft excel is an Electronic Spread sheet Computer Program.

2. Title bar
Quick Access tool
bar
File tab
Ribbon
Row labels
Active Cell
Status Bar
Formula bar
Name box
Column labels
Command tabs
Worksheet tab

3. Elements of Advance Excel
• Work Book
• Work Sheet
• Row
• Column
• Cell
• Name Box
• Formula Bar
• Functions
• Ribbon
• Scroll Bars (V,H)
• Tabs
• Work Book :- It is a collection of many work
sheets. Each file of excel called Work Book . A
Work Book contains maximum 255 worksheets
• Work Sheet :- A Worksheet is a collection or
sets of many cells. It is a working surface you
interact with to enter data
• Row :- A horizontal line called row. It runs
from left to right. There are 10,48,576 rows in
each worksheet.
• Column :-A vertical line called column. It
starts with letter “A”. There are 16,384
columns in each worksheet.(A-XFD)
This is row
This is column

4. •Cell :- A cell is the intersection of rows &
columns. It is identified by row number &
column header such as A1, A2.There are
17,179,869,184 cells in a worksheet.

•Name Box :- The name box normally
displays the address of the “active cell” on
the worksheet. We can also use the name
box to quickly create a name range.
• Formula Bar :- The formula bar in excel
sits directly above the worksheet area, to
the right of the name box .The formula bar
can be used to edit the content of any cell
and can be expanded to show multiple
lines for the same formula.
•Function :- A function is a
predefined formula that performs
calculation using specific values in a
particular order. A function may be
written a specific way, which is
called the syntax. The basic syntax
for a function is the equals sign(=)
• Tabs
• File tab :- It contains file
options such as New, Open,
Save, Save As, Print, Share,
Export, Exit
This is cell
Name box address
Formula bar

5. Home Tab
✓Clip board
➢ Cut :- It removes the data from its
original position.
➢ Copy :- It copies the selected the
data from a cell & range and paste it
to other position.
➢ Paste :- It paste the cut or copied
data of the selected cell & cell.
➢ Paste Special :- It is a special
feature of M.S. Excel . There ae
many options in M.S. excel
a)Formula :- It shows only formula of
the data sheet
b) Formula & formatting :- It shows
formula with formatting from the data
c) Keep source formatting :- It
shows formatting of the data
d) No borders :- it not shows
the border of the data

6. e) Keep source formatting with width :- it auto
adjust column widths
g) Values :- It copy only the numerical
values from a data
h) Values & formatting :- It paste
values & formatting from a data
i) Values & source formatting:-
It paste values with all type of
formatting
f) Transpose :- It converts our data
horizontal to vertical & vertical to
horizontal
Paste Special options

7. j) Formatting :- It paste only formatting not a
data.
l) Picture :- It paste data as a picture
k) Paste link :- it paste data as a link
m) Linked picture :- it paste data as a
linked picture
Paste Special options

8.  Font
a. Font style :- It changes the font style of
the selected data.
b. Font size :- It increases & decreases the
font size of the selected data.
c. Bold :- It bolds the selected the data.
d. Italic :- It italics the data which we have
selected.
e. Underline :- when we can highlight the
special text and word we use underline
font.
f. Font Color :- It is used to change the font
color .
g. Fill color :- when we highlight any cell or
range we fill the color on selected data.
h. Increase font size:- it increases the font
size.
i. Decrease font size:- it decreases the font
size.

Alignment
I. Alignment :- there are nine alignments right top,
right middle, right bottom, center top, center
middle, center bottom, left top, left middle, left
bottom.
II. Orientation :- There are many options in orientation
to applied in data.
III.Decrease indent:- it move our content closer to the
text border.
IV.Increase indent :- it move our content father away
from the cell border.
V.Wrap Text :- It wraps the words in one sentence.
VI.Merge & Center:- there are four types of merges-:
a.merge & center:- it merges the selected rows
and columns and arrange the text in center
position.
b. merge across :- it merges any columns of the
selected cell.
c. merge cells:- it only merges the selected rows
and columns and not centarize the text.
d. unmerge cells:- it unmerges the t cells which
we have merges.

9.  Number
▪ General:- all the cells are in general format
▪ Number :- it applies the numbers in decimal
form.
▪ Currency :- it creates currency sign after any
number or values.
▪ Accounting :- it creates currency sign
according to cell alignment.
▪ Short date :- it enters short date in the sheet.
Ex:-

▪Long date :- it enters long date in the sheet.
Ex-:
▪Time :- it enters time on the sheet.
Ex:-
 More number formats :-With this number
we apply many custom formatting like
Short & long date i.e. dd-mmm-yy and also
apply +91 option in any mobile no.
formats
I. first select contact no.
II. Then go to more number format option
otherwise press (ctrl+1) short key
III. Then select custom format option
IV. And apply +91 format

10.  Styles
❑ Conditional formatting :- it creates many
styles according to our data sheet.
for ex:- we can show our data sheets with many
options like Highlighted cell rules, Top & Bottom
rules, Data Bars, Color scales, Icon sets etc.
Format as table :- it creates any data sheet
in table design format.
❑ Cell Styles :- it creates styles in cells.
 Cells
❖ Insert :- we can insert any cell ,
rows , columns in the sheet where
we want.
❖ Delete :- we can delete any cell,
rows & columns which we want to
delete.
❖ Format :- through this option we
can increase rows and columns size
and hide , unhide any rows ,
columns & sheet which we want.
We can also change the name of
the sheet & its tab color also.

11.  Insert tab
✓ Pivot table :- through this option we can
summarize , effect in the sheet.
1. first we select data
2. Then we go to pivot table option
3. Then summarize our data in the form of
pivot tables

12. Slicer :- Through slicer option we can makes
our pivot table easier to filter any data
1. first we go to slicer option we insert slicer
2. select data from the slicer
3. Through slicer option we can show our
data in filteraize form
Pivot table data

13. Pivot Chart
We can show our data in the form of pivot chart & we can make pivot chart
through pivot table option.
a) Firstly select pivot table option then go to pivot chart option and select
any type of chart according to our need.
b) then insert pivot chart
0
20000
40000
60000
80000
100000
120000
abhay aparna
Total
Total

14. We can show our pivot chart option data through slicer and filter
connections
i. Firstly select all slicers
ii. Then connected to each & every slicers to pivot table to run our data
Pivot table data

15.  Illustrations
➢ Picture :- we can insert picture in the sheet and
we can give effect on the picture and can also
change the picture.
➢ Shapes :- we can insert shapes in the sheet. &
give style , effect on shape & we can also write
any txt. In the shape
➢ Smart Art :- we can insert smart art graphic
diagrams any many formats.
➢ Charts:- we can convert our data in any chart
format i.e. bar , line ,pie , surface , scatter ,
stock , bubble , combo etc.

16.  Spark lines
o Sparklines :- sparklines are mini type of charts
present data in the form of lines, columns & win
loss.
 Links :- we can apply link in any word , we can
also insert any file in the form of link in the sheet.
 Text
o Text box :- when we give any small info. ,
quotes in the sheet we applied text box
o Header & footer :- we applied header on the
top of the page & footer on the bottom of the
page.
o Word art :- through the word art option e can
highlight & effect our main lines.
o Signature line :- this option shows that whose
sign indicated in the document.
o Object :- we can insert any other documents
of other softwares .
 Equation :- we can apply any type of equation in
the sheet.
 Symbols :- we can apply symbols in the sheet
according to our need.

17. Page layout tab
 Through this option we can
change the font styles, colors
,font styles , margin , orientation
of the page & layout of the page
for print any sheet

18. Formula Tab
1. Mathematical functions
▪ SUM() :- It adds the values of given
numbers.
Ex:- =sum(select range)
=sum(select number1,number2….)
▪ SUMIF() :- Adds the values in a range the
we specific meet.
=sumif(range, criteria)
▪ SUMIFS() :- adds the value in a range
based on multiple criteria.
=sumifs(sum range , criteria
range 1, criteria 1 , criteria range 2 ,
criteria 2 ,……)
Ex:-

19. EVEN () :- it converts odd values in to even values.
=even(number)
 ODD () :- it converts even values in to odd values.
=odd(number)
 FACT () :- Returns a factorial value of a given
number.
=fact(number)
 PRODUCT () :- used to multiply a numeric range of
records.
=product(number1 , number2,….)
 POWER () :- it returns the power of any value.
=power(number , power)
 ROMAN () :- converts a number I to a roman
number.
=roman(number)
 SQRT () :- returns the square root of a
number.
=sqrt(number)
 MOD () :- returns the remainder.
=mod(number , divisor)
 SUMPRODUCT() :- sum a range of cells that
meet multiple area.
=sumproduct(array1 , array 2 ,
..)
1.Mathematical functions

20. 2. Statistical functions
▪ AVERAGE :- It calculates the average
number from a range of values.
=average(select
number1,number2….)
▪ AVERAGEIF() :- Calculates the average
range of values that meet specific
area.
=averageif(range, criteria)
▪ AVERAGEIFS() :- Calculates the
average of a range of values that meet
multiple area.
=averageifs(average_ range ,
criteria range 1, criteria 1 , criteria range
2 , criteria 2 ,……)
Ex:-

21. ▪ MAX() :- it finds maximum values from the
selected data or range.
=max(number1 , number2 ,number3,..)
▪ MIN() :- it finds minimum values from the
selected data or range.
=min(number1,number2,number3,..)
▪ RANK():- it returns the rank or position of a
numbers with in a range of numbers.
=rank(number,ref)
▪ COUNT() :- it counts all the values in a
range.
=count(value1 , value2 , value3..)
▪ COUNTA() :- it counts all type of data in the
cell. i.e. text, symbols, values etc.
▪ COUNTBLANK() :- counts all the blank cells
in a range.
=countblank(range)
▪ COUNTIF() :- count all the cells in a range that meet
specific area.
=countif(range,criteria)
▪ COUNTIFS() :- count all the cells in a range that meet
multiple area.
=countifs( criteria _range1,critreria1, criteria
_range2,criteria2.
2.Statical Functions

22. 3.Logical Functions
▪ IF() :- test a condition and takes an
alternative action
=if(logical _test, value _if _true , value_
if _false)

▪ AND():- it test up to 30 conditions using
logical and
=and(logical1,logical2,…)
▪ OR() :- it test up to 30 conditions using
logical or
=or(logical1, logical2,..)
4.Date & Time Functions
▪ TODAY() :- It returns the current date.
=today()
▪ NOW() :- it returns the current date &
time.
=now()
▪ DATE() :- It returns the sequential serial number for
the specific date and formats the result s a date
=date(year, month, day)
▪ DAY() :- it returns the day corresponding to a date
represented by a number between 1 and 31.
=day(serial_ number)
▪ MONTH() :- it returns the day of the week
corresponding to a specified date.
=month(serial_ number)
▪ YEAR() :- it returns the year corresponding to a date
represented by a number in the range.
=year(serial_ number)
▪ WEEKDAY() :- it returns the days of the week
corresponding to a specific date.
=weekday(serial_ number, return_ type)

23. ▪ WORKDAY() :- it returns the date a
specified number of working days
before or after a date.
=workday(start_ date, days,
holidays)
▪ WORKDAYS.INTL() :- it returns the
serial number of the date before or
after a specified number of workdays
with custom weekend parameters.
=workdays.intl(start _date, days,
weekends ,holidays)
▪ NETWORKDAYS() :- it returns the
number of workdays between two
dates
=networkdays(start_ date , end
date ,holidays)
▪ NETWORKDAYS.INTL():- it returns
the number of whole work days
between two dates with custom
weekend parameters.
=networkdays.intl( start_ date , end
_date , weekends, holidays)
4.Date & Time functions

24. 5 . Text Functions
▪LEFT() :- Extracts a specific number of
characters from the start of a cell.
=left(text, number_ chars)
▪ RIGHT ():- Extracts a specific number of
characters from the end of a cell.
=right(text, number_ chars)
▪ MID ():- Extracts a specific number of
characters from the middle of the cell.
=mid(text, start_ num, num_ chars)
▪ UPPPER() :- converts the contents of a cell
to uppercase.
=upper(text)
▪ LOWER () :- converts the contents of a cell
to lowercase.
=lower(text)
▪ LEN() :- returns the length, in number of
characters, of the content of a cell.
=len(text)

▪ REPT() :- repeats a character a
specified number of times.
=rept(text, number_ times)
PROPER() :- Converts the content of a cell to
proper case.
=proper(text)
▪TRIM() :- it removes unwanted spaces from
the cells.
=trim(text)
▪ REPLACE() :- replace existing characters in
a cell with a different set of characters.
=replace(old_ text, start_num, num_
chars, new_ text)
▪ SUBSTITUTE() :- replace existing characters
with a new characters in a cell.
=substitute(text, old_ text, new_ text,
instance_ num)

25. 6 . Financial Functions
▪PMT() :- Calculates loan repayments based on
constant payments and a constant interest
rate.
=pmt(rate, nper, pv,[fv] , [type])
▪ PPMT() :- calculates the principal payment
made in a period of investment.
=ppmt(rate, per, nper, pv, [fv] , [type])
▪ IPMT() :- calculate the interest paid during
the period of a loan or investment.
=ipmt(rate, per, nper, pv,[fv], [type])
▪ PV() :- represent the preset value of an
investment based on a constant interest rate
and payments.
=pv(rate, nper, pmt, [fv] , [type])
▪ FV() :- returns the future value of an
investment based on constant payments and a
constant interest rate.
=fv(rate, nper, pmt, [pv], [type])
▪ RATE() :- returns the interest rate per period
of a loan or investment.
=rate(nper, pmt, pv, [fv], [type])

7. Lookup and Reference
Functions
▪ LOOKUP():- performs a rough match
lookup either in a row or a column range
and returns the information.
=lookup(lookup_ value, array)
▪ VLOOKUP():- Looks vertically down a list to
find a record and returns information related to
that record.
=vlookup (lookup_ value, table_ array , col_
index_ num,[range_ lookup])
▪ HLOOKUP() :- Looks horizontally across a list
to find a record and returns information related
to the record.
=hlookup(lookup_ value, table_ array, row_
index_ num, [range_lookup])
▪ MATCH() :- returns the position of a value in a
list.
=match(lookup_ value, lookup_ array, [match
type])

26. ▪ INDEX() :- returns an
item from a specific
position in a list.
=index(array, row_
num, [col_ num])

▪INDIRECT () :- Allows you
to use a cell reference
entered as a test string.
=indirect(ref_ text,a1)
7.Lookup & References functions

27. ▪Vlookup+Match() :- It finds vertically down list and also find the position of headings in the cell.



▪





▪










7.Lookup & References functions

28. ▪ INDEX + MATCH():- through
this option we can find the
indexes of the data and can
find the exact heading
matches
Ex-:



▪























7.Lookup & References functions

29. ▪ VLOOKUP + INDIRECT():- Through
this formula we can find the data in
a multiple sheets and display in one
sheet.
Steps to create :-
1. create multiple table -> select table-
> go to name box -> erase a text and
create a new name> enter




▪























7.Lookup & Reference functions

30. DATA TAB
a) Get External data:- we can
import any application data by
using this option.
Ex:- From access , from text , from
web , from table range etc.
i.Select From Text file option.
ii. Then select notepad file &
import our data
















Queries & connections :- It shows connections of
the different sheet From access file.

31. d) Text to columns :- we can separate any
sentence in to columns.
Ex:-



▪
























Data tab

32. c) Sort & Filter :- We can use this
option to apply advance filter option
in the data sheet.
1. We make data sheet and given
some headings like name,
designation, items , sales
2. Then go to advance filter option
3. Then select data & shows the
data by using advance filter.



▪









d) Text to columns :- we can separate any
sentence in to columns.
Ex:-
e) Flash fill :- it separates the one cell data
Ex:- we write full name of person in one sheet
and using flash fill option we can separate first
name & last name.








Data tab

33. Data tab
e) Remove duplicates :- it
removes duplicates data from
the sheet.
▪f) Data validation :- we can create
a list , age, salary data through this
option.
















g) Consolidate :- It summarize data
from separate ranges, consolidating
the results in a single output range


1. We maintain our data in different
sheets
2. Then go to our new sheet & go to
consolidate option then select our
data from many different sheets
and add our data one by one
3. Then merges our data and in our
new sheet

34. Then add data one by one and merges our
data in the form of new sheet
Data tab

35. h) What if analysis :-
•Scenario Manager :- creates different group of
values or scenarios, & switch between them.
A. Firstly create our table
B. Then go to scenario manager option
C. Then select our data and add it after
that we make some changes in our
data
D. Then select summary option and
show our data in summary form.























We change our data through scenario
values and then ok our data
Data tab

36. • Goal Seek :- It find the right input from
the value we want.
Ex:-



• Data table :- through data table option
we can find the interest of years according
to rate.
•Ex:-






















Data tab

37. REVIEW TAB
➢ Proofing
i. Spelling :- It finds any
spelling & grammatical error
in data.
ii. Thesaurus :- It finds same
meaning of the words which
we have given.
iii. Workbook Statistics:- it
counts sheet, data , objects
we given in the sheet.
➢Languages :- it translates the selected
data in other languages.
➢Comment: :- we give a note to our data
through this option. & we can also delete
any comment.



➢Protect sheet :- We can prevent our data
by any changes like editing , formatting
etc.
➢ Protect book :- It keeps others from
making Structural changes to your
workbook such as moving , deleting , or
adding sheets.
➢ share workbook :- We can share workbook
by this option.

38. VIEW TAB
❖ Workbook Views
i. Normal View :- To see your
document in normal view.
ii. Page break preview :-we see
where the page breaks will
appear when your document is
printed.
iii. Page layout :- Through this
option we can see where pages
began or end & also see any
headers and footers of the page.
iv. Custom views :- It save your
current data and print settings as
a custom view that we can
quickly apply in the future.



❖ Show :- we can show ruler , gridlines ,
formula bar, headings when we print a
document.
❖ Zoom :- we can zoom in & zoom out our page
through zoom option. We can zoom any
particular cell through zoom to selection.
❖ Windows :- We can open a new window,
arrange window, side by side windows, and
switch windows.
❖ Freeze panes :- we can freeze first column &
first row.
❖ Split :- we can split our sheet to use this
option.
❖ Hide :- we can hide or unhide our current
window by using this option.
❖ Macros :- we can record & view our data
through macros option.

39. Short cut keys of advance excel
o Ctrl +A :- select all data
o Ctrl +B :- It bolds the data
o Ctrl +C :- It copies the data
o Ctrl +D :- It fills down the series
o Ctrl +F :- It finds the data
o Ctrl +G :- Go to
o Ctrl +H :- It replaces the data
o Ctrl +I :- It italics the word.
o Ctrl +K :- Insert hyperlink
o Ctrl +N :- to open new workbook
o Ctrl +O :- to open any file
o Ctrl +P :- to print out the data
o Ctrl +R :- to fill right series
o Ctrl +S :- to save the workbook
o Ctrl +T :- It creates a table
o Ctrl +U :- It underlines the selected
data
o
oCtrl +V :- It paste the cut or copied data
oCtrl +W :- to close the window
oCtrl +X :- to cut the selected data.
o Ctrl + Y :- It repeats the data we given
o Ctrl +Z :- It undo our data
o Ctrl + 1 :- Changes the format of the
selected cells
o Ctrl + Shift + : enter the current time
o Ctrl + ; enter the current date
o Ctrl + Shift + = Insert a new column or row
o Ctrl + Shift + ! Applies comma formatting
(number)
o Ctrl + Shift + $ Applies currency formatting
o Ctrl + Shift + % Applies percentage
formatting
o Ctrl + Shift + & Place the border around the
selected in cells.
o Ctrl + Shift + _ Remove a border
o Alt + Shift +F1 Insert a new worksheet
o Shift + F3 open the excel formula window

40. Short cut keys of
advance excel
o Shift + F5 :- bring up
search box
o Ctrl + Tab :- Switch
between two or more
opened excel files
o Alt + = Create formula to
sum all of the above cells
o Ctrl + ‘ Insert value of
above cells in to current
cells
o F7 :- For spell check
o Alt + Shift + F7 :- It
translate the data to
other languages
o Shift + F2 :- To insert a
note on the data
o Alt +F8 :- To record the
data
o Alt + H + B + A :- for
create borders

o Alt + H + B + N :- remove
the border
o Alt + H + H :- To fill the
color
o Alt + H :- to open box
&then press FC to select
color
o Alt + H+M+M :- to merge
the selected cells
o Alt + f8 :- To run macros
option

41. •






Create a dashboard through pivot table option
Dashboard file data

42. A dashboard is a visual summary of data that uses
charts, tables, & graphs to display information. It
help users make decisions by presenting large
amount of data in condensed format.

Purpose to use dashboard :-
❑ To quick overview of a project or area’s
performance & status.
❑ To help users make decisions based on the
data.
How to create dashboard in excel :-
1. First we maintain data sheet according to our
project.
2. Then prepare & connect data through charts,
tables , graphs & filter option.
3. Plan the layout
4. Build the dashboard

Dashboard File
First create data

43. •






Create a dashboard through pivot table option
Dashboard file data

44. •






To create dashboard layout
1.first create charts by using our pivot data table
2. Then insert slicers for filter option
3. Then apply Get Pivot Data formula to show totals of dashboard files
4. Then make graphics
5. Then embedded our dashboard file
First create charts according to pivot table data
Dashboard file data

45. •






Then apply “Get Pivot Data” formula for seeing the data of dashboard file
1. First go to pivot table data click on blank cell given syntax (=) and then
select the grand total of pivot data & result is coming out
We can also show our data through spark
lines option first select location to show
pivot data then select our data & result
will appear
Dashboard file data

46. •






We can show the result of get pivot data in dashboard file
1. First copied the get pivot data result
2. Then paste on dashboard file through paste as link picture option
Dashboard file data

47. •






We can show our data through slicers option
1. first we can connect each & every slicers through each other
2. Through this data we can embedded our dashboard
Dashboard file data

48. •






Complete layout of Dashboard file