All articles in 'Excel Howtos' Category

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.

The other day, while I was putting my kids to sleep, this idea came to me. How do I check if a cell contains a palindrome, using Excel formulas?

Next morning, I wrestled with excel for about 20 minutes and boom, the formula is ready.

But what is a palindrome?
A palindrome is a word, phrase, verse, or sentence that reads the same backward or forward. For example: A man, a plan, a canal, Panama!
So, to check if a cell contains palindrome, we need to reverse the cell contents and see if both original and reverse are the same.

You’ve been there before. It’s almost 5:00, and you are going crazy trying to finish the presentation due for a monthly performance meeting the next morning. The model is refreshed, and now it just takes a LOT of copying, pasting, and positioning to get the PowerPoint ready. Finally, the slides are finished…, until you read a new message from your boss requesting a minor change. But of course her change means you have to start all over with the copy and pastes…

There is always a better way! In the Oil and Gas industry, I constantly have monthly reports to assess the performance of our operating assets. Excel VBA makes it a cinch to automate the entire process. So when a simple change is requested, the presentation is automatically generated with the click of a button. No more wasting time!

Some of you have heard my neighbor’s dog bark in a video or two that I recorded. While I was busy explaining how to do something awesome in Excel, this dog would decide to bark, adding her own two cents to the lesson. Quite a few of my VBA class students have grown used to it. So much that they complain when a lesson doesn’t have a couple of woofs. But I digress.

So coming back, one of the dogs (probably stray) has decided that she should bring her infant puppies and hide them under our terrace stair case. So, now we have 2 cute little puppies barking day long (and very late in to the night) just outside my office window. We have tried hard to get rid of them, but they somehow sneak back in and start barking or crying. So, I will be busy this weekend trying to move them out.

But that doesn’t mean, you have to live Excel-less for a few days. So I have a homework.

OR XOR AND, Get busy this weekend!

Don’t worry. I am not speaking elvish or something. OR, XOR & AND stand for bit-wise operations. This week, your task is to write formulas in Excel that would get the bit-wise results for AND, OR & XOR.