All articles in 'Learn Excel' Category

Pricing Tier Lookup formula

Published on Dec 1, 2015 in Excel Howtos, Learn Excel
Pricing Tier Lookup formula

Here is an interesting twist on the good old VLOOKUP. How to find the pricing applicable for given quantity of a product?

Something like above.

Looks interesting? Then read on…

Continue »

Employee training tracker & calendar – tutorial & download

Published on Nov 4, 2015 in Charts and Graphs, Learn Excel
Employee training tracker & calendar – tutorial & download

Imagine you are the head of training department at ACME Inc. You arrange training programs round the year to empower your team. It is hard work, coordinating between employees, trainers, department heads, venues and coffee machines. What if there is something to help you keep track of all this? I am not talking about getting you a shiny new iPad, you silly. I am talking about a tracker & calendar built in Excel that ties everything together (well, almost everything, you still have to fill the coffee machine.)

We are going to build a training program tracker & calendar using Excel.

Continue »

Can you extract numbers from text – homework

Published on Oct 30, 2015 in Excel Challenges, Learn Excel

Here is a quick homework to keep you busy this weekend.

Can you extract number of days from below text.

Nov15 PUTS (23 days)
March15 TIKS (3 days)
March1 TIKS (25 days)
June11 TIKS (10 days)

Assume the data is from cell A1.

Your solution should return the following:

Post your answers (formulas, VBA code or Power Query M code) in the comments.

Continue »

Use NUMBERVALUE() to convert European Number format

Published on Oct 6, 2015 in Excel Howtos, Learn Excel

If you deal with customers or colleagues in Europe, often you may see numbers like this:

  • 1.433.502,50
  • 9.324,00
  • 3,141593

When these numbers are pasted in Excel, they become text, because Excel can’t understand them.

Here is a simple way to convert the European numbers to regular ones.

Use NUMBERVALUE() Function.

Continue »

Excel is 30!!! – what was your earliest memory of Excel? [weekend poll]

Published on Oct 2, 2015 in Learn Excel

This week Excel turned 30. As per wikipedia,

Microsoft released the first version of Excel for the Macintosh on September 30, 1985

Let’s celebrate 30 years of Excel by reliving your first memory of it. Share your earliest memory of Excel in the comments section.

Continue »

Weighted Sorting in Excel [video]

Published on Oct 1, 2015 in Excel Howtos, Learn Excel
Weighted Sorting in Excel [video]

Imagine you are looking customer data like below and want to sort them by performance. If you sort the data by any one column, you will not get full picture of performance. To understand which customers rank low on performance, you need to defined a weighed sort, the kind of sort where you assign weights to each attribute (customer age, recent purchases and rate of returns) and come up with single score to sort them all.

Sounds interesting? Watch below video to understand how to do weighted sorting in Excel.

Continue »

Excel Links – Dashboards book delayed edition

Published on Sep 15, 2015 in excel links

Quick update about Dashboards for Excel book:

Dashboards for Excel - advanced Excel, analysis, reporting & automation book from Jordan Goldmeier and Purnachandra DuggiralaAs you may know, my first print book – Dashboards for Excel (co-authored with Jordan Goldmeier) is supposed to release today – 15th September, 2015. But unfortunately, there is some delay with the printing process, so the book is not available yet. Our publisher, Apress, tells us that it can take two more weeks.

Thanks to all of you who pre-ordered this book. I am sorry for this unexpected delay. Just few more weeks and you will be reading it. :)

Time for another round of Excel links

Continue »

Weekend poll: Formulas or Pivot Tables?

Published on Sep 4, 2015 in Learn Excel

Time for a quick weekend poll. What is your favorite tool for data analysis?

  • Formulas
  • Pivot Tables
  • Or both

Post your choice in the comments. Also mention the number of years Excel experience you have.

For ex, my answer is: Both (10 years)

Continue »

Book Announcement: Dashboards for Excel is launching on 15th September

Published on Sep 3, 2015 in Charts and Graphs, Learn Excel

I have an exciting announcement to share with you. My first ever print book – Dashboards for Excel, co-authored with Jordan Goldmeier is releasing on 15th September.

Dashboards for Excel – what is it?

As the name suggests this book is a handy guide for anyone preparing dashboards, management reports and business intelligence workbooks using Excel. The key benefits are,

  • Learn the visualization tools, charts, tables, and graphs important to management.
  • Understand what management doesn’t want to see in a report
  • Turn around dashboards faster and cheaper than ever before
  • Understand the key role dashboards play in an organization
  • Analyze real-world dashboards to apply important features to your own organization
  • Utilize understated, but powerful, Excel formulas and VBA code
  • Avoid common pitfalls in Excel development and dashboard creation
  • Get started with the Excel data model, PowerPivot, and Power Query

As you can see, this is an advanced Excel book with lots of examples, case studies and practical tips.

Continue »

How to create cascading drop downs in Excel – video

Published on Aug 31, 2015 in Excel Howtos, Learn Excel
How to create cascading drop downs in Excel – video

Cascading drop downs enhance usability of your dashboards & interactive workbooks. A cascading drop-down is a 2 or more level selection mechanism. When you have 100s of selection choices, instead of creating one massive drop down or combo-box, you can set up multiple levels of drop downs, so that users can narrow down their selection. For example, users can select Country, State and then City using cascading drop downs.

There are many ways to setup cascading drop downs. You can use formulas coupled with either data validation or form controls. You can also use Slicers. In this video we will review these techniques.

Continue »

Build models & dashboards faster with Watch Window

Published on Aug 30, 2015 in Learn Excel, VBA Macros
Build models & dashboards faster with Watch Window

Here is a familiar scenario: You are building a dashboard. Naturally, it has a few worksheets – data, assumptions, calculations and output. As you make changes to input data, you constantly switch to calculations (or output) page to check if the numbers are calculating as desired. This back and forth is slows you down.

Use Watch Window to reduce development time.

Continue »

Make a quick funnel chart in Excel to track sales performance [video]

Published on Aug 29, 2015 in Charts and Graphs, Learn Excel
Make a quick funnel chart in Excel to track sales performance [video]

Funnel charts are useful to visualize sales & marketing performance. In this brief video, let’s understand how to make a quick funnel chart in Excel.

Read on to learn how to make funnel charts & to download a template for your funnel analysis needs.

Continue »

Summarize only filtered values using SUBTOTAL & AGGREGATE formulas

Published on Aug 25, 2015 in Learn Excel
Summarize only filtered values using SUBTOTAL & AGGREGATE formulas

We all know the good old SUM() formula. It can sum up values in a range. But what if you want to sum up only filtered values in a range? SUM() doesn’t care if a value is filtered or not. It just sums up the numbers. But there are other formulas that can pay attention […]

Continue »

Dashboard best practice: Highlight user selection [video]

Published on Aug 20, 2015 in Charts and Graphs, Learn Excel

Here is a best practice to improve your dashboard usability. If you have an interactive dashboard, highlight user selections thru conditional formatting.

Check out below quick video to understand what this means.

Continue »

A simple trick to make your dashboards user friendly [video]

Whenever you have a dashboard that is quite long or spans across multiple worksheet tabs, it can be hard to use. Here is a simple trick to make your dashboards user friendly. If your dashboard has form controls, create duplicate sets of them and place them in locations where users are looking. For example, If your […]

Continue »