Posts Tagged ‘Data’

This is a common requirement while creating dashboards. One selects an option and the charts update to show data for that particular selection. It would be very useful if the same chart could show you how that particular selection fares against average or a set benchmark.

Comparison Chart Against AverageLet’s take an example. I have data for a class of students with their subject marks. I make a selection of the student name, his marks for each subject are plotted on a chart. I wish to see how this particular student has performed in each subject as against the class average. This custom chart does just the same, and it can be very very easily created in Excel as below:


UntitledWhen working with a hierarchical data, most often we need to view at various levels of hierarchy. One of the ways of getting it done is using Data List Outline feature of Excel. To better demonstrate what I’m talking about, let me take an example.

I have the following data – the first column contains the month name, the second column contains the region name and the last column has the sales.

Raw Data

Raw Data


Large Operation Warning: Excel

Posted: September 12, 2013 by Theodoulus in Excel
Tags: ,

We generally see a “Warning Dialogue Box” like:

large operation warning

This is seen when we try to put a formula, value or formatting to a big range (many cells) at a time in Excel worksheet.
So the good part is, we can modify the number of cells for which the dialogue box gets popped-up. And how do we do this:

Simply, Open Excel File -> Goto Excel Options-> Goto Advanced Tab -> In Editing Options, we can find one option like highlighted below:

large operation warning Solution



We can set any number in the box given there (in Thousands).

Now, if we perform any calculation on the a bigger range than specified, the Warning will be popped-up.

UntitledProblem Statement: I have grade-wise student numbers in column C as given below. I have written a formula in cell [A2] that sums up the total number of students in each grade.



Now I want to put a filter on grade, i.e. column A, and see the total number of students in that grade in all sections. If I simply use Autofilter, it will not give the desired result. (more…)

Custom Data Validation :: Using Formulas

Posted: June 4, 2013 by MaxFraudy in Excel
Tags: , ,

Excel provides Data Validation feature that lets you restrict the type of data that the user enters into the cell. There are many types of built-in validations that are available, like below:

Data Validation Options

Data Validation Options

The last option, i.e. Custom, can be used to put in non-standard validations using Excel formulas. Let’s see how it works using an example. Suppose you have a data table in which the first column contains the Product Code. (more…)

Most of us who have worked with data in MS Excel have had to use Filter option every now and then. It helps when sifting through huge amounts of data and when one wants to look at only part of it at a time. e.g. looking at one region’s data from the national database or filtering on values that are greater than (or smaller than) a particular number and so on.

While Filter is a mighty useful feature in itself, there are certain limitations. e.g. if one wishes to filter on two columns but want records that satisfy “either” column criteria then one cannot do so using simple filters. Neither, can one apply multiple combinations of criteria. This is where Advanced Filter option comes into picture. It can do most of what one would need from an Excel application. Advanced filtering, similar to filtering, displays a subset of the rows in a table or range of data.

Using Advanced Filtering

Step 1: Set up the database such that it satisfies all the conditions below:

  • Should have headings
  • No blank rows in between
  • Blank row at end & blank column at right

Step 2 – Set up the criteria range. The criteria range headings should match exactly with the headings of the database

Step 3 – Set up the extract range

  • If one plans to copy the data to another location, one can specify the columns that he wants to extract. By default, ALL columns are extracted.
  • Select the cell at the top left of the range for the extracted data.
  • Type the exact headings for the columns that you want to extract

Step 4 – Apply the filter

On launching the Advanced Filter (Data tab -> Sort & Filter section -> Advanced) a dialog box appears
Continue Reading

Sometimes one might need to export data from one Excel file to another and if one wants to do so without opening any of those files (Source and destination files), then it can be done using the following procedure.

It has four parameters, last of which is optional:
1. strSourceFileFullName : Full name of the source file with extension. e.g. “C:\Challenge1.xlsx”
2. strTargetFileFullName : Full name of the target file with extension. e.g. “C:\Challenge2.xlsx”
3. strSourceSheetName : Sheet name from where data needs to be exported.
4. strTargetSheetname : Sheet name in the target file where data should be pasted. This is an optional parameter. If this parameter is not passed then a new sheet will be created with the same name as the source sheet.

Sub TransferData(strSourceFileFullName As String, _
                 strTargetFileFullName As String, _
                 strSourceSheetName As String, _
                 Optional strTargetSheetname As Variant)

    Dim adoConnection   As Object
    Dim adoRcdSource    As Object
    Dim Provider        As String
    Dim ExtProperties   As String
    Dim strFileExt      As String

    Set adoConnection = CreateObject("ADODB.Connection")
    Set adoRcdSource = CreateObject("ADODB.Recordset")
    If Len(Dir(strSourceFileFullName)) = 0 Then
        MsgBox "Input file does not exist"
        Exit Sub
    End If

    strFileExt = Mid(strTargetFileFullName, InStrRev(strTargetFileFullName, ".", -1, vbTextCompare), Len(strTargetFileFullName))

    If strFileExt = ".xlsx" Then
        ExtProperties = "Excel 12.0 XML"
    ElseIf strFileExt = ".xlsb" Then
        ExtProperties = "Excel 12.0"
    ElseIf strFileExt = ".xlsm" Then
        ExtProperties = "Excel 12.0 Macro"
        ExtProperties = "EXCEL 8.0"
    End If

    If CDbl(Application.Version) > 11 Then
      Provider = "Microsoft.ACE.OLEDB.12.0"
       Provider = "Microsoft.JET.OLEDB.4.0"
    End If

    If IsMissing(strTargetSheetname) Then
        strTargetSheetname = strSourceSheetName
    End If
    adoConnection.Open "Provider=" & Provider & ";Data Source= " & strTargetFileFullName & ";Extended Properties=""" & ExtProperties & ";HDR=YES"";"

    On Error GoTo Errorhandler
    adoRcdSource.Open "Select * into [" & strTargetSheetname & "] From [" & strSourceSheetName & "$] IN '" & strSourceFileFullName & "'[" & ExtProperties & ";HDR=YES;]", adoConnection
    If Err.Number = -2147217900 Then
        MsgBox "A sheet or a named range with the same name already exists in the target workbook. Data will not be copied.", , "Name Conflict"
    End If

    Set adoRcdSource = Nothing
    Set adoConnection = Nothing

End Sub

Note: If a file with the name ‘strTargetFileFullName’ is not found then it gets created automatically in the target folder but if you want to transfer data into an xlsm file than it must be there. It won’t be created automatically.
If you face any problems, email us or leave a reply.



RANK in MS Access

Posted: April 23, 2013 by Transformer in MS Access, SQL
Tags: , , ,

Sometimes one needs to rank data based on some field e.g. Students’ Marks, Product Sales etc. In different platforms there are different ways to do it.
In SQL Server and Excel there is RANK function that does the job but in MS Access there is no such built-in function.

However same result can be achieved using a correlated query.

e.g. Consider a dataset of student marks below.

Students Marks
Tony 34
Bob 32
Thor 48
Jack 42
Tom 41
Kate 45
Sid 26

Suppose one wants to rank students based on their marks (rank = 1 for highest marks). Following query can be used to rank them.

SELECT Students,
       (SELECT COUNT(T1.Marks)
                 [Table] AS T1
         WHERE T1.Marks >= T2.Marks) AS Rank
      [Table] AS T2

Following is the result of the query.

Students Rank
Thor 1
Kate 2
Jack 3
Tom 4
Tony 5
Bob 6
Sid 7

If,  rank 1 is to be assigned to the lowest value, then in that case the query above can be slightly modified to:

SELECT Students,
       (SELECT COUNT(T1.Marks) 
                 [Table] AS T1 
         WHERE T1.Marks <= T2.Marks) AS Rank 
      [Table] AS T2 

There is often a confusion in the minds of most people when it comes to using the HAVING clause. On the surface of it, it looks very similar to how a WHERE clause works, but that is not the case. While WHERE clause filters out records before data aggregation is done, HAVING does it later.

Let’s take an example to understand it better. I have a dataset of employee salaries with details of their departments.

Employee_ID                    Dept                                 Salary
E10002                                HR                                   14000
E10050                                IT                                    20000
E15334                                HR                                   15000
E33336                                IT                                    16500
E35480                                Legal                                30000
E45670                                Legal                                29000
E74016                                HR                                   12000

Suppose I want to find out the average salary of the HR and the IT department. I will write the following query:

          SELECT dept, AVG(salary) AS AVG_SAL
         FROM employees
        WHERE dept IN (“HR”, “IT”)
        GROUP BY dept

Continue Reading

FREQUENCY Function in Excel

Posted: April 16, 2013 by Transformer in Excel
Tags: , , ,

Frequency function calculates the number of values in a dataset that fall within a particular interval. Generally it is used to group the values of a dataset in intervals.
It requires two arguments, the first one is a dataset and the other is a list of intervals.

                         Frequency (data_array, bins_array)

Data_array: it is an array or a range of values for which you want the frequency (Dataset)
Bins_array: it is an array or a range of intervals based on which you want to group the data_array (Intervals)
This function returns an array of numbers (count of frequencies). The size of the returned array is one more than the size of bins_array, the extra element being the count of elements in the data_array that are greater than the max value of the bins_array. It must be entered as an array formula.

We have a list of students’ marks and we want to group them in intervals as given below. Here, the list of marks is the data_array and bins_array is the upper limits of the intervals.

Scores	 Interval   bins_array   Results
30	 <=20        20           0(No element <= 20)   
97	 21-40	     40           4(4 elements between 21 to 40)  
62	 41-60	     60           5(5 elements between 41 to 60)  
45	 61-80	     80           4(4 elements between 61 to 80)  
94	                          7(extra element that tells how many 
32			            elements lie outside the intervals)

The order of values given in bins_array is immaterial, as illustrated in the example below.
In the same dataset above, if we change the order of elements in bins_array then the function returns same values against each element of bins_array as it would have done if the bins_array was sorted.

Unsorted (bins_array)               Results
  40                                   4
  20                                   0
  80                                   4
  60                                   5