With the myriad of calculations you can perform in Excel, figuring out which functions to use can be a bit of a mystery. Finding the correct function in a localized version of Excel can be even more challenging. Sometimes functions have cryptic names. Other times they are closely named and perform similar tasks yet are not identical.
When Microsoft chose to localize Excel functions to make them intuitively available to non-English speakers, it came with its own complexities. You will often know the US name of the function you need, but how do you go about finding the corresponding localized one? The answer until today was that it is difficult, requiring a fair amount of searching.
The more specialized a function becomes, the more difficult it is to supply an adequate translation, and the more difficult it can be for the user of localized Excel to figure out the localized name.
It’s also challenging to keep a consistent taxonomy for related functions, since Excel’s 800 functions were added incrementally over time and localized out of context of each other.
At some stage I did an online course in Excel. I had Danish Excel installed, and as a bilingual user it would have been easy enough for me to use the English version, but since I am in the Office localization group, running the Danish version of Office helps me get an idea of the localized product.
The course started with Functions and finding the right names. I struggled with finding the right translated functions, then the course became even more difficult, with full formulas. An English Excel user could have copy pasted, but I had to look up every Function in the formula, find the Danish equivalent, remember to convert the delimiters. And so on.
Eventually I switched to US English Excel and the course became much easier to handle. I was curious to understand if I was the only one who had this problem, so I talked to colleagues who, like myself, spoke English as their second language. They often used US versions of Excel for the exact same reason.
After these eye-opening experiences I decided to see if it was possible to do something smart in this space.
I am not a developer, but if I could scope the problem well, show that there was a real issue, and come up with a compelling idea, I might be able to find a developer who would be interested in creating a solution.
I realized that we could create a comprehensive solution by mining all the translated functions from Microsoft’s translation databases and exposing them in a simple add-in. That is how Functions Translator was born.
The idea
I created a spec that effectively outlined what I wanted to accomplish. In the end I decided on the below goals:
- Enables users who know the US functions to become productive in localized versions of Excel
- Enables users to easily translate full formulas they find on the web to their native language
- Supports all localized languages and functions of Excel, 80 languages, 800 functions
- Provide an efficient way to search for any part of a functions name, in both languages selected
- Shows a scrollable and categorized list of US functions and the corresponding localized functions
- Enables the users to give feedback to Microsoft on the quality of the translations of the functions, good and bad
- Enables us to support users that are struggling with finding the wanted function in a localized language
- Supports Excel 2013 and 2016 for Desktop, Excel Online, and the latest version of Excel for Mac and iPad
Get the project details and download link
Creating an integrated solution
Over the last 20 months we have pursued this solution. The Functions Translator team consists of Martin Ørsted (myself), and Vadym Khondar. I have a background in localization and have experienced the problems first-hand. Vadym is a software developer working on the Office store. We joined forces in the summer of 2016 during the global Hackathon to solve this problem.
It was during the Hackathon that I connected with Vadym and we took this idea, forming the Functions Translator team.
It was a great chance for us to work on something that we were passionate about and it was outside our normal work scope. At Microsoft we have hackathons on a regular basis, and everybody can take part. You do not have to be a developer but can instead join to raise interest in an idea or take part in other ways. It’s about creating solutions to problems that you may not be able to address in your normal workday. It’s also fun to do something else occasionally and collaborate with different people.
The outcome of the Hackathon proved that it was possible to create a Microsoft Excel add-in that could solve the pain points users were encountering. This was exciting because the solutions available so far were limited in reach and were not in any way integrated with Excel. We set out to create a full solution as an Excel add-in that would cover all Excel functions for all localized languages.
As we progressed we realized that the technology we had chosen for our solution would also work for Mac and iPad, not to mention Excel Online and Excel 2013.We expanded the project to cover Excel Online, Excel 2013 and Excel 2016 for Desktop, and the latest versions of Excel for Mac and iPad.
We realized there were some differences in Functions between the versions and platforms. We therefore created dictionaries for every platform and version to ensure the returned function names were correct in all situations.
Moving to Formulas and meeting the core team
Translating the functions was a fairly quick solution. But we were ambitious; we wanted to translate the formulas as well. We figured that since we had all the function names for all languages sitting in dictionaries this should be easy. We were wrong! As we worked on this we realized that Excel formulas can be very complex. One issue we ran into was parsing and potential conversion of delimiters. For US English, the delimiter between fields is a comma, but for example in Danish it is a semicolon. Since somebody can use Danish Excel with US regional settings, the solution got a bit complicated.
Serendipitously, we soon found contacts in the Excel core team. Steve Kraynak and Jakob Nielsen saw our first prototype, and they liked the potential. They installed it, tested it, and started giving feedback on both layout and functionality.
In addition, the Excel core team asked, “wouldn’t it be cool if our Functions Translator could read directly from a cell when the user clicked on it and provide the translated formula?” We didn’t believe that was possible, but the core team directed us to the correct documentation, and we built that into the functionality as well.
“Wouldn’t it be cool if our Functions Translator could read directly from a cell when the user clicked on it and provide the translated formula?”
Get the project details and download link
Going to The Garage
The Microsoft Garage helps people ship software outside of the big dev teams. As you can imagine the large dev and product group teams have systemic access to everything they need to ship in the form of advice, review and feedback. Since we were doing this as a hackathon project, we needed somebody to help us. That is where The Garage team came in. Their job is to help people like us to ship products and experimental solutions.
The first part of this was for us to find somebody to champion our little add-in. Luckily, Steve and Jakob offered to sponsor. After that we created a pitch video. In terms of videos it wouldn’t get any stars on YouTube. But it did the job, The Garage team liked our pitch, and we were officially in business.
Meeting the professional developers and designers
When we had progressed our add-in to what we thought to be a satisfactory level, we landed ourselves an introduction to the Excel core team in Redmond. This was a great experience. They felt that we were tackling a very relevant problem and that we had a good solution in our hands. They did a design review of our add-in, resulting in a 40 slide PowerPoint presentation that radically changed a lot of what we had already done, and radically interrupted our timeline.
We did all the work needed based on the feedback received. There were a few areas where we decided to stick to our guns, but they were few and based on our expectation of how the tool would be used.
Meeting the designers was a humbling experience. We had created what we thought was a nice icon and an attractive UI. Talking with the professional designer was an eye opener of the Less-is-more kind of style. For example, our icon was too complex and colourful. Same for the various frames we had created. Simple style, simple colours, fit better with Excel, and so on. We were sold on the design team’s suggestions and made the appropriate changes, happy with our progress.
Meeting the professional Excel users
As it turned out, the Excel core team had more good ideas. They suggested that we send the application out in beta testing with the Excel MVP community. We liked the idea.
Quite quickly we started receiving feedback from the MVPs. Lots of positive feedback and encouragement, and a few good bugs. This was where we learned about the complexity of Excel formulas. We went through all the bugs, fixing each one. Overall, the experience of working with the Excel core team, their designers, and the Excel MVPs has been one of the biggest positive surprises of the project. There is an enthusiasm that is frankly infectious. They really care about the product, about our solution, and they want to see it through.
At this point in time we were happy we had a very solid solution that we should be able to release.
Localization
Our solution is highly targeted towards users of localized versions of Excel. For that reason, we decided that it might make sense to localize the solution. Localization in this context is about the user interface, and unrelated to the actual functionality of translating formulas and functions. We asked for permission to send the Functions Translator resources out for localization and got it approved. A few weeks later we had the Functions Translator add-in localized for 13 languages. We still had a bit of a problem, because while the biggest languages were now supported we found that from our perspective another two important languages needed to be added, Danish and Ukrainian. (We are respectively from Denmark and Ukraine). Strangely enough those languages had not made the initial list.
The result is that besides supporting US English we also support Danish, German, Spanish, French, Italian, Japanese, Korean, Dutch, Portuguese Brazilian, Russian, Swedish, Turkish, Chinese Traditional and Chinese Complex Script.
We tested the various languages and more bug fixing commenced.
The fine print that nobody ever reads
You’ve probably had experience with signing contracts without reading the fine print. Life is too short for reading that stuff, it’s a slow read with tons of strange terms. It turns out that when you want to release an add-in, you really need to care about the fine print, and not only do you have to read it, you have to supply it. We went through all the reviews for security, privacy, accessibility, and more.
Though these steps took some time to complete, they were a crucial part of the shipping process.
The big reveal
As of the time of writing this we are at the unveiling stage for Functions Translator. Fixing a few glitches and trying to create a bit of awareness around the add-in. We have spent 20 months working on this. It would be incredibly fulfilling to see Functions Translator make a difference in Excel users’ experience worldwide.
It has been great fun to work on as a project, and we hope that users will find it helpful. We are looking forward to seeing the translator in use, and we hope it will help people solve problems and become more productive in localized versions of Excel. If we succeed in that, it will all have been worth the effort.
