All articles in 'Excel Howtos' Category

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 »

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 »

Custom Number Formats (Multiply & Divide by any Power of 10)

Published on Jan 31, 2012 in Excel Howtos, Huis, Posts by Hui
Custom Number Formats (Multiply & Divide by any Power of 10)

In the past here at Chandoo.org and at many many other sites, people have asked the question
“How can I display a number multiplied or divided by 10, 100, 1000, 1000000 etc, but still have the cell maintain the original number for use in subsequent calculations”.

Typically the answer has been limited to “It can’t be done” or “it can only be done in multiples of 1000”.

This post will show you how you can display numbers whilst Dividing or Multiplying the cells value by any Power of 10 !

Continue »

Cleaning Up Imported Data – A Recent Case Study

Published on Jan 25, 2012 in Excel Howtos, Huis, Posts by Hui
Cleaning Up Imported Data – A Recent Case Study

Houston, We’ve Had a Problem!
In the initial emails requesting a solution to yesterday’s Formula Forensics, Chandoo’s solution, although Technically correct, Didn’t work ?
This post looks at the problem and what was wrong with the data causing the error.

Continue »

Formula Forensics. 009 – Pradhishnair’s Chainage Problem

Published on Jan 17, 2012 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics. 009 – Pradhishnair’s Chainage Problem

A common Forum Post question and one that Chandoo has written about a few times is, Does my data overlap with another range?

This week Formula Forensics examines Pradhishnair’s Overlapping Chaninage Problem where he wants to know if two values overlap with a range of other values

Continue »

Use CTRL+Enter to Enter Same Data in to Multiple Cells [Quick Tip]

Published on Jan 9, 2012 in Excel Howtos
Use CTRL+Enter to Enter Same Data in to Multiple Cells [Quick Tip]

Here is a quick Excel tip to kick start your week.

Sometimes, we want to enter same data in to several cells. You can use CTRL+Enter to do this in a snap.

(1) Select all the cells where you want to enter the same data.
(2) Type the data
(3) Press CTRL+Enter
(4) Done!

See the animation aside to understand how this works.

Continue »

Quick Update about VBA Classes & Discount Expiry!

Published on Jan 5, 2012 in Excel Howtos, Learn Excel

I have 2 quick announcements & 1 Excel tip for you.

Announcements

Read on for a bonus Excel tip as well.

Continue »

8 Tips to Make you a Formatting Pro

Published on Dec 5, 2011 in Excel Howtos, Learn Excel
8 Tips to Make you a Formatting Pro

We can take any Excel workbook and format it until Christmas, and we would still not be done. But not many of us have so much of time or energy. So, today, lets talk formatting.

In this, you will learn how to
1) Use tables to format data quickly
2) Change colors of your worksheet in a snap
3) Use cell styles
4) Quickly clone formatting using format painter
5) Clear formats to begin with a clean-slate
6) Formatting shortcuts
7) Formatting options for print
and 8 ) Why you should not go overboard formatting

So go ahead and become a formatting pro.

Continue »

Formula Forensics No.004 – Fred’s Problem

Published on Nov 30, 2011 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics No.004 – Fred’s Problem

.
.
.
.
.
.
.

This is the Forth post in Chandoo’s, Formula Forensics series.

Last week Luke showed us how to extract a sorted list according to a criteria from a larger list
and he analysed a formula to solve this problem

This week we look at Fred’s Problem…

How do I simplify a very long formula?

Continue »

Formula Forensics No. 002 – Joyces Question

Published on Nov 7, 2011 in Excel Howtos, Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 002 – Joyces Question

Formula Forensics # 002 – Joyces Question

This is the second post of a new regular weekly series at Chanoo.org, Formula Forensics, where we will examine how a formula works from the inside out with a worked example to help you understand its formation.

Last week we looked at Taruns problem and analysed a formula to solve his problem

This week we look at Joyce’s Problem…

Continue »

Fancy Posts – using HTML Display Codes in Chandoo.org Posts

Normally when responding to a Post or a Forum Post you will see a plain old text response like
Chandoo.org

Occasionally you may see some Bold or Code highlighted like
Chandoo.org

How is this done and what else can I do to add value / pizazz to my posts ?

Continue »

A Technique to Quickly Develop Custom Number Formats

Published on Nov 2, 2011 in Excel Howtos, Huis, Learn Excel, Posts by Hui
A Technique to Quickly Develop Custom Number Formats

This post examines a quick technique for developing Custom Number Formats for Cells, Charts or any other Number location in Excel.

Continue »