TTS Blogs, Excel

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

Original in-built cell styles

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
 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.

New cell styles

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.

Learn more with Training The Street

Training The Street offers Corporate Learning Solutions, Public Courses and Self-Study Courses to help you and your team unlock your career potential.

Browse our range of Excel courses below.

Applied Excel - Public Course

Applied Excel - Public Course

What you’ll learn

  • Excel Best Practices and Efficiencies
  • Working with Client Data
  • Analyzing Data
  • Scenario Analysis
  • Introduction to Graphing and Visualizations

Excel Best Practices

Excel Best Practices - Self-Study Course

What you’ll learn

  • Basic Excel setup – macro security, iterations, calculations settings
  • Financial modeling essentials – appropriate color schemes, page setup, and customizing toolbars in Excel
  • Common shortcut keys and other efficiency tips
  • Understanding how to efficiently design historical inputs and forecast financial projections

Applied Excel: Working with Data Sets and Scenario Analysis

Applied Excel - Self-Study Course

What you’ll learn

  • Excel Best Practices and Efficiencies
  • Working with Client Data
  • Analyzing Data in Excel
  • Scenario Analysis
  • Macros and User-Friendly Tools
  • Pivot Tables & Data Tables
  • Enhancing Model Integrity
  • Introduction to VBA & Macros
  • Array Formulas