Microsoft Excel :It basically provides an electronic spreadsheet where all the calculation can be done automatically through built programme. Spreadsheet s are required mainly for tabulation of data. Microsoft Excel is like a big table. It consists of fixed number of rows and columns. It has facility to present information in the form of chart and graphic.
In other word Microsoft Excel is a spreadsheet programme included in the MS office suite of application. Spreadsheet present tables of values aranged in rows and columns that can be manipulated mathematically using both basic and complex arithmetic operations and function.
Objectives of Ms Excel :
Creating an Excel workbook
Saving the workbook with password protection
The auto save option.
Editing and formatting Excel workbook.
Borders and shading.
Adjusting row and column width.
Working with formulae.
Preaparing charts and graph.
Microsoft Excel Features:
Worksheet: It is a grid of cell made up of horizontal rows and vertical columns. It contains 65536 rows and 256 column.
Row Number: Each row is given a number.
Column letter: Each column is given a letter starting from A,…..,Z,AA,….ZZ and so on.
Cell : It is basic unit of worksheet where numbers ,text, formulae,etc, can be place.
The default file name is. : Book 1
The extension file name is : XLS
Maximum no of column. : 256
Maximum no of rows. : 56536
Cell pointer: It is a cell boundary that specifies which cell is active at that moment.
Current cell: It is a cell which is active.
Range of cell: It is a group of contiguous cells that forms a rectangular area in shape.
Workbook: A worksheet is a grid of cell made up of rows and columns.
Data in Worksheet: Three types of data can be entered Number/characters,text and formulae.
Formula: It is a sequence of values ,cell adress,name functions or operators in cell that produces a new value form existing values.
Cell referencing: Each cell in worksheet has a unique address ,when cell adress is reffed in formulas it is called cell Referencing.
Relative Referencing: Cell referencing in which the cells are referred by their relative position in which worksheet relative to a particular cell.
Absolute Referencing: Cell referencing in which the cell are refferd by their fixed position in the worksheet.
Mixed Referencing: Combination of relative and absolute Referencing .
Functions: Functions are predefined formulae ,that perform calculation by using specific values called arguments.
Structure: The structure of a function begins with the function name ,followed by an opening parenthesis arguments for the function seapreted by commas and closing parenthesis.
Charts: Charts are the pictorial representation of worksheet data.
Area chart: An area Chart emphasises the magnitude of change over time.
Column chart: A column chart shows data changes over a period of time.
Bar chart: It illustrates comparisons among individual items.
Line chart: It shows trends in data at equal intervals.
EXCEL SHORTCUT KEYS
1. Ctrl + N: To create a new workbook.
2. Ctrl + O: To open a saved workbook.
3. Ctrl + S: To save a workbook.
4. Ctrl + A: To select all the contents in a workbook.
5. Ctrl + B: To turn highlighted cells bold.
6. Ctrl + C: To copy cells that are highlighted.
7. Ctrl + D: To fill the selected cell with the content of the cell right above.
8. Ctrl + F: To search for anything in a workbook.
9. Ctrl + G: To jump to a certain area with a single command.
10. Ctrl + H: To find and replace cell contents.
11. Ctrl + I: To italicize cell contents.
12. Ctrl + K: To insert a hyperlink in a cell.
13. Ctrl + L: To open the create table dialog box.
14. Ctrl + P: To print a workbook.
15. Ctrl + R: To fill the selected cell with the content of the cell on the left.
16. Ctrl + U: To underline highlighted cells.
17. Ctrl + V: To paste anything that was copied.
18. Ctrl + W: To close your current workbook.
19. Ctrl + Z: To undo the last action.
20. Ctrl + 1: To format the cell contents.
21. Ctrl + 5: To put a strike through in a cell.
22. Ctrl + 8: To show the outline symbols.
23. Ctrl + 9: To hide a row.
24. Ctrl + 0: To hide a column.
25. Ctrl + Shift + :: To enter the current time in a cell.
26. Ctrl + ;: To enter the current date in a cell.
27. Ctrl + `: To change the view from displaying cell values to formulas.
28. Ctrl + ‘: To copy the formula from the cell above.
29. Ctrl + -: To delete columns or rows.
30. Ctrl + Shift + =: To insert columns and rows.
31. Ctrl + Shift + ~: To switch between displaying Excel formulas or their values in cell.
32. Ctrl + Shift + @: To apply time formatting.
33. Ctrl + Shift + !: To apply comma formatting.
34. Ctrl + Shift + $: To apply currency formatting.
35. Ctrl + Shift + #: To apply date formatting.
36. Ctrl + Shift + %: To apply percentage formatting.
37. Ctrl + Shift + &: To place borders around the selected cells.
38. Ctrl + Shift + _: To remove a border.
39. Ctrl + -: To delete a selected row or column.
40. Ctrl + Spacebar: To select an entire column.
41. Ctrl + Shift + Space bar: To select an entire workbook.
42. Ctrl + Home: To redirect to cell A1.
43. Ctrl + Shift + Tab: To switch to the previous workbook.
44. Ctrl + Shift + F: To open the fonts menu under format cells.
45. Ctrl + Shift + O: To select the cells containing comments.
46. Ctrl + Drag: To drag and copy a cell or to a duplicate worksheet.
47. Ctrl + Shift + Drag: To drag and insert copy.
48. Ctrl + Up arrow: To go to the top most cell in a current column.
49. Ctrl + Down arrow: To jump to the last cell in a current column.
50. Ctrl + Right arrow: To go to the last cell in a selected row.
51. Ctrl + Left arrow: To jump back to the first cell in a selected row.
52. Ctrl + End: To go to the last cell in a workbook.
53. Alt + Page down: To move the screen towards the right.
54. Alt + Page Up: To move the screen towards the left.
55. Ctrl + F2: To open the print preview window.
56. Ctrl + F1: To expand or collapse the ribbon.
57. Alt: To open the access keys.
58. Tab: Move to the next cell.
59. Alt + F + T: To open the options.
60. Alt + Down arrow: To activate filters for cells.
61. F2: To edit a cell.
62. F3: To paste a cell name if the cells have been named.
63. Shift + F2: To add or edit a cell comment.
64. Alt + H + H: To select a fill colour.
65. Alt + H + B: To add a border.
66. Ctrl + 9: To hide the selected rows.
67. Ctrl + 0: To hide the selected columns.
68. Esc: To cancel an entry.
69. Enter: To complete the entry in a cell and move to the next one.
70. Shift + Right arrow: To extend the cell selection to the right.
71. Shift + Left arrow: To extend the cell selection to the left.
72. Shift + Space: To select the entire row.
73. Page up/ down: To move the screen up or down.
74. Alt + H: To go to the Home tab in Ribbon.
75. Alt + N: To go to the Insert tab in Ribbon.
76. Alt + P: To go to the Page Layout tab in Ribbon.
77. Alt + M: To go to the Formulas tab in Ribbon.
78. Alt + A: To go to the Data tab in Ribbon.
79. Alt + R: To go to the Review tab in Ribbon.
80. Alt + W: To go to the View tab in Ribbon.
81. Alt + Y: To open the Help tab in Ribbon.
82. Alt + Q: To quickly jump to search.
83. Alt + Enter: To start a new line in a current cell.
84. Shift + F3: To open the Insert function dialog box.
85. F9: To calculate workbooks.
86. Shift + F9: To calculate an active workbook.
87. Ctrl + Alt + F9: To force calculate all workbooks.
88. Ctrl + F3: To open the name manager.
89. Ctrl + Shift + F3: To create names from values in rows and columns.
90. Ctrl + Alt + +: To zoom in inside a workbook.
91. Ctrl + Alt +: To zoom out inside a workbook.
92. Alt + 1: To turn on Autosave.
93. Alt + 2: To save a workbook.
94. Alt + F + E: To export your workbook.
95. Alt + F + Z: To share your workbook.
96. Alt + F + C: To close and save your workbook.
97. Alt or F11: To turn key tips on or off.
98. Alt + Y + W: To know what’s new in Microsoft Excel.
99. F1: To open Microsoft Excel help.
100. Ctrl + F4: To close Microsoft Excel.
How to Start Ms Excel?
Start- Programs- Microsoft Excel
OBJECTIVES
Creating new worksheets.
Inserting and deleting Rows.
Move, copy and
Rename a Worksheet.
INSERT AND DELETE A ROW, COLUMN OR SELECTED CELLS
To insert a column or row
> Select the column(s) or row(s) where the new ones are to be inserted
> Right-click the selection and from the shortcut menu, select Insert (Speed Key:Ctrl++)
OR
> From the Insert menu, select Columns or Rows
To delete a column or row
> Select the column(s) or row(s) to delete
> Right-click the selection and from the shortcut menu, select Delete (SpeedKey:Ctrl +-)
OR
> From the Edit, menu select Delete
Insert a New Worksheet
To add a new worksheet
•From the Insert menu, select Worksheet
A new sheet is inserted before the current sheet and given the next sheet number in sequence.OR
Right-click a Sheet tab and from the shortcut menu, select Insert …
The Insert dialogue box is displayed.
•Select the Worksheet icon
> Click OK
Move, Copy, Rename and Delete a Worksheet
To copy a worksheet using drag-and-drop
•Point to the sheet tab of the worksheet to move or copy
• Hold down Ctrl then click-and-drag to the left or right
A small down pointing arrow appears above the sheet tabs.
Copying a Worksheet
•Release the mouse to insert the duplicate sheet where the arrow is positioned
The new sheet has the same name as the original, with a number after it to identify it as a duplicate. For example, if the original sheet is named Summary, the duplicate is named Summary (2).
To move a worksheet using drag-and-drop
> Position the pointer over the worksheet tab to move then click- and drag to move the sheet.
> A small down pointing arrow appears above the sheet tabs.
•Release the mouse to move the sheet where the arrow is positioned.
To move or copy a worksheet using the Move or Copy dialogue box
•Point to the sheet tab of the worksheet to move or copy
•From the Edit menu, select Move or Copy Sheet …
OR
•Right-click the selected tab and from the shortcut menu, select Moy or Copy …
The Move or Copy dialogue box is displayed.
•From the Before sheet: box, select a location for the sheet
•To copy the sheet, click the Create a copy check box
•Click OK
To rename a worksheet
> Select the worksheet tab to rename
•Double- click on the sheet tab
OR
> From the Format menu, select Sheet then Rename
OR
> Right-click the selected tab and from the shortcut menu, select Rename
> Type the new name on the highlighted sheet tab and press Enter
To change the colour of a worksheet tab
Select the worksheet tab
> From the Format menu, select Sheet then Tab Color …
OR
> Right-click the selected tab and from the shortcut menu, select Tab Color …
The Format Tab Color dialogue box is displayed.
Format Tab Colour dialogue box
> Select a colour from the colour palette
> Click OK
To see the colour more clearly once applied,click onto another worksheet tab,as a selected worksheet tab is still displayed as white.
To delete a worksheet
> Select the worksheet tab
> From the Edit menu, select Delete Sheet
OR
•Right-click the selected tab and from the shortcut menu, select Delete
FORMULA
It will perform the mathematical calculation on number. Every started with an equal to (=) sign.
Types of Operator
There are four types of operator in a formula such as :
a.Arithmetic Operator (+ ,-, x, %,/,^)
b.Text Operator (‘&’- Ampersand)
c.Comparison Operator (<,>,+ ,=, <>)
d.Reference Operator (:(Colon) ,, Comma,)
Types of Function
There are six types of function in a Microsoft Excel.
1. Mathematical Function
2. Statistical Function
3. Text Function
4. Logical Function
5. Date & Time Function
6. Financial Function
> Every formula and function started with an equal to sign (=).
Let’s started Formula and function
1. Arithmetic Operator / Mathematical function
SUM
Type the value 5 in A1
Type the value 4 in A2
Formula :-
Select the cell B1 and type “=A1+A2” then press the enter key in the keyboard. In cell B1 the answer will be displayed 9.
Function:-
Select the cell C1 and type”=Sum(A1,A2)then press the enter key in the keyboard. In cell C1 the answer will be displayed 9.
AVERAGE
Type 56 in cell A1, 16 in Cell A2,64 in A3 and 76 in A4.
Formula :
Select the cell B1 and type “=(A1+A2+A3+A4)/4. Then press the enter key in the keyboard. The answer will display in cell B1 as 53. Function:
Select cell C1 and type “=Average (A1:A4)” then press enterkeyinthe keyboard. Then the answer will display in cell C1 as 53.
PERCENTAGE
Type 6500 in cell C2.Calculate the 12.36% service tax of total value.
Select the cell D2. And type =C2x12.36%. And press the enter key in the keyboard. Then you will get the answer 403 in the cell D2.
Text Operator / Text Function
Type the word in”IN”in cell A1
Then type the word in”DIA”in cell A2
Formula :
Select the cell B1 and type “=A1&A2”, then press the enter key in the keyboard.Then the answer will be displayed in cell B1 as “INDIA”
& – Ampersand (It will joint the different text string in a single text string.)
Function:
Select the cell C1 and type= Concatenate(A1,A2).”And press the enter key in the keyboard then the answer will be displayed “INDIA” in cell C1.
CHART & GRAPH
Chart is a graphical and pictorial representation of numeric data. There are 14 types of chart in Ms Excel.
Column Chart
Bar Chart
Pie Chart
Bubbles Chart
Line Chart
Pyramid Chart
Scatter Chart
Area Chart
Doughnut Chart
Radder Chart
Surface Chart
Cone Chart
Cylinder Chart
Stock chart
To use chart or graph:
Select the database. Then select the Insert Menu and select the chart.
OR
It also apply from Standard Toolbar
Then you will get the chart wizard dialogue box
Select the required chart Type and press Next button
Put the data range (if required) and press Next button
Now enter your “chart Tittle”,name of X and Y axis.press Next
Enter chart Location and press Finish.
In this post We discussed about the Microsoft Excel,How to start Ms excel and short keys of Ms Excel. We hope that this post helps you to improve ideas about Ms Excel Basic.For any query about the post please visit our contact us page.Thank you for visit our page. Next post @Powerpoint Basic Coming Soon…….
