All articles with 'Microsoft Excel Formulas' Tag

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 »

CP047: Best Excel tools for Entrepreneurs

CP047: Best Excel tools for Entrepreneurs

In the 47th session of podcast, let’s see how Excel can make you an awesome entrepreneur.

What is in this session?

In this podcast,

  • Why Excel for entrepreneurs
  • Key areas of a business owner’s work
    • Projects & to dos
    • Finances
    • Customers & marketing
    • Planning & strategy
    • Processes & workflows
  • 5 features of Excel that help
  • Conclusions
Continue »

Formula Forensics No. 038 – Find Which Worksheet a Max or Min Value is located on

Published on Oct 14, 2015 in Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 038 – Find Which Worksheet a Max or Min Value is located on

Learn how to find which worksheet a max or min value occurs on using this neat formula

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 »

How countries spend their money – chart alternatives

Published on Sep 22, 2015 in Charts and Graphs

Econimist’s daily chart is a one of my daily data porn stops. They take interesting data sets and visualize in compelling ways. While the daily chart page is insightful, sometimes they make poor charting choices. For example, this recent chart visualizing how countries spend their money uses a variation of notorious bubble chart. Click on the chart to enlarge.


What is wrong with this chart?

Bubble charts force us to measure and compare areas of circles. Unless you have a measuring tape somehow embedded in your eyes and you are a walking human scientific calculator, you would find this task impossible.

So when you look at the chart and want to find out what percentage Japanese spend on restaurants or how much Americans pay for housing, your guesses will have large error margins.

Not only bubble charts are difficult to read, they are very hard to align. So when you have a bunch of bubbles, no matter how hard you try, your chart looks clumsy (see how the Russian food bubble eats in to Mexico’s bubble, as if it is too hungry 😉 )

Let’s check out a few alternatives to this chart. Read on…

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 »

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 »

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 »

Use GETPIVOTDATA to integrate pivot tables with dashboards

Published on Aug 26, 2015 in Pivot Tables & Charts
Use GETPIVOTDATA to integrate pivot tables with dashboards

Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.

How to combine the power of pivot tables with elegance of your dashboards?

The answer is: GETPIVOTDATA()

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 »

Give descriptive titles to your charts for best results

Published on Aug 19, 2015 in Charts and Graphs
Give descriptive titles to your charts for best results

Here is a simple & effective tip on charting.

Give your charts descriptive & bold titles.

How to set up title that are smart & descriptive?

Simple, follow below steps.

  1. Create the title you want in a cell
  2. Select the chart title
  3. Go to formula bar, press = and point to the cell with title
  4. Press enter.
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 »

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 »

Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers

Published on Jun 24, 2015 in Learn Excel, Pivot Tables & Charts
Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers

Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.

Slicers – what are they?

Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.

For example, let’s say you are looking at sales by customer profession in a pivot report. And you want to see how the sales are for a particular region. There are 2 options for you do drill down to an individual region level.

  1. Add region as report filter and filter for the region you want.
  2. Add a slicer on region and click on the region you want.

With a report filter (or any other filter), you will have to click several times to pick one store. With slicers, it is a matter of simple click.

Read more to learn all about slicers

Continue »