Q 1: when I use the search function in Excel I find the highlight/indicator almost invisible and difficult to see in the middle of a spreadsheet. Is there a setting to adjust this so that it is more contrasting and visible?
Q 2: If I want to pivot a table I can only paste/pivot if I’ve copied but not if I’ve cut. Is there a setting so that I can cut and pivot?
I don’t believe you can make the find cell locator different, but Excel shows both the row and the column highlighted for the current cell as well as the displaying the Cell identifier right next to the formula field in the toolbar.
No, because copying and pasting a formula is handled differently than cutting and pasting a formula (beyond the obvious erasure of the previous contents) and Microsoft decided that wouldn’t be good for transposing data.
To see this in action, put 123 in cell A1, 456 in cell B1, and =A1+B1 in cell C1. If you cut and paste C1 to D23, the formula will remain “=A1+B1”. If you copy and paste it to cell D23, it will be changed to “=B23 + C23”.
Re #1: agreed, just one of many horrible things about Excel. I often type garbage just so I can see the cell, then ESC to undo it.
Thanks DMC & phs3. That’s what I was afraid of.
For question 1 use conditional formatting. Ctrl A will select the entire sheet. Then highlight cell rules and text that contains will format the cells however you like.
I don’t understand what you are trying to do in question 2.
So picture a column, for example, with Ford, Audi, Porsche, and Buick, from top to bottom. If I want to use the pivot function to make a row with Ford, Audi, Porsche, and Buick from left to right, I have to copy and paste when I want to cut and paste. I can’t seem to do that by cutting and pasting.
Note: absolutely no sarcasm or snark intended here - does my example make sense to you?
I didn’t know the answer to this one off hand, but this may be the answer to your second question:
Another possibillygoat for #2:
Particularly post #6
That’s OK. I see what you mean now. Funnily enough I use the copy/transpose function all the time when I am writing SQL for reports with Excel output, but had no idea that it wouldn’t work if you did a cut instead.
Did my suggestion about using conditional formatting make sense? Again, it is something that I use fairly often, so it is almost my default find option unless I am doing a mass replace.
Have you tried the theme setting in MS Office?
That is not called the “pivot function.” That is confusing because there is something called a “pivot table.” You are talking about “Paste Transpose.”
No. Conditional formatting won’t work to highlight the currently selected cell.
It is possible if you add a VBA function to return whether the cell is currently selected, but it would be a Volatile function and run for all cells every time there is a recalculation, so would be painfully slow.
Ignorance fought😀!
I thought the OP was asking about highlighting the cell found using the search function?
Yes I was. For example there are some spreadsheets I have to work with that have 20-some columns and maybe 2 or 3 hundred rows with cells populated by either serial numbers or acronym-heavy text.
I could be looking for something like “ops” or “AN/SPS-49” among all this stuff and when using the Search function there is so little contrast to indicate a cell populated with the search term (nested among other text in the cell) that it is really, really hard to find.
So did my suggestion make sense? And more importantly does it work for you?
Sorry about taking so long to respond don_t_ask but, holy crap - it’s wonderful and is the obvious solution. That sort of outcome is what the Find function should produce.
Thank you so much.
The Find function selects the cell that is found. I thought you wanted better highlighting on the selected cell. Is it enough just to color the cell without selecting it?
Is there a way of doing both? Ideally I would get the Conditional Formatting result while selecting it and, if chosen, also replacing.