## Microsoft Excel Implementation of the Needleman-Wunsch Sequence Alignment Algorithm

The Needleman-Wunsch algorithm aligns nucleotide sequences by taking into account a numerical penalty for gaps in the sequences. Using Excel, this template calculates the similarity matrix and uses Excel’s conditional formatting to display the path for traceback.

You can copy the formulas outward if you want to compare longer sequences.

By default, random sequences are loaded for the top (subject) and left-side (query) sequences. You can press F9 in Excel to generate new random sequences.

## Screenshots

## Traceback Key

Red: move diagonally up and left, sequences are aligned

Green: move up, gap in top (query) sequence

Blue: move left, gap in left-side (subject) sequence

White: alternate optimal alignments. Move up, left, or diagonally toward the highest value

## Default Scoring Values

Gap Penalty: -1

Match Score: 1

Mismatch Score: 0

## Example Formula

The formula for cell C8 is representative of an entry in the table (cells B1 through B3 hold the match, mismatch, and gap scoring penalties):

=MAX(B7+IF(C$6=$A8,$B$1,$B$2),C7+$B$3,B8+$B$3)

## Finding Semi-Global Alignments

The default behavior of this Excel template is to find global alignments. It can be modified to find semi-global alignments by making three adjustments:

- Initializing the leftmost numerical column and the topmost numerical row with all
`0`

s - Allowing free horizontal moves in the last row
- Allowing free vertical moves in the last column

## Download Link

Needleman-Wunsch Algorithm Excel Template

(Works for Microsoft Excel 97-2003, Excel 2007, and Excel 2010)

## Alternate Version

This alternate version shows the traceback arrows along with the cell coloring. The arrows are helpful for understanding the algorithm, but the formula gets cluttered (for example, in cell internal2!C8):

=CONCATENATE( IF( AND(internal1!B7+IF(internal1!C$6=internal1!$A8,Main!$B$1,Main!$B$2)>internal1!C7+Main!$B$3, internal1!B7+IF(internal1!C$6=internal1!$A8,Main!$B$1,Main!$B$2)>internal1!B8+Main!$B$3), "↖",""), IF( AND(internal1!C7+Main!$B$3>internal1!B7+IF(internal1!C$6=internal1!$A8,Main!$B$1,Main!$B$2), internal1!C7+Main!$B$3>internal1!B8+Main!$B$3), "↑",""), IF( AND(internal1!B8+Main!$B$3>internal1!B7+IF(internal1!C$6=internal1!$A8,Main!$B$1,Main!$B$2), internal1!B8+Main!$B$3>internal1!C7+Main!$B$3), "←","") )

## Screenshot

## Alternate Version Download Link

Needleman-Wunsch Algorithm Excel Template with Traceback Arrows

(Works for Microsoft Excel 97-2003, Excel 2007, and Excel 2010)

leave a comment