Bottom Line: Walk through five essential macros I use daily to clean up data, format sheets, and improve workflow efficiency—saving hours every week.
Skill Level: Intermediate
Watch the TutorialWatch on YouTube & Subscribe to our Channel
Download the Example FileThe workbook I use in the video can be downloaded by clicking below. This file also contains the code for all 5 macros that we cover in this post (and a few extra).
Every Day Excel Macros Files.zipDownload
1. Fill Down Blank Cells InstantlyBlank cells can be a problem when working with data, especially for pivot tables and formulas that depend on complete datasets. Instead of manually filling them in, this macro quickly finds each blank cell in a column and fills it with the last non-blank value above it. The result is a clean, fully populated column with values converted to static entries to prevent accidental changes.
2. Automatically Format a Sheet TitleClick to enlargeA properly formatted sheet title makes a workbook more readable, especially when sharing reports. This macro inserts a new row at the top, merges and centers the title, applies bold formatting, and uses the sheet name as the default title. Instead of manually adjusting headers every time, you can format them instantly with one click.
3. Create a Table of Contents for Easy NavigationLarge workbooks with dozens of sheets can be frustrating to navigate. This macro automatically generates a Table of Contents on a new sheet, listing all sheet names with clickable hyperlinks. It even adds a link taking you back to the TOC that you can paste onto each sheet, allowing you to return to the main index in one click.
BONUS! If you prefer a table of contents that has an image tile of each page, for a more visual-based navigation experience, I have a post that explains it in more detail.
And I also have a post on a macro for a table of contents that updates automatically when changes are made to the workbook.
4. Instantly Format Pivot ChartsClick to enlargeExcel’s default pivot charts often require tedious manual adjustments to look polished. This macro applies a clean, professional format instantly—it sets a descriptive title, adjusts spacing, applies a consistent color scheme, and removes unnecessary gridlines and clutter. With a single click, your charts go from raw data visuals to boardroom-ready presentations.
5. Backup Your Workbook with One ClickBefore making major edits, it’s always a good idea to save a backup. This macro duplicates the current file, appends a timestamp to the filename, and saves it in the same folder—no need to manually rename anything. It’s a quick and effortless way to create a safety net.
Customize Your Own My Macros TabClick to enlargeIf you're using macros regularly, adding them to a custom “My Macros” tab in Excel can make your workflow even more efficient. This allows you to access your favorite macros with a single click—no more opening the VBA editor or hunting through menus.
Use this tutorial to make the customized tab: How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar
I also have a tutorial on how to use ChatGPT to help with the macro writing process.
Do More with Macros and VBAMacros like these are built using VBA (Visual Basic for Applications), a computer programming language developed by Microsoft. If you'd like to learn more about using VBA to accomplish amazing things in Excel, I recommend our in-depth training program, The VBA Pro Course. It's got practical and valuable information whether you are just starting with VBA or already have plenty of expertise.
ConclusionThese five macros help automate common Excel tasks, saving hours of manual work. Which one will you try first? Or do you have a favorite macro of your own? Drop a comment below!
Link to post: 5 Excel Macros I Use Every Day to Save HOURS