Fortuneomg

Excel has two useful functions when it comes to generating random numbers. The RAND and RANDBETWEEN function. Excel Easy #1 Excel tutorial on the net. To generate a list of random numbers, select cell A1, click on the lower right corner of cell A1 and drag it down. How To Put Auto Serial Number In MS-Excel. Excel Tips - Quickly Fill Series of Numbers in a Few Seconds Fill. Excel is Magic# How to Auto Create Serial Number in excel video Tutorial.

If you need to create invoices for your business, you can use one of the many invoice templates found on Office.com. Although these templates are a great starting point, they won’t automatically generate unique invoice numbers—you’ll need a macro for that.

Excel MVP Bill Jelen graciously offered up a video that demonstrates how to create just such a macro, along with a number of other useful tips. Thank you, Bill!

What’s in the video

Here’s a breakdown of the overall proccess:

1. 1:03: Make whatever customizations you like, and then save the invoice template in a file format (.xslm) that is macro-friendly.
2. 1:26: Create a macro that automatically 1) increments the invoice number and 2) clears cells on the worksheet so you can start fresh on the next invoice.
3. 3:00: Attach your macro to a shape, so that you can run it easily from the invoice worksheet.
4. 3:44: Create another macro that lets you save a copy of the invoice as a macro-free file (.xslx) with a unique file name.
5. 4:33: Attach that second macro to a different shape in the workbook.
6. 4:58: Save the master version of the workbook as a macro-enabled (.xslm) file.

The macro code

If you want to try this yourself, feel free to reuse Bill’s VBA code for the two macros.

If you’ve never used macros before, your copy of Excel may have macros turned off, even for .xlsm file types. To check, use the keyboard sequence ALT+T, M, S to get to macro settings in the Trust Center dialog box. If Disable All Macros without Notification is selected, choose Disable All Macros with Notification instead. If you use this option, Excel prompts you to enable macros each time you open a file that has them.

Macro #1: Generate the next invoice number

Sub NextInvoice()
Range(“E5”).Value = Range(“E5”).Value + 1
Range(“A20:E39”).ClearContents
End Sub

Macro #2: Save invoice with new name

Sub SaveInvWithNewName()
Dim NewFN As Variant
‘ Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = “C:aaaInv” & Range(“E5”).Value & “.xlsx”
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

For more great tips, visit Bill’s site, MrExcel.com. Also, if you’re new to the concept of VBA and macros, and you’d like to explore further, check out Get started with VBA in Excel 2010 or Create or delete a macro.

— Anneliese Wirth

Unlike other Microsoft Office programs, Excel does not provide a button to number data automatically. But, you can easily add sequential numbers to rows of data by dragging the fill handle to fill a column with a series of numbers or by using the ROW function.

Tip: If you are looking for a more advanced auto-numbering system for your data, and Access is installed on your computer, you can import the Excel data to an Access database. In an Access database, you can create a field that automatically generates a unique number when you enter a new record in a table.

What do you want to do?

Fill a column with a series of numbers

1. Select the first cell in the range that you want to fill.

2. Type the starting value for the series.

3. Type a value in the next cell to establish a pattern.

   Tip: For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4.

4. Select the cells that contain the starting values.

   Note: In Excel 2013 and later, the Quick Analysis button is displayed by default when you select more than one cell containing data. You can ignore the button to complete this procedure.

5. Drag the fill handle across the range that you want to fill.

   Note: As you drag the fill handle across each cell, Excel displays a preview of the value. If you want a different pattern, drag the fill handle by holding down the right-click button, and then choose a pattern.

   To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.

   Tip: If you do not see the fill handle, you may have to display it first. For more information, see Display or hide the fill handle.

   Note: These numbers are not automatically updated when you add, move, or remove rows. You can manually update the sequential numbering by selecting two numbers that are in the right sequence, and then dragging the fill handle to the end of the numbered range.

Serial Numbers Nero

How To Create Number List In Excel

Use the ROW function to number rows

1. In the first cell of the range that you want to number, type =ROW(A1).

   The ROW function returns the number of the row that you reference. For example, =ROW(A1) returns the number 1.

2. Drag the fill handle across the range that you want to fill.

   Tip: If you do not see the fill handle, you may have to display it first. For more information, see Display or hide the fill handle.

• These numbers are updated when you sort them with your data. The sequence may be interrupted if you add, move, or delete rows. You can manually update the numbering by selecting two numbers that are in the right sequence, and then dragging the fill handle to the end of the numbered range.

• If you are using the ROW function, and you want the numbers to be inserted automatically as you add new rows of data, turn that range of data into an Excel table. All rows that are added at the end of the table are numbered in sequence. For more information, see Create or delete an Excel table in a worksheet.

To enter specific sequential number codes, such as purchase order numbers, you can use the ROW function together with the TEXT function. For example, to start a numbered list by using 000-001, you enter the formula =TEXT(ROW(A1),'000-000') in the first cell of the range that you want to number, and then drag the fill handle to the end of the range.

Display or hide the fill handle

The fill handle displays by default, but you can turn it on or off.

How To Generate Serial Numbers In Excel Automatically Order

1. In Excel 2010 and later, click the File tab, and then click Options.

   In Excel 2007, click the Microsoft Office Button , and then click Excel Options.

2. In the Advanced category, under Editing options, select or clear the Enable fill handle and cell drag-and-drop check box to display or hide the fill handle.

Note: To help prevent replacing existing data when you drag the fill handle, ensure the Alert before overwriting cells check box is selected. If you do not want Excel to display a message about overwriting cells, you can clear this check box.

Add Serial Number In Excel