Study Idea – Create Flashcards in Excel from Word Lists

Previously, I showed you how to create flashcards in Excel:

https://duolinguist.wordpress.com/2014/08/13/excel-flash-cards-1000-expressions/

But these flashcards were not random. You merely scrolled up and down to see the data entries.

There is an easy way to take a sheet that has a column of words/phrases, next to another column of translated words/phrases, and add a sheet that randomly chooses from either column, displaying both the fetched cell and the adjacent cell. You will be using the RANDBET, MAX and VLOOKUP functions. Let me walk you through the simple process so you can get a feel for it, and then you can take any of the word lists I have on this blog (category or tag: Word Lists), and go nuts with them.

Step one: copy and paste the simple list below, to Excel.

1 a has, to
2 abaissé lowered
3 abandonne leaves
4 abandonné abandoned
5 abandonner give up
6 abondamment profusely
7 abondante abundant
8 abonnement subscription
9 abonnés subscribers
10 abordable affordable
11 aborde addresses
12 aborder address
13 abouti resulted
14 aboutir lead
15 aboutit results
16 abrite home
17 absence absence
18 absolu absolute
19 absolue top
20 absolument absolutely

Step 1.1: If you have a list that looks like columns B and C above, but no numbers, you will need to add the sequential numbers to column A, by inserting a column as column A, placing the number 1 in cell A1, and then highlight A1 and the cells below it, then choosing “Fill”, “Series”, “Step Value”=1, and they will magically appear. If you need more detailed instructions on how to do this, go here:

https://support.office.com/en-us/article/Fill-data-automatically-in-worksheet-cells-71eeb2f2-d689-459b-be7e-614f03e69012?ui=en-US&rs=en-US&ad=US

Step 2: The sheet holding the above three columns is given a name like…Flashcard. We’ll need this because we will be inserting another worksheet that will work with the cells above and refer to the sheet location as “Flashcard!”.

Step 3: In cell A1 of the new worksheet (the new worksheet’s name does not matter since it is not called out in any formulas), insert the formula:

=VLOOKUP(C1, Flashcard!A:C, 2, FALSE)

Step 4: In cell A2 of the same worksheet insert the formula:

=VLOOKUP(C1, Flashcard!A:C, 3, FALSE)

Step 5: In cell C1 of the same worksheet insert the formula:

=RANDBETWEEN(1,MAX(Flashcard!A:A))

Step 6: You are done, but not really, because you should customize the font, color, size, etc. of the A1 and A2 cells, to make them appear like flashcards. In order to activate another flashcard, simply hit your F9 key and the formulas will activate and produce another data fetch.

Personally, I made the French word shown in A1, a large cell, and the English word fetched and displayed in A2 very small and a very, very light gray color, so that I would not see it right away unless I looked for it, allowing me to think of it first.

If you use Google spreadsheets and wish to make Flashcards, this blog post is what you’re looking for:

http://www.makeuseof.com/tag/make-flashcards-google-docs-spreadsheets-web-iosandroid/

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s