All articles with 'Learn Excel' Tag

Recently, we discussed about the case of unwieldy data and how we lookup what we want using formulas like SUMIFS. Today, let us learn few more ways to solve the same problem.

Suitable structure spawns simple solutions

Poorly structured is the 2nd biggest problem of analysts. The first one is not enough coffee. That is why there is a dictum in the data analytics world.

Structure is everything

So, we can easily solve our lookup problem, if our data were to magically re-arranged in 2 column fashion – Data & Value.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 25th session of Chandoo.org podcast, let’s learn how to avoid SSUP syndrome.

What is in this session?

Most of us suffer from Sexy on the Spreadsheet, Ugly on Printout syndrome. I used to suffer from it too. This happens because we spend all our attention creating that perfect workbook, report or model. And then, we forget about making the proper print settings.

In this podcast, let’s understand how to create awesome workbooks that look great and print great.

In this podcast, you will learn,

• Frozen & Cars, where my free time goes
• Primer on print settings
  • Width & height of printouts
  • Page breaks
  • Row & column repetitions on every page
  • Size & orientation of paper
• Dealing with unprintables
• Proofing your print settings
• Printing whats not on screen
• Closing thoughts

Hello folks,

Its almost ready. Our ready to use Excel dashboard templates are coming to a spreadsheet near you on November 13th.

Here is a teaser preview of the dashboards. Our own official trailer, rated A for awesome.

Watch it below:

http://youtu.be/2-TvjpEKf-o

(see it on our YouTube Channel, alternative link)

Excel Dashboard Templates – What are they?

As the name suggests, these a set of workbooks where you can key in your data, set up how you want the looks & bam, a beautiful, insightful, fun, easy to use dashboard pops right out. All in few minutes.

Here is how these templates can help you:

• Design awesome dashboards in minutes, not months: Don’t waste hours trying to figure out formulas, chart settings and layouts. Instead focus on what your users want, how your business works and use those insights to tell the ‘Excel Dashboard Template’ what you want. Save time and look awesome.
• Accommodate change elegantly: People change. Businesses change. What your boss wanted last month is no longer what she wants today. So when someone asks you, “hmm, can you show me YTD data instead of last 13 months”, or “Can I see top 10 KPIs instead of all”, you just smile and tap a few clicks and bingo, the new dashboard will be ready. These templates are designed to handle change (data, requirements, design preferences and insights).
• Customize everything: Unlike a template set in stone, these are designed to let you customize almost every little aspect of the output, calculations and behavior.
• Beautiful & Informative: These templates are designed with a delicate balance of beauty & insight. Everything is seamlessly designed, perfectly aligned and precisely calculated so that you & your boss can focus on the insights & decisions. Time tested and well honored visualization principles & guidelines are observed thru-out the reports.
• Built for starters, built for pros: Whether you are just starting with Excel or a seasoned pro, you can find the templates right up your alley. For advanced users, we have many customizations to whet your appetite. Want to build a custom calculation or include another type of sorting, you can do it by tinkering with ‘calculations’ sheet.

Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.

Recently I found myself in such a situation and learned something new.

Introducing … data that won’t play nice

Drum roll please. Here is a data-set that I got from somewhere.

The problem – build a lookup formula

And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.

But how?

Its Halloween time. So humor me and imagine that Bill Gates invited you to his castle for an Excel themed party.

What costume would you wear?

Go ahead and tell me in the comments.

I will go first:

I would dress up as a spider (radar) chart. All I need is some cob webs on my regular dress.

Your turn…

This is a guest post by Sohail Anwar.

Let’s not bore you with an intro. You are about to learn a VLOOKUP trick that Lucifer himself would not want you to know. It’s so absurdly powerful that it was developed in a lab and had to be tested on Rocky’s arch nemesis Ivan Drago.

Presenting the Multiple criteria VLOOKUP!

…boring…pass, we’ve seen it.

Oh, have you? Not like this you haven’t. This will change the way you work with Excel.

Let me start with an easy example. Here’s some data and we would love to know what Bb and Dd is.

Hello awesome folks. It has been a while since I posted on Chandoo.org. And there is a reason for that. As you may know, recently (on October 12^th) a category 3 cyclone (hurricane) passed thru our city devastating trees, power lines, cellular towers, old houses & roads on its way. This means our family was left without power, water, telephone and internet for almost 10 days. Early last week we got power & water. Then slowly the internet started working too. (more on this here)

I am swimming thru heaps of email & backlog work. Thanks to everyone who emailed me with kind thoughts, prayers and love. I can’t tell you how thankful I am for having you in my life.

I am really glad to be back online, sharing my stories, knowledge & tips with you all.

As it has been a while, I want to share a few quick announcements first.

Here is a charming little problem to kick start your day.

Lets say you run a cute little bakery around the corner. Since you want your prices to look charming, you have a policy to round them down or up based on below rule.

If the price ends with 0, 1 or 2 cents, round it down to 9 cents.

If the price ends with 3, 4 or 5 cents, round it up to 5 cents.

If the price ends with 6, 7, 8 or 9 cents, round it up to 9 cents.

For example,

So how do you round to nearest charmed price? You could do it manually. But you would rather bake a few more of those Tiny Cup Cakes than waste time rounding the prices. So you want an automatic way to round prices. This is where Excel helps.

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 21st session of Chandoo.org podcast, lets compare lists. Quickly

What is in this session?

Comparing things is a favorite pastime for analysts all over the world. Sadly, it is also an area where we waste hours. So in this episode, I share my top secret comparison techniques to save you time.

Note: This is a short format podcast. That means you spend less time listening to it, while becoming more awesome.

In this podcast, you will learn,

• Why I sound like I am on a secret mission at a mafia hideout.
• 5 ways to compare 2 lists
  • Manual method
  • Conditional Formatting
  • Row Differences
  • LOOKUP formulas
  • COUNTIF formulas
• Bonus tip: Removing duplicates
• Conclusions

Hola folks…

My trip to Houston & Dallas was very successful, fun & awesome. I got back home on Friday and instantly I am in another fun, awesome & happy place with my kids, Jo (my wife), rest of the family & friends.

Today, I want to share a very simple yet super awesome trick with you. I learned this from Augie, one of the Houston Masterclass participants.

You can drag slicer items to multi-select them.

Selecting multiple items in a slicer quickly

We know that slicers are powerful, friendly and fun way to filter the pivot tables, pivot charts, power pivot tables and regular tables (only in 2013). They are visual filters that can be used to instantly filter the data (or report). But when it comes to selecting multiple items, slicers can be hard. We must hold CTRL key and tap multiple slicer items one at a time to select them. At least that is how I used to do it.

Do you know we can drag to multi-select?

See this demo:

Podcast: Play in new window | Download

Subscribe: Apple Podcasts | Spotify | RSS

In the 20th session of Chandoo.org podcast, lets save some time.

What is in this session?

We all want to save time and stay productive. The obvious answer seems like using keyboard shortcuts. But they can only get you so far. So what about the real productive strategies? That is what we address in this podcast.

In this podcast, you will learn,

• Announcements
• 5 key areas of business analyst work – tracking, analysis, reporting, data management & modeling
• Time saving strategies for tracking
• for analysis
• for reporting
• for data management
• for modeling
• Conclusions

My mom will be very unhappy with this post. She always told me to focus on one thing at a time. But in this post we are talking about 3 things, not one. Sorry mom.

1. Thank you

I want to thank you for visiting chandoo.org & supporting us.

As I am about to leave to USA for attending Excelapalooza conference, I couldn’t help but be amazed at how much you have given me & my family. Almost 4.5 years ago, when I left my plush corporate job to work full time on Chandoo.org, I had no clue how the future will unfold. Today my heart is full of happiness, my family is secure, my site has grown by heaps and our community (especially you) is awesome.

Without your enthusiasm to learn and keen desire to become awesome, I would not have a job (of running this website). You inspire me to learn new things everyday so that I can share them with you.

Thank you for all the visits, clicks, comments, emails, tweets, likes, signups, purchases & love.

Thank you.

Hi friends & readers of Chandoo.org,

I have an exciting and awesome new training program for you. 50 ways to analyze your data is aimed to give you critical data analysis & charting skills necessary for your success as an analyst (or manager). I want to make sure that this course offers you best content & value. I need your help in designing it. Please take a few minutes to read this short post & share your feedback.

Today is teachers’ day in India. I owe everything I learned to my teachers. Their powerful, insightful & inspirational lectures make me a better person.

Today I want to recruit more teachers. Because you never learn enough.

So why don’t you come on board and teach me (and rest of website visitors) something?

All you have to do is post a comment with an Excel tip, technique, idea or experience with us. It can be related to anything, from formulas to solver, charts to power pivot, VBA to keyboard shortcuts. Anything goes.

Lets go. I am all ears.

Here is a quick tip to start the week.

Often, we end up with a situation where a bunch of numbers are stored as text.

In such cases, Excel displays a warning indicator at the top-left corner of the cell. If you click on warning symbol next to the cell, Excel shows a menu offering choices to treat the error.