All articles with 'advanced excel' Tag

We all have some projects to manage every now and then and there are needs of various trackers that help us in gauging the progress of the same. One of the most important things are heat maps that quickly help us in visually displaying the names of the projects that need special attention and resolve issues that are impacting them.

Lets say you are looking at some data as shown above and wondering what is the sum of budgets for top 3 projects in East region with Low priority. How would you do that with formulas?

Are you making these 10 rookie mistakes when creating dashboards?

Watch below to video to understand what these mistakes and how to avoid them.

10 Rookie mistakes to avoid when making dashboards [23 mins]

Imagine you have a worksheet with lots of charts. And you want to make it look awesome & clean.

Solution?

Simple, create an interactive chart so that your users can pick one of many charts and see them.

Today let us understand how to create an interactive chart using Excel.

One of the popular uses of Excel is to maintain a list of events, appointments or other calendar related stuff. While Excel shines easily when you want to log this data, it has no quick way to visualize this information. But we can use little creativity, conditional formatting, few formulas & 3 lines of VBA code to create a slick, interactive calendar in Excel. Today, lets understand how to do this.

Hello folks,

I have a super exciting news to share with you all. I will be conducting a series of Masterclasses on Advanced Dashboards & Excel in USA this Summer (May – June 2012). So if you live in (or nearby) Chicago, New York, Washington DC or Columbus OH, and want to one up your dashboard skills, you know what to do next.

I am conducting these masterclasses in partnership with PowerPivotPro.

Click here to know more and book your slot.

Recently, I had a peculiar problem. I have a list of zip codes and I wanted to find out nearest zip codes for each of them.

Now, If I wanted to find out near by zip codes for one area, I could go and search in Google. But, how to do it for dozens of them?

Today, lets understand how you can use Excel (that’s right) to do this automatically. We will be using Excel 2013 for this.

Today, lets understand how to use Calculated items feature in Pivot tables. We will use a practical problem many of us face to learn this feature – ie calculating conversion ratio from a list of sales calls.

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 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.

A measure is a formula for the values area of Power Pivot table.

A measure can be implicit or explicit.

Implicit measures are created automatically when you drag and drop a field in to Power Pivot values area. For example, in last week’s introduction, we created an implicit measure for SUM of Sales by dragging and dropping the sales amount field in to values area of our power pivot table.

Explicit measures are created by you using New measure button in Power Pivot tab (or Calculated Field button in Excel 2013 Power Pivot tab). You can also create a measure in the Power Pivot window.

Learn what measures are, how to create them using DAX (Data Analysis Expression) formulas in this video tutorial.

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)

Today, lets talk about Power Pivot & understand it.
What is Power Pivot?
Power Pivot is an Excel add-in to connect, analyze & visualize massive amounts of data..

Lets take a closer look at the definition.

Connect: You can use multiple tables of data & set up relationships between them using Power Pivot. For example, you can connect customer details to sales transactions so that you can summarize sales by customer location or gender easily.

Analyze: You can create simple pivot table style reports or create something exceedingly complex by defining your own calculated fields for values area of the pivot. There is a whole set of formulas defined for exactly this purpose, called as DAX formulas.

Visualize: Instantly filter your reports using slicers, time lines (Excel 2013 or above only), conditional formats, pivot charts etc. You can even define KPIs and see the performance in bands.

Massive Amounts of Data: Although your typical Excel worksheet contains a million rows, if you tried to load even half of those with any data, Excel would quickly become slow & lazy. Power Pivot can take a million rows for breakfast and would be hungry for more. It can processes millions of rows of data very quickly and easily, all from the comfort of a standard desktop or laptop.