It’s been a long time since the last Excel roundup, so here’s a new one! Check out these Excel links and articles, and I’m sure you’ll find a few interesting things to read.
NOTE: To get Excel links and articles every week, sign up for my weekly Excel newsletter.
Contextures Articles
First, here are a few of the Excel articles that I’ve published since the last roundup.
Pivot Table Styles: Use this macro to make a quick list of a workbook’s pivot tables & styles, with colour samples.
Data Entry: Allow weekend dates only in Excel, with a Data Validation custom formula that blocks Mon-Fri dates
Drop Downs: Create an Excel drop down that only shows visible items from a filtered list
Data Viz
Data Viz – Nathan Yau chose his favourite data viz projects from 2018, so take a look, to get inspiration for your own projects.
Data Viz – Jonathan Schwabish taught data visualization to his son’s 4th-grade class, and there’s good information (and fun ideas) there for adults too!
Data Viz – Google has published their guidelines for great data design. There’s an overview on the Fast Company site, and see the guideline details here.
Data Viz – Alli Torban shares great tips for giving and receiving critiques of data visualizations. Use these tips when a co-worker shows you their latest Excel “masterpiece”.
Power BI
Power BI – Another topic to explore in 2019 is Power BI. Reza Rad posted part 2 of his excellent series on Getting Started with Power BI Desktop. And here’s Part 1, if you missed it.
Power Query – Ken Puls shows how to pivot stacked data, using Power Query. For example, a mailing list, with names and addresses all in the same column, with blank rows separating them. You don’t need complicated formulas now!
Excel Tips
Tips – In the Actuary community on Reddit, there’s a discussion with Excel tips. I didn’t notice any offensive language, but it’s Reddit, so enter at your own risk!
Excel Skills – How would you rate your Excel skills? Are you an expert? Read this interesting discussion on Quora, of what an Excel expert is.
Charts
Charts – Jon Peltier shows how to make a repeated Gantt chart in Excel, to track player time on ice. Can you think of other ways to use this type of chart?
Charts – Jeff Lenning shows how to create a dynamic chart title with Slicers.
Functions
Lookup – Liam Bastick shows how to use the LOOKUP function to pull data for a financial modelling workbook. The download link for the sample file is just above the second image.
Dynamic Arrays – If you use the new Dynamic Arrays in Excel, there might be problems if you send your workbook to someone using an older version of Excel. Get the free CheckDA tool from Charles Williams, to check for problems. Charles also sells a FastExcel add-in.
Prediction – The Excel team at Microsoft built a college basketball prediction spreadsheet, and even if you don’t follow that sport, take a look at the file, to see how it works. There’s always something interesting to learn!
Beer – Larry Carpenter is a full-time engineer, and part-time brewmaster. Even if you’re not interested in beer, his brewing spreadsheet is worth a look. It’s free, with no macros, and a video explains how it works.
Spreadsheet History
Rivalry – In this old article from The Register, Dave Mandl describes the moment when he realized that Microsoft Excel would knock the market leader, Lotus 1-2-3, out of the top spot.
Calculators – Without the Great Calculator Race of the 1970s we wouldn’t have iPhones. Or spreadsheets – Dan Bricklin competed with calculators when he built VisiCalc (video 3:10 mark). And have you tried Excel’s built-in calculator/converter?
_____________________
Excel Roundup 20190711
_____________________
_____________________