Data base function

Database function

Daverage function
    Averages the values in a field (column) of records in a list or database that match conditions you specify.
syntax
    DAVERAGE(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dcount function
    Counts the cells that contain numbers in a field (column) of records in a list or database that match conditions that you specify.
syntax
    DCOUNT(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dcounta function
    Counts the non blank cells in a field (column) of records in a list or database that match conditions that you specify.The field argument is optional. If field is omitted, DCOUNTA counts all records in the database that match the criteria.
syntax
    DCOUNTA(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dget function
    Extracts a single value from a column of a list or database that matches conditions that you specify.
syntax
    DGET(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dmax function
    Returns the largest number in a field (column) of records in a list or database that matches conditions you that specify.
syntax
    DMAX(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dmin function
    Returns the smallest number in a field (column) of records in a list or database that matches conditions that you specify.
syntax
    DMIN(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dproduct function
    Multiplies the values in a field (column) of records in a list or database that match conditions that you specify.
syntax
    DPRODUCT(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dstdev function
    Estimates the standard deviation of a population based on a sample by using the numbers in a field (column) of records in a list or database that match conditions that you specify.
syntax
    DSTDEV(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dstdevp function
    Calculates the standard deviation of a population based on the entire population by using the numbers in a field (column) of records in a list or database that match conditions that you specify.
syntax
    DSTDEVP(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dsum function 
    Adds the numbers in a field (column) of records in a list or database that match conditions that you specify.
syntax
    DSUM(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dvar function
    Estimates the variance of a population based on a sample by using the numbers in a field (column) of records in a list or database that match conditions that you specify.
syntax
    DVAR(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Dvarp function
    Calculates the variance of a population based on the entire population by using the numbers in a field (column) of records in a list or database that match conditions that you specify.
syntax
    DVARP(database, field, criteria)

parameters
Database
     is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
Field
     indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "quantity" or "quantity rate" or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
Criteria
     is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

    

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.