 |
Training Index
Vital Tips in Using Microsoft Excel
|
|
|
If you came here from a search engine, the answer to your question is
probably on this site. Look below to see if the answer is on this page.
Alternatively search our site in the Google search bar right at the bottom.
Please help us out by indicating in the space provided where you would
like further information and submit by going to the bottom of the page.
|
Excels inbuilt functions- using what you’ve already
got
Over the years it has become apparent that spreadsheets contain errors.
This is clearly acknowledged by the addition of many audit functions to
the more recent versions of Excel. These are very useful features which
are seldom known about, understood or used. What follows is a summary
of these features and how they can be used:
Auditing Toolbar:
To load you auditing toolbar perform the following:
- Click on View > Toolbars > Formula Auditing
Note:
If you don’t see the Formulae auditing option, look in
the customised option. In some versions of Excel it is referred
to as the Auditing Toolbar. |
 |
What should pop up on the toolbar is shown below (or similar depending
on which version of Excel you are using). I highly recommend that you
anchor the toolbar amongst the other permanent toolbars as this will be
a vital component of any spreadsheet work (development or review).
The benefit of this tool is that it visually identifies
and can trace links across different sheets and even different workbooks.
The only requirement is that the workbooks are open at the same time.The
meaning and use of the buttons are as follows:
| Trace Precedents: |
 |
Visually identifies the cells used in the formula
being reviewed. If the cell is on a different sheet or workbook,
a dotted line will appear. Double click the dotted line and chose
the reference you want to see and you will be taken there.
|
| Trace Dependents |
 |
Visually identifies the cells that depend on the
current cell. As with the Precedent button, this can span across
a workbook to other workbooks as long as all related workbooks are
open. This can be used when deleting cells to ensure that the cell
is not used elsewhere in the spreadsheet.
|
| Remove arrows: |
 |
Eventually it will become confusing with all the
arrows on the spreadsheet. Use this button to remove all the arrows.
|
| Comments tool: |
 |
| Allows you to quickly insert a comment. |
TOP
Links:
Although it is nice to be able to trace relationships between cells over
multiple workbooks, it sometimes arises that the spreadsheet has an external
link that you cannot easily find.
The first trick is to identify what the link is referring to. Excel has
addressed the issue by giving you the LINK feature. When you are in a
workbook that indicates that there is an external link, do the following:
- Click Edit > Links
|
 |
This will bring up a screen that will indicate what other spreadsheets
are being referenced to. It also allows you to automatically change the
links in the spreadsheet to a new link, update links and the like.
However, it does not identify the actual cells that contain
the link and this is sometimes required. Therefore we need to find another
workaround.
TOP
Find and Replace:
The FIND/ REPLACE function has proved to be very useful in the auditing
of spreadsheets. Along with its normal function of finding words, it can
be used to find external links or particularly risky functions.
This is how:
When looking at the results of the LINKS function, find the external reference
that concerns you and look closely at the path. Try and identify some
unique components of the path e.g. special characters, unique names etc.
By using the unique set of characters you can search for them in the spreadsheet
and quickly identify the cells that contain the links.
So in the picture example you would search for the words:
"Other Workbook". For older versions of Excel you will need
to perform this on every sheet. For Excel XP, by clicking on the OPTIONS
button when you are in the FIND/ REPLACE menu, you can choose to search
the entire workbook. If you do not find a formulae with the external link
then look at any graphs you have and review the source data. As a result
the combined use of LINKS and FIND/ REPLACE can make it easy to track
down errant links.
TOP
Go To Special:
An underutilised tool in Excel is the GO TO feature and particularly the
button marked SPECIAL. By clicking EDIT, then GO TO, then SPECIAL, the
following will appear. (for training via video follow this
link)
By clicking on the desired option and clicking OK, Excel
will highlight all the cells on the sheet (or the selection) that
meet the specified criteria. Below is a description of each function:
Comments- will highlight all cells containing comments
(useful to delete unwanted comments)
Constants- will highlight all cells containing constants of the
kind required (number, text, logical, error)
Formulas- will highlight all cells containing formulae of the
kind required (number, text, logical, error).
Blanks-
will highlight all blank cells
Current region- will
highlight all cells in the current region
Current Array-
will highlight all cells in the current array.
Objects-
highlights all objects on the sheet (e.g. buttons, pictures etc)
Row/ Column
Differences- will highlight which cells in a selection contain
different formulae.
Precedents/ Dependants- will highlight the precedence or dependents
of a cell, either to one level or all levels.
Last cell-
will identify the last cell in a sheet (useful to find stray cells).
Visible cells only- highlight only cells that are not hidden
(useful to make changes to what you can see without affecting hidden
cells)
Conditional formats- highlights cells that have conditional formatting
on them (either any conditional format or the same format of the current
cell).
Data Validation- highlights cells that have data validation on
them (either any validation or the same validation as the current
cell).
|
 |
TOP
Functions and Features:
 |
Click here to tell us what you think. |
|
