Excel Interactive Reports and Translation Features

Overview

• Recent Excel update allows creating reports in any language.
• Discussion on interactive reports, performance optimization, offline functionality, and a double X lookup technique.

New Translate Function

• Function Syntax: =TRANSLATE(text, source_language, target_language)
  • Text: Reference to text you want to translate (e.g., B4).
  • Source Language: Language of the original text (e.g., "EN").
  • Target Language: Language you want to translate to (e.g., "ES").
• Note: The translate function is currently in beta.

Detect Language Function

• Newly released alongside the translate function to identify the language of a given text.

Setting Up the Report

• Current Example: A report with data in multiple languages (130+).
• Source Data Requirements:
  • Any text columns can be translated (e.g., source and month columns).

Steps to Create the Report

1. Input Target Language:
   • Enter the target language code (e.g., "ES" for Spanish).
2. Using Translate Function:
   • Apply the translate function to source and month columns.
   • Rename columns to indicate translation (e.g., Source X, Month X).
3. Dynamic Language Selection:
   • Create a dropdown for language selection.
   • Use XLOOKUP to fetch language codes.
4. Creating Summary Report:
   • Utilize the new Pivot By function or a Pivot Table.
   • Format the resulting summary report.

Enhancing User Interaction

• Dropdown Creation:
  • Use Data Validation to create a language list dropdown.
• Chart Creation:
  • Insert charts based on the pivot table results, ensuring titles are dynamic and translated.
  • Use the PROPER function for title formatting.

Offline Functionality and Efficiency

• Translation Table Creation:
  • Create a separate translation table using Power Query to minimize repeated translations.
  • Unpivot data and remove duplicates to create a unique list of text values.
• Using XLOOKUP for Translations:
  • Replace direct translate functions with XLOOKUPs referencing the translation table.
  • This reduces resource usage and speeds up performance.

Updating the Translation Table

• Refresh the translation table with new data to automatically include new unique values.
• Only languages in the translation table can be used; if a new language is required, it needs to be added to the table.

Additional Functionality

• Manual Translation:
  • If the translate function is unavailable, use external services like Bing or Google Translate to populate the translation table manually.
• Detect Language Usage:
  • Use the detect language function to identify the language of unknown text.
  • Combine with the translate function to find and display the language code.

Conclusion

• The new translate and detect language functions greatly enhance Excel's reporting capabilities.
• Engage with viewers by asking them about their preferred features.
• Encouragement to like, subscribe, and check out additional resources.