How to Fill Odd Numbers in Excel (5 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, you will learn how to fill in odd numbers in Excel.


How to Fill Odd Numbers in Excel: 5 Suitable Examples

1. Use Fill Handle Tool to Fill Odd Numbers with a Specific Sequence

The Fill Handle tool is one of the widely used features of MS Excel. In this example, we will use this to create a list of odd numbers that have a specific sequence. You can not only fill in odd numbers but also odd patterns.

  • Look at the following image. The first two cells C5 and C6 have two consecutive odd numbers 1 and 3. Similarly, the cells D5 and D6 have two patterns AAA1 and AAA3.
  • How to create an odd number of pattern lists using these inputs?

Using Fill Handle Tool to Fill Odd Numbers with a Specific Sequence

  • First, select the odd inputs.

  • You can also select the numbers and patterns together.

  • Now hover the mouse on the bottom-right corner of the selection.
  • You will notice that the Fill Handle icon (like a plus sign) appears.
  • Drag this icon with your mouse down, until you want to stop.

Using Fill Handle Tool to Fill Odd Numbers with a Specific Sequence

  • After dragging till Row 19, we have gotten the following lists: 1,3,5,7,9,…. and AAA1, AAA3, AAA5,……., etc.

How to Enable Fill Handle in Excel:

Not seeing the Fill Handle icon? Probably, it’s turned off in the Excel Options menu. To fix this, press Alt+F+T. This will take you to the Excel Options window. Now, from the Advanced section, look for the Editing options pane. Then find the “Enable fill handle and cell drag-and-drop” checkbox, mark it, and press OK.

Enabling Fill Handle in Excel


2. Use the Fill Series Commands

2.1 Fill Odd Numbers with Arithmetic Sequence

  • To use the Fill Series command to fill odd numbers with arithmetic sequence, write the first odd number in cell B5 (it’s 1 here) and go to the Home tab.

  • From the Editing group, click on Fill, and select the Series option.

Filling Odd Numbers with Arithmetic Sequence

  • The Series window appears. Here, select Columns to locate the series (Series in section), and Linear as Series Type.
  • Fix a Step value. We will fill in odd numbers, so type an even step value here.
  • Fix a value on or before which the series will stop. Here the Stop value is 30.
  • Press OK.

Filling Odd Numbers with Arithmetic Sequence

  • Here we see the outcome in the image below: 1,3,5,7,………………….,29.


2.2 Fill Odd Numbers with Geometric Sequence

  • To create a geometric sequence of odd numbers, select Series Type as Growth and fix a Step value which is also an odd number (it must be an odd number this time). Here, it’s 3.

Fill Odd Numbers with Geometric Sequence

  • After pressing OK, here we get the following geometric series of odd numbers 1, 3, 9, 27, …………, 6561.


2.3 AutoFill Odd Numbers Keeping the Formatting Pattern Intact

  • To fill odd numbers while keeping the existing sequence, and keeping the formatting pattern intact, we have to write the first 2 odd numbers initially.
  • Then select a range and get to the Series window.

  • In this window, select the AutoFill type and press OK.

AutoFill Odd Numbers Keeping the Formatting Pattern Intact

  • Look at the consequence in the following image.


2.4 Fill Odd Numbers Following the Existing Trend

  • There is another way to create a 1,3,5,7… sequence of odd numbers.
  • First, type the first two odd numbers in the two cells in a column.
  • Then select a range.

  • After that, get to the Series window and mark the Trend checkbox.
  • For arithmetic series, select Linear type and press OK.

  • The result will be like the following image.

  • To fill odd numbers geometrically, mark the Trend checkbox, select Growth type, and press OK.

  • Look at the results now.


3. Use a Simple Formula to Create Odd Number Series with a Specified Step

We can also fill odd numbers with our own specified starting number and step using simple mathematical operations in Excel.

  • Look at the following image. Here, I have specified the starting number as 5 and the step as 4 (the step must be an even number).
  • Now, write the following in the first cell of the series, cell B5.
=D5
  • If you change that starting number in D5, the first number in the series will also change now.

  • Now, write the following formula in the next cell, cell D6, and drag the Fill handle to copy the formula down.
=B5+$E$5

  • Keep the step cell reference absolute ($E$5), since the step is supposed not to change while you copy the formula down.

4. Fill a Specific Range with Odd Numbers Using Mathematical Operations in Excel

In this example, we will see how to fill odd numbers in a specific rectangular range. You design your own range, here we have ours.

  • In the range, we have 6 columns and 10 rows. We will start from R1C1 with the number 1.
  • Type this number manually in C5.

  • Now, go to cell D5, and type the following formula, and press Enter. Then drag the Fill handle to the right to fill the blank cells with odd numbers.
=C5+2
  • When you reach cell H5, copy the formula down to cell H14.

  • We see that we have gotten the following so far. Now a few more steps are there to get the job done.

  • Write the following formula in cell C6 and copy the formula down the column.
=H5+2

Fill a Specific Range with Odd Numbers Using Mathematical Operations in Excel

  • Now, we have perfectly generated what we wished for!


5. Create a Random List of Odd Numbers Using RANDBETWEEN and ODD Functions

In the last example, we will see how to fill random odd numbers between top and bottom values. We will use the RANDBETWEEN and ODD functions in this regard.

  • To apply the formula, first, select the range and type the following formula in the formula bar.
=ODD(RANDBETWEEN($D$5,$E$5))
  • The D5 and E5 cells have the lower and upper-end values here.

Create a Random List of Odd Numbers Using RANDBETWEEN and ODD Functions

  • Now, press Ctrl+Enter. Then you will see the following output.

Note:

The RANDBETWEEN function is dynamic and recalculates each time a new operation is performed.


Download Practice Workbook

Before we start, please download the practice workbook so that you can do exercises while reading this article.


Conclusion

So, this was the discussion for today! I hope you find this article useful. If you still face any problems filling in odd numbers in Excel, please leave us a comment.


<< Go Back to | Excel for Math | Excel ODD or EVENLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo