Hello, Namaste & Kia Ora. Welcome to Chandoo.org.
My name is Chandoo. My mission is to make you awesome in Excel and Power BI.
I do this by sharing Excel & Power BI tutorials, examples, tips, videos and articles on this website. I live in Wellington, New Zealand with my beautiful wife Jo & our twins Nishanth & Nakshatra. Take a minute to browse various topics of the site to see how I can help you.
Thank you and welcome.
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
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.
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.
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.
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.
Analyst’s life is busy. We have to gather data, clean it up, analyze it, dig the stories buried in it, present them, convince our bosses about the truth, gather more evidence, run tests, simulations or scenarios, share more insights, grab a cup of coffee and start all over again with a different problem.
So today let me share with you 35 shortcuts, productivity hacks and tricks to help you be even more awesome.
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.
Are you hungry from some Excel awesomeness? It’s launch time. I am very happy to announce that our most popular analytics online class – 50 ways to analyze data is opening up for enrollments in first week of May 2018. As the name suggests, 50 ways program is all about data and making sense of it. You will learn many ways to analyze, visualize and present data to your clients and superiors. Next time someone asks you, “Can you look at this data and tell us what is going on?” you can confidently say YES.
Learn all about 50 ways program and signup for the waiting list.
In this article, Let me show case one of the examples from 50 ways to analyze data course and how it can help you.
for more videos…
BeginnerTables & Structural Referencing
Cell referencing
Excel operators
IF
IntermediateSUMIFS, COUNTIFS
VLOOKUP
INDEX + MATCH lookups
AdvancedMulti-condition lookups
Array Formulas
OFFSET
INDEX
Lists100+ Excel Formulas list
Top 10 formulas
15 Everyday formulas
Challenges & Home workExcel Homework
BeginnerExcel Pivot Table Tutorial
Multi-table pivots with data model
AdvancedDistinct count in Pivots
Ranking values in Pivots
GETPIVOTDATA
How to use slicers
Lists35 shortcuts & tricks for data analysis
Top 10 pivot table tricks
15 quick & powerful ways to analyze business data
ResourcePivot Tables Page
BasicsHow to pick right charts
Why bar charts should start at 0
Add a line to column chart
Correlation vs. Causation
ExamplesHand-drawn charts
Budget vs. Actual chart
Interactive charts
Chart typesHistograms & Pareto charts
Forecasting with charts
Gantt chart
Funnel chart
5 star chart
Indexed charts
Panel charts - Small multiples
AdvancedTarget vs. Actual progress - Biker on a hill chart
Stacked chart with indicators
Cropped chart - when some values are too big
Jitter plot
Joy plot
Step chart
Dynamic chart with check boxes
Lists & TricksCharting shortcuts & tricks
Using shapes in charts
Awesome chart titles with this trick
Use chart themes and styles
Use selection pane to work with charts faster
If you are new to Excel or have never used it, use below links to come up to speed.
Have been using Excel for a while? Check out these tutorials to learn more and become awesome.
Work with Excel a lot and know your game well? We have some very-advanced topics for you too. Check out:
Power BI is the most exciting thing to happen for your data since spreadsheets. If you are looking for a new skill to learn this year, I highly recommend Power BI. Check out below tutorials and get started today.
Introduction to Power BI – your first tutorial
Power BI Example – Commonwealth games tracker
Power Mondays – Learn all about Power BI, Power Query & Power Pivot
Learn how to work with data, make calculations, pivots, create amazing charts and powerful dashboards from scratch using Excel School + Dashboards program. Suitable for analysts, managers or professionals who need to use Excel often.
Use VBA to automate your tasks and build powerful spreadsheet based apps. In this course, learn all about how to program with VBA, how to use the language and object model to your advantage. Suitable for people who build a lot of things with Excel.
One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.
© 2019 All rights reserved
