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.
Consolidating Multiple Sheets of Data in to One
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?.
Getting Data to Excel from a website that requires log-in
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.
Show selected cells info in Excel Status Bar
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?’.
Interesting Chart on Increase in Housing Quality since 1940
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.
Interactive Dashboards in Excel – the next level
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.
12 Responses
But… Is the housing quality chart done with Excel?
Hi chandoo! As u told to share something with our reader, i already sent one excel file to ur e-mail with the subject “NUMERIC TO WORD CONVERTER”. But yet there is no response. This file convert numeric digit to word just with the help of formula. So, Plz check and revert.
A really great Excel website that I recommend to everybody is excelunusual.com
It covers many impressive Excel applications, and the explanations are very easy to follow.
Congrats India, though I thought it was nice of the Aussies let another team win the WC for a change 🙂
Yes, very polite of the Aussies…..
Chandoo, I have noticed a change since first viewing the site around 3 yrs ago.
Originally it seemed you had emphasis on Excel “without” VBA or macros.
But now these feature more regularly.
I don’t have an opinion about whether they should or shouldn’t be included, but just thought it was interesting to see the change in the site over time.
Keep up your good work, and congrats to Team India!
Christian
@Istiyak
Have you seen: http://support.microsoft.com/kb/213360
@Carlos
No it’s not done in Excel, But there is no reason it couldn’t be
@Hui
It would be very interesting to see how can we do it with Excel. It’s hard to imagine (at least for me). Any brave Excel expert can help us?
I see that we have some strong Australian supporters 🙂
@Pablo.. I am aware of excelunusual.com. I read it thru my google reader. I wish to share few of his articles thru next editions of excel links.
@Christian: I still try to avoid VBA as much as possible. But I have realized its power overtime and I want to experiment more with it. Also, we will be opening VBA School shortly, so it makes sense to share what I am learning.
Though I prefer to do things with Excel (Formulas)itself, bt some times VBA can be used as a Top Up fecility..Readers can make use of the following VBA code(preferably keep in your Personal file) to consolidate sheets in the given file.
Sub ConsolidateSheets()
Dim wssheet As Worksheet
Dim lngLastRow As Long
Sheets.Add
Range(“A1:Z1”).Value = “Header”
strConsolidate = ActiveSheet.Name
ActiveSheet.Name = “Consolidate”
For Each wssheet In ActiveWorkbook.Worksheets
If wssheet.Name “Consolidate” Then
wssheet.Select
lngLastRow = ActiveSheet.Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range(“A2:Z” & lngLastRow).Select
Selection.Copy
ActiveWorkbook.Sheets(“Consolidate”).Select
lngLastRow = ActiveSheet.Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Range(“A” & lngLastRow).Select
ActiveSheet.Paste
End If
Next wssheet
End Sub
@ hui : i am not searching for it but i had sent it with email for our users. u have to check it in mailbox of chandoo.
Hi Chandoo,
I’m glad you are going to offer the VBA school, definitively I will sign up for that one.
At what level are going to take it, from beginner to expert?
Cheers!
“We should accept that Millennials are an important part of our company,but different from others.So we need to take into account their belliefs and lifestyle so they can give 100% at work.That’s good for the company,right?”