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.
Here is a New year gift to all our readers – free 2018 Excel Calendar & daily planner Template.
This calendar has,
Hellow there…
I wish you a merry Christmas & Happy New Year 2018. May your holidays be filled with joy, togetherness, celebrations and fulfillment. May your new year be filled with hope, energy and awesomeness.
I want to tell you how thankful I am for all your support in this year. Each time you click, visit, read, comment, share, view, listen, purchase, subscribe, follow, question, discuss or email, my heart is filled with gratitude and happiness. Thank you for all the love and support. 2017 has been another amazing year in Chandoo.org journey and I owe this to you.
‘Tis Friday and it is too hot in my home office to stand and type a longish post. So, let’s keep this skirtish (short and pretty).
How would you calculate long service bonus?
Let’s say you are HR manager at BigLargeInc. and you are looking at Julia’s service details. You have her employment start date, current date, her leave without pay details, as shown below.
You need to calculate how many days of continuous service Julia had (ie total service – duration on leave without pay). How would you write the formula?
Time for another round of unconditional love. Today, let’s learn about conditional formatting top tips. It is one of the most useful and powerful features in Excel. With just a few clicks of conditional formatting you can add powerful insights to your data. Ready to learn the top tips? Read on.
Imagine you are the first officer at ship terminal αε974F1 on remote planet Alderaan. Your job involves looking at terminal log to see anomalies in time space continuum. So one day after getting to work late, thanks to crazy traffic on the floating super way in your settlement, you are looking at latest terminal log for αε974F1 on Excel (of course Excel, what else are you going to use? Notepad?!?) and want to check all the records logged at 7 AM on any day. You don’t have all the time in universe to filter records one at a time. You don’t want to write a formula or something else as it is too early in the morning and the nearest Starbucks is 7 light years away. So what would you do?
Use filters of course.
Time for a quick residential break from all the Excel awesomeness. I have an exciting news to share with you all.
We have a home in New Zealand.
In the middle of 2016, we moved to New Zealand. We have been living in windy, lovely and beautiful Wellington ever since. After renting a house and sampling life in NZ, we decided to call it home for next few years. The next natural step is to purchase a home and move in.
We had to switch power providers soon, so I started reviewing the options. There are heaps of providers in New Zealand and each offer a ton of different plans. Some offer welcome bonus or credit worth up to $ 200. Other offer straight forward rates. Some others offer discount if you sign up for both electricity and gas with them. So how do you decide which one is better for you?
Using Excel of course.
The result is awesome. I ended up saving more than $1000 with a simple model. Puzzled? Curious? Check out this short but powerful video tut.
Yesterday, my mate from across the ditch, Hui posted about conditional rank formula (RANKIFS) using awesome SUMPRODUCT
Of course, not everyone can whip up a sumproduct formula like that. On a scale of One to Hui of Excel awesomeness, you would need to be at least an H to write sumproduct or countifs formulas shown in that post. So does it mean, you can’t conditional rank if you don’t know your X from L?
Don’t worry. We got you covered. You can still get your conditional ranks, without inception level array formulas. Simple, use pivot tables instead.
for more videos…
Everything from Power Query to Dashboards, Shortcuts to Formulas, Pivots to Charts in one course.
BeginnerTables & Structural Referencing
Cell referencing
Excel operators
IF
IntermediateSUMIFS, COUNTIFS
XLOOKUP NEW
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
Advanced
Advanced Pivot Tables
Distinct 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:
Excel challenges
Advanced Charting
Data Tables & Simulations
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.
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.
© All rights reserved.
