|
LEARN MORE
Tips, Tricks
and Aha's
Format Cells - Number
- Custom The
most frequently asked question seems to be - how to align positive numbers
with negatives in brackets. In Custom Type enter the following #,##0.00
;(#,##0.00) The space before the semicolon matches up the bracket space.
Leave off decimals if not required. Apply format to the cell, block of cells
or column as required.
F4 - Repeats
the last command ie Insert a Row, then press F4 to repeat as many times as
necessary
AutoSum - Did
you know that if you select a block of figures plus one blank row and one
blank column you can cross cast and down cast all in one go.
Ctrl ['] -
Press control and apostrophe together to copy the contents of the cell
above.
Right Click the sheets scroll bar I< <
> >I - To see and select from a list
of all sheets in workbook.
Don't Hide join a Group
- Select Rows or Columns to hide and then select Data - Group and Outline -
Group. You can then click the + or - or the [1] [2] to Hide/Unhide - much
easier.
Columns to text
-
Ever wanted to split information in one column into
two or more i.e. First Name Surname. Type John F Smith in one cell. Select
cell, select Data - Text to Columns, when the wizard pops up select
Delimited then Next then click Space under Delimiters, then Next, then
Finish. If this were a list of names you could now sort by surname.
Text to Columns
- To merge data from many columns into one i.e. if John
F Smith is now in A B C in row 3 - select D3 and type =A3 & B3 & C3
and enter. Answer will look like JohnFSmith to insert spaces type = A3 &
' ' & B3 & ' ' & C3 or use the Function Wizard f(x)
and Concatenate.
Edit Paste Special
-
After selecting data and clicking the copy icon or
pressing Ctrl C there are several different ways of pasting - ie Values
pastes result of a formula rather than the formula, Format just pastes
format i.e. not data, Transpose pastes from column to rows or vice-versa,
Paste Link references the pasted cells back to the source sheet or workbook
ie change original entry and linked cells change.
Find and Replace
- Select Edit - Find or Ctrl F and type in text,
number or formula to find. To replace all occurrences of something - select
Edit Replace - Replace all - but be careful that the item to replace cannot
be part of something else i.e. 'the' could be part of 'these' or 'therefore'
etc.
To refine the Replace - select the area to be searched and use Match case
and Find entire cells only options if relevant.
Fill Cells
- there are many ways to do this but it is worth
remembering that if you want to fill say C3:C30 with text, value or formula
and B3:30 has data in it - then put the item to be copied down in C3
position cursor on bottom right of C3 so it appears as a + and double click.
Filter Quickly
-
There is an AutoFilter icon Select - View - Toolbars -
Customise - Commands tab - Data and drag the funnel with an = next to it
onto a toolbar. Select a data sheet containing multiple occurrences of
something i.e. a word or a value etc - select the item to filter and click the
icon - all rows not containing the item will be hidden (ie filtered out). -
Select - Data - Filter - AutoFilter to return to unfiltered data.
Total Filtered Data
- Using AutoSum beneath a column of filtered data
will return =Subtotal(9,C2:C300) or whatever the data range is.
Subtotal(9,n:n) will just total the filtered items left visible.
Select Contiguous (Touching) Data
-
To select all data in a block
surrounding cursor cell press Ctrl Shift * together.
File Location
- If you don't know why File Open and File Save usually
go to My Documents then select Tools - Options - General tab and look in
default file location and change as required.
Useful Add Ins
VAT-Check - Totally FREE - available on request by e-mailing
peter@excelforaccountants.com
Validate Vat Numbers - Enter a Vat number and see if it is valid.
Creates a worksheet showing how each digit is used to prove the final
2 digit check total. Needs Excel 95 or above
FREE Quotes - to Develop Templates, Macros and Training tools for your
firm. Please e-mail
peter@excelforaccountants.com
and specify your requirements.
Useful Links
- Other sources of help and information - much of it for FREE
Surfing
the Web for Excel related sites has yielded some very useful downloads
and a lot of interesting information. Here are a few of the results
that may interest you.
J-walk.com/22/excel/links
John Walkenbach is a top US Excel guru, Author of The Excel Bible and
other useful books etc. This site has links with loads of useful Excel
based sites.
http://www.ozgrid.com The Ozgrid team cover just about
everything you could want in relation to Excel - lots of good VBA help.
lacher.com John F
Lacher CPA is another Excel MVP (Microsoft Most Valued Professional).
His site has many free downloads relevant to accountants.
http://www.add-ins.com Has some useful Excel Add Ins to make life easier plus
book titles including downloadable E Books on VBA etc
AccountingWeb -
Expert Guides section contains a lot of Excel related information.
TKB- The Knowledge Base
provide IT services specifically for the accountancy profession.
Courses on disk - provides computer based training on CD
aimed at accountants.
Details can be found through TKB
- Special offers for Sole Practitioners
SWAT Limited
provide a full range of services to the accountancy profession
including Excel training
Looking Glass Training
This site contains training modules and many useful Excel Tips. The
self study training packs start at £5 per module and cover essential
practical issues like Vlookup to fill in accounts values
automatically.
Excel
based products for accountants - we will happily include any other
products recommended by you
VTSoftware
-
Final accounts in Excel for sole trader, partnership, and limited
companies, which links automatically with ExFiles. With this
combination you can import client trial balances from Sage or other
systems, prepare working papers and journals and final accounts all in
Excel.
Excel based utilities
Find Links
- Look under Bill Manville - If you have ever copied a
worksheet/workbook
with references to other sheets you will know that your new workbook
will have links back to the original reference. These links can be in
formulae, in a Name and often not easy to find. Sometimes removing
these links can be tedious - Find Links is an Add In that will find
and offer to list and/or delete each link.
Excel Books
Some time ago we were sent The Accountants Guide to Excel and the
The Accountants Guide to Advanced Excel. Our review follows:-
The author is James Fulford is an Irish Chartered Accountant, Partner
in an Accountancy Practice and Consultancy Firm and IT Editor of
Accountancy Ireland.
These publications covers the basics of computers, Windows and Excel
97, followed by Spreadsheet Auditing, Pivot Tables, Practical use of
VBA, What If, Consolidations and using PowerPoint to present
information to Management and Clients.
The author has taken a very practical approach to the delivery of
information. The books contain numerous Screen Shots to illustrate
every key point and the attached CDs contain numerous example
worksheets of practical value to accountants, such Net to Gross Pay
calculator, Cash Flow Projections, Accounts, VBA code and PowerPoint
slides
The examples on the CDs are intended to be used in conjunction with
the information in the books so that the reader can practice with live
data to reinforce understanding of the facilities and concepts.
The whole approach is extremely thorough and if time is taken to work
through the examples the reader will have a good working knowledge of
all aspects of Excel from basic to advanced usage. I particularly like
the fact that the examples are based on material that is directly
relevant to accountants, whether in Practice or in Business.
These books can be acquired for £35 each from Oak
Tree Press
JOBS
Accounting Jobs
AccJobs.com is the Accounting Jobs website. Search
100's of Jobs online or receive jobs via email.
|
