Add-Ins, Relative, Absolute _ Mixed Addressing

To extend the functionality of MS-Excel you can install extra add-ins in Excel. By default, you’ll not see any additional add ins installed in Excel. You have to manually install and activate plugins to gain extra features and optional commands in to your Excel software. 

MS-Excel Add-ins

You can install add-ins to extend the functionality of Excel across multiple platforms including Windows, Mac, iPad OS and in the browser. 

You can use Excel add-ins in a workbook to have the following features:

• Interact with Excel objects, read and write Excel data.
• Extend functionality using web-based task pane or content pane
• Add custom ribbon buttons or contextual menu items
• Add custom functions
• Provide richer interaction using dialog window

Capabilities of an Excel Add-in:

• Add in Commands

Add-in commands are UI elements that extend the Excel UI and start actions in your add-in. You can use add-in commands to add a button on the ribbon or an item to a context menu in Excel. When users select an add-in command, they initiate actions such as running JavaScript code, or showing a page of the add-in in a task pane.

• Task Panes

Task panes are additional UI elements which usually appear to the right side of your workbook. These task panes provide extra features and functionality via third party add-ins which are not pre-built into excel.

• Custom Functions

If your workflow requires some functions which are not default functions of Excel and If you have an expression which you use many a times while working in your workbook, you may want to not write the expression every time you want to calculate something. This can be done by using custom functions. 

A custom function’s name and no. of parameters are defined by the developer and the function body includes the expression which needs to be calculated in order to do faster calculations.

Custom functions enable developers to add new functions to Excel by defining those functions in JavaScript as part of an add-in. Users within Excel can access custom functions just as they would any native function in Excel, such as SUM().

• Content Add-ins

Content add-ins are surfaces that you can embed directly into Excel documents. You can use content add-ins to embed rich, web-based objects such as charts, data visualizations, or media into a worksheet or to give users access to interface controls that run code to modify the Excel document or display data from a data source. Use content add-ins when you want to embed functionality directly into the document.

• Dialog Box

When some sign-in function or any extra features which cannot be implemented in the ribbon, toolbars or task panes, Dialog boxes could help.

They can help you to set the settings for the add-ins you have installed or to sign-in to a third-party service or API to access services outside Excel.

How to add, remove and activate/inactivate Add-Ins in MS-Excel:

To activate an Excel add-in    

1. Click the File tab, click Options, and then click the Add-Ins category.
2. In the Manage box, click Excel Add-ins, and then click Go.

The Add-Ins dialog box appears.

3. In the Add-Ins available box, select the check box next to the add-in that you want to activate, and then click OK.

If you cannot find the add-in that you want to activate in the Add-Ins available box, you may need to install it.

To install an Excel add-in    

• To install an add-in that is typically installed with Excel, such as Solver or the Analysis ToolPak, run the setup program for Excel or Microsoft Office and choose the Change option to install the add-in. After you restart Excel, the add-in should appear in the Add-Ins available box.
• Some Excel add-ins are located on your computer and can be installed or activated by clicking Browse (in the Add-Ins dialog box) to locate the add-in, and then clicking OK.
• Some Excel add-ins require running an installation package. You may need to download or copy the installation package to your computer (an installation package is typically a file with an .msi file name extension), and then run it.
• Other add-ins that are not available on your computer can be downloaded and installed through a Web browser from Downloads or from other sites on the Internet or a server in your organization. Follow the setup instructions for the download as needed.

To inactivate an Excel add-in    

1. Click the File tab, click Options, and then click the Add-Ins category.
2. In the Manage box, click Excel Add-ins, and then click Go.
3. Then in Add-Ins available box, clear the check box next to the add-in that you want to inactivate, and then click OK.

In most of the cases, when you inactivate an add-in, it is removed from its group on the ribbon. In other cases, to remove the add-in from the ribbon, you may need to restart Excel.

Note    Inactivating an add-in does not remove the add-in from your computer. To remove the add-in from your computer, you must uninstall it.

To remove an Excel add-in    

Important: If you originally installed the Excel add-in from a network file server or from a shared folder, you might need to remove the add-in from that location. If you installed the Excel add-in from a CD and you mapped your CD drive to a new drive letter after installing the add-in, you need to reinstall the add-in from the CD. If you are running any Excel add-ins from the CD, you must uninstall the Excel add-ins, and then reinstall them from the CD.

1. Click the File tab, and then click Exit.
2. In Control Panel, click Programs and Features (Windows 7 and Windows Vista) or Add/Remove Programs (Windows XP).
3. Do one of the following:
   • If you installed Excel as part of Microsoft Office, click Microsoft Office in the list of installed programs, and then click the Change button.
   • In case of Excel installed individually, click the name of your program in the list of installed programs, and then click the Change button.
   • If you installed the add-in from the Download Center, click the name of your program in the list of installed programs, and then click the Uninstall button.
4. Follow the instructions in the installation program.

Addressing Modes in Excel:

Microsoft Excel has mainly three addressing modes:

1. Relative Addressing
2. Absolute Addressing
3. Mixed Addressing

Relative Addressing

When you reference cells in excel formulas without the dollar $ sign it is Relative addressing.

Relative addressing is useful when you want to copy a formula in another cell. When you use relative addressing, excel changes the formula into the copied cell based on a relative position of rows and columns. By default, all references in Excel are relative.

For e.g.,

When you copy the formula from B1 cell to B2 cell, Excel automatically changes the new formula by referencing the next row.

Absolute Addressing

When you use the $ sign to reference a cell in a formula, it is Absolute referencing.

Absolute reference means when you copy the formula with $ referencing to cells, the formula remains the same over to the copied cell. This is useful when you do not want excel to automatically determine the change in formula. For e.g. if you want the cells to be multiplied to only a single cell every time, absolute referencing will work.

Mixed Referencing

When you want to change a column or row relatively and want another column or a row to be fixed in a formula, Mixed Referencing will help.

A mixed cell reference in Excel is a reference where either the column letter or a row number is fixed. For example, $A1 and A$1 are mixed references.

• Absolute column and relative row, like $A1. When a formula with this reference type is copied to other cells, the $ sign in front of the column letter locks the reference to the specified column so that it never changes. The relative row reference, without the dollar sign, varies depending on the row to which the formula is copied.
• Relative column and absolute row, like A$1. In this reference type, it’s the row’s reference that won’t change, and the column’s reference will.

The table below summarizes how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right.

For a formula being copied:	If the reference is:	It changes to:
	$A$1 (absolute column and absolute row)	$A$1 (the reference is absolute)
	A$1 (relative column and absolute row)	C$1 (the reference is mixed)
	$A1 (absolute column and relative row)	$A3 (the reference is mixed)
	A1 (relative column and relative row)	C3 (the reference is relative)

Sources

1. https://support.office.com/en-us/article/switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9