Chandoo.org - Learn Excel, Power BI, Charting, Dashboards and VBA online

Latest Tips & Tutorials

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.

Read This »

November 4, 2015

Can you extract numbers from text – homework

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.

Read This »

October 30, 2015

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.

Read This »

October 28, 2015

Contest: Visualize KPI data

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:

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).

Read This »

October 23, 2015

CP047: Best Excel tools for Entrepreneurs

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

Read This »

October 22, 2015

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)

Read This »

October 15, 2015

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

Read This »

October 14, 2015

CP046: Gantt charts & project planning using Excel

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

Read This »

October 8, 2015