Skip to content

Starter macros

123 ready-made Excel macros

Every one is built from MacroRoni's tested steps, explained in plain English, and ready to adjust to your sheet. Open any of them in MacroRoni's Starter gallery.

Clean up Text & IDs Reports Highlight Calculations Dates & time Print & share Sheets & safety Workbook tools

Clean up

Clean upTidy a messy listTrim spaces, remove hidden characters, delete empty rows and duplicates, then fit the columns.Changes data · backup on Clean upFix numbers stored as textTurn numbers typed as text back into real numbers so totals and sorting work.Changes data · backup on Clean upStandardize namesTrim spaces and capitalize each word in a column of names.Changes data · backup on Clean upFill the gapsWrite N/A into empty cells so missing values are easy to spot and count.Changes data · backup on Clean upSplit full namesSplit "First Last" in column A into separate columns starting at B.Changes data · backup on Clean upFill down grouped labelsCopy each label in column A down into the empty cells below it, so every row says which group it belongs to.Changes data · backup on Clean upRemove empty columnsDelete columns that have nothing in them, then fit the rest.Changes data · backup on Clean upTrim spaces everywhereRemove extra spaces and hidden characters from every cell in the sheet.Changes data · backup on Clean upClean up phone numbersStrip brackets, dashes, dots and spaces from phone numbers in column B, keeping only the digits.Changes data · backup on Clean upTidy email addressesTrim spaces and make every email address in column C lowercase.Changes data · backup on Clean upFix ALL CAPS namesTurn names typed in capitals in column A into Title Case.Changes data · backup on Clean upRemove dashes from IDsTake the dashes out of IDs or account numbers in column A.Changes data · backup on Clean upClear N/A placeholdersFind every “N/A” typed into the sheet and remove it, leaving the cell empty.Changes data · backup on Clean upRound amounts to centsRound the amounts in column B to 2 decimal places and show them as currency.Changes data · backup on Clean upClean up an imported reportFor pasted data: remove hidden characters and spaces, fix text numbers, delete empty rows and columns.Changes data · backup on Clean upRemove duplicate emailsTidy the emails in column C, then delete rows whose email is already listed.Changes data · backup on Clean upStrip all formattingRemove colors, fonts and borders so the data is plain again, then fit the columns.Changes data · backup on Clean upMake Yes/No answers consistentTurn y, Y, yes, true and similar answers in column D into exactly “Yes” or “No”.Changes data · backup on Clean upMake a place name the same everywhereChange NY, N.Y. and NYC in column C to “New York”. Edit the spellings for your own data.Changes data · backup on

Text & IDs

Text & IDsAdd leading zeros to IDsMake every ID in column A six characters long, like 000123, and keep the zeros.Changes data · backup on Text & IDsFix ZIP codes that lost their zeroExcel drops the leading zero from ZIP codes like 02134. Put it back in column E.Changes data · backup on Text & IDsGet the domain from emailsWrite the part after @ (the company domain) from column C into column D.Changes data · backup on Text & IDsTurn emails into user namesWrite the part before @ from column C into column D.Changes data · backup on Text & IDsPull out first namesWrite the first word of each full name in column A into column B.Changes data · backup on Text & IDsPull out last namesWrite the last word of each full name in column A into column C.Changes data · backup on Text & IDsMake initialsWrite initials (like JS for Jane Smith) from the names in column A into column B.Changes data · backup on Text & IDsJoin first and last namesPut first names (A) and last names (B) together with a space in column C.Changes data · backup on Text & IDsPut a prefix on every IDAdd “INV-” in front of each value in column A. Change the prefix to suit.Changes data · backup on Text & IDsMask account numbersKeep only the last 4 characters of each value in column B and hide the rest with ****.Changes data · backup on Text & IDsCount charactersWrite the length of each value in column A into column B, to spot text that is too long.Changes data · backup on Text & IDsTidy product codesMake product codes in column A uppercase with no spaces.Changes data · backup on

Reports

ReportsMake a report look professionalDark header row, banded rows, borders, frozen header and fitted columns.Doesn't change values ReportsPrint-ready reportLandscape on one page wide, header repeated on every page, page numbers in the footer.Doesn't change values ReportsSave the sheet as a PDFFit the sheet to the page and save it as a PDF next to the workbook.Doesn't change values ReportsExport to CSVSave the current sheet as a CSV file for other systems.Doesn't change values ReportsMonthly report layoutHeader, banded rows, borders, currency in column C, frozen header and landscape page.Doesn't change values ReportsTurn the list into a tableMake A1:H500 an Excel table with filter buttons and banded rows.Doesn't change values ReportsAdd data bars to amountsShow a bar in each amount cell in column C so big and small values stand out at a glance.Doesn't change values ReportsColor-code numbers (heat map)Color the numbers in B2:F500 from red (low) through yellow to green (high).Doesn't change values ReportsClean dashboard lookHide gridlines, style the header, use a modern font and zoom out a little.Doesn't change values ReportsQuick stats boxWrite the total, average, largest and smallest of column B into a small box at H1:I4.Changes data · backup on ReportsTop 10 reportSort by column B from highest to lowest and highlight the top 10.Changes data · backup on ReportsSort by date, newest firstSort the table by the dates in column C so the most recent rows are at the top.Changes data · backup on ReportsSort by name A to ZSort the table alphabetically by column A.Changes data · backup on ReportsSort by region, then nameGroup rows by the region in column C, then sort names within each region.Changes data · backup on ReportsReady for reviewStyle the header, freeze the top row and first column, and fit the columns.Doesn't change values ReportsMake a sheet easier to readLarger text, wrapped and top-aligned cells, and rows that fit their contents.Doesn't change values

Highlight

HighlightHighlight overdue itemsColor the whole row red when the date in column F is in the past.Doesn't change values HighlightFlag what's due this weekColor rows yellow when the date in column F is within the next 7 days.Doesn't change values HighlightSpot large amountsColor amounts over 1,000 in column B green.Doesn't change values HighlightFind duplicates and missing cellsColor repeated IDs in column A and every empty cell, without changing any values.Doesn't change values HighlightHighlight the top 10Fill the 10 highest numbers in column B green. It updates when the numbers change.Doesn't change values HighlightHighlight the 5 lowestFill the 5 lowest numbers in column B red.Doesn't change values HighlightHighlight negative numbersFill every number below zero in B2:F1000 red.Doesn't change values HighlightFlag zero amountsFill amounts that are exactly 0 in column B yellow.Doesn't change values HighlightHighlight a keywordFill every cell that mentions “urgent” (in any capitals). Change the word to suit.Doesn't change values HighlightHighlight weekend datesFill dates in column A that fall on a Saturday or Sunday.Doesn't change values HighlightFlag amounts over budgetFill amounts over 1,000 in column C red. Change the limit to your budget.Doesn't change values HighlightHighlight low stockFill stock levels of 10 or fewer in column D orange so you know what to reorder.Doesn't change values HighlightHighlight repeated IDsFill IDs in column A that appear more than once.Doesn't change values HighlightHighlight missing valuesFill every empty cell in the data so gaps are easy to see.Doesn't change values HighlightColor-code scoresColor the scores in column B from red (low) to green (high).Doesn't change values HighlightGray out finished rowsFor each row whose status in column E is “Done”, fill the row gray.Doesn't change values HighlightHighlight rows due in 3 daysFill the whole row when the date in column F is within the next 3 days.Doesn't change values HighlightFlag items older than 30 daysFill dates in column F that are more than 30 days ago.Doesn't change values HighlightHighlight expired datesFill the whole row when the expiry date in column D has passed.Doesn't change values HighlightFind TBD placeholdersFill every cell that still says “TBD” so nothing unfinished slips through.Doesn't change values

Calculations

CalculationsAdd a totals rowAdd a bold total under column B and show it as currency.Changes data · backup on CalculationsRunning totalAdd a running total of column B in column C.Changes data · backup on CalculationsCount and total one categoryCount the rows where column A says Sales and add up their amounts from column B.Changes data · backup on CalculationsPercent of totalShow what share of the column B total each row is, in column D.Changes data · backup on CalculationsPercent changeCompare this period (C) with last period (B) and show the change as a percent in D.Changes data · backup on CalculationsProfit marginWork out the margin from price (B) and cost (C) as a percent in column D.Changes data · backup on CalculationsAdd sales taxWork out 7% tax on the amounts in column C and write it in column D. Change 0.07 to your rate.Changes data · backup on CalculationsApply a discountWrite the price after a 10% discount next to each price in column B.Changes data · backup on CalculationsWork out commissionWork out 5% commission on the sales in column B, in column C.Changes data · backup on CalculationsRank from highest to lowestGive each value in column B its rank (1 = highest) in column C.Changes data · backup on CalculationsTurn scores into letter gradesTurn the scores in column B into A, B, C, D or F in column C.Changes data · backup on CalculationsTotal each rowAdd up columns B to F on every row and write the total in column G.Changes data · backup on CalculationsAverage each rowAverage columns B to F on every row and write it in column G.Changes data · backup on CalculationsPass or failWrite Pass when the score in column B is 60 or more, and Fail otherwise, in column C.Changes data · backup on CalculationsAdd 20% VATWrite the price including 20% VAT next to each net price in column B.Changes data · backup on CalculationsMonthly loan paymentA small calculator: type the rate, years and amount in B1:B3 and see the monthly payment in B4.Changes data · backup on CalculationsCount the filled rowsWrite how many rows have something in column A into E1.Changes data · backup on CalculationsQuantity times priceMultiply quantity (B) by price (C) into column D and add a grand total at the bottom.Changes data · backup on CalculationsConvert kilometers to milesWrite the miles for each distance in kilometers (column B) into column C.Changes data · backup on

Dates & time

Dates & timeAge from date of birthWork out each person's age in years from the birth dates in column B, in column C.Changes data · backup on Dates & timeDays until dueCount the days from today until each due date in column C, in column D.Changes data · backup on Dates & timeDays overdueShow how many days each due date in column C is late (0 if not late), in column D.Changes data · backup on Dates & timeMonth name from a dateWrite the month (January, February…) of each date in column A into column B.Changes data · backup on Dates & timeDay of the weekWrite the weekday (Monday, Tuesday…) of each date in column A into column B.Changes data · backup on Dates & timeQuarter from a dateWrite Q1, Q2, Q3 or Q4 for each date in column A into column B.Changes data · backup on Dates & timeYear from a dateWrite the year of each date in column A into column B, ready for grouping.Changes data · backup on Dates & timeWorking days between datesCount the weekdays between the start (B) and end (C) dates in column D.Changes data · backup on Dates & timeHours workedWork out the hours between the start (B) and finish (C) times in column D.Changes data · backup on Dates & timeStamp today's dateWrite “Updated” and today's date at the top of the sheet.Changes data · backup on Dates & timeEnd of the monthWrite the last day of the month for each date in column A into column B.Changes data · backup on Dates & timeShow dates the same wayShow every date in column C as mm/dd/yyyy, centered.Doesn't change values

Print & sharePrint on one pageLandscape, narrow margins, centered, with gridlines, the header row on every page and page numbers.Doesn't change values Print & shareSheet name and page numbersPrint the sheet name at the top and “Page 1 of 3” at the bottom of each page.Doesn't change values Print & sharePDF with page numbersAdd page numbers, fit the page, and save the sheet as Report.pdf next to the workbook.Doesn't change values Print & shareClean, then export CSVRemove extra spaces and hidden characters, then save the sheet as Export.csv.Changes data · backup on Print & shareSave, then draft an emailSave the workbook and open an Outlook draft so you can send it. Nothing is sent automatically.Doesn't change values Print & sharePrint just the tableSet the print area to A1:H50, center it on a landscape page, then ask before printing.Doesn't change values Print & sharePortrait print setupPortrait page, narrow margins, centered, with the header row on every page.Doesn't change values Print & shareGet the workbook ready to shareGo to A1 on every sheet so it opens neatly, then save.Doesn't change values

Sheets & safety

Sheets & safetySave a backup copySave a dated copy of the workbook before you make big changes.Doesn't change values Sheets & safetyFormat every sheet the sameStyle the header, freeze the top row and fit columns on every sheet in the workbook.Doesn't change values Sheets & safetyLock the finished sheetAsk first, then replace formulas with their results and protect the sheet from edits.Changes data · backup on Sheets & safetyShow everything that is hiddenUnhide every sheet, plus the hidden rows and columns on this sheet.Doesn't change values Sheets & safetySort sheet tabs A to ZPut the worksheet tabs in alphabetical order.Doesn't change values Sheets & safetyList every sheetWrite the name of every worksheet down column A, under a bold heading.Changes data · backup on Sheets & safetyUnlock, date-stamp, lock againRemove protection, write today's date in B1, then protect the sheet again.Changes data · backup on Sheets & safetyMark the sheet as finishedColor the tab green and add a “Finished” note to A1.Changes data · backup on Sheets & safetyHide working columnsHide helper columns X to Z so only the results show.Doesn't change values Sheets & safetyKeep an archive copy of the sheetDuplicate the sheet as “Archive” at the end of the workbook, so you keep a snapshot before making changes.Doesn't change values

Workbook tools

Workbook toolsTidy the whole workbookOn every sheet: fit the columns and freeze the top row. Then go to A1 everywhere.Doesn't change values Workbook toolsBack up, then clean the dataSave a copy of the workbook first, then trim, remove hidden characters, empty rows and duplicates.Changes data · backup on Workbook toolsRefresh data and saveRefresh every query and connection, then save the workbook.Doesn't change values Workbook toolsComma format on every sheetShow the numbers in B2:Z1000 on every sheet as 1,234.Doesn't change values Workbook toolsTrim spaces on every sheetRemove extra spaces from A1:Z1000 on every sheet in the workbook.Changes data · backup on Workbook toolsHide gridlines on every sheetHide gridlines on every sheet for a cleaner look, then go to A1 everywhere.Doesn't change values Workbook toolsRemove all linksRemove the hyperlinks from A1:Z1000. The text stays.Changes data · backup on