How I Study Vocabulary (Spreadsheets)

[sparrow]

I've tried flashcards, straight-up vocab lists, programs like Anki, and so on. However, the method that I feel gives me the best control is using a spreadsheet.

 

This is a sample of my vocabulary spreadsheet. I'm including just 100 words because my vocabulary list uses the Routledge frequency dictionary, and I don't want to pirate their stuff.

 

For clarity, sheets within the spreadsheet will be written in CAPS.

 

If I want to study new words from Pinyin to Hanzi...

1. In the Pinyin to Hanzi column (P2H), I write 0's for new words.
2. ALL VOCABULARY > Select All > Data > Sort
3. Sort with respect to Pinyin to Hanzi and with respect to the Shuffle column, which has random decimals in it. (The decimals are rounded to one decimal, which makes searching by word number easier, otherwise searches would turn up entries from the shuffle column.)
4. This will sort words by their difficulty rating, which is a number I assign when testing myself. Then the words are shuffled so that all the most difficult are shuffled but appear together, the medium words are shuffled but appear together, and so on.
5. In the PINYIN TO HANZI sheet, there are the first 100 cells from the ALL VOCABULARY sheet. I keep my window small enough that only one row shows at a time.
6. I then go down the page testing myself. I mark hard words as a 1, medium words as 2, and don't mark easy words.
7. The difficulty column shows what my original rating was and is color-coded by difficulty. This way, if I just want to study hard words, I will know from the color coding when I've run into the medium or easy words.
8. The last column copies my the new difficulty ratings that I assign while testing myself. However, if I don't write a difficulty number down, it automatically inputs a 3 for easy. This just saves some time so I don't have to write 3s as I usually remember stuff.
9. Once I finish testing myself, I copy the difficulty numbers from the last column, then paste them to replace the zeros in ALL VOCABULARY.

If I want to test going from Hanzi to Pinyin or Hanzi to English, then I use those sheets.

 

It might sounds complicated, but anytime you explain steps for doing stuff with the computer, it looks more complicated than it is. It's really pretty easy.

 

It's also easy to extract the lists as a PFD using the last sheet. The 100 vocab words I'm currently practicing appear on the last sheet. I download this as a PDF, save it to my Dropbox folder, then upload it with my phone. I can quiz myself by hiding one line at a time just like I do with the spreadsheet.