Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: AutoFilling with the Alphabet.
Marlene is a teacher and she has students who love word searches. She finds it quite time consuming to make them, but the students seem to remember the course material much better when she uses them. Marlene wondered if there was some way to AutoFill a range of cells with letters of the alphabet, A through Z. That way she can use the feature to fill in the squares of the word search with letters, before she replaces some of those letters with the actual words to be searched.
The AutoFill tool in Excel has a few standard sequences it will fill automatically, such as dates and numeric sequences. The very powerful part of AutoFill, however, is that you can create custom lists that the tool uses just as easily as the built-in sequences. In order to create a custom list manually, you can follow these steps:
Figure 1. The Custom Lists dialog box.
To use the newly created custom list, just type one or two letters you want to start the sequence with, select those cells, and use the AutoFill handle to drag over as many cells as you want to fill.
There's another way to create the custom list that may be a bit easier, just in case you don't want to type twenty-six letters in the dialog box. Instead, if you already have the letters of the alphabet in twenty-six cells, follow these steps:
Of course, there is one drawback with using a custom list, especially when it comes to creating word searches: the letters added to blank squares are always in a predictable sequence, which could make finding the actual words a bit easier than you want. To make the puzzles a bit more challenging, it would be better to fill the non-word squares with random letters.
One easy way to get random letters is to use the following formula:
=CHAR(RANDBETWEEN(65,90))
This formula works because the RANDBETWEEN function returns a random numeric value between the two boundary values provided. In this case, it will return a value between 65 and 90, which are the ASCII values of the letters A and Z, respectively. The CHAR function is then used to convert this random numeric value into an actual letter.
If you create a lot of word search puzzles, then you may want to use a macro to fill a range of cells with random letters of the alphabet. There are any number of ways that such a macro could be put together; the following is one that is particularly flexible. It will work with either a pre-selected range (a range selected when you run the macro) or you can select a range after you run the macro.
Sub AlphaFill() Dim Cell, CellChars Dim Default, Prompt, Title Dim rangeSelected As Range Dim UpperCase As Boolean Title = "AlphaFill Cell Selection" Default = Selection.Address Prompt = vbCrLf _ & "Use mouse in conjunction with " _ & "SHIFT and CTRL keys to" & vbCrLf _ & "click and drag or type in name(s) " _ & "of cell(s) to AlphaFill" & vbCrLf & vbCrLf _ & "Currently selected cell(s): " & Selection.Address On Error Resume Next Set rangeSelected = InputBox(Prompt, Title, _ Default, Type:=8) If rangeSelected Is Nothing Then Exit Sub UpperCase = True Randomize For Each Cell In rangeSelected CellChars = Chr(64 + Int((Rnd * 26) + 1)) If Not UpperCase Then CellChars = LCase(CellChars) Cell.Value = CellChars Next End Sub
The macro code, as written, inserts the uppercase letters into whatever range you specify. If you want to use lowercase letters instead, then all you need to do is set the UpperCase variable to False rather than True.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7803) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: AutoFilling with the Alphabet.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
AutoFill is a great editing tool that comes in particularly handy when you are first creating a worksheet. You may, at ...
Discover MoreAutoFill can be a real timesaver if you often work with set lists of data. You can define your own custom lists and then ...
Discover MoreNeed to fill a range of cells with the days of the week? Excel makes it easy to do so using AutoFill.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-01-11 19:57:01
Leslie Glasser
A simple way to insert capitals in a column is to use =CHAR(ROW()+64) in successive rows starting from row 1. Use 96 in place of 64 for lower case.
If you want to start in a lower row use =CHAR(64+ROW(OFFSET(A1,0,0))) where A1 can be any cell in the top row.
2016-01-11 09:21:32
Jeaux Brown
LOVE the simple tip of adding custom lists. Thank you.
2016-01-11 08:22:51
For Marlene, the teacher, who wants to autofill alphabets for word search puzzles:
Excel is awesome, but there is a website for teachers that makes word searches and crosswords (and the answers) on the internet in just a few minutes (for free). You simply put in the words you want, and how wide and tall, and in a few seconds, you have your puzzle. You can then click on the answer sheet.
Here is the site:
http://puzzlemaker.discoveryeducation.com/WordSearchSetupForm.asp
2015-06-17 21:41:33
silvex3000
Tremendous - ABC AutoFill (AutoHotKey):
https://www.youtube.com/watch?v=xbU-5hYgK7w
extra options:
https://www.youtube.com/watch?v=ZfPPVzH8xKQ
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments