Changing Invoice Number in Excel

The world today runs on data, and this data is formatted and presented with the help of various data tools. The most popular one among them is the Microsoft Excel spreadsheet. The potential and capabilities of excel are considerably immense, provided the tools and functionalities available in the software are made use of optimally.

Steps to Change Invoice Number

Macros are a function that enables you to automate excel, thus helping you save time. A macro is an action that can be performed repeatedly. When a macro is created, it records the mouse clicks and keystrokes.

Step 1: Create an invoice in excel

Excel has a variety of invoice templates that one can choose from based on the details required in the invoice format. Once a template that meets the needs is selected, the user may proceed towards invoice creation. The invoice number should be entered into a separate cell.

Step 2: Locate the cell with the invoice number

To create the macro, the locational information of the cell containing the invoice number is required. The information can be accessed by selecting the relevant cell, as shown in the image below.

Step 3: Open Microsoft Visual Basic for Application

The next step would be to open the Microsoft VBA tool via the “Alt + F11” shortcut. Once this is done, the VBA Tool window will open.

Step 4: Select the entire Workbook

Under the Microsoft Excel Objects option displayed on the left half of your screen, proceed to click on “This Workbook”.

Step 5: Revise, copy and paste the code

A new window opens up on the right once “This Workbook” is selected. This is the window wherein the code has to be fed in. As displayed in the screenshot under step 2, “B2” is the location of the cell containing the invoice number. Having made a note of that, the following code needs to be pasted in the blank window. 

Private Sub Workbook_Open()

Range(“B2”).Value = Range(“B2”).Value + 1

End Sub

Step 6: Adjust the macro settings

Ensure that you have the “Developer” tab in your toolbar. It is normally displayed between the “View” and “Help” tabs. If it isn’t shown, click on the “File” tab, select “Options”, and then select “Customize Ribbon”. On the right side, the third box from the bottom, “Developer” should be ticked as shown in the image below.

Once this is done, go to the toolbar, click on the Developer tab, and select “Macro Security”. Select the boxes containing “Enable VBA macros” and the “Trust access to the VBA project object model”, as shown in the image below.

Step 7: Save the file as macro-enabled

When performing the same function, assign an appropriate file name and then proceed to save the file as “Excel Macro-Enabled Template”.

Step 8: Restart the system

Once the file is saved, exit the application and hit the restart button so the system can perform a restart. Once the system restarts, open the excel invoice file. Excel should have automatically updated the invoice number to the following invoice number in the sequence.

Step 9: Create a new invoice

Once the invoice has been filled and completed in all respects, the file will have to be saved under a new name, for instance, “Invoice-1001”. Always remember to save the file as an Excel Macro-Enabled Template.

These steps are to be compulsorily followed each time a new invoice is created.

Presently, many customised tools assist in invoice creation. One such tool is the ClearOne Invoice software, cloud-based software that is entirely secure, comprising all the necessary features with respect to invoice creation, billing and much more. All you have to do is log on to http://one.clear.in, sign up with your phone number, and you may proceed to use the tool.