Other Excel Add-In, Templates and Training
As with most
Microsoft Office applications, Excel contains a very handy feature that allows
us to Undo or Redo certain steps we have taken.
and Redo can both be found on the Standard toolbar. These are
represented by the arched left pointing blue arrow for Undo, and the
arched right pointing blue arrow for Redo. It can also be found under
Edit on the Worksheet menu bar as can the shortcut keys Ctrl + Z for
Undo and Ctrl + Y for Redo but most often you will probably
use the icons on the Standard toolbar as they are easily accessed.
The other reason
why you would probably choose the Undo and Redo via the
Standard toolbar is because you may notice next to the arched arrows to the
left or right, there are two small downward pointing arrows. Selecting these
arrows will present you with a list of up to the last 16 actions taken. In other
words, Excel will only store the last 16 actions taken by the user.
What this means
is that if you have performed 17 actions, the first of the 16 actions will be
replaced by the 17th. To see what how this works, close the Workbook you currently have
open and say No to saving any changes.
Now open a new
workbook. The reason we are doing this in a new Workbook will become apparent
In the new
Workbook type the numbers 1-16 in cells A1:A16. Do not use the
Fill Handle as this will defeat the purpose of the exercise.
Now go up to the
downward pointing arrow to the right of the Undo icon and left click on
it and you should see your last 16 actions. Take note of the fact that you have
Typing 1 in A1 as the action at the bottom of the list.
Now click in
cell A17 and type the number 17.
Go back up to
the downward pointing arrow to the right of the Undo icon and left click
on it and you will notice that Typing 1 in A1 has disappeared and at
the top of your list should now be Typing 17 in A17.
In other words,
Excel has replaced your very first action taken with the last action taken. This
will continue on as such at any time you are working in Excel.
the last action, you simply click the Undo icon.
Click this now
and the number 17 should disappear.
If you now click
on the drop arrow to the right of the Redo action, you should see that
the Typing 17 in A17 is now a Redo action as opposed to an
Now go back to
the downward pointing arrow to the right of the Undo action and hover
your mouse pointer over the top action, ie; Typing 16 in A16.
clicking, drag your mouse down slowly and you will see Excel highlight each
action as you go. This allows us to select the number of actions to Undo.
When you have reached the action at which you wish to stop, simply stop dragging
and click on the last highlighted action (or push Enter) and Excel will Undo
all the highlighted actions.
If you now click
on the downward pointing arrow of the Redo feature, you will see the list
of all the actions that we have just Undone.
So again, select
these actions and click (or push Enter) and you should be back to exactly
where we started.
important point to remember here is that Excel will only store the last 16
actions taken. The reason the number is only 16 is because storing any
more than this would start to have an adverse affect on the memory usage
The other very
important point to note of the Undo and Redo feature, is that
as soon as you Save, all of the Undo or Redo actions are
wiped and there is no way to get them back.
You can change
the number of actions stored in Undo, but to do that requires hacking
into Excel. If you are interested in how this works, let us know.
When working in
Excel there are times when you may wish to copy the formatting of a cell or
range of cells to another location without copying the content of the cell(s).
To make this simple, Excel has what is known as the Format Painter.
This can only be found on the Standard toolbar, is represented on the toolbar
by a paintbrush and is immediately to the left of the Undo icon. To be honest
this is much the same as using the Paste special and selecting Formats
as the option. The big advantage is that its much easier and quicker to access
and the copy and paste range do not have to be of the same shape and size. So
you can get a feel for this feature try these two simple exercises.
Type any number
in cells A1:A5.
Type any numbers
in cells B1:B10.
(select) cells A1:A5 and click the $ (dollar icon) on the
Now click the
B (Bold) and I (Italic) icons, also on the Formatting toolbar.
Format Painter icon and then click cell B1.
You should now
have painted the formatting we applied to cells A1:A5 to B1:B5.
You would also have noticed there was a paintbrush symbol next to our mouse
pointer, which disappeared once we had selected cell B1.
the Undo feature undo steps 3 to 5 so that all we have is the
unformatted numbers in A1:A5 and B1:B10.
apply the same formatting to these cells; eg. $, bold and italics,
A1:A5. With cells A1:A5 selected, double click the Format
click B1 and the formatting should be painted to cells B1:B5.
The difference this time is that the paintbrush symbol should still be
present. This is because we double clicked the Format Painter instead
of single clicking it
cell B6 and cells B6:B10 will also have the formatting applied.
the Format Painter, either single click the Format Painter icon
or push Esc on the keyboard.
You can see a
single click will apply the Format Painter once, while a double click
will keep the Format Painter active until we either click the icon again
or push Esc.
We mentioned above
that the copy and paste range do not need to be of the same shape and size when
using the Format Painter, to see this follow these steps.
Type any numbers
in cells C1:G1.
A1 and then single click the Format Painter.
Now select cell
C1 and with the left mouse button still held down drag to cell G1
and then release.
You should now have the same
formatting applied to cells C1:G1 as you have in A1.
So as you can see
the copy and paste range does not need to be of the same shape and size, unlike
the Paste special.
This should give
you enough to practice with for a while and we mention above, have a play about with
the Format Cells dialogue box. Do take note of any questions you may have
concerning it or any other feature of Excel and direct them to us and we will
The next lesson
we will be starting to head into the best part of Excel, which is numbers and
Goto Next Lesson
Back to Excel Add-in, Templates and Training Main Page
Get the Excel VBA Training for free when you purchase our Technical Indicators now!