•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Excel Column Conditional Formatting
Our take
Managing daily metrics for a sports team in Excel can be streamlined with effective conditional formatting. To ensure that each day's column accurately reflects the current data without affecting previous entries, it’s crucial to set up your conditional formatting rules to reference only the specific day's data. This way, you can color-code athletes based on their performance relative to the team average without altering past metrics.
I am working in an excel that gets updated daily. We are tracking daily values for metrics for a sports team (think training load, distance, etc.) I have conditional formatting for each individual day's column to identify those who are -2/-1/+1/+2 standard deviations from the team average with color coding so we can see who to rest more or can do extra conditioning. I need it to just pull that day when formatting the column. I managed to get the file set up and the rules set, but now when I add a new day's data, previous days' data are being affected and changing, and I believe being included as part of the average. I've been manually copy and pasting an individual day's formatting across and updating the entire sheet daily to fix it, but is there an easier way to stop this from happening?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
- My conditional formatting and data validation keeps breaking when it wasn't previously.I have a daily report I have to review and I built a workbook to make the process more digestible. Here is the basic structure of my workbook/worflow: Each day I extract a report to a CSV file. I save it in a designated folder and replace the previous days report. Sheet 1 has a power query mapped to where I save to report I refresh the data which updates the table. I apply some filters/delete items/sort rows (I deleted via deleting table rows). I have a macro button that sends all of my filtered and sorted data to my Daily_Maint table on sheet 2. Sheet 2 Daily_Maint table I have additional columns for manual data. One column on the end for comments (which has no issues). Second to last column is a review status column which has data validation to a list on a separate sheet (Completed, outstanding, system maintenance, circle back). I have (or had) conditional formatting for the list options to color code the list options (green,red, yellow, orange respectively). In another column (one which lists different users) I had conditional formatting to change the color for 2 specific users. In a column that lists client number, I have the cells formatted to insert letters before the numbers. Applied to the whole table I have conduit for atting which it looks at the column with client numbers (aka RM #'s) and when the number is diff from the cell above it, I have formatted a border across the whole table. (This visually separates groups of maintenance by customer). Once I have completed my review, I make a copy of the sheet and rename it to the date of the report. I delete the rows in the Daily_Maint table and do it all again the next day. Recently our drives were remapped and I had to remap my power query. When that happened my macro broke... Which didn't make sense because the macro and power query shouldn't talk to each other. As if by magic the macro started working again. What I'm struggling with: Data validation keeps breaking. This was rarely an issue before the remapping. But now it breaks every day and I can't figure out why. Conditional formatting for color coding keeps breaking. Origioally O tried applying it to =$O:$O which worked well until it didn't. I tried applying it to an absurdly large range like =$O$6:$O$20000 but after I delete rows it changes the applied to formula to what ever number was after the range I deleted. (Ex $O$500:$O$19500) Similar situation with the condition l formatting for the usernames. Tried =$M:$M and this worked really well for months... Until it stopped. Conditional formatting for the line to visually separate groups by customer. My original formula was =$F7<>$F6 applied to the full sheet. This would break every day but it was an easy enough fix I was fine living with. But after talking to AI I tried =INDEX($F:$F,ROW())<>INDEX($F:$F,ROW()-1). I thought it was a permanent fix because I tested it several times. But when I actually use it... It duplicates/breaks/still needs fixing every day. I've tried applying the formatting to "This Table", "This worksheet", and "Current Selection" and nothing is working. What am I doing wrong? Would it be more stable if I built everything on the worksheet and not in a table? I think I'm too close to it at this point to have any perspective and I don't have that much excel experience, most of my knowledge I've gained through building this spreadsheet. I appreciate any advice. submitted by /u/jazzlikebridge42 [link] [comments]
- Conditional formatting based on range of values in different column?https://preview.redd.it/l3guc8q9wfog1.png?width=1324&format=png&auto=webp&s=ec9fba2147eebc33af9e108241f82cb9163cf512 I've tried several different ways to do this but clearly I'm missing something. I have spreadsheet that shows the hours scheduled on all the machines at our facility. The dates in row 2 auto populate based on today's date. I want to make a rule that changes the formatting of rows 3 - 12 if any of the dates in row 2 are company holiday. For example, if B2 matches any of the dates in R3 through R12, I want B3 through B12 to fill in black with red text, so that way it shows that date is blacked out because we're closed, but if someone accidentally scheduled an order that day the machine hours will show in red so they know to reschedule it. On the original version of this spreadsheet I made a few years ago I had the same issue and end up just using =B$2=$R$3, so for each day I had to make 10 different rules for every holiday in column R. I knew that definitely was not the best way to do it but I was tired of messing with it and just needed it to work. I am updating this spreadsheet and it now shows the next 60 days instead of the next 14 days and I know there's got to be a better solution than creating 10 different rules for each day. I know =B$2=$R$3:$R$12 does NOT work and I think I'm starting to smell burnt toast. submitted by /u/BluestockingLife [link] [comments]
- Conditional formatting rows with multiple conditionsHi I recently was promoted to a role that within management. Part of my role includes auditing time spent in certain "auxes." We take these reports with the important columns being A the Auxe and F the duration. What i want to do is have a formula that can highlight the row when if column A has a certain name (i.e. break) and and column F goes over a certain duration (i.e. 16:00) but also still work for different factors for Column A? I hope im making sense. Im not well versed in Excel. Thanks for any help in advance. submitted by /u/MavethOrel [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#Excel compatibility#Excel alternatives#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#rows.com#financial modeling with spreadsheets#Excel#Conditional Formatting#Daily Metrics#Sports Team