Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 203495 times)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3556
  • Total likes: 649
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1520 on: March 30, 2022, 02:40:54 PM »
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.

Offline hocker

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2009
  • Posts: 3473
  • Total likes: 72
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1521 on: March 30, 2022, 05:49:18 PM »
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

Offline Yo ssi

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Aug 2019
  • Posts: 5071
  • Total likes: 1802
  • DansDeals.com Hat Tips 49
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1522 on: March 30, 2022, 10:29:08 PM »
I believe vlookup is what you want
_    ,
' )  /
 /  / __   _   _   o
(__/_(_)  /_)_/_)_<_
 //
(/

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 14791
  • Total likes: 10408
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1523 on: March 30, 2022, 11:13:21 PM »
Is there a simple way to sum a cell from each tab in the sheet without referencing them individually?
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12503
  • Total likes: 2661
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1524 on: March 30, 2022, 11:15:27 PM »
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)
Workflowy. You won't know what you're missing until you try it.

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 14791
  • Total likes: 10408
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1525 on: March 30, 2022, 11:18:58 PM »
Feelings don't care about your facts

Offline yuneeq

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2013
  • Posts: 8079
  • Total likes: 2802
  • DansDeals.com Hat Tips 10
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Help and Problems
« Reply #1526 on: August 04, 2022, 10:10:26 AM »
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.
"If you fall, I will be there." -Floor

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5067
  • Total likes: 19
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Help and Problems
« Reply #1527 on: August 04, 2022, 10:13:10 AM »
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
« Last Edit: August 04, 2022, 10:16:27 AM by bubbles »

Offline Alexsei

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2011
  • Posts: 4256
  • Total likes: 362
  • DansDeals.com Hat Tips 5
    • View Profile
    • Travel & Kivrei Zadikim
  • Location: Truckistan
Re: Excel Help and Problems
« Reply #1528 on: August 04, 2022, 10:18:06 AM »
︽ Help me join the 1,000 Likes society

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3556
  • Total likes: 649
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1529 on: August 04, 2022, 02:47:19 PM »
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?

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 14791
  • Total likes: 10408
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1530 on: August 05, 2022, 12:22:49 PM »
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.
Feelings don't care about your facts

Offline yuneeq

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2013
  • Posts: 8079
  • Total likes: 2802
  • DansDeals.com Hat Tips 10
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Help and Problems
« Reply #1531 on: August 05, 2022, 04:56:54 PM »
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!
"If you fall, I will be there." -Floor

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3556
  • Total likes: 649
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1532 on: August 05, 2022, 06:12:17 PM »
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.

Offline yuneeq

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2013
  • Posts: 8079
  • Total likes: 2802
  • DansDeals.com Hat Tips 10
  • Gender: Male
    • View Profile
  • Location: NJ
Re: Excel Help and Problems
« Reply #1533 on: August 05, 2022, 06:14:57 PM »
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.
"If you fall, I will be there." -Floor

Offline Euclid

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3684
  • Total likes: 3833
  • DansDeals.com Hat Tips 3
    • View Profile