Is there a way to make an entire column have a formula? I'm exporting data into an excel sheet, and I need one column (J) to have a formula in it from cell 1-65536. I know that you can go for example: (=$H2-$I2) and drag that all the way down. But this fills all the blank columns with 0's and when I go to print it, it prints like 2500 sheets of 0's. Any idea how to set a formula for an entire column?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Microsoft Excel Formulas 2
- Thread starter bradth
- Start date
-
1
- #2
TonyJollans
Programmer
Hi bradth,
Select your column (J).
Type your formula for cell J1 in the Formula box.
Press Ctrl+Enter to enter the (row-adjusted) formula in all the cells.
This has the same effect as entering it once and filling down, though, and you will still get a lot of zeroes. The way to remove (or hide) them is through formatting:
Select your column J again
Press Ctrl+1
Select Custom from the Category list
Enter [blue][=0]""[/blue] under Type
(There are other ways but's that's about the simplest in this case)
Enjoy,
Tony
--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[
Select your column (J).
Type your formula for cell J1 in the Formula box.
Press Ctrl+Enter to enter the (row-adjusted) formula in all the cells.
This has the same effect as entering it once and filling down, though, and you will still get a lot of zeroes. The way to remove (or hide) them is through formatting:
Select your column J again
Press Ctrl+1
Select Custom from the Category list
Enter [blue][=0]""[/blue] under Type
(There are other ways but's that's about the simplest in this case)
Enjoy,
Tony
--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[
-
1
- #3
AnotherHiggins
Technical User
bradth,
To hide the zeros, I'd just change the formula to include an IF like this: [COLOR=blue white]=if(H2-I2=0,"",H2-I2)[/color]
HOWEVER: If you try to print the spreadsheet, Excel will still want to print all 65536 rows! Hiding the zeros from appearing won't fix that. Besides that, having 65,536 formulas in your workbook is going to make it pretty large - I just tried it with the formula I offered and nothing else in the entire workbook - it is now about 4.5 Meg.
What are you trying to accomplish? Are you doing the importing via VBA? If so, code can be added so the formula only appears on occupied lines.
There's probably a better way to do whatever you want, we just need more details.
[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886
To hide the zeros, I'd just change the formula to include an IF like this: [COLOR=blue white]=if(H2-I2=0,"",H2-I2)[/color]
HOWEVER: If you try to print the spreadsheet, Excel will still want to print all 65536 rows! Hiding the zeros from appearing won't fix that. Besides that, having 65,536 formulas in your workbook is going to make it pretty large - I just tried it with the formula I offered and nothing else in the entire workbook - it is now about 4.5 Meg.
What are you trying to accomplish? Are you doing the importing via VBA? If so, code can be added so the formula only appears on occupied lines.
There's probably a better way to do whatever you want, we just need more details.
[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886
- Thread starter
- #4
- Status
