All articles in 'Excel Howtos' Category

Clean data quickly with Flash Fill

Published on Aug 1, 2015 in Excel Howtos
Clean data quickly with Flash Fill

Excel has many powerful & time-saving features. Even by Excel’s standard, Flash Fill is magical. Introduced in 2013, Flash Fill is a rule engine to Excel’s fill logic. Every time you type something in a cell, Excel will try to guess the pattern and offers to fill up the rest of cells for you. That is some serious time saving magic.

Let’s understand what Flash Fill is and few sample use cases.

Continue »

How to find out if a text contains question? [Excel formulas]

Published on Jul 17, 2015 in Excel Howtos, Learn Excel
How to find out if a text contains question? [Excel formulas]

On Wednesday (15th July), I ran my first ever webinar, on a topic called, “How to be a BETTER Analyst?” (here is the replay link, in case you missed it).  It was a huge success. More than 1,100 people attend the live webinar and hundreds more watched the replay. As part of the webinar, we had interactive Q&A. Viewers posted their questions and I replied to as many of them as I can.

After the webinar, I wanted to make sure I covered all the questions. So I downloaded the chat history. There were more than 700 messages in it. And I am not in the mood to read line by line to find-out the questions. A good portion of chat messages were not questions but stuff like ‘hello everyone, I am from Idaho’, ‘Wow, Chandoo has beard!”, “Enjoying a beer in Belgium while watching webinar” etc. So I wanted a quick way to flag the messages as question or not.

Continue »

Use Paste Special to multiply (or add, divide etc.) a range with a variable [quick tip]

Published on Jun 16, 2015 in Excel Howtos, Learn Excel
Use Paste Special to multiply (or add, divide etc.) a range with a variable [quick tip]

Here is a fun way to use Paste Special to quickly multiply everything in a range with 1.1 (why 1.1? Well, imagine you have a report with everything in US $s and your boss wants to see the numbers in Australian $s…)

Since your report has different formulas for each cell, you can’t multiply first cell with a rate variable and drag it down. You have to manually edit each formula and add *rate at the end of it.

Oh wait…, you can use Paste Special.

Continue »

Ensure cleaner input dates with conditional formatting [quick tip]

Published on May 12, 2015 in Excel Howtos
Ensure cleaner input dates with conditional formatting [quick tip]

Here is a familiar problem: You create a workbook to track some data. You ask your staff to fill up the data. Almost all the input data is fine, except the date column. Every one types dates in their own format. Here is a fun, simple & powerful way to warn your users when they […]

Continue »

Find and Highlight all blank cells in your data [Excel tips]

Published on Apr 20, 2015 in Excel Howtos
Find and Highlight all blank cells in your data [Excel tips]

True story:

On Friday (17th April – 2015), I flew from Vizag (my town) to Hyderabad so that I can catch a flight to San Francisco to attend a conference. As I had 10 hours of overlay between the flights in Hyderabad, I checked in to a lounge area so that I can watch some sports, eat food while pretending to do work on my laptop. There was a gentleman sitting in adjacent space doing some work in Excel. As I began to compose few emails, the gentleman in next sitting space asked me what I do for living. Our conversation went like this.

Me: I run a software company
He: Oh, so you must be good with computers
Me: smiles and cringes at the stereotyping
He: What is the formula to select all the blank cells in my Excel data and highlight them in Yellow color

Mind you, he had no idea that I work in Excel. We were 2 random guys in airport lounge watching sports and eating miserable food.

Me: Well, what are you trying to do?
He: You see, I am auditing this data. I need to locate all the blank rows and set them in different color so that my staff can fill up missing information. Right now, I am selecting one row at a time and filling the colors. Is there a one step solution to this problem?

Needless to say, I showed him how to do it faster, which led to an interesting 3 hours at the lounge.

End of true story.

So today, let’s understand how to find & highlight all the blank cells in the data.

Continue »

How to insert a blank column in pivot table?

Published on Apr 16, 2015 in Excel Howtos, Learn Excel
How to insert a blank column in pivot table?

We all know pivot table functionality is a powerful & useful feature. But it comes with some quirks. For example, we cant insert a blank row or column inside pivot tables.

So today let me share a few ideas on how you can insert a blank column.

But first let’s try inserting a column

Imagine you are looking at a pivot table like above.

And you want to insert a column or row. Go ahead and try it.

Continue »

Use apply names to create readable formulas [quick tip]

Published on Apr 1, 2015 in Excel Howtos
Use apply names to create readable formulas [quick tip]

We all know that using named ranges is a good practice. So you went ahead and created names for every value in your complex workbook. But now, what about those formulas which still refer to cells by their addresses? Here is a quick tip to make your formulas readable by replacing cell addresses with the names in one go.

Use Apply Names feature.

Continue »

Conditionally Format Chart Backgrounds

Published on Mar 27, 2015 in Charts and Graphs, Excel Howtos, Huis, Posts by Hui, VBA Macros
Conditionally Format Chart Backgrounds

Learn two techniques to conditionally format the background of a chart based on some external value.

Continue »

Who is the most consistent seller? [BYOD]

Published on Feb 18, 2015 in Analytics, Excel Howtos, Pivot Tables & Charts
Who is the most consistent seller? [BYOD]

Who is the most consistent of all?

Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.

Looking at this list, you have a few questions.

  1. Who is the best seller?
  2. Who is the most active seller?
  3. Who is the most consistent seller?
  4. Which seller has fewest invoices?

Let’s go ahead and answer these using Excel. Shall we?

Continue »

How to consolidate data that is different shapes [BYOD]

Published on Feb 16, 2015 in Excel Howtos, Pivot Tables & Charts, VBA Macros
How to consolidate data that is different shapes [BYOD]

Last week, I asked my email newsletter readers to submit “one data analysis problem you are struggling with”. We called it BYOD – Bring your own data. More than 100 people have emailed various interesting (and often very difficult) problems. This week (between 16th of February to 20th of February), let’s take a look at some of these problems and solve them.

Consolidating data in different shapes

We can use either VBA or Excel’s consolidation features to combine data that has same shape (ie same number & type of columns). Here is one way to do it.

But what if we need to consolidate data that is in different shapes?

Something like above.

In such cases, we can use 3 powerful tools.

  1. Multiple Consolidation Ranges – Pivot Tables
  2. VBA
  3. Power Query

So let’s examine how to use these approaches to consolidate data in different shapes.

Continue »

Custom Number Formats – Colors

Published on Feb 3, 2015 in Excel Howtos, Huis, Posts by Hui, Quick Tip
Custom Number Formats – Colors

Did you know you can use Excel 95-03 Color Names or Color Numbers in your Custom Number Formats in all Excel versions up to an including Excel 2013?

This post will show you how.

Continue »

Unleash the pattern power with Excel Fill [quick tip]

Published on Jan 20, 2015 in Excel Howtos
Unleash the pattern power with Excel Fill [quick tip]

When you are a “work from home” dad, you can see a lot of patterns. Here is one.

My kids come home from school by noon (they are too young for full day school). Right after lunch they watch their favorite cartoon program, Team Umizoomi, in which few fictional characters go about solving problems in the Umi city using maths. Milli, one of the characters is an expert with patterns. She solves problems by identifying patterns and unleashing pattern power. 

Team Umizoomi & Excel Fill – How do they link up?

Here is how they link up.

Imagine you have a workbook where you need to follow a pattern, like above.

You too can unleash the pattern power. What more… you needn’t break in to a song sequence every-time you unleash the power.

Continue »

Write faster formulas with Auto-correct

Published on Jan 6, 2015 in Excel Howtos
Write faster formulas with Auto-correct

Want to write formulas faster? Here is a quick tip.

Use Auto-correct!

That is right. Excel’s auto-correct feature can be setup to help you write formulas faster. See above demo. Read on for details.

Continue »

Export iPhone contacts to Excel using this free template

Published on Dec 2, 2014 in Excel Howtos, Learn Excel, Templates
Export iPhone contacts to Excel using this free template

Recently my iPhone 4 crashed. It is 3.5 years old. And just like any other 3 year old, it started acting weird & crazy one night. The next morning it went silent. It won’t go beyond the Apple logo whenever I start it. Since I couldn’t wait for the phone to start, I took out the SIM card (the phone is unlocked, if you are wondering) and placed it in my old Nokia phone. But alas, none of my contacts are on the SIM. They are in “cloud”.

After a day of answering phone calls from everyone including my mom as “Chandoo here”, I’ve decided to get my contacts back. So I logged in to iCloud to download a backup. And the backup was a .VCF file.

Since I wanted to have all my contact numbers in a spreadsheet, I did what any Excel nerd would do. I built a template that can convert VCF data to Excel worksheet.

Continue »

Looking up when the data won’t co-operate (case study)

Published on Nov 4, 2014 in Excel Challenges, Excel Howtos
Looking up when the data won’t co-operate (case study)

Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.

Recently I found myself in such a situation and learned something new.

Introducing … data that won’t play nice

Drum roll please. Here is a data-set that I got from somewhere.

The problem – build a lookup formula

And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.

But how?

Continue »