Category Archives: Uncategorized

Learn Excel – 2 Awesome Ways

As most of you have heard by now, I am starting a fantastic new Excel training program called Excel Hero Academy. So far there has been a tremendous amount of interest in learning to craft Excel projects the way I do. Thank you for this. It truly is an amazing feeling that so many of you would have such strongly positive feedback. I am still working on the details and hope to have much more concrete information to you in the next week or so. Over a hundred people have already filled-in the questionnaire giving me valuable information on what to cover in the course. Please take a moment to add your input!
I’ll immediately send you the quick survey.
This will be a one-of-a-kind training program with lots of interactivity and direct access to me. I’m really looking forward to it. My goal is to start the 12-week course on October 11, 2010.
Excel Hero Academy will be advanced training, but as long as you are intelligent, you need not be an advanced user already to benefit tremendously from the course. Completing the course will make you advanced. Scratch that. It will make you much more; it will make you an Excel Hero.
— — —
If you are just learning Excel for the first time – a beginner – you should also seriously consider Chandoo’s Excel School. A few months back I had the honor of reviewing his online school, just before he started the 2nd round of students.
I can tell you categorically that he has done a fine job. His online school is first rate, as are the downloadable workbooks and videos. It is obvious from the moment you first log on that Chandoo has put a lot of effort into designing the curriculum and developing the lessons, with the business user in mind.
The motto of Chandoo’s blog is Become Awesome in Excel. Excel School will definitely help get you there.
Every lesson can be viewed online or downloaded, and the class includes over 1,200 minutes of video instruction hosted by Chandoo himself who not only is the creative force behind the famous Chandoo.org Excel blog, but he is also a Microsoft Excel MVP.
Working your way through this pertinent material will leave you with a solid, well-rounded Excel foundation. Just look at the lesson plan:
excel_school_lesson_plan.png
For Excel School 3, Chandoo has added several nice features and bonuses, including a live webinar.
Chandoo’s Excel School comes with over 40 crystal clear example workbooks and access to the Online Classroom which is an extremely valuable resource where class-members share ideas, ask questions, and discuss lessons.
The last class had over 200 students enrolled, so the debate was very stimulating. This will be the third class and I have a feeling it will be even more of a success.
Admissions open on September 15, 2010. You owe it to yourself to check into Chandoo’s Excel School 3.
excel_school.png
Enhanced by Zemanta

Excel Optical Illusions Week #31

This week’s Excel Optical Illusion is called Square Circle Spiral. I first saw this on the Discover Magazine website.

square_circle_spiral_optical_illusion_excelhero.com.png
My Excel version is a simple XY Scatter plot of four circles. That’s right, circles. The illusion of spirals is particularly strong, at least for me. To the right of the chart I left the various markers so you can see how it is done.
Here is the file.
Excel Hero Academy coming soon! I plan to release a lot more details later this week.
If you like my blog, please be sure to sign-up for the new Excel Hero Newsletter. Subscribers receive all of my articles AND extra Excel Hero tips.
Why do I share these optical illusions? The techniques that are used to make them, when mastered, can be used in many other Excel projects, in charting, formula crafting, and formatting. Learn them. They will aid you on your journey to become an Excel Hero.
Here is a list of other Excel Optical Illusions here at Excel Hero:
Enhanced by Zemanta

Excel Data Visualization

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!

This visualization, originally by axiis.org, caught my attention some time ago. Here is my version of it in Excel 2007.
w3schools_historical_browser_stats_excelhero.com.gif
The animated GIF looks terrible. The Excel chart is much better.
I arbitrarily picked a date range for the chart that was different than the original as a means of dealing with Excel’s nutty limitation of 255 series in a chart. So there are exactly 255 series in the above chart, and yes I know that Safari and Opera were shorted on the outer ring, but it is easy to see that their data did not change much in that month. The date range that I chose from the available data (all of which is on the data sheet) tells the best story in 255 series, I believe.
I chose not to use two charts on top of each other as I did on the Smith Chart since this visualization called for mouse interactivity with the series and doing so would have left only the top chart’s series interactive. Compromises.
Additionally, this chart suffers the same limitation of the HTML 5 Readiness visualization in that line End Caps must stay rounded instead of flat as there is no VBA access to that part of the object model! And if you change the lines manually with the format dialog, they will revert to rounded when changing the line color with VBA!!!
Another challenge was that Excel draws circles counter clockwise starting at 3:00. Getting all the arcs to start at 12:00 and rotate clockwise took some doing! And I should add that they don’t – they just look like they do…
Tough chart and although it is functional, I’m not convinced I like how it turned out. But it remains extremely instructive. What do you think?
I do like how in the most abstract way, it looks like the Firefox logo.
Here’s the file.
Please check out the Excel Hero Academy.

Chandoo’s Excel School Now Open

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.

If you want to build a solid foundation in Excel, you owe it to yourself to watch the five-minute introductory video to check out how Excel School works. The video is on the extreme right of the Excel School description page.
And please don’t forget that my new training program, Excel Hero Academy, begins on October 11, 2010.
There is a new visualization coming later in the week to this blog! It’s going to be pretty cool.

Faith and G.D.P.

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:

religious_outlier_nyt_excelhero.com.gif
This project demonstrates the quickest formula lookup method that I know, array-entered INDEX feeding off a common MATCH. It’s a simple bubble chart. All of the interactivity is accomplished by formulas. There is no VBA here.
The most frustrating data to work with is a data table embedded within an image. Unfortunately this project required doing just that with Gallup’s religiosity data. Notably missing from their data is Australia. I suspect that they would be more like the USA and less like Europe, but I could be wrong. Also it is interesting that UAE, Kuwait, Bahrain, and Singapore were left off the original infographic published in the NY Times. All of these countries are even more of an outlier than the USA.
The rest of the data came from the CIA’s The World Factbook.
Here is the file.
If you haven’t heard, I am gearing up to start Excel Hero Academy, the purpose of which is to teach you how to do Excel projects like I do.

Excel Hero Academy – Interested?

Please take a moment to give me some feedback.
For several months now I have been working on and planning to start an online Excel training program that I’m calling Excel Hero Academy. And for planning purposes it would be very helpful to know if YOU are interested in this.
In a nutshell, this is it:
  • 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 WHAT I NEED FROM YOU:
Are you up for such a program? Please take a second and tell me your name and email if you are interested.
I will add you to our list and keep you updated on the program.
I think I have a robust agenda, but I want to make certain that I am covering items of interest to you as well. So I’ll send you a very short survey and you can tell me what topics you are interested in. 

Excel Partial Match Database Lookup

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.

partial_match.gif
In the sample file, there are 10,000 records and as you can see the partial match lookup is very quick. It supports wildcard characters.


While this is entirely an Excel application, what we are doing here is equivalent to a traditional database SQL statement of:
SELECT *
FROM Data
WHERE (((Data.Description) Like “*search_string*”));
So how do we do this in Excel. It could be done using just formulas, but this is a VBA project. It leverages the brilliant FindAll() function by Chip Pearson. All of the code is in the Sheet2 code module. Have a look. It’s succinct, potent.
The file works in all versions of Excel from 2000 forward.
So tell me how you do partial match lookups?
Here is the file.
Please remember to sign-up for the free Excel Hero Newsletter!
— UPDATE —
As we have seen many times before,  Excel always has multiple ways of doing things. Sam sent me an awesome tip. I revised the workbook to use the tip and it is now much faster and the code is better too. Chip’s FindAll() function is still brilliant, but it was not needed here, so I’ve removed it in the new version. It is useful for many, many things and I’ll write about it often. Sam’s tip was to use the AdvancedFilter method of the Range object. The previous version was more than adequate as long as the search string was three or more characters long. Now it’s instant even if one character long. In fact, it’s instant even if zero characters long!
Here it is.
— UPDATE #2  —
As promised in the comments, here is the ADO way of doing the Partial Match Database Lookup. I have taken Nikolai’s example and reworked the code somewhat to conform to my style of programming. Using ADO is very, very powerful, as it unlocks the entire universe of real databases, such as SQL Server, Oracle, MySQL, Access, and countless others. Virtually all Windows computers have ADO installed and ADO can read Excel files even if the machine does not have Excel installed! Of course for our purpose, we will be using it from Excel so that is beside the point. But you see, ADO, is a separate library that dozens of programming languages can use. So to use it from Excel we need to reference the ActiveX Data Objects Library from the Tools-References menu of the VBA Editor.
Over the coming months I will share many applications that use ADO to interact with databases. But there is one hiccup that you need to know about. If you are using it to interact with Excel data in the same workbook where you are using ADO, there is a problem. Each time you call the ADO library, a bit of memory will be used up on your computer and you will never get it back until you reboot your machine. This is called a memory leak, and it is poor programming practice. This particular memory leak is a recognized bug and Microsoft has published a KnowledgeBase article about it on their support website:
So you should never do this. However, for instructional purposes I have setup Nikolai’s workbook with a check box that allows you to toggle between using local data, and having the VBA code create a copy of the data in a new workbook and accessing that instead (which is the correct way to do it). To see the memory leak in action, just open the Windows Task Manager to the Processes tab while you have the workbook open. Sort the list by Image Name. Find the Excel.EXE process and select it with the mouse. Now start using the Partial Match Database Lookup with ADO workbook to do searches. You will notice if you  have the External Data Method check box checked that the memory will temporarily increase for the Excel.EXE process as ADO is invoked; but you will also notice that the memory is quickly given back to the Windows operating system. This is good and the way things are supposed to work.
Now un-check the External Data Method check box in the workbook and do some searches. You will notice that each time a search is done the memory spikes, but that memory is NEVER released back to Windows. This is the memory leak. If you keep using ADO in this fashion eventually you will run out of memory and your computer will crash. The only way to get that memory back is a reboot.
So this file is interesting and instructive. It shows you how to use a very powerful library to access data, and it demonstrates the correct and incorrect way of doing so, allowing you to toggle back and forth between the two.
Thank you Nikolai for the file. He runs a fantastic website on chess, so please check it out:
Here is his modified Parial Match Database Lookup file that uses ADO:
Enhanced by Zemanta

Excel Formulas Based Sudoku Solver

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.


sudoku_solver_excelhero.com.gif
Above is an animated GIF of my Sudoku Solver in action. It does use VBA, but not for solving a puzzle, just for updating the display and moving the game forward. What’s nice about this implimentation is that it is interactive and can be used to learn the various human logic techniques that when mastered can solve these puzzles efficiently.
The techniques that are currently supported are Naked Singles, Hidden Singles, Naked Pairs, Hidden Pairs, Naked Triples,  Hidden Triples, Naked Quads, Hidden Quads, and Intersection Removal. I had planeed to add X-Wing, Turbot Fish, Y-Wing, Sword-Fish, Jelly Fish, and X-Wing Pairs, but sadly never found the time. Perhaps a budding Excel Hero will. The project is open-ended so any number of logical techniques could in theory be added.
I’ll warn you that the logic gets a little complex. I’m sure my formulas could be improved upon. The project was done as a proof of concept and not as a production piece. However, there is a lot here that can be learned from advanced named formulas to VBA to conditional formatting.
To run the solver, just click on the Search button. The program will test the puzzle to see if any of the logical techniques in its arsenal will work. When it finds one, it stops and highlights the technique and the affected squares. This is how you learn. To continue, just click on Search again. This iterative process continues until either the puzzle is solved or the current roster of techniques cannot advance the puzzle solution further. Surprisingly most puzzles published can be solved with this limited list of techniques.
The small numbers are candidates for the larger square immediately to their right.
You will notice a sheet named “z.” On that sheet is a collection of puzzles that you can try. Most can be solved by this program. Some cannot. Just copy the 81 numeral string to the clipboard and then switch back to the Game sheet and click the Import button. Paste the string in the dialog and click OK. You can import any Sudoku puzzle this way; it just needs to be in the standard 81 numeral format.
On the bottom-right corner you will find a section entitled Show Candidates. Clicking on any of the colors there will highlight the respective candidates on the game board.
The program goes out of its way to protect itself and password protects the Game worksheet on every move. The password it just the numeral “1”. Tear into this. I guarantee you will learn a lot.
I’ve tested it in Excel 2002, 2007, and 2010. Please let me know what you think.
Here is the file.
Enhanced by Zemanta

Excel Hero Newsletter

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!

I’m rolling all blog entries into my new Excel Hero Newsletter along with some extra tips for subscribers. People tell me every day now, that they come here because of the fresh spin on Excel and the creative ideas I share. If this is you, I promise I’ll make the newsletter worth your time.
Here is the form to use to subscribe, but you’ll also find it in the sidebar of every blog entry:

Which Function To Use – Part 1

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.


 
Just knowing where to start in a given situation requires experience. There’s a science to it, but truly inspired solutions are as much art as science. Think of this series of articles as a treasure map, as I will attempt to lead you to the most productive paths.


Before we delve in, I need to backup. We must understand what a range is and how it is specified. A range is a group of one or more cells. The range is NOT the values in those cells. This distinction is important. Some functions (and hence formulas) return values. Some return ranges. Still others can return either in varying circumstances. The value is just one property of a cell. Other properties include color, type of border, font, etc. Typically just the value property is directly accessed from a formula, but many of these other properties can be manipulated via formulas when using Conditional Formatting. All range properties can be accessed when using VBA.
Ranges can be specified by either R1C1 notation or A1 notation. R1C1 is no longer common and requires an option setting. This article will discuss A1 notation exclusively.
Excel recognizes three reference operators. These three operators combine input ranges and produce an output range.
The first is the Range Operator, which produces a minimum rectangle around the inputs. The Range Operator is the COLON:
A1:B4
You’ve seen this in action a million times, I’m sure, but you may have never realized what it means. The colon specifies the range defined by the minimum rectangle circumscribing the input ranges. The input ranges can be specified by any method that describes a range, including defined names, functions, and formulas:
A1:K2:N16:W15

A1:lstLastInvoice

INDIRECT("B1"):OFFSET(Z1,,,100,100)

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:W16

but by substituting formulas for the input ranges, novel ways of controlling other formulas begin to present themselves.
There are two other reference operators and you may not even know about them. These are the Union and Intersect operators. The Union Operator is the COMMA. The Intersect Operator is the SPACE character (space bar).
While the Range Operator always returns a rectangular range, these two other operators can return ranges of any shape, contiguous or not, as long as all of the input ranges are on the same worksheet:
(A1,B2,C3,D4,E5,F6)

(A3:E3,C1:C5)

(A3:E3 C1:C5)

(A1,A10,J10,J1)

(CurRow CurCol)
The first of these five examples returns a range in the shape of a diagonal. The second returns a range in the shape of a plus sign. The third returns a range composed solely of C3. The fourth returns a range of four disconnected cells in the shape of the vertices of a square. The last returns the range where the named formulas intersect. This last example is probably the most overlooked lookup method in Excel and can be used to conveniently return information from a list or a table of data. Again, the caveat is that all of the input ranges must be on one worksheet.
Another note is in order for the Union and Intersect Range Operators. In math, a union of two or more sets will only include an element once, but the Excel Union Range Operator does not work this way (pity):
=SUM( (A2:B2,B2:C2) )
The above formula uses the Union Range Operator. It should be the equivalent of:
=SUM(A2:C2)
But it is not. Whatever is in B2, gets summed twice in this example. Also, notice the parentheses around the compound range. This is good practice since functions that require multiple input parameters use commas to separate those inputs. The parentheses ensure that Excel is not confused by ambiguous input.
So the Union Range Operator joins input ranges but is not a true Set Union Operator. On the other hand, the Intersect Range Operator is a true Set Intersect Operator, returning only elements (cells) in common.
There’s at least one more twist to range referencing that deserves mention. Ranges can also be 3-D:
Sheet1:Sheet12!A14:Z14
Notice that the Range Operator is used twice in this 3-D range example. This returns a range composed of A14:Z14 for twelve worksheets. This could be useful for adding monthly sheets, for example. The other two reference operators do not work on 3-D ranges. Here is an article from Microsoft explaining 3-D ranges in greater detail and specifying which functions work with them. There’s actually more functions than listed there, but that is a topic for another day.
Now that we know what a range is, we need to realize that the roster of functions that we can use to slice and dice our data, use these ranges as inputs. This is most commonly done with rectangular ranges, but the others can produce interesting results.
There is a lot to go over here and so I am going to break it up into a series of articles. To begin with, I thought I would share a video presentation I made a couple of years ago on the VLOOKUP function, long before I began this blog. I had the idea for the blog at the time, but really did not know the direction I would take with it. Here is the VLOOKUP video:
master_series_vlookup.png
It’s a long video; 27 minutes on a simple function! Probably too long. But I’d like to hear your feedback. I’ve always wanted to host a masters class in Excel, and after seeing the popularity of Chandoo’s foundational Excel School, I started thinking of this once again. The production is not as polished as I would like, but hey it was a couple of years ago when I was just learning how to make videos.
The idea behind the masters series is to explore each function in detail and then see how they can be combined into elegant formulas. Of course it would have to have advanced charting, excelhero style. So what do you think? Is this something I should invest my time in?