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. […]
Spilled range as chart source
Using a spilled range as a chart source will now automatically update the chart when the source changes. Another good reason to embrace these ‘new’ formula in Excel. You can read more about one of these formulas here Below is a bar chart with the data source next to it. The data source is the […]
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 […]
Data Validation drop down lists
Data validation lists are better than ever. The list will now automatically reduce to match what you have typed into the cell. This wasn’t the case for so long in Excel, but has been standard in forms on the internet for ages. It was possible to create a drop down box that did it, but […]