MS Excel notes (ccc) by social guide

 

MS Excel

 

Ms excel is a spread sheet based application.  The previous name of ms excel spread sheet was VisiCalc it was developed in 1979. It is used for creating ledger invoices and analysis report, it shows the file in the form of work book, the work book contains many work sheets each work sheet has 1048576 rows  and 16384 column, the column is given name a, b, c like that and the rows are numbered 1, 2, 3. The page is divided into different cells & each cell can hold one set of value and the value is referred by the address; there are different type of addressing in ms excel. The extension of excel file is .xls or .xlsx

 

1-             Relative addressing: - this addressing is used when you require copying the formula in different cell and need the address to be adjust according to the corresponding cell and column. for ex-

	A	B	C	D	E	F	G
1	Roll No.	Name	Hindi	English	Maths	Total	%age
2	1001	Sonu	98	29	86	=C2+D2+E2	=F2*100/300
3	1002	Monu	45	73	53
4	1003	Sona	87	49	59
5	1004	Golu	98	58	64
6	1005	Mona	30	99	79

 

2-             Absolute addressing: - this method of addressing is used with ($) dollar symbol with column name and row number this method of addressing does not change if you copy the formula in other cell. It means $ makes the row and column constant (unchanged). For ex-

	A	B	C	D	E	F	G
1	Roll No.	Name	Hindi	English	Maths	Total	%age
2	1001	Sonu	98	29	86	=$C$2+$D$2+$E$2	=F2/300*100
3	1002	Monu	45	73	53
4	1003	Sona	87	49	59
5	1004	Golu	98	58	64
6	1005	Mona	30	99	79

 

3-             Mixed addressing: - relative and absolute addressing methods are mixed together in the form of mixed addressing. for ex:-

 

 

	A		B	C	D	E	F	G
1					5%	18%	12%
2	Name	Post		Salary	Ta	Da	Hra	Total salary
3	Sonu	Doctor		65000	=C3*$D$1	=C3*$E$1	=C3*$F$1	=C3+D3+E3+F3
4	Monu	Accountant		40000
5	Sona	Clerk		25000
6	Golu	Manager		50000
7	Mona	Engineer		85000

 

 

 

There are some menu and tool bars are available in ms excel.

Top align:-it is used for set the top alignment of the selected cell.

 

Middle aligns:-it is used for set the middle alignment of the selected cell.

 

Bottom aligns:-it is used for set the bottom alignment of the selected cell.

 

Cell alignment: - you can format the cell value its alignment font border and pattern. To work with this you have to select particular cell or cell range and choose the formatting style from the format cell box.

 

Row formatting:-it formats the row for row size set the height of the row hide and unhide it.

 

Column formatting:-to set the column width and auto fit selection of the column we used column format.

 

Sheet format:-you can rename hide and unhide the sheet with the help of this option.

 

Auto format:-table designs are saved in excel template you can set the style and pattern of the table using auto format.

 

Auto sum:- it is use to display the sum directly to the selected cells.

 

Fill: - it fills the numbers to the selected cells. You can generate the series also the series can be from linear and growth for number. In the same way you can fill the series of dates.

 

Linear: - it fills the series by adding the step value to the number.

 

Growth: - it fills the series by multiplying the value with the step value.

 

Clear All: - it removes all cell contents and formatting including comments and hyper links from the selected cells.

Formats: - it removes only the formatting from your selection the content and comment are unchanged.

 

Content: - it removes the cell content (data and formula) from the selected cell.

 

Comments: - it removes only the comment from the selected cell.

 

Sort:-it arranges the record on the basis of any field or more than one field.

 

Ø Steps:-select the table

Ø Click on home menu

Ø Select sort option

Ø Choose the sort by column option

Ø Select ascending or descending order

Ø Click on ok button.

 

Filter:-it is used to short out the records from the table and show the records selectively.

 

                            Insert menu

 

Pivot table and pivot chart report:-pivot table is the report analysis tool that provide you the report to set interactive table display you can use it when you have a long list of figures and you want to compare several facts and each figures.

Ø Steps - select the table

Ø Click on data menu

Ø pivot table and chart

Ø Choose and click on next button

Ø Click on layout button

Ø Drag and drop the field into the required area

Ø Click on finish button.

 

Picture: - it inserts the clipart, chart, auto shapes, picture etc... At the insertion point.

 

Chart: - the graphical representation of data in the form of graph is called chart. There are different type of charts are available in ms excel but on the basis of data sets there are two category of the data series from the chart.

 

1-Single data series: - in this series of data set there is a set of category and one set of value in the form of series. In this method pie chart will be used.

For ex-

Party	Seats
Bsp	45
Bjp	256
Sp	102
cong.	84
Other	30

2-multiple data series: - this type of data series consist one set of category and many set of values

For ex-

Party	seat 2017	seat 2018	Seat 2019	Seat 2020
BSP	120	202	88	19
BJP	87	123	145	299
SP	45	100	55	74
CONG	90	24	98	33

 

Hyperlink:-it is used to link any file to your existing file or cell.

 

Text box: - it draws a text box with horizontal text direction where you clicks or selects the text. It helps you to arrange the text and graphics.

 

Header and footer: - the text block that appears either at the top or the bottom of your workbook is called header and footer, it repeats each and every page of your workbook.

 

Word art:-it is used for insert decorative text in your file.

 

Object: - it is used to insert an object such as any drawing, word art, and text effect at the insertion point.

 

Symbol: - it is used for insert the different symbols into your workbook.

 

                           Page layout  

 

Themes: - it is used for change the overall design of the entire workbook including color, font and effect.

 

Margins: - it is used for select the margin sign for the entire workbook or the current section.

 

Orientation: - it is used for switch the page between the portrait and landscape.

 

Size: - you can choose the paper size for the current section.

 

Print area:-

set print area:-it define the selected range as a print area which is only portion of your file that will be printed out.

 

Ø Steps- select the area which you want to print

Ø Click on page layout menu

Ø Select print area

Ø set print area.

 

Clear print area: - it is used for clear the selected print area from your active worksheet.

 

 Page breaks:-it specifies where a new page with breaks in printed copy. Page breaks are inserted above and to the left of the section.

 

Background:-it is used for choose an image to display as the background of the sheet.

 

Print titles:-it specifies row and columns to repeat on each printed page.

 

Height:-it formats the row for row size set the height of the row.

 

Width:-to set the column width and auto fit selection of the column we used column format.

 

Scale:-it is used for stretch or shrinks the printed output to a percentage of its actual size. The maximum width and height must be set to automatic to use this feature.

 

Gridlines:-

View:-It is use for show the lines between row & columns in the sheet to make editing and reading easier.

 

Print:-it is used for print row and columns heading.

 

                           Formula  Menu

 

Function:-functions are pre defined set of formula that executes the result according the procedure. There are different types of functions some of them are used for micro programming and some of them are used for spread sheet functions.

 

Concatenate: - it is used for joins the two or more strings

=concatenate (“hello”, “world”)

 

Dollar:-it precedes the dollar symbol with any number

=dollar (2400)

 

Rept: - it is used for repeat the strings

=rept (“R”, 9)

 

Trim: - it is used for remove the extra spaces

=trim (“Social               guide”)

 

Abs: - it returns the absolute value

=abs (-25)

 

Fact: - it returns the factorial of any number

=fact (10)

 

Int:-it returns integer number

=int (66.2)

 

Mod:-it show the reminder from the division

=mod (27, 2)

 

Product:-it multiply all numbers

=product (15, 6)

 

Sum:-it show the result by adding all numbers

=sum (B2:G2)

=sum (B2,C2,D2,E2,F2,G2)

 Note: - short cut key for sum is alt + += button .

  

IF Function:-it is used to compare value.

Ex:-=if(F2>300,”First”,if(F2>200,”Second”, if(F2>100,”Third”,”Fail”)))

 

	A	B	C	D	E	F	G	F
1	Roll No.	Name	Hindi	English	Maths	Total	%age	Grade
2	1001	Sonu	98	29	86	=C2+D2+E2	=F2*100/300	=if(F2>300,”First”,if(F2>200,”Second”, if(F2>100,”Third”,”Fail”)))
3	1002	Monu	45	73	53
4	1003	Sona	87	49	59
5	1004	Golu	98	58	64
6	1005	Mona	30	99	79

 

Upper function :-

It is use to capitalize is text which is given in small case.

Ex:- =upper(“social guide”)

 

Lower function:-

It is use to change all upper letter into small letter.

Ex:- =lower(“SOCIAL GUIDE”)

 

Len function:-

 This function is use to count the length of any string.

Ex:- =len(“social guide”)     5

 

ü Important

 

Round:-

It is use to perform the round robin function of any given number.

Ex:- =round(121.3658,2)

 

Now:-

It is use to display the current date and time of the system

Ex:- =now()

 

Power:-

it is use to find out the square value of the specific value .

Ex:- =power(2,4)

 

   Square:- it is use to find out square root value.

   Ex:- =sqrt(9)

 

 

 

Name: - it creates the name for cell range, or computes the value that you can use to refer to the cell range or value

1-define:-it is used for creating cell name for set of value in to the individual cell

	Jan sale	Feb sale	March sale
Sale	1000	2000	3000
Expense	120	300	450
Profit

For ex:-

 

 

 

 

 Steps:-

Ø Select cell b2

Ø Click on formula menuà define name

Ø Type the field name (jan sale)

Ø Click add

Ø Select cell c2

Ø Click on formula menuà define name

Ø Type the field name (feb sale)

Ø Click ok

Ø Now type the formula (=jan sale+feb sale) it will calculate the value from b2 and c2.

 

Auditing: - this option traces the value collectively with formula and dependency of formula or cell to different formula.

 

1-             Trace precedent:-if the formula is given in any cell it draws the tracer arrow from the cell to the formula.

 

2-             Trace dependent:-the value collected with the formula is traced by trace dependent a tracer arrow draw from the table to formula.

 

 

3-             Remove arrows:-it is using for remove all arrows from the table.

 

                         Data menu

 

Get external data: - you can select and create the data table and query from external data base source like ms access for importing you have to choose the file name from import select source box.

 

Sort:-it arranges the record on the basis of any field or more than one field.

Ø Steps:-select the table

Ø Click on data menu

Ø Select sort option

Ø Choose the sort by column option

Ø Select ascending or descending order

Ø Click on ok button.

 

Filter:-it is used to short out the records from the table and show the records selectively. In filter there are 2 types of filter are available –

1-             Auto filter:-it filters the record and shows the list of records selectively from the table it is easiest way to list the record from the table it filters one field at a time.

Ø   Steps:-select the table

Ø Click on data menu

Ø Select filter option

Ø Click on auto filter option.

 

2-Advance filter:-in advance filter you can filter the record form the table according to given criteria so that it may meet the criteria range which you wants to display the table area is called table range and the items which are written besides the table is called criteria.

Name	Post	Salary
Sonu	Doctor	14000
Monu	Accountant	30000
Sona	Clerk	19000
Golu	Manager	12000
Mona	Engineer	45000
Janu	Professor	30000
			Criteria
			Post	Salary
			Clerk	>=19000

Ø Steps:-select the table

Ø Data

Ø filter

Ø advance filter

Ø In criteria range box click and select the criteria

Ø Click ok.

 

Text to column:-it converts the selected text into a column.

 

Validation:-it provides u the way to create a validation entry into your excel sheet for ex: - if u wants to input the data between 1 to 100 like that; then u must have to use validation.

Ø Steps: - select the table(blank area)

Ø Click on data menu

Ø Select validation option

Ø Choose the condition from allow box (whole number)

Ø From data box choose criteria in minimum & maximum

Ø And type the value

Ø Click ok.

 

Consolidate:-it is the process of calculating or analyzing the result from different tables. To consolidation there must be more than one table or work sheet to aggregate the value from different work sheets and receive the result as consolidated result.

Steps:-

Ø Open all file which you want to use for consolidation

Ø Click on window menu

Ø Select arrange option

Ø Click Ok

Ø Select the blank area in one work sheet

Ø Click on data menu

Ø select consolidate option

Ø Select the range which you wants to consolidate

Ø Click on add button.

       Repeat last 3 steps according to your requirement.

 

 Scenarios:-it is another analysis tool with the help of scenarios we can change the rate and amount both according to the requirement

Table:-

Rate	6%
Term	50
Amount	20000
	=pmt(B1/12,B2,-B3)

Ø Steps:-select cell B4

Ø Click on data menu

Ø  what-if analysis

Ø Select scenarios option

Ø Click on add button

Ø In by changing cell box select B1 to B3 cell or type B1:B3

Ø Click on ok button

Ø Change rate, term and amount according to your requirement

Ø Click on summary button

Ø Click ok.

 

Goal seeks:-Goal seeks is an analysis tool it adjust the value in the specific cell to reach the target value according to the dependent cell.

For ex:-if you want to calculate the payment from the pmt (per month tax) value for a loan and want to analysis the changes are made with pmt value , what changes will reflect on the dependent cell amount

  Table:-

Rate	6%
Term	50
Amount	20000
	=pmt(B1/12,B2,-B3)

 

 

 

 

 

 

 

Steps:-select cell B4 or where u has inputted the formula.

Ø Click on data menu

Ø what-if analysis

Ø Select goal seek option

Ø Type new trail value in to value box

Ø In by changing cell box type B3 or select amount cell

Ø Click on ok button.

 

Table:-data table is related to the analysis tool it is range of cells that shows how changes certain value in your formula, it effect the result on the formula it provide the shortcut tool to calculate the multiple value and compare the result on different variations there are 2 type of table –

 

1-             Single variable table: - this option is used for show the pmt value on different rate of interest where term and amount are not changing only rate will change.

Table:-

Rate	6%
Term	50
amount	20000
	=pmt(B1/12,B2,-B3)
3%
10%
8%

 

 

 

 

 

Ø Steps - select the cell from a₄ to end of range of b column

Ø Click on data menu

Ø table

Ø Type or select b1 in column input cell

Ø Click ok

 

2-Double variable table: - this option is used for show the pmt value on different rate of interest as well as amount that will continuously change.

Table:-

Rate	6%
Term	50
Amount	20000
	=pmt(b1/12,b2,-b3)	2000	5000	3000
	3%
	10%
	8%

 

 

 

Ø Steps - select the cell from pmt to end of range of table range

Ø Click on data menu

Ø table

Ø Type or select b1 in column input cell

Ø Type or select b3 in row input cell

Ø Click ok.

 

Group and outline:-

1-   Hide detail: - on an outlined worksheet it hides the details rows and columns of a selected summary row and column.

 

2-   Show detail:-on an outlined worksheet it display the hidden detailed row or column.

 

 

3-   Group:- it define the selected row or column of detailed data as a group in an outline so you can summarize the data if you have not create an outline  this command creates it for you.

 

4-   Ungroup:-it removes the selected row and column from a group on an outlined worksheet.

 

 

5-   Auto outline: - it automatically outlined the selected data.

 

6-   Clear outline:-it removes the outlines from an outlined sheet of your work book.

 

 

7-   Setting: - it specifies the options used to outline and summarize data in a worksheet or a selected range of cells.

 

Subtotal:- it calculate the sub total for the numeric field on the basis of any group field the table which is used for subtotal must be arranged on the basis of field or column which u want to use for subtotal.

 

Ø Steps:-select the table

Ø Click on data menu

Ø Select subtotal option

Ø Choose the group field name from (at each change in) box.

Ø Choose the check mark from (add subtotal to) box

Ø Click on ok button

Note: - to remove the sub total click the remove all button from the sub total box.

 

                           Review menu

 

Spelling: - this command is used for check spelling in the current document, file, work book or item.

 

Research:- it is used for search the word meaning of the selected words.

 

Thesaurus:- you can see the meaning of related words and synonyms of selected words.

 

 

Comment: - it is used for giving any notation about any cell, cell range or value.

 

Track change:- it highlight the changes to cell contents in shared work book including moved and pasted content and insert/delete the row and column which can latter be accepted or rejected.

 

Protection:-

Protect sheet:-it prevent the changes to cell contents work sheet’s item in the chart , graphics object on a work sheet or chart sheet.

 

Protect workbook:-it is used to protect a workbook’s structure and window u can prevent changes to the structure of a work book so that there cannot be delete, moved, hidden, unhidden or renamed and new sheet cannot be inserted.

 

Protect for sharing: - it protect a shared work book.

 

                       View menu

 

Normal:-it switches to normal view which is the default view for most tasks of ms excel such as entering the data, filtering the record, charting etc…

 

Page break preview: - it switches to page break preview it is used for display your work sheet as it will be printed out.

 

Custom view: - it creates deferent views of your work sheet. A view provides an easy way see your data with deferent display option.

 

 

Full screen: - it hides most screen elements so that u can view more of your file to switch back on your original view press esc key from the keyboard.

 

Zoom:- To manipulate a display so as to make it large & possibly more details.

 

Window:-

New window: - it opens a new window with the same content as the active window so u can view different part of your file at the same time.

 

 Arrange:-it display all open files in separate windows on the screen. The arrange command make it easier to drag between the files.

 

Split: - it split the active window into panes or removes the split from the active window.

 

Macro:-macro records the series of action during the task of you’re excel working that you can play back in future.