Word Banks: Keeping track of vocabulary

This week I wanted to produce something useful for both teachers and students, and after writing about Google Ngrams last week, I thought it would be a good idea to discuss word banks.

Word Banks are a great way to manage new vocabulary, but rather than using paper-based resources, have you tried using Microsoft Excel to keep track of your vocabulary?

Using flashcards, or a box full of index cards is a great resource for the whole class, but it is not as useful for self-study. Writing notes on loose leaf paper or in a notebook is great for self-study but it may become more difficult to keep track of words when there are hundreds of entries in your word bank.

Are there any advantages to keeping a word bank in Excel?

Yes, there are. For example:

  • You can use it completely offline. This is useful for classrooms with no, or unreliable connectivity.
  • You can keep track of the types of words you are learning. This will help keep your vocabulary balanced.
  • It is easy to search through old entries and sort words alphabetically.
  • The word bank is easy to distribute. You can copy and paste vocabulary between files, e-mail the file, or store it on a cloud so it can be accessed from multiple devices.
  • The file size is small and if data protection is an issue the file can be password protected.
Click on Word Bank to download the template. It is also available from the downloads page.
When you have downloaded the file, delete the example words and start adding your own!

There are three sheets in the Excel file: the ‘Word Card’ sheet, the ‘Data’ sheet, and the ‘Statistics’ sheet.

On the ‘Word Card’ you can select a word from the list, and it will automatically display your notes for that word.
Screenshot of Excel 2013
Screenshot of Word Bank made in Excel 2013
The ‘Data’ sheet is where you add your words, their synonyms and antonyms, and your notes.
Screenshot of Word Bank made in Excel 2013
Screenshot of Word Bank made in Excel 2013
The ‘Statistics’ sheet is where you can keep track of how many words you have learnt and what type of words they are.
Screenshot of Word Bank made in Excel 2013
Screenshot of Word Bank made in Excel 2013

For words that have multiple meanings, it is better to tag them differently. Study is both a noun and a verb, so you may want to enter it as Study-1 and Study-2 or annotate the word in your notes section.

The template will store 10,000 words.

For more than 10,000 words you will have to adjust the Excel formulas. I hope you find the template helpful. This word bank was a new project, if you have any problems, suggestions or comments, then please leave some feedback.

Take care!

Be aware that there may be some compatibility issues with Excel 2007, and with other spreadsheet software such as OpenOffice Calc. Adjust the formulas and customize the template as needed. If you use Numbers, you should experience no problems.   If you would like to know more about the formulas and how the word bank works, then read on.

So, you have decided to learn how the word bank works. This will help you to customize the template and build a more comprehensive word bank.

The formulas used in the word bank are: COUNTA; COUNTIF; IFERROR; SUBSTITUTE; VLOOKUP This video provides a brief tutorial about how these functions work to make this word bank.

Resize the video by clicking on the arrows in the bottom right corner. The video shows PowerPoint 2013.

Fast Forward, Pause, Rewind, and Play the video as much as you want.

If you have any questions regarding the video, feel free to leave a comment. Thanks for watching the video!

32 thoughts on “Word Banks: Keeping track of vocabulary

  1. Very helpful. I put the entire code into Google Sheets, so I can access this on my other devices. Thank you for making this, so I can keep track of my vocabulary easier!

    Liked by 1 person

    1. Hello, Teknologic, would it be okay to share my Google Sheets version on my blog? I will be linking to your word bank post; I just wanted to give my followers a Google Sheets version, too, in case they didn’t have Excel.

      Liked by 1 person

      1. Yes, I have my own customized version that I use only for myself, but I have the unmodified format that I want to share with my followers: https://docs.google.com/spreadsheets/d/1mFGr4f22MBxZEdkEhpX84haLJkN14MnKWCps1NrN-9o/edit?usp=sharing

        It has the same format and formula, so I have it so you are already credited. I think there was only one formula that didn’t work properly for the search function, so I had to get help on it. The rest is all of your formulas, though.

        I will add the additional link to my post as well. When I make the post, I will share the link with you, so you will have it for your reference.

        Thank you so much for making this, though. It’s been useful for me.

        Liked by 1 person

  2. dear thnaks for word bank. can u make such word bank if we add word excel shows its meaning so that we can make word bank. in excel sheat, ie. one column have word other has meaning. waiting for you quick reply


    1. Hi Yaser, It’s been a while since I last looked at the template but I am sure that there is a column for you to type the meaning. The Word Bank isn’t a dictionary though, there are no definitions for words available unless you create them. Thanks for reading! T


      1. Thnks for ur reply. But i am looking for other way around. I mean excel column work as dictionay for me as i am teacher and i fiund much problematic to get meanings of full subject . It would b great help if excel do it and make vocabilary data having wrds meaning automatically. Rather to put myself. I hope u understand me. It meaning col can be simplified by selecting one meaning of specific word and enter in that place so go on for rest of words in col. Waiting plz help


      2. Sorry Yaser, doing that is extremely complicated. You are basically asking me to write a technical dictionary in Excel where the definition appears automatically without being written by you.

        However, the formula I used in the Word Bank requires you to write a definition first. It would be a new and extremely difficult project and at the moment I don’t have the time to take it on.

        If you want definitions to appear when you type a word I would suggest you use Microsoft’s bilingual dictionary tool.


  3. Hi there,
    Great word bank. I’m having trouble utilizing it with Arabic vocabulary words because the letters are not connecting properly. Do you know a way to correct this?


Leave a Reply

Fill in your details below or click an icon to log in:

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 )

Connecting to %s