Question for those who know how to use EXCEL

Post by **ToxicBug** » Mon Jan 08, 2007 11:11 pm

How do I apply a formula to an entire column without stretching it with the mouse for thousands of rows?

Post by **tnf** » Mon Jan 08, 2007 11:37 pm

Make your formula in the top column. Copy it. Clikc on the top of the column (the column letter), it will highlight the whole column. Paste.

Post by **seremtan** » Mon Jan 08, 2007 11:38 pm

select the cell with the formula in it

CTRL+C

select the cell where you want the pasting to start

scroll down to the cell where you want the pasting to stop

shift and click on that cell, which should highlight all the cells between the two

CTRL+V to paste in your formula

an alternative to scrolling down to find the end cell is to manually type in the name of the cell you want the pasting to stop in the little dropdown field at top left where the current cursor position should be displayed, then before hitting enter, hold down the shift key, which will select all the cells in between

hope that made sense

Post by **tnf** » Mon Jan 08, 2007 11:45 pm

that question brings back memories.

I taught classes at the university on excel, word, access, and powerpoint. At the time I knew about every fucking function those programs had (except for access, as it was a very cursory course on basic databases).

I can't remember half of the shit now

Post by **R00k** » Mon Jan 08, 2007 11:54 pm

I just refrained from being a complete smart ass. I'm proud of myself.

Post by **ToxicBug** » Tue Jan 09, 2007 12:20 am

Thanks tnf and seremtan. I asked this because I thought that EXCEL would have some sort of a smart way to manage data if I applied a formula to an entire column, but it is not the case. By using the IF command, I made "invisible" entries into columns, for a couple thousand rows, but they still take up space, thus making my file really heavy.

Post by **tnf** » Tue Jan 09, 2007 12:22 am

what are you working on where that kind of file size difference really matters? Is your 5.25" disk almost full?

Post by **X-R-Cist** » Tue Jan 09, 2007 12:22 am

what is it you are trying to do?
there is always more ways than one with exel

Post by **ToxicBug** » Tue Jan 09, 2007 12:28 am

Basically I made a spreadsheet to calculate my weekly budget based on the number of days left until the date that I should start working again (I don't work during school). I wanted to share this file with others so that they could , but then I realized that the file was 4.5mb... I think that if I apply all the formulas to the entire columns, it could hit 15mb+ easily.

Btw, here's a sample of my budget sheet:

http://putstuff.putfile.com/35215/2171087

Post by **X-R-Cist** » Tue Jan 09, 2007 12:31 am

do you have anything embeded or fancy formatting?
i have a spreadsheet with hundreds of formulas and its no bigger than 200k...

Post by **ToxicBug** » Tue Jan 09, 2007 12:33 am

take a look yourself.

Its all pretty self explanatory.

Total ML is total money left
DMF is daily money flow
WMF is weekly money flow.

Post by **X-R-Cist** » Tue Jan 09, 2007 12:45 am

well first off, you dont need the formula pasted all the way down. Thats 65,000 cells wich you dont need.. you will need 365 rows at max.
so cut that down.
its now 270k file...
i cant upload it to you. send me a pm with your email and ill send it back

Post by **ToxicBug** » Tue Jan 09, 2007 12:51 am

Yeah, come to think of it, I will need a whole new budget sheet for next year anyway, so I don't need to paste the info for thousands of cells like you said. Cheers!

edit: hmm, I've deleted everything after row 365 and its still 1.56mb... weird.

Post by **X-R-Cist** » Tue Jan 09, 2007 12:55 am

gimme your email ill send you the one i got

Post by **ToxicBug** » Tue Jan 09, 2007 1:02 am

pm'ed

Post by **X-R-Cist** » Tue Jan 09, 2007 1:02 am

emailed!

Post by **ToxicBug** » Tue Jan 09, 2007 1:06 am

Got it... I see what you mean now. However, if you already input the date, then its not as neat and it fucks up the graph. The neatness can be fixed by using IF and whatnot, but what about the graph?

Post by **X-R-Cist** » Tue Jan 09, 2007 1:51 am

the graph only read what data is on the sheet. so if you use if statements to keep the cells clear until you input some data, then the graph wont be as messy.

Alternitavely, just delete the cells for upcoming days and enter them in one by one as the days go on.

Post by **ToxicBug** » Tue Jan 09, 2007 4:33 am

X-R-Cist wrote:Alternitavely, just delete the cells for upcoming days and enter them in one by one as the days go on.

Yeah, thats what I do all the time. However, how can I write a formula that will specify that if C3 and/or I3 are "", then "", if not then I3/C3?

Post by **seremtan** » Tue Jan 09, 2007 8:08 am

sounds to me like you're overcomplicating your budgeting...