Other Excel Add-In, Templates and Training
Lesson 2B
Inserting Rows, Columns and Cells
When working in
Excel, there are times that you may need to insert an entire new row, an entire
new column or only a single cell. Although the term Inserting gives
the impression that you are actually adding another row, column or cell, you are
in fact NOT. You may remember that in the first lesson we mentioned the fact
that Excel has 256 columns, 65,536 rows and 1,677,216 cells per worksheet. These
numbers are fixed, so technically we cannot add more. What we can do, however,
is move a particular row down, column across, cell in. This can seem confusing,
so it is probably best that we do a practical example so you can see how it
works.
-
In cell A1
type any data. In cell A2 also type any data.
-
Select cell
A2 right click, and select Insert.
You will notice
that the Insert dialogue box will be displayed giving you four options.
-
Shift Cells Right
-
Shift Cells Down
-
Entire Row
-
Entire Column
For this
exercise, lets select Shift Cells Right, then click OK. You will
notice that the contents of cell A2 has now been moved to cell B2.
Lets now select
cell B2, right click, select Insert and select Shift Cells Down.
The contents of cell B2 should now appear in cell B3.
Now select any
cell in row 1, right click, select Insert then Entire row, click
OK. You should have an entire new empty row for row 1.
Lets now insert
an entire column. Select any cell in column B, right click and select
Insert, then Entire Column and click OK. You should now have
an entire new empty column for column B. When we insert rows, columns or
cells in this manner (that is with a single cell selected) Excel will always
display the Insert dialogue box, allowing us to make our choice.
The same
Insert dialogue box is available by selecting Insert on the
Worksheet menu bar and then your required option (cells, rows, columns)
We can, however,
bypass this dialogue box and insert either an entire row or column by default.
To insert an entire row by default, select any row number (the row number on the
left in the shaded area) so that the entire row is highlighted. Then right click
and select Insert. You will notice that Excel makes an educated guess
that you are after an entire row as it is an entire row that you have
highlighted. The exact same thing would happen if we had an entire column
highlighted, ie; an entire column would be inserted.
If we want to
insert more than one cell, row or column at a time, we simply select the
appropriate number of rows, cells or columns before using Insert either
via the menu option or the right click Shortcut menu. For example, if you select
four entire rows, then right clicked and selected Insert, you would be
inserting four new empty rows.
Changing Default Options in Excel
Sometimes when
working in Excel, it can be handy to set it up in a way that you would most
commonly use. In other words, change its default options.
To do this, go to
Tools>Options. This will display the Options dialogue box. On this
dialogue box you will notice that there are multiple page tabs. We will go
through the most commonly used options. If however, there is an option on here
anywhere that you would like to know about, but are unsure of, just let us know
and we will explain it in detail.
The View Tab
Show/Formula Bar
This option
simply toggles the Formula Bar to being visible or non-visible.
Show/Status Bar
This option
simply toggles the status bar to being visible or non-visible.
Show/Windows
In Taskbar
With this option
checked, there will be an icon on your taskbar for each Excel Workbook you have
open. The Taskbar is the very bottom one, where your Start button
is located.
Comments/None
Cell comments are
not displayed (discussed in a later lesson).
Comments/Comment
Indicator Only
Only an indicator
in the cell containing the cell comment is shown by default.
Comments/Comment
and Indicator
Both the comment
and its indicator will be visible by default.
Objects/Show All
All graphic
objects are visible. A graphic object could be a text box, drawing object,
inserted picture, etc.
Objects/Show
Placeholders
Selecting Show
Placeholders means that any pictures or charts are only shown as grey
rectangles.
This option will
not have any affect on text boxes and other drawing objects.
Objects/Hide All
All objects
including charts, text boxes, pictures etc., are not displayed.
Window Options/Formulas
Selecting this
option will make any formulas in cells visible as opposed to the result of the
formula only. This is most commonly used should you wish to print a Worksheet
and have all the underlying formulas displayed.
Window Options/Gridlines
Toggles the view
of the gridlines between on and off.
Window Options/Colour
Changes the
colours of the gridlines
Window Options/Row and Column Headers
Removes the
column letter headings and the row number headings.
Window Options/Outline Symbols
Displays outline
symbols. (worksheet must contain a previously created outline.
Window Options/Zero Values
Toggles between
displaying zeros and not displaying zeros.
Window Options/Horizontal Scroll Bar
Toggles the
horizontal scroll bar on and off.
Window Options/Vertical Scroll Bar
Toggles the
vertical scroll bar on and off.
Window Options/Sheet Tabs
Toggles the
display of the sheet name tabs on and off.
The
Calculation Tab
Note: It is
recommended that you leave these options as their default unless you are sure of
their definitions.
Calculation/Automatic
This means Excel
will automatically recalculate whenever necessary.
Calculation/Manual
This means Excel
will only calculate when we tell it to. To do this manually, you would push the
Shortcut key F9. You would basically only ever use this option if you had
a very large workbook containing a lot of formulas that was recalculating very
slowly.
The Edit Tab
Settings/Edit Directly in Cell
There are many
ways you can edit a cell. This option allows you to edit a cell by
double-clicking.
Settings/Allow Drag and Drop
This enables us
to perform the Drag and Drop operation as discussed before with Copying and
Cutting.
Settings/Alert
before Overwriting Cells
This option means
that if we use the Drag and Drop operation to place the contents of
another cell into a cell that already contains data, Excel would display an
Alert informing us and giving us the choice as to whether to continue or
not.
Settings/Move
Selection after Enter
As you are
probably aware, when you are selected in a cell and you push Enter you
are moved to the cell directly below it. We can change this to any of the four
directions, that is down, right, up or left. Or, if we uncheck the Move
Selection after Enter box, we will not be moved to another cell after
pushing Enter
Settings/Fixed
Decimals
Selecting this
option allow us to determine how many decimal places Excel will use by default.
In other words, if we selected this option and had the Places set to 2, and then
entered the number 2 into any Worksheet cell, we would actually end up with the
number 0.02. Again, unless needed, leave this option unchecked as it can cause
confusion.
The General Tab
Settings/Sheets in New Workbook
This lets us
determine the number of sheets Excel will automatically place in a new Workbook.
Settings/Standard
Font
Allows us to
determine the font type and size Excel will use by default.
Settings/Default
File Location
This allows
us to determine the location by default that your files will be saved in unless
specified otherwise.
Summary
To summarize
Lesson 2, we have looked in detail at Copying, Cutting and Pasting and the
multitude of ways that this can be achieved. We have gone deeper with the
Paste Option by looking at the Paste Special options and the types of results it
can give us.
We have looked in
depth at the very powerful and useful Fill Handle and discussed ways of
inserting and deleting cells, columns and rows. We have also covered
changing some of the default options in Excel to customize our workspace.
Goto Next Lesson
Back to Excel Add-in, Templates and Training Main Page
Special :
Get the complete training Excel Training for free when you purchase our Technical Indicators now!
|
