All articles with 'Learn Excel' Tag

I have a confession to make.

I am not sure how to describe this new thing I made in Excel / VBA. So first take a look at it.

Read on to learn how you can create something like this using Excel & a little bit of VBA.

Here is a handy trick to calculate last day of any month.

Assuming y and m contain the year & month for which you want to find the last day’s date, write

=DATE(y, m+1,0)

That is right, you can use ZERO (0) as the day.

When you do this, Excel tells us the last day of previous month.

Often in my work, I need to replace a bunch of formulas with values. Blame it on old habits, but this is what I used to do:

1. Copy the cells with formulas (CTRL+C)
2. Press ALT+ESV and then enter.

While this is ok, it does take quite a bit of time and key strokes.

Here is a shorter way I recently learned…

Yesterday was Halloween. To our readers who are not familiar with it, ‘Halloween is a colorful festival with lots of costumes, scary stories, theme parties and trick-or-treating, celebrated on 31st October, every year.” I have never celebrated Halloween as it is an unknown tradition in India where I live. But that is no excuse. Especially when the celebration calls for colorful clothes, scary themed houses and shrieking kids.

Of course, we are not going to have a traditional Halloween. Because,

• At our house, we use pumpkins to make sambar, not lights
• The only ones with costumes in our house are my kids.
• If I send my kids for trick or treat, they will get neither.

So that brings us to the only part of Halloween that I can celebrate. Telling scary stories.

So lets talk about the stuff that scares us. But bear in mind that I am not interested in that time when you & your cousin went camping and stumbled in to an abandoned log cabin to discover the …Save it for real Halloween.

We want to talk stuff that scares you in Excel of course.

Hi folks,

What a night it has been. Unlike hi-fi product launches for a new perfume or car, my book launch did not have hot models or unlimited champagne. And yet, last 18 hours has been an intense and lovely experience. And finally,

My book is out.

You can purchase a copy thru Amazon or our website.

Go ahead and get your copy today.

Keen readers of Chandoo.org would have noticed a slow down in new posts recently. There are a few reasons for that.

• I went to Malaysia to conduct Excel Dashboards & Power Pivot masterclass.
• After coming back, we (our family) went to Goa to vacation for a few days.
• When we got back, we all fell in to a vicious loop of flu, body pains and fatigue.
• And it took a few days to recover and fall back in to routine.
• But the most important reason is, I am writing a book 🙂

Introducing The Vlookup Book

Friends, readers & supporters of Chandoo.org,

I am very glad to announce my newest book – the vlookup book to help you become awesome in answering any lookup related question.

Click here to get your copy.

Today, let’s travel in time. Pack your photon ray guns, extra underwear, buckle your seat belts and open Excel!

Of course, we are not going to travel in time. (Come to think of it, we are going to travel in time. By the time you finish reading this, you would have traveled a few minutes)

We are going to learn how to travel in time when using Excel. In simple terms, you are going to learn how to move forward or backward in time using Excel formulas.

So are you ready to hit the warp speed? Let’s beam up our Excel time machine.

Howdy again, folks. Jeff Weir here, borrowing the keys to the blog off Chandoo again. (Hopefully I don’t scratch it again). How remiss of me…jacob reminded me in the comments of my previous BLOATED post on good spreadsheet anti-bloat practices that I completely missed one of Excel’s newest and most efficient anti-bloat features: PowerPivot. So […]

Hello folks…

Time for a fun & useful survey. This time lets talk about Excel Interview Questions.

Many of you are silently becoming awesome in Excel, data analysis, charting, dashboard reporting, VBA, Power Pivot and business skills, thanks to all the time you spend on Chandoo.org. I am sure there will be a day in near future, when you have to face another interview and be selected for a challenging, fun & high paying role.

Likewise, there is also a significant portion of you who are too good in your job that you will become a senior manager, VP or CXO, or better still start your own business. When the tables have turned, you will be the one looking for smart, dedicated, talented and fun individuals to join your team and make you look even more awesome.

So my question for both prospective interviewees and wannabe Excel pros,

According to you, what are the best Excel interview questions?

How to Solve an Equation in Excel

Today lets get cozy. Lets start a fling (a very long one). Lets do something that will make you smart, happy and relaxed.

Don’t get any naughty ideas. I am talking about INDEX() formula.

INDEX?!?

Of all the hundreds of formulas & thousands of features in Excel, INDEX() would rank somewhere in the top 5 for me. It is a versatile, powerful, simple & smart formula. Although it looks plain, it can make huge changes to the way you analyze data, calculate numbers and present them. It is so important that, whenever I teach (live or online), I usually dedicate 25% of teaching time to INDEX().

Enough build up. Lets get cozy with INDEX.

Lets say you have a large list of numbers, and you want to calculate the average of every nth value. Not the average of all numbers, but just every nth number.

That is what we will learn in next few minutes.

Lets say you are the head of purchasing department at Big Corp Co.

You are obviously very busy. Every day starting with a large cup of coffee and ends with a big smile, as you save your company thousands of $s by negotiating best deals, finding best providers and being awesome.

Today, let me share a small Excel tip with you that will make you even more awesome.

Hello friends..,

I have 2 things for you:

• Details about our upcoming Power Pivot courses
• A short video about designing better dashboards.

Hi there,

I have a question and an announcement for you.

Question: Are you interested in learning Power Pivot? Do you want to connect, analyze and visualize lots of data using Excel?

IF(Your_answer="YES", "Read Announcement", "Read Announcement anyway!")

Announcement: 2nd Batch of Power Pivot coming on August 1st.