•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How can I keep conditional formatting in a template spreadsheet? It's set up and works until I save it as a template, then the formatting is lost.
Our take
If you’ve set up conditional formatting in your Excel spreadsheet, only to find it disappears when saved as a template, you’re not alone. This common issue can stem from various factors, including the intricacies of working across different SharePoints. Fortunately, understanding how Excel handles templates can help you retain your formatting. In this guide, we’ll explore practical solutions to ensure your color-coded rows remain intact, empowering you to streamline project management effectively.
Does anyone know if it is common for conditional formatting to be lost on a spreadsheet when it's saved as a template? I'm not sure if this is a normal issue or if it's because I'm working in an organisation that shares documents across two SharePoints, so it might be losing something in the saving process. That does seem odd though!
Essentially I've set up a sheet that should change the colour of a whole row depending on whether it is marked as pending, complete or cancelled. In the sheet I've set the rules up in it works, but as soon as I save it as a template and open it from that file type the formatting is lost.
(ETA: Using Excel on desktop through Office 365 Apps for business, am an intermediate excel user, don't need help with the formula unless that impacts what stays in an excel template)
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Font formatting of a calculation dashboard KEEPS reverting every time the data in the other sheets is updatedI am at my wits end, every time I open this spreadsheet the formatting of these cells (supposed to be large and extremely readable to low-tech users on same team) reverts back to Calibri size 11 for some reason. I even created a custom cell style to try and force it to stay that way and I can see that it's selected in the menu, but not reflected whatsoever. Is it just impossible to format cells with a constantly changing calculation? What can I do here?? I feel like I have gone through every formatting constraint/rule there is to try and force it to behave but it always changes back, even if its not right away. It's a spreadsheet stored on sharepoint primarily but I don't know if that makes a difference. Advice appreciated. 😞 submitted by /u/vailette [link] [comments]
- Does anyone know why my conditional formatting rules sometimes mess up and automatically change by themselves?I have a rule that applies to A2:B200 however every few weeks I’ll see that the conditional formatting doesn’t get applied right and I’ll open the formatting rules and find it looks like “=A$21$B$67$B$63$B$69$A$71$A$72$A$74：$B$200” when it was set to “=$A$2:$B$200”. Does anyone know why this happens and how to prevent it from happening in the future? I am using the latest version of office 365 and my document is saved in OneDrive. submitted by /u/LearningPenguin [link] [comments]
- Excel Column Conditional FormattingI 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? submitted by /u/Mdotb774 [link] [comments]
- Set conditional formatting formula to change cell colors after conditions are met in a separate tabI'm doing r/Fantasy's reading Bingo challenge this year, and thought I'd set up a spreadsheet to help me track my progress. The conditional formatting formulas have been causing me some trouble, though: In Tab 1, I have the Bingo squares arranged in the usual 5x5 grid, and in Tab 2, I have all the squares & their reading rules, hard modes, and completion statuses laid out. I'd love to set it up so that if, in Tab 2, I mark "x square" as completed in "EASY" mode, then the equivalent "x square" cell in the 5x5 grid will automatically change color in Tab 1. Ideally the 5x5 grid won't need any manual color formatting, and I'll only need to edit Tab 2. But I haven't been able to figure out how to make this work across the two separate tabs. In case I'm not describing it well, I'm including an editable Google Sheets version of the spreadsheet for reference: https://docs.google.com/spreadsheets/d/1S6EtJFLLSoysxIp-HHZT_tjVznucJuDbhqpDhbdPZX0/edit?usp=sharing Thanks so much in advance! submitted by /u/defenestratedduck [link] [comments]