Using Conditional Formatting for Zebra Striping in Microsoft Excel

Zebra Striping in Excel is a useful way to make it easier to differentiate between long (page-wide) rows of data especially when each column stores data which looks very similar. Simply put, you use alternating colors to define rows. I’ve found this to be most useful when printing picklists or other lists for use in our warehouse.

Achieving this effect is simple in Microsoft Excel with Excel’s Conditional Formatting tool. In any version of Office after 2003, click on “Conditional Formatting” in the “Home” ribbon and create a new rule. Here the instructions diverge slightly: In Office:Mac choose “Classic” style then “Use a formula to determine which cells to format”, in Office for Windows choose the “Use a formula to determine which cells to format” Rule Type.

At this point the instructions converge again: type in the following formula into the formula box:

=MOD(ROW(),2)

After this, in Office for Windows click on “Format…” to define the format you want to use for every other row. In Office:Mac click on the “Format With” drop down and choose “Custom Format” to define your format.

The formula above uses Modular Arithmetic (the MOD() function) with the row number (the ROW() function gets the current row) as the input and 2 as the divisor. If the result is greater than 0 (any uneven row since uneven numbers can’t divide cleanly by two) the row will be formatted with your defined format, otherwise the result is 0 and the row is left with its current format.

 

Generating EAN/ISBN-13 Check Digits in Excel

When you’re dealing with ISBN-13’s you’ll sometimes need to generate a check digit, either to verify¬† a number your employees are inputting during inventory, or scanning off a book or product, or possibly you’re receiving input from a publisher which uses the antiquated ISBN-10 standard and you need to convert it to ISBN-13’s.

ISBN-10 Check Digits

First, what is a check digit? On most barcodes or standardized numbers the last character is what’s called a check digit. This number is generated by using a formula on all the other numbers. In ISBN-10’s this number is generated by multiplying each of the first 9 digits by decreasing numbers starting with 10. For example, the number 0330258648 is a valid ISBN-10. To generate the check digit (which in this example is the 8 at the end of the ISBN-10) we use the following formula:

((0*10)+(3*9)+(3*8)+(0*7)+(2*6)+(5*5)+(8*4)+(6*3)+(4*2)) mod 11 = 3 11-3 = 8

What’s going on here is that each of the first 9 digits of the ISBN-10 are being multiplied by a descending sequence of numbers from 10 to 2. The sum of these multiplications are then taken and divided by 11 with the remainder being take (using modular arthimatic here). The remainder plus the check digit must equal 11 to be valid so to find the check digit subtract the remainder from 11. If the result is 11 (11-0=11), the check digit is written as an upper-case “X”.

ISBN-13 Check Digits

Check digit calculation with ISBN-13s is somewhat different. To find a check digit for a certain ISBN-13, for example 9780395489314, you can use the following formula:


((9*1)+(7*3)+(8*1)+(0*3)+(3*1)+(9*3)+(5*1)+(4*3)+(8*1)+(9*3)+(3*1)+(1*3)) mod 10 = 6

10-6 = 4

This works very similarly except that the initial sum is developed by multiplying the first 12 digits of the ISBN-13 by 1 and 3 alternately and then using mod 10 instead of mod 11 do find the remainder of that sum. After that, again, the check digit + the remainder = 10 so 10 – the remainder = the check digit. In this case 4.

Performing the calculations in Excel

To do this, you’ll need to use the following formula:


=B2&(ROUNDUP(((MID(B2,2,1)+MID(B2,4,1)+MID(B2,6,1)+MID(B2,8,1)+MID(B2,10,1)+MID(B2,12,1))*3)+(MID(B2,1,1)+MID(B2,3,1)+MID(B2,5,1)+MID(B2,7,1)+MID(B2,9,1)+MID(B2,11,1)),-1)-(((MID(B2,2,1)+MID(B2,4,1)+MID(B2,6,1)+MID(B2,8,1)+MID(B2,10,1)+MID(B2,12,1))*3)+(MID(B2,1,1)+MID(B2,3,1)+MID(B2,5,1)+MID(B2,7,1)+MID(B2,9,1)+MID(B2,11,1))))

You’ll notice that this formula refers to cell B2. In cell B2 you’ll need to have one of the following:

  1. The first 12 digits of an ISBN-13 (A useful function here is =LEFT(B1,12) which will simply take the first 12 characters of cell B1).
  2. The first 9 digits of an ISBN-10 with “978” concatenated to the front of it (e.g. 978033025864)

And that’s how to generate a ISBN-13 check digit in Excel!

Notes

  1. Remember that an ISBN-13 is the same as an ISBN-10 with “978” prepended and a different check digit.
  2. If you’re validating an ISBN-13 which has all 13 digits, don’t forget to remove the checkdigit before passing it through the Excel formula, otherwise you’ll end up with an ISBN-14, which doesn’t exist and is definitely not valid.

Formula to Convert Fractions to Decimals in Excel

I’ve been working to get a product information file into a format that would allow me to import it into my database and one of the issues I’ve encountered is that this file contains three columns for dimensions: height, width, length. These three contain fractional values in the format “6 1/16” which Excel doesn’t translate well. So, I put together this formula to deal with that column:


=IF(LEN(TRIM(A2)) >0,IF(TRIM(A2)-INT(TRIM(A2)) > 0,INT(LEFT(A2,FIND(” “,A2)))+(INT(MID(A2,FIND(” “,A2),FIND(“/”,A2)-FIND(” “,A2)))/INT(MID(A2,FIND(“/”,A2)+1,LEN(TRIM(A2))-FIND(“/”,A2)+1))),TRIM(A2)),””)

You’ll note that the value I’m looking for is in column A, row 2 and that if the value of A2 is blank I’m letting the result be blank. If you’d prefer the result to be 0 if there is no value in the fractions column, you can change the “” to 0.