Want to get rid of all the unnecessary built-in cell styles in a new Excel worksheet?

We all love Excel for its versatility, but if you’ve ever found yourself wrestling with the default cell styles, you’re not alone.

Fear not! I’m here to show you a handy Visual Basic for Applications (VBA) macro that will allow you to effortlessly remove those default cell styles, putting you in control of your Excel aesthetics.

Step 1: Accessing the VBA Editor

1. Press Alt + F11 to open the VBA editor.
2. Navigate to the workbook where you want to remove the built-in cell styles.

Step 2: Insert a New Module

1. In the VBA editor, right-click on any item in the Project Explorer.
2. Select Insert and then Module to add a new module. (Keyboard shortcut ALT + I + M)

Step 3: Paste the VBA Macro Code

Paste the following VBA code into the module

Sub DeleteAllInbuiltStyles()
 Dim style As style
 ' Iterate through each style in the workbook
 For Each style In ThisWorkbook.Styles
 ' Check if the style is a built-in style ("Normal" is a built-in style but will not be deleted)
 If style.BuiltIn Then
 ' Delete the style
 On Error Resume Next ' Handle errors if the style is in use
 ThisWorkbook.Styles(style.Name).Delete
 On Error GoTo 0
 End If

Step 4: Run the Macro

1. Close the VBA editor (before that, read the steps 5 & 6 and below).
2. Press Alt + F8 to open the “Macro” dialog box.
3. Select “DeleteAllInbuiltStyles” from the list.
4. Click “Run” to execute the macro.
5. Alternatively, before closing out the VBA editor, just run the macro by hitting F5 (or Fn F5 if your function keys are doubled up with other keys) and you can skip the above steps 1 to 4.
6. If you do not want the macro to sit in your file – delete it before closing out the VBA macro

Step 5: Marvel at the Magic

That’s it! You’ve just removed those pesky built-in cell styles, making your workbook look cleaner like the image below.

Now you can go ahead and personalize your cell styles by defining new ones… This macro is especially useful when working with templates or importing data from various sources.

Feel free to explore and modify the code to suit your specific needs. Happy Excel-ing!

This article was written by Rasna Saini, Financial Training Instructor at Training The Street.