Excel Roundup 20150112
Doug Glancy used Excel’s camera tool, and some VBA, to create a mesmerizing “selfie” of a range which has conditional formatting applied. Doug describes the results:
Kind of like when you’re in a dressing room with the mirrors front and back and you get a reflection of a reflection of a reflection … Unless you’re like me and you just keep buying the same pants online.
I downloaded the sample file, and changed the colours, to match my Contextures logo. I’m not sure if this has too many practical applications, but perhaps you can use it to hypnotize your boss, and get a big raise.
Here’s what I posted recently:
- In a named Excel table, add counter field that fills in automatically, without macros. This is useful for summary formulas, pivot tables, and calculated fields.
- A pivot table calculated field always uses the SUM of other fields in its results. If you need a count, add a counter field to your source date, and use it in pivot table formulas
- For a humorous peek at what other people are saying about spreadsheets, read the latest collection of Excel tweets, on my Excel Theatre blog.
Other Excel Articles
Here are a few of the Excel articles that I read recently, that you might find useful.
- On the Microsoft Office blog, students can learn how to build a GPA calculator in Excel. Kevin Lehrbass spotted an error in the VLOOKUP formula, and it’s fixed now.
- You’ll find new ideas for your dashboards in Tableau’s Top 5 Vizzes of 2014.
- Max Fraudy shows how to dynamically highlight a data point on your chart, without using VBA.
- Chandoo uses the AutoCorrect feature to make it quicker to enter formulas. Have you ever tried that? I probably wouldn’t be able to remember all my shortcuts for the formula names.
- Microsoft’s Excel team shared a link to this video, in which Brian and Karolyn used Excel to announce that they’re expecting a baby.
- Sumit Bansal shares a trick for using a defined name to get a list of file names from a folder in Excel.
- Things are a bit different in Excel for iPad, and Stephen Ashby shows how to create formulas in that version.
- This mini-course from Microsoft’s Office Online shows how to use the Sparklines — tiny charts to show data trends.
- Leon Lovett (That Data Dude) shows how to stop Excel from formatting two hyphenated numbers to a date, when you import data.
- Dr. Nitin Paranjape demonstrates why it’s easier to create a set of charts with Power View, instead of using regular Pivot Charts
Here are some upcoming events, courses, recently published books, and other new items, related to Excel.
DAX Patterns 2015, by Marco Russo, Alberto Ferrari, Daniele Perilli
“A pattern is a general reusable solution to a commonly occurring problem. This book is a collection of ready-to-use data models and formulas in DAX, that you can use in Power Pivot for Excel and in Microsoft Analysis Services Tabular.
Create your data model faster by using a DAX pattern!”
Amsterdam Excel Summit, April 13-15, 2015
Mark your calendar for April 13-15, so you can attend this amazing Excel event. Last year’s summit was an outstanding success, and registration will open soon, for this year’s event.
Bookmark this page, for event details and registration: Amsterdam Excel Summit 2015
Share Your Events and Articles
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please let me know. Thanks!
Links to Recent Excel Books on Amazon.com