Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

Using Excel for Business Analysis by Danielle [Book Recommendation]

Business analysis & modeling are new areas where a lot of fresh graduates find themselves when they start work. Unfortunately, this is also an area where there is very little structured information. Thanks to Danielle’s new book, Using Excel for Business Analysis, you can understand the basics of financial modeling, good spreadsheet design & business analysis

First up, let me congratulate my good friend Danielle for the successful launch of her first book. Today, I want to share my review of this book with you.
Who is this book for?

You are someone who uses Excel to analyze & model parts of a business,
You are not sure how to go about it
You started using Excel in last few years and lack structured approach to spreadsheet design
You are a fresh MBA hoping to become an analyst

Then this book is for you.

Read This »

September 21, 2012

Formula Forensics No. 029 SumIf with Inconsistent Column Layouts

How do we perform a Sumif on seemingly randomly spaced columns?
Today in Formula Forensics we look at a solution to do just that.

Read This »

September 20, 2012

Customize Zebra lines Quickly using Table Styles [tip]

Zebra lines, the dull highlighting applied to alternative rows is a very good way to make your tables readable & pretty.

We can use either conditional formatting or table formats to quickly add zebra lines to our data.

But what if you want a little more?

What if you want to highlight, lets say 3 rows in one color and 3 in another and repeat this …

Read This »

September 19, 2012

OFFSET formula – Explained

Today, lets learn OFFSET formula.
OFFSET formula gives us reference to a range, from a given starting point with given height and width in cells.

OFFSET formula syntax
OFFSET formula looks like this:
=OFFSET(starting point, rows to move, columns to move, height, width)

Starting point: This is a cell or range from which you want to offset
Rows & columns to move: How many rows & columns you want to move the starting point. Both of these can be positive, negative or zero. More on this below.
Height & width: This is the size of range you want to return. For ex. 4,3 would give you a range with 4 cells tall & 3 cells wide.

Read on…,

Read This »

September 17, 2012

How many values are common in 2 lists? [homework]

Here is a formula challenge for you. Lets say we have 2 lists of values in A1:A10 & B1:B10 Now, how do you find the

Read This »

September 14, 2012

Interactive Pivot Table Calendar & Chart in Excel!

Can we make a calendar using Pivot Tables?!? Of course we can. Today let us learn a simple technique to create calendar style reports using

Read This »

September 12, 2012

A Spreadsheet walks in to a bar … [open mic]

Lets have some fun this Friday.

Post your favorite Spreadsheet jokes using comments. Have a big smile.

I will start:

1. A pivot table walks in to a bar and orders a beer. It says, “Put me in the same tab, will ya?”
2. A slicer and pivot chart walk in to a bar. The bartender says, “look at those bast*rds, walking around with out a pivot!”.
3. Once Excel, Access and Windows were bragging to each other. Excel says, “I once crashed so hard, it took 5 minutes to recover”. Access says, “Oh thats nothing. I once crashed and took down an entire data base. It took them 30 minutes to recover”. Windows doesn’t say anything. Excel pokes him in the arm asks “what about you?”, Windows jolts & replies, “Sorry, what did you say, I just crashed again.”

Read This »

September 7, 2012

Formula Forensics No. 028 – It’s Just a Jump to the Left

VLookup is often seen as the poor cousin in the lookup function family and often gets overlooked when it comes to looking up values due to a common misconception that Vlookup doesn’t or can’t lookup values to the left of the reference value.
Today at Formula Forensics we will explode this myth and see how to make VLookup, lookup to the Left and we’ll explain how it works.

Read This »

September 6, 2012