All articles with 'advanced excel' Tag
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 »
Hi friends & readers of Chandoo.org,
I am very happy to invite you to our newest online class, 50 ways to analyze your data. This program makes you an awesome analyst, training you on vital skills like data analysis, data science, visualization, modeling business problems and finding best solutions.
Please click here to know more about this program & enroll.
What is this course?
It is the age of big data. Alas, what we need is big insights. But finding even small insights buried in our data is a hard task. To find the stories hidden in your data, you need to follow a process like this:
- Collect & clean data
- Structure the data
- Model business problems
- Analyze the data (or solve the problem)
- Visualize results
- Find conclusions
- Add layers of complexity to the problem
- Build what-if scenarios
- Reach conclusions
- Take action
This is where the 50 ways to analyze your data course helps. In this program, we analyze 50 familiar, important and diverse business situations using several of the above steps.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 »
My twins (Nishanth & Nakshtra) are now almost 7. They are super keen to learn how computers work. So the other day, I showed them Code.org where there are several coding exercises disguised as games. They loved those games … err coding exercises. So that got me thinking… why not make a game in Excel that teaches kids simple programming concepts.
So I built a Snowman & Hot Chocolate Maze game. In this post, let’s understand how to build such a game using Excel VBA.Continue »
This is second episode of our Monthly Master Class.
In this one, you will learn all about SUMPRODUCT. Know all about basics to advanced usage of this powerful & versatile formula in this 98 minute master class.Continue »
Today is Friday the 13th. If you are a raging friggatriskaidekaphobiac, I suggest you to stop reading this post. For the rest of you, I have something fun.
Given a year in cell C3, let’s find out all the months with Friday the 13th. Something like above.Continue »
As announced earlier, here is the first one of our Master Classes. In this one, you will learn how to create advanced interactive charts in Excel by blending pivot tables, slicers, timelines, VBA, conditional formatting and charts. The final outcome is BEAUTIFUL!!! Monthly Master Class – Episode 1 – Trailer Check out a quick trailer of the […]Continue »
I have an exciting announcement for you. Starting this month, I will be running a monthly master class on advanced Excel. This is a video class to help advanced users learn creative, powerful ways to use Excel in their day to day work.
What is this Master Class?
The idea is simple. Every month, I will create a lengthy video discussing one of the below topics, specifically targeted at advanced Excel users. As our blog grew, we have a lot of experienced, advanced users and I want to help you achieve more. You will learn,
- Data Analytics
- VBA / Macros
- Business Modeling
- Business Intelligence
Each class will be on an different topic with in-depth coverage, creative outputs and awesome techniques. You can sign up for the class that suits your needs.Continue »
This is a guest post by Chirayu, a member of Chandoo.org forum.
Animating Charts in PowerPoint cannot be done without the help of 3rd party software’s that create a flash file of the chart & embed it into the presentation.
However there is a workaround for this. Save your chart as multiple images & insert them (overlapping on top of each other). Use VBA on Developer tab Controls such as Combo Box, Option Button, Check Box etc. to “Bring To Front”” the corresponding image. Thus giving the illusion of an Animated Chart in PowerPoint.
This guide will teach you how to animate the charts, using the three Developer tab Controls that were mentioned before. The code & functionality only works in Slide Show Mode. File must be saved as PowerPoint Macro-Enabled Presentation (*.pptm)Continue »
In the 53rd session of Chandoo.org podcast, let’s talk about data validation.
What is in this session?
In this podcast,
- What is data validation
- How Excel DV compares with database & software DV?
- Types of data validation rules
- List & custom rules explained
- Input & error messages
- Alternatives to data validation
- Enhancing data validation
- Removing data validation rules
- Homework problem for you
- Resources & show notes