Excel Tips and Tricks / Business Reporting Question: Can you help me to apply custom format for employee numbers to all excel workbooks on my computer? I would like the employee numbers to have preceding zeros such as 001,002,003 etc.

Answer: Yes, with the Personal Macro workbook option

Why: For the custom format to apply to all workbooks on the computer

Applies To: Excel 2000, XP, 2003, 2007 and 2010

1. Enter the data as given in the example below.

 

2. For Excel 2007 & 2010, click on the View ribbon and then Macros-Record, New Macro. For other versions of Excel, click on Tools-Macro-Record, New Macros.

3. Make changes as given below.

 

4. Click Ok and select the relative reference button. (This enables the macro to run in any location within the worksheet.) For Excel 2007 & 2010 click View-Macros-Use Relative Reference. Other versions of Excel click on the relative reference icon as given below.

 

5. Right click in any cell. Select format cells and make changes as given below.

 

6. Click ok; select the relative reference button again.

7. Excel 2007 & 2010- select View-Macros-Stop recording and lower versions of Excel -Tools-Macro-Stop Recording.

8. Open the target workbook and highlight the respective employee numbers.

9. Excel 2007 & 2010- select View-Macros-View Macros and lower versions of Excel -Tools-Macro-Macros. The screen shot below will be displayed.

10. Click on the drop arrow next to macros in and select Personal.XLSB, select the Employee Numbers macro and click the run button.

 

The EmployeeNumbers macro will run in any workbook and apply the custom number format. In that way the process of applying custom number formats in the workbooks is automated.