Add blank row in excel spreadsheet based on day of week

New Contributor

Help - I have a few massive excel spreadsheets created by importing data from another source via copy/paste and need to separate out the data by week (Mon-Sun) - is there a simple (read easy for excel dummies) way to make excel add a blank row above each occurrence of "Mon" in my date column (dates are formatted as Mon 3/30/2020 for example)?


2 Replies


actually need to separate by week mon-sun .... too many don't have mon in data


@maryfrecker01 When you want to add an empty row based on some criteria, you're talking Visual Basis programming which is NOT "easy for dummies". You're also talking about "massive spreadsheets" and importing data via copy/paste. Such a manual process is prone to error is you're not careful and comfortable using Excel. It sounds more like a task for "Get&Transform" a.k.a. Power Query. But, that's NOT "easy for dummies" either.


Rather than inserting an empty row, could you live with highlighting Mondays (or changes in week numbers) in your data set (sorted by date)? That could be done with Conditional Formatting, relatively easy, provided your data is clean and consistent.


Can you upload a sample of your data (deleted any private and confidential information), spanning a couple of weeks and indicating how you want to separate/highlight the weekly data? Then, it may become clear how your data looks like with regard to structure, formatting, sorting, etc. No guarantees for an "easy" solution, though.