Microsoft Excel (MS Excel)

 Microsoft Excel (MS Excel)


Microsoft Excel (MS Excel)




Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications (VBA). Excel forms part of the Microsoft Office suite of software. An MS Excel file is saved with an extension of .xls

You can open the application on your personal computer while following these simple steps:

Click Start -> All Programs-> Microsoft Office->Microsoft Excel


To open a new worksheet in MS Excel, go to run and type “excel.exe” and press the enter button, then the workbook opens. 

run and type “excel.exe”

Features of MS Excel


MS EXCEL (MICROSOFT EXCEL)


Worksheet size-1,048,576 rows by 16,384 columns 

Column width– 255 characters 

Row height– 409 points 

Page breaks– 1,026 horizontal and vertical 

Total number of characters that a cell can contain– 32,767 characters 

Characters in a header or footer– 255 

Maximum number of line feeds per cell– 253 

Hyperlinks in a worksheet– 66,530 hyperlinks 

Panes in a window– 4 

Zoom range– 10 percent to 400 percent 

Fields in a data form-32 

Sheets in a workbook– Limited by available memory (default is 1 sheet) 

Unique cell formats/cell styles– 64,000 

Unique font types-1,024 global fonts available for use; 512 per workbook


SCREEN ELEMENTS IN MS EXCEL


SCREEN ELEMENTS IN MS EXCEL


Microsoft Office Button

Covered earlier in MS Office Section.

Worksheet or Spreadsheet

MS Excel provides us with a row and column sheet that we call a spreadsheet or worksheet. Which we can increase as needed. All these sheets are used for data entry. 

Workbook

The file in MS Excel is called a workbook, in which we work and store our data. Each workbook has by default 3 worksheets. In a workbook, we can store many types of information. 

Cell

The worksheet holds a lot of rows and columns. Intersection of each row and column creates a cell. A cell is a rectangular box. Text is typed in the cell itself.

Cell Address

The cell address is the name by which is cell can be addressed. Every cell has its own address. 

Ribbon

Microsoft Excel ribbon is the row of tabs and icons at the top of the Excel window that allows you to quickly find, understand and use commands for completing a certain task. It looks like a kind of complex toolbar, which it actually is. 

Quick Access Toolbar

The Quick Access Toolbar is a customizable toolbar that contains a set of commands that are independent of the tab on the ribbon that is currently displayed. You can move the Quick Access Toolbar from one of the two possible locations, and you can add buttons that represent commands to the Quick Access Toolbar. 

Title Bar

A bar the display the name of active workbook.

Column Headings

The letters that appear along the top of the worksheet to identify the different columns in the worksheet. 

Worksheet Window

A window that displays an Excel worksheet, basically this is where you work all the tasks.

Vertical Scroll Bar

Scroll bar to use when you want to scroll vertically through the Worksheet window. 

Horizontal Scroll Bar 

Scroll bar to use when you want to scroll horizontally through the worksheet window.

Zoom Controls

Used for magnifying and shrinking of the active worksheet. 

View Shortcuts 

Buttons used to change how the worksheet content is displayed. Normal, Page Layout or Page Break Preview.

Sheet Tabs 

Tabs the display the name of the worksheet in the workbook, by default its name sheet 1, sheet 2, etc. You can rename this to any name the best represent to your sheet. 

Sheet Tab Scrolling Buttons 

Buttons to scroll the sheet tabs in the workbook.

Active Cell 

The cell selected in the active worksheet.

Name Box 

A box that displays the cell reference of the active cell.

Formula Bar 

A bar that displays the value or formula entered in the active cell.

Charts

Charts are visual representations of worksheet data. Charts often makes it easier to understand the data in a worksheet because users can easily pick out patterns and trends illustrated in the chart that are otherwise difficult to see. 

Macros

 A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes.


FORMULA’S AND FUNCTIONS


FORMULA'S AND FUNCTIONS


Formulas in Excel

Formulas in Excel is an expression that operates on values in a range of cell addresses and operators. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3. An example of a formula made up of discrete values like =6*3.


Function in Excel

Function in Excel is a predefined formula that is used for specific values in a particular order. Function is used for quick tasks like finding the sum, count, average, maximum value, and minimum values for a range of cells. For example, cell A3 below contains the SUM function which calculates the sum of the range A1:A2.


Common Functions in Excel:-

SUM– Adds all the values in a range of cells

AVERAGE– Calculates the average value in a range of cells

SUBTOTAL– The SUBTOTAL() function returns the subtotal in a database. Depending on what you want, you can select either average, count, sum, min, max, min, and others. 

REPLACE– REPLACE() function works on replacing the part of a text string with a different text string. 

SUBSTITUTE– The SUBSTITUTE() function replaces the existing text with a new text in a text string. 

MAX– Finds the maximum value in a range of cells

MIN– Finds the minimum value in a range of cells

DAYS– Returns the number of days between two dates

NOW– Returns the current system date and time

COUNT– Counts the number of cells in a range of cells

LOOKUP– To look in a single row or column and find a value from the same position in a second row or column.

LEN– Returns the number of characters in a string text

RAND– Generates a random number between 0 and 1

MEDIAN– Returns the number in the middle of the set of given numbers

PI– Returns the value of Math Function PI(π)

POWER– Returns the result of a number raised to a power. 

MOD– Returns the Remainder when you divide two numbers

ROUND– Rounds off a decimal value to the specified number of decimal points

LEFT, RIGHT, MID– The LEFT() function gives the number of characters from the start of a text string. Meanwhile, the MID() function returns the characters from the middle of a text string, given a starting position and length. Finally, the right() function returns the number of characters from the end of a text string.


Microsoft Excel Keyboard Shortcuts

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 italicise 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 strikethrough 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 + Spacebar: 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. 


File Extensions in MS Excel


File Extensions in MS Excel



Workbook– .xlsx 

Macro-enabled workbook-.xlsm 

Template- .xltx 

Macro-enabled template– .xltm 

Non-XML binary workbook– .xlsb 

Macro-enabled add-in– .xlam 


Leave a Comment