Sep 11, 2015

How many times have you wanted to test a layout in Word or Excel before actually putting the content in?

In this post, I'll show you a couple of simple ways of generating data in both Word and Excel.

Excel

When doing the Excel courses, the one piece of advice I give most is to use simple numbers. Anyone can work out simple percentages in their head e.g. What is 10% of 120? Once you start nesting formulas and functions, it can be tricky to use real world numbers.

There are a couple of features that help you!

1. Bulk-fill – If you preselect a range of cells, enter a value in the active cell and press CTRL ENTER, that value appears in all the selected cells.

In the screenshot below, I have selected B2:F7

If I then enter a value of 10 by typing into B2 and press CTRL ENTER when finished, the 10 is entered into every single selected cell.

2. The other key to allowing you to test formulas with simple numbers is the RANDBETWEEN function. It requires two arguments, the first is a small number and then second is a larger number. So an example of use might be =RANDBETWEEN(1,5), when entered into a cell, this will give you a random number between 1 and 5 each time the sheet is recalculated.

If I select B2:F7 again and this time I type =RANDBETWEEN(1,10) in B2 and press CTRL ENTER, I see all random numbers between that range in the cells.

And then if I force a recalculation by pressing F9, they all change, but remain between 1 and 10.

Using both bulk-fill and RANDBETWEEN means it's really simple to create a layout with formulas you know work in Excel. Once you have the layout complete, simply remove the random numbres from the cells and you are ready for real-world data to be entered.

Word

Although I am focusing on Word, you can use this tip in any application where you need to generate a large amount of text for layout purposes.

There are two ways to achieve this:

The first is to generate random english text by using =RAND – it requires a single parameter, although allows you to specify two.

If I were to type =RAND(4) into Word and press ENTER, I am asking it for four paragraphs of random text and it would appear as per the image below.

If I were to type =RAND(4,6) I would be asking it for four paragraphs each with 6 random sentences and it would look like the image below once I pressed ENTER.

Now, I could copy and paste that into any other program to use the bulk text for layout purposes.

The only downside is that the text is english. That is, I can read the words and because I can read the content, I might focus on that more than the layout.

This introduces the second way of generating random text. It is done by using =LOREM.

It works identically to =RAND in that if I supply a single value, I get that number of paragraphs with 3 sentences. If I supply two values, I get that number of paragraphs with that number of sentences.

So =LOREM(4) yields:

And =LOREM(4,6) yields:

The beauty of =LOREM as that as the text is psuedo-latin, our brains decide we can't understand it and thus, we gloss over the actual words. This in turn allows us to focus on the layout.

So there we have it, a couple of ways to layout Excel workbooks and Word documents (or design documents) with random data.