Enhance Your Excel Glossaries with a Dynamic Search Bar

Even though there are several tools on the market that allow interpreters to store and retrieve terminology quickly and safely, many of us still rely on Excel to create and share glossaries with our colleagues. And many of us still use a simple search via Ctrl + F to find our terms.

This is not only inefficient, as it doesn't display all the relevant terms grouped together, but rather jumps from hit to hit, making it impractical for the real-time searches required by interpreters. To achieve this, filtering is the best solution. However, setting up and using a filter while interpreting is not ideal either. A dynamic search box, on the other hand, would mimic the behavior we get from dedicated terminology management tools. 

A dynamic search box enhances glossary usability

The purpose of this post is not to explain how to create such a search box (that will be the subject of a separate post, when I can find a bit more time), but rather to share a ready-to-use Excel glossary file where I have added a dynamic search box. This file can be used as a template to create your own glossaries with dynamic search capabilities.

To use it, follow the steps below.

1. Download the Excel file, save it and open it. You may need to click Enable editing if you see a message appear at the top of the file. It may also be a good idea to use "Save As" and make a copy of the file.

2. Replace the sample terms in the Glossary tab with your own terms. You can type or paste your terms. 

3. To use the glossary, go to the Search tab and start typing your desired term in the Search box (indicated by the magnifying glass). You will see all the terms that match the search string immediately listed under the search box.

The file is set up for English and Spanish. If you'd like to change the language labels, you will need to:

1. Edit the column titles in the Glossary and Search tabs.

2. In the Search tab, click cell A5 and look at the formula bar. You will see the formula that makes all the magic happen. Replace "English" and "Spanish" with the appropriate language names. Make sure they match exactly the names that you gave to your columns in the previous step. Be very careful not to change anything else.

If you'd like to see all of this in action, here's a short video.

And that's it. Time to convert your Excel terminology files into enhanced dynamic glossaries!

An Excel-Based Terminology Provider for SDL Trados Studio: The TermExcelerator

With the release of SDL Trados Studio 2015 SR2, there is an exciting new addition: the TermExcelerator, available from the SDL Open Exchange.

This new terminology provider is a plug-in that allows the user to use an Excel glossary as a termbase in Studio. It couldn't be easier or more convenient. Once the Excel termbase has been added to Studio, terms can be added, edited or deleted either in Studio or in Excel, making termbase maintenance a breeze.

Let's have a look at how it works.

The basics
The TermExcelerator works only with one target language, so it's not intended for multilingual termbases. In addition to the source and target column, the Excel file can have a third column called "Approved column" in the provider. This can be used for notes and comments or any other relevant information.

Adding an Excel glossary directly to Studio
An Excel termbase is added the same way as a Multiterm termbase. After installing the plug-in, Studio will offer an option to add an "Excel-based Terminology provider" in addition to the usual Multiterm termbases.

The screenshot below shows the Project Settings route, used for adding termbases to existing projects. Termbases can also be added via Options (the termbase will be added to all new projects created thereafter and to single-document workflows) and when creating a new project.

Notice the redesigned Termbase pane above, which now resembles the layout of the Translation Memory pane.

Selecting to add an Excel-based terminology provider takes us to the Settings window.

For reference, here's the glossary I've added to this example.

After clicking Submit, the Excel glossary has now been added to my project.

When I close this window with OK and go back to the Editor view, I can see that the termbase is now active.

Once the termbase has been loaded, term recognition and search work as usual.

Editing and maintaining the Excel termbase

The TermExcelerator offers some interesting features: in addition to the regular Add New Term and Quick Add New Term functionality from within the Studio Editor, we can delete and add entries from the Termbase Viewer, and sync changes between the Excel file and the termbase in Studio.

Adding Terms

To add a new term to our Excel termbase from the Studio Editor view, simply select the source and target terms, right click and select Add New Term or Quick Add New Term. Either option will automatically save the newly added term to the Excel file.

Tip: To enter terms quickly via the keyboard, set a shortcut in Options - Keyboard Shortcuts - Editor 

for the Quick Add New Term feature.

The new term has been added to the Excel termbase.

We can also add a term by clicking the Add button in the termbase viewer and entering the new term manually.

One of the most convenient features of the TermExcelerator is that we can now quickly add multiple terms to the Excel file and, by clicking Sync in the Termbase Viewer, they will become immediately available in Studio. (Note: The Excel file must be saved and closed before syncing).

Here, I've added the terms in rows 9-13 in Excel.

When I click Sync in the Termbase Viewer, the newly-added terms become available in Studio.

Deleting and editing terms
The steps to delete and edit terms are pretty straightforward. The Delete button in the Termbase Viewer is used to remove the selected entry, while selecting and double-clicking an entry allows us to edit its contents. The Save Entry button should be used to save any changes after editing. Of course, entries can also be deleted or edited in Excel, and after Syncing, the updated termbase will be immediately available in Studio.

Data integrity
The usual warnings for data integrity apply, and it's always a good idea to make a copy of the Excel file just in case something goes wrong.

Final thoughts
Although I don't see myself abandoning my Multiterm termbases any time soon, I appreciate the benefits and convenience of this great new tool, which has quickly become an essential component of my toolbox.

For more details, don't miss Paul Filkin's post on the TermExcelerator: Committing the cardinal sin... 

The New Bilingual Excel File Type in Studio 2015

With the new Bilingual Excel file type in Studio 2015, we can extract text from a specific column for translation and have Studio insert the translated text into a different column in the target file. No more cutting and pasting!

Note: File type and filter are sometimes used interchangeably, so Bilingual Excel file type and Bilingual Excel filter refer to the same thing in this post.

What It Does

Here's a typical use scenario.

The regular Excel file type produces the following Studio file, with all the content extracted for translation.

The resulting target file of the above would be an exact copy of the source file, but with all the text replaced by the translations.

With the new Bilingual Excel file type, however, only the text I need will be extracted for translation.

And the resulting target file will have the translations inserted in Column F, while the rest of the text remains intact, as shown below.

How to Use It

First things first: For any projects created with a pre-2015 version of Studio, the Bilingual Excel file type will need to be added manually by clicking on the "Additional installed File Types exist" link, and checking the new file type checkbox, as shown here.

Are the files in the correct order?

A key thing to keep in mind to make this work is that Studio will process your Excel file with whatever filter it finds first, so if the Bilingual Excel file type comes before the regular Excel file type in the list, the Bilingual Excel file type will be used to process your file, but if it comes after the regular Excel file type, then the regular Excel filter will be used. So, to make sure Studio uses the Bilingual Excel filter, you can either make sure it comes before the regular Excel filter in the list or you can disable the regular Excel file type.

Go to File - Options - File Types

Important note: There are two places where you can make this change.

1. If you make it by going to Options - File Types (as shown in the screenshots above), this change will affect future projects and single-document flows, but not existing projects.

2. If you make it by going to Project Settings - File Types, the change will only affect your active project.

This gives you the ability to enable the Bilingual Excel file type only for a specific project without changing how Studio handles Excel files in general.

Once the file type is enabled, it's time to look at the settings. For my example above, I set the source column to E and the translation column to F and chose to confirm any existing translations that may already be in column F.

After clicking OK, you would process your file as usual. Keep in mind that any changes made to Project Settings or Options will be applied to files that are processed after the changes, not to existing files, so if you've made this change after a file had been added to a project and prepared, you would need to remove the file, add it and prepare it again for the new settings to go into effect.


Limitations
Note: This section has been edited to clarify how cell and text-level formatting are handled.
A limitation of this first version of the Bilingual Excel filter is that text-level formatting (e.g. bold, italics, font, color applied to individual words or phrases) is not recognized. Cell formatting, however, can be preserved by unchecking the "Preserve Target Style" checkbox in the file type settings, as shown below. Note that in this example, column C has been marked as containing comments.

This is the source file to be used in this example. Rows 2 and 5 in Column A have cell formatting applied to them. For the rest of the cells, formatting was applied at text level.

Here's the above file, processed with the bilingual file type and with the settings shown earlier. Note that there are no formatting tags in the source column.

Now let's have a look at the resulting target file.

So, as we can see, formatting that applies to the entire cell is transferred over to the target column, but since we have no tags to help us indicate the formatting of individual words or word groups, text-level formatting cannot be preserved.

Let's compare now how the same text looks when processed through the regular Excel filter.

Here we get full control over text formatting tags, so it's easy to duplicate source formatting.

Another limitation of the bilingual file type is that there are no embedded content settings for this file type. For most regular jobs, however, this may not even be an issue.

In conclusion, choosing the right Excel file type to use will depend on the specific use case at hand. While heavily-formatted files may still call for the regular Excel filter, there will be other files with little or no formatting that will be perfect for the new Bilingual Excel filter.