All articles with 'macros' Tag

Making a slick on/off switch using Excel & little bit of VBA [case study]

Published on Dec 5, 2013 in VBA Macros
Making a slick on/off switch using Excel & little bit of VBA [case study]

I have a confession to make.

I am not sure how to describe this new thing I made in Excel / VBA. So first take a look at it.

Read on to learn how you can create something like this using Excel & a little bit of VBA.

Continue »

What would James Bond have in his Personal Macro Workbook?

Published on Nov 18, 2013 in Posts by Jeff, VBA Macros
What would James Bond have in his Personal Macro Workbook?

You know, I‘m constantly amazed at the depth of knowledge and wisdom that comes up in the comments of this site. For instance, the other day, undercover Excel secret agent KV came up a great list of interview questions in response to Chandoo’s great article What are best Excel interview questions?

I particularly liked this one:
Do you have a Personal Macro Workbook setup ? If yes, what are the most common macros you use in your daily work?

Secret Agent KV obviously has some purpose-built gadgets that he employs to kill inefficiencies. And he obviously also knows the two top-secret reasons to keep code in a Personal Macro Workbook:

  1. To keep it safe from international spy rings.
  2. So you can whip it out whenever you are cornered by any evil-looking spreadsheet.

While that first point is important, that second is the clincher: code stored in a personal workbook can be unleashed with a simple judo-chop to the throat.

Today, lets understand how to setup your personal macro workbook and open a secret passage-way to Excel awesomeness.

Continue »

Excel Risk Map

Published on May 28, 2013 in Automation, Project Management, VBA Macros
Excel Risk Map

We all have some projects to manage every now and then and there are needs of various trackers that help us in gauging the progress of the same. One of the most important things are heat maps that quickly help us in visually displaying the names of the projects that need special attention and resolve issues that are impacting them.

Continue »

Learn Top 10 Excel Features

Published on Apr 16, 2013 in Charts and Graphs, Learn Excel, Power Pivot, VBA Macros
Learn Top 10 Excel Features

Last week, we had a lovely poll on what are your favorite features of Excel? More than 120 people responded to it with various answers. So I did what any data analyst worth his salt would do,

I analyzed the data and here are the top 10 features in Excel according to you.

Read on to learn more.

Continue »

How to create interactive calendar to highlight events & appointments [Tutorial]

Published on Apr 9, 2013 in Charts and Graphs, Learn Excel, VBA Macros
How to create interactive calendar to highlight events & appointments [Tutorial]

One of the popular uses of Excel is to maintain a list of events, appointments or other calendar related stuff. While Excel shines easily when you want to log this data, it has no quick way to visualize this information. But we can use little creativity, conditional formatting, few formulas & 3 lines of VBA code to create a slick, interactive calendar in Excel. Today, lets understand how to do this.

Continue »

Show hide list boxes using VBA

Published on Dec 18, 2012 in VBA Macros
Show hide list boxes using VBA

Rama, one of our readers emailed this:
I Have Many List boxes In That I need to Hide Few Of them Using Check box

Show Hide list boxes by using a check box

We can use check box and a bit of VBA to do this easily. Something like above demo.

Read on to learn how to do this using Excel & VBA.

Continue »

Macros for Automatically Implementing Modeling Best Practices

Published on Nov 29, 2012 in Financial Modeling, VBA Macros
Macros for Automatically Implementing Modeling Best Practices

In the first part on our Modeling Best Practices series, we learned 5 best practices to follow. This article shows how to automatically implement the best practices using macros.

Continue »

Project Portfolio Dashboard in Excel [Part 2 of 2]

Published on Nov 19, 2012 in products, Project Management, VBA Macros
Project Portfolio Dashboard in Excel [Part 2 of 2]

In this 2 part tutorial, we will learn how to design a project portfolio dashboard. Part 1 discussed user needs & design. Part 2 will show you Excel implementation.

Project Portfolio Dashboard Pack is now available.
Click here to get your copy.

Final Implementation – Project Portfolio Dashboard

First lets take a look at the finalized dashboard implementation. Continue reading to learn more & download this dashboard.

Continue »

Happy Diwali [Animated Chart inside]

Published on Nov 13, 2012 in Charts and Graphs, VBA Macros
Happy Diwali [Animated Chart inside]

My heartiest Diwali wishes to all our readers.

The spirit of Diwali is to encourage people to spread joy, celebrate good things and throw away darkness (evil). These ideas are universal. So let me wish you a very happy Diwali.

Diwali, festival of lights, is celebrated in the month of October / November. It is one of my favorite festivals since childhood. A time when all family members get together, celebrate all the good in their life, laugh and light a few fire crackers (fire works).

This year, our kids (Nishanth & Nakshatra) too are excited about the festival. They are eager to light diyas (small lamps), watch the fire works and enjoy. Naturally I do not feel like opening Excel.

But then…,

Sometime during my morning coffee, I thought “hey, why not create a small Diwali greeting using Excel?

So here we go.

Continue »

Journey of Hurricane Sandy – Animated Excel Chart

Published on Oct 31, 2012 in Charts and Graphs, VBA Macros
Journey of Hurricane Sandy – Animated Excel Chart

Hurricane Sandy has taken front seat in all major news channels, papers, websites even in far off places like India. I hope & pray that our readers in US East coast are safe.

Today, lets understand the journey of Hurricane Sandy in this animated chart, prepared by Chris from Excel365.

Continue »

Even faster ways to Extract file name from path [quick tip]

Published on Oct 24, 2012 in Excel Howtos, VBA Macros
Even faster ways to Extract file name from path [quick tip]

The best thing about Excel is that you can do the same thing in several ways. Our yesterdays problem – Extracting file name from full path is no different. There are many different ways to do it, apart from writing a formula. Learn these techniques to be a data extraction ninja.

1. Using find replace
2. Using text to columns
3. Using UDFs

Continue »

Using pivot tables to find out non performing customers

Published on Oct 3, 2012 in Excel Howtos, Pivot Tables & Charts, VBA Macros
Using pivot tables to find out non performing customers

Moosa, one of our readers emailed this interesting question:

I have huge list of customers (around 1500).
Table includes following information
Customer # , Customer Name, Sales 2002, sales 2003, … sales 2012

My requirements are
1. list of customer who did not have sales during all these years
2. List of customer who have not business from 2003
3. List of customer who have not business from 2004

Today, lets learn how to identify all the non-performing customers.

Continue »

Interactive Pivot Table Calendar & Chart in Excel!

Published on Sep 12, 2012 in Charts and Graphs, Excel Howtos, VBA Macros
Interactive Pivot Table Calendar & Chart in Excel!

Can we make a calendar using Pivot Tables?!? Of course we can. Today let us learn a simple technique to create calendar style reports using Pivot tables. Thanks to Rob for inspiration Before making any progress, let me thank Rob from PowerPivotPro for the inspiration. Recently he wrote an article explaining how to use PowerPivot […]

Continue »

How fast can you finish this Excel Hurdles Challenge [Spreadsheet Olympics]

Published on Aug 10, 2012 in Excel Challenges, VBA Macros
How fast can you finish this Excel Hurdles Challenge [Spreadsheet Olympics]

Watching the Olympic athletes run & jump all I could think of is,

  • What should I eat to jump & sprint like that?
  • How come I never heard about steeple chase?
  • Should we really have 3 bullet points in all lists?

But I digress. Coming back, when watching one of those hurdles events, I got an idea as sharp as Chinese table tennis team.

Why not create a hurdles game in Excel to measure how good you are with keyboard?

So ladies & gentleman, let me present you our very own Olympics hurdle run.

Continue »

Making your dashboards interactive [Dashboard Essentials]

Published on Aug 2, 2012 in Charts and Graphs, Excel Howtos, Learn Excel
Making your dashboards interactive [Dashboard Essentials]

Everyone likes to be in control. Even my 2 year old daughter jumps with joy when she lays her hands on TV remote. She pushes the buttons and assumes it is working. It is another story that we rarely watch TV at home.

By adding an element of control, we can make our dashboard reports fun. Interactive elements like form controls, slicers etc. invite users to play with your dashboard, get involved and understand data by asking questions. That is why I recommend making dashboards interactive.

Today lets understand how you can make dashboards interactive.

Continue »