How to Adding To Sheets—Inserting Rows, Columns and Cells microsoft Excel 2010

Having the capability to add rows, columns, and cells raises the obvious question: if you have all those
billions of cells to begin with, why would you need to supplement them with even more? The answer—or
at least the standard answer—is that after you’ve constructed a table, for instance, you may decide you
need an extra field’s worth of data—and that decision means you’ll have to introduce a new column into
the table. And if you want that column to appear  between  two columns already in place, you’ll need to
insert another one. If, on the other hand, you’ve entered data all over the worksheet and you’d like to see
them a bit closer to one another, you may want to delete a column or two.
The means for adding and deleting columns or rows are pretty easy (although as usual, there’s
more than one way. We’re demonstrating the most straightforward approach here). But before we
demonstrate how it’s done, we need to anticipate and answer a big question—namely, what happens
to cell references when additions or deletions are carried out? 
For example, suppose you’ve written this formula in cell H3:
=AVERAGE(B17:B32)
If you delete any of columns between C and H, will the cells referred to in that expression change?
After all, delete one such column and the formula now appears in cell G3–and as a result, will the
formula read

=AVERAGE(A17:A32) ?
The answer is no. When you add or delete rows or columns, Excel  maintains the existing cell
references that might otherwise be impacted by the additions or deletions, so not to worry.  But keep in
mind that if you insert a row or column such that cells contributing  to a formula are repositioned, the
formula  will rewrite itself correspondingly. If a column is added to the left of the B column in the first
example above, the formula will now read
=AVERAGE(C17:C32)
Because the values being added are now in column C.
Inserting a Column
To go ahead and insert a column, just click anywhere in the column to the  right of where you want the
new one to be inserted. Thus if you want to insert a column between H and I, click any cell in I. Then
cl i ck  Home  Cells button group    Insert    Insert Sheet Columns  (Figure7–1):

Figure 7–1. Where to insert a column or a row

The new column will slide into place, and will claim the column letter I. The original column I will
move to the right, and become J, and so on. If you want to insert multiple columns, just drag across as
many consecutive columns as you wish and execute the above conmands. You’ll insert as many
columns as you’ve selected—and they’ll appear to the  right of the selection. Thus if you select cells
R3:S3 an d cl i ck In se r t She e t Col umn s, R an d S wi l l  be come  T  an d U—be cause  e ach wi l l  hav e  mov e d
two columns rightward. 

Inserting a Row

The procedure for inserting rows is basically identical. Click in the row  beneath which  you want to
insert a new one and click the above commands, culminating with  Insert Sheet Rows  instead. Thus if
you click in row 17, you’ll insert a new row above the original 17— which becomes the “new” row 17,
whilst the original row 17 is now bumped to 18. To insert multiple rows select as many rows as you
wish to insert.

Deleting Rows and Columns

To delete rows or columns, click anywhere in the column or columns you wish to delete and click
Home   Cells button group  Delete    Delete Sheet Rows or   Columns. (Yes, you can Undo these
commands). Just keep in mind that if you delete the cells whose data contribute to a formula, that
formula will suddenly have nothing to work with—and instead of a result, you’ll be left with an error
message in the cell instead. 

Inserting and Deleting Cells

You can also insert and delete selected cells, not just entire rows and columns, a possibility which is
curiously piecemeal. If you click in cell A12 and carry out the Insert Cells command, you’ll push A12
down  a r ow– but y ou won’t push down row 12 in its entirety. Only the A column will be affected by the
command. Any data in cell B12 will remain there, for example. 
To insert or delete cells, click in the cell or cells in question and click either the Insert or Delete
buttons we described in the previous to command sequences, but click Insert Cells… or   Delete Cells…
instead. Click Insert Cells and you’ll see (Figure 7–2):

Figure 7–2. Where to insert selected cells Click OK. If you select Shift cells right, all the cells to the right of the cell(s) in which you click will
move in that direction—but not the cells to their left. The other two options you see— Entire row  and
Entire column—are nothing but alternatives to the Insert Row and Columns commands we’ve already
de scr i be d.
To delete selected cells, click in the cells you wish to delete and click Delete Cells… in the Cells
button group (Figure7–3).

Figure 7–3. Going in reverse: where to delete selected cells.

Note here that deleted cells move the remaining cells that are to their right to the left , and cells
be n e ath the m wi l l  be  shi fte d up.

Multiple Worksheet Basics in Microsoft Excel 2010

As you can see, the three start-off worksheets that stock an Excel workbook share the same first name—
Sheet1, Sheet2, and Sheet3 (you move between worksheets simply by clicking the tab of the sheet you
want to access, or by utilizing these keyboard equivalents: Ctrl+Pg Dn to advance to the next sheet on
the right; Ctrl+Pg Up to the next sheet to your left). But as with file names, Sheet1, etc. are default
identities which can be changed as your needs require. As a result, you might very well want to
rename any or all of these, and it’s easy to do so. To rename a worksheet: 
•  Right-click the tab of the sheet you want to rename. Click Rename on the shortcut
menu (Figure 7–5):

Figure 7–5. By any other name..where to rename a worksheet

•  Since the current tab is selected, just type the new name, and press Enter.
You can also rename the sheet by double-clicking the sheet tab in question, which also selects the
tab. Type the new name and press Enter. You’re allotted 31 characters per name.

Inserting a New Worksheet In Microsoft Exel 2010

Inserting a new worksheet is most easy, too. Just:
•  Right-click the sheet to the right of which you want to insert the sheet. You’ll see
again (Figure 7–6):

Figure 7–6. One way in which to insert a new worksheet
•  Click Insert… .In the Insert dialog box. The new worksheet will be selected by
de faul t.
Click OK. The new sheet appears, bearing the default name Sheet4, if it’s the first new sheet you’ve
inserted. And there’s a still easier way to insert a new sheet. Click the Insert Worksheet  button to the
immediate right of the worksheet tabs (Figure 7–7):

Figure 7–7. And here’s another
Note the keyboard equivalent, too—Shift-F11. Clicking Insert Worksheet inserts a new sheet to the
immediate right of the last sheet. It’s the swiftest way to introduce a new sheet, but because it
automatically installs the sheet at the end of the worksheet queue, you may decide you want to
reposition the new sheet somewhere else.
Deleting an existing sheet entails right-clicking a sheet tab, then clicking Delete, and if it’s empty,
the sheet simply disappears. If the sheet contains data, this message materializes on screen (Figure 7–8):

Figure 7–8. In case you need to rethink a worksheet deletion
Note that prompt. The word “permanently” means that if you click Delete, the sheet (and not just
its data, in spite of what the prompt states) will not be retrievable via the Undo command. As a result, if
you’ve accidently deleted a sheet you still need, you may have to resort to the classic close-the-file-without-saving-it technique. Don’t say you weren’t warned

Busting a (Sheet) Move In Microsoft Exel 2010

To continue our medley of right-click options: If you want to move or copy a sheet, either within the
existing workbook or to another open workbook, or sheets (we’ll soon see how to select multiple
sheets), right-click the relevant sheet tab, and select Move or Copy….You’ll see this dialog box (Figure
7–9):

Figure 7–9. Peripatetic worksheet: Where to move or copy a worksheet to another book.
As you see, you’ll need to click on the name of the sheet before  which you want the sheet to be
moved. Note here that, by default, the To book:  field names the workbook in which the sheet is
currently positioned. If you want to move or copy the sheet to a different book, click the down arrow by
“To book” to view the names of other  open wor kbooks. Alter natively, you can click  (move to end),
whereupon the sheet will be resituated at the end of the sheet collection, no matter how many sheets
you currently have on hand in the workbook. Then click OK.

Note that you can copy the sheet(s) to a different open wor kbook, too. Clicking Create a copy will do
just that, replicating the sheet (including all its data) and placing it in the first position among the
sheets (though obviously you can go ahead and move it). Copying a sheet coins a new sheet name
based on the copied sheet, e.g., Sheet 2 (2).

■ Tip You can also move a sheet by clicking the sheet tab (left button, this time), dragging it to its new position
among the tabs, and releasing the mouse when you’ve reached your destination. A small page icon will
accompany you as your drag, letting you know that sheet move is in progress.

And you can also recolor the sheet tab. Right-click the tab, select Tab Color, an d se l e ct y our  hue 
from the resulting color selection. (Note: Your new color won’t actually appear in the tab until you click
on  a di ffe r e n t tab.)

Far-Flung Formulas: Working with Multi-Sheet Cell References in Microsoft Excel 2010

Now what about those multi-sheet cell references? As stated earlier, you can write formulas in which
cells in different worksheets contribute to that formula result. For example, if you allocate a separate
worksheet to each of three employees and enter their salary in the same cell on each sheet—say cell

A7—you can write a sum formula, anywhere, on any sheet, which totals the three salaries. (And in fact
the salaries don’t have to be entered in the same cell address on the respective sheets. They can be
situated in any cells.)

Doing a Multi-sheet Calculation
But let’s start with a simpler case—you want to add two numbers on different sheets:
1.   On Sheet 1 type 56 in cell D12. 
2.   On Sheet 2 enter 48 in cell B3. (Remember that the formula referencing these
two cells can be written on any sheet—even Sheet 3, but we’ll enter ther
formula on Sheet 1.)
3.   Click back on Sheet 1, onto cell A21. Once there, type the usual, and necessary,
= sign. 
4.   Click on cell D12, the cell in Sheet 1 containing 56. You’ll see:
=D12
Nothing new so far. Then:
5.   Type the + sign, simply because we’re about to add the contents of two cells. 
6.   Click the Sheet 2 tab, and click on cell B3. You’ll see (Figure 7–11):

Figure 7–11.  Writing a formula in Sheet1, including a cell in Sheet2
Note the budding expression in the formula bar: =D12+Sheet2!B3. By clicking on cell B3, the
formula supplements that cell reference with the Sheet2! prefix, and why? Because, remember—we’re
actually writing this formula in cell A21 on  Sheet 1, and so we need to indicate  which  cell B3 we’re now
referring to. After all, that cell could be in Sheet 1, Sheet 2, or Sheet 3—or any other sheet we might
have inserted into the workbook. As a result, Sheet2! is Excel’s way of notifying the formula that we
want to call upon the B3 in Sheet2. T he n  pr e ss En te r , an d we ’ r e  sn appe d back to She e t1, an d the

answer—104. And as with any Excel formula, its result will automatically recalculate, should either of
its two contributing values—the 56 and the 48—be changed. 
Now you may want to know why Sheet2! is attached to the second of the two cell references in the
formula, but nothing like it accompanies the first. That’s because the first cell reference–D12—appears
in the  same sheet as the one in which the formula was written, and Excel assumes, by default, that unless
the user indicates something to the contrary, all the cell references in a formula  and  the formula itself
emanate from the same sheet—and after all, isn’t that usually the case?
Now had we added these two numbers in a formula composed in Sheet3  instead—the sheet that
contains neither of the values we wanted to add—we would have clicked in a cell on Sheet3, typed =,
and then clicked on the two cells in Sheet1 and Sheet2 respectively. The formula would in this case
have looked like this:
=Sheet1!D12+Sheet2!B3
See why? In this case  neither cell shares its worksheet location with that of the formula itself, and
so Excel needs to specify the worksheets on which  both ce l l s a r e  posi ti on e d. 
So that’s the general approach to multi-sheet cell references in a formula—enter the = sign and
the mathematical operation (or function) you wish to perform, and then click on the sheet and the
cell(s) on that sheet you wish to incorporate into the expression. And if you need to reference a range
of cells from another worksheet, you can just type = in your current destination cell, click on the first
cell in the range on the sheet from which you wish to copy, then drag the appropriate range length, and
press Enter. All those cells from the source worksheet are now referenced here, because they’ll all be 
accompanied by the Sheet1, Sheet2, etc., identifier.
Now here’s a neat variation on that theme. Suppose you want to add a group of cells, all of which
ha v e  the  same address  in a collection of different worksheets—for example, values in the cell A3 in
Sheets 1, 2, and 3. Let’s try it:
•  In those three cells, enter 86, 72, and 4. 
•  In cell C19 on Sheet1, enter =SUM( . 
•  Then click on cell A3 in Sheet1, hold down the Shift key, and click on the Sheet3
tab. 
You should see this in the formula bar:
=SUM(‘Sheet1:Sheet3!’A3)
Press Enter, and your answer—162—flashes into the cell (note you don’t have to type the close
parentheses. Pressing Enter automatically supplies it). With this technique, Excel automatically
references the same cell in all the selected sheets—and by tapping the Shift key we’ve really grouped
all three sheets (this method works with any Excel function, not just SUM. In fact, once you press Shift
and click on the last of the sheets you want to reference, you can go ahead and drag any range you
want. That range, with precisely the same coordinates, will be selected on all the sheets, and all will be
calculated into the formula. Thus: 
=AVERAGE(‘Sheet1:Sheet3!’A6:B14)
will calculate the average of all the values in the A6:A14 ranges on the three sheets.
Note in addition that a named range on one worksheet can be directly referenced on any other
worksheet, without any concern for relative cell reference complications (note: this assumes the
range’s scope is the Workbook, which is the default in any case. See the Appendix on range names.)
Understand a key point here-that the cell coordinates of a named range don’t change—they’re treated
as absolute references (a point elaborated in the appendix) by default. Thus you can write:
=MAX(Scores)
on Sheet1, even if the range Scores is on Sheet2

Extending Your Reach: Referring to Cells in Different Workbooks in Microsoft Excel 2010

But there’s still another possibility: you can even reference cells in your formulas that come from
other   workbooks, that is, completely different Excel files. It’s possible you’ll need to calculate some
bottom- l i n e  total  for  sal e s or  budg e t data assi g n e d to di ffe r e n t wor kbooks, an d hav e  i t al l  di sti l l e d i n to
just one workbook; and that sort of task is eminently doable once the relevant cells are referenced.
True, you’ll want to proceed with care here, because if you email someone such a workbook—one
containing cell references to data in another workbook—and you don’t send along the  latter  wor kbook
as well, your data will be missing something. 
The way to go about referencing, or linking, data across workbooks is actually pretty easy, and
similar to the method we described above for referencing cells across worksheets in the same
workbook. Let’s try this: 
1.   Open two new wor kbooks, and save one as Link, the other  as Link2. 
2.   In cell G13 in Link type 65. In cell I2 in Link2 type 17. Now we’re going to try
and add the two numbers (needless to say, you can add many more than two,
and you can link ranges as well this way). 
3.   Remaining in Link2, type =I2+ in cell A1.
4.   Click on Link, and simply click cell G13. You’ll see (Figure 7–12):

Figure 7–12.  Note the more elaborate cell reference, pointing to a different worksheet in a different
workbook
5.   Then press Enter. The answer appears.
Note the syntax of the formula we’ve just written. Because we’re working with cells in two
different workbooks, Excel needs to specify two things: the  workbook in which the linked cell(s) is

located, as well as the worksheet, too. The name in brackets—[link.xslx]—obviously points to the
workbook. Note as well that it’s the cell that isn’t  in the same workbook as the formula that needs all
these specifications, and note also that it’s written with dollar signs, signifying an absolute reference.
T ha t’ s be ca use  i f y ou copy  the  for mul a  down  a  col umn , for  e xa mpl e , Exce l  a ssume s y ou wa n t a l l  the 
copied formulas to reference the same cell in that other workbook.
Keep in mind that if you currently have only  the workbook open that contains the linked  cell and
you change its data—and then later open the workbook containing the formula—the formula  will
recalculate automatically. And by the same token, let’s say you change the data in the linked cell, close
it and save it, thus leaving neither workbook open. When you open the formula-bearing workbook, it
will likewise recalculate. (Note: If you move one of the workbooks to a different folder, the current link
will be severed and will have to be reinstituted, if that’s what you want, requiring a fairly messy repair
job. An  Edit Link dialog box appears, asking you to supply the new location of the moved workbook.) In
sum, wor ki n g  wi th ce l l  r e fe r e n ce s can  be  a bi t di ce y , an d shoul d be  use d fr ug al l y . Apar t fr om the 
moving-folder issue, tracking the cell references that contribute to your results can be daunting,
particularly if you need to analyze a mistake in a formula

The Watch Window—Spying On Your Own Data In Microsoft Excel 2010

I’ve said it before, and I’ll say it again: workbooks are vast. You may have formulas scattered all across
its worksheets, or even in far flung cells on the same sheet. And what if you’ve written a formula
referencing cells in very different places on the workbook, such that when you changed the data in one
such cell you could no longer see the new formula result on screen, because you’ve scrolled too far
away? Well, you can always keep that result in your sights with the Watch Window option, located in
the  Formula Auditing button group in the Formulas tab. Let’s try a very simple illustration, which
should prove its point. Just watch.
Type 71 in cell D18 on Sheet1. Then type 21 in cell A2 in Sheet2. Return to Sheet1, and write the
following formula in E17:
=D18+Sheet2!A2
Answer: 92. OK—been there, done that, got the t-shirt. But now click back onto Sheet2, and click
For mul as   Watch Window. You’ll see (Figure 7–13):

Then click Add Watch…. You’ll see something like this (Figure 7–14):

Figure 7–14.  Watch this
I say “something like this,” because the cell reference you actually see right now depends on the
last cell you clicked on Sheet2, because that’s where we are right now. But we want to track the current
value of the formula in E17 on Sheet1, so just click that cell, and that reference should appear in the
Add Watch di al og  box. (Note : T he  di al og  box asks y ou to cl i ck the  ce l l s y ou wi sh to watch, sug g e sti n g  y ou
could select a range. If you do, each cell in the range appears in the Watch Window, along with its
value.) Then Click Add . You should see (Figure 7–15):

 Figure 7–15.  The current result for cell E17 in Book3, Sheet1

Note the dialog box records the workbook name as well as the sheet in which our watched cell is
positioned; that tells you that if you have more than one workbook open at the same time, you can
watch cells in any and all of them. The Name column, currently blank, is reserved for any range name
you may have assigned a range you’re watching (See Appendix XX). More obviously, the cell address
and current value  in the cell is recorded, along with the formula the cell is housing. Remember that
we’ve clicked on Sheet2; so type 93 in cell A2, the cell on this sheet that is contributing to our formula.
The Watch Window experiences a change in its  Value  column to 164—reflecting the new total in the
formula in E17 on Sheet1—which we can’t actually see right now. That’s the point; the Watch Window
keeps us posted of changes in the values of cells that, at the moment, aren’t visually available to us. To
turn off the Watch Window, just click the standard X in the window’s upper-right corner. The Watch
Window isn‘t remembered by the saved workbook. It has to be reconstructed if you want to use it again
with a r eopened wor kbook.
Protect Your Cells 

Hiding Worksheets in Microsoft Excel 2010

You can  al so hide  entire worksheets, raising the obvious question as to why you’d want to. The
principal reason isn’t a desire to conceal the sheet from the dark intentions of industrial spies,
cov e tous col l e ag ue s, or  assor te d othe r  bad g uy s, be cause  hi dde n  wor kshe e ts can  be  r e v e al e d e asi l y 
(there are Visual Basic programming means for securing the sheet with a password, though). Rather,
you may want to hide a sheet because it contains complex formulas you’d rather not overwrite, or
because all those calculations are unsightly (you can also protect all or part of a worksheet for much the
same reasons, but protection options leave the sheet in view. More on protection a bit later.) Keep in
mind that hidden worksheets remain active; that is, all their data and formulas continue to be
available in the workbook, and can still be referenced by formulas in the visible sheets.
To hide a worksheet, right-click the sheet you want to hide and select  Hide Worksheet. That’s all.
You can also execute the Hide command by clicking on the Home tab   Cells in the Format button
group    Hi de  & Un hi de    Hide Sheet  (I suspect you’ll find the first approach just a bit more efficient).
Note  a s we l l  tha t y ou ca n  ca r r y  out the  Mov e  or  Copy, Re n a me , or  T a b Col or  cha n g e  comma n ds v i a  the 
Format button, too. To reveal a hidden sheet or sheets—say we’ve hidden Sheet 3—right-click
anywhere among the still-visible worksheet tabs and click Unhide (rather an inelegant verb, but Excel
seems to be fond of it). You’ll see (Figure 7–10):