Example files

How did it come about?

What is going on in Excel's function BESSELK? I wouldn't know. But I sure can make it work and that is worth something. For most functions it fortunately goes like that apart from making them work, I also understand what they do. That's how FunctiesAlle.xls came about.
To make a long story short: I tried to put 'all' of Excel's functionality into practice. All functions, all chart types, all formatting options. I must admit: extremely boring and far from reality but at the same time, nice to have at hand as a reference. Here and there, I used simple real life examples.
Regarding Excel and Word, I caught up to version 2003. Access and PowerPoint stay initially limited to 2003. I no longer maintain the old files.
In the Access samples, for example, you'll find the steps of normalization, all field types with their (in-) possibilities, all possible ways to create queries, all types of forms and reports etc.
PowerPoint ... just one file, one presentation. With all this program is capable of.

All together, you'll find about 125 Mb of information and examples!

It stands to reason that on the Internet, people have shown what is known and what a program is capable of. In these example files you'll also find things that I don't know or things that cannot be done marked in red (as far as I'm aware of, that is). By doing so, these matters stand out. Not the way things are usually done, but perhaps a more honest approach. Assistance in these matters (additions, solutions) would be highly appreciated. He who knows everything casts the first stone.

If you appreciate my activities and want to encourage me working on it more, you can either send me greetings, patches or make a donation:

How to use?

Most Excel workbooks are listed twice: finished and blank. The finished version shows formatting, functions, animations, etc. In the blank copy, you can work your way.
Most example files contain macros. According to me, they are safe :-). Switch the macro security to Medium that you can choose to (not) use them.

The other examples are listed once.

The files

The content on this website may not be used for commercial purposes. The presentations and sample files (in whole or in part) may not be used in trainings, classes / courses, books or presentations without my express permission. I hope these files will add to your knowledge of the programs covered.

The use of the files is at your own risk.

For best performance [Save] and view the files locally. That will work better than [Open] in the browser.

Excel
File Mb Zipped Sheets Comments
Alles.xls
Alles.xlsx
0.2
0.1
0.2 1 A complete overview of all sheets in all workbooks with a short description.
Might help searching.
Beveiliging.xlsm 0.5 0.5 26 Examples of sheet and workbook protection, share workbook and trach changes. Some things still have to be cleared out.
DiversenBerekeningen.xls
DiversenBerekeningen.xlsm
0.4
0.4
0.4 23 A colorful collection of Excelleritus: nice matrices, trend and growth in various ways (charts included), registering work hours and meter readings, honest sharing of food costs, etc. etc.
DiversenDatumTijd.xls
DiversenDatumTijd.xlsm
0.5
0.53
0.6 20 I go for 'Captains log, star date ...'. Until then we are stuck with: leap years, ISO week numbers and negative times ... Also the date of the third (and first) Tuesday of september and upcoming months can be calculated.
DiversenGrafieken.xls
DiversenGrafieken.xlsm
15.4
11.1
29.5 59 Some nice and silly charts: the Netherlands getting hot, a waterfall, a histogram, the quadratic equation, a chart-vector drawing and other. See the differences between ten rounding functions and how to brighten up your family album with charts. In-cell charts: the 2010 Sparklines in 2003. The modulo charts are stunning.
DiversenGrafiekenAanvulling.xlsm 11.9 29.1 76 The third waterfall graph and a second box plot, this time created by Hessel de Walle. Most charts came from Hessel: the traffic lights, dynamic graphs by row and by column and more. In the zip-file an overview of the techniques used.
DiversenGrafiekenFunctieAnalyse.xlsm 0,7 0,7 11 Graphical analysis of various types of equations (van Nitin Metha).
DiversenGrafiekenOmwentelingen.xlsm 0,7 0,7 13 Graphs based on trigonometric functions (van Roberto Mensa).
DiversenGrafiekenPeltier.xlsm 2.0 2.6 77 You must pay but then you'll have something: additional chart types such as Waterfall, Miramekko and more.
DiversenOplosser.xls
DiversenOplosser.xlsm
0.1 0.1 0.3 10 The Solver samples by Microsoft make more sense. Yet a few sheets with amongst others an embedded equation and an ... erroneous Solver solution. The cups-and-balls exemple is fun.
DiversenRariteiten.xls
DiversenRariteiten.xlsm
5.1
2.1
6.0 22 Moronism and reality in one workbook: all kinds of math (Fermat, Kaprekar, Mersenne, Pascal), primes, combinations and permutations vs. iterations, mini RSA, check numbers and a working Conways Life.
NB: You may ignore the message on circular references when opening the file: click [Cancel].
Draaitabel.xls
Draaitabel2003.xlsm
Draaitabel2010.xlsm
0.8
0.5
1.0
2.8 21
 
24
Over 200 pivot tables, from simple to complex: all eleven functions, lay outs and usage of an external source. The workbook includes all table formats, calculated fields and items and a hand made steering.
DraaitabelOpgave.xls
DraaitabelOpgave2003.xlsm
DraaitabelOpgave2010.xlsm
0.5
0.3
0.5
     
ExterneGegevens.xls
ExterneGegevens.xlsx
0.5
1.1
1.8 11 Various examples of Data-Get external data.
Formules&Functies.xls
Formules&Functies.xlsm
0.5
0.6
4.9 20 Exegesis and examples on using formulas and functions. The workbook contains (almost) all theory in pocket size.
NB: There's no need to update links on opening. And click [Cancel] at the message regarding the circular references.
Formules&FunctiesOpgave.xls
Formules&FunctiesOpgave.xlsm
0.4
0.6
     
F&FHulp.xls
F&FHulp.xlsx
0.0
0.0
  3  
FunctiesAlle.xls
FunctiesAlle2003.xlsm
FunctiesAlle2010.xlsm
FunctiesAlle2016.xlsm
2.5
1.0
1.8
1.7
9.6  
26
31
31
All (2003: 335 - 2010: 418) Excel functions working! But no explanation of difficult functions such as MDURATION (calculates the modified - mcAuley - duration for a security), GAMMAINV (the inverse of the gamma cumulative distribution) or IMLN (the natural logarithm of a complex number). And a few 'User Defined function' samples.
FunctiesAlleOpgave.xls
FunctiesAlleOpgave2003.xlsm
FunctiesAlleOpgave2010.xlsm
FunctiesAlleOpgave2016.xlsm
2.1
0.8
1.6
1.5
     
FunctiesExtraInvoegtoepassingen.xls
FunctiesExtraInvoegtoepassingen.xlsm
0.8
1.8
10.7 30 Some add-ins contain their own functions; sometimes documented, sometimes not. Here a report of my struggle with the ASAP-Utilities, Name Manager, Polynomials and XlXtrFun.
To mention just one: horizontal and vertical lookup (one or two demensional!) to the nearest value in stead of the smaller value.
NB: You'll have to install the add-ins mentiond on your own computer first. ;-)
FunctiesExtraFastExcel.xlsm 6.8 5.9 27 Another add-in: Fast Excel Name Manager.
With Name Manager you can do more than with Excel's manager. The over 80 functions can do more and are faster than the Excel versions. Some can handle 'regular expressions'!
Formulier.xls
Formulier.xlsm
0.2
0.1
0.2 5 Amongst others a comparison between the VBA controls, old fashioned dialog sheets and the controls in the Forms toolbar.
FormulierOpgave.xls
FormulierOpgave.xlsm
0.1
0.0
     
Fractals.xls
Fractals.xlsm
8.7
6.8
17.2 13 Fantastic, those fractals. Inspired by a book, I've misused Excel to depict this phenomenon. The working (!) Mira fractals on the last sheet are by Ad Commeren. Excel frequently 'chokes' on the quantity of data.
Gegevensanalyse.xls
Gegevensanalyse.xlsm
1.5
1.4
2.3 25 My knowledge of statistics is limited. Yet I've had a shot at getting to the bottom of Data Analysis from the Analysis Toolpak. A lot is still missing in this workbook.
GegevensanalyseOpgave.xls
GegevensanalyseOpgave.xlsm
0.7
0.7
     
Grafieksoorten.xls
Grafieksoorten2003.xlsm
Grafieksoorten2010.xlsm
Grafieksoorten2016.xlsm
0.5
0.4
1.6
3,4
6.1 20
20
24
32
Locate the cell pointer somewhere in a list and press <F11>: chart made. This workbook gives 404 samples e.g. a 3D bubble and my 'pride': I've been on top of that 3D surface chart ;-).
GrafieksoortenOpgave.xls
GrafieksoortenOpg2003.xlsm
GrafieksoortenOpg2010.xlsm
GrafieksoortenOpg2016.xlsm
0.2
0.1
1.1
2.5
     
Grafiekopties.xls
Grafiekopties2003.xlsm
Grafiekopties2010.xlsm
0.6
0.4
5.0
3.6 16
16
34
I needed 122 versions of one charts to demonstrate all (?) chart options.
GrafiekoptiesOpgave.xls
GrafiekoptiesOpg2003.xlsm
GrafiekoptiesOpg2010.xlsm
0.2
0.1
3.6
     
HyperlinkMouseOverUDF.xlsm 0.4 0,3 9 Only point - no click! - and a cell value changes to a complete different chart.
All 'borrowed' from wize man.
Invalpool.xls
Invalpool.xlsm
0.1
0.1
0.8 1 At one time created for a school board. Based on available data, you can select an appropriate supply teacher. The workbook also reads data that is regitred by Invalpool.doc.
Lijsten.xls
Lijsten.xlsm
LijstenPersoneel.xlsm
3.3
3.6
0.1
8.2 47
50
6
Basic rules for the setup of an Excel list but also anything on sorting, filtering, applying functions in a list etc. Also a trick to compare two lists and some example lists with charts (treated with macros).  In the zip three 'list compare' tools by others (with credits!).
LijstenOpgave.xls
LijstenOpgave.xlsm
2.0
2.9
     
Macros.xls
Macros.xlsm
1.2
3.6
3.9 32 Ripe and rotten: sensible and absurd examples. A smart kilometer admini­stration, naming of sheet tabs and Excel starts 'talking'. A function macros parses an address in street, number and extention and Driehoek calculates the missing side of a right-angled triangle.
MSEAnder.xls
MSEAnder.xlsm
1.2
0.5
3.1 12 Links between Excel and other programs, such as Word.
MSEAnderOpgave.xls
MSEAnderOpgave.xlsm
0.2
0.1
     
OLE.xls
OLE.xlsx
2.3
2.7
5.0 6 Examples of the usage of OLE-objects.
OLEOpgave.xls
OLEOpgave.xlsx
0.2
0.6
     
Opmaak.xls
Opmaak.xlsm
1.0
7.6
9.4 16
27
Fonts, colors, borders, custom number formats, conditional formatting (also with formulas and relative) and so on. What RGB value is used by the House of Orange?
OpmaakOpgave.xls
OpmaakOpgave.xlsm
0.4
1.3
     
Plakken.xlsm 2.3 2.4 7 Copy and then paste, could it be more easy? Why then fifteen buttons and Paste Special with twenty options? I've tried to sort it out.
PlakkenHulp.xlsx
PlakkenHulp.docx
0.1
0.1
  1 Two tiny files with source data for Plakken.xlsm. Included in the zip file.
StructuurXML.xlsm 0.1 0.1 6 Office 2010: pptx, docx, xlsx, xlsm. It is all XML in the first quarter. This file is a first attempt to unravel the secret of the new file format.
Tekenen.xls
Tekenen2003.xlsm
Tekenen2010.xlsm
2.2
0.7
7.1
12.7 19
19
28
Excel is made for calculation. But you can neatly use the program to draw: all options with AutoShapes, Word- and SmartArt. From hightech schemes to a colouring picture for the kids.
TekenenOpgave.xls
TekenenOpgave2003.xlsm
TekenenOpgave2010.xlsm
0.9
0.5
4.6
     
VBAatje.xls
VBAatje.xlsm
1.3
0.9
1.6 13 Main topic of this workbook is programming in VBA. It contains 11 sheets, 3 forms, 18 modules, 4 class modules and hunderds of sample procedures and functions.
Access
File Mb Zipped   Comments
Attention please! The database showcase files are still 'under construction'.
On the other hand: I expect these files are worth while publishing.
You should download the auxiliary files (Hulpbestanden) anyhow. Place them in the same folder in the database and make this folder the working directory for Access.
Hulpbestanden.zip   0.2   In some example files OLE-references are used. All help files are in this zip.
BitsAndPieces1.mdb 2.8 0.7   A database full of twaddle: all data types, field properties, lookup fields, cross-field validation and indexes. Twenty one tables with simple (and funny) samples.
tblFieldValidations is about the mystery 'validation rule Is Not Null, Required and Allow Zero Length'.
Three forms show all data types, field formatting and that 'mystery'.
BitsAndPieces2.mdb 1.1 0.1   In BitsAndPieces2, I try to visualize 'normalisation'.
You'll find about the same data four times. At first, all data is in one flat table: normal form zero. In the fourth version, 'everything' is split out in main, junction and domain tables.
NB: I've used the Lookup Wizard to show foreign key values as readable data.
BitsAndPieces3.mdb 1.4 0.2   All kinds of queries in about 140 samples. Sorting, filtering, calculation (even ranking numbers); one or more source tables; inner, outer and self joins; action queries and SQL. All topics are covered one by one.
BitsAndPieces4.mdb 11.0 3.5   All sorts of forms. From an AutoForm, via all options in the Forms Wizard to a main-sub-sub-subform. And of course the Switchboard.
Programming controls is matter to discuss separately. For this see KleineDemos.mdb. But all events are shown in frm10##.
BitsAndPieces5.mdb 2.7 0.3   Ibid on reports.
BitsAndPieces6.mdb 1.0 0.2   Pages ... Once, I'll have to start on them ;-(.
BitsAndPieces7.mdb 1.4 0.3   Macros are a bit old fashioned. Yet a lot can be done. A little extra: macros 'run' from a custom toolbar (!).
BitsAndPieces8.mdb 1.7 0.4   In the modules no event procedures but VBA with MAO, DAO, ADO and SQL. That results in miles of code.
AccessBeveiliging.doc 0.0 0,0   The next seven files (Coderen to Wachtwoord) deal with security and such. The data and structure are not that interesting.
This document gives a short description and a few passwords.
Coderen.mdb 1.3 1,3   The data in the database are encrypted and therefore can no longer be read from disk (using a text editor). In Access everything will be readable and accessible as usual.
GebruikersGroepen.mdb
GebruikersGroepen.mdw
1.7
0.1
2.7   Using the User-level Security Wizard you really lock up a database. Rights can be set by database object and by user or group; from nothing, by read only up to managing.
See AccessBeveiliging.doc for the users and their passwords.
The zip file contains the necessary mdw-file and amongst others a batch file and a shortcut to open the database with the correct workgroup file.
MDE.mde 1.3 0.6   In a MDE-database, the design of forms, reports and modules no longer can be opened.
ReadOnly.mdb 1.3 0.6   This is the simplest form of security: the RO attribute of Windows/DOS.
Replicatie.mdb
RGlobal1.mdb
2.6
2.1
10.1   By replication, two or more users can work in separate versions of a database. By synchronization, the replica set is updated.
The zip file contains 19 files: global, local, anonymus and partial replicas plus a little documentation.
Splitsen.mdb
Splitsen_be.mdb
1.4
0.5
1.8   In a split database, the tables are stored in a so called back-end.
The zip file contains both.
Wachtwoord.mdb 1.3 0.6   The admission to a database can slimply be arranged by a password. It doesn't give admission by object.
See Access Beveiliging.doc for the password.
KleineDemos.mdb 3.8 1.3   KleineDemos is one big, pleasant 'mess'. Keep the <Shift> down while opening to go backstage.
In fact there are 3.5db's in this package: persons with hobbies, a counter registration, an administration for guided tours and some small fry with fantastic forms. Too much to mention here.
Also the 'operation' may be seen: a switchboard, four layers deep with ten (10) buttons and an added command.
MacroLibrary.mda 0.5 0.0   My first Access add-in!
In my permanent struggle to document databases, I had the need of VBA-routines that
a) would capture the database (the names and properties of tables, queries and fields) in tables and
b) would be available in any database.
The result goes herewith. The file must be located in "Documents and Settings\UserName\Application Data\Microsoft\AddIns".
The commands appear in the menu Tools below Add-Ins.
Word
File Mb Zipped Pgs Comments
Word in vogelvlucht.doc
Word in vogelvlucht.docx
0.2
11.6
   
147
As long as there is no PPt presentation available for Word, this document must do: Word in short (drone view).
It provides an overview of all commands in all groups on all tabs.
AfbeeldingPlaatsingAlle.docx 0.6   123 What does Word do with those images? They bounce across the page.
AlineaOpmaakAlle.docx 0.8   35 Largely known items: alignment, indentation etc. But also tricks like keep lines together. Bullets and Numbering provide many pages of slob and sadness.
Bladwijzers en Velden.doc
Bladwijzers en Velden.zip
0.6  
0.5
 
30
Dynamic documents with fields and bookmarks: facinating!
Upon opening, you will be asked to confirm the link with Outlook Contacts.
In the zip also a small database.
BouwstenenAlle.docm 0.1   23 In my Word, I found 312 buildingblocks. They include the old AutoTexts and are used for the many quick parts.
LettertypenAlle.docx 0.1   24 Extremely boring: all characters, numbers and punctuation of all fonts.
LettertypeOpmaakAlle.docm 2.8   19 Could anything be easier in a word processing program like Word than character formatting (Font)? It appears not: 21 pages bare and only examples of the possibilities. (Was this my first Word 2010 file ever?)
OpmaaksetsAlle.docx 2.9   50 The general format of styles is controlled by stylesets. In this file 30 pictures of two themes with 15 different stylesets. In one theme, I added the colors, fonts and paragraph spacings.
SectieOpmaakAlle.zip   0.8 94 The lesser-known phenomenon of 'section' inside out: margins, headers and footers, page border, ... Everything in the flesh.
I had to make five auxiliary files because some settings conflict within one file.
Sjabloon.dot
Sjabloon.docm
0.4
0.1
   
2
My Normal.dot might contain nice things. The documentation is added to the file as text.
SneltoetsenAlle.docx 0.1   13 No character typed: an overview of all (252) my (!) shortcutkey assignments.
StijlenAlle.docm 0.3   32 A big table with examples of all (269) styles for text, lists and tables. All with some formatting settings.
StijlenOpmaakAlle.docx
StijlenOpmaakAlleVervolg.docx
0.3
0.0
  8
10
Styles are the hub for a firm, consistent and manageable layout of a document. Everyone (?) knows about the heading styles. But who knows that styles have a weight? Styles inside out!
Tabellen.doc
Tabellen.docx
0.6
0.3
   
18
This example file is hardly suitable for publucatiation. Much remains to be done, it is truly 2003 and it is a hotchpotch of English and Dutch. Yet, because so many nice things can be done with tables: Tabellen.doc.
Invalpool.doc 0.4     At one time created for a school board. Supply teacher can use this document to state their availability and qualifications. The documents data can be read by Invalpool.xls.
PowerPoint
File Mb Zipped Slides Comments
PowerPoint.ppt 20.4 12.3 1,150 This file can also be found on the page Presentations. It demonstrates about all possibilities of this program.
MacroLibrary.ppt 0.0 0.0 1 This file contains usefull/-less macros e.g. Dubbelscroll to scroll all windows and also a refined search command. These commands exist in Word and Excel but not in PPt.
pptEventFvZ.ppa 0,0 0,0 1 This add-in shows how to 'catch' events in PPt.