Never Stop Learning

Education

• INDEX-MATCH:

INDEX: Returns the value of a cell in a specified row and column of a given range.
MATCH: Searches for a value in a range and returns the relative position of that value.
Example: =INDEX(B2:B10, MATCH(“SearchValue”, A2:A10, 0)) – This formula will find “SearchValue” in the range A2:A10 and return the corresponding value from B2:B10.

• SUMIFS / COUNTIFS / AVERAGEIFS:

SUMIFS: Calculates the sum of values in a range that meet multiple criteria.
COUNTIFS: Counts the number of cells in a range that meet multiple criteria.
AVERAGEIFS: Calculates the average of values in a range that meet multiple criteria.
Example: =SUMIFS(C2:C10, A2:A10, “Criteria1”, B2:B10, “Criteria2”) – This formula will sum values in C2:C10 where corresponding values in A2:A10 meet “Criteria1” and values in B2:B10 meet “Criteria2”.

• PivotTable:

A Pivot Table is a dynamic summary tool that allows you to analyze and summarize data from a larger dataset.
Example: Create a PivotTable to analyze sales data, with rows representing product categories and columns representing months, and the values showing total sales.

• IFERROR:

Returns a value you specify if a formula results in an error, otherwise, returns the formula’s result.
Example: =IFERROR(A1/B1, “Error: Division by zero”) – This formula will perform A1/B1 but display “Error: Division by zero” if an error occurs.

• SUMPRODUCT:

Multiplies corresponding components in arrays/ranges and returns the sum of those products.
Example: =SUMPRODUCT(A1:A5, B1:B5) – This formula will multiply each element in A1:A5 with the corresponding element in B1:B5 and sum the results.

• TEXTJOIN:

Combines text from multiple cells into a single cell with specified delimiters and optional criteria.
Example: =TEXTJOIN(“, “, TRUE, A1:A3) – This formula will combine the values in cells A1:A3, separated by commas.

• DATE / TIME Functions:

Excel offers various functions for working with dates and times, such as DATE, TIME, NOW, TODAY, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.
VBA (Visual Basic for Applications):

VBA is a programming language integrated into Excel for creating custom macros and automating tasks.

• CHOOSE:

Returns a value from a list based on a specified index number.
Example: =CHOOSE(3, “Apple”, “Banana”, “Cherry”, “Date”) – This formula will return “Cherry” because 3 is the index chosen.

• ARRAY Formulas:

Array formulas perform calculations on arrays of data. They are created by entering a formula and then confirming it with Ctrl+Shift+Enter.
Example: {=SUM(A1:A10 * B1:B10)} – This array formula calculates the sum of products of corresponding elements in A1:A10 and B1:B10.

F1 = For help

F2 = To Edit

F3 = Paste Name

F4 = Repeat Last Action

F5 = Go To

F6 = Next Pane

F7 = Spelling Check

F8 = Extend mode

F9 = Recalculate All Workbooks

F11 = New Chart

F12 = Save As

Shift+F1 = What’s This?

Shift+F2 = Edit Cell Comment

Shift+F3 = Paste Function Into Formula

Shift+F4 = Find Next

Shift+F5 = Find

Shift+F6 = Previous Pane

Shift+F9 = Calculate Active Worksheet

Shift+F11 = New Worksheet

Shift+F12 = Save

Microsoft word Shortcuts To Learn.

Ctrl+F3 = Define Name

Ctrl+F4 = Close

Ctrl+F5 = Restore Window Size XL

Ctrl+F6 = Next Workbook Window

Ctrl+F7 = Move Window

Ctrl+F8 = Resize Window

Ctrl+F9 = Minimize Window

Ctrl+F10 = Maximize or Restore Window

Ctrl+F11 = Insert 4.0 Macro Sheet

Ctrl+F12 = File Open

Ctrl+A = Select All

Ctrl+B = Bold

Ctrl+C = Copy

Ctrl+D = Fill Down

Ctrl+F = Find

Ctrl+G = Go to

Ctrl+H = Replace

Ctrl+I = Italic

Ctrl+O = Open

Ctrl+P = Print

Ctrl+R = Full Right

Ctrl+S = Save

Ctrl+U = Underline

Ctrl+V = Paste

Ctrl+W = Close

Ctrl+X = Cut

Ctrl+Y = Repeat

Ctrl+X = Undo

Ctrl+X = New Worksheet Amit Singh Rawat: Owner/ Founder of Knowandask a Digital Learning platform to educate yourself learn spread knowledge and share knowledge.

• business solutions
• Knowandask
• Urvashi Sharma