How to create a bilingual glossary of terms in Excel?

For most translators, a bilingual glossary of terms plays a vital role in their work. As translators are not experts in all the fields they are doing translation, it is really hard work to remember a huge volume of unfamiliar terms that should be properly and exactly used. For those who are supported by CAT tools (Trados, Wordfast...), it may be no problem at all as the glossary management tool may help them solve the problem. However, for the translators who cannot afford such CAT tools or such glossary management tools are not available in their CAT tools and then an electronic bilingual glossary of terms is opined to be so helpful. From my own experience, creating a bilingual glossary in Excel is the most effective way. Guidance provided herein is based on Ms Excel 2007. You may also do the same things with other Excel versions.

There are several reasons for creating glossary in Excel:

Easy to look up terms, phrases;

Easy to manage (add, edit, delete...) a huge volume of terms in various fields just in one Excel workbook;

Relatively small file size;

Possible to be imported as TMs for CAT Tools.

2. Designing a Glossary Template:

Creating Index Sheet:

As a translator, you may work with various fields other than your expertise. It is important that you create a list of your fields first. You may arrange them in alphabetical order or in the most convenient way for you. To do so, open the Excel spreadsheet, list and number all your fields of work in an Index on the first sheet. It’s advisable to reserve some spaces in the Index as you may need to import terms of other fields in the future. The name of these fields will be linked to the sheet containing the terms.

Creating Template Sheet:

In the second sheet, create a five-column table (or at your own customization) including columns named order, source language (SL), target language (TL), source of terms, remarks. Adjust the column width and format the table. Name the sheet in number i.e “1” so as to keep it short.
And it is also essential to give the sheet’s title (same as in the Index) which should be in capital and on the very second row of the sheet. To keep the title visible while the rest of worksheet scrolls, do as follows:

Select the row just beneath the row containing the title;

Go to View → Freeze Panel (in the Toolbar);

Select Freeze Panel.

Now, you have a template sheet for inputting your terms. Just duplicate the sheet by right click on the selected sheet, select “Move or Copy”; tick on “Create a copy”. You can create as many sheets as you want. Remember to renumber the sheets and rename the sheet titles.

Creating Hyperlink:

+ Link the Index to sheets:

Select the name of the field; then go to Toolbar → Insert → Hyperlink or just press “Ctrl + K”; choose the “Bookmark” and select the desired sheet in the “Cell Reference" that you want to link.

+ Link the sheet to the Index:

Select each sheet title; Go to Toolb
ar → Insert → Hyperlink or just press “Ctrl + K”; choose the “Bookmark” and select the Index in the “Cell Reference” to link.

When the link is done, its texts turn into another color. Click on the text to test the link.

Now the Glossary Template is ready to use.

For your ease, please find my template in the following links:

Excel 2003: http://www.mediafire.com/?4zls88sg9cgbsb8

Excel 2007: http://www.mediafire.com/?wi89r7cvr5oqaww

Alternative link: https://sites.google.com/site/linhtranslate/glossaries?pli=1

3. Working with the Glossary:

Recording Terms:

Whenever you have any new term, input it in the relevant sheet. You may note the source of terms and its translation to ensure its reliability when you use it in future.
If there are any notes on the terms (usage, thesaurus, context...), note it down on “Remarks” column.

Sorting Terms:

The input Terms can be sorted in the alphabetical order by simply selecting the area in the sheet you want to sort. Remember to select all the relevant columns. Then go to Toolbar → Data → Sort.

Looking up Terms:

Open the spreadsheet, press “Ctrl+F” and enter the terms you are looking up in the “Find what” field. Click the “Options” button to add search criteria. You may choose to look up your terms within one sheet or the entire workbook, by row or by column. Then press “Find next” or “Find All” button to search your terms. Once the terms have been input, they will surely turn up upon searching.

Reducing file size:

Press “F12”, click Tools → Compress Pictures → Options, and then choose “Email (96 ppi) – minimizing document size for sharing”. Choose “Excel workbook (*.xlsx)”; and save the file in a different name. The extension “.xlsx” helps minimize the file size. This extension only works with Ms Excel 2007 or higher, remember to change the extension to “.xls” when you intend to use the workbook in a PC not installing Ms Excel 2007 or higher. Of course, the same file with “.xls” extension is larger than the one with “.xlsx”.

Protecting the Glossary:

To create a password for the workbook, go to “Office Button” (in the upper left corner of the Screen); choose Prepare → Encrypt Document. Enter your password in the field. The password should contain both words and numbers.
You can also protect certain sheets or workbook elements from changing, moving and deleting. From Toolbar, select Review → Protect Sheet/ Workbook → Protect Structure and Windows and enter the password (optional).

Naming the file:

As the Glossary will certainly be updated a lot of times, please remember to change the file name whenever you transfer it to other computer(s) or archive it. It is advisable to change the file name by changing the version number e.g v1, v2,..., v12... or by changing the date of the last inputting or revision. Failing to do so, you may lose the terms that you have input as you cannot remember which the latest updated version is if several files located in different locations have the same file name.

These are all the basic steps and some tips that help to create a bilingual glossary of terms in Excel. Hoping that, to some extent, the guidance will help you create your own data for your translation work.

If you have any queries, do not hesitate to contact me via email.