Skip to content

Workshop: Using Excel to create vocabulary lists for tablets and smartphones

Artificial intelligence enables machine transcription to be permanently improved. On YouTube, the (personal) error rate is less than one percent. In addition, there is the interesting option that the subtitles can be translated into 133 languages at present. To use this option for better understanding of foreign-language content, one only has to produce explanatory videos – the AI takes care of the rest.

Explainer videos with subtitles in almost any language that can be accessed at any time are now an ideal supplement to language teaching. The author has gained experience with students and course participants from several countries in many video conference events. This is how the video and the accompanying workbook came about as supporting material for an online course in data protection and data security.

With the help of the macro programming possibilities contained in Microsoft 365, it is possible to extract word uniques from eBooks and video transcripts in minutes and compare them with a reference vocabulary, e.g. the Goethe-Zertifikat B1 word list. In this way, you can quickly compile any subject vocabulary and word lists for language levels higher than B1.

In the table below, you can see that Source H (which is an eBook from Herdt-Verlag) contains 24,537 words. If we delete the words that occur more than once, we are left with 4502 uniques. Of the unique words, 1,454 belong to language level B1 in German as a Foreign Language (DaF) and 2,458 word unique words go beyond that.

The source KR (12 explanatory videos by Konrad Rennert) has 31,372 more words than the eBook, but it contains fewer uniques. Because the lecture is given in free speech, simpler words are chosen than in the written elaboration. The number of uniques that go beyond the B1 level is also lower with 2,429 than with the Herdt book (H) with 3,048 uniques.

This confirms the claim made by course participants with different mother tongues that a freely given lecture is easier to understand than the reference book on the same topic.

words Unique pieces Language level
Total Details Total DetailsB1 TotalB1 Details>B1 Total>B1
Source H 24,537 2,944 4,502 486 1,454 2,458 3,048
H_KR 1,558 968 590
KR 31,372 2,622 4,180 783 1,751 1,839 2,429
Total 55,909 7,124 2,237 4,887

To use the Workbook to build language skills linked in the description to the video, all you need is the free Microsoft 365 app for tablets and smartphones:

If several participants register, there will be a workshop to help those interested in developing their own specialised vocabularies. In addition to suitable internet access, this also requires the full version of Microsoft 365, which can be used to create new pivot tables and macros.

Translated from German by the DeepL translator:

Workshop: Excel zur Erstellung von Wortschatzlisten für Tablets und Smartphones nutzen