Microsoft Excel is a powerful spreadsheet software with a wide range of commands and shortcut keys to help you work more efficiently. Here are some commonly used Excel commands and their corresponding shortcut keys:

Basic Navigation:

Move to the next cell: Enter or Tab
Move to the previous cell: Shift + Tab
Move to the beginning of the current row: Home
Move to the beginning of the current column: Ctrl + Home
Move to the last cell in the data region: Ctrl + End
Move one cell to the right: Right Arrow
Move one cell to the left: Left Arrow
Move one cell up: Up Arrow
Move one cell down: Down Arrow
“Esc” (Escape) key : Cancel an Action: The primary use of the “Esc” key in Excel is to cancel or escape from a current action (editing) or operation.

Selection:

Select the entire worksheet: Ctrl + A
Select the entire row: Shift + Spacebar
Select the entire column: Ctrl + Spacebar
Select adjacent cells: Click and drag
Select non-adjacent cells: Ctrl + Click
Select the entire region of data: Ctrl + Shift + *
Extend the selection: Shift + Arrow keys

Editing:

Cut: Ctrl + X
Copy: Ctrl + C
Paste: Ctrl + V
Undo: Ctrl + Z
Redo: Ctrl + Y or Ctrl + Shift + Z
Insert a new row: Ctrl + Shift + +
Insert a new column: Ctrl + Spacebar, then Ctrl + Shift + +
Delete a row: Ctrl + –
Delete a column: Ctrl + Spacebar, then Ctrl + –

Formatting:

1. Bold: Ctrl + B
2. Italic: Ctrl + I
3. Underline: Ctrl + U

Strikethrough: Ctrl + 5

Format Cells: Ctrl + 1
Open the Font dialog: Ctrl + Shift + F
Open the Format Cells dialog: Ctrl + Shift + P
Align left: Ctrl + L
Center: Ctrl + E
Align right: Ctrl + R
Wrap text: Alt + Enter
Increase font size: Ctrl + Shift + >
Decrease font size: Ctrl + Shift + <

Formulas and Functions:

Insert a function: Shift + F3
Edit the active cell: F2
Autosum: Alt + =
Insert Function dialog: Shift + F3

Workbook and Sheet Actions:

Create a new workbook: Ctrl + N
Open an existing workbook: Ctrl + O
Save: Ctrl + S
Save As: F12
Close workbook: Ctrl + W
Print: Ctrl + P
Switch between open workbooks: Ctrl + Tab
Insert a new worksheet: Shift + F11
Delete the current worksheet: Alt + E, then L

function keys in excel

Function keys, often labeled as F1, F2, F3, and so on, serve various purposes in Microsoft Excel and can be used to perform specific functions or execute certain commands. Here’s a list of the most common uses of function keys in Excel:

F1: Opens the Excel Help window, providing access to Excel’s built-in documentation and assistance.
F2: Edits the active cell, allowing you to modify its contents directly. It’s useful for quickly entering or editing data within a cell.
F3: Opens the Paste Name dialog box, which allows you to select and paste named ranges or cells into your formula or worksheet.
F4: Repeats the last action (such as formatting or cell editing). It’s particularly handy for applying the same formatting or action to multiple cells.
F5: Opens the Go To dialog box, enabling you to navigate to a specific cell or range in your worksheet.
F6: Cycles through different elements of the Excel window, including the worksheet, ribbon, and task panes.
F7: Opens the Spelling and Grammar check dialog, helping you identify and correct spelling and grammar errors in your worksheet.
F8: Toggles Excel’s Extend Selection mode on or off, allowing you to select cells or ranges using the arrow keys.
F9: Calculates all worksheets in all open workbooks. If you have manual calculation mode enabled, it calculates only the active worksheet.
F10: Activates the Excel menu bar, making it accessible using the arrow keys. It also toggles key tips on the ribbon.
F11: Creates a new chart sheet with the currently selected data or creates a new worksheet and inserts a chart based on selected data.
F12: Opens the Save As dialog, allowing you to save the current workbook with a different name or in a different location.

Please note that some laptops or keyboards may require you to press the “Fn” key in combination with the function key to activate its Excel-specific function. The specific functionality of these keys may vary depending on your Excel version and any customizations you’ve made to Excel’s settings.

shift + function keys in excel

In Microsoft Excel, you can use the Shift key in combination with function keys (F1, F2, F3, etc.) to perform additional actions or access certain features. Here are some common uses of Shift + function keys in Excel:

Shift + F1: Opens the “What’s This?” help feature, which allows you to get information about specific elements in the Excel interface. Click on the element you want to learn more about while holding Shift + F1, and Excel will provide a description.
Shift + F2: Opens the Edit Comment dialog box, allowing you to edit the comment associated with the active cell. Comments are often used to add explanations or notes to cells.
Shift + F3: Opens the Paste Function dialog box. This dialog allows you to insert a function into a cell and provides a list of available functions categorized by type.
Shift + F4: Repeats the last Find action. If you’ve used the Find feature (Ctrl + F) to search for something, Shift + F4 will repeat that search.
Shift + F5: Opens the Find and Replace dialog box, which is a more advanced search and replace tool. You can use this to search for specific values or formulas and replace them with other values or formulas.
Shift + F6: Switches between the worksheet, ribbon, task panes, and other parts of the Excel window. It’s useful for navigating Excel when you have multiple elements on the screen.
Shift + F8: Activates Excel’s Add to Selection mode. This allows you to select non-contiguous cells or ranges. After activating Add to Selection mode, you can use the arrow keys to extend the selection.
Shift + F9: Calculates the active worksheet. Unlike F9, which calculates all open workbooks, Shift + F9 focuses on the currently active worksheet.
Shift + F10: Opens the context menu (the same as right-clicking) for the selected cell, range, or object. It provides a list of options relevant to the selected item.
Shift + F11: Adds a new worksheet to the workbook without displaying the Insert Worksheet dialog. It’s a quick way to insert a new sheet.
1. “Alt + I + R” is adding new worksheets to your Excel workbook without using the mouse or navigating through the ribbon.
Shift + F12: Opens the Save As dialog box, allowing you to save the current workbook with a different name or in a different location.

These Shift + function key combinations can be handy for various tasks in Excel, especially if you need to access specific features quickly or perform actions related to data editing, navigation, or calculation.

ctrl + function keys in excel

In Microsoft Excel, you can use the Ctrl key in combination with function keys (F1, F2, F3, etc.) to perform various tasks and shortcuts. Here are some common uses of Ctrl + function keys in Excel:

Ctrl + F1: Toggles the display of the Ribbon (the Excel toolbar) on and off. This provides more screen space for your worksheet.
Ctrl + F2: Opens the Print Preview window, allowing you to preview how the printed version of your worksheet will look. You can also access print-related settings from this window.
Ctrl + F3: Opens the Name Manager dialog box, which allows you to create, edit, and manage named ranges in your workbook.
Ctrl + F4: Closes the active workbook. If you have multiple workbooks open, this shortcut closes the currently active one.
Ctrl + F5: Restores the window size of the workbook to its normal size. If you’ve maximized or resized the workbook window, this shortcut returns it to its default size.
Ctrl + F6: Switches between open workbooks. If you have multiple workbooks open, this shortcut allows you to cycle through them.
Ctrl + F7: Activates the Move mode, which allows you to move the Excel window using the arrow keys. This can be useful if the window is partially off-screen.
Ctrl + F8: Activates Excel’s Resize mode, which lets you resize the Excel window using the arrow keys. This can be helpful when you need to adjust the window size.
Ctrl + F9: Minimizes the active workbook window, reducing it to a taskbar button. You can then use Alt + Tab to switch between open applications.
Ctrl + F10: Maximizes or restores the Excel window. If the window is not maximized, this shortcut maximizes it. If it’s already maximized, it restores it to its previous size.
Ctrl + F11: Inserts a new worksheet in the current workbook. This is a quick way to add a new sheet without navigating through menus.
Ctrl + F12: Opens the Save As dialog box, allowing you to save the current workbook with a different name or in a different location.

Alt keys in excel

Alt keys (also known as Alt key shortcuts or Alt keyboard shortcuts) are used in Microsoft Excel to access various functions and features without using the mouse. By pressing the Alt key and a specific key or key combination, you can activate Excel commands and navigate through the ribbon interface. Here are some common Alt key shortcuts in Excel:

Alt: Pressing the Alt key once activates the ribbon interface, and key tips (letters or numbers) appear on the ribbon tabs and command buttons. You can then press the corresponding key to access a specific tab or command. For example, Alt, H, and then V activates the Home tab and selects the Paste command.
Alt + Enter: used to insert a line break within a cell’s content.
Alt + N: Activates the Insert tab on the ribbon, allowing you to insert various elements like charts, tables, and shapes.
Alt + P: Activates the Page Layout tab, where you can configure page setup options, themes, and print settings.
Alt + M: Opens the Formulas tab, providing access to various mathematical and financial functions.
Alt + A: Switches to the Data tab, where you can perform data-related tasks such as sorting, filtering, and data validation.
Alt + R: Activates the Review tab, allowing you to check spelling, protect sheets, and track changes.
Alt + W: Opens the View tab, where you can adjust the workbook’s view settings, zoom level, and gridlines.
Alt + F: Activates the File menu or file tab (Backstage View), which is used for managing and working with files, including opening, saving, and printing.
Alt + E, S, V: These keystrokes will open the Paste Special dialog, which allows you to paste copied data in various formats and with specific options.
Alt + E, S, T: Opens the Text to Columns Wizard, which is useful for splitting text into separate columns based on delimiters.
Alt + E, S, A: Opens the Sort dialog, where you can specify sorting criteria for your data.
Alt + E, S, F: Activates the Filter dropdown in the header of a selected column. This allows you to filter data based on specific criteria.
Alt + E, S, C: Clears filters from the selected range or column.
Alt + O, C, A: Opens the Conditional Formatting menu, allowing you to apply various formatting rules based on cell values.
Alt + H, I, R: Inserts a row in the active worksheet.
Alt + H, D, C: Deletes the selected column(s).
Alt + E, D: Opens the Data Validation dialog, where you can set rules for data entry in selected cells.

These are just a few examples of Alt key shortcuts in Excel. Excel provides many Alt key combinations to access specific commands and features, making it possible to work efficiently without constantly reaching for the mouse. Remember that these shortcuts may vary slightly depending on your Excel version and configuration.

Ctrl keys in excel

Ctrl key shortcuts (also known as Ctrl keyboard shortcuts) in Microsoft Excel are a set of key combinations that allow you to perform various actions and tasks quickly without having to use the mouse. Here are some common Ctrl key shortcuts in Excel:

Ctrl + C: Copies the selected cells or text to the clipboard.
Ctrl + X: Cuts (copies and removes) the selected cells or text to the clipboard.
Ctrl + V: Pastes the contents of the clipboard into the selected cells or text.
Ctrl + Z: Undoes the last action, allowing you to reverse your most recent changes.
Ctrl + Y: Redoes the last undone action, effectively reversing the Undo command.
Ctrl + A: Selects all the data in the current worksheet or selected region.
Ctrl + S: Saves the current workbook.
Ctrl + N: Opens a new, blank workbook.
Ctrl + O: Opens an existing workbook.
Ctrl + R : Rename the table.
1. to fill the selected cell or range of cells with the contents of the cell immediately to the left. It’s a quick way to copy data from one cell to adjacent cells in the same row.
Ctrl + P: Opens the Print dialog for printing the current worksheet or workbook.
Ctrl + F: Opens the Find and Replace dialog, allowing you to search for specific text or values within the worksheet.
Ctrl + H: Opens the Replace dialog, allowing you to find and replace specific text or values in the worksheet.
Ctrl + G: Opens the Go To dialog, which helps you navigate to a specific cell or location in the worksheet.
Ctrl + 1: Opens the Format Cells dialog, where you can adjust cell formatting, such as number formats, font styles, and alignment.
Ctrl + 5: Applies or removes strikethrough formatting to the selected text or cells.
Ctrl + 6: Hide and Shows Hidden Objects
Ctrl + 9: Hides the selected rows.
Ctrl + 0 (zero): Hides the selected columns.
Ctrl + * (asterisk): Selects the current region (the block of cells around the active cell containing data).
Ctrl + – (minus): Deletes the selected cells or rows.
Ctrl + Shift + =: Inserts a new row above the active cell.
Ctrl + Shift + +: Inserts a new column to the left of the active cell.
Ctrl + Spacebar: Selects the entire column containing the active cell.
Shift + Spacebar: Selects the entire row containing the active cell.
Ctrl + Tab: Switches between open workbooks or worksheets.
Ctrl + Page Up: Switches to the previous worksheet in the current workbook.
Ctrl + Page Down: Switches to the next worksheet in the current workbook.
Ctrl + Arrow Keys: Moves the active cell to the edge of the data region in the direction of the arrow.
1. Ctrl + Right Arrow: Moves the active cell to the last column with data in the current row. If there is no data in the current row, it will go to the last column in the row containing data.
2. Ctrl + Left Arrow: Moves the active cell to the beginning of the current row, no matter where you are within the row.
3. Ctrl + Down Arrow: Moves the active cell to the last row with data in the current column. If there is no data in the current column, it will go to the last row in the column containing data.
4. Ctrl + Up Arrow: Moves the active cell to the top of the current column, regardless of where you are within the column.

Microsoft Excel Features

In Microsoft Excel, the main menu bar is referred to as the “Ribbon.”
Each tab on the Ribbon is further divided into groups, and each group contains specific commands related to a particular category.
The Ribbon replaced the traditional menu and toolbar system in Excel starting with the 2007 version.
The Ribbon is designed to make it easier for users to find and use the commands they need while working in Excel.

Overview of the Main tabs on the Ribbon in Excel:

File : This tab is not part of the Ribbon but is accessed by clicking the File tab in the upper-left corner. It allows you to jump into the backstage view options for managing and working with files, such as opening, saving, printing, and accessing Excel options.

Home: Contains commonly used commands such as formatting options, copy and paste, font settings, and alignment options.

Insert: Allows you to insert various elements into your Excel workbook, such as tables, charts, shapes, and hyperlinks.

Page Layout: Includes tools for adjusting the layout and appearance of your Excel sheets, including themes, page setup, and print options.

Formulas: Provides access to functions and formulas. You can use this tab to insert, edit, and manage formulas in your worksheets.

‘=’ must be used before a formula.

Data: Contains tools for importing, sorting, and filtering data. You can also use this tab for data validation and creating data connections.

Review: Includes features for proofreading, spelling check, and workbook protection. You can also add comments and track changes in this tab.

View: Allows you to control the way you view your Excel workbook, including options for zooming, arranging windows, and switching between different views. eX – Hide, unhide,split,

Developer: This tab is not always visible by default. You can enable it in Excel options. It provides tools for creating and managing macros, forms, and other developer-related tasks.

Sorting data in Excel is a useful feature that allows you to arrange information in a specific order based on one or more columns.

AutoSum: AutoSum is a quick way to add up a column or row of numbers.

AutoFill: AutoFill is useful for quickly filling cells with a series pattern or copying formulas.

PivotTable: PivotTables are powerful tools for analyzing and summarizing data.

Rows : 1 to 1048576 ; Row Height 409 points ,

Columns : A to XFD (16384 = 2^14) ; Column Width 255 Characters

Cells : small rectangular boxes in a worksheet.

Default alignment of numeric values in cell : Right
Default alignment of Text in cell : Left
Total no of characters a cell can contain is 32767.
A cell may display ##### when a column is not wide enough to show all cell contents.

Spreadsheet/ worksheet : File made of rows and columns.

Workbook : Collection of one or More worksheets.

By Default in Ms excel 2010, there are 3 worksheets.

Title Bar: The title bar is located at the top of the Excel window and displays the name of the current workbook or document.

It also contains the minimize, maximize/restore, and close buttons on the right side.

Taskbar:The taskbar is a part of the Windows operating system and is not specific to Excel, but it plays a crucial role in managing open applications.When you have Excel open, its icon may appear on the taskbar. You can use the taskbar to switch between open applications.

Status Bar:The status bar is located at the bottom of the Excel window and provides information about the current status of the workbook. It includes several sections:

Ready: This section displays the word “Ready” when Excel is waiting for user input.
Zoom Slider: Allows you to adjust the zoom level of the worksheet.
View Shortcuts: Icons for different view modes (Normal, Page Layout, Page Break Preview).
Recording Macro: Appears when you are recording a macro.
Caps Lock, Num Lock, Scroll Lock: Indicators for the state of these keys on your keyboard.
Average, Count, Numerical Sum: These display the average, count, and sum of selected cells when you have a range of cells selected. Right-click on these areas to customize which statistics are displayed.

Range of Cells:

A range of cells is represented by specifying the starting cell and ending cell, separated by a colon. For example, A1:B3 refers to the rectangular range of cells from A1 to B3.

Relative Reference (default in excel): If you copy the formula to another cell, the reference adjusts based on its new location. For example, if you have a formula in cell A1 referencing B1 (=B1), and you copy the formula to cell A2, the reference will automatically adjust to =B2.

Absolute Reference: The reference remains constant, regardless of where you copy the formula. You create an absolute reference by using the dollar sign ($). For example, if you have a formula in cell A1 referencing B1 with an absolute reference (=$B$1), and you copy the formula to cell A2, it still references B1.

Mixed Reference: type of absolute reference in which either the Column is made constant or the row is made constant. Examples:

=$A1 is a mixed reference with an absolute column and a relative row.
=A$1 is a mixed reference with a relative column and an absolute row.

Microsoft Excel time-related functions

NOW(): Returns the current date and time.
TODAY(): Returns the current date without the time.
TIME(hour, minute, second): Creates a time value based on the specified hour, minute, and second. =TIME(12, 30, 0) // Returns 12:30:00 PM
Year : Year of a Date [ Range 1900 -9999]
WEEKDAY : Day number of a Week [1 to 7 ].
WEEKNUM : Week number of a Year .
MONTH (serial_number): Returns the Month portion of a time value.
HOUR (serial_number): Returns the hour portion of a time value.
MINUTE(serial_number):Returns the minute portion of a time value.
SECOND(serial_number):Returns the second portion of a time value.
DATEDIF(start_date, end_date, “unit”):Calculates the difference between two dates in years, months, or days.
TIMEVALUE(time_text):Converts a time in the form of text to a serial number.
TEXT(value, format_text):Converts a value to text according to a specified format.
NETWORKDAYS(start_date, end_date, [holidays]):Returns the number of whole working days between two dates, excluding weekends and optionally specified holidays.
EDATE(start_date, months):Returns the serial number of the date that is the indicated number of months before or after the start date.
DATEVALUE : convert a text date in a cell to a serial number.

Microsoft Excel Logical functions

IF: Returns one value if a condition is true and another value if it’s false. Allows to make logical comparisons.
AND : Returns TRUE if all the arguments are true, and FALSE if any of the arguments are false.
OR : Returns TRUE if at least one of the arguments is true, and FALSE if all the arguments are false.
NOT: Reverses the logical value, returning TRUE if the argument is false, and FALSE if the argument is true.
EXACT : function returns TRUE if the two text strings are exactly the same and FALSE if they are different.
IFERROR : Returns a value you specify if a formula evaluates to an error, and the formula result otherwise.
COUNT(value1, [value2], …): Counts the number of cells that contain numbers in a range.
COUNTIF(range, criteria): Counts the number of cells within a range that meet a specific condition.
COUNTA(value1, [value2], …): Counts the number of non-empty cells in a range.
COUNTBLANK(range): Counts the number of blank cells in a range.
VLOOKUP function : used to search for a value in the first column of a table or range, and then return a value in the same row from a specified column.
Floor : return the nearest largest integer less than or equal to a given number
Ceiling : return the nearest largest integer more than or equal to a given number.
ROUND : [ROUND(1.6666666666)= 1.67]
ABS : Absolute value of a number
Nested Function : function inside other functions.
MATCH(lookup_value, lookup_array, [match_type]): Searches for a value in a range and returns the relative position of that item.
- lookup_value: The value you want to find.
- lookup_array: The range of cells containing possible matches.
- match_type: Optional. Specifies the type of match. Use 0 for an exact match, 1 for less than, and -1 for greater than.
Dollar$ is used to create an absolute address in excel.
Concatenate/ampersand (&) is used to join or combine.
Colon (:) : Represents Range
Comma(,) : Represents Union.
CLEAN : removes all non printable characters from text.
Char : returns the character with the given ASCII decimal Code.

Text Functions in Excel

CONCATENATE:

Concatenates (joins together) multiple text strings into one.
Example: =CONCATENATE(A1, ” “, B1) combines the text in cell A1 with a space and the text in cell B1.

LEFT:

Returns a specified number of characters from the beginning of a text string.
Example: =LEFT(A1, 5) extracts the leftmost 5 characters from the text in cell A1.

RIGHT:

Returns a specified number of characters from the end of a text string.
Example: =RIGHT(A1, 3) extracts the rightmost 3 characters from the text in cell A1.

MID:

Returns a specific number of characters from a text string, starting at the position you specify.
Example: =MID(A1, 3, 5) extracts 5 characters from cell A1, starting from the 3rd character.

LEN:

Returns the number of characters in a text string.
Example: =LEN(A1) returns the length of the text in cell A1.

LOWER and UPPER:

Converts text to lowercase or uppercase, respectively.
Example: =LOWER(A1) converts the text in cell A1 to lowercase.

PROPER:

Capitalizes the first letter of each word in a text string.
Example: =PROPER(A1) capitalizes the first letter of each word in the text in cell A1.

TRIM:

Removes extra spaces from a text string, except for single spaces between words.
Example: =TRIM(A1) removes extra spaces from the text in cell A1.

SUBSTITUTE:

Replaces occurrences of a specified substring with another substring in a text string.
Example: =SUBSTITUTE(A1, “old”, “new”) replaces “old” with “new” in the text in cell A1.

FIND and SEARCH:

Locate the position of a substring within a text string.