All articles with 'downloads' Tag

First a Quick Announcement: Our VBA Class enrollments will be closed this Friday (Sep 16). If you want to learn VBA & Excel, please consider joining our course. More than 120 students have already joined us in the second batch and are learning VBA as you read this. Click here to learn more about the VBA Classes and join us.

Moving on…,

As you may know, Chandoo.org offers quite a few Online Excel training programs. Over the last few weeks, many of you have emailed us and asked which training program is best for your situation. This got me thinking. “It should be easy for YOU to know what is best.”

So today morning, I locked my office room and made a beautiful Excel workbook that asks you only 2 questions and tells you which training programs are best for you. Try it here.

Over at PTS Blog, my dear friend & charting guru, Jon Peltier has an interesting post on using dot plots to visualize Murders & Suicides data by US States.

Not that murders & suicides fascinate me, but I wanted to play with this data myself and see how we can visualize it. So I emailed Jon and asked him to share the raw data. Being a lovely chap Jon is, he immediately sent me the data. So here we are, playing with gory data on a Friday.

Suicides & Murders by US States – An Interactive Excel Chart

You can see a demo of the chart I came up with above. Read on to learn how this chart is constructed.

In part 4 of our VBA Crash Course, we are going to create our very first VBA application using what we learned so far.

We will take the “We Are Nuts” example and create a daily sales tracker application using all the things you have learned so far. You can download the example VBA workbook and play with it to learn better.

In part 2 of our VBA Crash Course, we are going to learn what Variables, Conditions & Loops are and how to use them in Excel VBA.

What are Variables, Conditions & Loops?

If you are new to computer programming, you might think I am speaking legalese. So, to make it easy to understand, lets assume you run a bunch of stores across the town. To make it colorful, lets call your stores “We are nuts” – a dry fruit and nuts store chain. At the end of every day, you call each of the 24 store managers and ask them how much sales they have made in that day.

Now, you are not the kind of boss who micro-manages & nitpicks. So you don’t really note down sale for every store. Instead, as you call the store manager, you just mentally update the total. So first store says “$2,300” your total is 2300. Second manger says “$4,000”, the total now will be 6300. So on.

The value 6300 here is nothing but a variable.

We will use the “We are nuts” example thru out this lesson & towards the end we will build a sales logging application using Excel VBA.

This article is part of our VBA Crash Course. Please read the rest of the articles in this series by clicking below links.

1. What is VBA & Writing your First VBA Macro in Excel
2. Understanding Variables, Conditions & Loops in VBA
3. Using Cells, Ranges & Other Objects in your Macros
4. Putting it all together – Your First VBA Application using Excel
5. My Top 10 Tips for Mastering VBA & Excel Macros

Any Tom, Dick and Sally can make things complex. It takes guts and clarity to simplify things. That is why I was pleasantly surprised to see this dashboard prepared by Michigan State. You can see it in the detailed article.

Linda, one of My Excel School students shared this dashboard link with me and asked if I can show how to construct something like this. Here is my version of the dashboard.

Read this post to learn how to construct a similar dashboard on your own. Also, you can download the excel workbook and play with it.

Hafiz, One of our avid readers, writes in. My problem is when I convert data from text to column using dash “-“, conversion is easy. but when the gap provided in text is with “alt+enter”, I can’t convert the data.

Well, I tried to use text to columns feature (from Data ribbon) and it would not work.

Although you can use formulas to do the splitting, they might become tedious. So the next logical option is to use macros.

In this article, learn how to use VBA to split text on New Line characters

The other day, while I was putting my kids to sleep, this idea came to me. How do I check if a cell contains a palindrome, using Excel formulas?

Next morning, I wrestled with excel for about 20 minutes and boom, the formula is ready.

But what is a palindrome?
A palindrome is a word, phrase, verse, or sentence that reads the same backward or forward. For example: A man, a plan, a canal, Panama!
So, to check if a cell contains palindrome, we need to reverse the cell contents and see if both original and reverse are the same.

Nothing gives a teacher more satisfaction than seeing a student apply the knowledge to do something awesome. So naturally, I jumped with joy when I got this email from Dan, one of my VBA Class students,

Hi Chandoo,
After going thru your VBA Classes, I realized that we can lots of awesome stuff with pivot tables + simple macros. I recently created an Excel Dashboard to depict MLB (Major League Baseball) Pitching Stats. I could not have done this had I not learned VBA. Thank you so much for teaching the class.

I got curious and requested Dan, if I could share the file with you all. Being a lovely person, Dan agreed immediately.

On a cup of green tea with a friend I grabbed the golden rule of accounting. It is simple (You can easily miss if it came your way!) and can be applied to almost all the accounts (Depreciation, Gross Block, Cash, Equity, Debt) – You name it, and you have to use it! So concentrate […]

You’ve been there before. It’s almost 5:00, and you are going crazy trying to finish the presentation due for a monthly performance meeting the next morning. The model is refreshed, and now it just takes a LOT of copying, pasting, and positioning to get the PowerPoint ready. Finally, the slides are finished…, until you read a new message from your boss requesting a minor change. But of course her change means you have to start all over with the copy and pastes…

There is always a better way! In the Oil and Gas industry, I constantly have monthly reports to assess the performance of our operating assets. Excel VBA makes it a cinch to automate the entire process. So when a simple change is requested, the presentation is automatically generated with the click of a button. No more wasting time!

Today I want to share an Employee Shift Calendar Template with you. You can use this template to keep track of shift timings on various days. See a demo of the template: How does this template work? This template uses a 3 main ideas, Excel formulas to create the calendar & checking for a day’s […]

Few months ago, we learned how to create a project valuation model in Excel as part of our series on Financial Modeling in Excel.

My Project Evaluation Model had a limitation!! In one of the personal comments that I received, the reader pointed out an important problem!

Few days ago, we published an article on how to create interactive dashboards using hyperlinks. Many of you loved this tutorial. But quite a few of you also said, this tutorial is a bit complex to follow.

So, I made a short video explaining how the UDF and interactive hyperlinks work and how our dashboard is weaved together.

Last week we learned how to create dynamic hyperlinks in Excel. Today, I want to show you something even cooler. An interactive dashboard based on hyperlinks, like shown above.

Isn’t it impressive?

Well, to create something like this, you don’t need a degree in advanced cryogenics. You just need a bunch of data, a chart, a one line macro code and some pixie dust (go easy on pixie dust).