All articles in 'Excel Howtos' Category

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

Announcements

• Registrations for next batch of VBA Class start form January 11th (Wednesday). Please click here to download the course brochure.
• 20% Holiday discount on Excel School expires tonight (Midnight, Pacific Time). Please visit Excel School page and use the code LETSGOEXCEL to claim your discount.

Read on for a bonus Excel tip as well.

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

Maintenance on the 18 month old, Data Tables, Monte-Carlo Simulations and Fractals in Excel – A Comprehensive Guide has been completed.

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…

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

Occasionally you may see some Bold or Code highlighted like

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

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

Chandoo is off on Holidays and once again thrown me the keys to Chandoo.org.

So this week we’re going to:

• Pull apart an Array Formula and see what’s under the hood;
• Have a look at a technique for quickly developing Custom Number Formats &
• Look at how we can use HTML Codes in posts at Chandoo.org to spice up your questions and answers.

Today is Tarun’s Problem

“I have got multiple names in each row and would like to have what name is repeated maximum number of times and how many times?”

Today we pull apart an answer to this question and see how it works.

Comparison is one of the most common things we do with Excel. Naturally, there are so many ways to compare 2 lists of data using Excel.

Today, I want to share an interesting comparison problem with you.

Lets say you run a small shop which sells some highly specialized products. Now, since your products require quite some training before customers can buy them, you keep track of all product queries and arrange demos.

After a hectic week, you are staring at 2 lists. One with product queries, another with product demos. And you want to know whether all the queries are answered with a demo or not.

This post is authored by Martin, one of our readers.

Situation:

Sometimes I encounter data in my tables with blank cells where there is a repeated value from the cell directly above. See below:

This can be annoying when it comes to interpreting the data and when sorting columns.

Solution:

To know how I solve this, just read rest of the article.

Filtering is a simple yet powerful way to analyze data. When you apply filters to any list of values, Excel gives you some really useful pre-defined filters so that you can analyze the data in a variety of ways. So, assuming you have data like this: We all know how to filter data for Bananas. […]

Lets just say, you run a nice little orange shop called, “Joe’s Awesome Oranges“. And being an Excel buff, you record the daily sales in to a workbook, in this format. After recording the sales for a couple of months, you got a refreshing idea, why not analyze the sales between any given 2 dates? […]

Do you enjoy printing complex spreadsheets with multiple pages with different print ranges and different setups on each page for different people who have different requirements ?

If the answer is No…

This post may save your day and change your life.

Hafiz, One of our avid readers, writes in. My problem is when I convert data from text to column using dash “-“, conversion is easy. but when the gap provided in text is with “alt+enter”, I can’t convert the data.

Well, I tried to use text to columns feature (from Data ribbon) and it would not work.

Although you can use formulas to do the splitting, they might become tedious. So the next logical option is to use macros.

In this article, learn how to use VBA to split text on New Line characters

Last week I introduced the technique of using custom Number Formats for Chart Axis labels.

Today I present a few more alternative layouts including Chart Series Data Label Formats.

A few weeks back, John asked a post question, “ How can I add a £ to the 80 on a Chart axis and leave the rest of the numbers plain numbers ? ”

Custom Number Formats, That’s How.