All articles with 'Learn Excel' Tag

CP035: on Solver, its story and future – Interview with Dan Fylstra

Published on May 21, 2015 in Chandoo.org Podcast Sessions
CP035: on Solver, its story and future – Interview with Dan Fylstra

In the 35th session of Chandoo.org podcast, Let’s hear from Dan Fylstra, the creator of Excel Solver. I had the fortune of meeting Dan when I was in Santa Clara last month. I immediately asked him to be part of Chandoo.org podcast and he was kind enough to agree. So today let’s take a trip down the memory line, hear him talk about some of the fascinating all the early development stories of Solver, VisiCalc & Excel.

What is in this session?

In this podcast,

  • Introduction
  • Early days of Solver
  • Working with VisiCalc, migration to Excel
  • What keeps Dan busy these days
  • Advice for anyone planning to learn Solver & business modelling
Continue »

Ensure cleaner input dates with conditional formatting [quick tip]

Published on May 12, 2015 in Excel Howtos
Ensure cleaner input dates with conditional formatting [quick tip]

Here is a familiar problem: You create a workbook to track some data. You ask your staff to fill up the data. Almost all the input data is fine, except the date column. Every one types dates in their own format. Here is a fun, simple & powerful way to warn your users when they […]

Continue »

How would you train your boss to use Excel better? [Weekend Survey]

Published on May 8, 2015 in Excel Challenges

I am in Sydney and yesterday we had a meetup of Sydney Excel users. Around 15 people turned up for the event and we talked about various Excel related topics over few drinks. One of the questions that came up was,

I learn and use Excel in better ways. But my boss doesn’t how to open the workbook and use simple stuff like filters, slicers, sorting and selecting. So I end up sending screenshots or PDFs instead of powerful Excel files. What to do?

Although we discussed various possible solutions to the problem, I thought it would be a good topic for us discuss here.

So how would you train your boss to use Excel?

Please share your thoughts, experiments, experiences and suggestions in the comments. Let’s make our bosses awesome.

Continue »

Use arrow keys to select small, unreachable chart series [quick tip]

Published on Apr 30, 2015 in Charts and Graphs
Use arrow keys to select small, unreachable chart series [quick tip]

Here is a fairly annoying problem.

Imagine a chart showing both sales & customer data. Sales numbers are large and customer numbers are small. So when you make a chart with both of these, selecting the smaller series (customers) becomes very difficult.

In such cases, you can use arrow keys – as shown above.

Continue »

How to insert a blank column in pivot table?

Published on Apr 16, 2015 in Excel Howtos, Learn Excel
How to insert a blank column in pivot table?

We all know pivot table functionality is a powerful & useful feature. But it comes with some quirks. For example, we cant insert a blank row or column inside pivot tables.

So today let me share a few ideas on how you can insert a blank column.

But first let’s try inserting a column

Imagine you are looking at a pivot table like above.

And you want to insert a column or row. Go ahead and try it.

Continue »

Use apply names to create readable formulas [quick tip]

Published on Apr 1, 2015 in Excel Howtos
Use apply names to create readable formulas [quick tip]

We all know that using named ranges is a good practice. So you went ahead and created names for every value in your complex workbook. But now, what about those formulas which still refer to cells by their addresses? Here is a quick tip to make your formulas readable by replacing cell addresses with the names in one go.

Use Apply Names feature.

Continue »

Formatting shortcuts for keyboard junkies

Published on Mar 17, 2015 in Charts and Graphs, Keyboard Shortcuts, Learn Excel
Formatting shortcuts for keyboard junkies

A lot of analysts swear strong allegiance to keyboard shortcuts. But when it comes to formatting a spreadsheet, these shortcuts go for a toss as formatting is a mouse-heavy activity.

But we can use a few simple & effective shortcuts to zip through various day to day formatting tasks. Let me share my favorite formatting shortcuts.

Continue »

Celebrate Holi with this colorful Excel file

Published on Mar 6, 2015 in Charts and Graphs, VBA Macros
Celebrate Holi with this colorful Excel file

Today is Holi, the festival of colors in India. It is a fun festival where people smear each other with colors, water balloons, tomatoes and sometimes rotten eggs. This year we wanted to play with only water guns, but kids vetoed that idea vehemently. So we ended up driving to my sister-in-law’s place to play with colors (there were no rotten eggs or tomatoes, thankfully).

Let me smear a few colors on you

I would love to splash a jug full of color water on you and say Happy Holi. But the internets have not advanced thus far. So I am going to give you the next best option.

An Excel workbook to play holi

Continue »

CP031: Invisibility Tricks – How to make things disappear in Excel?

Published on Mar 5, 2015 in Chandoo.org Podcast Sessions
CP031: Invisibility Tricks – How to make things disappear in Excel?

In the 31st session of Chandoo.org podcast, let’s disappear.

What is in this session?

Spreadsheets are complex things. They have outputs, calculation tabs, inputs, VBA code, from controls, charts, pivot tables and occasional picture of hello kitty. But when it comes to making a workbook production ready, you may want to hide away few things so it looks tidy.

That is our topic for this podcast session.

In this podcast, you will learn

  • Quick announcements first anniversary of our podcast etc.
  • Hiding cells, rows, columns & sheets
  • Hiding chart data points
  • On/off effect with form controls, conditional formatting
  • Making objects, charts, pictures disappear
  • Disabling grid-lines, formula bar & headings
  • Hiding things in print
Continue »

CP030: Detecting fraud in data using Excel – 5 techniques for you

Published on Feb 19, 2015 in Analytics, Chandoo.org Podcast Sessions

In the 30th session of Chandoo.org podcast, let’s learn how to uncover fraud in data.

How to detect fraud in data - 5 techniques for you - CP030 -  Chandoo.org podcast

What is in this session?

In the wake of hedge fund scams, accounting frauds and globalization, We, analysts are constantly second guessing every source of data. So how do you answer a simple question like, “am I being lied to?” while looking at a set of numbers your supplier has sent you.

That is our topic for this podcast session.

In this podcast, you will learn

  • Quick announcements about 50 ways & 200k BRM
  • Introduction to fraud detection
  • 5 techniques for detecting fraud
    • Benford’s law
    • Auto correlation
    • Discontinuity at zero
    • Analysis of distribution
    • Learning systems & decision trees
  • Implementing these techniques in Excel
  • A word of caution
Continue »

Who is the most consistent seller? [BYOD]

Published on Feb 18, 2015 in Analytics, Excel Howtos, Pivot Tables & Charts
Who is the most consistent seller? [BYOD]

Who is the most consistent of all?

Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.

Looking at this list, you have a few questions.

  1. Who is the best seller?
  2. Who is the most active seller?
  3. Who is the most consistent seller?
  4. Which seller has fewest invoices?

Let’s go ahead and answer these using Excel. Shall we?

Continue »

Revenue vs. Commission growth – Getting the message across [BYOD]

Published on Feb 17, 2015 in Analytics, Charts and Graphs

Situation: Our commissions are growing way faster than revenues
Let’s say you are looking revenues & sales commissions of your company for last few years. The data looks like this:

revenue-growth-vs-commission-growth-data

And you want to highlight the fact that commissions are growing faster than revenues.

So you plot YoY growth rates for revenues & commissions.

Problem: The chart of YoY growth rates is not convincing

Take a look at the chart. It doesn’t convey the message that we want. At best it says “revenue growth is less than commission growth”

revenue-growth-vs-commission-growth-problem

How to convey the message “Commission growth is a problem for us”?

Continue »

CP029: Impress your boss with Excel charts – 6 step road map for you

Published on Feb 5, 2015 in Chandoo.org Podcast Sessions, Charts and Graphs
CP029: Impress your boss with Excel charts – 6 step road map for you

In the 29th session of Chandoo.org podcast, let’s impress the boss with Excel charts.

What is in this session?

Many Excel charts live a short life. They spawn in an ambitious analyst’s spreadsheet. They go to boss with literally flying colors. The boss frowns, they disappear in to recycle bin.

Don’t curse your Excel charts with short life span.

Here is a 6 step road map to help you create awesome Excel charts, everytime.

That is our topic for this podcast session.

In this podcast, you will learn

  • Quick announcements about 50 ways & Einstein
  • 6 step road map for charting success
  • ONE: Dig your data
  • TWO: Validate insights
  • THREE: Pick charts that go well
  • FOUR: Add title & message
  • FIVE: Remove clutter
  • SIX: Prompt action
  • A real life example with road map in action
  • Resources for creating awesome charts
Continue »

2 Must watch Excel webinars for you

Published on Feb 2, 2015 in Learn Excel, Training Programs

PASS BA 2015 - Excel webinars from Chandoo

Just a quick announcement.

As you may know, I will be speaking at PASS Business Analytics conference in Santa Clara this April (between 20th & 22nd). As a run up to the event, the good folks at PASS are conducting free webinars on various topics of Excel.

Here are 2 topics on which I am doing webinars. You can watch them and pick up some really cool Excel skills. Just click on below links to enjoy them.

  1. 5 Dashboard mistakes to avoid (watch it any time)
  2. Excel charting – productivity tips (live on February 3rd)

That is all for now. See you in the webinar.

Continue »

What is the length of longest winning streak? [Excel homework]

Published on Jan 30, 2015 in Excel Challenges
What is the length of longest winning streak? [Excel homework]

Here is a fun problem to think about.

Let’s say you are looking at some data like above.

And you want to find out what is the longest streak of wins in the list.

How do you calculate it?

bonus question: What formula calculates when the longest streak began?

Continue »