fbpx

All articles with 'macros' Tag

CP047: Best Excel tools for Entrepreneurs

CP047: Best Excel tools for Entrepreneurs

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
Continue »

Build models & dashboards faster with Watch Window

Published on Aug 30, 2015 in Learn Excel, VBA Macros
Build models & dashboards faster with Watch Window

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.

Continue »

CP043: My favorite time saving features of Excel, Revealed.

Published on Aug 27, 2015 in Chandoo.org Podcast Sessions
CP043: My favorite time saving features of Excel, Revealed.

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
Continue »

Filter as you type [Quick VBA tutorial]

Published on Aug 22, 2015 in VBA Macros
Filter as you type [Quick VBA tutorial]

Filtering a list is a powerful & easy way to analyze data. But filtering requires a lot of clicks & typing. Wouldn’t it be cool if Excel can filter as you type, something like above.

Let’s figure out how to do this using some really simple VBA code.

Continue »

Save time with custom ribbons in Excel [tutorial]

Published on Aug 5, 2015 in Excel Howtos
Save time with custom ribbons in Excel [tutorial]

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.

Continue »

CP039: May the FOR Loop be with you – Introduction to For Loops in Excel VBA

Published on Jul 23, 2015 in Chandoo.org Podcast Sessions, VBA Macros

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.

Introduction to Excel VBA FOR Loops - What are they, how to use them - Chandoo.org Podcast - Session 039

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
Continue »

Quickly filter a table by combination of selected cell values using VBA

Published on Jul 15, 2015 in VBA Macros
Quickly filter a table by combination of selected cell values using VBA

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.

Continue »

Excel Links – PASS BA 2015 Edition

Published on Apr 14, 2015 in excel links

PASS BA conference - 2015In 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.

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 »

Celebrate Holi with this colorful Excel file

Published on Mar 6, 2015 in Charts and Graphs, VBA Macros
Celebrate Holi with this colorful Excel file

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

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 »

How to check for hard-coded values in Excel formulas?

Published on Jan 14, 2015 in VBA Macros
How to check for hard-coded values in Excel formulas?

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?

Continue »

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 »

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 »

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 »