Formulas Ribbon Tab

Introduction:

The “Formulas” ribbon tab in Microsoft Excel is one of the primary tabs located at the top of the Excel window. The “Formulas” tab is dedicated to working with formulas and functions in your Excel worksheets. It provides various tools and options to create, manage, and troubleshoot formulas, allowing you to perform calculations and manipulate data efficiently.

Here are some common groups of commands you’ll find on the “Formulas” tab of Microsoft 365.

1. Function Library
2. Defined Names
3. Formula Auditing
4. Calculation

Function Library: This group contains a vast collection of built-in functions that you can use to perform various calculations. Functions are categorized by type, such as mathematical, statistical, logical, date and time, text, and more.

A. Insert Function: Use the Insert Function dialog box to help you insert the correct formula and arguments for your needs.

B. AutoSum: Automatically add a new function to your worksheet such as sum or average. Shortcut Key: Alt + =

C. Recently Used: Choose the recently used functions quickly.

D. Financial: The finance function is used to add a function for performing financial calculations and analysis such as present value calculation, preparing loan amortization schedules, valuation, and cash flow analysis.

E. Logical: The logical function is used to build complex logical conditions in Excel. such logical function helps users to automate decision-making processes based on conditions. Some of the examples are AND, OR, NOT, IFERROR, etc.

F. Text: This function is used to work while manipulating and working with the textual data within Excel cells. It includes functions like concatenate, left, right, mid, etc.

G. Date & Time: This function allows users to work with date and time data present in the Excel spreadsheets. Some of the date and time functions are today, Now, Date, Time, DATEIF, EOMONTH, etc.

H. Lookup & Reference: These functions are used for searching, retrieving, and referencing data from and within a spreadsheet or from the specified data range.

There are a couple of lookup functions, viz VLOOKUP and HLOOKUP, in the advanced version of Excel now you have XLOOKUP as well. Other reference functions are Index and Match.

I. Math & Trig: This function is used to perform mathematical and trigonometric calculations on the data present in Excel. Some of the functions in this category are SUM, SIN, COS, SQRT, POWER, RADIANS, ABS etc.

J. More Functions: Browse more functions from categories such as statistical, engineering, cube, web, information, and compatibility.

Defined Names: The “Defined Names” group allows you to manage, and use named ranges in your workbook. Named ranges make it easier to refer to specific cells or ranges in formulas.

1. Name Manager: you can find all the names used in a workbook; names can also be used as a substitute in formulas to reference cell numbers e.g. SUM (My Sales) instead of SUM (C20:C30).
2. Define Name: Define and apply names.
3. Use in Formula: you can choose a name used in your workbook to use in your current formula.
4. Create from selection: you can automatically generate the names from the selected cells.

Formula Auditing: This group provides tools for checking and debugging formulas. You can trace precedents and dependents, evaluate formulas step-by-step, and identify errors.

A. Trace Precedents: Show arrows that indicate which cells affect the value of the currently selected cell.

Shortcut: Ctrl + [

B. Trace Dependent: This shows you the arrow to which cells are affected by the currently selected cell.

Shortcut: Ctrl + ]

C. Remove Arrow

1. 1. Remove Arrows: It removes the arrows drawn by Trace Precedents or Dependents.
   2. Remove Precedent Arrows: It removes the Trace Precedents arrows.
   3. Remove Dependent Arrows: It removes the arrows generated by Trace Dependent

D. Show Formula: you can use this to display the formula in each cell of your sheet instead of the value.

E. Error Checking

1. 1. Error Checking: Check for common errors that occur when using formulas.
   2. Trace Error: Shows arrows to which cells caused the error in the currently selected cell.

F. Evaluate Formula: you can use this option to evaluate your formula, not only evaluate the entire formula but parts of your formula individually.

G. Watch Window: you can add cells to the watch window list so that you can see the value changing as you update the values of other cells even on a different sheet.

Sheet Options: The “Sheet Options” group provides checkboxes for controlling the visibility of gridlines and heading elements on the printed page.

There are four checkboxes in this group two are for gridlines and two are for the headings. These two checkboxes are view and print meaning whether you wanted to view that function in the spreadsheet and print is to have that function printed on the printout.

1. Gridlines
   1. View
   2. Print
2. Headings
   1. View
   2. Print

Arrange: This group contains commands for organizing objects, such as images or shapes, within your worksheet. You can align, group, or position objects precisely.

1. Bring Forward: This command brings the selected object in front of all the other objects.
2. Send Backward: This command sends the selected object behind all the other objects.
3. Selection Plane: This command shows a list of all the objects, it makes it easier for the users to select the objects and change their order and visibility.
4. Align: The Align command helps the alignment of the objects in various positions such as left, right, middle, center, top, and bottom of the page.
5. Group: This command helps to group various objects into one.
6. Rotate: This command helps to rotate the object in any degree of orientation.