PraxIS May 2010

10-05 Contents: Upcoming conferences, how not to use Excel

ISSN 1649-2374 This issue online at http://www.sysmod.com/praxis/prax1005.htm   [Previous] [Index]  [Next]

Systems Modelling Ltd.: Managing reality in Information Systems - strategies for success

IN THIS ISSUE

 Risk and Quality
      Conferences in Dublin for May & June

 Spreadsheets
     What not to do in Excel

 Off Topic
     What not to do in Powerpoint
About this newsletter and Archives
Disclaimer
Subscribe and Unsubscribe information


Welcome to PraxIS

I've been working hard at upgrading ny XLTest spreadsheet auditing add-in, as well as preparing training courses in spreadsheet development practices and analysing survey data. This month, I summarise recent discussions on best and worst habits in using Excel spreadsheets.

Patrick O'Beirne

____________________________________________________________
____________________________________________________________

IT Risk

Test Manager Forum May 20

SQS are holding a forum for Test Managers in Croke Park, Dublin, on 20 May.
http://www.sqs.de/events/testmanager_dublin_event.htm Registration 150 euro

ISSA Ireland event May 27

The Information Systems Security Association (ISSA) are holding a free lunchtime event on 27 May in Dublin. Presentations will be on SQL Injection, data privacy legislation, and practical controls in social networking sites.
http://www.issaireland.org/may2010

Epicenter for Software Developers, June 8-11

Four days and 74 sessions for software developers. Languages, Tools, Best practices, international speakers.
http://epicenter.ie Grants are available for small companies to attend from the Software Skillnet
http://www.isa-skillnet.com

____________________________________________________________
____________________________________________________________

Spreadsheets

New resources

Tom Grossman of the University Of San Francisco has just started a web site on Spreadsheet Analytics with many links to best practice guidelines and tools:
http://www.usfca.edu/bps/spreadsheet-analytics

What NOT to do in Excel

This month saw a long thread "How NOT to use Excel" developing on the Excel-L list http://peach.ease.lsoft.com/archives/excel-l.htm Many of the points made are to do with efficient use of Excel rather than error avoidance.

Don't:

Merge Cells - they often cause problems in copy/paste and VBA operations. Use Centre Across Selection instead.
Press the space bar to erase a cell - it leaves the cell with text content rather than using the delete key which leaves it empty.
Format thousands more rows that you need - it causes file bloat and slow performance.
Use Pie charts when Bar/Column charts convey meaning more easily and do not hide negative values.
Use 3D charts - it's visual clutter, like Flufferpoint.
Use large tables of Lookup formulas when a Pivot table is simpler.
Leave blank rows or columns in data tables, it defeats many convenient navigation and manipulation shortcuts.
Put =SUM() around every formula on the grounds that "We are doing Sums".
Ignore 'Circular Reference' in the status bar.
Use worksheet formulas rather than VBA user defined functions (UDF) where possible.
Use VBA UDFs rather than over-complex worksheet functions (You can see this is like conflicting proverbs :-)).
Leave ghost links in a workbook caused by copy & paste between workbooks.
Embed (hard-code) variables in formulas when they should be in their own cell for ease of update.
Use shared workbooks with tracked changes that can become grossly bloated.
Use garish colour schemes or fonts.
Accumulate large amounts of macro-recorded code without pruning and simplifying it.

Do:

Use SUM(A1:A4) rather than A1+A2+A3+A4. It is easier to type and safer to insert or delete rows within the range. On the other hand it hides a possibly mistaken sum if a precedent cell is a number formatted as text.
Use names for constant or parameter cells.
Put disparate blocks of data on separate sheets rather than one one worksheet where inserts or deletions could mess up unrelated tables.
Set number of Recently Used Files to the maximum.
Use Full menus rather than ones that drop down after a hesitation.
Read some good Excel books .. see below for 'Spreadsheet Check and Control'.

Jocelyn Paine is collecting all these at: http://www.j-paine.org/blog/how_not_to_use_excel/
Other web sites with advice:
http://www.sysmod.com/praxis/prax0404.htm#Excel The Devil's Guide to spreadsheet creation
http://michiel.wordpress.com/2009/04/05 10 worst Microsoft Excel practices
http://www.dailydoseofexcel.com/archives/2009/04/06 Worst Excel Practices
http://caseelse.net/209098/08/06 Why not to use Excel for data gathering
http://www.burns-stat.com/pages/Tutor/spreadsheet_addiction.html Spreadsheet Addiction
http://www.eusprig.org European Spreadsheet Risks Special Interest Group (EuSpRIG)
http://thomasonspreadsheets.blogspot.com Most common problems implementing complex spreadsheet apps
http://www.plumsolutions.com.au/articles/five-excel-formulas-make-you-cringe Five Excel formulas that make you cringe

Spreadsheet Check and Control: 47 best practices to detect and prevent errors

http://www.sysmod.com/scc.htm

____________________________________________________________
____________________________________________________________

FEEDBACK

Simply send your comments to FEEDBACK (at) SYSMOD (dot) COM

Thank you! Patrick O'Beirne, Editor

_______________________________________________________
_______________________________________________________

Off Topic

http://www.youtube.com/watch?v=GB7S-KOJIfEHow not to use Powerpoint, a video by comedian Don McMillan

_______________________________________________________
_______________________________________________________

Copyright (c) Systems Modelling Limited, http://www.sysmod.com . Reproduction allowed provided this copyright notice is included.

We appreciate any feedback or suggestions for improvement. If you have received this newsletter from anybody else, we urge you to sign up for your personal copy by sending a blank email to   EuroIS-subscribe (at) yahoogroups (dot) com

For those who would like to do more than receive the monthly newsletter, the EuroIS list makes it easy for you to discuss issues raised, to share experiences with the rest of the group, and to contribute files to a common user community pool independent of the sysmod.com web site. I moderate posts to the EuroIS list, to screen out inappropriate material.

Patrick O'Beirne, Editor
_______________________________________________________
ABOUT THIS NEWSLETTER
"Praxis" means model or example, from the Greek verb "to do". The name is chosen to reflect our focus on practical solutions to IS problems, avoiding hype. If you like acronyms, think of it as "Patrick's reports and analysis across Information Systems".
Please tell a friend about this newsletter.
We especially appreciate a link to www.sysmod.com from your web site!
______________________________________________________
ARCHIVES
To read previous issues of this newsletter please visit our web site at http://www.sysmod.com/praxis.htm

DISCLAIMER
This newsletter is prepared in good faith and the information has been taken from observation and other sources believed to be reliable. Systems Modelling Ltd. (SML) does not represent expressly or by implication the accuracy, truthfulness or reliability of any information provided. It is a condition of use that users accept that SML has no liability for any errors, inaccuracies or omissions. The information is not intended to constitute legal or professional advice. You should consult a professional at Systems Modelling Ltd. directly for advice that is specifically tailored to your particular circumstances.
_______________________________________________________
PRIVACY POLICY:
We guarantee not to sell, trade or give your e-mail address to anyone.
To subscribe to this Newsletter send an email to
EuroIS-subscribe (at) yahoogroups (dot) com
To unsubscribe from this Newsletter send an email to
EuroIS-unsubscribe (at) yahoogroups (dot) com
EuroIS is the distribution list server of the PraxIS newsletter. It also offers a moderated discussion list for readers and a free shared storage area for user-contributed files. The archives of this group are on YahooGroups website http://finance.groups.yahoo.com/group/EuroIS/
_______________________________________________________