All articles with 'Learn Excel' Tag

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.

Go ahead and solve this riddle.

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.

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 […]

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

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?

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

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

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.

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.

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.

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=

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.

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

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.

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.

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.