Excel/Excel formulas


QUESTION: For work I have created a multi column checkbook register and expense tracking sheet all in one. However if I have to enter a new row I can't get the formulas to carry down. I have to go in and drag them down each time.  How would I make them lock in. I have excel 2010

ANSWER: Julie -

If I understand your need correctly, this has a very simple solution that will take you seconds to implement. It just involves formatting.

Select the section that you want to be able to insert or add a row and retain any formulas. Then, on the Home tab, under Styles, choose Format As Table. Choose whatever style you like, you can use the new Tables > Design tab that is created to get rid of formatting you don't like.

The result is the new "table" will behave kind of like a table in Word. If you are in the last cell of the table, hitting the Tab key will create a new row with the same properties as the last. If you insert a new row just under the last row, it will also adapt the table's properties.

Let me know if this doesn't answer your question - Andrea

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

QUESTION: Andrea  I did as you suggested and one formula still will not carry over. Do you have any other suggestions?

ANSWER: Julie -

Please send me a line of your table, so I can put it into Excel and see how it works on my end.

- Andrea

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

QUESTION: I'm not able to send you an image of the spreadsheet.  The formula in column F won't carry over but I was able to get the formula in AE to carry over

Julie -

Without looking at how the data is behaving, I'm afraid I can't figure out what could be preventing your formula in column F from carrying down.

My alternate suggestion is to create a last row, that contains all of your formulas but no data. When you want to insert a new row, insert above that last row. This inserted row will have preserved the pre-defined range.

Hope this helps - Andrea
About Excel
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


All Answers

Answers by Expert:

Ask Experts


Andrea Lynch


I am very familiar with teaching excel concepts, and formulas. I am not as capable with VBA questions.


I currently tutor in this area. I worked for four years developing curriculum and teaching Microsoft applications to adult audiences. I also previously volunteered for allexperts.com.

BA, English, Western Washington University Certificates in C Programming, ASP.NET, VisualBasic.NET, University of Washington Extension

Awards and Honors
Rated in the top ten instructors (national), New Horizons Computer Learning Center.

©2016 About.com. All rights reserved.