Excel Roundup 20171214

Here is an end-of-the-year Excel Roundup, with articles that I’ve read recently. To get weekly links and articles, sign up for my weekly Excel newsletter. Merry Christmas, and happy holidays, and come back for a new blog post in January!

Excel Data

Power BI – For everything that you wanted to know about Power BI, download a free copy of Reza Rad’s book, Power BI From Rookie to Rockstar. Or, read the book online — scroll down about halfway on that page, to see the table of contents.

Clean Data – Apparently, messy data is the biggest headache for machine learning and data science. That article is based on Kaggle’s interesting survey of 16K data scientists.

Dashboards – Tableau shared 7 tips and tricks for building dashboards, and they’re great tips for Excel dashboards too. And for dashboard charts, read these 5 Data Viz tips from Jorge Camoes.

 

Excel Complaints

Excel Complaints – A recent WSJ article reported that some CFOs want their employees to stop using Excel. This Bloomberg article says some complaints are valid, but others are misguided.

Number Problems: If you hate the way that Excel messes up some number when you import them,  vote for this fix request on UserVoice. It has almost 1200 votes, and the last response I see from Microsoft was 2 years ago.

Table Problems: Jeff Weir also has an Excel complaint – problems with Excel tables on protected sheets. There is a UserVoice request to fix that too, and Jeff hopes that you’ll add your vote there.

Formulas

Advanced Formulas: Ben Collins is offering a free course in Google Sheets Advanced Formulas. Many of the functions have a twin in Excel, such as VLOOKUP, INDEX and MATCH. You’ll also see functions that Excel doesn’t have, like QUERY and FILTER. I don’t know how long Ben’s course will be free, so if you’re interested, sign up soon.

Formatting

Formatting: Chandoo shares five conditional formatting tips, from simple highlighting, to complex formula rules – get the sample workbook too. If you want to learn more, there are conditional formatting examples on my website.

Excel Fonts – If you want to choose the best fonts for an Excel report, Kevin Lehrbass has a free workbook that lets you compare several fonts at once. (Level – All)

Excel Fun

Excel Art – Tatsuo Horiuchi creates amazing art in Excel, as you can see in the video at that link. There’s more Excel art on the Datalabs site, created from charts. And Andy Pope has a free tool for converting a normal BMP image into an Excel scatter chart.

Excel Games – If you enjoy games, take a look at this Excel Sudoku Solver. Also, Andy Pope has a few Excel games on his website, and lots of other great stuff, like this Chart Exporter.

Excel Christmas Tree – And finally, a bit of holiday fun. Download the sample file from my website, and you can decorate an Excel Christmas tree, using a spin button. There are NO macros in the file – just conditional formatting, formulas and named ranges. Merry Christmas!

_____________________

 

Excel Roundup 20171214

Excel Roundup http://blog.contextures.com/