Working in multinational organisations many of us struggle with ad-hoc translations of Word documents or Excel spreadsheets. Translations are a boring task. Usually what most of us have mastered is a simple process of copying texts from documents to Google Translate and back. If you have my VBA Time Saver AddIn you probably already have a neat VBA function for translations which you can add as a shortcut. However, on a daily basis if you often switch between languages you might want an AddIn instead. Thankfully I am introducing the Word Translator AddIn and the Excel Translator AddIn!
Word Translator AddIn: Translate text in Word
The Word Translator AddIn is very simple to use. First set the from and to languages. Now all you need to do is select any text and hit the Translate button (or CTRL+SHIFT+T shortcut)!
The AddIn is provided with a list of supported languages. Making it easy to simply select the language you want to translate from and to in Word. Hitting the translate button with result in the text being translated and replaced, however the formatting might change – so watch out!
The Undo functionality does not work when running a VBA macro in Word. A translated text can’t be undone. Undo will however work properly in other cases of course
Install Word Translator AddIn
Download the Word Translator AddIn
Download the Word Translator AddIn from here. Provide your email to get the download link:
Copy the AddIn to your Word Startup folder
Copy the dotm template document to your Word Startup folder. In most Windows systems it should be similar to:
The Excel Translator AddIn is similarly simple to use. First set the from and to languages. Now all you need to do is select any amount of Excel ranges and hit the Translate button (or CTRL+SHIFT+T shortcut)!
The Undo functionality does not work when running a VBA macro in Excel. A translated text can’t be undone. Undo will however work properly in other cases of course
Install Excel Translator AddIn
Download the Excel Translator AddIn
Download the Excel Translator AddIn from here. Provide your email to get the download link:
Copy the AddIn to your Microsoft AddIns folder
Copy the xlam AddIn Workbook to your Microsoft AddIns folder. In most Windows systems it should be similar to:
Translations in Word and Excel can be a dull task. With the use of the above AddIns I was however able to translate my documents in 50% less time, giving me more time to check the quality of the translations and make minor language fixes.
The AddIns are powered by Google Translate functionality and require a working direct Internet Connection. If you get errors, most likely you are working from behind a proxy server and need to connect directly to the Internet.
Today on Top 10 Excel features you need to know. I am a frequent visitor to StackOverflow to see what is troubling the Excel community (and other developer/analyst communities). It is nothing incredible that many users have issues due to not being aware of some of the most significant features in Excel like PivotTables, Array Formulas, Tables and other. I have seen more than once users reaching out to use VBA macros instead of much easier PivotTables/Charts. Although, I value VBA it should also be the solution of last resort compare to “native” Excel features used more common.
I have decided therefore to list some of the top 10 features regarded often as the most significant and useful to know. So let’s start with the 10 Top Excel Features…
No. 1: PivotTables
You can’t call yourself an advanced Excel user without knowing about PivotTables! There is no other feature in Excel I being used more often and with success. Almost the first thing I always do when analyzing data is pivot the data in multiple manners and analyze the patterns/results. Pivots can process a large amount of data in a short period of time and are optimized in the Excel back-end for performance. This is definitely one of the 10 top Excel features!
PivotTables allow you to transform and analyze data in a structure manner. Just select a range of data (data in columns with headers) and select the ROWS, COLUMNS and VALUES for your Pivot Table! You can also create custom columns (based on formulas), summarize data by groups/rows/columns etc. There is almost no limit in the possibilities.
How to find it in Excel? Insert->Tables->PivotTables
Filtering and sorting your data is just as useful as using PivotTables. Excel is meant to transform and analyze data and filtering/sorting is one of the key elements. When provided with a table of data you will probably want to sort the data in a descending/ascending manner or filter out rows based on some features (values in certain columns). This is a must-know feature.
If you want your data tables to be neat and structured you need to use Excel Data Tables. What do you get when using Tables in Excel? A consistent structure and formatting of your entire data table, automated copied formulas (across columns), non-repeating column headers and more. It is always good to resort to Excel Data Tables as you will have less work managing your data table and can focus on more interesting work like data transformation/analysis.
How to find it in Excel? Insert->Tables->Table
No. 4: Conditional formatting
Analyzing/transforming data is important, but it is just as useful to be able to identify variances in a range of values using graphics like colors, bars or icons. Conditional formatting can allow you to notice patterns in data values which might not be obvious when looking at raw numbers.
How to find it in Excel? Home->Styles->Conditional Formatting
No. 5: Lookup Excel functions
Although these functions are not exactly a separate feature they are considered to be one of the most useful and most often used when analyzing data. I can’t stress how often I stumbled upon articles/posts about these functions. These functions are also frequently used by recruiters for Excel jobs.
VLOOKUP – search the first column of a range of cells, and then return a value from any cell on the same row of the range
HLOOKUP – as above but for columns instead of rows
INDEX – returns the value of an element in a table or an array, selected by provided index
MATCH – searches for a specified item in a range of cells, and then returns the relative position of that item in the range
How to use these functions?
The VLOOKUP function will return a corresponding value from another cell of the same row of a value found in the first column of the data table.
The INDEX and MATCH functions are best to be used together (see the links section below). Why? They allow you to basically achieve the same result as the LOOKUP functions HOWEVER these are more flexible. I encourage you to read the links below.
Array Formulas are one of the greatest knowledge gaps in the Excel community in my opinion. I see so often questions which can be easily answered if someone at least made an effort to learn them. Many Excel users fall into the trap of writing a lot of custom VBA just because they are not aware or are too lazy to use a neat Array Formula.
How to use Array Functions?
Go to the links section for a decent tutorial. However, the process itself is quite simple:
Create a function using an Excel range e.g. A1:A10
So easy and yet so powerful! Let’s jump into a simple example:
Excel Array Formula Example
Let’s assume we have a range of value for a certain period of time. We want to get the maximum value for dates after the 1st of March 2015. We can get this in a single Array Formula! See below.
How does it work?
'MAX ( returns all cells from A2:A8 for which B2:B8 are older than 2015-03-01)
See the logic? You can also multiply/divide/sum and do other cool thing with Array Formulas. See the tutorial link below.
All tools needed in doing basic data cleansing can be found in the Data Tools section of the Data ribbon. Working on data often? You need to know how and when to use Text to Columns, Data Validation and all the other neat tools.
Naming cells/ranges comes in handy when you repeatedly reference certain cells or arrays e.g. USD/EUR currency field, interest rate used in formulas etc. This is a nice and clean way to manage all reference to those fields and allows you to easily relocate these cells or ranges.
How to set a Defined Name to an Excel cell/range?
Click on the text field in the upper-left with the cell/range reference
Type in Defined Name for this cell/range – it must not have whitespaces
Now when trying to reference the cell/range in a formula simply type the new Defined Name.
How to find it in Excel? Formulas->Defined Names
No. 9: VBA macros and recording macros
The reason VBA is placed before last on the 10 Top Excel features list is because, in my opinion, it is so often abused but users who refuse to learn well the remaining Excel features. VBA macros fills the gap of all the missing features/functions in Excel. Macros let’s you program almost anything in Excel, you name it – forms, database connectivity, analytics, web browsing etc. You can’t basically consider yourself an Excel Pro without being able to program macros in VBA. However, it is important to stress that the problem with VBA is that once learned it tends to make analysts lazy – instead of Excel Array Formulas you will see custom macros or hideous UDF-functions. VBA should be used as a tool of last resort!
Typical applications of VBA
Custom algorithms (custom analysis of data sets)
Custom Excel UDF functions (User Defined Functions)
Excel Forms (making custom user forms to input data or create a custom UI)
How to find it in Excel? Developer->Code->Visual Basic Recording macros
The other fantastic thing with Excel is that it enables you to record macros – by recording your actions in Excel and translating them into VBA code. In some cases you need not even understand the code to be able to reuse it e.g. applying custom formatting to selected cells. This is definitely a great feature on it’s own.
How to find it in Excel? Developer->Code->Record Macro
No. 10: Microsoft Power Add-In’s
Last but not least on the 10 Top Excel features list are the PowerPivot, PowerQuery and PowerMap powerful Microsoft developed Add-Ins for Excel. Harness the power of Big Data, SQL, complex pivots and charts with these fantastic Add-ins! The PowerMap is a relatively new member of the family delivering nice bells and whistles to your Workbooks!
PowerPivot basically pumps Excel with more Analytics features by extend the PivotTable with summarization, cross-tabulation, expanded data capacity, advanced calculations, ability to import data from multiple sources, and the ability to publish the workbooks as interactive web applications.
PowerQuery will allow you to easily harness data and access to external data sources such as files, the Web, databases etc. and more easily manipulate and cleanse data. PowerQuery enables you to process enormous data sources/tables counting millions of records (more than an Excel Worksheet can contain).
PowerMap (as quoted on the official MS site) is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways. A power map lets you discover insights you might not see in traditional two-dimensional (2-D) tables and charts.
Both these tools complete one another. If you want to do Business Intelligence in Excel you need to be able to use both these Add-Ins. Microsoft seems to have great plans for them and I would anticipate that both these Add-Ins become a “native” part of Excel in the following versions of Microsoft Excel.
Introducing the new extension to the Scrape HTML Add-In: Automatic Updating of Excel Worksheets. When configuring your Excel file to scrape data of HTML websites there is often the issue of how to refresh this data periodically. For example – you want to scrape stock price data and refresh it every 2 minutes. You can either do this manually, write some clever VBA using the Application.OnTime function or… simply easily configure the periodical refresh in the Automatic Update tool as part of the Add-In.
Click on the Automatic Updating button to see the list of worksheets and refresh rates. You can add any worksheet and configure the interval at which it should be updated. Then click “Off” to turn the updating on. And that is it! The tool will then refresh on the Get* functions (e.g. GetElementByRegex) on the interval.
No VBA required!
How to use this feature?
This will certainly be useful for users who:
Need fresh data – whether you are a stock broker, analyst in any other role. This will certainly make life easier
Are waiting for an important website update – maybe you are waiting for the new iPhone or want to sign up to a marathon with limited participation. This will also prove useful.
Want to make automatic reports – why not make a dashboard of reports based on web provided data? Make charts based on data all in one place w/o needing to constantly switch between websites to extract the information you need
The primary concept of the Add-In is to reduce any need for writing VBA code. However, the problem with the Get* functions may be that if you specify multiple functions with the same URL there might be some overhead due to having to download the same HTML content just as many times – which might heavily impact performance. In order to solve this issue I have introduced the Cache functionality. If you specify the same URL in more than 1 Get* function then provided that the cache timeout has not passed the HTML content will not be refreshed from the Web but from the Cache. E.g. if you have 2 Get* functions with the same URL and a 60 sec timeout, then only the first call to the Get* function will download the content from the Web, whereas the second one will not – provided that the second call happens before the 60 sec timeout ends. You can change the setting at any time in the “Cache settings”.
As I mentioned I am planning to extend the Scrape HTML Add-In with additional tools and functionalities. Scraping HTML content should not require any VBA coding – this rule is guiding the further development of this tool.
Adding to the toolbox I would like to introduce you to the Scrape HTML Tool. The Get* functions (e.g. GetElementByRegex) which I posted earlier are really useful when you are making a solution which can be reused e.g. scraping regularly posted online data etc. I suppose, however, that sometimes there is only a need to scrape some content once but preferably in a structured manner or only some subsets of the content.
The Scrape HTML Tool
The tool comes in handy when you want to quickly scrape all items of a certain type (matching a certain regular expression). It comes with several predefined scraping regular expressions e.g. scraping URLs and img src properties. However, these examples are just to start you off with building your own patterns/expressions.
See this video on how the Scrape HTML Tool can help you:
Let me know if this tool is useful to you and if you see any need of extending it!
I am also planning to post some a simple tutorial or something to show more elaborate examples of scraping/downloading HTML content from the web. Information is power – it is time to make usage of data more simple.