Excel Help and Problems

[skyguy918]

If anyone can help me with a simple conditional formatting I'd really appreciate it. I'm trying to compare two lists and highlight cells in column 1 that also appear in the list on sheet 2. Tried google and for some reason it is not working.

Let's say list 1 is in column A of Sheet1 and list list 2 is in column A of Sheet2. Highlight list 1 > Conditional Formatting > New Rule > click the last option (Use a formula...), then enter the following into the Formula box:
=COUNTIF(Sheet2!$A:$A,A1)>=1
Then you can click format to choose your desired formatting.

Keep in mind that this version of the formula will be slow if you apply it to massive lists. There are ways to make it more efficient if necessary.

[hocker]

Let's say list 1 is in column A of Sheet1 and list list 2 is in column A of Sheet2. Highlight list 1 > Conditional Formatting > New Rule > click the last option (Use a formula...), then enter the following into the Formula box:
=COUNTIF(Sheet2!$A:$A,A1)>=1
Then you can click format to choose your desired formatting.

Keep in mind that this version of the formula will be slow if you apply it to massive lists. There are ways to make it more efficient if necessary.

TY

[Yo ssi]

I believe vlookup is what you want

[aygart]

Is there a simple way to sum a cell from each tab in the sheet without referencing them individually?

[etech0]

Is there a simple way to sum a cell from each tab in the sheet without referencing them individually?

Code: [Select]

=SUM(Sheet1:Sheet3!A1)

[aygart]

Code: [Select]

=SUM(Sheet1:Sheet3!A1)

Thanks

[yuneeq]

More accurately,
2-Jan

Why the most useless date format is the default is another question

For a program that was created to work with numbers, Excel is absolutely horrendous at formatting them. 164849398282 by default turns into scientific notation like 163748e+3, you convert it to a number and half the time it gets truncated and turned into zeroes at the end. So you get lucky one time and it converts over cleanly, you save it, and open the file again and it’s back in the old useless format. Or when your number starts with a zero. You try putting a zero before, convert to text and pray it stays.

Or the times there’s a number in a cell and it doesn’t recognize it as a number even if you try converting it to a number, currency, or anything else. So you can’t sum it or use it in any formula. But once you double click on the cell, the number magically awakens, aligns itself to the right, and is suddenly recognized as a valid number.

I’m sure there are ways to solve the issues mentioned above, but typically the solution is tedious, unintuitive hacks.

Anyone have good tips or advice about formats in excel? How to proactively avoid and solve the issues listed above?

For the last issue - numbers not recognized - aside for double clicking on the cell (which would be tedious when dealing with many cells), you can copy/paste special - values only - to another column, then c/p back to your original column.

[bubbles]

The TEXT formula is very useful.

* 1 can force the text to be treated like a number. You can use paste special > multiply for that if you dont want to have to add in a column

[Alexsei]

🔥Bookmark this
https://excelformulabot.com/

[skyguy918]

Anyone have good tips or advice about formats in excel? How to proactively avoid and solve the issues listed above?

For the last issue - numbers not recognized - aside for double clicking on the cell (which would be tedious when dealing with many cells), you can copy/paste special - values only - to another column, then c/p back to your original column.

Regarding cell formatting resetting, what file format are you using?

[aygart]

Anyone have good tips or advice about formats in excel? How to proactively avoid and solve the issues listed above?

For the last issue - numbers not recognized - aside for double clicking on the cell (which would be tedious when dealing with many cells), you can copy/paste special - values only - to another column, then c/p back to your original column.

For long strings of digits to display as text add a ' before it or format the cell/column/row/sheet as text before entering the string. If you are then pasting paste only values.

Another option is to use text to columns and select delineated and hit finish right away without adding a delineator. I think the delineator needs to be added, but it is possible that I simply have it set on a delineator that is not found in the sequences. If that becomes a problem then just go to the next step and ensure there is either none at all or one that will not be found in the data set. I have this programmed into some macros for sheets that are exported from online data and have multiple unformatted columns. I think that the concept was given to me in a response on this thread.

[yuneeq]

Regarding cell formatting resetting, what file format are you using?

CSVs or XLSX
Many times it’s downloaded reports that come formatted improperly.

For long strings of digits to display as text add a ' before it or format the cell/column/row/sheet as text before entering the string. If you are then pasting paste only values.

Another option is to use text to columns and select delineated and hit finish right away without adding a delineator. I think the delineator needs to be added, but it is possible that I simply have it set on a delineator that is not found in the sequences. If that becomes a problem then just go to the next step and ensure there is either none at all or one that will not be found in the data set. I have this programmed into some macros for sheets that are exported from online data and have multiple unformatted columns. I think that the concept was given to me in a response on this thread.

Thanks!

[skyguy918]

CSVs or XLSX
Many times it’s downloaded reports that come formatted improperly.

Thanks!

CSV won't save your formatting. If you have a CSV and make formatting changes, save it as XLSX or similar.

[yuneeq]

CSV won't save your formatting. If you have a CSV and make formatting changes, save it as XLSX or similar.

Yeh I never save as CSV. Only use it when it’s downloaded like that and change it right away.

[Euclid]

https://arstechnica.com/gaming/2022/08/excel-esports-on-espn-show-world-the-pain-of-format-errors/