All articles in 'Excel Howtos' 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 »

Edit cells & formulas faster [shortcut]

Published on Nov 16, 2015 in Excel Howtos
Edit cells & formulas faster [shortcut]

Let’s keep this simple & short.

Whenever you are editing cells or formulas, the usual sequence is like this:

  1. Double click on the cell you want to edit
  2. For existing cells: Go to the left most / right most part and start typing
  3. For blank cells: start typing right away

Here is a faster sequence:

Read on…

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 »

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 »

Case Sensitive Lookups

Published on Sep 7, 2015 in Excel Howtos
Case Sensitive Lookups

We all know that VLOOKUP (and its cousins MATCH, HLOOKUP and LOOKUP) are great for finding information you want. But they are helpless when you want to do a case-sensitive lookup.

So how do we write case sensitive VLOOKUP formulas?

Simple. We can use EXACT formula.

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 »

Dynamic Print Areas

Published on Aug 24, 2015 in Excel Howtos
Dynamic Print Areas

Yesterday, you learned about Print Areas – a time & paper saving feature of Excel. While print areas are great, you can only set up one print area per sheet. What if you want to print either report or data based on user selection? 

In such cases, you can set up dynamic print areas. 

That is right. See above demo to understand how it looks. Read on to learn how to set up dynamic print areas.

Continue »

Save time & paper with print areas in Excel

Published on Aug 23, 2015 in Excel Howtos

Here is a very simple but effective tip: use print areas. Oh.. but what is a print area? Imagine looking at a report or dashboard worksheet. Now, that sheet also has few calculations, some extra stuff to the right of the report. When you print, you don’t need those extra elements. Just the report will do. This […]

Continue »

Reconcile debits & credits using Solver [Advanced Excel]

Published on Aug 18, 2015 in Analytics, Excel Howtos
Reconcile debits & credits using Solver [Advanced Excel]

Here is a tricky problem often faced by accountants and finance professionals: Let’s say you have 5 customers. Each of them need to pay you some money. Instead of paying the total amount in one go, they paid you in 30 small transactions. The total amount of these transactions matches how much they need to pay you. But you don’t know which customer paid which amounts. How would you reconcile the books?

If you match the transactions manually, it can take an eternity – after all there are more than 931 zillion combinations (5^30).

This is where solver can be handy. Solver can find optimal solution for problems like this before you finish your first cup of coffee.

Continue »

Use mail merge to create custom letters, invoices, labels and more

Published on Aug 17, 2015 in Excel Howtos
Use mail merge to create custom letters, invoices, labels and more

The idea of mail merge is simple & powerful. Imagine you handle recruitment at a large company. You just hired 300 analysts for the big data division. The next job – generate employment offer letters for each of them. Of course, you don’t want to type these letters one at a time. You have the details of all the 300 offers (name, email, address, offer code, role, benefit package details and date of joining etc.) in a spreadsheet. You also have a template of the offer letter.

Enter mail merge. This powerful feature of MS Word combines (Excel) data with Word document contents to generate all the 300 letters in just few minutes.

Continue »

In-cell 5 star chart – tutorial & template

Published on Aug 15, 2015 in Charts and Graphs, Excel Howtos
In-cell 5 star chart – tutorial & template

Whenever we talk about product ratings & customer satisfaction, 5 star ratings come to our mind. Today, let’s learn how to create a simple & elegant 5 star in-cell chart in Excel. Something like above.

Read on to learn how to create the above chart.

Continue »

Work with charts faster using selection pane & select object tools [quick video tip]

Published on Aug 14, 2015 in Charts and Graphs, Excel Howtos

Working with multiple charts (or drawing shapes / images) can be a very slow process. But here is a secret to boost your productivity.

Use selection pane & select object tools

Selection Pane & Select Objects?

If you have never heard of these, don’t worry. These are 2 very powerful features hidden in Excel. Once you know how to unlock them, you will never look back.

How to use selection pane & select object tools to work with charts faster – Video

In this video, understand how to use these powerful features to work with charts faster.

Continue »

VLOOKUP the last value

Published on Aug 11, 2015 in Excel Howtos, Learn Excel
VLOOKUP the last value

VLOOKUP is one of the most useful Excel functions. So much so that I even wrote a book about it. But it has one serious limitation.

It looks up the first occurrence and returns corresponding data.

What if you want to find the last value?

Say, for example, you are looking at a task assignment list and want to know what is the last task assigned to employee Emp13?

We want to extract the task “Make amazing workbook”. Of course our good old VLOOKUP stops once it finds Emp13 and returns the answer as “Create intuitive workbook”.

Continue »

Declutter your reports by showing icon only

Published on Aug 9, 2015 in Excel Howtos
Declutter your reports by showing icon only

Conditional formatting is one of the most powerful & awesome features of Excel. It is very easy to setup. Naturally, people use it extensively. But the default conditional formatting rules can clutter your reports. Here is one tip that can declutter your reports.

Just show the formatting, not values.

See the above report.

Continue »

Remove duplicate combinations in your data [quick tip]

Published on Aug 7, 2015 in Excel Howtos
Remove duplicate combinations in your data [quick tip]

By now, we know how to remove duplicates from data. You can use the Remove Duplicates button to do that.

But do you know that we can use remove duplicates button to get rid off duplicate combinations too?

Remove duplicate combinations – Tutorial

To remove duplicate combinations in your data, just follow below 4 steps:

  1. Select your data
  2. Click on Data > Remove Duplicates button
  3. Make sure all columns are checked
  4. Click ok and done!

See this demo:

Continue »