All articles with 'macros' Tag

Today is Diwali, the festival of lights. I wish you and your family a happy, bright and prosperous festive time. May your house shower with lots of light, laughter and love.

Diwali is one of my favorite festivals. It is a time when all family members get together, eat delicious food, laugh to hearts content and light up diyas (small oil lamps) to celebrate the victory of good over evil. This year, my kids (who are 6 yrs btw) are very excited about the festival. They are looking forward to lighting up diyas and crackers (fire works).

To celebrate the holiday, I made something for you.

An animated flower pot firework in Excel.

Last night I asked members of our Chandoo.org facebook page to share an Excel problem you are struggling with.  Francis asked,

How to save a file as .txt in vba without quotes? When I save as .txt, the file has got quotes inside of it. I used the code Print, but it didnt work because the file loses its delimitation.
Does anyone know how to solve this?

Let’s understand how to save a range as text and overcome the double quote problem.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 47th session of Chandoo.org podcast, let’s see how Excel can make you an awesome entrepreneur.

What is in this session?

In this podcast,

• Why Excel for entrepreneurs
• Key areas of a business owner’s work
  • Projects & to dos
  • Finances
  • Customers & marketing
  • Planning & strategy
  • Processes & workflows
• 5 features of Excel that help
• Conclusions

Here is a familiar scenario: You are building a dashboard. Naturally, it has a few worksheets – data, assumptions, calculations and output. As you make changes to input data, you constantly switch to calculations (or output) page to check if the numbers are calculating as desired. This back and forth is slows you down.

Use Watch Window to reduce development time.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 43rd session of Chandoo.org podcast, let’s talk about top time saving features of Excel.

What is in this session?

In this podcast,

• Quick announcement about Awesome August
• My 9 favorite time saving features of Excel
• Remove Duplicates
• Tables
• Pivot Tables
• Auto fill
• Format Painter
• Find & Replace
• VBA / Macro Recorder
• Auto save
• Auto complete / Intellisence
• Recap & Conclusions

A significant portion of our time in Excel is spent navigating between various ribbon tabs and clicking buttons. Wouldn’t it be better if you can combine all the frequently used features in to one ribbon? Something like above:

Read on to learn how to create, edit and share custom ribbons in Excel.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 39th session of Chandoo.org podcast, Let’s learn about FOR loops.

There is a special giveaway in this podcast. It is a workbook with several FOR loop VBA code examples. Listen to the episode for instructions.

What is in this session?

In this podcast,

• Announcements
• What is a loop – plain English & technical definitions
• For Loop vs. other kind of loops (While & Until)
• For Next loops
• For Each loops
• Nested For loops
• Special tips on For loops
• Performance issues & infinite loops
• Conclusions & giveaway

Filtering is one of the most used feature in Excel. It is a quick way to take lots of data and narrow down to the subset we want.

But here is one common filtering scenario that is slow as snail.

Imagine you are looking at some sort of sales data (if you can’t imagine, look at the above demo).

Now, you want to filter this list for gender=male, profession=self-employed, product category = chocolates and  quantity = 1.

If you use the right click, filter > filter by selected value approach, this will take several clicks.

Wouldn’t it be cool if you can select the entire combination and say filter?

Unfortunately, no such feature exists in Excel.

But you are not aiming to be ordinary in Excel.  You are aiming to be awesome in Excel. That means, you don’t take no for answer.

Fortunately, we can quickly write a VBA macro that filters a list by selection. So let’s do that.

In about 3 days, I am leaving to USA for participating in PASS Business Analytics conference – 2015. It is an annual event for people in analytics profession. This is the first time I am attending & speaking at the event. I am so excited for many reasons.

• I will be meeting many Excel bloggers, authors & internet friends for the first time
• I will be meeting many of you (readers, listeners, followers & customers of Chandoo.org) too
• I will be speaking at an awesome conference
• I will be visiting San Francisco for the first time in life
• I will be meeting a few college friends too

All this excitement means, I have too much going on. But that shouldn’t leave you out . So here are a few awesome Excel links for you. Check out and learn.

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

Today is Holi, the festival of colors in India. It is a fun festival where people smear each other with colors, water balloons, tomatoes and sometimes rotten eggs. This year we wanted to play with only water guns, but kids vetoed that idea vehemently. So we ended up driving to my sister-in-law’s place to play with colors (there were no rotten eggs or tomatoes, thankfully).

Let me smear a few colors on you

I would love to splash a jug full of color water on you and say Happy Holi. But the internets have not advanced thus far. So I am going to give you the next best option.

An Excel workbook to play holi

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.

Here is a common problem. Imagine you are looking a complex spreadsheet, aptly titled “Corporate Strategy 2020.xlsx” which as 17 tabs, umpteen formulas and unclean structure. Whoever designed it was in insane hurry. The workbook has formulas like this, =SUM(Budget!A2:A30, 3600)+7925 .

It was as if Homer Simpson created it while Peter Griffin oversaw the project.

So how do you go about detecting all cells containing formulas with hard-coded values?

We analysts like to compare. If you ever want to keep an analyst busy, just give her 2-3 options. She won’t return to your desk until the cows come home. My wife uses this trick all the time. Picture this:

[In late 2013]
Me: I want to buy a new phone
She: Do you want Nexus 5 or Galaxy S5 or iPhone 5s?

Its late 2014 and I am not done comparing.

So today, let’s talk about an interesting comparison scenario.

Comparing by letter or word

See above demo to understand the concept. Read more to learn how to do this.

Recently, we discussed about the case of unwieldy data and how we lookup what we want using formulas like SUMIFS. Today, let us learn few more ways to solve the same problem.

Suitable structure spawns simple solutions

Poorly structured is the 2nd biggest problem of analysts. The first one is not enough coffee. That is why there is a dictum in the data analytics world.

Structure is everything

So, we can easily solve our lookup problem, if our data were to magically re-arranged in 2 column fashion – Data & Value.