Creating Cascading Drop-down Lists in Excel

If you’re using Microsoft Excel to capture and track data, one of the challenges is maintaining good data quality when more than one person is updating the workbook. The data validation features in Excel help by only allowing the user to select data based on pre-defined options in a list. This feature works well on individual cells. However, if you have a column that depends on the value in a different column, you will need to get a little more creative. This post describes the steps for creating cascading drop-down lists in Excel using a combination of data validation and named ranges.

Step 1 – Define the Reference Data

Let’s assume that we are collecting data that includes Organization and Department attributes. In this example, an Organization is the parent to one or more Departments (children). We’ll set up the reference data for the data validation drop-down lists as follows on its own worksheet.

  • Column A (“ORGANIZATIONS”) represents the list of valid Organizations to appear in the Organization drop-down list.
  • Column B (“ORGANIZATION TO DEPARTMENT MAPPINGS”) represents the names given to ranges that will be defined in a later step.
  • Columns D through E (“REF_ORGANIZATION_X_DEPARTMENTS”) represent the lists of valid Departments to appear in the Department drop-down list depending upon which Organization is selected.
Excel Cascading Drop-down List - Reference Data
Reference Data

Step 2 – Define the Named Ranges

Now that we have the reference data established, we can define the named ranges that refer to these lists. The named range entry defined as REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS is the key to creating the cascading drop-down list functionality. It is important that the values in Column B (above) match the names given to the REF_ORGANIZATION_X_DEPARTMENTS ranges. This value is used in a subsequent step using a combination of INDIRECT and VLOOKUP in the data validation formula.

Excel Cascading Drop-down List - Named Ranges
Named Ranges

Step 3 – Define the Organization Data Validation (Parent Column)

In this step, we establish the standard data validation on the parent data in Column A (“REF_ORGANIZATIONS”) on a new data collection worksheet.

Excel Cascading Drop-down List - Parent Level Validation
Parent Level Validation

Step 4 – Define the Department Data Validation (Child / Dependent Column)

Here we establish the data validation rule that performs the cascading drop-down list function. Once the user selects a valid Organization, this formula performs a VLOOKUP against the REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS named range and returns the name of the Department named range, e.g. REF_ORGANIZATION_X_DEPARTMENTS, associated with the selected Organization. The indirect function then converts that name, a text value, into the named range reference.

=INDIRECT(VLOOKUP(A2,REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS,2,FALSE))
Excel Cascading Drop-down List - Child Level Validation
Child Level Validation

When you click the OK button, Excel will display the following error message. Click the Yes button to continue.

Excel Cascading Drop-down List - Validation Error Message
Validation Error Message

Step 5 – Test the Cascading Drop-down Lists

Now that the data validation rules are set up, we can test the cascading drop-down list functionality. Selecting “Organization 1” causes the validation drop-down list in the Department column to reflect only those Departments associated with “Organization 1”. Selecting “Organization 2” causes the Department drop-down list to show those Departments associated with “Organization 2”.

Excel Cascading Drop-down List - Parent List Values
Parent List Values
Excel Cascading Drop-down List - Child List Values Based on Parent Selection
Child List Values Based on Parent Selection
Excel Cascading Drop-down List - Both Parent and Child Selected
Both Parent and Child Selected

Step 6 – Macro to Validate Parent / Dependent Relationship

Now that the worksheet is functioning as expected, you release it for users to update and someone will inevitably enter data in a way that breaks the parent / child relationship. As an example, “Organization 2” is selected in the Organization column and “Department 6” is selected in the Department column. The user then returns to the Organization column and changes the entry to “Organization 3”. The Department column retains the value of “Department 6” which is not valid for the “Organization 3” selection in the Organization column. To help avoid these errors, you can save the file as a macro enabled workbook and add the following code to the data entry worksheet. If this macro had been enabled, the “Department 6” value is removed as soon as the user selected “Organization 3”. This code assumes that the parent column is the first column (Column A) in the worksheet and the child column is immediately to the right (Column B). Please revise the code to meet your specific requirements.

Excel Cascading Drop-down List - Selecting Parent Field Clears Child Field Through Macro
Selecting Parent Field Clears Child Field Through Macro

Example

Excel Cascading Drop-down List – Example in Action
Excel Cascading Drop-down Lists in Action

Source Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    If Target.Column = 1 Then
        If Target.Validation.Type = xlValidateList Then
            If Not Target.Offset(0, 1).Validation.Value Then
                Target.Offset(0, 1).ClearContents
            End If
        End If
    End If
exitHandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Replace Line Feeds With Commas Using Javascript

The following JavaScript function can be used to replace line feeds and carriage returns with commas. I find this particularly useful when formatting a data column from Excel into an SQL constraint.

Sample Input

1
2
3
4
5

Sample Result

1,2,3,4,5

The JavaScript function uses a simple regular expression to remove line feeds and carriage returns. The comma as the second argument to the ref.value.replace function may be replaced with any other character to meet your specific requirement.

Live Test

Test the code using the following text area. Enter a carriage return / line feed separated column of data, move focus off the text area, and the code will automatically format the list.

Source Code

function remove_crlf(ref) {
  ref.value = ref.value.replace(new RegExp("[\r\n]", "gm"), ",");
}

Using a simple HTML page with a Text Area, we can paste the data list into the form. When the cursor is moved away from the Text Area, the function is automatically called and the list is formatted with commas replacing the line feeds and carriage returns on each line.

Putting all of the code together, we get the following which can be saved to an html file.

<html>
  <head>
    <script language='javascript'>
      function remove_crlf(ref) {
        ref.value = ref.value.replace(new RegExp("[\r\n]", "gm"), ",");
      }
    </script>
  </head>
  <body>
    <form name='frmremover'>
      <textarea name="txtdata" rows="5" cols="40" onblur="javascript:remove_crlf(this)"></textarea>
    </form>
  </body>
</html>

How to Round Double to Any Decimal Place in Java

The following Java snippet demonstrates several ways to round a double to the nearest integer, tenths, hundredths, thousandths, or any decimal place. The code handles both positive numbers, negative numbers, and zero, too.

If you don’t read through any of the following detail, be sure to use the BigDecimal rounding method (Method 4). The other methods have precision issues which don’t provide a reliable way to round decimals especially if you’re performing calculations to put someone on the Moon.

public static String roundDecimalUsingBigDecimal(String strValue, int decimalPlace) {
  return new BigDecimal(strValue).setScale(decimalPlace, RoundingMode.HALF_UP).stripTrailingZeros().toPlainString();
}

Why is Rounding So Complicated? Round-off Errors!

It has been awhile, but I’m fairly certain that round-off errors were part of the first lesson in Computer Science 101. As a generalization, computers are limited in the way numbers are represented in binary. This isn’t a limitation of Java or any other programming language specifically, but it’s a limitation of how fractional numbers can be represented in a finite binary storage model. As a simple example, the fraction 1/3 cannot be accurately represented in binary since it is a non-terminating expansion, e.g. 0.333…, and floating point primitives have a defined maximum storage size. Certain other fractions may not represented without adding additional bits to the field for increased accuracy. If additional bits are not available, there will be small errors in the represented numbers.

The following example demonstrates round-off errors using the input value 255.335.

public class RoundOffErrorExample {
  public static void main(String[] args) {
    double dValue1 = 255.335d;
    double dValue2 = 0.1d;
    double dValue3 = 10.0d;
    double dValue4 = (dValue1 / dValue2) / dValue3;
    System.out.println("dValue1 = " + dValue1);
    System.out.println("dValue2 = " + dValue2);
    System.out.println("dValue3 = " + dValue3);
    System.out.println("dValue4 = " + dValue4);
  }
}

The expected value after some trivial calculations is 255.335. However, the actual result is 255.33499999999998. If the input value 255.335 is rounded to the hundredths decimal place, the expected result is 255.34. If the actual result is rounded to the hundredths decimal place, the result is 255.33.

dValue1 = 255.335
dValue2 = 0.1
dValue3 = 10.0
dValue4 = 255.33499999999998

It may not seem like a big issue if some numbers have small errors in outer decimal places, but these errors accumulate and magnify as further calculations are performed. Continuing with the 255.335 example, assume the rounding error occurred in a banking application across millions of transactions. These one-cent errors could accumulate into a material financial impact over time. This has been part of the plotline in several movies. In Superman III, the character portrayed by Richard Pryor programmed a way to capture (embezzle) these round-off errors into a separate account. This is called salami slicing or penny shaving.

How to Handle Decimal Rounding in Java

The source code I’ve provided below has four possible methods to round decimal numbers in Java. I’ll provide a walkthrough of each method to show the underlying issues. Again, the summary is to use BigDecimal to manipulate fractional numbers to get the most reliable results from calculations. For each described method, I’ll use two input values to demonstrate the issues: 1.335 and the first 50 decimal digits of pi.

Java Options to Round Decimals - Example Using 3 Decimal Digits
Java Options to Round Decimals – Example Using 3 Decimal Digits
Java Options to Round Decimals - Example Using 50 Decimal Digits of Pi
Java Options to Round Decimals – Example Using 50 Decimal Digits of Pi

Method 1: Using Math.round() [Result – Fail]

I’m including Math.round() to illustrate that this method only rounds to the nearest whole number. Since it doesn’t provide any capability to round the fractional part of the number, the Math.round() method isn’t useful for rounding decimals.

Using 1.335 as the input, the result is 1.0.

Method 1:  Rounded using Math.round()
--------
Rounded value 1.335 using Math.round(): 1

Using the first 50 decimal digits of pi, the result is 3.0.

Method 1:  Rounded using Math.round()
--------
Rounded value 3.141592653589793 using Math.round(): 3

Method 2: Using Powers of 10 [Result – Fail]

This method uses a combination of type conversion and multiplication/division by powers of 10. While this initially appears to be a reliable calculation, the combination of type conversion and round-off errors in the underlying primitives results in calculation errors for certain input values. This method also fails when the multiplication by powers of 10 exceeds the maximum value of what a double or long can store.

Using the input value 1.335 and rounding to the 2nd decimal place (hundredths), the math is 1.335 * 100 = 133.5, 133.5 + 0.5 = 134; 134 / 100 = 1.34. If we wanted to round to the 3rd decimal place (thousandths), 1000 would be used instead of 100. In this instance, the rounding works as expected. However, with certain input value, I have seen this method fail as well.

Method 2:  Rounded using Powers of 10
--------
Rounded value 1.335 to 0 decimal places: 1.0
Rounded value 1.335 to 1 decimal places: 1.3
Rounded value 1.335 to 2 decimal places: 1.34
Rounded value 1.335 to 3 decimal places: 1.335

Using the first 50 decimal digits of pi, we find this method fails when rounded to the 19th decimal place. Also, the double used to store the value does not contain all 50 decimal digits. Depending on the size on the input value, this method begins to fail when the interim value multiplied by powers of 10 exceeds the maximum or minimum value of the underlying primitive data type (either double or long). The output illustrates utter failure at the 19th and 20th decimal places. The output also demonstrates limitations on the number of decimal places and precision loss at 15 decimal places.

Method 2:  Rounded using Powers of 10
--------
Rounded value 3.141592653589793 to 0 decimal places: 3.0
Rounded value 3.141592653589793 to 1 decimal places: 3.1
Rounded value 3.141592653589793 to 2 decimal places: 3.14
Rounded value 3.141592653589793 to 3 decimal places: 3.142
Rounded value 3.141592653589793 to 4 decimal places: 3.1416
Rounded value 3.141592653589793 to 5 decimal places: 3.14159
Rounded value 3.141592653589793 to 6 decimal places: 3.141593
Rounded value 3.141592653589793 to 7 decimal places: 3.1415927
Rounded value 3.141592653589793 to 8 decimal places: 3.14159265
Rounded value 3.141592653589793 to 9 decimal places: 3.141592654
Rounded value 3.141592653589793 to 10 decimal places: 3.1415926536
Rounded value 3.141592653589793 to 11 decimal places: 3.14159265359
Rounded value 3.141592653589793 to 12 decimal places: 3.14159265359
Rounded value 3.141592653589793 to 13 decimal places: 3.1415926535898
Rounded value 3.141592653589793 to 14 decimal places: 3.14159265358979
Rounded value 3.141592653589793 to 15 decimal places: 3.141592653589793
Rounded value 3.141592653589793 to 16 decimal places: 3.141592653589793
Rounded value 3.141592653589793 to 17 decimal places: 3.141592653589793
Rounded value 3.141592653589793 to 18 decimal places: 3.141592653589793
Rounded value 3.141592653589793 to 19 decimal places: 0.9223372036854776
Rounded value 3.141592653589793 to 20 decimal places: 0.09223372036854775

Method 3: Using DecimalFormat [Result – Fail]

This method also suffers from underlying round-off errors resulting in a miscalculation at the second decimal place with an input value of 1.335. The expected result is 1.34, however, the actual result is 1.33.

Method 3:  Rounded using DecimalFormat
--------
Rounded value 1.335 to 0 decimal places: 1
Rounded value 1.335 to 1 decimal places: 1.3
Rounded value 1.335 to 2 decimal places: 1.33
Rounded value 1.335 to 3 decimal places: 1.335

Using pi, we find this method fails at the 16th decimal place. Precision that was included in the input value is now lost after the 15th decimal place in the output.

Method 3:  Rounded using DecimalFormat
--------
Rounded value 3.141592653589793 to 0 decimal places: 3
Rounded value 3.141592653589793 to 1 decimal places: 3.1
Rounded value 3.141592653589793 to 2 decimal places: 3.14
Rounded value 3.141592653589793 to 3 decimal places: 3.142
Rounded value 3.141592653589793 to 4 decimal places: 3.1416
Rounded value 3.141592653589793 to 5 decimal places: 3.14159
Rounded value 3.141592653589793 to 6 decimal places: 3.141593
Rounded value 3.141592653589793 to 7 decimal places: 3.1415927
Rounded value 3.141592653589793 to 8 decimal places: 3.14159265
Rounded value 3.141592653589793 to 9 decimal places: 3.141592654
Rounded value 3.141592653589793 to 10 decimal places: 3.1415926536
Rounded value 3.141592653589793 to 11 decimal places: 3.14159265359
Rounded value 3.141592653589793 to 12 decimal places: 3.14159265359
Rounded value 3.141592653589793 to 13 decimal places: 3.1415926535898
Rounded value 3.141592653589793 to 14 decimal places: 3.14159265358979
Rounded value 3.141592653589793 to 15 decimal places: 3.141592653589793
Rounded value 3.141592653589793 to 16 decimal places: 3.141592653589793
Rounded value 3.141592653589793 to 17 decimal places: 3.141592653589793
Rounded value 3.141592653589793 to 18 decimal places: 3.141592653589793
Rounded value 3.141592653589793 to 19 decimal places: 3.141592653589793
Rounded value 3.141592653589793 to 20 decimal places: 3.141592653589793

Method 4: Using BigDecimal [Result – Success]

This is the recommended solution for rounding in Java. BigDecimal can handle both very large and very small numbers without round-off errors and without losing precision and it is generally limited only by available memory.

Method 4:  Rounded using BigDecimal
--------
Rounded value 1.335 to 0 decimal places: 1
Rounded value 1.335 to 1 decimal places: 1.3
Rounded value 1.335 to 2 decimal places: 1.34
Rounded value 1.335 to 3 decimal places: 1.335

Rounding 50 decimal digits of pi results in the expected results for all cases.

Method 4:  Rounded using BigDecimal
--------
Rounded value 3.14159265358979323846264338327950288419716939937510 to 0 decimal places: 3
Rounded value 3.14159265358979323846264338327950288419716939937510 to 1 decimal places: 3.1
Rounded value 3.14159265358979323846264338327950288419716939937510 to 2 decimal places: 3.14
Rounded value 3.14159265358979323846264338327950288419716939937510 to 3 decimal places: 3.142
Rounded value 3.14159265358979323846264338327950288419716939937510 to 4 decimal places: 3.1416
Rounded value 3.14159265358979323846264338327950288419716939937510 to 5 decimal places: 3.14159
Rounded value 3.14159265358979323846264338327950288419716939937510 to 6 decimal places: 3.141593
Rounded value 3.14159265358979323846264338327950288419716939937510 to 7 decimal places: 3.1415927
Rounded value 3.14159265358979323846264338327950288419716939937510 to 8 decimal places: 3.14159265
Rounded value 3.14159265358979323846264338327950288419716939937510 to 9 decimal places: 3.141592654
Rounded value 3.14159265358979323846264338327950288419716939937510 to 10 decimal places: 3.1415926536
Rounded value 3.14159265358979323846264338327950288419716939937510 to 11 decimal places: 3.14159265359
Rounded value 3.14159265358979323846264338327950288419716939937510 to 12 decimal places: 3.14159265359
Rounded value 3.14159265358979323846264338327950288419716939937510 to 13 decimal places: 3.1415926535898
Rounded value 3.14159265358979323846264338327950288419716939937510 to 14 decimal places: 3.14159265358979
Rounded value 3.14159265358979323846264338327950288419716939937510 to 15 decimal places: 3.141592653589793
Rounded value 3.14159265358979323846264338327950288419716939937510 to 16 decimal places: 3.1415926535897932
Rounded value 3.14159265358979323846264338327950288419716939937510 to 17 decimal places: 3.14159265358979324
Rounded value 3.14159265358979323846264338327950288419716939937510 to 18 decimal places: 3.141592653589793238
Rounded value 3.14159265358979323846264338327950288419716939937510 to 19 decimal places: 3.1415926535897932385
Rounded value 3.14159265358979323846264338327950288419716939937510 to 20 decimal places: 3.14159265358979323846

Usage

The following illustrates the code being compiled and executed using the input 255.335.

java RoundDecimalExample [value]

[value] represents the number to round using the four methods; multiple numbers may be passed when separated by a space.
Rounding Double to Various Decimal Places in Java
Rounding Double to Various Decimal Places in Java

Source Code

import java.lang.Math;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
public class RoundDecimalExample {
  public static void main(String[] args) {
    double dValue = 0.0d;
    int i = 0;
    for(String strValue: args) {
      dValue = Double.parseDouble(strValue);
      // Method 1:  Double rounded to the nearest integer using Math.round().
      System.out.println("");
      System.out.println("Method 1:  Rounded using Math.round()");
      System.out.println("--------");
      System.out.println("Rounded value " + dValue + " using Math.round(): " + Math.round(dValue));
      // Method 2:  Double rounded to any decimal place by multiplying by a power of 10, converting to long, and dividing by a power of 10.
      System.out.println("");
      System.out.println("Method 2:  Rounded using Powers of 10");
      System.out.println("--------");
      for(i = 0; i <= 20; i++) {
        System.out.println("Rounded value " + dValue + " to " + i + " decimal places: " + RoundDecimalExample.roundDecimalUsingPowers(dValue, i));
      }
      // Method 3:  Double rounded to any decimal place using DecimalFormat.
      System.out.println("");
      System.out.println("Method 3:  Rounded using DecimalFormat");
      System.out.println("--------");
      for(i = 0; i <= 20; i++) {
        System.out.println("Rounded value " + dValue + " to " + i + " decimal places: " + RoundDecimalExample.roundDecimalUsingDecimalFormat(dValue, i));
      }
      // Method 4:  Double rounded to any decimal place using BigDecimal.
      System.out.println("");
      System.out.println("Method 4:  Rounded using BigDecimal");
      System.out.println("--------");
      for(i = 0; i <= 20; i++) {
        System.out.println("Rounded value " + strValue + " to " + i + " decimal places: " + RoundDecimalExample.roundDecimalUsingBigDecimal(strValue, i));
      }
    }
  }
  public static double roundDecimalUsingPowers(double dValue, int decimalPlace) {
    return ((long) ((dValue * Math.pow(10.0, decimalPlace)) + ((dValue < 0.0) ? -0.5 : 0.5))) / Math.pow(10.0, decimalPlace);
  }
  public static String roundDecimalUsingDecimalFormat(double dValue, int decimalPlace) {
    DecimalFormat decimalFormat = null;
    StringBuffer stringBuffer = null;
    stringBuffer = new StringBuffer("#");
    if(decimalPlace > 0) {
      stringBuffer.append(".");
    }
    for(int i = 1; i <= decimalPlace; i++) {
      stringBuffer.append("#");
    }
    decimalFormat = new DecimalFormat(stringBuffer.toString());
    decimalFormat.setRoundingMode(RoundingMode.HALF_UP);
    return decimalFormat.format(dValue);
  }
  public static String roundDecimalUsingBigDecimal(String strValue, int decimalPlace) {
    return new BigDecimal(strValue).setScale(decimalPlace, RoundingMode.HALF_UP).stripTrailingZeros().toPlainString();
  }
}