Handling highlighted Excel cells in Studio

Last week I had to translate a long medical questionnaire. The source text was a single column in an Excel file and I was instructed to translate cells with an orange fill, which were the questions addressed to the patient. Processing the file in Studio turned out to be slightly trickier than I expected, so I’m sharing my workaround here.

At the end of this post, I’ll explain how I used AutoHotkey to save a few thousand keystrokes in this project!

I can’t share the file itself for confidentiality reasons, so I mocked up a similar one in English to show what it looked like:

Problems:

  • Studio doesn’t show this orange formatting, even in the WYSIWYG display, so I couldn’t tell which cells I had to translate. The file was huge, with 9500 rows (67,000 words) and I only had to translate about 1500 rows, so manually skipping the not-for-translation cells wasn’t an option.
  • You can customise Studio to exclude a specific font colour, but not highlighted text or colour-filled cells. (If you ever want to filter by font colour, go to Project Settings> File Types> MS Excel 2007-2016>Exclusions)

Solution:

In Excel, I set up a filter to display only the cells with orange fill. To do this, I right clicked one of the orange cells and selected Filter > Filter by Selected Cell’s Color

I opened the file in Studio. Only the orange cells were displayed:

After finishing the translation and saving the file, I cleared the filter in Excel by clicking the filter symbol at the top of the translated column and selecting Clear Filter.

AutoHotkey trick

You might have noticed that almost all the segments I translated in this project ended with a question mark. In fact, the questionnaire had well over 1000 questions, which meant that at the end of most segments, I had to press shift, question mark, control and enter (to confirm the segment and move to the next). Four keystrokes.

To transform these four keystrokes into a single one for this project, I remapped the ñ key with AutoHotkey (AHK):*

ñ::
Send, ?{Control Down}{Enter}{Control Up}
return

I then added a first line to tell AHK to deploy this code in Studio only, so the ñ key was fully functional in other programs.

#IfWinActive ahk_exe SDLTradosStudio.exe

AutoHotkey is a free program that performs repetitive tasks in any Windows application through customised scripts. To learn more about AHK and Studio, check out the SDL Community AutoHotkey forum.

By customising the display and reducing keystrokes, I had more time to focus on the translation itself. I hope these two tricks inspire you to look for similar workarounds.

* Just to clarify: I used the ñ key because it’s easy to access on a Spanish keyboard and isn’t needed in an into English translation. However, any key can be chosen as the hotkey.
This entry was posted in 2. Beyond the Basics, SDL Trados Studio and tagged , , . Bookmark the permalink.

9 Responses to Handling highlighted Excel cells in Studio

  1. Ben Senior says:

    Hi Emma,
    Two great tips. I’ve used “Transtools” in the past for this kind of work. It’s free software and can be used in Excel, Word and AutoCAD. I found it extremely useful in AutoCAD when I was translating a lot of DWG and DXF files.
    Best regards,
    Ben

    • Ben, you’re right, Transtools is great for processing highlighted text in Word (and many other things too). I didn’t know it had a similar feature in Excel, but in this particular case the Excel filter function worked just fine.

  2. Nice! I get some Excel files when I do financial translations, but have not come across having to do only highlighted cells. Thanks! I’m sharing! 🙂

  3. Paz Gómez Polledo says:

    Very interesting! Thanks, Emma!

  4. Mhairi says:

    You are a genius Emma, what a great workaround! Thank you for your invaluable help not only in this case but also in general.

  5. Heather Hamilton says:

    That was timely, thank you! One just came in today! However, when creating the translated doc, I couldn’t find where to undo the filter. Never mind, copying the translated column and pasting into the source doc worked beautifully, filtered and unfiltered cells showed up perfectly.

    • Glad you could test it in real life, Heather! The “Clear filter” should be visible when you right click in that column, and you should also see it up in the ribbon. Anyway, it’s good that you got it sorted in the end.

      • Heather Hamilton says:

        Nope, that’s just what I was looking for, it all appeared in grey. Anyone else who has the same problem can be comforted by the easy solution.

Comments welcome!

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 )

Google photo

You are commenting using your Google 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.