In this week’s roundup, combine data, select multiple items, power query tips, better reports, and much more.
1. Multiple Selections with Drop Down List
If you select an item from a data validation drop down list, it replaces any existing data in the cell. If you’d like to add more items to the cell, instead of replacing the current text, you can use a bit of programming to allow that. You can download my Multi-Select Drop Down sample file, to see how this works, and I’ve added new code, to prevent a problem that occurs when you try to type an item in the cell.
2. Combine Data in Excel
If you’re combining data to build a dashboard, Mynda Treacy is offering free one-hour Excel Dashboard webinars this week. Get the details, and sign up for a date and time that is convenient for you. The webinars won’t be offered again for a few months, so don’t miss them!
Even if you don’t have Office 2016 yet, you can use the techniques in the free sample chapter from Curtis Frye’s new book, Microsoft Excel 2016 Step by Step. The chapter title is “How to Combine Data from Multiple Sources in Microsoft Excel 2016”, and shows how to use workbooks as templates, link to data in other files, and consolidate multiple sets of data. That’s how we combined data before we had all the fancy new Power BI tools, and the techniques still work!
3. Data Visualization
Chandoo announced a quick visualization challenge, with prizes for the top two entries. Download the data file, then visualize the data in one Excel chart, without using any add-ins or VBA. The deadline is Sunday, Nov. 1st, so get going, if you want to enter.
For other projects, if you’re creating data visualizations in Power BI Designer or Excel Power View, there’s a handy summary chart that shows which chart types are available in each tool.
4. Power Query
Using Power Query, Gil Raviv shows how to transform any nested table into a Pivot Table. This technique is available for everyone using the new Get & Transform section of the Data tab in Excel 2016, or as an Add-in in previous versions of Excel.
Also, the ebook version of the Power Query book, M is for (Data) Monkey, by Ken Puls and Miguel Escobar, is available now. The print version will be released in December.
5. How to Get Office 2016 With Office 365 ProPlus
Chris Newman couldn’t get the Office 2016 download from this Office 365 ProPlus account, and didn’t want until February 2016, for the general release date. Fortunately, he learned how to tweak some Admin settings, to get the new version, and he shows how to do that, if you’re in the same situation.
6. Better Excel Reports
Charley Kyd posted five ways to make your Excel reports incredibly popular with the managers at your company. One suggestion is to create small charts and tables for comparing key data — but make sure the font is big enough to read!
7. Excel Humour
Finally, for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets. Here’s one of my favourite tweets from this week’s collection.
Get Weekly Excel News By Email
To get Excel news and tips by email, add your name for the Contextures Excel newsletter.
________________
