Dynamic Chart

Dynamic chart

A dynamic chart range is a data range that updates automatically when you change the data source.This dynamic range is then used as the source data in a chart. As the data changes, the dynamic range updates instantly which leads to an update in the chart.

follow the steps

1.   Click the Insert tab.

2.   In the Tables group, click Table.

3.   Excel will display the selected range, which you can change. If the table does not have headers, be sure to uncheck the My Table Has Headers option.

4.   Click OK and Excel will format the data range as a table.

     Any chart you build on the table will be dynamic. To illustrate, create a quick column chart as follows:

1.   Select the table.

2.   Click the Insert tab.

3.   In the Charts group, choose the first 2-D column chart in the Chart drop down.

     Now, update the chart by adding values for March and watch the chart update automatically.

Excel Anatomy - Create Your Own Ribbon

Create own ribbon

1. Right click on ribbon area and select “customize ribbon” option
2. Now, add a new tab (or group or both) – see below for illustration.
3. Add a few commands (or buttons) to your new ribbon 
4. Click ok and you have a sparkling new ribbon ready.

 

 

1. Use New Tab button to create a new ribbon tab.
2. Use New Group button to add a new group of commands to an existing or new ribbon.
3. Rename button helps you to change the name of an existing custom group or tab.
4. Once you add a group / tab, you have to select it to add items to that group / tab.
5. You can choose the type of commands you want to add to your ribbon tab / group. You can also add any macros as well (sweet!).
6. Now select the command you want to add to your group
7. Click on “Add” button to add the command to your ribbon tab / group.
8. You can use “Remove” button to remove any commands from custom tabs / groups.
9. Use the up / down arrow buttons to move your ribbon tab / group up or down. (For eg. you can move your custom tab to first, ie before home tab).

    A. You can export your ribbon customizations and re-use them in other computers (both ribbon and QAT settings will be exported).

        You can now add a group of commands (for eg. all alignment options) to Quick Access Toolbar to improve your productivity.

 

Visual basic interface

Visual basic interface

     Visual Basic is a computer programming language allows the creation of user-defined functions and the automation of specific computer processes and calculations. Users do not have to buy a copy of Visual Basic professional because Visual Basic for Applications is a standard feature of Microsoft Office products. Visual Basic for Applications (VBA) allows users additional customization beyond what is normally available in Microsoft Office products, such as Excel, Access, Word and PowerPoint.

How to open the VBA environment
     
Select the Developer tab from the toolbar at the top of the screen. Then click on the Visual Basic option in the Code group.

Now the Microsoft Visual Basic editor should appear and you can view your VBA code.

Project Explorer

The Microsoft Visual Basic for Applications window displays your VBA environment in Excel 2010:

Next, let's take a few moments to analyze the various sections in the Microsoft Visual Basic window.

The Project Explorer can usually be found in the top left portion of the Microsoft Visual Basic window. It is a hierarchical listing of the objects recognized by VBA.

In this example, there are four Excel objects which represent each sheet and workbook in your Excel file - Sheet1, Sheet2, Sheet3, and ThisWorkbook.

There is also one module called Module1. The VBA code in Module1 can used anywhere in your spreadsheet. Whereas, the code within an Excel object is typically only used by that object.

If the Project Explorer is not visible when you open the Microsoft Visual Basic for Appliations window, you can make it visible by selecting Project Explorer under the View menu.

Properties Window

The Microsoft Visual Basic for Applications window displays your VBA environment in Excel 2010:

Next, let's analyze the Properties window in the VBA environment.

The Properties window is usually found directly below the Project Explorer in Excel 2010. It displays the properties for the object currently highlighted in the Project Explorer.

In the example above, it is displaying the properties for the module called Module1.

If the Properties Window is not visible when you open the Microsoft Visual Basic for Applications window, you can make it visible by selecting Properties Window under the View menu. 

Code Window

The Microsoft Visual Basic for Applications window displays your VBA environment in Excel 2010:

Next, let's analyze the Code window in the VBA environment. The Code window is usually found to the right of the Project Explorer in Excel 2010. It displays the VBA code for the object currently highlighted in the Project Explorer.

In the example above, it is displaying the VBA code for the module called Module1.

If the Code window is not visible when you open the Microsoft Visual Basic for Applications window, you can make it visible by selecting Code under the View menu.

Immediate Window

The Microsoft Visual Basic for Applications window displays your VBA environment in Excel 2010:

Next, let's analyze the Immediate window in the VBA environment.

In Excel 2010, the Immediate window is usually found below the Code window. It is an essential element of the debugger found within the VBA environment. It lets you:

• Type code and press ENTER to view the results of the code.
• When in debug mode, it lets you view the value of a variable in its current state. This will be discussed in the tutorial on Debugging VBA Code.

If the Immediate window is not visible when you open the Microsoft Visual Basic for Applications window, you can make it visible by selecting Immediate Window under the View menu.

Watch Window

The Microsoft Visual Basic for Applications window displays your VBA environment in Excel 2010:

Next, let's analyze the Watch window in the VBA environment.

In Excel 2010, the Watch window is usually found below the Code window. It is one of the most valuable tools when debugging in the VBA environment. It lets you:

• Define and monitor any expression.
• When in debug mode, it lets you view the value of the watched expression in its current state. This will be discussed in the tutorial on Debugging VBA Code.

If the Watch window is not visible when you open the Microsoft Visual Basic for Applications window, you can make it visible by selecting Watch Window under the View menu.