Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 2.)

Welcome back. Get a good night’s sleep? Great. Hopefully you had freshly-picked brain-function-enhancing blueberries for breakfast, and a red bull or five, because you’re gonna need it. Why? Because today, my friends, we peek inside the multi-dimensional mind of Sajan the Excel Magician. As you’ll keenly recall from yesterday’s gripping episode of “When good formulas go GREAT”, Sajan claimed Bronze in our inaugural formula challenge at Formula Challenge 001 – Return everything in string after first block of numbers By way of a quick refresher, this formula challenge calls for a formula to return a substring from a longer string. Those strings look something like this: String: Required Substring: Monaco7190Australia1484 Australia1484 Liechtenstein4352Austria8590 Austria8590 Malta1306Belarus7578 Belarus7578 You can download the challenge and full dataset here: Formula Challenge 1.2 (Excel 2007+ only, because the formulas have too many nested formulas for earlier Excel versions to handle) Specifically, we need a formula to return only the second country and associated number …in the case of Monaco7190Australia1484 it’s that bit in bold i.e. Australia1484 Sajan the Magician split those strings with this award-winning beauty: =MID(A1,MODE(MMULT((N(ISNUMBER(-MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))={1,0})*(ROW(INDIRECT(“1:”&LEN(A1)))-{0,1}),{1;1}))+1,LEN(A1)) How does this wizardly wonder work? Let’s find out… 1. Sajan splits apart the source string into it’s characters This is as I did in the last post, albeit using a slightly different formula than I did: =MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1) =MID(A1,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23},1) ={“M”;”o”;”n”;”a”;”c”;”o”;”7″;”1″;”9″;”0″;”A”;”u”;”s”;”t”;”r”;”a”;”l”;”i”;”a”;”1″;”4″;”8″;”4″} 2. Then he adds zero to the array This coerces any numbers stored as text into numbers, while causing the text bits to throw errors ={“M”;”o”;”n”;”a”;”c”;”o”;”7″;”1″;”9″;”0″;”A”;”u”;”s”;”t”;”r”;”a”;”l”;”i”;”a”;”1″;”4″;”8″;”4″} +0 ={#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7;1;9;0; #VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;4;8;4} 3. Wrap an ISNUMBER around this unsightly spawn of Satan, This turns numbers to True, and turns those hideously unnatural errors of nature to better-behaved booleans:=ISNUMBER({#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7;1;9;0;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;4;8;4}) ={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE} 4. Turn this array into Ones and Zeros, and checks if it equals either One or Zero. I know…me too. But let’s give him the benefit of the doubt for now, and see what rabbit hole he’s leading us down…we might yet pop out of a magicians’ hat: =N({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE})={1,0} ={0;0;0;0;0;0;1;1;1;1;0;0;0;0;0;0;0;0;0;1;1;1;1}={1,0} ={FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE} What this has done in effect is create a 2D array – with one column of the array being the inverse of the other. So one column records whether something is a number, and the other records whether something isn’t a number. I know…me too. But maybe it would help if we saw what this would look like if entered over an Excel range, with our original string down the side so we can try and work out what spell this Wizard is whipping together:  5. He then creates a second 2d array with this bit: =(ROW(INDIRECT(“1:”&LEN(A16)))-{0,1}) ={1,0;2,1;3,2;4,3;5,4;6,5;7,6;8,7;9,8;10,9;11,10;12,11;13,12;14,13;15,14;16,15;17,16;18,17;19,18;20,19;21,20;22,21;23,22} …which if we were to enter over an Excel range would look like this: 6. He then throws this array into his wizard’s cauldron together with the array in 5 – along with generous portions of Eye of newt, toe of frog, wool of bat and tongue of dog. (This simply means Multiply the two previous Arrays) This gives us the following: ={FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE;TRUE,FALSE}*{1,0;2,1;3,2;4,3;5,4;6,5;7,6;8,7;9,8;10,9;11,10;12,11;13,12;14,13;15,14;16,15;17,16;18,17;19,18;20,19;21,20;22,21;23,22} ={0,0;0,1;0,2;0,3;0,4;0,5;7,0;8,0;9,0;10,0;0,10;0,11;0,12;0,13;0,14;0,15;0,16;0,17;0,18;20,0;21,0;22,0;23,0} …which again would look like this if entered over an Excel range (with our original string down the side by way of reference): Well mess up my hair, and call me Einstein. Because now I see what all that was about. The only time that the same number in that first array also appears in that second array is that magical place where the first block of numbers ends, and the second block of letters begins. That is some serious sorcery! 7. Multiply the arrays Next he conjures this 2D array into 1D, with the help of MMULT. Microsoft tells us that MMULT “…returns the matrix product of two arrays. The result is an array with the same number of rows as array1 and the same number of columns as array2.” If that’s got you scratching your head, then try this alternate explanation: The M in MMULT stands for Magic.   The way Sajan is using MMULT is to basically add across each row to find out what the total is for each row. Or another way of thinking about it is that because there’s ether a zero or a number in each row, he’s zipping up these two lists into one to just get the numbers and to ignore the zeros: =MMULT({0,0;0,1;0,2;0,3;0,4;0,5;7,0;8,0;9,0;10,0;0,10;0,11;0,12;0,13;0,14;0,15;0,16;0,17;0,18;20,0;21,0;22,0;23,0},{1;1}) = {0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23} 8. Work out the most common number in that array and add one to it Now he’s got the start position of our desired substring. =MODE({0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23})+1 =11 9. Lastly, he uses this trickery to tease out our answer string =MID(A16,MODE({0;1;2;3;4;5;7;8;9;10;10;11;12;13;14;15;16;17;18;20;21;22;23})+1,LEN(A16)) =MID(A16,11,LEN(A16)) =Australia1484 Austrailia1484. Crikey dick. Strewth, mate! That bonza beaut has more bounce than a Boomer! Makes me feel a couple of tinnies short of a slab. And that’s the formula that got the lowly Bronze! Tune in tomorrow, and we’ll see what the Silver-medal winner, Haseeb,has to offer. If you dare….. About the Author. Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this: =NOT(EVEN(PROPER(OR(RIGHT(TODAY()))))) That’s right, pure #VALUE!

Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 1.)

The Formula Challenge Series: Where good formulas go GREAT: Formula Challenge 001 – Return everything from a string after first block of numbers Introduction to the Formula Challenge series: Formulas are like the DNA of Excel…you can solve some very complicated evolutionary challenges by stringing together a few simple base-pairs in the right order. Over at http://chandoo.org/forums/forum/excel-challenges you’ll find a whole bunch of tricky formula challenges posted by a group of excel nerds (me included) trying to out-nerd each other with formula nerdiness. Consequently, these formula challenges are designed to get participants thinking creatively about how to solve a tricky problem using formulas only. (Oh, and they might be designed to get a whole bunch of nerds to do my tricky work assignments for me too.) [Psst….so as to not hurt any feelings, I’m obliged to point out under the UN’s declaration of Super-Human Rights that one person who posted a response at this challenge is not an Excel Nerd, thus allowing everybody who posted to assume it was them.] [Psst (again)…of course, in actual fact that lone non-nerd was in fact moi - but don’t tell them that…it will spoil their delusions of normality  ] Putting together a formula to tackle these challenges shows you have a great mastery of excel. Putting together a formula that tackles the challenge and is also shorter than anyone else’s is something more akin to standing on the gold podium at the Olympics and shouting “In yer face, LOSERS” down to the silver and bronze teams while you strut your best Jagger strut to the Windows 95 launch song. http://www.youtube.com/watch?v=5VPFKnBYOSI (Yes, it’s actually that satisfying to a true Excel nerd. Or so I’m told.) Anyway….I’ve learned a lot from looking at how different people tackle these, and my formula toolkit is much bigger as a result. To help you increase your toolkit, today we’re going to take a look at the first challenge in this series: Formula Challenge 001 – Return everything from a string after the first block of numbers (Part 1). Without further ado, here’s the details of the first challenge: Split the strings in Column A with a formula that only returns the substring that you see in column B. So for instance, if the string is Monaco7190Australia1484 then we want everything to the right of that first block of numbers i.e. Australia1484. Your formula should handle any length of string, and any length of numbers – not just the lengths shown above/below You should not use any helper cells, intermediate formulas or named ranges, or VBA Those strings look something like this: String: Required Substring: Monaco7190Australia1484 Australia1484 Liechtenstein4352Austria8590 Austria8590 Malta1306Belarus7578 Belarus7578 You can download the challenge and full dataset here: Formula Challenge 1.1 The bit that’s required to be returned is the Blue Bold bit i.e. the block of text and numbers after the first block of text and numbers, which in the case of Monaco7190Australia1484 is Australia1484. What’s tricky about this challenge is that we have a block of text, followed by a block of numbers, followed by the block of text/number that we actually want to extract. So it’s hard to pinpoint the location of that 2nd block of text/numbers given there’s a preceding block of text/numbers. Despite having a head start of several weeks on this challenge (I first conceived of the approach when answering a post a weeks before posting the problem as a challenge) and despite (or perhaps because of) taking copies amounts of performance-enhancing drugs, I didn’t even make it onto that Olympic podium. Instead, I arrived huffing and puffing at the finish line with this unwieldy beast: =MID(A1,1+MATCH(1,(CODE(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1))<58)*(CODE(MID(A1&”a”,ROW(A$2:INDEX(A:A,LEN(A1)+1)),1))>57),0),LEN(A1)) The approach I took was to try to find the point at which we have a number immediately followed by a letter. For instance, the zero and letter A from Monaco7190Australia1484. To do this: 1. I dynamically break the string apart into one-digit bites with this: = (MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1) Excel returns: ={“M”;”o”;”n”;”a”;”c”;”o”;”7″;”1″;”9″;”0″;”A”;”u”;”s”;”t”;”r”;”a”;”l”;”i”;”a”;”1″;”4″;”8″;”4″} 2. Wrap a code function around that which converts these to the specific number that Excel uses to represent each number/letter: =CODE({“M”;”o”;”n”;”a”;”c”;”o”;”7″;”1″;”9″;”0″;”A”;”u”;”s”;”t”;”r”;”a”;”l”;”i”;”a”;”1″;”4″;”8″;”4″}) Excel returns: ={77;111;110;97;99;111;55;49;57;48;65;117;115;116;114;97;108;105;97;49;52;56;52} 3. Work out which of those codes represents numbers. ie: All numbers fall before character code 58 ={77;111;110;97;99;111;55;49;57;48;65;117;115;116;114;97;108;105;97;49;52;56;52}<58 Excel returns: ={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE} 4. Repeat steps 1 to 3 but this time I check for the occurrence of letters. Furthermore I make a change so that the resulting array is offset by one position, by changing the ROW(A$1… bit in 1 to ROW(A$2… : =CODE(MID(A1&”a”,ROW(A$2:INDEX(A:A,LEN(A1)+1)),1))>57 Excel returns: ={TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE} 5. Multiply these two arrays together This converts these TRUE and FALSE numbers to Ones and Zeros. Furthermore, because that 2nd array is offset by one, this gives me the position where a letter and a number coincide. (Which – given that 2nd array has been offset – is equivalent to identifying the occurrence of where a number is immediately followed by a letter: ={FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE} * {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE} Excel returns: ={0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1} 6. All I need to do then is to identify where that first 1 falls in this array: =MATCH(1,{0;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;1},0) Excel returns: =10 7. …and then return all of the original string to the right of that position: =MID(A1,1+10,LEN(A1)) Excel returns: =Australia1484 Summary Neat, eh? Well, I thought so. In fact, I thought that would be a sure-fire winner of the challenge. But that didn’t even get me the Bronze. Rather, that honour belongs to Sajan, who scooped up the consolation prize with this little beauty: =MID(A1,MODE(MMULT((N(ISNUMBER(-MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)))={1,0})*(ROW(INDIRECT(“1:”&LEN(A1)))-{0,1}),{1;1}))+1,LEN(A1)) …which requires you to pre-emptively take a whole bottle aspirin AND get a good nights’ sleep before trying to wrap your head around it. So go take that aspirin, take your pens out of your pocket, and hit the pillow, and I’ll see you back here this time tomorrowfor some Einsteinium formula relativity as we tackle this gem of an approach in Part 2. About the Author. Jeff Weir – a local of Galactic North up there in Windy Wellington, New Zealand – is more volatile than INDIRECT and more random than RAND. In fact, his state of mind can be pretty much summed up by this: =NOT(EVEN(PROPER(OR(RIGHT(TODAY()))))) That’s right, pure #VALUE! Find out more at http://www.heavydutydecisions.co.nz Comments from Hui: Jeff has been a valuable contributor at Excel Hero Academy during the past two years and recently started posting at theChandoo.org Forums This is Jeff’s First post at Chandoo.org and I am sure you will welcome his verbose narrative to these interesting Formula Challenges

Never use simple numbers in your dashboards (bonus tip: how to fix default conditional formatting)

Pop quiz: What is wrong with below report? At first glance, it looks alright. But if you observe closely, you realize that it is not telling the entire story. Just looking at regional sales numbers, you have not much clue what is going on with them. So how to improve it? 1. Add context In order to know whether a number like $120,000 sales in South is good or bad, you need to provide some context. For example, if you include previous month sales figures, suddenly $120k is comparable to some other number. This tells a better story than a simple number alone. You can also try these, Target values Same month last year values YTD, QTD values 2. Add % Change When you have 2 numbers like $120k and $110k in a report, anyone looking at them are going to mentally calculate the % change from last month to this month. This is easy for numbers like 120 and 110, but if your numbers are like 36,450 and  43,150 then calculating % change values will take time. Why force your audience to do this mental math? Instead show these %s on the report. 3. Highlight bad numbers Another way to enhance your report is to highlight poorly performing regions. Since each region is different, comparing sales of one with another is not good. But you can compare % change (from previous month / same month last year / targets etc.) and highlight poorly performing regions. This can be done with conditional formatting. So lets go ahead and do it for our report above. 3.1 Add conditional formatting Just select the %change column, go to conditional formatting > icon sets > and choose an arrow icon set that you fancy. 3.2 The default formatting kinda sucks We are not done yet. If you look at the default icon formatting, it looks in-accurate. We are seeing red colored, down-ward arrows even when there is a positive change. And, when the % change is negative, we no longer need minus sign (-) because it will be indicated by down arrow. 3.3 Fix the conditional formatting icons Select the cells again, go to home > conditional formatting > manage rules. Select the rule and edit it (you can double click on the rule to edit). Change the rule type as shown below. 3.4 Remove the minus sign Select the %change column once again, go to format cells (ctrl+1) and set the custom formatting code 0%;0% This will make sure that even when the percentage is negative, Excel will not show the sign (minus symbol). Related: More on custom cell formatting in Excel. So there you go. A regional sales report that tells better story. Key ideas to keep in mind In your reports, try to provide as much context as possible. This can be done by providing comparisons including additional statistics (sum, count, median etc.) indicating the time frame of the report highlighting bad numbers or areas that require attention giving user a choice to change report criteria (interactive features). Do you follow these principles when making reports or dashboards? I try to observe these ideas in all my dashboards. What about you? Are you using simple numbers in your dashboards? Go ahead and tell us how you are making your dashboards better, in comments. Analyze data and make reports / dashboards often? If your job involves data analysis, reporting & dashboards, then you will love our Excel School program. In this online course, you will learn how to use Excel to analyze data with formulas & pivot tables, highlight important stuff, create stunning charts & tables, make them interactive and put everything together to weave an informative dashboard & more. Please click here to know more about Excel School program and join us.

5 Keyboard shortcuts for writing better formulas

5 Keyboard shortcuts for writing better formulas:
As an analyst (or manager), I bet a good portion of your Excel time is spent writing formulas and getting the results.


So today, let us learn 5 important keyboard shortcuts that will save you a lot of time and help you write better formulas.

1. F2: Edit a formula cell. When you select a cell and press F2, Excel places cursor at the end and lets you edit the cell value / formula.
2. F3: Paste names in to formula. When you have a lot of names, often remembering them can be tricky. Whenever you want to type a name, press F3 instead. Excel will show a list of all names and pick what you want.
3. Tab: Auto-complete functions, names, structural references. As you start typing a formula, Excel shows auto-complete suggestions. Use arrow keys to select the function, name or structural reference you want, Press Tab key to let Excel type it for you. You can save a ton of time by just arrow key + tabbing.
4. F4: Switch reference styles – Absolute > Mixed > Relative > Absolute. When typing formulas, often you may want to change a certain cell reference to Absolute or Mixed or Relative. You can use F4 key to do the switching. Just place cursor inside the range / cell address and press F4 to cycle thru all available reference styles. (more: Guide to Excel cell references and when to use what?)
5. ALT + ESF: Paste Formulas only. If you just want to make a copy of the formulas and omit cell formatting etc., copy the cells with formulas, go elsewhere, press ALT + E and then S and F.  And you get a copy of the formulas alone.

Bonus Shortcuts & Tricks:

Writing and editing formulas is such an important part of Excel that there are many other useful shortcuts and tricks. Here are a few of my favorites:

• F9: Evaluate selected portion of a formula. Select a portion of a formula and press F9 to evaluate it alone. See the results and debug easily. (related: debug formulas using F9)
• CTRL + ` : Show formulas. Very useful when explaining your worksheet to others. Press CTRL + ` (back quote, usually the key above tab on left) to on / off show formulas mode. (related: auditing formulas & spreadsheets)
• Use mouse to edit formula ranges: When you select a cell with formula and edit it (by pressing F2), you can see these blue, green, red rectangles around the cells to which the formula is talking. You can move these rectangles or resize them to edit the formula input ranges. Very useful and very time saving. (more: Using mouse to save time in Excel)
• Fill down / side ways: Once you have a formula in a cell, you can drag it down or sideways (using mouse) to fill the formula down or across.

What are your favorite shortcuts for writing / editing formulas?

I am sure there are tons more shortcuts that I have omitted. So go ahead share your favorite ones in comments. Teach us something new. Go.
Also check out: Comprehensive list of Excel keyboard shortcuts.

Introduction to Structural References

Introduction to Structural References:
Ever seen a formula like =SUMIFS(Sheet1!B2:B3923, Sheet1!C2:C3923, A1, Sheet1!D2:D3923, A2) and wondered what it is really doing?!?
If so, you are not alone.
Formulas written with cell references tend to look complicated and clunky. What if we could write formulas in plain English?
That is what Structural References do. When using structural references in formulas, your focus will be on your data, not on which cell ranges the data takes up.
For example, you can write formulas like these:

• SUM(mySales[no. of customers]) to find how many customers we had.
• SUMIFS(mySales[no. of customers], mySales[product], “FastCar”) to find how many customers bought “FastCar”

Learn how to use Structural References and why they can change you life

Introduction to Structural References [video]
[Watch this on our YouTube channel]

Download Example File

Click here to download example workbook and play with Structural References to learn more.

Learn more about Tables & Structural Refs

• Introduction to Tables in Excel
• Using Tables with VLOOKUP formulas
• How to preserve table column references
• Customizing table formatting

Do you use tables & structural references ?

I use tables all the time. They help me stay focused on analysis & visualization instead of cell addresses.
What about you? Do you use tables & structural references? Please share your experiences, tips & ideas using comments.
Bonus Video: Here is a video I did with Bill Jelen (MrExcel) about this topic.
Personal update: We are at beautiful Woodbury Inn on Blue Ridge Pkwy and loving our stay. See this video.

How to get VLOOKUP + 1 value?

How to get VLOOKUP + 1 value?:
Here is a question someone asked me in a class recently.

I know how to use VLOOKUP to find a value based on search term. But I have a slight variation to it. I need to extract value below the cell VLOOKUP finds.

This is simpler than it sounds.
We can use INDEX + MATCH formulas to do this.
The syntax is like below:
=INDEX( value column, MATCH (search what, search column, 0) + 1 )
Why it works?
MATCH formula finds the position of what you are searching. By adding 1 to it and extracting the corresponding “values column”, we can get VLOOKUP + 1 value.
Homework for you
If you think finding VLOOKUP+1 is easy then I have a challenge for you.
Find the last match. Lets say in a table you have multiple items matching lookup value. How would you find the last item. Assume what you are finding is in A1, list is in C1:D20 and we want the value in 2nd column.
Go ahead and post your answers in comments section.

Are you ready for 2,000 miles, 15 days & 10 Excel tips road trip?

Are you ready for 2,000 miles, 15 days & 10 Excel tips road trip?:

Finally my Excel classes in USA are over. It was a lot of fun traveling to new cities, teaching Excel & dashboards to enthusiastic crowds and making new friends. As if that is not fun enough, we (Jo, kids & I) are going on a 2,000 mile, 2 week road trip starting today.
Although I am enjoying all this, I also feel bad for not taking enough time to share new tricks, ideas & techniques with you here. So, I have a wacky, wild & awesome plan for you. Join us on our road trip.
That is right. You can join me on our road trip and see what I see, learn some pretty cool Excel tricks, all while sipping coffee and stretching legs in the comfort of your office cubicle. No oppressive summer heat, no driving thru a long turn-pike looking for next rest area while your daughter is screaming in the back; ‘daddy, I need to go now.’ or no  drinking three coffees in a row so that you can access free wi-fi and check your email.
So buckle up. Err, I mean unbuckle and join us on this road trip.

What is going to happen?

Simple. While I am on road, each day I will be posting a new Excel tip or article. I will accompany it with a short snippet of what we are doing, include a photo or two of the beautiful things we are seeing.

Sounds Exciting, What should I do?

Nothing really. Just follow our journey for next 2 weeks. May be tell your colleagues or friends about it so that they too can enjoy. And if you happen to be in Raleigh or Pittsburgh or DC, ping me. We may be able to catch up.

So where are we going?

Well, I could tell you all about our Road trip using a map or several bullet points. Heck, I could even use a fancy image to tell you what we are up to. But you know that is not my style. So I have the right thing for you. So I present to you…,

Interactive Map of our Road Trip

That is right. First take a look at our road trip details:

How is this chart / map / interactive thingie made?

I wish I can sit here and type out all the little details about this. But I must rush now and pick up our rental car. So here is the recipe in a nutshell.

1. First I created a Google Map with all the locations we are visiting. (link)
2. Took a screenshot of the map. Embedded it in to Excel and cropped it.
3. Assuming the top left of the map is (0,0) and bottom right is (2.2, 3.2) figured out the x,y co-ordinates for all the markers by trial and error. (Hint: apply grid lines at 0.1 so that you can easily find marker positions).
4. Then created an agenda table with below structure.
   
   
5. Added a scrollbar form control and set it from 1 to 16. Linked it to a blank cell. (related: Introduction to form controls)
6. Using scrollbar selection, fetched corresponding row details from above table.
7. Created an XY scatter plot with (x1,y1) and (x2,y2) values for selected row.
8. Set cropped map image behind the chart’s plot area and resized things until they align ok.
9. Using text boxes, fetched various details for selected scrollbar value (date, heading, details, Excel tip).
10. Color and format so that everything looks good.

So there you go. Now, you know how to create your own map / chart / interactive thingie.

Download Road Trip Interactive chart

Click here to download the workbook and play with it. Examine the way chart is set up (ungroup it to move things) and various INDEX formulas to understand this better.

More on Maps & Excel

If you like to combine maps & data, then Excel is not going you help you much (unless you are using either Power View or Geo Flow). But there are a few ways to get maps in Excel. Check out below examples to learn more.

• Lithuania at a glance (Choropleth maps in Excel)
• Count-down timer on a map
• Visualizing Olympic medals by country
• Tracking Hurricane Sandy in Excel

So see you tomorrow, from Waynesboro (VA). Until then, stay in your lane and cruise.

We Want You – Revisited

We Want You – Revisited:

Chandoo.org Wants You

Over the past 5 years Chandoo has written about 1,500 posts on all things Excel and Hui has contributed another 80 posts mostly targeted at the application of Excel techniques to real life situations.
Two years ago we asked you “Was what we were posting actually what you wanted to see ?”
http://chandoo.org/wp/2011/06/22/we-want-your-ideas/
71 people responded with 101 ideas which were summarized here:
http://chandoo.org/wp/2011/07/22/we-want-your-ideas-results/
But that was two years ago
So again we’re opening the floor to you, with a single question:
What would you like to see discussed in future posts at Chandoo.org ?
Your ideas can be as specific or general as you like:
One Rule only: The Idea must involve the Functionality, Use or Application of Excel !
We cannot guarantee that your idea will result in a Post, But if you don’t ask, you won’t receive
We will do our best to schedule posts where most requested and suitable skills and time is available by authors.
So, What would you like to see discussed in future posts at Chandoo.org ?
Let us know what you’d like to see in future posts in the comments below:

A quick Excel tip while on bike…

A quick Excel tip while on bike…:
As you may know, I am in USA and having a lot of fun exploring new areas, meeting people and conducting live classes. While all of this is enjoyable, I am also feeling guilty because I am unable to squeeze time to share Excel tips on the blog.
So to make it up to you, I recorded this short video (2 mins) with an Excel tip while bicycling in beautiful Euclid Creek Reservation in Cleveland on Friday. Watch it below.


[Watch this video on our youtube channel]
Related: More keyboard shortcuts and a game to test your Excel keyboard skills.