Archive for October, 2015

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:
23
3
25
10

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

Continue »

Save a range as text file using VBA [tutorial]

Published on Oct 28, 2015 in VBA Macros
Save a range as text file using VBA [tutorial]

Last night I asked members of our Chandoo.org facebook page to share an Excel problem you are struggling with.  Francis asked,

How to save a file as .txt in vba without quotes? When I save as .txt, the file has got quotes inside of it. I used the code Print, but it didnt work because the file loses its delimitation.
Does anyone know how to solve this?

Let’s understand how to save a range as text and overcome the double quote problem.

Continue »

Contest: Visualize KPI data

Published on Oct 23, 2015 in Charts and Graphs, Excel Challenges

Here is a quick visualization challenge for you.

There are 25 KPIs and each has a base value, current month, corresponding month values. The KPIs also have targets.

The data looks like this:
kpi-contest-data

Download the data:

Click here to download the data for this contest.

Rules for this contest:

  • Visualize this data using one Excel chart (one chart per KPI is ok too).
  • You may omit any any data
  • You can use interactive charts.
  • Do not use add-ins or VBA to make your charts

Go ahead and make your chart(s).

Continue »

CP047: Best Excel tools for Entrepreneurs

CP047: Best Excel tools for Entrepreneurs

In the 47th session of Chandoo.org 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. 039 – Find the Cell Address for a value (2D & 3D Reverse Lookup)

Published on Oct 15, 2015 in Formula Forensics, Huis, Posts by Hui
Formula Forensics No. 039 – Find the Cell Address for a value (2D & 3D Reverse Lookup)

Find the Cell Address for a value from a 2D or 3D Range
(2D & 3D Reverse Lookup)

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 »

CP046: Gantt charts & project planning using Excel

Published on Oct 8, 2015 in Chandoo.org Podcast Sessions, Charts and Graphs
CP046: Gantt charts & project planning using Excel

In the 46th session of Chandoo.org podcast, let’s talk about gantt charts and project plans.

What is in this session?

In this podcast,

  • A brief intro to Excel 2016
  • What is a Gantt chart?
  • How Gantt charts can help us?
  • How to create Gantt charts in Excel
    • Using bar charts with invisible series
    • Using conditional formatting and formulas
    • Using ready-made templates
  • Resources on Gantt charts & project planning
  • Conclusions
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 »