Mastering Excel Utility: Essential Add-ins and Macros for Productivity

Build Your Own Excel Utility: Simple VBA Scripts for Common Tasks

What this guide covers

• Goal: Teach you how to create a small, reusable Excel utility using VBA to automate common tasks (cleanup, formatting, data consolidation, simple reports).
• Audience: Intermediate Excel users comfortable with the Ribbon and basic formulas but new to VBA.
• Deliverable: A compact workbook with a code module and ribbon buttons (or Quick Access Toolbar shortcuts) that run 5 real VBA scripts.

The five included VBA scripts (what they do)

1. Clean Columns — Trim spaces, remove non-printable characters, convert numbers stored-as-text to real numbers, and remove duplicate blank rows in selected columns.
2. Standardize Dates — Detect common date formats in a selection and convert them to one user-specified format (e.g., yyyy-mm-dd).
3. Auto-Format Table — Convert a selection to an Excel Table, apply a consistent style, autofit columns, freeze header row, and add filters.
4. Consolidate Sheets — Combine similarly-structured sheets in a workbook into a single summary sheet with a source column.
5. Export CSV by Filter — Prompt for a filter value, copy filtered rows to a new workbook, and save as CSV named with the filter plus timestamp.

File & setup steps

1. Open a new workbook and press Alt+F11 to open the VBA editor.
2. Insert a Module and paste the provided VBA procedures (see examples below).
3. Optional: Add buttons to the Quick Access Toolbar or create a small Ribbon tab using the Custom UI Editor to run macros.
4. Save the workbook as a macro-enabled file (.xlsm).

Example VBA snippets

• Clean Columns (trim, remove non-printables, convert numbers):

vb
Copy CodeCopied
Sub CleanColumns() Dim rng As Range, c As Range   On Error Resume Next   Set rng = Application.InputBox(“Select range to clean”, Type:=8)   If rng Is Nothing Then Exit Sub   For Each c In rng

 
If Not IsEmpty(c) Then   c.Value = Application.WorksheetFunction.Trim( _               Application.WorksheetFunction.Clean(c.Value))   If IsNumeric(c.Value) Then c.Value = Val(c.Value) End If 
Next c   MsgBox “Clean complete”, vbInformation End Sub 


Consolidate Sheets:

vb
Copy CodeCopied
Sub ConsolidateSheets()   Dim ws As Worksheet, tgt As Worksheet, lastR As Long, tgtR As Long   Set tgt = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))   tgt.Name = “Consolidated”   tgtR = 1   For Each ws In ThisWorkbook.Worksheets

 
If ws.Name <> tgt.Name Then   lastR = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row   ws.Range("A1", ws.Cells(lastR, ws.UsedRange.Columns.Count)).Copy _     Destination:=tgt.Cells(tgtR, 1)   tgtR = tgt.Cells(tgt.Rows.Count, 1).End(xlUp).Row + 1 End If 
Next ws   MsgBox “Consolidation done”, vbInformation End Sub 

Usage tips

Back up your workbook before running macros.
Test scripts on sample data to tweak for your layout (header rows, blank rows).
Add error handling and logging for production use.
For repeated distribution, create an Add-in (.xlam) so utilities are available across workbooks.

Next steps (recommended)

Implement the five macros in your workbook and test each on representative data.
If you want, I can generate a complete .xlsm with these macros and a simple Ribbon — tell me which macros you want and any custom rules (date format, target table style).