All articles in 'Excel Howtos' Category

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 »

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 »

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 »