All articles with 'Learn Excel' Tag

15 Quick & powerful ways to analyze business data

Published on Jul 1, 2015 in Analytics, Charts and Graphs, Learn Excel
15 Quick & powerful ways to analyze business data

Here is a situation all too familiar.

You are looking at a spreadsheet full of data. You need to analyze and tell a story about it. You have little time. You don’t know where to start.

Today let me share 15 quick, simple & very powerful ways to analyze business data. Ready? Let’s get started.

Continue »

Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers

Published on Jun 24, 2015 in Learn Excel, Pivot Tables & Charts
Introduction to Slicers – What are they, how to use them, tips, advanced techniques & interactive reports using Excel Slicers

Slicers are one of my favorite feature in Excel. And here is a quick demo to show why they are my favorite.

Slicers – what are they?

Slicers are visual filters. Using a slicer, you can filter your data (or pivot table, pivot chart) by clicking on the type of data you want.

For example, let’s say you are looking at sales by customer profession in a pivot report. And you want to see how the sales are for a particular region. There are 2 options for you do drill down to an individual region level.

  1. Add region as report filter and filter for the region you want.
  2. Add a slicer on region and click on the region you want.

With a report filter (or any other filter), you will have to click several times to pick one store. With slicers, it is a matter of simple click.

Read more to learn all about slicers

Continue »

Calculating Billy’s total working hours [solution & discussion]

Published on Jun 22, 2015 in Excel Challenges

Few days ago, I asked you “How many hours did Billy work?” There were more than 100 responses with lots of innovative solutions.

So today, let’s examine various ways to calculate total working hours given start & end times of tasks. Please watch below video.

Calculating Bill’s total working hours (video)

Continue »

CP036: How to do trend analysis using Excel?

Published on Jun 11, 2015 in Chandoo.org Podcast Sessions, Charts and Graphs
CP036: How to do trend analysis using Excel?

In the 36th session of Chandoo.org podcast, Let’s follow the trend.

What is in this session?

In this podcast,

  • A quick trip to down under
  • What is trend analysis
  • 4 types of common trends
    • linear
    • curve
    • cyclical
    • strange
  • Doing trend analysis in Excel – the process
  • How to use trend analysis results
  • Conclusions
Continue »

How many hours did Billy work? [Solve this]

Published on Jun 5, 2015 in Excel Challenges

Here is a simple but tricky problem. Imagine you are the HR manager of a teeny-tiny manufacturing company. As your company is small, you just have one employee in the shop floor. He is Mr. Billy. As this is a one person production facility, Billy has the flexibility to choose his working hours. At the […]

Continue »

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 »