I cannot get the Auto Fill function to increase values in a series, and I
feel certain I'm doing it right:

First, I tried typing a series of values in a column like this: Cell A1, I
typed 1. Cell B1, I typed 2. Then, I selected both cells with the mouse, and
then moved to the lower right corner of Cell B1 and left-clicked and held the
mouse button down. Then I dragged the mouse button down several columns. The
little pop-up message seemed to indicate that it was repeating 1, 2, 1, 2.
When I released the mouse button, it had actually filled a 1 in each cell!

After several unsuccessful attempts, I read online help and decided to try
using a different method: the Fill tool function button on the toolbar. I
could not seem to make that command work.

The instructions in Online Help suggested I had a problem. I read, "1. On
the Home tab, in the Editing Group, click Fill, and then click Series." When
I clicked Fill, the Series command was grayed out, apparently regardless of
what I had selected: a blank cell, a series of cells with a series of
increasing values, or a combination of both.

I began to suspect that my program settings were "messed up", so I clicked
the Microsoft Office button, then the Excel Options button near the bottom
right corner of the tab, as it instructed. I reviewed all the settings.
Everything appears to be set properly. For example, Under Advanced options,
"Enable fill handle and cell drag-and-drop" is checked, as is almost every
other option on that tab.

I wondered if there was an configuration problem with my software, so I even
ran the online Microsoft Office Diagnostics. It found no problems, conflicts,
etc.

What am I doing wrong?

It sounds like you may be experiencing a common issue with the Auto Fill function in Excel 2007. Here are a few things you can try to fix the problem:

1. Check your Auto Fill options: Click on the Microsoft Office button, then Excel Options, and then Advanced. Scroll down to the Editing Options section and make sure that "Enable fill handle and cell drag-and-drop" is checked. Also, make sure that "Extend data range formats and formulas" is checked.
   
2. Try using the Auto Fill Options button: After you have typed in your initial values, click on the cell that contains the first value. Then, click and drag the fill handle (the small square in the bottom right corner of the cell) down or across to fill in the rest of the series. When you release the mouse button, a small Auto Fill Options button will appear. Click on this button and select "Fill Series" from the drop-down menu.
   
3. Use the Fill command: Click on the cell that contains the first value. Then, click on the Home tab, and in the Editing group, click on the Fill button. From the drop-down menu, select "Series". In the Series dialog box, select the type of series you want to create (e.g. linear, growth, date, etc.) and enter the appropriate values. Click OK to fill in the series.
   
4. Check for updates: Make sure that you have the latest updates for Excel 2007 installed. Click on the Microsoft Office button, then Excel Options, and then Resources. Click on "Check for Updates" and follow the prompts to download and install any available updates.

Hopefully one of these solutions will help you get the Auto Fill function working properly again. Good luck!

P.S. I had applied filters to the data sheet, and was working deliberately
with one of the filters. I see that if I remove the filters, the function
works.

So that still leaves me with a question: If I want to count the number of
entries in a filtered list, how do I do that? For example, should I use a
formula with something like "COUNT" or "COUNTIF", rather than trying to
simply enumerate the rows?

If so, how do I do that?

I will be eternally grateful for a correct, understandable answer.

Steve
--
Steve "the Raudawg"


"Raudawg" wrote:

I cannot get the Auto Fill function to increase values in a series, and I
feel certain I'm doing it right:

First, I tried typing a series of values in a column like this: Cell A1, I
typed 1. Cell B1, I typed 2. Then, I selected both cells with the mouse, and
then moved to the lower right corner of Cell B1 and left-clicked and held the
mouse button down. Then I dragged the mouse button down several columns. The
little pop-up message seemed to indicate that it was repeating 1, 2, 1, 2.
When I released the mouse button, it had actually filled a 1 in each cell!

After several unsuccessful attempts, I read online help and decided to try
using a different method: the Fill tool function button on the toolbar. I
could not seem to make that command work.

The instructions in Online Help suggested I had a problem. I read, "1. On
the Home tab, in the Editing Group, click Fill, and then click Series." When
I clicked Fill, the Series command was grayed out, apparently regardless of
what I had selected: a blank cell, a series of cells with a series of
increasing values, or a combination of both.

I began to suspect that my program settings were "messed up", so I clicked
the Microsoft Office button, then the Excel Options button near the bottom
right corner of the tab, as it instructed. I reviewed all the settings.
Everything appears to be set properly. For example, Under Advanced options,
"Enable fill handle and cell drag-and-drop" is checked, as is almost every
other option on that tab.

I wondered if there was an configuration problem with my software, so I even
ran the online Microsoft Office Diagnostics. It found no problems, conflicts,
etc.

What am I doing wrong?

On Sat, 16 Jan 2010 00:29:01 -0800, Raudawg wrote:

So that still leaves me with a question: If I want to count the number of
entries in a filtered list, how do I do that?


Highlight the cells you are wanting data on, then look at the status
line at the bottom of the worksheet window. There are several
statistical figures there, including count.

To count autofiltered entries use the SUBTOTAL function.

=SUBTOTAL(3,range)

Will count only visible cells.

As far as autofilling goes.

I generally use this method.

Enter a number in a cell

Right-click on fill handle and drag down.

Release button and "Fill Series" to get a series of numbers.

Otherwise you must have two numbers so Excel can see a pattern.

Enter 1 in A1, 2 in A2

Select both cells and drag down.

The series will fill.


Gord Dibben MS Excel MVP

On Sat, 16 Jan 2010 00:29:01 -0800, Raudawg wrote:

P.S. I had applied filters to the data sheet, and was working deliberately
with one of the filters. I see that if I remove the filters, the function
works.

So that still leaves me with a question: If I want to count the number of
entries in a filtered list, how do I do that? For example, should I use a
formula with something like "COUNT" or "COUNTIF", rather than trying to
simply enumerate the rows?

If so, how do I do that?

I will be eternally grateful for a correct, understandable answer.

Steve

Regarding the reply from Gord Dibben MS Excel MVP


As far as autofilling goes.
I generally use this method.
Enter a number in a cell
Right-click on fill handle and drag down.
Release button and "Fill Series" to get a series of numbers.
Otherwise you must have two numbers so Excel can see a pattern.
Enter 1 in A1, 2 in A2
Select both cells and drag down.
The series will fill.

If you have an autofilter on, it will not fill. "Fill/Series" is grayed out. It works once you turn your filters off.



On Saturday, January 16, 2010 3:13 AM Raudawg wrote:

I cannot get the Auto Fill function to increase values in a series, and I
feel certain I am doing it right:

First, I tried typing a series of values in a column like this: Cell A1, I
typed 1. Cell B1, I typed 2. Then, I selected both cells with the mouse, and
then moved to the lower right corner of Cell B1 and left-clicked and held the
mouse button down. Then I dragged the mouse button down several columns. The
little pop-up message seemed to indicate that it was repeating 1, 2, 1, 2.
When I released the mouse button, it had actually filled a 1 in each cell!

After several unsuccessful attempts, I read online help and decided to try
using a different method: the Fill tool function button on the toolbar. I
could not seem to make that command work.

The instructions in Online Help suggested I had a problem. I read, "1. On
the Home tab, in the Editing Group, click Fill, and then click Series." When
I clicked Fill, the Series command was grayed out, apparently regardless of
what I had selected: a blank cell, a series of cells with a series of
increasing values, or a combination of both.

I began to suspect that my program settings were "messed up", so I clicked
the Microsoft Office button, then the Excel Options button near the bottom
right corner of the tab, as it instructed. I reviewed all the settings.
Everything appears to be set properly. For example, Under Advanced options,
"Enable fill handle and cell drag-and-drop" is checked, as is almost every
other option on that tab.

I wondered if there was an configuration problem with my software, so I even
ran the online Microsoft Office Diagnostics. It found no problems, conflicts,
etc.

What am I doing wrong?


On Saturday, January 16, 2010 3:29 AM Raudawg wrote:

P.S. I had applied filters to the data sheet, and was working deliberately
with one of the filters. I see that if I remove the filters, the function
works.

So that still leaves me with a question: If I want to count the number of
entries in a filtered list, how do I do that? For example, should I use a
formula with something like "COUNT" or "COUNTIF", rather than trying to
simply enumerate the rows?

If so, how do I do that?

I will be eternally grateful for a correct, understandable answer.

Steve
--
Steve "the Raudawg"


"Raudawg" wrote:


On Saturday, January 16, 2010 1:27 PM CellShocked wrote:

Highlight the cells you are wanting data on, then look at the status
line at the bottom of the worksheet window. There are several
statistical figures there, including count.


On Saturday, January 16, 2010 4:32 PM Gord Dibben wrote:

To count autofiltered entries use the SUBTOTAL function.

=SUBTOTAL(3,range)

Will count only visible cells.

As far as autofilling goes.

I generally use this method.

Enter a number in a cell

Right-click on fill handle and drag down.

Release button and "Fill Series" to get a series of numbers.

Otherwise you must have two numbers so Excel can see a pattern.

Enter 1 in A1, 2 in A2

Select both cells and drag down.

The series will fill.


Gord Dibben MS Excel MVP