Excel Challenges
Elevator problem – Excel homework
The other day while I was in lift (elevator), it made an alarm like sound and won’t close the doors. Turns out there are one
December 13, 2018
Welcome to Chandoo.org
Hello, Namaste & Kia Ora. Welcome to Chandoo.org.
My name is Chandoo. My mission is to make you awesome in your work. 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 again.
Power Query
Quick tip: Make a list of numbers (or dates) in Power Query easily
Just a quick tip to revive the blog from a month long silence. I am alive and kicking. I have been occupied with a quest to rescue princess & maidens on video game console. Recently we bought SNES classic console from Nintendo and I have been playing Legend of Zelda – a link to past regularly. As it is almost summer, I am also enjoying the beautiful outdoors in Wellington. All this means, little time for blogging. I will try to post a few more times before the end of year.
Make a list of numbers in a jiffy with Power Query:
We know that in Excel, you can type a few numbers and use the fill handle to fill down (or up etc.) numbers as you want.
But what if you need some numbers in Power Query?
November 13, 2018
3D Maps (non Power Maps)
Hui’s World ! – An Excel Project of Global Scale.
Hui’s World is an advanced Excel animated chart displaying the Earth, Continents, Countries and States and key geographical features.
This post will describe how it works and how it was made.
October 23, 2018
Excel Challenges
Leave entitlement vs. usage analysis with Power Query
Last Friday, I asked you to analyze “sick leave entitlement vs. usage” data and answer homework questions. We got several interesting responses to that. Today, let me share a quick video highlighting how to analyze such data with Power Query.
This is part of our Power Mondays series, where every Monday you will learn something new & useful about Power BI, Power Query and Power Pivot.
September 24, 2018
Excel Challenges
How many people used their entire sick leave entitlement? [Power Query / Excel homework]
Imagine you are the HR analyst at BigLargeCompany. You are asked to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.
You have two tables – emps & leaves as illustrated below.
Your mission is to find out answers to below questions.
- How many employees used exactly 100% of their entitled sick leave?
- How many employees did not take any sick leaves?
- Listing of all employees who used 100% of their entitlement
Use either Power Query, Excel formulas or any other technique to answer the questions.
September 21, 2018
Excel Howtos
Lenient lookup [Advanced Formula Trick]
We all know VLOOKUP (or INDEX+MATCH) as an indispensable tool in our Excel toolbox. But what if you want the lookups to be a little gentler, nicer and relaxed?
Let’s say you want to lookup the amount $330.50 against a list of payments. There is no exact match, but if we look 50 cents in either direction, then we can find a match. Here is a demo of what I mean.
Unfortunately, you can’t convince VLOOKUP to act nice.
Hey VLOOKUP, I know you are awesome and all, but can you cut me some slack here?
VLOOKUP is tough, reliable and has a cold heart. Or is it?
In this post, let’s learn how to do lenient lookups.
September 13, 2018
Power BI
My top 5 tips for designing beautiful Power BI reports
Power BI allows you to create rich, interactive and informative reports. But it is also a massive pain to create beautiful yet functional reports with Power BI. Over the last 12 months of heavy usage, I have picked up a few tricks to speed up my Power BI report design time. In this post, let me share my top Power BI design tips for creating pretty reports.
September 10, 2018
Master Class
5 tips: Power Query for Accountants (and finance people) – Free Masterclass
Do you work with accounting or finance data? You are going to love, no scratch that, adore Power Query. It can save you precious time, make you look like a hero in-front of clients and keep you sane.
In this masterclass, learn all about Power Query for accountants (and other kinds of finance people) & 5 tips.
Why Power Query?
Power Query is a data processing software. Using Power Query, you can create process driven programs (queries) to collect data, clean or reshape it, calculate things and publish final output as a table. Once you create a query in PQ, when the underlying data changes, you just refresh the query and brand new data will be fetched, cleaned and published for you.
For example, you can use Power Query to combine ledger files from various accounts to one master file with Power Query. No more VBA or manual copy pasting. Just one query and you are done.
September 3, 2018
Topics
Formulas
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
Pivot Tables & Data Analysis
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
Charting
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
Excel & Power BI for you
Beginner
Intermediate
Advanced
Power BI
Beginner
If you are new to Excel or have never used it, use below links to come up to speed.
Intermediate
Have been using Excel for a while? Check out these tutorials to learn more and become awesome.
Advanced
Work with Excel a lot and know your game well? We have some very-advanced topics for you too. Check out:
Power BI
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
What people say about Chandoo.org
I have been using Excel since Excel 4 days, but understood the true potential of Excel only after learning at Chandoo's site a few months ago. His contents, language, topics covered and methodologies are unmatchable. His website has been a great inspiration. This guy is amazing. Great regards to Chandoo.
Vivek K
Long time Excel user
Hi Chandoo,
I just want to say I really appreciate your Excel tutorials and all your hard work that it took to compile the huge library of content. Your site has helped me gain a better understanding of Excel which has helped me in my job.
You are helping the world!
Felix H
Accountant
Throughout my life as an administrative assistant; your site has been invaluable to me. And now that I’m a recruiter; it’s still an amazing resource for when I need to teach someone something quickly.
You are a friggin’ Rock Star!!!
Thank you for everything you do.
Jason S
Recruiter
Courses for you
Excel School
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.
VBA Classes
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.
=VLOOKUP(chandoo, on_social_media,TRUE)
Get FREE Excel & Power-BI Newsletter
One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.
Online Classes
About
© 2018 All rights reserved