Archive for January, 2013

Details about our Power Pivot Course [and a video for those of you not interested]

Published on Jan 30, 2013 in Excel Howtos, Power Pivot
Details about our Power Pivot Course [and a video for those of you not interested]

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.

Continue »

Introduction to DAX Formulas & Measures for Power Pivot

Published on Jan 28, 2013 in Power Pivot
Introduction to DAX Formulas & Measures for Power Pivot

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.

Continue »

Can you calculate vacation days in a period? [Homework]

Published on Jan 25, 2013 in Excel Challenges
Can you calculate vacation days in a period? [Homework]

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)

Continue »

Designing a dashboard to track Employee vacations [case study]

Published on Jan 24, 2013 in Charts and Graphs, Learn Excel

Employee Vacation Dashboard & Tracker using Excel

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

Continue »

What is Power Pivot – an Introduction [video]

Published on Jan 21, 2013 in Learn Excel, Power Pivot
What is Power Pivot – an Introduction [video]

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.

Continue »

Top 10 Formulas for Aspiring Analysts

Published on Jan 16, 2013 in Learn Excel
Top 10 Formulas for Aspiring Analysts

Few weeks ago, someone asked me “What are the top 10 formulas?” That got me thinking.

While each of us have our own list of favorite, most frequently used formulas, there is no standard list of top 10 formulas for everyone. So, today let me attempt that.

If you want to become a data or business analyst then you must develop good understanding of Excel formulas & become fluent in them.

A good analyst should be familiar with below 10 formulas to begin with.

Continue »

Insert Multiple Columns at once [Quick tip]

Published on Jan 15, 2013 in Excel Howtos
Insert Multiple Columns at once [Quick tip]

Here is a quick tip to start your week. Ever wanted to insert a few columns in between like this? Here is how to do it. Hold down control key. Select one column at a time Right click and choose Insert Done! Bonus tip: You can use this to insert rows too! More Quick tips.

Continue »

Introducing ‘Finance for Non-finance people’ training program

Published on Jan 10, 2013 in Financial Modeling, products
Introducing ‘Finance for Non-finance people’ training program

Dear readers & supporters of,

I am very glad to announce that our brand new online course – “Finance for non-finance people” is now available for your consideration. Please take a few minutes to read this short message to understand what this program is & how it benefits you. If you are ready to join, please click here.

What is Finance for Non-finance people course?

This course aims to teach financial fundamentals & introduce you to the world financial analysis in a no-nonsense way.

We start by introducing financial analysis and the basic jargon. Then we talk about 3 important components of any company’s finances – Balance Sheet, Income Statement & Cash flow Statement.

Then we discuss about various analytical techniques like ratio analysis, valuation mechanisms, break-even analysis.

Finally we explain advanced accounting concepts like long-lived assets, depreciation, explore trend analysis and unit economics.

In a nut-shell, this course takes someone with no finance background and makes them proficient in world of finance.

Continue »

3 upcoming courses to Make you awesome [Announcements]

Published on Jan 9, 2013 in Learn Excel, products
3 upcoming courses to Make you awesome [Announcements]

A quick announcement for all of you aiming to become awesome in Excel in 2013.

I am very happy to share our course timeline for first 3 months of 2013.
Upcoming Courses:
Here is a summary of courses I am planning to launch in next 3 months. I will be posting more details about these courses soon.

1. Finance for Non-finance people

Learn about financial basics, financial analysis thru Excel in our finance for non-finance people course. Designed by my partners at Pristine education, this course helps you gain insights to finance world quickly.

This course will be launched Thursday, January 10th – 2013

2. Power Pivot School

Jump in to power pivot, analyze data & extract insights like a pro by going thru this program. Aimed at Excel analysts & reporting professionals, this course helps you unleash power of PowerPivot to do awesome things.

This course will be launched on Wednesday, February 6th – 2013

Continue »

To-do List with Priorities using Excel

Published on Jan 7, 2013 in Excel Howtos, Templates
To-do List with Priorities using Excel

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?!?

Continue »

Creating an Agenda template in Excel

Published on Jan 4, 2013 in Templates
Creating an Agenda template in Excel

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.

Continue »

Please take our readership survey and help me make you awesome in 2013 [Surveys]

Published on Jan 2, 2013 in blogging

Hi friends & readers of

I hope you had a fantastic start to 2013. Mine was awesome so far.

This year, I am even more committed to making you awesome in what you do. As a first step, I have created a small survey to know how we can help you better this year.

Please take our 2013 readership survey [takes less than 5 mins to answer]

That is all for now. Wishing you an awesome, Excellent new year 2013.

Continue »