All articles with 'advanced excel' Tag
This week, I am running a contest on YouTube. One of the criteria for picking winners is that they must comment on my video. So far, I got more than 200 comments. To make my job easier, I want to export the video comments to an Excel file. Turns out this is easily done once you have a Google developer API key. In this article, let me explain the process for extracting Youtube video comments to Excel table.Continue »
Excel Pivot tables make data analysis and visualization easy. With the help of these advanced pivot table skills, you can create powerful data analytics and reports. New to Pivot Tables? If you are new to Pivot Tables, check out this excellent introduction to Pivot Tables page. × Dismiss alert Table of Contents #1 – One Slicer, Two […]Continue »
Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. In this page, learn how to,
- Introduction to IRR & XIRR functions
- Calculate rate of return from a set of cash-flows with XIRR
- Simulating purchase or terminal value changes with data tables
- Apply conditional formatting to visualize the outputs
- Common issues and challenges faced when using XIRR
Can we predict cricket match score in Excel? Using machine learning, ensemble modeling, multiple regression and Excel formulas we can. This tutorial explains how.Continue »
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.Continue »
Ever wanted to calculate distance using Excel – between two locations (physical addresses)? If we know the addresses, we can go to either Google Maps or Bing Maps and type them out to find the distance and travel time. But what if you are building some model (or calculator) and want to find out the […]Continue »
Game for some charting awesomeness?
Off late, I have been doing a lot of data analysis and visualization on performance ratings, salary hike, gender pay equality etc. Today let me share you an awesome way to visualize massive amounts of data.
Scenario: Your organization of 3,686 people recently went thru annual performance ratings & review process. At the end of it, everyone was offered some salary increase (from $0 to $24,000 per year). You have 7 business groups. How do you tell the story of all these salary hikes in one chart?
How about the one above?
Ready to know how to create this in Excel? Read on.Continue »
When faced with tough problems I react in one of three ways
- Come up with ingenious solutions
- See if a simpler cheat solution is possible
- Sit back and ignore
For most problems, I choose 3rd reaction. Occasionally, I rely on 2nd option and very rarely the first one.
When faced with a tricky time sheet summary problem (as outlined above), after initial lethargy I wanted to solve it.Continue »
In this amazing guest post, the winner of our 2016 dashboard contest – Chandeep – Explains how he constructed the jaw dropping beauty (shown above) using Excel, creativity, love and sweat. Grab a full cup of coffee (or whatever liquid fancies you) and read on. Take lots of notes and play with the ideas in Excel while reading to maximize your learning.
Thanks Chandeep.Continue »
Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.
And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.
Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).
So how would you write the lookup formula?Continue »
First a quick personal update: There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.
Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.
There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.Continue »
Every week, we read news about failed analysis projects. If you listen carefully, you can hear the grunts, screams and curses of thousands of analysts all over the world about their analysis nightmares.
At Chandoo.org, we talk a lot about best practices for data analytics. So today, let’s peek in to the dark side and understand the mistakes that can turn your analysis project into a nightmare.
There are 3 parts in any analysis project
To understand these worst practices in analysis world, first let’s break analysis projects in to 3 parts.
- Data Structure
- Tools & Construction
Let’s deep dive in to each area of the analysis projects to see what can go wrong.Continue »
Around 2 months back, I asked you to visualize multiple variable data for 4 companies using Excel. 30 of you responded to the challenge with several interesting and awesome charts, dashboards and reports to visualize the financial metric data. Today, let’s take a look at the contest entries and learn from them.
First a quick note:
I am really sorry for the delay in compiling the results for this contest. Originally I planned to announce them during last week of July. But my move to New Zealand disrupted the workflow. I know the contestants have poured in a lot of time & effort in creating these fabulous workbook and it is unfair on my part. I am sorry and I will manage future contests better.Continue »
Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?
Finding the first responded team is tricky.
Today let’s take up this problem as a case study and understand various methods to solve it. We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.Continue »
This is third episode of our Monthly Master Class.
In this one, you will learn how to get maps in to your Excel workbooks. Understand 5 key techniques for making maps based visualizations in Excel – from regular charts to cell grids to VBA to Power Maps, everything is covered in this intense Master Class.Continue »