All articles with 'Learn Excel' Tag

Fish Eye Effect for highlighting selection – Is it effective? [Advanced Charting]

Published on May 26, 2016 in Charts and Graphs
Fish Eye Effect for highlighting selection – Is it effective? [Advanced Charting]

A few days back, WSJ ran a visualization titled “What’s your pay gap?” It depicts median pay gap between female & male workers in 422 different professions in USA. The chart uses fish eye effect to highlight the selected profession. See below demo to understand the effect.

Continue »

How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Published on May 13, 2016 in Excel Howtos, Learn Excel
How many ‘Friday the 13th’s are in this year? [Formula fun + challenge]

Today is Friday the 13th. If you are a raging friggatriskaidekaphobiac, I suggest you to stop reading this post. For the rest of you, I have something fun.

Given a year in cell C3, let’s find out all the months with Friday the 13th. Something like above.

Continue »

Extract the 10 digit number [formula homework]

Published on May 6, 2016 in Excel Challenges

Okay, time for another challenge.

Imagine you have some data like this. Each cell contains 3 numbers separated by line break  – CHAR(10) and you need to extract the number that is 10 digits long.

extract-10-digits

Go ahead and solve this riddle.

Continue »

Earth Venus cosmic dance – Animated chart in Excel

Published on Apr 25, 2016 in Charts and Graphs, VBA Macros
Earth Venus cosmic dance – Animated chart in Excel

Recently I saw an interesting Earth Venus cosmic dance video on Facebook. See the original video below or here.

Although this is not entirely accurate from physics & astronomy perspectives, the dance is a stunning example of patterns that are generated by simple things.

I wanted to recreate this cosmic dance in Excel. How else am I to get my spreadsheet fix on a Saturday?

Here is a quick demo of the final outcome. Read on to learn more about the Earth Venus cosmic dance.

Continue »

Sumerian Voter Problem [IF formula homework]

Published on Apr 22, 2016 in Excel Challenges

Here is a simple IF formula challenge for you. Go ahead and post your answers in the comments section. Can this person vote in Sumeria? Imagine you are the chief election officer in the great country of Sumeria. You have introduced a new eligibility criteria for voters just before the grand presidential elections of 2016. In order […]

Continue »

Excel Tips, Tricks, Cheats & Hacks – Microsoft MVP Edition

Published on Apr 21, 2016 in Excel Howtos, hacks, Huis, ideas, Learn Excel, Posts by Hui, Quick Tip
Excel Tips, Tricks, Cheats & Hacks – Microsoft MVP Edition

Learn some of the Microsoft Excel MVP’s favorite Excel Tips, Tricks, Cheats & Hacks in this post
Excel Tips, Tricks, Cheats & Hacks – Microsoft MVP Edition

Continue »

Figure out slot from given time [quick tip]

Published on Apr 19, 2016 in Excel Howtos, Quick Tip
Figure out slot from given time [quick tip]

Here is an interesting scenario.

Let’s say you are looking at a time, like 9:42 AM and want to know which 15 minute slot it fits into. The answer is 9:30 – 9:45. But how would you get this answer thru Excel formulas?

Continue »

CP054: Top 10 Pivot Table Tricks for YOU

CP054: Top 10 Pivot Table Tricks for YOU

In the 54th session of Chandoo.org podcast, let’s make you awesome in Pivot Tables.

What is in this session?

In this podcast,

  • Quick updates
  • Top 10 pivot table tricks
    • Adding same value field twice
    • Tabular layouts
    • GETPIVOTDATA & 2 bonus tricks
    • Relationships & data model
    • One slicer to rule them all
    • Show only top x values
    • Relative performance
    • Show unique count
    • Spruce up with conditional formats
    • Not so ugly pivot charts
  • Resources & Show notes for you
Continue »

A slicer that doesn’t slice [Pivot Table Tricks]

Published on Mar 30, 2016 in Pivot Tables & Charts, Power Pivot
A slicer that doesn’t slice [Pivot Table Tricks]

Mary Ellen, one of our readers, has an interesting conundrum,

I have some data that goes to Pivot table then to pivot chart. There is a slicer to filter the data. But when I slice, my pivot chart gets messed up. How to have the slicer, but still see the insights in the chart?!?

See above demo to understand:

This is because when you slice by a school, the pivot table gets filtered and hence % row total for that school becomes 100% (as there are no other schools).

How to fix the problem? The easy answer is to remove the slicers. But we want to have our slicers and eat a slice of them too. So we crank up the Excel awesomeness valve and get to work. There are two ways to achieve what we want.

  • Old school method: Two pivot tables, some formulas & a line chart
  • New Excel method: Power Pivot and a line chart

Read the rest of this article to know more.

Continue »

These icons are so pretty, can I get them in green? [conditional formatting trick]

Published on Mar 22, 2016 in Excel Howtos, Learn Excel
These icons are so pretty, can I get them in green? [conditional formatting trick]

One of our readers emailed this question recently,

I like the conditional formatting icons. I am trying to present some business data where going down is good. How do I get a green colored down arrow icon?

Essentially, Ms. CanIGetItInGreen wants this:

Unfortunately, Excel’s conditional formatting icons are not customizable. So we can’t get the green down arrows without some sneak. And sneak we shall.

Continue »

Unpivot and then pivot for clarity (case study)

Published on Mar 8, 2016 in Pivot Tables & Charts, Power Query
Unpivot and then pivot for clarity (case study)

Or more appropriately titled, the one where Power Query solves the problem in less time than it takes you to say Get & Transform Data.

Recently, one of my students Mr. K, sent me a pivot table problem.

Today my boss asked me “how much we paid to staff since the inception of our business with their respective date of joining?” He wanted to know, level wise summary of the last 16 years (on Quarterly / Year wise basis).

The records appended from the database month wise. Have a look to the file and give your ideas.

Mr. K’s data looked like above.

Continue »

Autosum many ranges quickly with Multi-select & ALT= [quick tip]

Published on Feb 26, 2016 in Keyboard Shortcuts, Learn Excel
Autosum many ranges quickly with Multi-select & ALT= [quick tip]

Let’s say you have data in a worksheet in various ranges, and you want sum up each range at the bottom.

Something like this:

How to do all this one shot?

Simple. We use multi-select & ALT=

Continue »

Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]

Analyzing half a million complaints – Customer Satisfaction Scorecard [Part 3 of 3]

This is the final part of our series on how to analyze half a million customer complaints. Click below links to read part 1 & 2.

  1. Complaint reason analysis – Part 1
  2. Regional trends & analysis – Part 2

Customer satisfaction scorecard

In the previous parts of this case study, we understood what kind of complaints were made and where they came from (states). For the customer satisfaction scorecard, let’s focus on individual companies.

Continue »

Not so wild lookups [video]

Published on Feb 12, 2016 in Excel Howtos, Learn Excel
Not so wild lookups [video]

In case, this is the first time you are hearing about Excel formula wildcards, check out the Using wildcards in Excel VLOOKUP formula tutorial.

So you know about wild cards like * ?, now how would you tell VLOOKUP to ignore them?

Say, you are genuinely interested in looking the value “* Payroll” in a lookup table. What then?

This is exactly the problem faced by Peter in our forum post VLOOKUP and cells with “*” NOT to be interpreted as wildcard

Continue »

50 ways to analyze data course opens on 24th of February, 2016 (Wednesday)

Published on Feb 10, 2016 in Learn Excel, Training Programs
50 ways to analyze data course opens on 24th of February, 2016 (Wednesday)

Hi friends & readers of Chandoo.org

I am very happy to invite you to our newest online class, 50 ways to analyze your data. This program is aimed to make you an awesome analyst, training you on vital skills like data analysis, data science, visualization of outputs, modeling business problems and finding best solutions.

Please take a few minutes to read this. If you are interested in the program, sign up for the waiting list.

Continue »