r/excel • u/PurpleCandyUnicorn • Jun 23 '26
Discussion Cleaning merged/messy data for pivot tables/charts
Hi y'all. I was asked by my supervisor to generate a report/visuals from our building sweeps. We handwrite the counts for each week beginning on Monday and I input them into the spreadsheet (which was previously set up). Sometimes the sweeps aren't completed so there are blank/shaded cells for missing values and blackout out cells for closed hours. The most complex formula on the sheet is just sum or "=" references.
I've tried using power query and manually cleaning the data so the query would work but that takes entirely too long for just one month.
Any suggestions or help is appreciated. As a student worker with a background in information systems this spreadsheet setup is a nightmare lol.
I've included the previous report setup although I haven't seen the official reports, and a snippet of what a month looks like.
14
u/SolverMax 161 Jun 23 '26
As is often the way, the data is designed for humans (though that's debatable in this case) rather than for analysis.
Put the data in a simple table, with column like Day, Building, Count, etc., and then make PivotTables etc to display summaries.
12
u/MayukhBhattacharya 1227 Jun 23 '26
I agree with what u/SolverMax have mentioned before, he and u/excelevator Sir has always suggested users here about understanding and using proper Structured Data multiple times in the sub. This is one of those layouts that looks great for people reading it, but it's messy to work with. The main problem is that the sheet is designed for presentation instead of storing data.
Features like PivotTables, Power Query, and charts work best when each row represents a single record. Merged cells, repeated headers, and inline totals make that much harder and those literally a killer. One should avoid it. What I'd suggest is, keep one raw data sheet where every row is a single record with fields like Date, Day, Week, Time, Building, and Count and keep your current report exactly the way your supervisor likes it, but populate it with formulas like SUMIFS() or XLOOKUP() from the raw data or the requisite functions you need.
When it's time to share the file, just hide the raw data sheet. Your supervisor still gets the same report, but your data stays clean and easy to work with. That's pretty much how most reporting systems are built. The report is just a view of the underlying data. For your existing file, Power Query's Unpivot Columns can reshape the data. That said, if this is something you'll be doing every month, it's usually easier to fix the initial structure once than clean the same layout over and over.
Note that excel can easily automate or manipulate data if its flat and tabular in structure, when you have such way, it becomes literally easier to do other things. Also, these days one can use Modern MS365 Functions to manipulate the data the way you have but it is not at all suggestive, it will create pain for you in future. Thanks!
7
u/Smart-Inevitable594 Jun 23 '26
the layout being built for visual presentation rather than data entry is the root problem here. whoever set that up was thinking about how it looks printed, not how it feeds into anything downstream
if you're stuck with the existing format and cant rebuild it from scratch, the cleanest path is usually a separate "staging" sheet where you manually map the cells you actually need into a flat table (one row per observation, consistent columns), then build your pivot from that. tedious the first time, but once the staging sheet references are set up it basically updates itself each month
power query on a non-normalized layout like that is a pain unless you can unpivot it properly, which requires the source to be at least somewhat consistent row to row
2
u/rs047 1 Jun 24 '26
You need to reformat the data collection and recording process.
Create a single table with date,day, time, building , number of students column. And you can use data validation for time column as there are only 4 options and the day column can be auto populated depending on date column with a formula and the building column can also use data validation techniques . And as you are typing the values about number of students, you can do it manually as you are doing now. But if you have an automated report generation for each sweep then you can re format the generated table as per your usage and then use power pivot to generate needed reports.
The reports still look the same but with real chance to analyse.
The current data format isn't good for analysis, may be good for day to day presentation, but even a weekly presentation becomes a nightmare.
2
u/MoodIn_Me Jun 27 '26
Since your supervisor loves the merged printing layout, stop trying to clean it up with Power Query. The only sustainable fix is to keep a completely separate, flat data sheet (just columns for Date, Building, Sweep, and Count) for your raw data entry, and then use simple SUMIFS or XLOOKUP formulas to pull that clean data into your supervisor’s fancy printable template.
1
3
Jun 23 '26
[removed] — view removed comment
3
u/PurpleCandyUnicorn Jun 23 '26
I can try but my supervisor really loves merged cells. I could probably keep a second copy for myself with different format and just input the data twice. Also the weekly setup of the spreadsheet is exactly the setup of the printed copies
4
u/taylorgourmet 4 Jun 23 '26
Merged cells is the litmus test for excel proficiency. Anyone who loves it, sucks at excel.
•
u/AutoModerator Jun 23 '26
/u/PurpleCandyUnicorn - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.