Archive for February, 2012

14 ways to check if an year is leap year, using Excel [just for fun]

Published on Feb 29, 2012 in Excel Howtos, Learn Excel
14 ways to check if an year is leap year, using Excel [just for fun]

Today is February 29th, and that means, this year we have one more day to be awesome. So lets celebrate it in Excel style!

Lets learn 14 different ways to tell if an year is leap year, using Excel Formulas.

Why 14? because, we are awesome like that.

Continue »

How to add your own Macros to Excel Ribbon [quick tip]

Published on Feb 27, 2012 in Excel Howtos, VBA Macros
How to add your own Macros to Excel Ribbon [quick tip]

Do you know that in Excel 2010 you can create your own Ribbon tabs and add anything to them, including your own macros? Today, we are going to take a look at this useful feature and learn how to add your own macros as buttons to Excel Ribbon. Steps to Add your own macros to […]

Continue »

Join my Excel Dashboard Masterclass in Australia [May-June 2012]

Published on Feb 23, 2012 in Excel Workshops

Hello folks, I have a super exciting news to share with you all. I will be conducting a series of Masterclasses on Advanced Dashboards & Excel in Australia this Autumn (May – June 2012). So if you live in (or nearby) Sydney, Melbourne, Canberra, Perth or Brisbane and want to one up your dashboard skills, […]

Continue »

Designing a Customer Service Dashboard in Excel [Part 1 of 4]

Published on Feb 22, 2012 in Charts and Graphs, Learn Excel
Designing a Customer Service Dashboard in Excel [Part 1 of 4]

Sawadee Krup folks. Today, we begin a new series on Chandoo.org – Making a Customer Service Dashboard using Excel. This 4 part tutorial teaches you,

Designing Customer Service Dashboard
Data and Calculations for the Dashboard
Creating the dashboard in Excel
Adding Macros & Final touches

Customer service is one area where a lot of data is collected regularly. Understanding all this and making business decisions is quite complex task. This is where dashboards shine.

Read on to learn how to design customer service dashboards.

Continue »

Formula Forensics 013. – On Vacation

Published on Feb 21, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 013. – On Vacation

Formula Forensics is on Vacation.

Formula Forensics is actually on a working Holiday and has visited the Excel Hero Academy this week.
So today in Formula Forensics we will showcase some of the work that have been derived from the learning’s at the Excel Hero Academy.

Continue »

Formula Forensics 012. – A Neat Formula

Published on Feb 16, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui

Chandoo.org;
Today we look at a very neat way of doing a complex Nested If or Vlookup style problem with a simple but beautiful Sumproduct based formula.

Continue »

Use Text Format to Preserve Leading Zeros in Excel [Quick Tip]

Published on Feb 15, 2012 in Excel Howtos
Use Text Format to Preserve Leading Zeros in Excel [Quick Tip]

Here is a quick tip to add awesome to your Wednesday.

If you want to enter numbers like 00023 or 023.340 or 23.34500 in your Excel sheet, you would notice that Excel magically removes leading zeros and trailing zeros (after decimal point) as the number 23 is same as 00023. But sometime, we want 00023, not 23. Then what?!?

Very simple, we use TEXT format instead of number format. Just select the cells where you are going to enter these numbers, and from Home ribbon > Number area, select “Text” as cell type. This tells Excel to treat any value you enter as Text, not as number. So when you type 00023, it will appear as 00023.

Continue »

Reporting Scenarios using Offset

Published on Feb 14, 2012 in Excel Howtos, Financial Modeling, Learn Excel
Reporting Scenarios using Offset

Project Managers often report financial numbers to the management. In a dynamic world, these numbers are usually based on a lot of factors that may or may not be under your control. So the top management demands that the numbers be reported as per different economic scenarios – Optimistic, Normal or Pessimistic. It is important […]

Continue »

Learn Statistics & Probability using MS Excel

Published on Feb 13, 2012 in excel apps, Excel Howtos, Learn Excel, simulation, VBA Macros
Learn Statistics & Probability using MS Excel

One of the most dreaded courses during my under-graduation is Probability, Statistics & Queuing Theory. We called it PSQT. I struggled to understand the significance and concept of this course as I could barely concentrate in the class. We had a professor, who is probably a genius, but the moment he started the class, I would magically fall in to one of my after-noon naps. When I woke up, we are either in the middle of an elaborate t-test or going thru intricacies of a Markovian queue.

This was all 11 years ago. Later in life, I have embraced the world of probability & statistics. I still fear queues. May be I will get there one day. 😉

A good understanding of statistics & probability theory is necessary if you want to model complex real-life problems using Excel or similar tools. Naturally, Excel has several functions, features & supported add-ins to help you in this area.

Today, I want to share some of this with you. This article is broken down in to 3 parts.

  1. Learning Statistics & Probability using Excel
  2. Downloadable Excel Workbooks to understand
  3. Full blown models & simulations in Excel
Continue »

How would you customize Excel after installing? [poll]

Published on Feb 10, 2012 in Excel Howtos, Learn Excel
How would you customize Excel after installing? [poll]

Recently, I bought a new laptop, because my old Toshiba died down. After installing the OS and other necessary tools (like browser, skype etc.), I have installed Office 2010. Since Excel is my bread and butter, I like to customize it so that I can get more work done. So today,let me share how I […]

Continue »

Formula Forensics 011. Lykes Formula

Published on Feb 9, 2012 in Formula Forensics, Huis, Posts by Hui
Formula Forensics 011. Lykes Formula

Lykes asked Formula Forensics “How does a formula work?” Lykes has been using the formula without understanding it.
The formula counts how many times the letters from a list of letters occur in a list of words.
Today we look at a Lykes question.

Continue »

Using external software packages to manage your spreadsheet risk [Part 4 of 4]

Published on Feb 8, 2012 in Financial Modeling, Learn Excel
Using external software packages to manage your spreadsheet risk [Part 4 of 4]

Background – Spreadsheet Risk Management

In the Managing Spreadsheet Risk series so far we have looked at the concept of spreadsheet risk and how to manage it both at a company level and at a spreadsheet level using Excel functionality. In this final article we are going to have a quick look at an example of spreadsheet auditing software.

What to look for in a Spreadsheet Risk Management Software

First off I should state that there is a wide range of spreadsheet auditing solutions in the marketplace of different types and styles and at a variety of costs. In this section I would like to take a little time to explain the criteria we applied when we were sourcing auditing software.

Continue »

Comparing 2 Lists with a Twist

Published on Feb 6, 2012 in Excel Howtos, Learn Excel
Comparing 2 Lists with a Twist

We love to compare. The instinct to compare leaves no one. Even my two year old twins compare their toys with each other (and fight).

It would make Excel hugely popular if Microsoft builds a handy data comparison tool right in to it. Alas, they have customizable ribbon, 3d effects & equation editor…

Since comparison is one of the main uses of Excel, we have written extensively about it here.

But there is always one more interesting comparison problem. Today, I want to share one such problem, based on a comment left by N-Man.

Continue »

Formula Forensics No. 010 Count How Many Times a List of Values Occurs in a Range

Published on Feb 1, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 010 Count How Many Times a List of Values Occurs in a Range

Count How Many Times a List of Values Occurs in a Range
(or How Can I Simplify My Formula)

Today in Formula Forensics we look at how to count how many times a range of values occurs within a Range of cells and in the process simplify a very nasty formula.

Continue »