Last week, we learned how to visualize Suicides vs. Murders data using Interactive charts in Excel.
William, one of our readers, took this technique and applied it to Stocks. He emailed me because he has some formula issues with the stock data. Once I solved the problem, I asked him, “Can I share this with our readers?” and he is too happy to agree. So here we go.
Interactive Analysis of Stocks using Excel
(Data as of 20SEP2011, 4PM EDT, from Yahoo Finance)
How does this Work?
This workbook is just a clone of Suicides vs. Murders visualization with different data. The only change is that, here we used LARGE and MATCH formulas instead of COUNTIF formula to sort the list.
Learn more about this technique is from KPI Dashboard Sorting article.
Download Stock Performance Analysis Workbook
Click here to download the workbook & play with it. Just change the data & formulas in “Share Data” worksheet to modify this.
Do you Like This?
I really liked how William put this together. It is simple and yet, quite powerful.
What about you? Do you like this technique? Are you planning to use it anywhere? Please share your ideas using comments.
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.