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:
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.
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.