All articles in 'Learn Excel' Category
ABC analysis is a popular technique to understand and categorize inventories. Imagine you are handling inventory at a plant that manufactures high-end super expensive cars. Each car requires several parts (4,693 to be exact) to assemble. Some of these parts are very costly (say few thousand dollars per part), while others are cheap (50 cents per part). So how do you make sure that your inventory tracking efforts are optimized so that you waste less time on 50 cent parts & spend more time on costly ones?
This is where ABC analysis helps.
We group the parts in to 3 classes.
- Class A: High cost items. Very tight control & tracking.
- Class B: Medium cost items. Tight control & moderate tracking.
- Class C: Low cost items. No or little control & tracking.
Given a list of items (part numbers, unit costs & number of units needed for assembly), how do we automatically figure which class each item belongs to?
And how do we generate above ABC analysis chart from it?Continue »
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,
- 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
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.Continue »
We all know about the MAX formula. But do you know about 3D Max?
Sounds intriguing? Read on.
Lets say you are the sales analyst at ACME Inc. Your job involves drinking copious amounts of coffee, creating awesome reports & helping ACME Inc. beat competition.
For one of the reports, you need to find out the maximum transactions by any customer across months.
But there is a twist in the story.
Your data is not in one sheet. It is in multiple sheets, one per month.Continue »
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.Continue »
Its homework time again. This time, lets tackle an interesting & everyday problem. Lets introduce our protagonist of the story – Jack. Jack likes long road trips, smell of freshly brewed Colombian coffee, clicky-clack sound of his computer keyboard. He hates toll plazas (they slow him down) & Potassium permanganate. And oh yes, Jack is […]Continue »
Time for another round of open-mic weekend.
As you know, Chandoo.org has been running a podcast show for last few months. We have completed 14 episodes as of today. For the 15th episode, lets have an “Ask Chandoo” as the theme.
What you need to do:
Simple. Head over to ask.chandoo.org and submit your questions. You can leave me a voice-mail or post your questions
What happens next?
I will listen (or read) your questions and choose a handful for the session 15 of our podcast.
What you get:
If your question is picked up for the podcast, you will receive the 25 Excel tips e-book.Continue »
Today lets learn about Excel CHOOSE() function.
CHOOSE eh? What does it do?
To understand CHOOSE() and appreciate its uses, lets invent an imaginary boos-subordinate pair.
Jasmine is the boss. She is, well, lets call her peculiar. She likes olives, Tuesdays & color Red. She hates potatoes.
Martin is the faithful butler of Jasmine. He is obedient, quirky and tall. He likes lotuses, Fridays & color blue. He hates potassium.
Enter Jasmine’s scarf problem:
Jasmine likes to wear a different colored scarf every weekday. She likes to wear Red colored scarf on Mondays & Tuesdays. She likes to put on the blue polka dot scarf on Wednesdays. On Thursdays, she wears her olive colored scarf. On Fridays & Saturdays, she prefers the lovely orange blue scarf. Sundays are no scarf days.
No wonder she is peculiar.Continue »
This is a guest post written by Joel Zaslofsky, author of Experience Curating.
How to Make “Put It in a Spreadsheet” Who You Are (and Not Just What You Do)
It seemed like a crazy question:
Could I use my passion for Excel as motivation to transform my leaky brain from a weakness into a strength?
Sure, I already had a fifteen year love affair going with Excel.
Every other email I sent to my co-workers had an attached Excel spreadsheet. And when I wasn’t using Excel for work purposes, I was using it for grocery list templates or budget tracking.
But I had to discover the answer to my crazy question.
So I told my wife Melinda in January 2012, “Honey, this is the year I put it in a spreadsheet!”Continue »
Recently we asked you, what do you struggle doing in Excel? 170 people responded to this survey and shared their struggles. In this post, lets examine the top 10 struggles according to you and awesome remedies for them.Continue »
Imagine you are the head of Accounts Receivable department at a large company. Drab, I know, But humor me and imagine.
Now, every month you get a transaction report.
And you want to know which numbers are matching up.
i.e, if your company gave Vendor-0002 $872.34 on 1st of April, 2014 and your received below payments from them subsequently,
- $427.77 on 1st April
- $152.88 on 2nd April
- $291.69 on 2nd April
Then you consider the account matched since the total received is same as total payable.(427.77 + 152.88 + 291.69 = 872.34).Continue »
This is a continuation of Session 9 – Averages are mean
In the earlier episode, we talked about AVERAGE and why it should be avoided. In this session, learn about 8 power analysis techniques that will lift your work above averages.
In this podcast, you will learn,
- Re-cap – Why avoid averages
- 8 Techniques for better analysis
- #1: Start with AVERAGE
- #2: Moving Averages
- #3: Weighted Averages
- #4: Visualize the data
As I am doing a road-trip across parts of southern India (we are in the beautiful temple town of Madurai as you read this post), here is something to keep you busy.
Fill in the blanks & post in comments.
I struggle doing _________________ with Excel.Continue »
First a quick update about state migration visualization contest.
We got more than 50 wonderful entries for this contest. I have compiled 50% of the entries. Due to personal reasons (its annual vacation time at our household), I had to delay the rest and resume it after first week of June. So the results will be announced in 2nd week of June. Thanks for understanding and your patience. Watch this space for mind-boggling dashboards in 3 more weeks.
Click “Read more” to get a sneak-peek at 3 random entries & 4 useful Excel links.Continue »
This weekend, lets get passionate
Few days ago, Cheryl, one of our forum members asked this question:
How do you know Excel is your passion? Or is it?
I am searching for my passion, you know that thing that makes my heart sing. I mean I am listening for the pitter-patter in my ticker. So how do I know if Excel is it? Or anything for that matter. I am looking for that thing that will make me turn my tv off. (TV is my crack, I am truly addicted). I thought it was database development and honestly I am not altogether sure that it isn’t. Excel may be a substitute. A more attainable passion.
Give me some insight peeps. Some thoughts, musings, ideas.
As usual, many of our forum members chipped in with words of wisdom. Hopefully Cheryl saw their replies, if she ever managed to turn off that tv.
That gave me an idea for this week’s poll.
What are you passionate about?Continue »