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.
Features of MS 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
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.
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.
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.
The cell address is the name by which is cell can be addressed. Every cell has its own address.
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.
A bar the display the name of active workbook.
The letters that appear along the top of the worksheet to identify the different columns in the worksheet.
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.
Used for magnifying and shrinking of the active worksheet.
Buttons used to change how the worksheet content is displayed. Normal, Page Layout or Page Break Preview.
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.
The cell selected in the active worksheet.
A box that displays the cell reference of the active cell.
A bar that displays the value or formula entered in the active cell.
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.
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
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
Macro-enabled template– .xltm
Non-XML binary workbook– .xlsb
Macro-enabled add-in– .xlam