Excel Tips & Tricks, Shortcuts For Excel

Shortcut Keys
On most of Excels menu items you will see the shortcut key associated with it. To
see a complete list push F1 and type "Shortcut Keys".

Quick Help
To get quick help on any menu item push Shift+F1 and click the menu item

Insert Today's Date
To insert Today's date push Ctrl+; (semicolon)

Insert Current Time
To insert the current time push Ctrl+Shift+: (Colon)

Show the Paste Function (Function Wizard)
Push Ctrl+F3

Show the GoTo dialog
Push F5

Show the Paste Names dialog
Push F3. This will only work if you have named ranges.

Name a Range
To name a selected range, click in the "Name box" (far left on the formula
bar) and type a one word name.

Go To a Named Range
To go to a named range select it from the "Name box" (far left of the formula
bar). Or push F5.

Edit a Named Range
To delete or edit a named range go to Insert>Name>Define or Push
Ctrl+F3.

Headings as Range Names
Highlight your range including the headings and go to Insert>Name>Create
or push Ctrl+Shift+F3.

Named Formula
To make a Name refer to a constant formula e.g. "TaxRate", go to Insert>Name>Define
and type TaxRate in the "Names in Workbook" box and 36% in
the "Refers To". Now enter =(10*TaxRate) anywhere on the Worksheet.

Named Range List
To obtain a list of all Named Ranges and where they refer, select any blank
cell (make sure you have no data underneath or 1 column over) and go to
Insert>Name>Paste then Paste List.

Nested Formulas
To help write nested formulas (more than 1 formula in a single cell) use
the "Paste Function" i.e. Insert>Function or Shift+F3. Select
the function that you need, enter the reference, number or text then select the
drop arrow to the left of the formula bar to add more Formulas. Doing it
this way ensures all your parentheses are in the correct places.

Debugging Formulas
To troubleshoot complex formulas select the cell containing it and then click the
= (Equal sign) to the left of the formula bar, this will activate the "Paste
Function". To step through your formula simply click in the part of the
formula you want to debug.

Personal Help
To add your own text to any of the Office Assistants help files, push
F1, enter your question then open the file. Go to Options>Annotate and
type in your own text then click OK. You will now notice a paperclip
symbol next to the heading, this will let you know that you have added your own
Help in a way you will understand.

Different Help
Sometimes the Office Assistant is not very helpful to your needs, so try
the "Context and Index" help by either clicking Help>Context and Index
or selecting "Help Topics" from any "Help" file.

Customizing Toolbars
Right click on any Toolbar and select "Customize" or push Ctrl+Shift+F10
twice then "Customize". Now click the "Commands Tab" and drag menu
items both on and off the Toolbars. If things get a bit messy simply click
the "Toolbars" tab and click "Reset". This will return all menu items
to their default.

Worksheet Template
Set up your Worksheet how you want it e.g. formatting, formulas etc then delete
all other sheets in the Workbook. Now go to File>Save or Alt+F2 and
select "Template (*.xlt)" from the "Save as Type". Type a name and
click "Save" Now right click on the sheet tab and select Insert you
should see your Template sheet.

Secret Menu
Click in any cell, then move your mouse pointer over any border of the cell
until the mouse pointer changes to an arrow, right click and drag
to it's destination and then release.

Secret Menu 2
Place a date in any cell, then move your mouse pointer over the bottom
right corner of the cell (Fill handle) until the mouse pointer changes
to a small black cross. Now right click and drag to any cell and release.

Quick Cell Move
Click in the cell(s) then move your mouse pointer over any border until the
mouse pointer changes to an arrow, left click and drag to it's destination
and then release.

Quick Cell Copy
Click in the cell(s) then move your mouse pointer over any border until the
mouse pointer changes to an arrow, left click and hold down the
Ctrl key and drag to it's destination and then release.

Change Formulas to Values
Click in the cell(s) with the formula(s) then move your mouse pointer over
any border until the mouse pointer changes to an arrow, right click and drag
to the next cell, now still holding down the right mouse button drag back
to where you Start ed and release. Now select Copy here as values only.

Quick List
To quickly copy down the contents of a cell that has a list in the column
to the left or right of it, simply click in the cell you want to copy and then
Double click the Fill handle (little black square on the bottom right of
the cell).

Fill Blank Cells Within a List
Let's say you have a list of entries in column A and within the list you have many
blank cells. Here is a quick way to fill those blanks with the value of the cell
above. Highlight column A, then push Ctrl+G and click Special then check the
Blanks option and click OK. Now push Equals (=) then the Up
arrow and finally holding down the Ctrl key push Enter.

Auto Fill
To fill a series across columns or down rows type January or Jan in any
cell and place your mouse pointer over the bottom right corner of
the cell (Fill handle) until the mouser pointer changes to a small
black cross. Left click and drag down or across. This can also be done with
Numbers, Weekdays, Quarters or any text that ends in a number e.g. Day1.

Custom Auto Fill
Type your list across columns or down rows. Now go to Tools>Option and select
the "Custom Lists" tab. Click the collapse dialog box to the right of the "Import
list from cells" box, highlight your range, click the expand dialog and then click
"Import". Or type your entries in the "list Entries" box.

Adding Text to Formulas
To show a formula result and text or number(s) in the same cell type a &
(Ampersand) after the formula then your text/number(s).

Adding Hidden Text to Formulas
Imagine you have a formula like: =$2018+$1056-4*$120. When you initially wrote it
you knew what each number represented, but you come back later and can't remember.
Add a hidden note to your formula by using the N() formula i.e. =$2018+$1056-4*$120+N("My
Wage+Bonus-4 weekly loan repayments"). The N() function will convert text to zero.

Custom Format
You can format a cell to show any number or text without changing it's real
value using "Custom Format". To see this type the number 20 in any cell then go
to Format>Cells or push Ctrl+1. Select the "Number" tab and then select
"Custom." Using any one of the pre-defined formats type "Twenty" (without quotations)
or any text and then click "OK". To test it use the cell in any formula.

No More Chart Gaps
If you have a chart that is plotting empty text ("") or 0 (zero) from a formula
then instead of using "" or 0 if the formula is False try using "#N/A" (without
the quotations) or the formula =NA(). Or you can hide the Row(s) or Column(s). Either
way Excel won't plot #N/A or hidden Rows or Columns.

My List
If you have a long list of Text with no blank cells between and you
want to see a preview of what is in your list. Click in any cell within your list
then right click and select "Pick from list", If you select one of the entries,
Excel will insert it in the cell for you.

Remove Blank Rows
Highlight your range and go to Edit>Go to>Special and select "Blanks" then
"Ok" now go to Edit>Delete or Ctrl+Shift+= (equal) and then select
"Entire row" from the "Delete" dialog and click "Ok".

Sort Out Blank Rows
The quickest way to remove all blank rows is to select you range then go to Data>Sort.

See Formula cells
If you have a sheet full of formulas and you want to identify these cells
at a glance go to Edit>Go to>Special and select "Formulas" then click "OK".
Now go to Format>Cells or Ctrl+1 and select the "Patterns" tab and
choose a color.

En Masse Changes
To make changes to more than one worksheet at the same time select
one of the sheets, hold down your Ctrl key and click on each sheet name
tab. Now any data entered one sheet will also be entered on the other(s). When
you have finished right click on any of the sheet name tabs and select "Ungroup
sheets".

En Masse Changes 2
Another way to have changes on one worksheet reflected on other sheets
is to make all the changes you want on one sheet then hold down your Ctrl key and
select the other sheet tabs. Go to Edit>Fill>Across Worksheets and Excel
will give you 3 choices of what to copy to the other sheets i.e. "All", "Contents"
or "Formats".

Worksheet Copy
Select the sheet name tab then hold down your Ctrl key and simply drag it
to the position you want it.

Paste Reference
An easy way to reference another cell is to select the cell you wish
to reference then right click and select Copy or Ctrl+C then
select the cell you want the reference in, right click again and select "Paste
Special" then click "Paste Link"

Absolute/Relative Toggle
If you have a formula you want to make absolute or relative
then double click in the cell or F2 then place the insertion point
anywhere in the cell address and push F4 1, 2 or 3 times.

Repeat
To repeat an operation push F4

Undo
To undo an operation push Ctrl+Z

Linked Picture
A good alternative to a textbox or any shape is a linked picture that reflects
any changes made to its reference. To make one, copy your cell(s), select
the destination cell and holding down your Shift key go to Edit<Paste
Picture Link.

Run a Macro by Clicking a Cell
This is possible with use of VBA but let's face it most people don't know VBA
so here is an easy way. Select the cell you want to run the macro and hold
down your Shift key and go to Edit>Copy Picture then select
"As shown on screen" from the "Copy Picture" dialog then hold down your Shift
key again and go Edit and click "Paste Picture". Now right click on the cell
picture and "Assign Macro".

Non Formula Result
Sometimes you just want the result from the Sum, Average, Min, Max etc from
a group of cells without typing a formula in a cell. Excel allows you to
do this very easily, first highlight the cells you want to evaluate then right click
on the "Status Bar" and select the function you want and your result will
be displayed in the "Status Bar".

Reduce File Size
When you have a workbook that is very large in size you can reduce
this dramatically by saving the file as "Microsoft Excel Workbook (*.xls)" as apposed
to "Microsoft Excel 5/95 Workbook (*.xls)". In other words avoid saving as multiple
versions whenever possible. Also click
here for much more details and other methods.

Cell Navigation
To move through a group of cells that you are working with without going
outside the range highlight the group of cells and then use the "Enter" key to move
through them.

Quick Formula Syntax
When writing formulas for Excel sometimes you just need a quick reminder of the
formula syntax. In this is the case then type an equal sign followed by the function
name and push Ctrl+Shift+A. For Example typing =Vlookup and then pushing
Ctrl+Shift+A will give you: =vlookup(lookup_value,table_array,col_index_num,range_lookup).
The non-bolded arguments are optional.

How to copy formulas without the reference changing
This can be achieved by either pressing F2 and then highlighting the formula, Copy,
Enter then paste to destination. Or doing the same in the Formular bar. However,
this is not much good for large amounts of data, so try this: Select the range of
cells with Formulae, use the Ctrl key for non-contiguous ranges. Now go to Edit>Replace
and Replace = with #. Copy and paste to your location and then simply use Edit>Replace
# with =

How to copy and transpose formulas without the reference changing
In cell A1 of sheet 2 put: =Sheet1!A1 now copy this down a max of 255 rows.
Now with the formulas selected go to Edit>Replace and Replace = with #. Now
copy, select cell B1, go to Edit>Paste special and choose Transpose. Delete
Column "A" and with Row 1 selected go to Edit>Replace and Replace # with
=

Turn a List Upside-Down
1. Copy the list to another location using Copy, Edit>Paste Special>Value.
2. Now select all data in the list, go to Tools>Options>Custom Lists.
3. Ensure the list address is in the "Import list from cells:" and click
"Import".
4. Now go back to the column next to your list and in the top cell place
the LAST entry from your list.
5. Now in the cell below, place the second last entry.
6. Select both cells and double click on the Fill Handle (small black square bottom
right).

The list should now be reversed. You could now also sort you original list using
Data>Sort>Options, nominate your list then sort!

Formula Errors
Whenever typing one of Excels functions (especially nested ones) into a cell always
use lower case. This way when you push Enter Excel will capitalize only the
names of the functions you have entered correctly.

Entering Named Ranges Into Formulas
When you write a formula, sometimes you want to use a Named Range as one of the
arguments for the formula, but you cannot remember the name. In these times simply
push F3 when you reach the argument that you want the Named Range in and
Excel will display the Paste Name dialog. Click the name you want then
OK.

Optional Function Arguments
Sometimes you may not be sure what arguments in a function are optional and which
are not. If your using the Paste Function (Function Wizard) then the non-bolded
arguments are optional.

Sort by more than 3 Columns
Excels sort feature only allows to nominate up to 3 columns to sort by, here is
how to get around this. The key to this is sorting by the last key first and working
back to the first key. Say you data is in Column A:E and you want to sort by A,
B, C , D then E

1. Select all of Columns A:E
2. Go to Data>Sort> sort by C then by D then by E
3. Click Sort
4. Now again with Columns A:E selected
5. Go to Data>Sort> sort by A then by B
6. Click Sort

Printing Workbooks
If you have quite a few Workbooks to print, go to File>Open from within Excel, select
the Workbook(s) using the Ctrl key, then right click and choose Print.