Basics and Advanced MS-EXCEL Shortcut Keys and Formulas

Learn easily all important basic or advanced excel shortcut keys and formulas 

Basic MS-EXCEL 

Here’s a list of some common MS Excel shortcut keys and how to use them:

Basic Navigation & Selection Shortcuts:

1. Ctrl + Arrow Keys: Move to the edge of data region in a worksheet.
• Example: Ctrl + Right Arrow moves the cursor to the last column of the current row with data.
2. Shift + Arrow Keys: Extend selection by one cell in the chosen direction.
• Example: Shift + Down Arrow selects the next cell down.
3. Ctrl + Shift + Arrow Keys: Select all data in the direction of the arrow key.
• Example: Ctrl + Shift + Right Arrow selects all data to the right of the selected cell.
4. Ctrl + Space: Select the entire column.
5. Shift + Space: Select the entire row.

Basic File Shortcuts:

1. Ctrl + N: Create a new workbook.
2. Ctrl + O: Open an existing workbook.
3. Ctrl + S: Save the current workbook.
4. F12: Open the "Save As" dialog box.
5. Ctrl + P: Open the print menu.

Editing & Formatting Shortcuts:

1. Ctrl + C: Copy the selected cells.
2. Ctrl + X: Cut the selected cells.
3. Ctrl + V: Paste the copied/cut data.
4. Ctrl + Z: Undo last action.
5. Ctrl + Y: Redo last undone action.
6. Ctrl + B: Bold the selected text.
7. Ctrl + I: Italicize the selected text.
8. Ctrl + U: Underline the selected text.

Data Manipulation:

1. Alt + E + S + V: Paste special (values).
2. Alt + H + D + C: Delete the selected column.
3. Alt + H + D + R: Delete the selected row.
4. Ctrl + T: Create a table from selected data.

Formula & Calculation Shortcuts:

1. Alt + =: Auto-sum selected range.
2. Ctrl + Shift + Enter: Enter an array formula.
3. F2: Edit the active cell (places the cursor in the cell).
4. Ctrl + ` (backtick): Toggle between showing cell formulas and values.
5. F9: Calculate all worksheets in all open workbooks.

Worksheet Shortcuts:

1. Ctrl + Page Up/Page Down: Move to the next/previous worksheet.
2. Shift + F11: Insert a new worksheet.
3. Alt + O + H + R: Rename the current worksheet.

Filter and Find/Replace:

1. Ctrl + F: Open the Find dialog.
2. Ctrl + H: Open the Replace dialog.
3. Ctrl + Shift + L: Toggle filters on/off.
4. Alt + Down Arrow: Opens the filter dropdown menu when a filter is applied.

Miscellaneous:

1. Ctrl + 1: Open the "Format Cells" dialog box.
2. Ctrl + 9: Hide the selected rows.
3. Ctrl + 0: Hide the selected columns.
4. Alt + F11: Open the Visual Basic Editor.

These shortcuts can significantly boost your efficiency when working with Excel. To use them, simply hold down the modifier key (e.g., Ctrl) and press the corresponding key.

 Learn easily all important advanced excel shortcut keys and formulas with examples that helped in your exams and interviews. Here you learned so many things so Urtechconnection blog always ready to help you. I hope, it's helpful for you please follow also our "Yourtechconnection" YouTube channel .

Read More Blog..

1. Increased more traffics on Blog or Website 
2. Earning Apps and Website , Ai Tools 

 Advanced MS-EXCEL | Shortcut keys  or Formulas

Advanced ms-excel formulas and short-cut keys and How to use it ?

Here are some advanced Excel formulas and their usage, along with shortcut keys that can help you work more efficiently:

---

Advanced Excel Formulas:

1. INDEX + MATCH (A Better Alternative to VLOOKUP)

• Purpose: This combination allows you to search for values in any direction (unlike VLOOKUP, which only works left-to-right).
• Formula:

Code

=INDEX(range_to_return_value_from, MATCH(lookup_value, range_to_lookup_in, 0))

• Example:

code

=INDEX(B2:B10, MATCH(E2, A2:A10, 0))

This will return the value from the B column where the value in the A column matches the lookup value in E2.

2. XLOOKUP (Advanced Lookup Function)

• Purpose: A modern alternative to VLOOKUP and HLOOKUP, XLOOKUP can search both horizontally and vertically.
• Formula:

code

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• Example:

code

=XLOOKUP(E2, A2:A10, B2:B10)

This returns the value in B2

that corresponds to the match found in A2

for E2.

3. SUMIFS (Multiple Criteria Summing)

• Purpose: Sum values based on multiple criteria.
• Formula:

code

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)

• Example:

code

=SUMIFS(C2:C10, A2:A10, "North", B2:B10, ">100")

This sums the values in C2

where the corresponding values in A2

are "North" and in B2

are greater than 100.

4. ARRAYFORMULA (Ctrl + Shift + Enter) (Create Dynamic Arrays)

• Purpose: Apply a formula to an entire range of data without copying it down.
• Formula:

code

{=formula}

• Example:

code

{=A1:A10 * B1:B10}

This multiplies each value in A1

by its corresponding value in B1

.

5. TEXTJOIN (Combine Text from Multiple Cells)

• Purpose: Combine multiple cells into one cell with a specified delimiter.
• Formula:

code

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

• Example:

code

=TEXTJOIN(", ", TRUE, A2:A10)

This combines all non-empty cells in A2

into one cell, separated by a comma and space.

6. IFERROR (Handle Errors Gracefully)

• Purpose: Return a custom result when an error is encountered, such as in lookup formulas.
• Formula:

code

=IFERROR(formula, value_if_error)

• Example:

code

=IFERROR(VLOOKUP(E2, A2:B10, 2, FALSE), "Not Found")

This returns "Not Found" if the VLOOKUP fails.

7. ARRAY FORMULAS with SEQUENCE

• Purpose: Generate a series of numbers or perform array calculations across rows/columns.
• Formula:

code

=SEQUENCE(rows, [columns], [start], [step])

• Example:

code

=SEQUENCE(5, 1, 1, 2)

This generates the sequence 1, 3, 5, 7, 9 across 5 rows.

8. LET Function (Assign Variables in a Formula)

• Purpose: Simplifies formulas by allowing you to store intermediate calculations in variables.
• Formula:

code

=LET(name1, name_value1, calculation)

• Example:

code

=LET(x, A1 + B1, x * 2)

In this case, x stores the sum of A1 and B1, and then multiplies that sum by 2.

9. FILTER (Filter Data Based on Criteria)

• Purpose: Dynamically filter data based on one or multiple criteria.
• Formula:

code

=FILTER(range, condition1, [condition2], …)

• Example:

code

=FILTER(A2:B10, A2:A10 = "East")

This filters the range A2

where the value in A2

is "East".

10. SPILL Range (Dynamic Arrays)

• Purpose: Automatically fills adjacent cells with an array result.
• Example:

code

=A2:A10 * B2:B10

This formula automatically populates the result of multiplying A2

and B2

across multiple cells without needing to drag.

---

Advanced Excel Shortcut Keys:

Formula Shortcuts:

1. Ctrl + Shift + Enter: Enter an array formula.
• Example: For matrix operations, this shortcut creates a formula that works across ranges.
2. Ctrl + ` (backtick): Toggle between showing formulas and values in cells.
• Helps you check formulas across large data sets.
3. Alt + =: Automatically insert the SUM function.
• Quickly totals numbers in a selection.
4. Ctrl + A (in formula bar): Open the function arguments window for a selected function.

Pivot Table Shortcuts:

1. Alt + N + V: Insert a new Pivot Table.
2. Alt + J + T + I: Activate Pivot Table Field List for selecting/deselecting fields.
3. Alt + Shift + Left Arrow: Move a selected field up one level in a Pivot Table.

Data Analysis Shortcuts:

1. Alt + H + O + I: Auto-fit column width based on cell content.
2. Alt + D + F + F: Apply/remove filters from the selected range.
3. Ctrl + T: Convert a data range into a table, enabling easier sorting/filtering.

Navigation & Selection Shortcuts:

1. Ctrl + Shift + L: Turn on/off filters.
2. Ctrl + Shift + Space: Select the entire table or range of data.
3. Ctrl + Shift + * (asterisk): Select the current region around the active cell (useful in selecting datasets).

Cell Formatting & Adjustment:

1. Ctrl + 1: Open the Format Cells dialog box.
2. Ctrl + Shift + $: Apply currency format to the selected cells.
3. Ctrl + Shift + %: Apply percentage format to the selected cells.

Conditional Formatting:

1. Alt + O + D: Opens the "Conditional Formatting" rules manager.
2. Alt + H + L: Opens the "Conditional Formatting" menu directly.

Formula Auditing:

1. Ctrl + [ (opening bracket): Selects all cells referred to by formulas in the selection.
2. Ctrl + ] (closing bracket): Selects all cells that contain references to the active cell.
3. Alt + M + P: Trace Precedents (shows arrows to cells feeding into the selected formula).
4. Alt + M + D: Trace Dependents (shows arrows from the selected formula to the dependent cells).

Chart Shortcuts:

1. F11: Create a chart with the selected data in a new sheet.
2. Alt + F1: Create a chart with the selected data in the current sheet.

---

These advanced formulas and shortcuts will enable you to efficiently handle complex data analysis and manipulation tasks in Excel. Make sure to practice these frequently to boost your productivity.

Filters and sort the data in ms-excel

Sorting and Filtering in MS Excel helps you manage and analyze data efficiently. Here’s how you can do it:

---

Filtering Data in Excel:

Filters allow you to display only the rows that meet specific criteria while hiding the rest. Here's how to filter data:

Steps to Apply Filters:

1. Select your data: Click on any cell within the range or table you want to filter.
2. Apply Filter:
• Shortcut: Press Ctrl + Shift + L to apply filters to the selected range.
• Alternatively, go to the Data tab and click on the Filter button in the toolbar.
3. Filter the Data:
• After the filter is applied, a small dropdown arrow will appear on each column header.
• Click on the arrow for the column you want to filter.
• You’ll see a checklist of unique values in that column.
• To filter by specific values, uncheck Select All, then check the values you want to show.
• You can also use Text Filters, Number Filters, or Date Filters based on the type of data in the column.

Filter by Condition:

1. Text Filters:
• Click the filter arrow on a text column.
• Select Text Filters, then choose conditions like Equals, Contains, or Begins With to filter text data.
2. Number Filters:
• For a number column, choose Number Filters, and then select options like Greater Than, Less Than, Between, etc.
3. Date Filters:
• If you have date data, click on the filter arrow and select Date Filters. You can filter by Today, This Week, Next Month, etc.

Clear Filters:

• Shortcut: Press Alt + D + F + F to toggle filters off and reset the view.
• Alternatively, click on the filter dropdown arrow and choose Clear Filter from [Column Name].

---

Sorting Data in Excel:

Sorting allows you to reorder data alphabetically, numerically, or by date. You can sort data based on one or multiple columns.

Steps to Sort Data:

1. Select your data: Highlight the data you want to sort, or click on a cell in the column you want to sort by.
2. Sort by Single Column:
• A-Z Sorting:
• Press Alt + A + S + A to sort the selected column in ascending order.
• Alternatively, go to the Data tab and click Sort A to Z (for text) or Sort Smallest to Largest (for numbers).
• Z-A Sorting:
• Press Alt + A + S + D to sort the selected column in descending order.
• Or click Sort Z to A (for text) or Sort Largest to Smallest (for numbers) in the Data tab.

Sort by Multiple Columns (Custom Sort):

1. Go to the Data Tab: Click on Sort in the Data tab (or press Alt + D + S).
2. Add Sorting Levels:
• In the Sort dialog box, click Add Level.
• Choose the primary column to sort by from the Column dropdown, then select the sort Order (A-Z, Z-A, etc.).
• To add more sorting conditions, click Add Level again, choose another column, and specify the sort order.
3. Sorting with Multiple Criteria:
• You can first sort by a major category (e.g., Region), and then within each region, sort by a secondary category (e.g., Sales Amount).
• For example: First, sort by Region in A-Z order, then by Sales in descending order.

Sort by Color or Icon:

If you’ve applied conditional formatting (colors or icons), you can sort by those too:

1. In the Sort dialog box, choose the column containing the colors or icons.
2. In the Sort On dropdown, select Cell Color or Cell Icon.
3. Choose the order of the colors or icons (which color/icon should appear first, second, etc.).

---

Example of Filter and Sort in Excel:

Assume you have the following dataset:

Name	Age	Department	Salary
John	28	HR	50000
Alice	34	IT	70000
Bob	25	IT	60000
Charlie	30	HR	65000

Filter Example:

• To show only the rows where Department is "IT":
1. Click the filter arrow on the Department column.
2. Uncheck Select All, then check only IT.
3. Only the rows with "IT" will be displayed.

Sort Example:

• To sort by Age in ascending order:
1. Click anywhere in the Age column.
2. Press Alt + A + S + A (or click Sort Smallest to Largest in the Data tab).
3. The data will be sorted by age, from youngest to oldest.

---

Shortcut Keys for Filters and Sorting:

• Ctrl + Shift + L: Toggle filters on/off.
• Alt + Down Arrow: Opens the filter menu for the current column.
• Alt + A + S + A: Sort in ascending order (A-Z or Smallest to Largest).
• Alt + A + S + D: Sort in descending order (Z-A or Largest to Smallest).
• Alt + D + F + F: Apply/remove filters (same as Ctrl + Shift + L).

By mastering filters and sorting, you can quickly extract and organize the exact data you need in Excel, making it easier to analyze large datasets efficiently.