clicking on formula doesn't highlight cells in formula

**john4430** · 01-04-2011, 01:08 PM

usually when i click on and highlight a formula in a cell on my worksheet, i can then click on formula bar above, and voila, all the cells used in the formula get color highlighted by excel, but i have this pesky formula that does not behave that way.

when i go to that formula, and then click on the formula tab above, the cells do not get highlighted

the formula i am clicking on is a sum of several other cells that have formulas in them.....i have a window cleaning business, and so I have each job listed in a column, and I have a formula at the end of each day totaling the jobs for that day.......and then i have a formula at the bottom that totals all the days to get the grand total of all the days.....this formula totaling all the other daily subtotals is the one not working

**6StringJazzer** · 01-04-2011, 01:50 PM

I suggest you post the workbook. If it has data you don't want posted, at a minimum copy the formula and paste it in a post.

**john4430** · 01-04-2011, 02:03 PM

=SUM(K23+K28+K35+K40+K48+K53+K65+K71+K77+K88+K100+K111+K119+K132+K150+K162+K180+K199+K219+K227+K256+K280+K305+K335+K356+K381+K415+K428+K449+K487+K523+K529+K543+K555)

this is the formula

when i click on the cell in the worksheet where the formula is, i used to see all the cells listed in color , so K23 was in color, K28 a different color, etc.) now this does not happen

**shg** · 01-04-2011, 02:28 PM

I think it's just a matter of how many arguments Excel is willing to highlight -- you have 34, if you reduce it to 31, Excel is happy.

The SUM function is redundant in that formula:

=K23+K28+K35+K40+K48+K53+K65+K71+K77+K88+K100+K111+K119+K132+K150+K162+K180+K199+K219+K227+K256+ K280+K305+K335+K356+K381+K415+K428+K449+K487+K523+K529+K543+K555

**john4430** · 01-04-2011, 02:36 PM

Thank you very much!!!

yes, I did try to reduce the number of cells in the formula and then the color highlighting did reappear!! I guess I will just have to make subtotals of smaller (<31) in the future, and then total the larger subtotals

by redundant do you mean that i do not need the + sign? all i need to do when making the formula is click on sum and then hold down the control button and click on whatever cells I want in the formulas? i guess the cells in the formula are then separated by commas and not the + sign?