Author Archive

This is second episode of our Monthly Master Class.

In this one, you will learn all about SUMPRODUCT. Know all about basics to advanced usage of this powerful & versatile formula in this 98 minute master class.

Today is Friday the 13th. If you are a raging friggatriskaidekaphobiac, I suggest you to stop reading this post. For the rest of you, I have something fun.

Given a year in cell C3, let’s find out all the months with Friday the 13th. Something like above.

Okay, time for another challenge.

Imagine you have some data like this. Each cell contains 3 numbers separated by line break  – CHAR(10) and you need to extract the number that is 10 digits long.

Go ahead and solve this riddle.

Recently I saw an interesting Earth Venus cosmic dance video on Facebook. See the original video below or here.

Although this is not entirely accurate from physics & astronomy perspectives, the dance is a stunning example of patterns that are generated by simple things.

I wanted to recreate this cosmic dance in Excel. How else am I to get my spreadsheet fix on a Saturday?

Here is a quick demo of the final outcome. Read on to learn more about the Earth Venus cosmic dance.

Here is a simple IF formula challenge for you. Go ahead and post your answers in the comments section. Can this person vote in Sumeria? Imagine you are the chief election officer in the great country of Sumeria. You have introduced a new eligibility criteria for voters just before the grand presidential elections of 2016. In order […]

Here is an interesting scenario.

Let’s say you are looking at a time, like 9:42 AM and want to know which 15 minute slot it fits into. The answer is 9:30 – 9:45. But how would you get this answer thru Excel formulas?

As announced earlier, here is the first one of our Master Classes. In this one, you will learn how to create advanced interactive charts in Excel by blending pivot tables, slicers, timelines, VBA, conditional formatting and charts. The final outcome is BEAUTIFUL!!! Monthly Master Class – Episode 1 – Trailer Check out a quick trailer of the […]

Hi folks,

I have an exciting announcement for you. Starting this month, I will be running a monthly master class on advanced Excel. This is a video class to help advanced users learn creative, powerful ways to use Excel in their day to day work.

What is this Master Class?

The idea is simple. Every month, I will create a lengthy video discussing one of the below topics, specifically targeted at advanced Excel users. As our blog grew, we have a lot of experienced, advanced users and I want to help you achieve more. You will learn,

• Dashboards
• Data Analytics
• Visualizations
• VBA / Macros
• Business Modeling
• Business Intelligence

Each class will be on an different topic with in-depth coverage, creative outputs and awesome techniques. You can sign up for the class that suits your needs.

Here is a simple but effective rule for your next dashboard. Tell all versions of truth. All versions? But there is only one version no? Of course, there is only one version of truth (or data), but you can present that in different ways, thus creating different perspectives, offering different insights. By using an interactive element (slicers […]

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 54th session of Chandoo.org podcast, let’s make you awesome in Pivot Tables.

What is in this session?

In this podcast,

• Quick updates
• Top 10 pivot table tricks
  • Adding same value field twice
  • Tabular layouts
  • GETPIVOTDATA & 2 bonus tricks
  • Relationships & data model
  • One slicer to rule them all
  • Show only top x values
  • Relative performance
  • Show unique count
  • Spruce up with conditional formats
  • Not so ugly pivot charts
• Resources & Show notes for you

Mary Ellen, one of our readers, has an interesting conundrum,

I have some data that goes to Pivot table then to pivot chart. There is a slicer to filter the data. But when I slice, my pivot chart gets messed up. How to have the slicer, but still see the insights in the chart?!?

See above demo to understand:

This is because when you slice by a school, the pivot table gets filtered and hence % row total for that school becomes 100% (as there are no other schools).

How to fix the problem? The easy answer is to remove the slicers. But we want to have our slicers and eat a slice of them too. So we crank up the Excel awesomeness valve and get to work. There are two ways to achieve what we want.

• Old school method: Two pivot tables, some formulas & a line chart
• New Excel method: Power Pivot and a line chart

Read the rest of this article to know more.

It is Easter time again. This year, we drove to my brother’s house in Hyderabad (700 km away from my home) to spend a weekend doing absolutely nothing (we will eat copious amount of food, share family memories, laugh and laze). It is Chandoo.org tradition to share few puzzles during Easter time, a la an Excel themed virtual Easter egg hunt. This year, I have prepared an amazing challenge for you.

One of our readers emailed this question recently,

I like the conditional formatting icons. I am trying to present some business data where going down is good. How do I get a green colored down arrow icon?

Essentially, Ms. CanIGetItInGreen wants this:

Unfortunately, Excel’s conditional formatting icons are not customizable. So we can’t get the green down arrows without some sneak. And sneak we shall.

This is a guest post by Chirayu, a member of Chandoo.org forum.

Animating Charts in PowerPoint cannot be done without the help of 3rd party software’s that create a flash file of the chart & embed it into the presentation.

However there is a workaround for this. Save your chart as multiple images & insert them (overlapping on top of each other). Use VBA on Developer tab Controls such as Combo Box, Option Button, Check Box etc. to “Bring To Front”” the corresponding image. Thus giving the illusion of an Animated Chart in PowerPoint.

This guide will teach you how to animate the charts, using the three Developer tab Controls that were mentioned before. The code & functionality only works in Slide Show Mode. File must be saved as PowerPoint Macro-Enabled Presentation (*.pptm)

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 53rd session of Chandoo.org podcast, let’s talk about data validation.

What is in this session?

In this podcast,

• What is data validation
• How Excel DV compares with database & software DV?
• Types of data validation rules
• List & custom rules explained
• Input & error messages
• Alternatives to data validation
• Enhancing data validation
• Removing data validation rules
• Homework problem for you
• Resources & show notes