Zebra Strips using Conditional Formatting

Zebra lines are shading to differentiate between rows. Using conditional formatting and the formula below, you can highlight the 2nd, 3rd, 4th, etc. row as desired. Just place the mod formula inside a conditional formatting box and format the cell shading as desired. Just change the number to indicate which row to highlight.

=MOD(ROW(),2)=0

Copying Data Validations to Different Areas of Your Worksheet

Data validation allows you to control what information is allowed to be in a cell. This is a handy feature to help ensure data accuracy. Quite often you may need to repeat the data validation in different areas of your worksheet. Well rather than recreating the data validation for each area, here’s a useful way to copy an existing cells validation to additional cells or ranges of cells.


In the below example I have setup the following validation for cells (A2..A7) where only whole numbers between 1 and 5 will be allowed.

It’s easy to copy this validation to different areas of my worksheet by first copying one of the cells between A2 and A7 using CTRL+C.

Next go to your target cell or range of cells and then press ALT+ESN. This copies only the data validation rule to your target cells or range of cells. You could also select Paste Special and then select Validation from the Paste options.

Click the OK button to complete the copy of your data validation.

Print Formatting : Show header rows show on every page

Today I want to discuss how to print header rows on every page of your worksheet. In this example I have a highlighted header row. I would like to have this header row printed at the top of every page of this report. This will help the reader understand what the columns represent on all pages of my document.

Start by selecting the page layout tab and then open the Page Layout dialog box.

On the Sheet tab of the Page Setup dialog box, click on the rows to repeat at top.

Clicking on the row you want to appear at the top of each page will populate the Page Setup – Rows to repeat at top dialog box. When done return to the Page Setup dialog box by hitting the enter key.

Select the OK button to complete the setup.

Now whenever you print preview or print your worksheet, the row you selected will be repeated at the top of each page.

Generating Random Numbers, Dates or Letters

Quite often I am asked to create dashboards without having the actual data. When I am presented with this dilemma, I will create dummy data to use in lieu of the real data.

To generate numeric and date data, I often will use the =RANDBETWEEN formula.

The different between Excel’s RAND formula and RANDBETWEEN is that you can determine the start and end points for the random number to be generated.

=RANDBETWEEN(Starting Number, Ending Number)

RANDOM NUMBERS

In this example I use the RANDBETWEEN formula to generate numbers between 100 and 500.

Formula	Results
=RANDBETWEEN(100,500)	350
=RANDBETWEEN(100,500)	217
=RANDBETWEEN(100,500)	491
=RANDBETWEEN(100,500)	183

RANDOM DATES

The RANDBETWEEN formula only returns integers so you can’t directly generate dates. However you can add a random number generated with this formula to a date column to generate a date. In this example I generate a random number and add that to the start data of my criteria (1/1/2012). The random number increments the date by the number of days that the random number represents.

Formula for Random Number	Random Number	Starting Date	Random Date	Random Date Formula
=RANDBETWEEN(100,365)	114	1/1/2012	4/24/2012	=C2+B2
=RANDBETWEEN(100,365)	268	1/1/2012	9/25/2012	=C3+B3
=RANDBETWEEN(100,365)	328	1/1/2012	11/24/2012	=C4+B4
=RANDBETWEEN(100,365)	142	1/1/2012	5/22/2012	=C5+B5

You can also use the following formula to pick a random date keyed off of the current date. In this example I am looking for a day within 30 days of the present date:

=TODAY() + RANDBETWEEN(0,30)

=TODAY() - RANDBETWEEN(0,30)

Using the same logic, you can select a date in or within the last year you could use the following formula:

=TODAY() + RANDBETWEEN(0,365)

=TODAY() - RANDBETWEEN(0,365)

RANDOM LETTERS

So how do we generate letters using the RANDBETWEEN formula? Return the Character specified by the code number from the character set on your computer.

=CHAR(RANDBETWEEN(65,90))   - returns one uppercase letter

=CHAR(RANDBETWEEN(97,122))   - returns one lowercase letter

=CHAR(RANDBETWEEN(65,90)) & CHAR(RANDBETWEEN(65,90))   - returns two uppercase letters

FINAL THOUGHTS

I would also like to point out that the number generated is not truly random but for most cases, can be used for generating generic data.

So how can you use the formula =RANDBETWEEN to help you complete your job?