Use Excel to set DIP switches

Configuration of DIP switches is normally a digital subject, but given my history of discussing Microsoft Excel on Planet Analog, I thought it appropriate to present this design idea here. I know that using DIP switches for system configuration is probably passé, but they are still in production. I alone am not responsible for the volumes being produced, so I have to assume that there are some of you who use them as well. How often have you seen tables of data detailing a particular configuration along with the associated switch setting? Some of my projects have several sets of DIP switches and producing these tables was becoming tedious. In addition, I am sure it is a pain for customers to search down the text to find the correct setting. I initially wanted to use Excel to generate the tables, but then it occurred to me that I could distribute an Excel worksheet where the customer could click and select a particular configuration, and the DIP switch settings would be displayed.

I previously wrote about Excel: Using the Validate Data Feature, so I won’t delve too deeply into it here, but it forms one of the tools needed for this idea. Let’s base the discussions on a 3 bit DIP switch, just to keep images and lists fairly small. On an Excel worksheet, we enter a list of options for each combination of switches. To state the obvious, with 3 switches there are 8 possible selections. Let’s assume that we are going to use the switches to set up a delay. First create the list from K7 to K14 as you can see in Figure 1 (labelled T-DELAY).

Then select the cell where you want the selection to appear. Choose D6 and then proceed to enter the validation function by clicking on Data | Data validation | Data Validation … Click on the Settings tab and in the Allow: drop down box, select List . In the Source: box select the data block describing the range of cells for the list (see the aforementioned K7 to K14). You can use the table button on the right of the box to graphically enter the selection, if you prefer.

Figure 1

Adding the list to display at D6

Adding the list to display at D6

Thereafter, selecting cell D6 will result in a drop-down arrow and the above message as you can see in Figure 2. On clicking the drop down arrow you will be presented with the whole list of options in the range K7 to K14 and selecting one will result in that line appearing in cell D6. It can be changed at any time.

Figure 2

Creating a drop-down menu

Creating a drop-down menu

After some editing with borders and merging some cells I came up with the layout that you see in figure 3. I have designated the DIP switch as SW3 in this hypothetical example.

Let’s go step by step in developing the formula. Depending on the options that an application needs, it would have been possible to use purely numerical values, but it loses its versatility- for instance, how would you deal with an entry like “Disabled” (cell K7) if you are limited to numbers? But when you come to try and analyze the cell, this versatility is a stumbling block. So we return to the list in column K to find the exact entry and identify which one it is. The Excel MATCH function looks for a match in a list and returns the index number of the matching entry. The function has conditions for inexact matches as well, but since the entry is derived from the list, we are guaranteed that there will be an exact match. MATCH takes the format:

=MATCH(lookup_value, lookup_array, [match_type])

Select cell C8. The lookup value is the source value for the function and it is cell D6, which was originally set up using the drop down menu. You will notice that this has been modified to “$D6”. That’s because I am going to copy the function across columns and without the “$” the column reference will update as well. The lookup array is the range $K7 to $K14, and the match type is set to “0” to signify an exact match. The “$K” is there for the same reason as the “$D”. So the formula is


Try changing the selection and watch as cell C8 will change from 1 to 8. Since I want an index from 0 to 7, I will subtract 1 from the result, so the formula becomes = ( MATCH($D6,$K7:$K14,0) -1 )

(In this and subsequent formulas, the bold enlarged text indicates what has been added to the previous formula). What I am going to do is convert the decimal number to a 3 digit binary number and then look at each bit to determine whether each switch is open or closed. Changing to a binary number uses the formula

=DEC2BIN (number,[places])

And so we modify cell C8 to

=DEC2BIN( (MATCH($D6,$K7:$K14,0)-1) , 3)

The “,3” at the end indicates that I want a 3-bit answer. Change D6 and watch as the number in C8 changes from 000 through to 111. Note that this is text data. The next step is to investigate each bit in turn as this is associated with a particular switch. The Excel MID function has the form

=MID(text, start_num, num_chars)

and will extract text from within a string.

Looking at the most significant digit, the start_num becomes 1 and the num_chars is 1, for a single character.

=MID( DEC2BIN((MATCH($D6,$K7:$K14,0)-1),3) , 1,1)

Once again try changing the selection on D6 and watch as the extracted MSB changes. In this hypothetical example we shall designate that when a bit is a logical one, the associated switch will be in the “OFF” state and vice-versa (which does not mean pornographic poetry- LOL). The IF formula evaluates a condition. If the condition evaluated is true, it will return one value; if not it will return a second value.


Adding this to C8 the formula becomes =IF( MID(DEC2BIN((MATCH($D6,$K7:$K14,0)-1),3),1,1)=”1″,”OFF”,”ON”)

In other words, if the result from the binary conversion is a textual “1”, the cell will display the text “OFF”, otherwise it will display “ON”.

Now we copy the cell at C8 to cells D8 and E8. Go to cell D8 and edit the formula to =IF(MID(DEC2BIN((MATCH($D6,$K7:$K14,0)-1),3), 2 ,1)=”1″,”OFF”,”ON”)

The 2 now indicates the 2nd digit in the binary number. Similarly edit the cell at E8 to

=IF(MID(DEC2BIN((MATCH($D6,$K7:$K14,0)-1),3), 3 ,1)=”1″,”OFF”,”ON”)


You can change the selection on D6 and watch as the switch settings update. To polish the model, right click on the column K and select Hide . Then click on the File tab, click on Protect Workbook | Protect Current Sheet and uncheck the Select locked cells option. A password is your choice, but not necessary. Click on OK . In the protected state only cell D6 can be updated by the user. The model is now finished.

Figure 3

The model with column K hidden. When the worksheet is protected it is only possible to click on cell D6.

The model with column K hidden. When the worksheet is protected it is only possible to click on cell D6.

You are not limited to a list when using the Validate Data feature. You can input a number and place conditions on that number. You can then convert that to an N-bit number for use with N switches. (To implement this change remember to unprotect the sheet!) Let’s assume that you wanted to set a trip current of 45 milliamps in a range of 100mA using 6 switches. Converting this requirement you would calculate ((45/100)*64) and then round this using the eponymous ROUND function. Then proceed to convert this to a binary number with the DEC2BIN as before. You will find the formulas in the worksheet. The setup is shown in Figure 4.

Figure 4

This will allow any integer between 0 and 100 and will flag and error if not (determined by the settings under the Error Alert tab.

This will allow any integer between 0 and 100 and will flag and error if not (determined by the settings under the Error Alert tab.

I have included both the list input and numeric input approaches in the example DIPsettings.xlsx. I hope you find this useful. I think it’s fair to say that I have written extensively on the subject of the use of Excel in electronics. However, quantity does not imply quality. You can judge for yourselves in this listing of almost all of my scribbles on Excel.

Let me know your thoughts in the comments below.

0 comments on “Use Excel to set DIP switches

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.