Spreadsheet Roundup 20150107

Here is a roundup of selected spreadsheet articles from last month.

• At Macworld, Lou Hattersley compares the iPad versions of Microsoft Excel and Apple Numbers. Her recommendation might surprise you.

• Mu Lin suggests 3 guidelines for designing data dashboards. While the article focuses on news reports, the tips can be applied to other dashboard types.

• Charlotte Utting outlines why Excel and R are useful together, and explains how you can connect them.

• To fix currency data from another region, you can quickly change currency separators with the Text to Columns feature

• In Excel, you can select multiple items from a long drop down list, using a popup form with a combo box and list box.

• Download “Communicating Data”,the first chapter of Ben Jones’ book, Communication Data with Tableau. The concepts can be applied to spreadsheets too.

• In December, an Office update broke the ActiveX controls in Excel, and Jan Karel Pieterse shows how to fix the problem

• If a data validation list has hundreds of items, this technique can help. Type a couple of letters in one columns, and see a short drop down list in the adjacent column, with only items that contain those specific letters.

• When you group dates in a pivot table, Excel creates extra items in the pivot table filter – see a couple of workarounds for this problem

• After the recent file hacking at Sony Pictures, Melanie Pinoa reminds you that you shouldn’t store all your passwords in an Excel file.

• Jordan Goldmeier takes a look at complicated Nested IF formulas, and when you should use them.

• If you build a column chart, Gašper Kamenše shows how to highlight the smallest and largest amounts.

• For an in-cell chart, Chandoo explains how to include markers for average or a target.

• Zack Barresse explains the difference between clean and dirty columns in an Excel table.

Links to Recent Excel Books on Amazon.com

__________________________-