Create Encoder Pattern Using Excel

I recently have been working with quadrature encoders. I created several programs using an encoder to control the speed of a motor using pulse width modulation. These encoders are of mechanical design and for best results require a resistor and capacitor filter on the inputs in order to compensate for switch bounce.

The final result worked very well. However, these encoders have mechanical contacts which are subject to wear and are generally limited to a maximum number of cycles.

Optical encoders can be used to overcome these limitations. They are quite a bit more expensive than mechanical encoders.

I decided I wanted to experiment with making my own optical encoder. The necessary artwork is not that complex, simply an alternating pattern of black and white segments. I did not have a suitable drawing program other than my PCB artwork program – which was unable to produce a suitable encoder design.

In studying the problem, I suddenly realised that the encoder artwork resembled a circular pie chart. Since I did have the spreadsheet program Excel and I was already familar with how it works, I decided to try using it to make a circular pie chart with equal alternating black and white segments.

It turned out to fairly easy. The requirements are simple: You want an even number of black and white segments with the total evenly divisable into 360 degrees. I chose to start with an encoder wheel with a total of 24 segments. Since 360 divided by 24 is equal to 15, you simply need to enter the value of 15 into 24 number cells in Excel. The easy way is to enter 15 into the first cell and then drag the value down into the next lower 23 cells (24 total cells).

Once this is done, you create a simple pie chart and then format each cell with an alternating pattern of black and white colors by clicking on each value in the legend and selecting the correct color. I suggest saving the final pattern as a PDF document.

The only other variable is the size of the artwork. This is important as the encoder output must be in the correct pattern in order work correctly.

The most basic encoder typically has two outputs called “A” and “B” and since the outputs are 90 degrees out of phase they are referred to as a quadrature output encoder. Having two outputs gives the ability to determine the direction of rotation either clockwise or counter-clockwise.

The ultimate size will be dependent on the size and type of optical encoders that are going to be used. I plan on experimenting with the Sharp GP1S53VJ000F transmissive photointerrupter.

I found I could easily adjust the final print size of the encoder using the printer software and entering a percentage of the normal print size. I ultimately did this by trial and error.

I eventually plan to use the artwork to create a circuit board with the encoder pattern.

Sharp OptoInterrupter


24 Segment Encoder


Forty Segment Encoder Pattern






End Post


Leave a Reply

Your email address will not be published. Required fields are marked *

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