Excel help with drag down?

retiredalso

I have an excel spreadsheet to calculate my gas and electricity consumption and costs.

I enter the date in column A and the meter reading in column B.

Columns C, D, E, F and G show me how much I have used, the daily average and the cost, along with some other workings to make it work.

If I enter the date and meter reading in cell A12 and cell B12 I have to then highlight cells C11, D11, E11, F11 and G11 and drag down to get the new values in cells C12, D12 etc.

Is there a way to automate this so that when I enter the data in columns A and B and press enter the cells in columns C - G are automatically filled in?

Excel 2003 but may be getting excel 2010

Teacher2301

Hmmm...not sure if it can be automated and you're already dragging and copying the functions in the cells anyway - you know you can drag them down further? You don't have to do just one row at a time - sorry if you know that!!

Once done, save it as a template for the next year - then all you need to do is add in A and B cell data.

piggeh

You could probably add some VB code into the worksheet so that on entering values into a column it checks if others are empty, and fill them if necessary, but too advanced for me to give you the exact code to sort it.

bubblesbonbon

You could use a conditional formula - the versatile "if"

For example to get how much used in C2 use the following format

=if(B2="","",B2-B1) which checks if a value is in B2, if not then nothing(null). If you have a value there then it puts in the value of B2-B1.

Use a similar formula for the rest of columns using B2 as the check cell then copy the whole lot down as far as you fancy.

Bit more about conditional IF here

http://spreadsheets.about.com/od/iffunctions/Excel_IF_Functions.htm

Regshoe

Conditional "if" is probably the best way. I believe though there is an option that does this automatically in Excel 2007/2010, I have a spreadsheet I use for a similar purpose which fills automatically, I didn't select any option to do this - it just does, so the functionality exists somewhere in 2007/2010, never bothered looking into it as it didn't bother me to drag down if required.

Trollburger

Excel 2007/2010: It's called 'Extend data range formats and formulas' and it's enabled by default.

Conditional if is the best way to do it if you want to avoid unsightly zeros everywhere