SBS - The best value in QMS software

Learning Excel - Web Site, Book, and Add-in Recommendations

A

adamsjm

#1
While my main expertise is in Process Improvement, I have become an intermediate MS Excel Application developer in order to implement many of the solutions. Having read this forum for the past couple of months and the questions about MS Excel posted on it, I thought I would pass on some MS Excel web sites to use and learn from, a list of books to read, and tools which can be added into Excel to speed up your application / project development.

MS Excel is an extremely versatile and powerful tool. You can even develop .xll (similar to .xls files) functions in C+ /C++ and integrate them directly into Excel. This process is used on Wall Street to value and assess Derivative Trading. While we will probably never obtain that level of expertise, we can greatly improve our effectiveness and efficiency along the way.

Websites

The most important web site to bookmark is a listing of Excel’s Most Valuable Programmers. These are independent Excel developers who do not work for MS, but make their living developing Excel applications. They have proven themselves in their knowledge and their willingness to share it.

http://www.mvps.org/links.html#Excel

From that list the best “General Interest How-to Sites” (especially for new learners) are:
http://www.cpearson.com/
http://www.contextures.com/tiptech.html
http://www.j-walk.com/ss/
http://www.mvps.org/dmcritchie/excel/excel.htm
http://www.exceluser.com/index.htm
http://www.dailydoseofexcel.com/

The “Higher End How-to Sites” are:
http://www.appspro.com/ - Rob Bovey
http://www.oaltd.co.uk/ - Stephen Bullen
Rob and Steven are, in my opinion, the best Excel developers / experts in the world.
http://www.datapigtechnologies.com
http://www.jkp-ads.com/
http://www.tushar-mehta.com/

The outstanding “Graphing and Charting How-to Sites” are:
http://www.peltiertech.com/
Jon is a metallurgist by training, with a Doctor of Science degree from MIT. He has over 20 years experience in research and development, and more recently in manufacturing and production support, in the aerospace, automotive, and industrial parts industries. His background includes Statistics, Total Quality, and Six Sigma. He has developed many quality charts and has posted the how-tos on his web site.
http://www.andypope.info/

Tip for searching the Web: How do you get a good response to your web search? When you are seeking a “how-do-I” start your search with "MVP", enter your key words that you are searching, and then finish with “*excel*”. By doing this your responses will have an answer from an Excel MVP which will be correct.


Books

The following books are targeted for Excel 2000-2003. The same authors have written companion books for Excel 2007. The books are listed in difficulty order – low to high.
[See the attached Word document or .jpg files for the book images.]

Add-In tools

These Add-in tools can greatly improve your productivity. And the best part is they are FREE.
Rob Bovey once told me, “If you find a tool that you need and they are charging for it, keep searching. There will be a better one free of charge.”

PopTools http://www.cse.csiro.au/poptools/
Greg Hood wrote the statistic tool add-in PopTools. MS Excel’s statistical functions are not noted for their robustness and accuracy. If you require the best in statistical tools, add PopTools to your Excel. It has been written as an Add-in so that you can see his methodology and as a compiled for fast execution (a ,xll).

[From the web site]
PopTools is a versatile add-in for PC versions of Microsoft Excel that facilitates analysis of matrix population models and simulation of stochastic processes. Included in PopTools are routines for iterating spreadsheets. These make it possible to run Monte Carlo simulations, conduct randomisation tests (including the Mantel test) and calculate bootstrap statistics. The routines include array formulas for matrix decompositions (Cholesky, QR, singular values, LU), eigenanalysis (eigenvalues and real eigenvectors of square matrices) and formulas for generation of random variables (eg, Normal, binomial, gamma, exponential, Poisson, logNormal). Many of these functions depend on Jean DeBord's TPMath library - a Pascal numerical library - which has been compiled into a DLL accessed via Excel.


Add-in tools from Rob Bovey ► http://www.appspro.com/

XY Chart Labeler

A very commonly requested Excel feature is the ability to add labels to XY chart data points. The XY Chart Labeler adds this feature to Excel. [Used extensively by Jon Peltier]

Excel Utilities

There are several great development utilities available for the VBE, but the Excel Utilities is the only comprehensive add-in aimed at Excel user interface development. The Excel Utilities provides a set of 30 features that you'll use on a daily basis.

VBA Code Cleaner

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage.
Note: Rob does not write his code to function in Worksheet Modules or in the ThisWorkbook Module. If the code breaks, it becomes unrecoverable. Code Cleaner can “fix” Standard, Form and Class Modules.

Code Documentor
The Excel Code Documentor automatically documents the vital statistics of your VBA project.


Add-in tools from Jan Karel Pieterse ► http://www.jkp-ads.com/

The Name Manager

An excellent utility to manage defined names in your workbooks.
(Incorporated into MS Excel 2007)

Flexfind

Eases searching and replacing throughout an entire workbook. Also enables you to search and replace strings in objects such as headers and footers, chart titles, buttons and many, many more.


Add-in tools from Decision Models Ltd, Charles Williams & Jan Karel Pieterse
http://www.decisionmodels.com/fastexcel.htm

FastExcel (Cost $79)

"Most slow-running spreadsheets contain a small number of problem areas, or Bottlenecks. Because Excel is such a flexible spreadsheet system there are usually many different formulas that can produce the answer you want. Some of these formulas are much faster than others.
In large spreadsheets it can be difficult to locate and prioritise the Bottlenecks.You can use the wide variety of timing and profiling tools in FastExcel to rapidly drill down, locate and prioritise these bottlenecks, and then use FastExcel's built-in Help for advice on eliminating or optimising them."


Add-in tools from Andy Pope ► http://www.andypope.info/

Button Editor

An add-in for creating button images.

Graphics Exporter

Add-in for exporting items in your workbook to graphics files



Add-in tools from Bill Manville ► http://www.oaltd.co.uk/MVP/Default.htm
FindLink
To look for links from a workbook


Add-in tools from MZ-Tools ► http://www.mztools.com/v3/mztools3.htm
MZToolsVBA
MZ-Tools 3.0 is a freeware 'all-in-one' add-in for Visual Basic 6.0, Visual Basic 5.0 and the Visual Basic For Applications editor (provided by a VBA-enabled application such as those in Office 2000 and higher) which adds several productivity features to


Program from TechSmithhttp://www.techsmith.com/
SnagIt Screen Capture (Cost $39.95)
Using SnagIt, you can select and capture anything on your screen and share it with others to visually clarify difficult concepts, create clearer documentation, and give your presentations a professional, dynamic look.

This is by no means an all-inclusive list of sources or tools, but I hope it will be a start.

Joe Adams
Process Excellence Architect
** Company name removed **
(near) Indianapolis, Indiana
 

Attachments

Last edited by a moderator:
Elsmar Forum Sponsor
M

mguilbert

#2
Thanks for the list. I have used most of these in past and they have been quite helpful. I would add www.mrexcel.com as another useful excel site that also deals with access and other programs. Also www.utteraccess.com is useful for access training.

Thanks again for compiling the list.:thanx:
Matt
 
R

RRder

#3
Not being a guru of Excel, but have touched the surface and like it. My company operates with Mac's, so my computer is a Mac OS X, version 10.5.7 and using MS Excel 2008 for Mac, version 12.2.0. Will many of these aids work with my Mac - Excel system or are they designed strictly for the PC - IBM version?
 

Jim Wynne

Staff member
Admin
#5
The most important web site to bookmark is a listing of Excel’s Most Valuable Programmers. These are independent Excel developers who do not work for MS, but make their living developing Excel applications. They have proven themselves in their knowledge and their willingness to share it.
Thanks for all the good links! A few points of clarification and a little more information: The "P" in "MVP" stands for "Professional," not "Programmers," and not all of the Excel MVPs make their living developing Excel applications. The best way to take advantage of their expertise is through the Microsoft Usenet newsgroups. There's more than one for Excel, such as microsoft.public.excel and microsoft.public.excel.programming. If you don't have or don't want Usenet access, MS also has a web interface that can be accessed here.
 
#6
Any Excel Guide Specifically concentrating on Excel Macro Application ? please share !


While my main expertise is in Process Improvement, I have become an intermediate MS Excel Application developer in order to implement many of the solutions. Having read this forum for the past couple of months and the questions about MS Excel posted on it, I thought I would pass on some MS Excel web sites to use and learn from, a list of books to read, and tools which can be added into Excel to speed up your application / project development.

MS Excel is an extremely versatile and powerful tool. You can even develop .xll (similar to .xls files) functions in C+ /C++ and integrate them directly into Excel. This process is used on Wall Street to value and assess Derivative Trading. While we will probably never obtain that level of expertise, we can greatly improve our effectiveness and efficiency along the way.

Websites

The most important web site to bookmark is a listing of Excel’s Most Valuable Programmers. These are independent Excel developers who do not work for MS, but make their living developing Excel applications. They have proven themselves in their knowledge and their willingness to share it.

http://www.mvps.org/links.html#Excel

From that list the best “General Interest How-to Sites” (especially for new learners) are:
http://www.cpearson.com/
http://www.contextures.com/tiptech.html
http://www.j-walk.com/ss/
http://www.mvps.org/dmcritchie/excel/excel.htm
http://www.exceluser.com/index.htm
http://www.dailydoseofexcel.com/

The “Higher End How-to Sites” are:
http://www.appspro.com/ - Rob Bovey
http://www.oaltd.co.uk/ - Stephen Bullen
Rob and Steven are, in my opinion, the best Excel developers / experts in the world.
http://www.datapigtechnologies.com
http://www.jkp-ads.com/
http://www.tushar-mehta.com/

The outstanding “Graphing and Charting How-to Sites” are:
http://www.peltiertech.com/
Jon is a metallurgist by training, with a Doctor of Science degree from MIT. He has over 20 years experience in research and development, and more recently in manufacturing and production support, in the aerospace, automotive, and industrial parts industries. His background includes Statistics, Total Quality, and Six Sigma. He has developed many quality charts and has posted the how-tos on his web site.
http://www.andypope.info/

Tip for searching the Web: How do you get a good response to your web search? When you are seeking a “how-do-I” start your search with "MVP", enter your key words that you are searching, and then finish with “*excel*”. By doing this your responses will have an answer from an Excel MVP which will be correct.


Books

The following books are targeted for Excel 2000-2003. The same authors have written companion books for Excel 2007. The books are listed in difficulty order – low to high.
[See the attached Word document or .jpg files for the book images.]

Add-In tools

These Add-in tools can greatly improve your productivity. And the best part is they are FREE.
Rob Bovey once told me, “If you find a tool that you need and they are charging for it, keep searching. There will be a better one free of charge.”

PopTools http://www.cse.csiro.au/poptools/
Greg Hood wrote the statistic tool add-in PopTools. MS Excel’s statistical functions are not noted for their robustness and accuracy. If you require the best in statistical tools, add PopTools to your Excel. It has been written as an Add-in so that you can see his methodology and as a compiled for fast execution (a ,xll).

[From the web site]
PopTools is a versatile add-in for PC versions of Microsoft Excel that facilitates analysis of matrix population models and simulation of stochastic processes. Included in PopTools are routines for iterating spreadsheets. These make it possible to run Monte Carlo simulations, conduct randomisation tests (including the Mantel test) and calculate bootstrap statistics. The routines include array formulas for matrix decompositions (Cholesky, QR, singular values, LU), eigenanalysis (eigenvalues and real eigenvectors of square matrices) and formulas for generation of random variables (eg, Normal, binomial, gamma, exponential, Poisson, logNormal). Many of these functions depend on Jean DeBord's TPMath library - a Pascal numerical library - which has been compiled into a DLL accessed via Excel.


Add-in tools from Rob Bovey ► http://www.appspro.com/

XY Chart Labeler

A very commonly requested Excel feature is the ability to add labels to XY chart data points. The XY Chart Labeler adds this feature to Excel. [Used extensively by Jon Peltier]

Excel Utilities

There are several great development utilities available for the VBE, but the Excel Utilities is the only comprehensive add-in aimed at Excel user interface development. The Excel Utilities provides a set of 30 features that you'll use on a daily basis.

VBA Code Cleaner

During the process of creating VBA programs a lot of junk code builds up in your files. If you don't clean your files periodically you will begin to experience strange problems caused by this extra baggage.
Note: Rob does not write his code to function in Worksheet Modules or in the ThisWorkbook Module. If the code breaks, it becomes unrecoverable. Code Cleaner can “fix” Standard, Form and Class Modules.

Code Documentor
The Excel Code Documentor automatically documents the vital statistics of your VBA project.


Add-in tools from Jan Karel Pieterse ► http://www.jkp-ads.com/

The Name Manager

An excellent utility to manage defined names in your workbooks.
(Incorporated into MS Excel 2007)

Flexfind

Eases searching and replacing throughout an entire workbook. Also enables you to search and replace strings in objects such as headers and footers, chart titles, buttons and many, many more.


Add-in tools from Decision Models Ltd, Charles Williams & Jan Karel Pieterse
http://www.decisionmodels.com/fastexcel.htm

FastExcel (Cost $79)

"Most slow-running spreadsheets contain a small number of problem areas, or Bottlenecks. Because Excel is such a flexible spreadsheet system there are usually many different formulas that can produce the answer you want. Some of these formulas are much faster than others.
In large spreadsheets it can be difficult to locate and prioritise the Bottlenecks.You can use the wide variety of timing and profiling tools in FastExcel to rapidly drill down, locate and prioritise these bottlenecks, and then use FastExcel's built-in Help for advice on eliminating or optimising them."


Add-in tools from Andy Pope ► http://www.andypope.info/

Button Editor

An add-in for creating button images.

Graphics Exporter

Add-in for exporting items in your workbook to graphics files



Add-in tools from Bill Manville ► http://www.oaltd.co.uk/MVP/Default.htm
FindLink
To look for links from a workbook


Add-in tools from MZ-Tools ► http://www.mztools.com/v3/mztools3.htm
MZToolsVBA
MZ-Tools 3.0 is a freeware 'all-in-one' add-in for Visual Basic 6.0, Visual Basic 5.0 and the Visual Basic For Applications editor (provided by a VBA-enabled application such as those in Office 2000 and higher) which adds several productivity features to


Program from TechSmithhttp://www.techsmith.com/
SnagIt Screen Capture (Cost $39.95)
Using SnagIt, you can select and capture anything on your screen and share it with others to visually clarify difficult concepts, create clearer documentation, and give your presentations a professional, dynamic look.

This is by no means an all-inclusive list of sources or tools, but I hope it will be a start.

Joe Adams
Process Excellence Architect
Strategic Quality
(near) Indianapolis, Indiana
 
A

adamsjm

#7
nirlep quote:
Any Excel Guide Specifically concentrating on Excel Macro Application ? please share !
For application how-to's, I would start with the book route.
The first book to read would be any VBA book by John Walkenback. Start with one for your version of Excel.
The second book would be Excel 2002 (or 2007) by Bullen and Bovey.
The third would be "Professional Excel Development" by Bullen and Bovey. It is the authority on application development, but it is also not for beginners or the uncommitted.

The websites I listed would have individule modules (or the bits and pieces) to speed up your application coding.

Joe
 
Thread starter Similar threads Forum Replies Date
S Any ISO standards around Artificial Intelligence and Machine Learning? Medical Information Technology, Medical Software and Health Informatics 4
K Regulatory requirement of SaMD with machine learning component IEC 62304 - Medical Device Software Life Cycle Processes 3
F Learning Uncertainty Budget for Novice - Fluke 5520A Measurement Uncertainty (MU) 1
I Good resources for learning statistics (quality engineering related) Statistical Analysis Tools, Techniques and SPC 10
M Informational Health Canada has launched an e-Learning tool to aid in understanding the premarket regulatory requirements for medical devices in Canada Medical Device and FDA Regulations and Standards News 0
pbojsen Computerized Learning Management Systems (Training systems) recommendations? ISO 13485:2016 - Medical Device Quality Management Systems 3
M Medical Device News FDA News - 27-08-18 - 2018 Learning program and GUDID updates Other US Medical Device Regulations 0
ScottK What Became of Alamo Consulting or Alamo Learning Systems Consultants and Consulting 5
M Learning by gaming / (gamification) Training - Internal, External, Online and Distance Learning 12
V Accelerated Learning and Simulations / Scenarios - Statistics Statistical Analysis Tools, Techniques and SPC 0
T Having Fun While Learning ISO 9001:2015 ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 6
F Getting the audit procedure in my head (learning it) Quality Manager and Management Related Issues 3
M Bachelor of Engineering Course - Distance learning Training - Internal, External, Online and Distance Learning 2
M How to prepare for a PLI (Professional Learning Indicator) job test Career and Occupation Discussions 6
W TS16949 Quick Learning Material Training - Internal, External, Online and Distance Learning 1
V Learning Management Systems (LMS) & Training Management Systems (TMS) Training - Internal, External, Online and Distance Learning 2
M Learning ISO 13485 - Getting a better understanding of the requirements ISO 13485:2016 - Medical Device Quality Management Systems 6
K Non-Normal Data Analysis Literature, Websites, Books for Learning Quality Tools, Improvement and Analysis 2
P Which training system or LMS (Learning Management System) to train Auditors? Training - Internal, External, Online and Distance Learning 8
R IQ/OQ/PQ Literature - Learning and implementing IQ/OQ/PQ procedures at my facility Design and Development of Products and Processes 3
R Quality Manager Training Methods - Learning and Implementing ISO 9001 ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 26
C Learning Material for a Third Party Distribution Sales Environment Training - Internal, External, Online and Distance Learning 2
Q Training Management Systems (aka Learning Management Systems) Training - Internal, External, Online and Distance Learning 3
C Zeiss C 90 HP - Any suggestions for learning Umess UX. Inspection, Prints (Drawings), Testing, Sampling and Related Topics 2
P Beginning with ISO - Suggest any diploma course or online learning course Training - Internal, External, Online and Distance Learning 3
H ISO 9126 E-learning training course - Recommendations wanted Software Quality Assurance 3
C Complete ISO idiot on a steep learning curve - Quality Manual Review appreciated ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 9
P What is Distance learning course? Training - Internal, External, Online and Distance Learning 8
J Learning Statistical Process Control - I need a crash course in SPC Statistical Analysis Tools, Techniques and SPC 8
M Internal Audit Exercises/Games to Engage Auditors in Experiential Learning ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 34
B CQE (Certified Quality Engineer) Exam Learning Guidance and Related Questions Professional Certifications and Degrees 15
B Suggestions for Books on Testing/QA related to E-learning Courses Book, Video, Blog and Web Site Reviews and Recommendations 2
B ISO 9001 for an E-Learning company - How to proceed when processes are not clear ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 7
Q Aerospace Quality related Distance Training/Learning/Exams Training - Internal, External, Online and Distance Learning 2
ScottK Foundations in Quality Learning Series: ASQ's Quality Basics Training - Internal, External, Online and Distance Learning 1
ScottK Learning about converting an AutoCAD .dwg to a .STEP file Manufacturing and Related Processes 5
L Registrar Customer Learning Session-Independence Ohio Coffee Break and Water Cooler Discussions 2
Govind Sharing key Audit learning across multi-site operations General Auditing Discussions 12
P TS 16949 AIAG books needed for learning the requirements of the standard IATF 16949 - Automotive Quality Systems Standard 12
S Layman's Learning Tool Needed - Regression analysis, t-tests, chi-square tests, etc. Statistical Analysis Tools, Techniques and SPC 5
Marc Distance Learning - From ivory tower to academic sweatshop Training - Internal, External, Online and Distance Learning 2
I Anyone know of any good TS 16949 E- learning sources? Training - Internal, External, Online and Distance Learning 1
C A New Term for Defectives: SLO - Significant learning opportunities World News 0
C Learning Uncertainty - Training, Good book? Good software or freeware? Measurement Uncertainty (MU) 14
N Distance Learning - Quality Management and TQM - Seeking Recommendations Training - Internal, External, Online and Distance Learning 1
C ISO 9001:2000 E-Learning for people new to ISO 9000 Training - Internal, External, Online and Distance Learning 2
Claes Gefvenberg E-learning take 2... Is anyone currently, or in the past, tried E-Learning? Training - Internal, External, Online and Distance Learning 21
C Learning Curves - Biggest Process "VARIABLE" is mobile workforce ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 19
A Use of Computer Aided Learning for Quality Topics - E-Learning Training - Internal, External, Online and Distance Learning 20
A Distance Learning where I would be able to obtain a 4 year degree in Quality? Training - Internal, External, Online and Distance Learning 7

Similar threads

Top Bottom