Month: August 2013

Pivot tables are the Excel gods’ greatest gifts. There is no better analysis tool within Excel.

As today’s example (download here), I’ve highlighted how you can analyze the amount of discounts and rebates that each customer receives within seconds. Insert pivot table, select the rows and columns that you want summarized and you are done! Just copy out the values for any analysis or chart. (We’ll be covering charting in a separate post)

3 things you must know about pivot tables –

1. The applications of pivots are huge. If you use Excel to track your accounts/expenses or your company’s expenses, you are just a step away from using pivots to view your data in many different ways.

2. The best way to become proficient in pivot tables is to do many of them. As you begin exploring them, you will come across many options to display your data differently to suit your needs. Just make sure you check out “Pivot Table Tools” and go through them.

3. If your job involves analyzing millions of rows or perhaps creating interesting pivot charts, you might want to consider a microsoft add in called “PowerPivot.” I haven’t used this myself but have heard good things.

Bottom line: If you’ve got a large set of data, don’t bother too with formulas. Just use pivot tables. They are very easy and you can get any kind of cut you want within seconds. You don’t have to be a superstar analyst to use them – they take 10 minutes to learn and will save you more time than you can imagine.

---

Series introduction, Part 1 (Set up), Part 2 (Shortcuts), Part 3(SUMIFS, COUNTIFS), Part 4 (INDEX+MATCH)

Decisions come in all sizes and we make a whole lot of them. For a long time, I’ve wondered how you make good decisions – well, more good decisions than bad ones. If decision making is a skill, there ought to be a framework and a process for getting better. Here are my steps.

First, define “good.” In my case, “good” is not looking back 6 months down the line and saying “Damn it. I knew better.”

Second, figure out a process before you arrive at a big decision. Mine involves 3 steps –

1. Do your research and form an opinion.

2. Seek counsel – align why’s and how’s. This means going to wiser friends and figuring out how they would approach a problem. The first step is figuring out who would be best placed to help – this is done by understanding their motives/”why” and “how” they would approach it. More often than not, folks you go to for counsel/advice are aligned on “why” because they get you and why you do things and would probably do things similarly. So, the next step is to test if their approach is something you would be comfortable with.

3. Understand the other end of the stick. This is something I don’t do nearly as well as I should. Once you know a path forward, try and sit beside a strong thinker and understand what the decision implies. Every decision closes some doors and it helps understanding what the consequences of your decision could be (financial, emotional, etc.)

Third, whatever your decision, just run it by yourself and pay attention to that feeling at the pit of your stomach. Sometimes your gut rejects the most logical of decisions and gives you an unpleasant feeling. My learning has been to listen to it.

And finally, treat your decision like Gandhi did – as an experiment. It might not work. And that’s okay. Track the results and use the feedback you receive from life to tailor your decision making next time around. Like most things, it’s an iterative process but it’s a process worth paying attention to because there are few skills as valuable as learning to make decisions you can live with.

Dan Ariely had a great tip when we asked him about productivity – “I basically try not to waste time. What I do is when I get tired of one task, I move to another one. I think often when people get tired of one thing, they think “let me go to Facebook, and I’ll get some energy and I’ll go back to this.” When I get tired of one thing I just switch to something else. I think the amount of hours I end up working is much higher because of that.”

I am one of those who checks my RSS feeds/email when I get tired of a task. My aim for the week is to implement Dan’s approach. I find that a pre-requisite for this approach is a well planned list that includes a slew of work and personal tasks. That way, you just need to toggle back to the list and pick up the next one when you are tired of the current one.

More to follow.

Success does beget success. When Malcolm Gladwell studied the development of IQ in kids in primary school, he noted that the differences between kids from affluent families and the rest became marked after a summer break. While most kids bummed around during their vacations, kids from affluent families were taught to spend time productively, learn new skills, and continue their education.

So, what do affluent families inadvertently teach kids? Willpower.

I believe affluent families do 2 things right –

1. They teach kids to learn the most important application of willpower – forming rock solid habits. They figure out basic routines like exercise, reading great books, etc., in a way that kids have this ingrained.

2. They have kids exercising their willpower muscle from an early age. When a man on the streets in India comes into some money, he immediately spends it on alcohol. If the same man learnt to delay gratification, he could build significant wealth over a lifetime. The concept that he’s missing is delayed gratification. That only comes with willpower muscle training. Kids in affluent families “get” this concept.

If we plan to be successful as people, parents, and educators, we need to understand that the most important concept we can teach the next generation is how to understand and flex the willpower muscle. Of course, to do that, we must understand it ourselves. There are 2 great willpower books out there – Willpower by Roy Baumeister and The Willpower Instinct by Kelly Mcgonigal. I’ve read and loved the former and have the latter on my reading queue. I hope you get to it. It might just be the most important thing you ever do..

This week’s book learning is from How Will You Measure Your Life? by Clayton Christensen.

A fast food joint asked its customers how it could make milkshake better so they could improve sales. With customer feedback, they made it sweeter and nuttier but it led to no change in sales.

Clay Christensen and team were then called to solve the problem. They approached the problem by asking the question – what job did the customers hire the milkshake to do?

Careful observation revealed there were 2 types of jobs –
– Morning customers hired it to make their long commutes interesting. A milkshake was as filling as a donut or bagel, less messy, and easier to hold in their cars
– Evening customers were typically fathers who saw this was an opportunity to finally say “Yes” to their kid’s request (after many “no you can’t do this/that”). But they observed that, thanks to the thin straws, kids took too long to finish their milkshakes resulting in irritated fathers.

So, they implemented 2 changes –
– Morning milkshakes were made more viscous to last longer and included small pieces of fruit to surprise customers
– Evening milkshakes were made thinner and were provided with thick straws so kids could drink it faster

The outlet exceeded their new sales targets!

Clayton Christensen’s book is packed with many great insights. Next week, we’ll look at how Ikea understands and implements the “what job am I hired to do?” principle.

When I started out with blogging, I used to re-blog a fair bit. It was the easy thing to do as thinking of a new idea/learning every day was not easy. These days, I have the opposite problems – too many ideas and too few days. But, every once a while, I come across a post that I feel deserves to be re-blogged and today was one of them.

The honor usually goes to Seth Godin but today, it’s a wonderful post from my friend Kevin Marshall (who built the Gawk.it search widget you see on the sidebar). Hope you enjoy his post as much as I did.

---

I often find myself thinking about my great grandfather (so much so that I used his first name as my oldest son‘s middle name as a small way to honor him and the following train of thought).

He passed away before I was born, so I never met him and I honestly don’t know very much about him. I don’t know what he did for a living, what his hobbies were, who his friends were, what his dreams and aspirations were…nothing.

Yet, I know that he existed and that I wouldn’t if he hadn’t. I know that he was a regular guy and so he most likely had good days, bad days, and more than anything average days.

And I imagine, or rather project, that his day-to-day stuff probably seemed really important and ‘required’ to him…because that is how most of us operate throughout our days. Yet I, just two generations removed within his very own family, appear to be mostly un-effected (or at least completely unaware) of any it.

This general thought has *really* bothered me over the years.

And it bothers me more when I extrapolate those thoughts out to the billions of other people who have lived through history…most completely forgotten (even by their own families).

I understand that only a few people from any given generation will be remembered by history…but given this, the fact that we all spend so much time, worry, and energy on what happens in our day-to-day seems kind of silly and somewhat useless to me.

And that is why, no matter what is going on in my own little corner of the world, I don’t let things get me too down or stressed out. I try to always keep my personal priorities and goals clear and in the front of my mind. Because in just a few short years, very little of any of it will be remembered or matter to the world at large.

This general flow of thought has long been one of the reasons I *rarely* have a bad day, and instead and am generally happy and in a good mood.

---

Wonderful thought for the weekend Kevin. Thank you.

Great!

If you aren’t coming face to face with your limits every once in a while, how would you be able to push them?

VLOOKUP is one of the most popular excel formulas that exists. Looking up and finding data is one of the most common uses of excel and VLOOKUP is many an excel user’s go-to formula. VLOOKUP has one very annoying limitation however; it can only search to the right of the table.

INDEX+MATCH solves this problem and also does a better job. On average, INDEX+MATCH has been found to be 5-10% faster than VLOOKUP and when you are working with huge sets of data, this stuff begins to make a difference.

Basic use

I would suggest you spend 5 minutes learning the syntax of INDEX+MATCH by heart as it will save you a ton of time.

Index(“column with data you want”, MATCH(“data you have”, “column which contains this data”,0))

Demonstration: I have demonstrated this in the INDEX+MATCH sheet in today’s spreadsheet. Please download it here or please feel free to send me an email with your dropbox ID and I’ll be happy to share the folder.

(Tip: When you open the sheet, press CTRL+~ to see al formulas in the sheet. Same combination to reverse the view)

Advanced use

INDEX+MATCH is a very powerful combination and has numerous advanced uses. I have illustrated one advanced use – you can use it to extract all unique values from a list.

Another useful use is to have embed an INDEX+MATCH within an INDEX+MATCH to search both horizontally and vertically. You’ll find these applications on google as and when you need it.

The point to remember here is that INDEX+MATCH is the way to go when building excel models. Learn how to use them and you’ll find multiple uses for this very powerful combination.

Look forward to your questions in the comments.

---

Series introduction, Part 1 (Set up), Part 2 (Shortcuts), Part 3(SUMIFS, COUNTIFS)

We haven’t figured out how to do online learning yet. A couple of friends and I are working on a project we call the “OpenLearn” project to improve how skill building is done online. 

We need your help with a 5 question survey to get your views about learning new skills and doing so online - it shouldn’t take more than 5 minutes and we would really appreciate if you took the time. 

Please click here or go to http://tinyurl.com/openlearnsurvey. Thank you for your help

Look back at your past few successes and failures and you will see that the margins between success and failure are often very fine. You’re often just a half-step away from being heralded a genius and most often, you need a bit of luck or inspiration to make it past that half step. If you don’t have that bit of luck on your side, you’re suddenly closer to a naive idiot than a genius.

Of course, you have to put in a ton of preparation to give yourself that shot, but once you get close, small external forces you have no control over can change the outcome. The good news is that in the long run, luck balances out. In short spurts however, a lack of luck at the final step can be very frustrating.

So, what can you do?

1. Acknowledge that you are the sailor in stormy seas. It helps knowing where you are going but a strong wind in either direction can make you miss the target despite your efforts.

2. Keep putting in the preparation required to give yourself a shot at succeeding. There may be fine margins in the final step when you are competing against 2 others to land the big client but there is a huge gap between the ones who made it to the final step and the ones who didn’t. Keep getting yourself in the mix.

3. Don’t account for luck in your plan. If you account for luck, call it a wish. It’s not a plan.