This week’s Excel Optical Illusion is called Square Circle Spiral. I first saw this on the Discover Magazine website.
This is going to be a short post because I’m hard at work on the Excel Hero Academy and need to spend my time on it in order to be ready for next month!
Just a quick note to let everyone know that Excel MVP Chandoo has opened the third round of his awesome Excel School a day early, well at least here in the USA.
I came across this provocative infographic on FlowingData. I made mine into a dynamic Excel chart. Here is an animated GIF of the Excel 2007 chart:
- In depth lessons to develop your skills to the point where you can craft Excel projects like I do! Topics covered will include charting (Excel Hero style, including animation), advanced named formulas, advanced array formulas, creative formula combinations, proper VBA coding (including branching, looping, subs, functions, modularization, classes, etc.), making forms, advanced macros, integrating Excel with other products and other workbooks, dealing with huge workbooks, optimization, dashboards, Solver, and many other things.
- All content will be 100% original. Like my blog, it will be stuff you will not find anywhere else.
- Completing the course will make you an Excel Hero, without question.
- The program will be online and delivered once a week for 12 weeks.
- The program will be paid.
- In addition to videos and workbooks, we will learn from each other and share creative tips in a private, member only website.
- I am calling this “Excel Hero Academy.”
Here is a technique that I’ve used countless times when crafting an Excel database project for clients. The goal is to show all records that partially match a lookup string.
About four years ago, I created an interesting Sudoku Solver. It was unique because rather than use brute force VBA to determine every possible combination of numbers to solve a given puzzle, it used recognized human logical techniques that had been developed for the Sudoku game by enthusiasts worldwide. Techniques such as Intersection Removal, Hidden Quads, and Naked Pairs. And to top it off, these logical techniques were executed by formulas, with the heavy lifting done by named formulas using bitwise operations. I alluded to this game solver back in January when I posted 5 And 3 Is 1. Today, I’m finally sharing it with you.
It seems like every day lately I’ve been receiving more and more requests for automatic email delivery of my blog posts. I’ve finally got around to adding that!
Excel sports a large number of worksheet functions that can be used to slice and dice data. One of the qualities that makes Excel so fascinating is the endless variety that these functions can be combined into formulas producing powerful, surprising, even elegant solutions. There is much to learn here and the user is easily bewildered, wondering which function or combination to work with to produce the desired results.
In each case the Range Operator returns the range that circumscribes the input ranges with the tightest rectangle. At first glance, the first example seems redundant, and in fact, it can be simplified:
(A1,B2,C3,D4,E5,F6)(A3:E3,C1:C5)(A3:E3 C1:C5)(A1,A10,J10,J1)(CurRow CurCol)
=SUM( (A2:B2,B2:C2) )