Formulas Tab

The Formula Tab helps you create, manage and analyze calculations within your workbook. It provides access to a variety of pre-defined functions and tools to help you work efficiently with your numerical data, perform complex calculations and also ensure the accuracy of your formulas.

Function Library

Consists of a collection of Excel functions that are categorized by type which makes it easy to find and insert the right function as per your needs.

• Insert Function (Shift + F3): A dialog box of insert function is opened where you can search for the function and select it as per your need. This is mainly used if you are not sure which function to use.
• AutoSum: Quickly sum the values in a range of cells or change it to other common functions such as Average, Count, Max and Min.
• Recently Used: Shows the recently used function in Excel for quick access without a need to search again.
• Financial: Includes the functions that are related to financial calculations such as NPV, IRR, and PMT, which are useful for analyzing loans, investments and financial planning.
• Logical: Includes functions such as IF, AND, and OR, that are useful for logical tests and help for decision-making for your data.
• Text: Contains functions for altering and manipulating text values in your data such as LEFT, RIGHT, CONCATENATE, and TRIM.
• Date & Time: For working with date and times this contains functions such as TODAY, DATE, WORKDAYS, and NETWORKDAYS.
• Lookup & Reference: Includes functions such as VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH and XMATCH to search and refer the data across active workbooks or different workbooks which are key to managing your large datasets.
• Math & Trig: Consists of functions such as SUM, ROUND, SIN, and COS which are useful for a wide range of numerical calculations.
• More Functions: A few more categories such as Statistical, Information and Web functions which offer specialized functions for various advanced calculations.

Defined Names

Allows you to create, manage and organize names for a range of cells making your formulas easier to read and maintain.

• Name Manager (Ctrl + F3): Opens a Name Manager dialog box to create, edit and delete named ranges. It will make your formula more readable, and easier to manage, especially in complex calculations.
• Define Name (Alt, M, M, D): This lets you create a meaningful name for a specific cell or a range of cells which will help you during formula creation instead of using cell references.
• Use in Formula (Alt, M, S): Inserts already existing defined names into your formula helping you to reduce errors.
• Create from Selection (Ctrl + Shift + F3): Automatically create named ranges from the selected ranges based on the labels (headers) in your worksheet.

Formula Auditing

Provides tools for checking and troubleshooting your formulas to ensure consistency in your complex calculations.

• Trace Precedents (Alt, M, P): Shows arrows pointing to the cells that directly affect the value of the selected cell.
• Trace Dependents (Alt, M, D): Shows arrows pointing to the cells that depend on the active selected cell.
• Remove Arrows (Alt, M, A): Clears any tracing arrows from the entire worksheet in a single click.
• Show Formulas (Ctrl + ‘): Switches between displaying formulas and cell values in the worksheet.
• Error Checking (Alt, M, K): Runs a quick scan for the entire worksheet to check if any errors in formulas such as division by zero or if any missing references and also provides a suggestion to fix them.
• Evaluate Formula (Alt, M, V): Shows a step-by-step calculation of a formula one piece at a time which helps you to evaluate formulas and check if any issues exist.
• Watch Window (Alt, M, W): A window pops up where you can monitor the values of key cells even if they exist in different worksheets.

Calculations

Allows you to control when and how the calculations of formulas take place in your workbook such as options for automatic and manual calculation.

• Calculation Options (Alt, M, X): Switches between automatic and manual calculation modes. By default, Excel sets the calculation mode to Automatic which recalculates all the formulas in your workbook the moment you change any value. However, if you switch it to Manual mode, it will only recalculate once you click on “Calculate Now” or “Calculate Sheet”.
• Calculate Now (F9): Calculate all formulas in the Excel workbook as soon as you click on this which is useful when the calculation mode is Manual mode.
• Calculate Sheet (Shift + F9): Calculate Now recalculates in the entire workbook however, this recalculates within the active workbook which can save time in the case of large workbooks.

Conclusion

In Summary, the Formulas Tab is a menu full of resources to manage, create and audit calculations effectively. Also, tools such as Name Manager and Formula Auditing, enhance the clarity and accuracy of your formulas to make your data management more efficient.