All articles with 'macros' Tag
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 »
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 »
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 »
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.
Final Implementation – Project Portfolio Dashboard
First lets take a look at the finalized dashboard implementation. Continue reading to learn more & download this dashboard.Continue »
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.
Sometime during my morning coffee, I thought “hey, why not create a small Diwali greeting using Excel?”
So here we go.Continue »
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 »
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
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »