Shortcut keys – even short term use is good.
Shortcut keys save you time. But, they are hard to learn, and we are so used to reaching for the mouse, that it is a hard habit to break. I recommend learning one shortcut key at a time. And, I also recommend using a shortcut key if you have something that you are going to […]
DETECT LANGUAGE
Another new function that can work in tandem with translate is detectlanguage. Read more about translate here. It does exactly what is says, it will detect the language of text and return a two letter code for the language found. There is only one input required for the function and that is the text to […]
Translate directly in Excel
You can now translate directly in your Excel spreadsheet. Use the TRANSLATE function to change one language to another. =TRANSLATE(text, [source language], [target language]) As usual, inputs in square brackets are optional. If you don’t specify the source language Excel will try and work it out from the text input. The target language will be […]
Keep Watch with the Watch Window
Meet the Watch Window – it keeps important cells visible no matter where you are in your workbook. Here’s how to set it up: Go to the Formulas menu. Click Watch Window (it’s in the Formula Auditing group). This will bring up the Watch Window box. (Or shortcut keys Alt, M, W.) Click the Add Watch […]
Trim range function and the dot!
You can now reference a whole column, but not the whole column in Excel and all with a dot which stands in place for the TRIMRANGE function. That might be a confusing statement, so let me explain. See the picture below. In both, I have referenced the whole of column Q. In the left-hand picture, […]
Focus Cell
You can now turn on Focus Cell in Excel. This highlights the row and column of the cell you have activated. Handy if you want to be able to see easily all the data in the row or column that you have selected. To turn on Focus cell, use the button on the View menu. […]
Check out a Checkbox in Excel
Checkboxes have been added recently to Excel. If you don’t have them, you may not have the latest version, so check for updates or your subscription. To add checkboxes to your sheet, go to Insert menu, and find the Checkbox. (Shortcut keys Alt, N, CB.) The value of a checkbox is True if ticked and […]
Referring to a spill in another formula
You can reference a spilled range in a formula. That way, whenever the spilled range changes size, your referencing formula automatically includes all the data. Above we have a filter formula that is spilling down four rows. Above we have a Unique formula that is referencing the spilled range. Note the # in the reference. […]
Value preview in formula bar.
Using Value Preview, you can see the value of each part of a formula in Excel. Just highlight the reference you are interested in, and a little box pops up that show you the value(s) in that reference. Or you can click on the parameter in the formula structure. More information and some videos showing […]
Search the right click menu
You can now search in the right click menu to find the tool you are looking for. You now get a search box when you right click on a cell, or use the keyboard shortcut key Shift F10 (or some keyboards have a dedicated key for it , see picture below.) The Search enables you […]