Excel Case Studies for Advanced Excel Users

[tcb-script async=”” src=”https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js”][/tcb-script][tcb-script] (adsbygoogle = window.adsbygoogle || []).push({});[/tcb-script]

Compare 2 worksheets

A participant came to me at the end of the course and asked if there is a easier way to compare 2 worksheets. This seems like a simple task of using VLOOKUP. Just enter the VLOOKUP formula into a column in one of the worksheet and refer to the other worksheet for comparative values.That’s what usually people need.

But for her problem, it is not so simple. Her worksheet which contains employee data (e.g. employee number, name, reporting manager, departmet) has multiple columns and rows and all the cells need to be compared against the other worksheet. To add to the challenge, the rows are not in the same order, meaning that the employee in row 10 may not be presented in Row 10 of the other worksheet. So that is the best way to compare the 2 worksheets?…. more details on compare 2 worksheets

Excel Calendar

This is a wonderful creation by John Walkenbach. The solution makes extensive use of the YEAR, MONTH, DAY and WEEKDAY formula. Understanding how Excel stores dates is a pre-requisite to understand how all the 4 formulas work as one. But another main ingredient is the use of array formula. The employ of the IF formula helps to clean up the monthly calendar so that only days related to that particular month is displayed.

The conditional formatting makes use of SUMPRODUCT, which I called a super formula. This formula can do wonders. Compared this formula with the new SUMIFs and COUNTIFs, it still wins. SUMPRODUCT allows the use of formulas within the conditions while SUMIFs and COUNTIFs do not. VLOOKUP and AND are also used in conditional formatting to generate this perfect solution. More details can be found in this excel calendar write-up.

Return to the top of case studies

New! Comments

Have your say about what you just read! Leave me a comment in the box below.

Share this page:

What’s this?

Enjoy this page? Please pay it forward. Here’s how…

Would you prefer to share this page with others by linking to it?

Click on the HTML link code below.
Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.

<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a> hiddensecretsbookcover hiddensecrets_online