You are here:

Excel/extending formulas

Question
QUESTION: I have created a multi-column checkbook and expense spreadsheet for work. column a-e are your date, payee, type of transaction, and memo.
Column f is the paid column.  In it i have the formula  =if(f3="CC","*","") this way i'm also able to keep credit card expenses on it and still balance the checkbook manually.
column g-aj are the various expense columns
column ak is for credits/deposits
column al is for the checkbook balance. in it i have the formula =if(f3="CC",al3,al3+ak4-sum(g4:aj4) this allows me to enter the credit card expenses without changing the checkbook balance until the credit card is paid like other expenses.
At the bottom i have the sums for each column allow me to view how much was spent on each item.
The problem is if i need to add a row the formulas do not carry over and i have to constantly extend them to get them to carry down.  I have checked in the advanced settings and auto fill is marked.  Is there a way to gt the formulas to automatically carry over.

Press CTRL-L to activate the "Create Table" wizard, confirm the range is correct and the headers.

Now your data is in a "Table" and when you click anywhere IN the data, you will see an expansion row offered at the bottom, it will have a star in the first column.

How it works... if you type anything anywhere in that expansion row, Excel will expand the table permanently to encompass this row and copy formulas and formatting down from the column above.

---------- FOLLOW-UP ----------

QUESTION: I tried that it didn't work and now I can't get it out of table form. How do I do that

The "Table" function certainly works and is a powerful, useful tool.  Unless I didn't understand your question, the expansion row at the bottom of the table will always inherit the properties of the row directly above it.

I'd be happy to look at your document to see if something unknown is affecting your results.  See my link below.

To turn off the table function, just close the workbook without saving your changes and reopen it again.
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010