All articles in 'VBA Macros' Category

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 […]

During London 2012 Olympics, Usain Bolt reached the 100mts finish line faster than anyone in just 9.63 seconds. Most of us would be still reading this paragraph before Mr. Bolt finished the race.

To put this in perspective, NY Times created a highly entertaining interactive visualization. Go ahead and check it out. I am sure you will love it.

So I wanted to create something similar in Excel. And here is what I came up with.

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.

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.

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.

Last week we discussed how to extract numbers from text in Excel using formulas. In comments, quite a few people suggested that using VBA (Macros) to extract numbers would be simpler.

So today, lets learn how to write a VBA Function to extract numbers from any text.

Suresh sent an email with interesting problem.

There is one data entry sheet where all the data needs will be entered, however once done we want the data to be stored separately in multiple sheets designated by the Employee code.

In this article we will learn how to use VBA to help in resolving the problem Suresh was facing at work.

Finally, I got some time to sit down and do what I love most – write a blog post to make you awesome in Excel. After a whirlwind trip to Sydney, I am back in India to spend few days with my kids & wife before rushing to Australia to run 2nd leg of my training programs (in Perth, Melbourne & Brisbane). I did 2 sessions in Sydney – one for KPMG and other for public and both went very well. We got lots of positive feedback and people really loved it. I am saving the details for another post, but today lets talk about Interactive Sales Chart using Excel.

Take a look at the Interactive Sales Chart

First, take a look at interactive sales chart. Today, you will learn how to build this using Excel.

Welcome back. In final part of Making a Customer Service Dashboard using Excel let us learn how to add macros & VBA code that makes our dashboard interactive.

As you can see, there are 2 important macros in this dashboard.

Ever wondered how we can use Excel to send emails thru Outlook? In this article we well learn how to use VBA and Microsoft Outlook to send emails with your reports as attachment.

Scenario: We have an excel based reporting template. We want to update this template using VBA code to create a static version and email it to a list of people. We will define the recipient list in a separate sheet.

Read on…

Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.

Often I have thought, if I could have write “Select EmployeeName From Sheet Where EmployeeID=123” and use this on my excel sheet, my life would be simpler. So today we will learn how to do this.

People spend a lot of time thinking whether to use Excel as their database or not. Eventually they start using Access or SQL Server etc.

Today we will learn how to use Excel as a Database and how we to use SQL statements to get what we want. We will learn how to build a form like above.

As part of our Speedy Spreadsheet Week, I have asked you to share your favorite tips & techniques for speeding up Excel. And what-a-mind-blowing response you gave. 75 of you responded with lots of valuable tips & ideas to speed-up Excel formulas, VBA & Everything else.

Thanks to all the contributors

Many thanks to everyone who shared their tips & ideas with us. If you like the tips, please say thanks to the contributor.

Read Excel Speeding-up tips by area

This page is broken in to 3 parts, click on any link to access those tips.

Read everything
Formula Speeding-up Tips
VBA / Macros Optimization Tips
Everything Else
Share your tips

As part of Speedy Spreadsheet Week, I have emailed few renowned Excel experts and asked them to share their tips & ideas to speedup Excel. Today, I am glad to present a collection of the tips shared by them. Read the Excel optimization & speeding up tips shared by Hui, Luke, Narayan, George, Gregory & Jordon.

Is VBA slowing down your workbooks? Do you spend hours starting at Excel while the macros finish running? As part of our Speedy Spreadsheet Week, today lets talk about optimization techniques for Excel VBA & Macros.

Optimization Techniques for Excel VBA & Macros

Lets break this in to 2 sections. (1) Quick optimization techniques & tactics (2) Optimization ideas for the long run. Read on…,