Advance Excel Formulas – Advance Excel Shortcuts

Few Advance Excel Formulas:


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: 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.


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.


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.


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.


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.

Advance excel short keys:

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

F10 = Activate Menubar

F11 = New Chart

F12 = Save As

Advance Excel Shift+ functions:

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+F8 = Add To Selection

Shift+F9 = Calculate Active Worksheet

Shift+F10 = Display Shortcut Menu

Shift+F11 = New Worksheet

Shift+F12 = Save

Microsoft word Shortcuts To Learn.

Advance Excel Ctrl + functions:

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+K = Insert Hyperlink

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_Knowandask

Amit Singh Rawat: Owner/ Founder of Knowandask a Digital Learning platform to educate yourself learn spread knowledge and share knowledge.

3 thoughts on “Advance Excel Formulas – Advance Excel Shortcuts

  • business solutions

    Thanks to my father who informed me regarding this webpage, this web site is truly awesome.

  • Knowandask

    Thanks for your comment, we will definitely keep on updating such information for people.

  • Urvashi Sharma

    Mastering formulae, VBA (Visual Basic for Applications) programming, and other Excel features for managing difficult jobs are all part of advanced Excel skills. Excel can be used for more complicated tasks such as data analytics and simulation by experts.

Leave a Reply

Your email address will not be published. Required fields are marked *