Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

Power Query

FIFA Worldcup 2018 Excel Tracker – FREE Download

FIFA world cup 2018 is around the corner. I love soccer, I love Excel, Let’s marry them. Here is an awesome, free FIFA world cup Excel Tracker to help you follow this year’s games in Russia.

Click here to download the FIFA worldcup 2018 tracker.

What you can do with this FIFA world cup Tracker Excel?

You can use this tracker to,

View schedules in your local time for group and knockout stages
View summary and detailed points table
Refresh live points table. When you refresh, the tracker show updated points based on latest results (You need Excel 2016, Office 365 or older versions of Excel with Power Query)
View knockout stage matches as a bracket
See timeline of the matches

Read This »

June 7, 2018

Learn Excel

Introduction to Excel SUMIFS Formula

Excel SUMIFS function is used to calculate the sum of values that meet any criteria. For example, you can calculate the total sales in east zone for product Pod Gun using SUMIFS formula.
In this article, you will learn:

What is SUMIFS function and how to use it?
Syntax for SUMIFS
Using SUMIFS() with tables and structural references
SUMIFS examples – simple, wild card
Using SUMIFS() with date & time values
Free sample file for SUMIFS formula
More formulas for data analysis

Read This »

May 25, 2018

Excel Howtos

Excel formula showing as text instead of actual result – How to fix the problem

Once in a while everyone is bound to come across this problem. You type a formula in a cell, then you press ENTER. Bam! nothing

Read This »

May 24, 2018

Pivot Tables & Charts

Distinct count in Excel pivot tables

Ever wanted to count distinct values in your pivot tables? Something like above:

Let’s say you have store sales data. Several products are sold on each day. When you make a pivot table from this data and add product count, Excel counts all products. But we want to see just the distinct count (ie if there is a duplicate product in a day, we want to count it just once).

Here is a simple trick to add distinct count to Excel pivot tables easily.

Read This »

May 23, 2018

Charts and Graphs

Beautiful Budget vs. Actual chart to make your boss love you

Call them by any name – Budget vs. Actual, Target vs. Actual, Goal vs. Progress, KPIs, Performance charts, but they are the bread and butter of business charting. So how about a drop dead gorgeous and insightful chart for your next meeting with the folks upstairs? Something like above.

Read on to learn how to create this chart in Excel.

Read This »

May 16, 2018

Charts and Graphs

60 sports in six charts

On twitter I follow many charting and visualization related accounts. One of them is @Andy Kriebel, who runs Makeover Monday. The idea is simple. Every Monday they publish a data-set and ask the community to visualize. Last Monday (7th May, 2018), they have published about toughest sport by skill data. This categorizes 60 sports by 10 skill categories to find out which sport is the toughest. Over the weekend, Andy posted a summary of all toughest sport viz entries. Many of the entries are made in Tableau. I thought it would be a fun challenge to re-create some of these charts in Excel. The result is this post. 60 sports in 6 charts. Check out the charts and download workbook to learn more.

First four charts are re-creations of Tableau designs. Last two are mine.

Read This »

May 14, 2018

Power BI

May the POWER BI with you [Star Wars Day Viz]

It is star wars day. Let’s celebrate the ultimate nerd day of the known universe in a way that resonates with us. Let’s dress up as storm troopers and make exploding 3d pie charts.

Just kidding. Let’s just make a cool visualization in Power BI instead.

Read This »

May 4, 2018

Pivot Tables & Charts

A trick to Pivot text values

We all know that Pivot Tables are best thing since avocado on toast. But they can’t slice text values and spread them in a table with Pivots. So how to take a large blob of text and turn it in to something meaningful like above?

Simple, we use Power Query.

Read This »

April 30, 2018