0000 Introduction

Advanced Excel Crash Course Section 1: Ninja Shortcuts
5 minutes
Share the link to this page
Copied
  Completed

Transcript

Greetings, everyone, I'm rishabh from Utah learning solutions and I proudly present before you our latest program that will make you an Excel ninja. And you know once you listen to this word Ninja, it comes to your mind that what exactly is ninja? Well, you'd have seen a lot of videos and content over the internet through different ebooks and articles learning about v lookup and statement. Well, those topics are certainly there in our program, but not only that, what makes us ninja is we take you beyond that and let me share some examples with you. One, if you happen to come across data with so many different errors are scattered across How do you do them at one go? Certainly not choosing one cell at a time and deleting them No, we show you a ninja level through which it can be deleted.

What in what five seconds? You see that? Next? If you are given a data with so many lines and intermittent blanks in between, how do you fill this blank by copy pasting Certainly not what if you are given data with 500 different names. Next, if you work with data, maybe sales, maybe purchase maybe closing stock or inventory, I would want to make reports multiple reports using different slices of criteria. For example, I may have country wise sales of different sports equipment over the last three years, let's say 2000 to 2003 and 2004.

Well, how do you make country by country different sheets of MS report, and each report tells you you're wise, month wise, what are the different units sold, and all that made in less than one minute without use of macros? So this was pivot table special trick, which we'll discuss in our program. Next, you will have learned about v lookup, maybe even gone beyond to two dimension lookup Well, we will also talk about three dimension lookup whereby if you are not only catering to one variable, second variable, we also show you that you can also cater to the third variable. So in this case, if you want to allot different performance incentive based on the rating of the employee and the division that he works with, and the region that he belongs to, you can do that. And trust me, even if the region changes that is there could be five more regions, Latin America, maybe Japan, the formula length will not change and you'll get your answer.

Next, through different aspects of conditional formatting will show you that in case the numbers change, it also changes the output. Now this is very useful for dashboards generation. So if I just change a few of the numbers, you notice, how would it look? Notice? Yeah, there you go. Another variation of conditional formatting would be if you choose the criteria, let's say HFD.

All the line items which have HFD mentioned there will be highlighted the entire row. Now this can only be done with formula based conditional formatting. And that is the ninja level we are talking about. Next. Now one aspect of dashboard where I want multi dimensional output through multi dimensional input. Now it sounds too heavy to listen, yes, but let me show you a live example.

If you have price and quantity, and that is leading to a certain revenue, well, of course, we'll be directing fixed and variable costs to find out how much profit Did you generate? Now, this is what everybody does. What we will be doing together is making a dashboard which will look like this. That is you can show what if the prices change and how would it impact the final profitability? What if the quantities change and how would it impact the profitability? Not only that, not only that, if you decide that I wish to have a different variable altogether, instead of PL I may want to have let's say, revenue.

Well, let me write revenue. And as soon as I do that, what I get is the impact on revenue. So that is multi dimensional input showing you impact on two output variables. Now that is the start of dashboard. Next. One of the fascinating aspects existing in Excel is three dimensional data consolidation.

Well, by default, it's two dimensional data consolidation. But with special tricks, we'll make a three dimensional one. In this case, I have people under me selling my company's product, and that the units are being shown in terms of months. So that's a basic model of water purifier. That's the reverse osmosis model of water purifier. Notice the names are in not in the same sequence as the last time.

And of course, there is one more sheet. Now all this has to be combined in one single solution such that each and every one of these numbers are linked to the original data. All this was done in what less than one minute. So things which you think can be done in hours will be done in less than one minute less than two minutes. And that is where I would want you to take through. In fact, all this knowledge you can use lies to become a trainer to become a consultant, and of course hero of your office.

So all this and more and I hope to see you in my program. This is Rochelle Pramila learning solutions signing off

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.