All articles with 'Learn Excel' Tag

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

Who is this course for?

If you use MS Excel to do data analysis, reporting, day to day tracking or project management then this is the right course for you. If you struggle with formulas or not sure which formula to use for any given occasion, then this is the perfect course for you. We will be introducing more than 40 different formulas with 100s of examples in this course. As we will be using realistic data & examples, you can very quickly apply the same to your day to day work and do so much more with ease & confidence.

Click here to learn more & join this course.

It is megaphone time at Chandoo.org. I have a few quick announcements about upcoming training programs & other interesting stuff. Just read on 🙂

1. VBA Classes – 2nd batch details.
2. Good news for our Indian customers
3. New Excel Formula Crash Course – from tomorrow (17 August)
4. Excel School prices going up!
5. Recommended Live Training – Mike’s Power Analyst Bootcamp in US

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

Everyone and their colleague knows the importance of Excel keyboard shortcuts. While it may be a good idea to know a 97 different shortcuts for everything in Excel, the fact is, you don’t use them all the time. So, it all boils down to memorizing the few shortcuts that you use all the time.

In this post, I am sharing 10 such shortcuts that I can’t live without, especially, when I am analyzing data, making charts and formatting my workbooks.

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!

Some of you have heard my neighbor’s dog bark in a video or two that I recorded. While I was busy explaining how to do something awesome in Excel, this dog would decide to bark, adding her own two cents to the lesson. Quite a few of my VBA class students have grown used to it. So much that they complain when a lesson doesn’t have a couple of woofs. But I digress.

So coming back, one of the dogs (probably stray) has decided that she should bring her infant puppies and hide them under our terrace stair case. So, now we have 2 cute little puppies barking day long (and very late in to the night) just outside my office window. We have tried hard to get rid of them, but they somehow sneak back in and start barking or crying. So, I will be busy this weekend trying to move them out.

But that doesn’t mean, you have to live Excel-less for a few days. So I have a homework.

OR XOR AND, Get busy this weekend!

Don’t worry. I am not speaking elvish or something. OR, XOR & AND stand for bit-wise operations. This week, your task is to write formulas in Excel that would get the bit-wise results for AND, OR & XOR.

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.

Two weeks ago we asked you what you wanted to see at Chandoo.org and you responded.

We have had 71 responses giving us 101 ideas to date.

Here are the results…

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).