Apply MONTH formula to entire column

**aspen_gal** · 06-30-2009, 12:53 PM

Hello, below is a sample of my excel sheet. The number of rows keep changing daily and the Month and year column has to be populated automatically as and when there is a new row inserted. I tried the formula =MONTH(A:A) and I paste it on the entire column. it works for rows with data but I also get 104,000 odd blank rows added to my excel because I pasted the formula on the entire column.

Date Month year
10/10/2009

hanks much!

**NBVC** · 06-30-2009, 01:02 PM

Try

=IF(A2="","",Month(A2)) copied down...

**aspen_gal** · 06-30-2009, 01:10 PM

Thank you, how do you want me to copy down? Select the entire column and paste?

**NBVC** · 06-30-2009, 01:11 PM

Select the cell you put that formula in.

Click and hold the little black square at bottom right corner of the cell and drag down to the bottom of your list.

**aspen_gal** · 06-30-2009, 01:11 PM

Thank you. I tried doing that and it inserts several 1000s of rows with blank data. I have to do a PIVOT table based on this sheet and with several rows having a "" Im not sure how the PIVOT would work? I want the formula done only if there are valid rows with Date in my Sheet.

**NBVC** · 06-30-2009, 01:22 PM

After you do the Pivot you can hide Blanks

http://www.techonthenet.com/excel/pi...hide_empty.php

or

for 2007

http://www.techonthenet.com/excel/pi..._empty2007.php

**aspen_gal** · 06-30-2009, 01:33 PM

Thank you. I just created a pivot and looks like it is automatically ignoring the blanks!! Thanks a lot.

**NBVC** · 06-30-2009, 01:42 PM

Please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save