All articles with 'macros' Tag

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 »

Visualize Excel salaries around world with these 66 Dashboards

Visualize Excel salaries around world with these 66 Dashboards

Ladies & gentleman, put on your helmets. This is going to be mind-blowingly awesome.

See how many different ways are there to analyze Excel salary data. Look at these 66 fantastic, beautifully crafted dashboards and learn how to one up your dashboard awesomeness quotient.

Continue »

Highlight Row & Column of Selected Cell using VBA

Published on Jul 11, 2012 in Excel Howtos, VBA Macros
Highlight Row & Column of Selected Cell using VBA

When looking at a big table of analysis (or data), it would make our life simpler if the selected cell’s column and row are highlighted, so that we can instantly compare and get a sense of things. Like above.

Who doesn’t like a little highlighting. So lets learn how to do highlighting today.

Continue »

Visualizing Roger Federer’s 7th Wimbledon Win in Excel

Published on Jul 9, 2012 in Cool Infographics & Data Visualizations
Visualizing Roger Federer’s 7th Wimbledon Win in Excel

Did I tell you I love tennis? Some of my personal heroes & motivators are tennis players. And as you can guess, I admire Roger Federer. Watching him play inspires me to achieve more. So last night when he lifted Wimbledon trophy for 7th time, I wanted to celebrate the victory too, in my style. So I made an interactive timeline chart in Excel depicting his victory.

Continue »

Creating a Masterchef Style Clock in Excel [for fun]

Creating a Masterchef Style Clock in Excel [for fun]

Jo (wife) likes to watch Masterchef Australia, a cooking reality show every night. Even though I do not find contestant’s culinary combats comforting, occasionally I just sit and watch. You see, I like food.

The basic premise of the program is who cooks best in given time. To tell people how much time is left, they use a clock that indicates how much time is left (much like a stop clock, with a small twist).

One day, while watching such intense battle, my mind went

It be cool to make such a clock using hmm… Excel?

While I cannot share my snapper (or pretty much any other food item) with you, I can share my Masterchef style Excel clock with you. So behold,

Continue »