All articles with 'downloads' Tag

Lets say you are responsible for sales of 100s of products (which belong to handful of categories). You are looking at sales of each product in last month & this month. And you want to understand whether sales are improving or declining by category. How would you do it?

Turns out, this is not a difficult problem. In fact, this question is asked every day & answered using Advances vs. Declines chart.

You may have seen this chart in financial newspapers or websites. Shown above, Advances vs. Declines chart tells us how many items have advanced & how many have declined.

Read on to learn how to create this chart using Excel.

Hi folks,

I have a quick announcement & an awesome Power Pivot technique to share with you. First the announcement.

Only few hours left to join our Power Pivot course…

As you may know, I have opened enrollments for our inaugural batch of Power Pivot course few days ago. The aim of this course is to make you awesome in Excel, Advanced Excel, Dashboards & Power Pivot.

We will be closing the doors of this program at midnight, today (11:59 PM, Pacific time, Friday, 15th of February).

If you want to join us, click here and enroll now.

How many people have joined the class?

At the time of writing this, we have 195 students enrolled in Power Pivot class. We are eager to share Power Pivot knowledge & techniques to as many more of you as possible. So go ahead and join us because you want to be awesome in Excel & Power Pivot.

When comparing 2 sets of data, one question we always ask is,

• How is first set of numbers different from second set?

A classic example of this is, lets say you are comparing productivity figures of your company with industry averages. Merely seeing both your series as lines (or columns etc.) is not going to tell you the full story. But if we can shade our productivity line in red or green when it is under or above industry average… now that would be awesome! Something like above.

When it comes to analyzing business data, managers are always asking, “so how many distinct x each y is doing?”

And that sends us, data analysts & reporting professionals running from pillar to post figuring out the best way to do it.

• We can use variations of SUMPRODUCT, COUNTIFS etc, but the methods are not flexible..
• We can use VBA, but it would become slow as you add more data.
• We can use Pivot tables, but it only gives half of what we want ie each y part, but not distinct count of x.
• We might as well shave our head with a shovel before manually counting values.

And that brings us to 2 distinctly simple solutions:

• Using Power Pivot & Excel 2010
• Using regular pivot tables in Excel 2013

Today, lets talk about these 2 approaches & see why they are so better than anything else for distinct count situations.

Today lets tackle a familiar data clean-up problem using Excel – Transposing data.

That is, we want to take all rows in our data & make them columns. Something like this:

Learn these 4 techniques to transpose data:
1. Using Paste Special > Transpose
2. Using INDEX formula & Helper cells
3. Using INDEX, ROWS & COLUMNS formulas
4. Using TRANSPOSE Formula

Hello folks,

If this article was a person, they would be schizophrenic. You see, it has 2 purposes:

• Give you all the details about my upcoming Power Pivot course
• Give you a solution to last week’s vacation days problem

Details about Power Pivot Course
Power Pivot, an Excel add-in makes it easy to connect, analyze & visualize massive amounts of data. This course aims to teach you how to use Power Pivot to analyze data, create advanced reports & prepare dashboards all from familiar interface of Excel. This is ideal for data analysts, reporting & MIS professionals, business analysts, managers & dashboard makers.

Its Friday, that means time for another Excel challenge for you.

Calculate vacation days in a period:

Your mission, if you choose to accept it,

Step 1: Download the hom work problem file.

Step 2: Calculate number of vacations taken in a period. Specifically,

1) How many vacations are taken between start & end dates, assuming complete vacation should be inside the start & end date period?
2) How many vacations are taken such that at least one day of vacation is between start & end dates?
3) How many people took vacations? (if same person took multiple vacations, then count it as 1)

HR managers & department heads always ask, “So what is the vacation pattern of our employees? What is our average absent rate?”

Today lets tackle that question and learn how to create a dashboard to monitor employee vacations.

What do HR Managers need? (end user needs)

There are 2 aspects tracking vacations.

1. Data entry for vacations taken by employees
2. Status dashboard to summarize vacation data

A while ago, we published a new year resolution template. This was a hit with our reader with thousands of you downloading it. During last week, Peppe, one of our readers from Italy, took this template and made it even more awesome.

The original template had tasks and completion check mark. As you finish each task, you can see the progress bar moving.

Peppe added priorities to this. With his new version, progress is measured based on how much priority we assigned that particular task. Pretty neat eh?!?

When planning a meeting, an event or activity, we deal with many individual steps (each taking certain amount of time). Today let us understand how to create an agenda template in Excel using simple formulas.

2012 has been the most awesome year since we started Chandoo.org.

The credit for this goes to our community of Excel users – that is you.

For practically every day of 2012, you have inspired me (and all of us at Chandoo.org) to learn something new, share and make you awesome. I know I say this many times, but I can never feel enough – Thank you for your support to Chandoo.org.

Apart from you, there are many other amazing people & companies that played a role in our success. In this post, let me highlight them & express my sincere gratitude.

Here is a New year gift to all our readers – free 2013 Excel Calendar Template.

This calender has,

• One page full calendar with notes, in 4 different color schemes
• Daily event planner & tracker
• 1 Mini calendar
• Monthly calendar (prints to 12 pages)
• Works for any year, just change year in Full tab.

Recently, I wrote a tutorial on tax burden in USA chart.

Jared, One of our readers liked this chart very much. Jared works as a workforce scheduler and has data similar to our chart. So he applied the same technique to analyze monthly service levels for last 7 years & sent me the file so that I can share it with all of you.

First take a look at the demo of Jared’s chart. And read on to know how this works.

Rama, one of our readers emailed this:
I Have Many List boxes In That I need to Hide Few Of them Using Check box

Show Hide list boxes by using a check box

We can use check box and a bit of VBA to do this easily. Something like above demo.

Read on to learn how to do this using Excel & VBA.

Are you ready for an Excel challenge?

Today, your job is very simple. Just find a pattern in a text and return corresponding value.

Your Homework:

In a range we have some resource types & their billing rates.

In another range, we have some descriptions. Each description contains a resource type somewhere inside it. We need to retrieve billing rate for each description by looking up which resource type is mentioned in it.

See above diagram.