Archive for September, 2012

18.2 Tips on Rounding numbers using Excel Formulas

Published on Sep 28, 2012 in Excel Howtos, Learn Excel
18.2 Tips on Rounding numbers using Excel Formulas

Lets talk round numbers today.I have 18.2 tips for you on round numbers.

We can use a variety of formulas to round numbers in Excel depending on the situation. We have ROUND, ROUNDUP, ROUNDDOWN, MROUND, INT, TRUNC, CEILING, FLOOR, FIXED, EVEN, ODD and few more. To know how to use all these formulas and how to round numbers based on any criteria, just read on.

Continue »

Introducing Excel School + Excel Hero Academy Bundle

Published on Sep 25, 2012 in Learn Excel, products

Hello friends & Chandoo.org readers,

I am super-excited to announce an my first joint Excel course with none other than Excel Hero – Daniel Ferry. For the the first time, you get basic, intermediate, advanced & super-advanced Excel + VBA course in one neat package.

I am happy to present Excel School + Excel Hero Academy course bundle for your consideration.

What is this course bundle & How it can help you?

Simply put, this course package is designed to make you 2x awesome in Excel, VBA & Dashboards. Please watch below video to understand how our course can help you.

Excel School plus EHA bundle Introduction

Continue »

Confessions of a dad [personal]

Published on Sep 24, 2012 in personal
Confessions of a dad [personal]

Today is my kids’ (we have twins – boy & girl) 3rd birthday. As we celebrate the day, I want to take a moment away from all Excel stuff and share my experience being a dad. Grab a cup of coffee or whatever keeps you going, relax and read on.

Continue »

Using Excel for Business Analysis by Danielle [Book Recommendation]

Published on Sep 21, 2012 in Movies-Books, products

Using Excel for Business Analysis by Danielle [Book Review]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.

Continue »

Formula Forensics No. 029 SumIf with Inconsistent Column Layouts

Published on Sep 20, 2012 in Formula Forensics, Huis, Posts by Hui
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.

Continue »

Customize Zebra lines Quickly using Table Styles [tip]

Published on Sep 19, 2012 in Excel Howtos
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 …

Continue »

OFFSET formula – Explained

Published on Sep 17, 2012 in Learn Excel
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…,

Continue »

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

Published on Sep 14, 2012 in Excel Challenges, Excel Howtos
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 number of common values in both lists? We just want the count, not list of common values it self. Go ahead and figure out the formula and post your answers […]

Continue »

Interactive Pivot Table Calendar & Chart in Excel!

Published on Sep 12, 2012 in Charts and Graphs, Excel Howtos, VBA Macros
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 Pivot tables. Thanks to Rob for inspiration Before making any progress, let me thank Rob from PowerPivotPro for the inspiration. Recently he wrote an article explaining how to use PowerPivot […]

Continue »

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

Published on Sep 7, 2012 in Humor

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.”

Continue »

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

Published on Sep 6, 2012 in Formula Forensics, Huis, Posts by Hui, Random
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.

Continue »

Show Decimal Points if needed [Quick Tip]

Published on Sep 5, 2012 in Excel Howtos
Show Decimal Points if needed [Quick Tip]

Sometimes you want to turnoff decimal points if the value after point is 0. Mireya, Chandoo.org member had one such situation. She writes:

I am a complete beginner in excel, how can I keep the zeros when I am working with decimals and remove them when are not required.

This is where we can use General formatting. Read on to learn how General formatting works and what to do in extreme cases.

Continue »

Excel Links – Going to Togo Edition

Published on Sep 4, 2012 in excel links

After a long time, I am writing one more Excel links post. First let me share a quick personal update.

I am invited to Lome, Togo to spend a day at Ecobank – CFO conference in 3rd week of September. I will be conducting a one day masterclass on Advanced Excel & Dashboard Reporting. I am excited about this trip as I have never been to any African country. I will post some pics and tell you how it went once I am done with the conference.

Moving on to Excel Links for today,

Continue »