All articles with 'Learn Excel' Tag

Use slicers to create a cool selection mechanism [quick tip]

Published on Feb 8, 2016 in Learn Excel, Pivot Tables & Charts

Most advanced Excel users know that slicers are cool. Today, let’s learn how to use slicers to create an awesome selection mechanism for your dashboards and forms.

First see a quick demo


Looks slick, eh? Read on.

Continue »

Make 1,200 dinosaurs in no time with Excel [formulas]

Published on Jan 29, 2016 in Learn Excel
Make 1,200 dinosaurs in no time with Excel [formulas]

It seems spreadsheets & dinosaurs on a collision course. How else can you explain Jon’s XKCD Velociraptor problem solved with Excel and now this. Debby, alert reader of our blog sent me this email yesterday.

I need an algebraic formula to solve this in Excel

I have 5 heads, 5 bodies, 4 arm sets, 4 leg sets and 3 tails. I need to see if I can create 1000 dinosaurs from these, and if that’s too many AND I need the 5 digit groupings to prove it and create them.
basically Xa*Xb*Xc*Xd*Xe=1000 – I’m not supposed to go over 1200. […] And then I want the 5 digit combinations if possible – right now they are trying to do the combinations by hand – would be awesome if we could do it in Excel.

Continue »

Format charts quickly with chart styles & color themes [quick tip]

Published on Jan 27, 2016 in Charts and Graphs
Format charts quickly with chart styles & color themes [quick tip]

Here is a quick tip to reduce the time you spend on chart formatting – use chart styles & color themes.

Excel offers various pre-defined color schemes and chart styles. Using them is very simple.

  1. Select your chart
  2. Go to Chart Design ribbon
  3. Click on the style or color scheme you want.
  4. Your chart changes instantly.
Continue »

How can I help you in 2016? [Survey]

Published on Jan 5, 2016 in Learn Excel
How can I help you in 2016? [Survey]

A very happy new year to you. May 2016 be your most awesome year yet.

This year too I plan to share tutorials, tips, podcasts & videos to make you awesome. I hope to focus on

  • Excel 2016 – exploring new features
  • Power BI – What is it, how does it make you awesome.
  • Write a sequel to The VLOOKUP Book.
  • Launch a new online course on Power BI & New Excel.
  • Run 2 more batches of 50 ways to analyze data program in Feb & July 2016.
  • Run another dashboard contest on
  • Write about awesome ways to work with data – formulas, charts, tables, pivots etc.
  • Talk about many advanced and work specific Excel scenarios in the podcast

But wait, what do you want to learn more…?

While these are my plans, I want to make sure helps you the best. So please take a minute and answer this one question survey.

Continue »

2016 Calendar, daily planner Excel templates [free downloads]

Published on Jan 4, 2016 in Learn Excel

Here is a New year gift to all our readers – free 2016 Excel Calendar & daily planner Template.


This calendar has,

  • One page full calendar with notes, in 4 different color schemes
  • Daily event planner & tracker
  • 1 Mini calendar
  • Monthly calendar (prints to 12 pages)
  • Works for any year, just change year in Full tab.
Continue »

Best of – 2015

Published on Dec 31, 2015 in blogging, Learn Excel
Best of – 2015

2015 has been the busiest year since starting

Wow, that is 12 years of breaking previous records. Thank you.

In 2015, we published 124 posts (down 3% YoY), received 6,300+ comments (up 5%). Our forum too had busy year with 1000s of new members and 5,000+ new threads. podcast continued to shine, we had 24 episodes this year and reached the 50 episode milestone. Our podcast episodes has been downloaded more than 900,000 so far since launch (in March 2014) with 600,000+ downloads this year alone!!!

Fun fact: People have spent 6.8 million minutes in 2015 listening to podcast. (assuming only 50% of downloads materialized to listens)

We have trained more than 1,800 people thru my online classes – Excel School, VBA Classes & 50 ways to analyze data program.

Continue »

People & websites that helped me in 2015 [thank you message]

Published on Dec 29, 2015 in personal

2015 has been the most awesome year since starting Thank you so much for making it happen. This year, more than 11 million people visited our site (up 8%), learned something and took first step to become awesome in their work. 2015 has been an amazing journey, thanks to all your visits, listens, purchases, […]

Continue »

How many Mondays between two dates? [homework]

Published on Dec 18, 2015 in Excel Challenges
How many Mondays between two dates? [homework]

Here is a quick but challenging homework problem for you.

Let’s say you have two dates – Start and End.

And you want to find out how many Mondays are there between those two dates (including the start & end dates).

What formula would give the answer?

Please post your formulas / VBA functions / DAX measures in the comments section.

Continue »

Color changing line chart [tutorial]

Published on Dec 15, 2015 in Charts and Graphs
Color changing line chart [tutorial]

Let’s learn how to create a color changing line chart using Excel. This is what we will create.

Looks interesting? Read on.

Continue »

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 »

CP049: Don’t do data dumps!!!

Published on Nov 19, 2015 in Podcast Sessions, Charts and Graphs
CP049: Don’t do data dumps!!!

In the 49th session of 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
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 »

Employee training tracker & calendar – tutorial & download

Published on Nov 4, 2015 in Charts and Graphs, Learn Excel
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.

Continue »

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 »

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 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 »