Data is in the range A1:E50 I keep rows 48, 49 and 50 as blank records which may include formulas, but no data When I need to add say, 5 more blank records, I goto row 49, and insert 5 rows above it. Then I staying in row 49, I fill down upto row 54 which is the new second-last row in the table. For some reason, if you do the above steps with only the last row blank i.
Khushnood, thanks, that's an interesting solution, and it inspired me to try another test.
Format Painter and other ways to copy formatting in Excel
I dragged down the marker at the bottom right of the table, to make it 3 rows longer. Then, I was able to do a normal copy and paste to the new rows, without adding a new conditional formatting rule. Looks like the bug that is still there is that pasting below the table causes expanding of the table, which then adds the CF of the table on top of the CF of the pasted cells. Might be useful to file a bug for this Debra. As far back as I can remember, Excel duplicates the formatting of the row above the one being inserted.
Some videos you may like
In your example, inserting a row in 48 will copy the formatting from When inserting in 49, it copies from 48, which in your case is non-formatted. You don't really need the third non-formatted row, If I may clarify something, the filling down is only necessary if you have formulas on row 48, but not if it is blank.
I just found that same horror to one table that is "updated" regurlarly for the last few weeks. I deleted all conditional format, and started afresh to find that the problem repeated itself a few days later. I Will use the three lines empty at the bottom as a safeguard as it is easier to enforce with other users then going to the paste values without organising for each one a paste value button on their ribbon. It's a pain that extra steps are required though, for something that should be simple. I didn't know you sold staplers too!
The price is a little high, but I'm sure the quality is up to the same stellar standards as the rest of your site s , so expect my order soon. FYI, in contrast to Jan Karel's comment, it happens for me in cells that aren't part of a table. The first time this happens, it's scary. I've always just gone into the CF dialog, deleted the dupes, and reset the range on the original. This way is way better. Doug, thanks, and I'll give you a discount on the staplers, if you order in sufficient quantity.
I too have the same problem in an semi-automated template that has over columns and between rows of data depending on the user's needs. Unfortunately, the users' requirement to be able to insert blank rows anywhere within the data area makes using the last 3 row solution impractical for me. This really creates a lot of unnecessary additional conditional formatting. Wish there was a better solution to clean up the mess or prevent it from happening. Slightly off-topic, but I find I use paste by value so often I long ago memorized the keyboard shortcut: alt-e, s, v.
I also find myself using the keyboard for other special paste commands alt-e,s,t for formats, alt-e,s,f for formulas, etc but paste by value is far and away the most common for me. I'm running into this with a table hooked up to a query.
As the query expands and contracts the table, it is adding the conditional formatting problem talked about here. This is on I haven't got the right data driver on my system with , so can't test if it is resolved for me. I have the same issue as Greg, the user needs to be able to add rows anywhere in the table and that messes up the CF and the 3 rows or the paste as values do not help there. Any ideas on how to prevent that? OK, I guess I'm just a little surprised that you haven't combined your earlier idea of a one-click formatting of your spreadsheet to include conditional formatting.
Without patting myself on the pack too hard got to watch out for that elbow A little more difficult to code but worth the time to ensure that you don't open up your spreadsheet and get frustrated. This is a great example of the possible issues arising from using the simple paste instead of paste special. When pasting normally, this could be more accurately called Paste ALL. Paste All will bring over almost all of these properties.
- Try it out.
- season match game for mac;
- Paste like a Pro - 15 Excel paste special tricks you should know!
- mac os x 10.8.4 web server.
- contact management software for mac download.
- Use Excel's Find feature to find and select cells - TechRepublic;
The majority of the time, pasting without using paste special will not cause problems, but I am in the camp of pasting exactly what I want into the cell and not bringing any extra baggage along. I use paste values for the majority of my work, and the other paste special options such as pasting formulas, formats, or transpose are indispensable. I would agree that using paste special values is the most appropriate solution.
A quick tip on using the quick access toolbar: No need to use a complicated keyboard shortcut to navigate the ribbon, and no need to create a shortcut key with VBA. As soon as you add an item to the quick access toolbar, it is assigned a shortcut key. I keep paste special values as the first item in the toolbar, so it has the shortcut ALT 1. The next item would be ALT 2, and so on.
Use Conditional Formatting to shade alternate rows in Excel
Conditional formatting used to work in a logical way, and pasting cells carried absolute or relative conditional formatting as any informed user would expect, irrespective of how it was accomplished. However, failed attempts to improve the product by bloating it with useless excess as well as needless changes of conventions has created this problem.
This kind of nightmare is typical of Microsoft - an authoritarian company that imposes its bloatware on suffering users everywhere. Each time a new often worthless and at times positively destructive tweak is added to a Microsoft product, millions of users must relearn how to use the software, and discover workarounds to the new bugs. This must be costing the world economy billions in lost productivity, of not trillions. This is the penalty we pay for the monopoly that Microsoft has become.
CF formula that worked
You are absolutely right. It was a breeze to make and use Conidtional Formatting in previous versions when there was no "applies to range". As you said, the formatting worked when copied "in a logical way, and pasting cells carried absolute or relative conditional formatting as any informed user would expect". Now all rows refers to the formula for the first cell and gives unexpected results down below and there is no way to change it. Conditional formatting should be simple. For the rules just write an if statement, or visual basic routine I refuse to call them macros just as one would for any cell calculation.
The only difference being the output is the cell format. I find the preconfigured rules and formats utterly useless, and the means of writing and implementing a rule frustrating. It seems the programmers don't follow exisiting Excel conventions or even use Excel.
Then they put this junk in.
Use a formula to apply conditional formatting in Excel for Mac - Excel for Mac
I have been testing Office and this is still an issue Some work books would get as big as 50 MB and after the cleanup back down to 35kb. Hello, I hope you can help.
I have 3 cells in a row. D4 form number , E4 case number , and F4 score between I have set up conditional formatting for E4 successfully to change the color of the cell based on the value contained within it. Fantastic, but now I want to match the conditional formatting of cells D4 and E4 to F4. So that based on F4's number those cells change color too. Please help. How do I get cells D4 and E4 to match the color conditional formatting for cell F4? Weird, it posted the formula's incorrectly. Well I have a sheet with lines, where different columns and ranges use total of 17 conditional formatting conditions.
If had to start all over again every time, I'd go mad. There must be a better solution! I think I solved the problem for myself: thanks of course to info here about pasting as values. I needed to duplicate a row and then erase all constants from it.