12/02/2015

Microsoft Excel 2016 Tutorial - Insert / Remove Columns Rows and Cells

In Microsoft Excel, many times you may
require to add more data to your current worksheet, by adding new columns,
rows, and even cells. So in Excel terms, we call it Inserting Columns, Rows,
and Cells. Not only that, but many times you may also need to delete columns
and rows. So in this tutorial, you will learn how to insert and delete columns,
rows and cells in an Excel Worksheet.
In this Excel Worksheet, I need to
insert a Student Roll Number column after the student name. So, to do this, I
select the column B, by left clicking on its heading. You can insert sheet
columns and rows in many different ways, and every time you insert a new
column, it is inserted left to the selected column. Now to insert a new column,
from the Home tab click Insert drop down menu, under Cells section, and then
click Insert Sheet Columns. There you have a new column inserted left, and your
current data is automatically shifted to the right. Now here I can add the
Student Roll Number.
The other method that you can use to
insert a new column is to use, right click context menu command. Let’s say I
need a blank column between the subjects, English and Physics, and for this I
need to insert a new column after column G, and before column H. So the other
approach to insert a new column is to, directly right-click on column H, and
then click Insert from the context menu. So it is up to you that which method
you find easiest to use.
Other times, you also might want to
remove some extra columns from your worksheet. For instance, in this particular
worksheet, I don’t need that 30% column under subject English. So to remove
this column, you can either select this column first, and then click Delete in
Cells section from the Home tab, and then click Delete Sheet Columns, or you
can right-click on the column in question, and then click Delete from the
context menu. When you delete a column, the data to the right is automatically
shifted to the left.
Same as columns, you can also insert
new rows between your current data. For instance, I need to enter a new student
name between John and James. Do note that, when you insert a new row, it is
inserted upward to the selected row. So here I need a new row after John and
before James. So I select James, and then click Insert Sheet Rows from the Cells
section under Home tab.
You can also insert a new row by
directly right-clicking on the row number, where you need a new row, and then
click Insert from the context menu. To remove an entire row, you can either
right-click on the row and then click Delete from the context menu, or you can
click on Delete from the Cells section, and then click Delete Sheet Rows.
So you always have two very common and
easy ways for inserting and deleting sheet columns and rows, and it is up to
you that which one you prefer, the most, or use both methods now and then.
Lastly, we have the option from
inserting and removing cells. Cell or Cells, are refered to as an intersection
of a column and row. It is important to understand the concept of inserting and
removing cells from the sheet, instead of inserting or removing an entire
column or row.
While rechecking the data of this
worksheet, I find out that, by mistake, I fill the marks of student name
Smiley, in the row of student name Lovely, and I actually forget to fill the marks
of Lovely. The data to the left and right side are fine. So I don’t need a
complete new row here. What I need here is to insert 5 new cells, and shift
these 5 values down. To do this, I first select these 5 cells, then either
right-click on them and choose Insert, or I can click Insert Cells from the
Cells section under Home tab. Then I will prompted with four options, like do I
want to insert a new column or row, or insert new cells and shift the currently
selected cells to either down or right side. As I want to insert new cells only
and want to shift the currently selected cells to down, so I choose the second
option and hit Ok. You can see that the data in the left and right cells of the
selected cells will remain intact.
You can also do the opposite by using
the Delete Cells command. Let’s say that you want to delete all the empty cells
for student name Lovely, under the subject Physics, and move the bottom values
to upward. So select these cells, then right-click and choose Delete. From the
Delete dialog box, select Shift Cells Up and press OK.
Sometimes, you also might want to
insert or remove more than one columns or rows in a time. When you use Insert
Column or Insert Row command, Excel automatically inserted those number of
columns or rows, equals to selected columns or rows. For instance, if I select
two columns and then use the Insert Sheet Columns command, then two new columns
will be inserted, and that also applies to the rows as well. Using the same
method, you can also delete more than one column or row at once.
You can also insert new columns, rows,
or even cells using a Keyboard Shortcut, and the shortcut key is, Control and
Plus Sign from numeric keypad. If a column or row is already selected, and you
press Control Plus, then it will automatically insert a new column or row
according to selection, without prompting anything. But if you press Control
Plus without selecting anything, then Insert dialog box appear, and you can
then make a choice.
To remove column, row, or cells, you
can use Control Minus sign from Numeric Key pad.
The procedure we just follows was, to
insert or remove one or more columns or rows in a series. But what if you need
to insert or remove multiple columns or rows in a non-continuous fashion. For
this, you need to make a non-continuous selection first and to do this, select
the first column or row, then hold down the control key, while clicking and
selecting other columns. Now if you will use Insert Sheet Columns command, a
new column will be inserted after each selected column, or you can also use
Delete Sheet Columns to remove multiple non-continuous columns at once. The
same procedure goes for the sheet rows as well.
So this was all about inserting a
single or multiple columns, rows, or cells into your worksheet. If you found
this video useful then don’t forget to hit the thumbs up, and do share this
video with your friends. Leave us a comment in the box below, and subscribe to
our channel for more upcoming videos. Thanks for watching, and take good care
of yourself!