The DVAR function is one of several database functions available in MS Excel. If you are not an Excel consultant, but rather a business users, you might be wondering how a Microsoft function in Excel is being called a database function when Microsoft Excel is only a worksheet and not an actual database, such as SQL Server or Microsoft Access. We’ll address that later in the article.

As the name suggests DVAR or Database Variance computes the variance from a range of numerical values. This would just be a simple definition of the function. DVAR can do more complex searches based on criteria we provide. Let us study the Microsoft Excel DVAR function in more detail.

Before we move into the usage of DVAR, a few words on Variance would add to the clarity of our article. Variance is used in Statistics and Probability as a measure of how far a set of numbers are spread out from each other. Suppose the average sales of salespersons in a company is $500,000 per year and if the variance is high (a high figure) denotes that there is a big difference in the sales i.e. some salespersons may be doing extremely well and others very poorly. On the other hand a small variance (a small figure) indicates that the average performance of the salespersons is good even if the overall sales may not be very good.

For our example if we want to find out the variance of sales from a table of data we can use DVAR.  We can also use additional criteria such as finding out the variance of sales for the state of Michigan and for the month of May etc.,

Like all other Microsoft Excel database functions, to use DVAR, there are certain pre-requisites. Do you recall just a while ago we mentioned that DVAR is a database function? So where is our database? We actually don’t use a Database but we present the data inside the spreadsheet in the form of a database. Didn’t get it? Read on.  So what does a database typically have? A database has fields and records.  A record is a group of fields.  So all we need to do in Excel is to present this data in the form of fields and records. Many of you have actually been doing this ever since you used Excel but were unaware of it. Remember your columns and column headers in your spread sheet. These become the fields. A row in your spreadsheet becomes the record. But to use these columns and rows with DVAR we’ll have to remember some fundamental and mandatory rules without which DVAR will not function properly.

  1. Your columns must have headers.
  2.  No empty or blank cells are allowed. So all cells MUST have data in them. ABSOLUTELY no blank rows either.
  3. There must not be data in cells adjacent i.e. touching the cells we want to include in the database.

The syntax of the DVAR function is as follows – DVAR(database, field, criteria). Let’s explore the parameters one by one.
Database – This is what we have been discussing so far.  Let us assume we have the following fields i.e. column headers Salesperson, State, Month(Date), Sales and Grade

Field – Is the field or column where we want to determine the variance, in our case the Sales field.

Criteria – Now here is where you can add other conditions, one or more.  To specify criteria we have to do some additional steps. In an unused portion of your spreadsheet create an identical table, this time with two (for clarity) row cells Field Name and Criteria, exactly one on top of the other. This is a mandatory condition. So the row of headers will contain the following headers, Field Name, Salesperson, State, Month(Date) Sales and Grade. You will fill in the cell just below Field Name with the word Criteria. This table must not be touching any cells of the main database table.

We’ll now look at some examples of DVAR computations using criteria.

First let us name the database here so that we may just have to refer to it by name instead of selecting it each time. To name the database select all the fields and all the rows (Ctrl+down arrow). Next name the selection by entering name in the Name field (top left) and this becomes the name of our database. Let’s say our database is named, Sales1.

So at the cell where we want to add the DVAR function enter =DVAR(Sales1,

Next, click on the cell header of the column where you are going to compute the variance. In our example we click on the Sales column header. The cell is automatically inserted into formula. Alternatively you can also enter the name of the column header as “Sales” or double click or use F9.

Next we need to input the criteria. For criteria we need to click on the field we wish to use in the criteria. In our example we wish to compute the variance of sales in the state of Michigan and so we enter “Michigan” in the cell below the header cell that is titled State in the separate data table we created for field and criteria. Now click the field State and drag in the value for the field for criteria also from the Criteria row where we had entered “Michigan”.

The MS Excel DVAR function will now have the criteria data embedded inside the formula and the variance is computed and displayed in the cell.

The final formula will look something like this – =DVAR(Sales1, “Sales”, I14:I15)

Note that as we discussed earlier you can have more than one criterion. Suppose you also want to find the variance of sales in Michigan for the month on May.  Under Month(Date) in the criteria table enter May. This time when you click and drag cells for criteria, drag in the State field and the “Michigan” and the Month(Date) and “May” criteria too. This was a “AND” condition i.e. one where we added two conditions. Now suppose you wanted an “OR” condition place the second condition below the first condition, i.e. vertically and not horizontally, and then drag them both.

The result, i.e. the variance of sales in the state of Michigan for the month of May will be instantly reflected in the cell.
Note that there is yet another method of inserting the DVAR function into the spreadsheet. For this first select the cell where you would like to insert the function. Then from the Formula tab, the Function Library group, choose Insert Function. The Insert Function dialog opens. Here select the DVAR function from the Select a Function box. Note that you need to ensure you have selected the Database category located just above the Select a Function box.  A new Function Arguments dialog is displayed where you can select the Database, Field and Criteria by clicking the buttons (located adjacent to the corresponding fields) which will take the control back to your spreadsheet and back to the dialog by inserting the selected ranges into the fields. When done click OK and your function is all set. As a confirmation your cell will now have the value computed by DVAR inside. And that is all there is to it. See, you do not need to be an expert Excel consultant to learn this stuff, anyone in business can use these free Excel help articles to learn the Microsoft Excel functions on their own time, and then can apply them to their unique business situation.