All articles in 'VBA Macros' Category

Compare 2 sets of data by letter or word & highlight mismatches [vba]

Published on Dec 15, 2014 in VBA Macros
Compare 2 sets of data by letter or word & highlight mismatches [vba]

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.

Continue »

Calculate Pi by throwing Frozen Hotdogs !

Published on Nov 28, 2014 in Huis, Posts by Hui, simulation, VBA Macros
Calculate Pi by throwing Frozen Hotdogs !

Did you know you can Calculate Pi by throwing Frozen Hotdogs !
No ?
Learn how to calculate Pi by throwing Frozen Hotdogs with this Excel Simulation of Buffon’s Needle.

Continue »

Looking up when data won’t play nice – few more alternatives

Published on Nov 12, 2014 in Learn Excel, VBA Macros
Looking up when data won’t play nice – few more alternatives

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.

Continue »

Introducing…Structured References for PivotTables

Published on Oct 18, 2014 in hacks, Pivot Tables & Charts, Posts by Jeff, Random, VBA Macros

Howdy folks. Jeff here, bringing you a Public Service Announcement: Thanks to the magic of VBA , Structured PivotTable References are coming to a PivotTable near you! Structured References for PivotTables? So what? Well, because PivotTables are the best bit of ‘old’ Excel, and Tables are the best thing about ‘new’ Excel, and it’s about […]

Continue »

CP022: What’s a Macro? Introduction to Excel VBA, Macros & Automation

Published on Oct 9, 2014 in Chandoo.org Podcast Sessions, VBA Macros
CP022: What’s a Macro? Introduction to Excel VBA, Macros & Automation

In the 22nd session of Chandoo.org podcast, lets do some macros.

What is in this session?

VBA (or macros, automation) is a mystery for many of us. So in this podcast, lets unravel the mystery behind it and get you started with the awesome world of automation.

In this podcast, you will learn,

  • What is a macro?
  • What is VBA then?
  • Reasons for using VBA Macros
    • Automation
    • Extending Excel’s capabilities
    • Efficiency
    • Applications
  • How to get started with VBA Macros?
  • Using Recorder
  • Example Macro
  • Going beyond recorder – Learning VBA
Continue »

A dashboard to visualize this FIFA worldcup [guest post]

This is a guest post by Krishna, a football lover & one of our readers.

The wait for lifting the most valued priced in football for Germans was finally over. For a football fan, world cup is best time that is scheduled every four years and that if your favorite team lifting the trophy is like your crush is going on a date with you. :)

A sneak-peek at the final dashboard

Here is the final dashboard (it has more functionality than depicted). Click on it to enlarge.

FIFA 2014 Worldcup Excel dashboard

Continue »

How fireworks animated chart is made [video tutorial]

Published on Jul 17, 2014 in Charts and Graphs, VBA Macros

On July 4th this year, I published an animated fireworks chart for you. Many of you liked it. Quite a few wanted to know how its made.

So here is a video explaining the construction of fireworks.

(You can see this video on our YouTube Channel too)

Continue »

4th of July Fireworks – an Excel animation for you

Published on Jul 4, 2014 in Charts and Graphs, VBA Macros

To all our readers & friends from USA,

I wish you a happy, fun & safe 4th of July.

For the last 4th of July (2013), we (Jo, kids & I) were in USA. We went to Washington DC to meet up a few friends for that weekend. And we had one of the most memorable evenings of our lives when we went to national mall area in the evening to watch beautifully choreographed fireworks. Kids really loved the amazing display of fire-crackers and enthusiasm.

While we all are back in India this time, it doesn’t mean we cant celebrate 4th of July. So I made some fireworks. In Excel of course.

Here is a little Excel animation I made for all of us.

4th of July Fireworks – Excel animation

First watch this quick demo (<15 secs)

Continue »

Top 10 things we struggle to do in Excel & awesome remedies for them

Top 10 things we struggle to do in Excel & awesome remedies for them

Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.

Continue »

Excel Dashboards – 49 dashboards to visualize US State to State migration trends

Hello everyone. Stop reading further and go fetch your helmet. Because what lies ahead is mind-blowingly awesome.

About a month and half ago, we held our annual dashboard contest. This time the theme is to visualize state to state migration in USA. You can find the contest data-set & details here.

We received 49 outstanding entries for this. Most of the entries are truly inspiring. They are loaded with powerful analysis, stunning visualizations, amazing display of Excel skill and design finesse. It took me almost 2 weeks to process the results and present them here.

49 Dashboards to visualize State to State Migration - Chandoo.orgExcel Dashboard Examples - Visualizing state to state migration trends - Chandoo.org

Click on the image to see the entries.

Continue »

Implementing Modular Spreadsheet Development – a walkthrough

Published on May 21, 2014 in Financial Modeling, VBA Macros
Implementing Modular Spreadsheet Development – a walkthrough

This article is written by Michael Hutchens from Best Practice Modelling.

In the first article on Modular Spreadsheet Development, we got a high level overview of Modular Spreadsheet Development principles. This article discusses the practical implementation of these principles in Excel.

From my experience using Modular Spreadsheet Development over the past decade, there are three increasingly-efficient methods of implementation in Microsoft Excel:

1. Manual implementation;
2. VBA automated implementation; and
3. Commercial add-in implementation.

This article provides a comprehensive overview of each of these methods and a summary of their advantages and disadvantages.

Continue »

Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study]

Published on May 13, 2014 in VBA Macros
Building a simple timer using Excel VBA to track my Rubik’s cube solving speed [case study]

Today, lets learn how to make a simple timer app using Excel. First some background…,

Recently, I learned how to solve Rubik’s cube from my nephew. As a budding cuber, I wanted to track my progress. Initially I used the stopwatch in my iPhone. But it wont let me track previous times. So I thought, “Well, I can use Excel for this”.

So I made a small timer app using Excel. Its quite minimalistic. It has a single button. I press it and it tracks the start time (date & time stamp). If I press the button again, it records the duration.

This way, I can see my progress over next few weeks and may be plot the trend.

Continue »

CP005: Introduction to Form Controls – an interview with Debra Dalgleish

Published on Apr 17, 2014 in Chandoo.org Podcast Sessions, VBA Macros
CP005: Introduction to Form Controls – an interview with Debra Dalgleish

In the 5th session of Chandoo.org podcast, we are going to demystify form controls.

I am very happy and excited to interview my good friend, fellow Excel MVP, author, blogger and virtual mentor – Debra Dalgleish about this topic.

In this podcast, you will learn,

  • What are form controls
  • When you would use them?
  • Example form control – Combo box
  • How form controls differ from active-x controls
  • How to enable form controls in your Excel?
  • Various important form controls
  • Special bonus & how to obtain it
Continue »

Hui’s Calendar Tool (as Borrowed from Chandoo)

Published on Apr 7, 2014 in Automation, Huis, Posts by Hui, VBA Macros
Hui’s Calendar Tool (as Borrowed from Chandoo)

Today we take a look at an exercise in how to develop a simple Calendar selection tool for your worksheets without using complex VBA Calendar Controls.

Continue »

VBA Serenity

Published on Mar 31, 2014 in Posts by Jeff, Random, VBA Macros

Greetings and salutations, my fellow VBA-fearin’ congregation. Evangelizin’ Jeff here, spreading the good word about everlastin’ VBA serenity. You may remember me from mah preeeevious sermons such as Tables, PivotTables, and Macros: music to your ears and Big trouble in little spreadsheet. Well today, I’m going to praise the work of a high-yah pow-wah. Our […]

Continue »