All articles with 'Learn Excel' Tag

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…

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 49th session of Chandoo.org podcast, let’s talk about data dumps!

What is in this session?

In this podcast,

• What is a data dump
• Examples of data dump
• Why we dump
• Ways to avoid data dumps
  • Go for information dumps
  • Sort the dump
  • Filter the dump
  • Give a table
• Resources for you

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…

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.

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.

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.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

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

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

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

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

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.

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.

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…

Let’s say you made a chart to show actual and forecast values. By default, both values look in same color. But we would like to separate forecast values by showing them in another color.

If you are a seasoned Excel user, you may be thinking, “Oh, that’s easy. I will just create 2 sets of data (one for actual and one for forecast), make a chart from them and apply separate colors.”

But here is a really simple way to get the same effect.

Use a semi-transparent box to mask the forecast values, as shown above. Read on to learn how to do this.

Quick update about Dashboards for Excel book:

As 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

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 44th session of Chandoo.org podcast, let’s talk about failures.

What is in this session?

In this podcast,

• Book announcement about Dashboards for Excel
• Story of my first ever dashboard
• Important lessons – Requirement Analysis for dashboards
• Resources for creating awesome dashboards
  • Podcasts
  • Books
  • Courses