Skip to main content

New Article

How to SUM by matching partial text in Excel

Post a Comment

Introduction to all options of Developer Tab in Excel


Click the Developer tab in your Excel window. Then below menu will appear under Developer tab:

Image 1: Developer tab

In this image you will see Add-Ins and Document Panel Modify options, which is added from Excel 213. In the previous versions of Excel these 2 options are not available. Let's see what can we do with these options of Developer tab step by step.


Code Group:

In the code group total 5 options are available.


Image 2: Code group

Visual Basic icon will help us to show the VB Environment in Excel where you can write codes, whose short cut is ALT+F11. Another shortcut is ALT+L+V.

Macro option will display a list of macros you have written or recorded if you click on it. You can Run, Edit, Create, Delete your macros from this dialog box.

Record Macro is for recording a new macro. After clicking on this button this will change to Stop Recording Macro.

Use Relative References is for using the macro for dynamic cells. As we know that, Excel VBA always works for absolute (example $A$3) reference, but Relative Reference (example A3) option will help you to show your macro result where your cursor placed. After clicking the Record Macro click this Use Relative References option to activate while recording your macro.

Macro Security is for changing the security settings of macros. You can enable or disable macros with or without notification.


Add-Ins Group

There are only 2 options in this group:

Image 3: Add-Ins group

Add-Ins are 2 types in Excel. The first one is Automation Add-Ins. Add-Ins tool will help you to show a list of Add-ins created by you. If you save any excel document as Add-Ins then it will show here. You can run it by selecting the check box. This type of Add-Ins are called Automation Add-Ins. Once you create an Add-Ins You can use this in all workbooks. But the problem is the source code will show in VBE.

The COM Add-Ins means Component Object Model (COM). This type of Add-Ins are professional. These are developed in VB and a .dll file helps to run it on all workbooks without showing the source codes. When a COM Add-in is installed, registry entries are created for the Add-in. COM Add-ins used by Excel are registered in the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins\


Control Group:

Again there are 5 options in Control group.

Image 4: Control group

Insert option will allow you to insert the control buttons, forms to design your own applications.

Image 5: Insert controls

Design Mode will allow you to edit the design of your VB Application. You can work with Form Controls and ActiveX Controls in Design Mode.

Properties option is to display the properties of a specific object you have selected. You can change the caption, background color or others settings of an object.

Clicking on View Code you will be able to view the macro codes written in VB Environment. Please don't think that, you cannot allow to edit macro codes in this window. Yes You Can.

Run Dialog will display a list of custom dialog box or forms, which you have created in VB Environment.


XML Group

Excel can works with XML data. You can create XML file from Excel or create Excel file from XML. There are 6 options are available in this group.

Image 6: XML Group

Source option will allow you to collect the source file you want to convert.

Map Properties will help you to select the fields or columns which you want to convert.

Expansion Packs is the group of files that constitutes a smart document. This tool lets users to attach, delete, create or edit  an XML Expansion Pack.

Refresh data is used to refresh XML connection and data displaying.

Import is used to convert the data from XML to Excel

Export is used to convert the data from Excel to XML.


Modify Group:

In the modify group there is only one option available.

Image 7: Modify Group

Document Panel is used to add the custom information of a document which saved in Microsoft InfoPath. You can view the Excel document info from Home | Info | Properties | Show Document Panel or Advance Properties.
Labels:

Popular posts from this blog

How to display an image in worksheet based on a List or based on IF condition?

Excel can show image on worksheet based on a specific IF condition. So, how to do it? Simple follow the below steps: Step by Step: Step 1: Insert images in your Excel Worksheet. Here I've inserted 5 different types of balls, Football, Cricket, Pool, Basketball, Tennis ball. Note that, All balls are placed into different cell. These are placed in Picture sheet. Image 1: 5 balls placed in 5 different cells and covered photo's wide and height Step 2: In the report sheet, design the report as you wish. I've designed in my way like below: Image 2: Kids asking to Donald Duck, which ball need to throw now Step 3: Make a drop down list "Games" in E6 cell in Report sheet from Data Validation. which is as below: Football Cricket Pool Basketball Tennis You can do an IF function here in E6 in Report sheet, which will meet a certain condition and returned Football, Cricket, Pool, Basketball or Tennis. Step 4: Now the tricky part is...
Read more

Cumulative Closing Balance like a Bank Statement in PivotTable

A Bank Employee in many case needs to calculate the Closing Balance after each transaction in PivotTable like a Bank Statement . But with the help of Calculated Field of a PivotTable , you can only calculate Field with Field . It's not possible to use a formula in Calculated Field where you can mention a single cell with Relative Reference Cell . Because a PivotTable acts like a Table format where you can work with Field or Column Name . A Helping Column (A regular column, that helps to get a partial result where formula applied) can be a good idea. But there is an option in PivotTable by which you can Calculate Cumulative . In this article I will show you, how you can do it. Calculating with Formula: Assume that, you have an Excel file with Debit and Credit transaction of an Account . Image 1: Sample Bank Statement In a Bank Statement , Month wise Cumulative Balance is important. In the Excel sheet, as above, it is very easy to use a formula and calc...
Read more

Value Paste and Formula Paste

In many case, in our professional life, we need to do Copy and Value Paste or Copy and Formula Paste in all most always. Those employees, who works under MIS Department, have to do it lots of time in a single day to prepare reports. It is very time costing task in Office. Many of us use Excel Menubar to do it, others are using Keyboard Shortcut by pressing ALT key (like for Value Paste Press ALT+H+V+V and for Formula Paste Press ALT+H+V+F ). But did you noticed that, these two ways strongly need your attention to do this task. More clearly, if you choose Value Paste from Menubar , then what you need to do? First you should take the mouse in Hand Wheel the mouse in Home menu Then Click on Paste Then you pressure your eyes to find the Value Paste icon And then Click on it to paste it These all steps can take more than 1 second. Am I right? Below is an image of this process: Image 1: Value Paste from Home Menu On the other hand, if you choose the shortcut f...
Read more