Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010


Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

In this tutorial we will cover two tips: how to quickly edit formulas, and also how to quickly edit data in multiple cells, all in Excel 2010.

How to Quickly Edit Formulas 

I am sure once you see this tip you will find lots of uses for it.

In typical worksheets, there is a great deal of consistency in formulas. They are usually identical in structure, differing only in the actual cells they are referencing within the spreadsheet. But when it comes to relative referencing, most formulas are identical. For example, =SUM(G1:G50) and SUM(H1:H50) are really SUM(the first row in this column to the cell immediately above this formula).

When you create worksheets that have formulas with identical structures, it makes copying and editing and understanding your formulas a whole lot easier.

To copy a formula, most Excel users type the first formula, then copy and paste it to the other cells. Same goes for editing a formula. Edit the first formula then copy and paste it to the other cells.

Here’s a trick you may not have seen, that can make things a whole lot easier and you can more quickly edit formulas once you get into the habit of using it every day.

Instead of typing or editing the first formula then copying and pasting it to the other cells, try this instead:

1) Select all the cells where you want the formulas to go;2) Type or edit (F2) the formula in the Formula Bar;3) Instead of pressing Enter, hold down the CTRL key and press ENTER. The formula (from Step 1) gets instantly copied to all the other selected cells.

How to Quickly Edit Data In Multiple Cells in A Specific Order

Most people routinely input data into cells spread throughout your worksheet in a specific order, such as an invoice or order form. Here’s a  quick and simple method that will enable you to easily input your data  without having to select each cell first, and in any specific order.

Say for example each week you need to input data into cells B8, B9, B10, B11, B12, then over to F12 and F13, then back to A17, D17, etc…. Normally, once you’ve entered data into the cells going down, then you have to click, scroll or use the arrow keys to move to the next input cell in another column or another section of the sheet.

The following is a method you can use each time you need to input data into cells in a specific order. The best thing about this method is that it allows you to select all of the input cells and, as you input information, the active cell moves to the next cell in the order you have predefined.

Define your input cells

1) Determine which cells and in which order you want the data input;

2) While holding down the CTRL key, start by selecting the second input cell first, then the third, then the fourth, etc… and the last cell you select is the first input cell. You now have all your input cells selected (in the order you will enter the data) and your first input cell is now the active cell.

3. With the input cells still selected, click in the Name box (the small rectangular white space immediately above column A and to the left of the Formula Bar), type INPUT and press Enter. You have now named your input cells INPUT.

Select input cells

Now that things are setup, whenever you need to select your input cells click the small dropdown arrow to the right of the Name box and select INPUT. You’ll notice that the active cell is the first input cell. Enter the data in the first cell then press the Enter key or the Tab key to move to the next input cell. If you make a mistake inputting data, simply hold down the Shift key while pressing Enter or Tab to move backwards through the input cells.

Redefine your input cells

If you make a mistake in naming the input cells, or if you subsequently want to change the order of the input cells, you’ll first have to delete the defined name INPUT.

Here’s how…

1. On the Formulas tab click Name Manager in the Defined Names group;

2. Select INPUT, click Delete and OK.

Now you can redefine your input cells as outlined above.

Clear your Input Cells

To quickly clear your input cells, click the small dropdown arrow to the right of the Name box, select INPUT and press the Delete key on your keyboard.

If this article was of value to you, please comment below and share.

By the way, please be free to check out our own Facebook Fan Page: Bren & Mike Prosperity Tips.

Excel Tips - How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Author: Mike Marko
Twitter: @mikemarko1
Skype: r.mike.marko
Facebook: mike.marko.37
Facebook Fan Page: Bren & Mike Prosperity Tips
Email: info@intendhomes.com

Article: Excel Tips – How to Quickly Edit Formulas And Data In Multiple Cells in Excel 2010

Get Free Email Updates!

Signup now and receive an email once I publish new content.

I will never give away, trade or sell your email address. You can unsubscribe at any time.

About The Author

Mike Marko

We at IM Marketing Consultant Services are your total internet marketing solutions company. We can help you look at the "big picture" and optimize your internet marketing. I have six sigma lean training; and when you combine that with internet marketing, helps get results that affect your business bottom line. I practice Law of Attraction, and have been investing in real estate since 2008. We have learned to make online marketing the ultimate path to prosperity in business and love teaching it to others.