Excel Links – Congratulations Team India Edition
Wow, what a weekend it has been. Indian cricket team has won Cricket world cup 2011. It has been a highly entertaining tournament and the finals were just incredible.
Congratulations to Team India for winning Cricket World-cup 2011.
I am tempted to make a dashboard of sorts to show the journey of Team India in this world cup. But I could not get much time to work on anything. I will post something this week to celebrate the victory.
Meanwhile, join me and congratulate the team.
Recommended Excel Reading
It has been a while since we had an Excel Links post. So here we go.
Consolidation of data spread across multiple sheets is one of the most time consuming and frequently done process in Excel. In this article from tipsindeed, Gobish explains how to do this using only formulas. The formula based consolidation technique uses SUMPRODUCT, SUMIF, INDIRECT, ADDRESS formulas. Read more to learn about this. For more on Consolidation, refer to Consolidating data in Excel – How to?.
Excel has a feature called as Web Queries [examples] to help us extract data from a website to your excel files. Now, this works very well if the website data is public. But what if the information you are trying to extract is behind a log-in screen? Web queries fail because they cannot login dynamically in to a site for you. Dick at DDoE shows us a macro based approach to dynamically submit the website login form to get thru to the data we want. Very interesting approach.
Excel status bar is quite helpful. It gives you summary statistics of the data you are working with. It tells you which mode you are in. It also lets you quickly start recording a macro and so much more. But it doesn’t tell you which cells you have selected. So Mike at DataPig blog wrote a simple macro that shows the selected cells address range and dimensions in the status bar. This could be useful if you select data frequently and vigorously scratch your head going, ‘wait a minute, what did I select?’.
This is an interesting chart on how the quality of housing (in US) has changed since 1940. It shows how the real value of a house has changed in the last 7 decades. What I liked most is that, it also shows a visual summary of various other things at the bottom along the timeline. Very interesting approach.
Robert at ClearlyandSimply shows us how much more we can get with Excel by combining features like conditional formatting, form & active-x controls and VBA. In fact, this is the same lesson he did for Excel School as a guest faculty. Very powerful ideas and quite elegant implementation too. Go ahead and explore the example on Robert‘s site.
Share a web site / resource with our readers
If you know a webpage or resource that can help us all learn Excel and become awesome, please drop the URL using comments. Also, you are welcome to suggest links for inclusion in Excel Links posts by emailing them to me at chandoo.d @ gmail.com.
You have a great week ahead.
PS: Cricket Worldcup victory image from Cricinfo.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« March 2011 is best month ever and other news||Give more details by showing average and distribution [Charting Tips] »|