Ultimate Master Course for Excel Dashboard Analytics | Ultimate Masters Academy | Skillshare

Ultimate Master Course for Excel Dashboard Analytics

Ultimate Masters Academy, Maximise your learning.

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
41 Lessons (5h 27m)
    • 1. [Free Preview Video]

      7:00
    • 2. Beginner Series - Getting Familiar with Excel

      4:43
    • 3. Beginner Series - Understanding the Data

      11:15
    • 4. Beginner Series - Format your Worksheet

      5:52
    • 5. Beginner Series - Multiple Data Worksheets

      3:20
    • 6. Beginner Series - Selecting/ Copying/ Pasting

      5:03
    • 7. Beginner Series - Formatting Data as Tables

      3:41
    • 8. Beginner Series - Check for Data Duplicates

      3:30
    • 9. Beginner Series - Locating Blanks & Errors

      4:10
    • 10. Beginner Series - Saving & Printing

      5:02
    • 11. Beginner Series - Data Security & Protection

      4:10
    • 12. Intermediate Series - Introduction to Pivot Tables

      7:00
    • 13. Intermediate Series - Understanding Pivot Tables

      13:22
    • 14. Intermediate Series - Building a Pivot Table

      15:46
    • 15. Intermediate Series - Designing a Pivot Table

      9:25
    • 16. Intermediate Series - Refreshing your Pivot Data

      8:07
    • 17. Intermediate Series - Moving Pivot Tables

      4:19
    • 18. Intermediate Series - Sorting/ Filtering/ Arranging

      5:12
    • 19. Intermediate Series - Conditional Formatting I

      8:27
    • 20. Intermediate Series - Conditional Formatting II

      8:32
    • 21. Intermediate Series - Conditional Formatting III

      6:58
    • 22. Professional Series - Introducing Pivot Charts

      11:32
    • 23. Professional Series - Building a Pivot Chart

      6:51
    • 24. Professional Series - Formatting Pivot Charts

      8:05
    • 25. Professional Series - Moving Pivot Charts

      3:28
    • 26. Professional Series - Introducing how Slicers work

      7:17
    • 27. Professional Series - Interconnected Pivots

      4:14
    • 28. Professional Series - Professional Tables/Charts

      5:10
    • 29. Professional Series - Data Spark Lines

      6:10
    • 30. Professional Series - Effective Visual Analytics

      11:34
    • 31. Professional Series - Alignment & Arrangements

      9:39
    • 32. SUPER PRO Series - 500% Faster with Shortcuts

      18:42
    • 33. SUPER PRO Series - Formatting like an Expert

      7:41
    • 34. SUPER PRO Series - The 'Must-Know' Formulas

      28:07
    • 35. SUPER PRO Series - Dashboard Basics I

      8:58
    • 36. SUPER PRO Series - Dashboard Intermediate II

      6:30
    • 37. SUPER PRO Series - Dashboard Expert Level III

      7:28
    • 38. SUPER PRO Series - Modifying Macros / VBA

      7:31
    • 39. SUPER PRO Series - Automate your Report

      7:00
    • 40. SUPER PRO Series - Publish in PPT/PDF/Emails

      6:43
    • 41. SUPER PRO Series - My Super Pro Excel Tips

      9:21

About This Class

The Ultimate Master Course for Microsoft Excel:
Learn Data Analytics, build interactive Dashboards and Automate your data reports.
Go from an Entry Beginner to a Super Pro Expert in just 6 hours!

This Microsoft Excel course combines 4 different courses.

Beginner Series
Students will get familiarized with Excel's system interface and learn the important functions of Excel how to handle and format data across multiple data worksheets.

Intermediate Series
Students will learn to use Pivot Tables in their data analysis for summarization, sorting, filtering, reorganizing and cluster-grouping, as well as formatting techniques for enhanced data visualization.

Professional Series
Students will learn about Pivot Charts and advanced Data Charting Visualization techniques to create Professional charts for high-impact data analysis reports and presentations.

SUPER PRO Series
Students will learn my expert Excel Shortcut techniques to be 500% faster in handling large data sets and also learn how to create interactive Dashboards and use VBA Macro to automate the data reports.

(Video Lessons were recorded with Excel 2016 and 2019 but works in 2008, 2010, 2013 (Office 365)]
Concepts and Features are applicable across all versions of Excel on both Mac and Windows.

*****************************************************************************************************************

Recent Student Review:

"The Ultimate Master Excel Dashboard Analytics course was way too good for how much it was selling! Can't believe I learnt all those awesome Excel tricks in just 6 hours! Can't wait to show my friends and colleagues what I can do now!

DEFINITELY ONE OF THE BEST COURSES THAT I'VE EVER HAD IN MY LIFE." - Felicia

"This Instructor is definitely the best Excel Instructor I've seen so far! Super-skilled and knowledgeable in Excel and Dashboard data analytics. Really wished I took this course earlier before I joined the work force. Would have helped me so much in my professional career if I knew earlier how to build these impressive dashboards!" - Derrick Y.


*****************************************************************************************************************

Enroll now to go through a deep dive
of the most popular spreadsheet tool on the market, Microsoft Excel. As your instructor I will use my 15+ years of Excel training to guide you step-by-step through the beginner to advanced level and beyond.

As you participate in each of the 4 courses, you will master Data Spreadsheet Analysis, learn advanced Data Visualization techniques, and be able to to build high-impact Interactive Dashboards and automate your Data Analysis Reports.

You will start with the basics, building a solid foundation that will give you further knowledge as you progress into intermediate and more advanced level topics.

Upon course completion, you will come out with sheer confidence to handle any data analysis work with efficiency and grace.

*************************
Go from an Entry Beginner to a Super Pro Expert in just 6 hours!

49aa76a7

Transcripts

1. [Free Preview Video]: . 2. Beginner Series - Getting Familiar with Excel: Hi, everyone. Welcome to the ultimate master cause for Excel Dashboard Analytics, this is the first chapter under the beginning, Siri's and it's called Getting Familiar with Excel. All right, so this is the most basic module of this whole entire cost will be going to exactly what all the basic functions of excel, how to navigate around the tool and, most importantly, to familiarize yourself with using this application. Okay, So ultimately would be learning how to be building dashboards and how to use Data Analytics . But before we do so it's definitely very important to know the basics. Okay, But if you're already, you know, somewhat like an experience Excel user, then maybe, you know, you can just skip forward to the more intermediate professional modules, but a super Pro Series. But you know, if you need a recap on the basics or you just need to understand it a bit more on the basics of excel, then let's quickly, you know, open up your excel and let me give you a quick walk through, right? So, basically, if you open up, excel this area here below this entire area here is your work. Shit. All right. This is where you put in information into your car. Wash it. The area above here is a way. It has a lot of tools and a little functions on on how you can create your work. Shit is basically called the ribbon. So this entire area here, with all this little taps home insert picture layout formulas, data review view and develop. All of this is called the Ribbon. We've got, like, customizable quick tools over here to help make it more convenient for used to use the functions. But practically every single who and function can be sort of like explode in the central ribbon area broken down into these various sub categories. OK, you know, let's say if you click on any of these rectangles, basically this occult cells, If you click on any of the rectangles, let's say G six and notice that the name box over here is also showing as G six. Accordingly, let's try to create our work ship, for instance, for sales profits over a couple of different months. All right, so we call it months and they would put it, sells profits and then let's say January and then we want to just, you know, copy and drag it all the way to December and then profits. Maybe we start off the year with 20,000 and then from there we want to put a formula. It was, you know, 20,000 times 1.10 So, uh, every course every subsequent month you you have improved by 10%. But when this happens, you've created basically a very simple data set, but, you know, steals profits. Let's say you want to put it to show it more professionally in dollar signs under the the home tab. You can actually, you know, sort of like put it in dollar signs and then notice that, you know, it's a little bit too much for the cell. With what you can do, you can mount over toe the whiff of the column, and when you see the two arrows pointing on each site can just drag it out, can just drag it out. And then there you go. There. Now, all in dollar science And with all these decimal places, maybe what you can do is just, you know, ah, decrease the decimal places here, decrease it. And there you go. This is Ah, a little spreadsheet you have here. You ennoble them, you can actually just press be control. Be or you can just click on this button. Okay, This is a quick special you have. Create that on all your sales revenues, your sales profits for the entire year across the different months. And as your mom's your your your mom's over. The data said there was a quick analysis. When you click on it, it allows you to create charts, tables, etcetera. You can do it however you want. And then let's see if you create a table. You can also choose different kinds of table styles together with your your spreadsheet. Okay, so it's up to you. Let's say I go for the black one, and then I can also, you know, decide whether you invent it. Rose. Oh, you don't want bend it rose. You on First column, as I tried, sits, hold. You wanna do it? And that's even a total as well. There you go. I've got the total. Okay, so it's It's pretty intuitive what you can do with excel. So this is basically just one spread shit. If you want to go to multiple spreadsheets. You can click The different spreadsheets over here will be covering more on that later. And this Adam mean categorical functions off Excel, the ribbon spreadsheet, the formula bar and also different pages across. All right, so, you know, hopefully this is not too much to take in for the first module. I'll see you in the next one. Okay. See you. 3. Beginner Series - Understanding the Data: Hi, everyone. Welcome back to the ultimate master cause for Excel dashboard anesthetics. I hope everyone here is well and good. Before we start chapter two off the beginner, Siri's understanding the data. OK, so this is gonna be a very key, crucial chapter because this is the foundation, as you're working on with huge, huge, huge amount of their. Usually when people give you raw data, expect you very quickly get a sense of what this entire data set is about. So in order to very quickly understand the data, I'm gonna be teaching you some super, super, super effective techniques in this course. Okay, so the key topics will be focusing today. How very quickly Zoom in and out off the data set to get yourself into a comfortable view. Next, you're gonna use the freezing ping function so you don't lose sight of what are these columns about? So you want to keep and retain the column, haters, and then I'm gonna teach you how you can use the filtering function to get a good sense of what each column within the entire data set is talking about. And finally, ones all of that is compete. I'm also going to show you a quick preview how you can use pivot table analysis. Of course, I'm not gonna be going too much details into the pivot tables. We're gonna leave that in the intermediate, Siri's. But I'm gonna show you how people tables can a swell help you very quickly understand the data. All right, so, you know, with that, let's get your excel open, and that's dive right in. All right, so this is Excel open right now. You're looking at us. Raw spread. Shit. All right. So fussed ing what we want to do when we're looking at a roll on type spread. Shit is we want to get into a comfortable view. Okay, so that's that's when you need to zoom in to the spread shit, this couple of different ways, how you can do it. You can use you compress the control button on your keyboard, and then you can just zoom in by schooling in the US, using a school function into your mouth. Alternatively, you can also just, you know, look at the bottom right side off excel. There is. You know, this Plasse minus thing way allows you to zoom in and out. Or you can just and and click on the specific ah, magnification you want ism into Um so let's save on a zoom in 280. I can just zoom in 280 like that. Okay. So, mix, you know, as you scroll up and down the data said, you're gonna notice that you are losing sight off the top roll, which is actually telling you what is the data and each column about? You wanna be retaining that role as you're scrolling down up the data set. Now, what you do is you basically you go to view and in freeze panes and then freeze top row. You know, when your school down and up. No, it is that, you know, column What role? One is now fixed. So as you're looking through, the entire data said, you know that Hey, I'm still not losing sight off the hater. So this is a very good technique to keep in mind off. All right, so now, instead of using this girl function where you're sort of eye scrolling and then, you know it takes a long while to reach all the way to the bottom of the data set, a much faster method is to actually get used to using the control button on your keyboard. Now, the control button on your keyboard is actually really useful together with the arrow keys on a keyboard so far in the stands. Right now, you, I'm I'm a rule one rule one which talk, which is region and sell a one. Now I want to go on a quickly move. So the most bottom roll off the this entire spreadsheet. So what do I do? Basically, you can just press control your keyboard, and then you press the bottom arrow and then straight away. You are now in the last rule off the data set. Okay, so now you know that this entire data set has about 3265 rows, okay? And you want to move to the most right side of the there are set to get a sense of what? How many columns are there? So same thing you press control and right side right hand side. So basically what the control in the pero button does is that it allows you to move from one end off a cell within the entire data spreadsheet toe another. So, for instance, one move all the way to to sell a one. What I do is press control. Left control, right? I go to sell p one control left. I go to sell a one all the way to the bottom control down it go. Brings me all the way down to the last row. Same thing. Um, well, I move right up, okay? So basically, just play around with the control buttons as you're walking on. Ah, data set. And that very quickly would get used to using a contraband and moving around spreadsheets very quickly. Okay. Now, will you get a sense that you know the size of this stretches about 3205 rows? Now you want to understand within this data said, what are the fields within each column are all unique fills or the, you know, sort of like categories. So one quick method to do that is to use the filter function. Now, the filter function allows you to very quickly get a good sense off what is inside the columns. So, basically, how to go to filter under the ribbon, go to home filter and then click on this button filter. Okay, so when you do that, basically, when you click on the drop down this you can see that Hey, column A under region. Basically, we only have tree different regions in this whole data set. Asia, UK and United States next four countries. We know that there's a different couple different countries here for relationship manager. We've got about, you know, 10 to 15 different relationship managers, and it's broken down into the first name and last names. Okay. And then, you know, on the agenda is just male and female on the H this different ages on the rankings there are there five different rings and the rang czar basically associate upto a managing director products. Basically, this this company cells, accessories, laptops, smartphones and tablets. Seems to me like a electronics retailer of the year. They are looking at 2015 and 2016 and the month they are looking at a 12 months, 12 months wolf off their months, they have broken it down to in this different convention years, years, years, years. Why? Why, why And mm, four months, months, okay, and then sales volumes. This is the amount of cells that I guess they're working on, um for each particular month under each relationship manager on the specific kind of put up . It's in USD. And then, you know, there is also the revenues for this product. So okay, and let's just take a look at one off them. Let's see if we in all scroll down, move all the way to the last one. Ah, yes. OK, so we'll get that if the understandings correct, we have Andrew Thanh, relationship manager in Singapore on Region Asia. You know his age is 45 managing director. He sought smartphones in January 26th in total sales revenue. Waas about one point tree 1,000,000. I guess this is rounded up to one point tree tree and then the polar sales volumes he had was about 67.6 million. OK, so that's basically at home and you can use the filtering function toe break down your analysis off a data set now, not is what I do. Instead of using the mountain, you can actually just use your keyboard to very quickly move around the data set. All right, so press the alternate button and then just press. Don't on your keyboard, and then you can straight away You get a sense off what date I was like instead of using your mouth will kick on each drop down. You can also just use the alternate button and the and the arrow keys. Okay, so you know it zapped your preference. I prefer using the keyboard. I think it's much faster, much more effective. Um, okay, so basically a site from that Aside from using the filter function, another method is basically just the use the alternate function itself. Okay, so let's say I don't have fielder on, and I just want to get a sense very quickly. What region is about North is I just press alternate down on my keyboard, and I know that region has three different kinds off regions. Asia u K U s. A same thing for for products. You know, I just press alternate down often it down. I know that there's four different kinds of products that this for mis selling these companies is selling all right and minds. You know, we've got different breakdown months and let's say each, you know, sorry, gender, different kinds. So basically that's that's about that. How you can use that? The ultimate button on your keyboard to quickly analyze the column data. Now, what about people? People's okay, so knowledge is pretty interesting. I'm gonna show you. Ah, quick review how people tables can let you understand the data much better as well. So let's move on to the next hat. People, people analysis. So I'm gonna show you. Let's see out of this data once I've got my pivot table tools on, um, I want to get a sense off. That's a region. Okay, so I click on region here, bring it down to the rose, and I can see that Whoa, Asia in this company is is actually bringing in the most sales revenues getting about 62% asses compared to UK and USA 25 20%. And that's basically what you can do with people tables. I mean, you get a quick summary off, but your data is telling you, and let's save honestly the difference in products honesty, which is my most profitable Poor ox. Um, okay. So very quickly, I can see that won't My smartphones are actually bringing me 62% off my revenues. So it seems like the core business of this company is actually on smartphones because smartphones giving them so much of their revenues a supposed to two laptops. So maybe what they might want to do is that one of skill Down downsize their laptops business, since since profits are not as high, it's about it force as compared to laptops and tablets and laptops are sort of like around the same. We've got the company's revenues pretty much skewed to smartphones doing very, very well. So maybe them I want to focus more on smartphones in Asia because we know that Asia is actually bringing so so much of the sales revenues. All we might wanna just take some time to monitor the data again, get a sense of how revenues are moving puts what kind of products and what kindof regions. Okay, so with that, you know, that's end this chapter and move on to chop the tree. They don't. All right, so see you 4. Beginner Series - Format your Worksheet: Hello. Welcome back to the ultimate master. Costs for dashboard and ethics of Microsoft Excel for all of you are doing good. Now this is check the tree off the beginner series and today will be focusing on home to format your work ship. Okay, that's a couple of different stop will be talking about today from funds alignments using different colors and four incisors and also help to copy and paste formats across from once part of the spreadsheet to another and out Amalia changing the color off your work. Shit. Okay, so there's gonna be a lot to cover in this short chapter. I want all of you to pay attention as we dive right in. Okay, so let's get excel open and let's take a look. Okay, so right now what I'm looking at is a very raw and touch the data, especially being open right in front of me right now. Straight away, you can note, is that in some of the columns here, they're all you know, sort off like squeeze until you can see the full wordings off each cell, for instance. Over here, sell J four. It's actually laptops, but then I can only see as a P. T. O. P. So clearly the word is not showing properly. So straight away. When you see a spreadsheet like that, when the alignments and arrangements of the words are not properly shown, what you can do it straight. We select the food that I set press control and a when your keyboard and then go on the top ribbon here format and click Auto fit Role column with Okay, although fit, call them with OK, And if you've got an instance where by, you know, the the heights of the cells are sort of like on even you can also select everything and then format auto fit, row height and then that straightaway arranges everything properly. So now that's just do it again. OK, let's maybe zoom in tow 60. Zoom into 60 and you want to be able to format your worksheets. Actually, it looks very neat. Looks as neat as as what we're seeing here. All right, so this is sort of like and I'm just moving on to another worksheet where it's a ready for method. Well, let's see how we can. I learned to form a ah role work shit like that. Okay. So straight away, we want to be creating at some distinction in the column. Heda. So that's at in some color to the column hater. What we can do is, you know, we sort of, like, at a little bit off contrasts to the rest of the cells, maybe put a degree and then for the phone, we put it as white. So that makes it very similar to what we see here like that for the roles we don't want it to be, you know, showing it consistently across without any distinction. Maybe what we can do is we can bend arose at a tinge of gray and what we can do right now, we can copy and paste copy. What I just did was copy and paste the form it across the whole spreadsheet. Breast control shift down and right. So you have selected all of the remaining roles, which you want a copy and paste the format onto. And then you press control alternative e on your keyboard and impressed t for four minutes and in click OK, so, straight away you've added some form of contrasts between each rose. So now these are all bend. It rolls right now and it helps you to differentiate from one role to the other is here. Okay, so that's what you can do to add it some form of distinction so that you can form it spread ship to make it look a little bit clearer. Another thing is alignment. You can align all of your bills or to the left or the right old in the center. Okay. In this case, all of the cell with nicely feet, the height off the characters in sight. But if there is a different height for all of them, then maybe you can, you know, choose them to be in the middle, to be aligned all in the middle, like get especially for for this few rows with increased height. Or you can put it out to the top or bring it to the bottom. Okay, It's how you wanna play around mentally what? I wanna just sort of like stress. Is that how you want a format? Your spreadsheet? Basically, you can just play around with the tools within the alignment tap within the phone tap. You can also just change all of this right now. I'm under Calibri as my phone. I can change it to zero and all is now Ariel. Okay, so it's up to you. How you wanna play around with it? You can increase the fond. You can decrease the fund, can bowl everything, compress control. BIA, contribute a bullet you can put in italics. You can underline everything that's up to you. How you wanna play around and and make it. Nita, In this case, you know, I really like the idea of having the rules bend it so that you can differentiate from one row two in a row easier and the copy and peace for making across. Okay, so basically, that's in a nutshell. Hall. You can format your spreadsheet One last thing before we wrap up this chapter. And that is the color for the format that work. Shit. I have put it as yellow color. And then for the unfair matter What shit I put its black color. But now we know that we have just format that it nicely. Okay, So if we want to change color off the work, she itself I since I just created tree new worksheets, you can just right click color and then choose whatever color you want. Say, for instance, I want blue. And there you go. Do if I want. Great. There you go. Written by one green. There you go, Green. Okay, So that's also how you conform in your work. Shit. If you want to delete the what shit, You can just, you know, just click delete like that. Like that. Okay, That's about how you can format your work. Shit. This techniques keep in mind. Do not forget them. All right, that's it for this chapter. See you. 5. Beginner Series - Multiple Data Worksheets: Hi, everyone. Welcome back was still in the beginning. A series today will be touching on Chapter four. Multiple data work shifts. Okay, so when you're walking around with a huge amount of data, oftentimes you just might not have all the information you need in one single workbook. OK, so you might need to retrieve information from added data sources and try to pull it all together and consolidated into one single work ship. So when that happens, you know, this chapter is really right for you. All right, So let's get Excel started. Well, this is Ah, raw data spreadsheet on relationship managers. How much sales revenues there in two years? Okay, So before we get started, let's recap what we covered in chapter tree. How to create new walk shits. So you click on this plus sign jet about them to create new work ships, and if you wanna delete them, you click delete like that. All right, but I'm gonna teach you a quick and easy tear instead of using your mouth. You can actually just use your keyboard pressing shift F 11 and that allows you to create new warships right away. And if you want to delete them. Basically, instead of right clicking the tap and click delete, you can actually just, you know, use a keyboard shortcut. Alternate e l and what, she 38 disappear. Poof. Okay. Same thing for shit. 37 Alternate E l alternate E l alternate E l E l. Okay, so that's it. That's Ah, quick, quick and easy trick. How to in to create new work. Shit. And then delete them. All right, so it's for you to take note. Let's say right now this this data set is not complete. You wanna retrieve information from other work shifts? So say, for instance, you got other worksheets, and you want to pull out all this different worksheets and you want to bring it all together. So if you got the country's details of the relationship managers, what country are they from? You've got the region, the kills of this country manager, and you've got the gender details as well. All in different What books? Now you want to bring it all over here. So what you can do, basically, you can just, you know, take on both of these worksheets and then you move or copy If you want to create a copy, it retains it in this spread. Shit. Um, let's say gonna move it over here. There you go. See, you just moved countries and regions over into your summary master spread Shit where you've got the regions in countries here as well. If you wanna move agenda and each over without copying what you what you're doing, You're extinguishing this to spreadsheets in this workbook. So let's say if I do that I don't notice I'm not clicking. Create a copy. I'm effectively putting them over to the cell summary. What book? And when that happens, that what do disappears. And then now you have those two work shits over here in this cell summary tack. Okay, so now it basically what you have done. You've consolidate a couple of different worksheets over into this main master workbook, and that helps you in walking around with the data you need. Okay, so it's not too difficult. Just some quick point doesn't take note off when you're walking around different worksheets . Hope this lesson has been pretty useful. I'll see you in the next time 6. Beginner Series - Selecting/ Copying/ Pasting: Hi, everyone. Welcome back. The pace has been very manageable so far. We're not chapter five of the beginner series selecting, copying and pasting again. So there's gonna be a very important foundation skill unique, learned for struck Before you start working with huge data, eventually built data dashboards or do any form of Dayton antics. Often times when there is big, huge amount data involved, you might need to replicate data, and that will require you to select copy and paste data efficiently from one data source to another. So in this chapter, we're going to be covering some very crucial elements. So listen tight as we get started. All right? Okay. So this is where we last left off earlier in chapter four. Now, we've got all the relationship managers information in this workbook. The gender, H regions and countries off. All the relationship managers are in this separate worksheets, and we've got the sales revenue summary over here. So what we want to do is we want to include those information one copy and paste them from all this separate worksheets over to this sales revenue summary. All right, so first thing first, what do we do we want to create for new roles, OK? Because we want to insert them in. So you can either use your mouth to insert like that. Or you can use the keyboard shortcut, which is to press control space. And that selects the entire column. And then you want to insert. So what's the shortcut for? Insert your press control shift and the plus I in your keyboard. Okay, I'm gonna do it one more time. Okay? Hold on, Corn. Sure. Shift on your keyboard and impress Plus and press Plus again. All right, so basically what you just did we just create that four new new roles. OK, so now we want to write down what information we need, Right? Tool. Say, for instance, we want to put the first column as countries followed by regions and then by gender, and then my each. So you want to move overthrew the country's spreadsheet instead, off clicking the country spreadsheet Foster Matt. That's actually the user keyboard, which is the press control pitch up. Okay, when we press control pitch up, we're moving from one spreadsheet to another spreadsheet towards the left site and we want to move it towards the right side. You press control picture down, okay? And that moves to the right side. Okay, so I'm gonna move all the way to the left site, go to countries tap. And now we're in countries that you could either use your mouths drag and kaya column notices. So slow combat to do what you can do your keyboard. Okay, so I'm gonna show you how you can do it. Your keyboard. Remember what we learned Aaliyah In previous chapters, when pressed control and down, it brings you from one end of the data set all the way to Diatta. Okay, now what we want to do is to hold down shift. So the moment you hold down shift, you are able to select all the data in that particular column. Notice that we're half really selected entire column D Now on a copy it press control, See? And then I moved Pitch down control pitch down back to this column, and then I just press control V. That's pretty simple, right? Same thing for region. I'm gonna show something different again now and stayed off copying. We want to cut. So say for instance, I want to cut off this entire column data. So I'm gonna hold on control X now. Okay, Control X. And then I go to sell some, and I paste it. So what happens is that this anti data is now missing from regions. So I just did a cut and paste. Okay, this functions are all available in this clipboard here. I'm gonna show you the gender one. Let's say I move all the way down. I use cut. Okay, You can click cut here, and then, you know, you can go here and then you click Peace. And there you go to genders there. If I want a copy, I goto h I control shift down. I click copy. And then I moved to my cell summary page, and then I pace and okay. Sorry. There is this pay special function, which I want to teach us. Well, you can I paste it down, or you can use the pay special function. So let's it now we've got this four columns in the summary. I want to be pasting the format off the other columns so that he looks consistent across. We don't have suddenly this yellow tape. So what? We can do we can just copy the entire column, move over to the other four columns, and then we do a piece special, which is pressing Quantrill alternate V on your keyboard. And then this pay special window appears on just how just click form. It's OK, so that's it. Basically, with edit those data of the countries, regions, gender and age into the muscle sweat shit. And now this special is consolidated with all those information. All right, so it's pretty intuitive. There's a lot of things to keep up in this chapter. Just keep in my off this concepts. I'll see you in the next chapter. 7. Beginner Series - Formatting Data as Tables: Hello, everyone. Welcome back to Excel Dashboard. An ethics was still focusing on the basics, trying to sharpen our skills before we start building dash bots today would be covering chapter six off the beginner series formatting data into tables. Okay, so this chapter is gonna teach you some basic yet impressive skills with data tables. So let's get started. Right now. What you're looking at is the spreadsheet we've been working on Aaliyah Basically auto sales revenues and sales volumes off your relationship managers across different regions. And it's a spend off two years worth off revenue data. OK, now very quickly. Let's say if you want to format it into a nice professional looking data table, what you need to do instead of, you know, manually changing the phone and bolding it, changing it from different colors. What you can do is fermented as table. So under the stalls, you can click on this button here on the top ribbon format as table, and that allows you to select many different kinds of tables from light team, medium demon Doctor. Okay, so let's say I just want to go on. We won ah! Click on this and then straight away. Everything changes into this stall that you, if possible. Okay. If you want to select different tables, it's up to you how you want to change to your own personal liking. Let's say if you want to go for DACA team, you can go for the doctor like that Well, or you want to go or something will be brighter. It's up to you how you wanna play around the moment you change your data set into a table. The filter button straight away comes on so that you can do any form of analysis Right away you can see different regions, etcetera. It's up to you and notice that the bend it rolls are all by default showing. So let me show you an example. Let's say you are looking at this first half off the year breakdown off sales revenues, by products and by regions using a table creates Ah, very professional looking for men for your spreadsheet. And the filters are on depending on how you would wanna look into your data. Gonna do any form of sorting smallest lodgers, formatting by color, etcetera. It's all up to you. Actually, one thing very useful about it as well is this drop down function? There is this drop down quick analysis function that Excel provides what you can do. You click on it. It shows you a couple of different range off functions you can use. You can use data bus to see the breakdown. Distribution between the revenues and use color can use iconic sets to sort of. I do some form of distinction. You can also add in charts right away, and it shows you the different kinds of charts you can walk around. If the data even for totals, you can add in some totals, you can see what is total across the different months, every count, percentage total. It's up to you. It's quite a couple of different stuff to play around tables as well. Spark lying. You can see a quick analysis on a more granular basis. Okay, so basically, that's what you can do with data tables for your keyboard. Instead of clicking the button here, you can just press control on your keyboard and the TEP function, the sort of like skill around the different functions and under the quick analysis test. Okay, so either you can use the keyboard. Or you can use the mounts. Both Selves purpose off using this function under the data tables. Okay, Hopefully, this chapter has been very helpful and very useful. I'll see you in the next course. 8. Beginner Series - Check for Data Duplicates: Hey, welcome back. This is chapter seven of the beginner. Siri's today. We'll be touching on a very crucial step when you're dealing with data and that is to check for data duplicates. Okay, so now when you're working on your data set and you have a hunch that something somewhere within the scratches not quite right the numbers are not adding up, then this is a very, very useless there and within the whole troubleshooting process to find out what exactly is wrong with the spread ship. And usually the case is because there are just some data on the pickets in the whole Stretch it. Okay, so for instance, right now, what you're looking at in Excel is the same structure that we've been working and we've got the breakdown off all the details over here in this stretch it now the sales volumes, you know, right at the bottom here is showing about 800 million. OK, now let's say we've got some duplicated entries. For instance, let's say n drew hands values were sort of like end twice across all of them. So let me just quickly create a new threat shit and paste it in Ah, let me just sort of, like, create his, uh, Hold on. Okay, let's say I will to copy and pace Andrews data over here and added right back into discretion. So now there is duplicated data, and now the sales volumes you see at the bottom it's showing Toby 932 million instead. That is clearly not within your expect that range off their sales volumes. You're looking at four for the whole period. You're expecting it to be on lee. 800 million. So at first instance, what you should do is to remove data duplicates. So assuming you're not sure exactly what is wrong with the data, my best advice is to firstly create a duplicate copy off this spread shit before you try to remove the big it's so that's always a good practice. Habits straight away at the bottom. A copy. Just create a copy, and then, you know, you just want to call it. Ah, tests. Okay. Just call it tests, and then you select all the data within this spread. Shit. Click on the data tap on and remove duplicates, and that will help you identify any duplicates. And there you go. You know that Now Excel has helped you identify that There's 102 duplicate values within this whole spread. Shit. Um, And if you click, OK, go all the way back up again and do a quick sanity check on the sales volumes. There you go. It's showing appropriately, 800 million. So now you're sure and certain that this spreadsheet is clean. Okay, so basically, that's a very useful step. Whenever you're working with huge data sets, you just want to get a sense that the data that you're working on its clean before you start building any dashboards, any data analytics to you want to make sure that the guys keen in order to make sure that the data is clean just do a quick sanity Check that date as clean by removing data. The bigots. Okay, so that's pretty much about it for this chapter. Hopefully, it hasn't been too complicated. I'll see you in the next chapter 9. Beginner Series - Locating Blanks & Errors: Hello, everybody good to be back. We're now at Chapter eight off the Siri's. Today we'll be touching on a very important topic as well, Similar to what we've covered in the last chapter removing bait out of the gates. Today we'll be touching on how to locate blanks and locate heiress and as well fix them. All right. This is a very important element as well within the whole trouble shooting process when you're working on a data spreadsheet. So before you you prepare any kind of dashboards, any reports or anesthetics, you want to make sure that the data you're looking at is clean. There is no duplicates. That's what we cover in the previous chapter. Today, we make sure that we were not able to identify blanks within the spreadsheet and learn how to fix them. So basically, that's about that. Let's get started. Let me just open up my excel and we're looking at the same spreadsheet we were working on hold as well. So let's say this special is clean, Okay, but how do I know that this spreadsheet is clean? First you got to do a check and see if there is any bangs or if there any errors, what were you want to do is firstly select everything on a spreadsheet, go finance alike. And then, um, go to and then special and then blanks. Okay. No blank cells will phone. Let's see There any errors as well? Let's go to go to again special. And then on the formula. You want to see there's any errors here. Okay, so there are no heiress. All right, But what if there are errors? All there are blanks. Okay, so let me just try toe manually, create some blanks. Let's I just delete some stuff. You know, they let some stuff around and we know that they're all this plots within the spreadsheet, all blanks. Okay, so I want to be fixing all of them up. I'm just gonna select all the data again, control a on your keyboard, and then fine, go to special blanks and click. Ok, there you go. So, basically, Excel has identified for you all the blanks within your spreadsheet, and this can help you to sort of, like, fix it up if there any issues with your regret. Shit. Okay. So we don't want to be working on a data set that has any banks. So first thing, first check there. Any duplicates? You know, check there any bangs and next thing you know, check if there any heiress. Okay, so let me just undo all of them. Control Z for undue in his case, inspection we're working on is keen, but let's say there are some form of errors. All right, so let me just walk on ah formula era to help simulate the environment. So we have got sales volumes here, and we've got revenues here, assuming and imagine for revenue is 15% off sales volumes, and then we just bring it all down. We copy and paste that unanimously across, so that gives us a correct formula. But what if the formula has some kind of error? For instance, you multiply with Ah, what right? We don't. We obviously don't multiply for what? So the moment you do that there's gonna be an error there where you see, there's this hashtag sign with value and exclamation mark. So for for sure, you know that that's an error. So what if we copy and paste it across some other cells that we know that Hey, this spreadsheet isn't very clean anymore, right there. Some errors. Yeah, So assuming you don't know about that and you're just glancing the spreadsheet on this view , it seems like, you know, it seems all good. Ah, sanity check will be the control. And so, like everything Fine, go to special. And then formulas click. Heiress. There you go. So, basically, Excel will help you identify all these errors, and then you can troubleshoot and fix them up. OK, so that's basically what you can do to make sure your data said it's clear. All right. Okay. So keep in mind this few steps and I'll see you in the next chapter. 10. Beginner Series - Saving & Printing: Okay. Welcome back. We are almost at the end of the series. We're now chapter nine, and that is learning how to safe and print your documents. Okay? It's gonna be a short and sweet chapter. Of course, there are different variations. Hold cream and save your fouls. I would be showing that all to you in this chapter. And let's not waste time and get stop it. Okay, so we've been we've been working on this spreadsheet for quite some time now. You want to be saving this document? So how do we save the document? Basically, just click on foul Kekana foul tab at the top, left corner of it off the spreadsheet, click foul and then click save s, and then click, browse, and then this window panel will open, and then this will allow you to select the specific directory in which you would want to save the document in you can, obviously selected as an excel workbook, which is the default foul type. Alternatively, you can also selected as a PdF foul or other. You know, as an excel template in our s a CS V followed. It's It's up to you. Hold. You wanna save. It s, I guess more commonly will be on the Excel workbook and PdF versions or XML versions that you've been working on closely. Okay, so basically, this shows you the different available formats in which you can save your foul in. And then once you're done, you can just click on the save button over here. So I click safe and that, you know, it's sort of like overriding my existing foul, obviously, because I have really created it. Ah, I just click on yes, do to save it. Okay. Ah, in thumbs off. Ah, saving notice that there is a safe s function and there is also a safe function. The difference, basically is that safe function is you use it on an existing foul that you have already saved before. So in this case, you know that we've just saved the fall as a document saving and printing. If we click on safe and basically just overwrite the existing foul that we have saved And that, you know, is you've been working on existing spreadsheet for some time. You really saved it before. Just click on the save button. It allows you to save it more efficiently rather than saving it as a new spread ship. So that's about it for saving. In terms of printing, it's not too difficult as well. Let's say you just want to print this document if you click on foul and then click on print , that allows you to be printing this entire document. But obviously, you know there's 30 tree rolls over here way. We know that this spreadsheet has about 3000 overrules. It's gonna be massive if you want. Want to be printing this document. So let's say you would want to just print a select that area of it off this whole spread ship, for instance, that same going to focus on Andrew. Obviously, we know that Andrew is one of the best sales performers within this this two years 2015 and 2016. Let's say we want to be printing his sales volumes and sales revenues across these two years. Okay, so we want to be, firstly extracting out his revenue. So what we're gonna do, let's do a filter on the spreadsheet and then we such for Andrew. Okay, and let's say we just want to focus on the smartphones, grow it down to just in smartphones. Cross just 2015. Okay, just 2015. And we want to copy out all of this data, and we want to create a new spreadsheet. I just press shift F 11 and then I just paste it down, and then on a copy of the whiff of it pay special. And there you go. So I've got all of Andrew's revenues over here for smartphones, and I want to be printing this document. Okay, So in order to print this document, and I just click ah, foul and in Iraq just click print. Basically, it would appear like that. Obviously, I want to fit it all in one page so that it's easier to see, so I just select fit shit in one page and then, ah, it doesn't look so good in a portrait form. I'm going to just do it in a landscape form. And there we go. All right, now, we've got all of NGOs, revenues for smartphones. He's obviously a managing director, one of the top high flyers in this firm, and his sales volumes all captured over here. So that's how you can, you know, print a specific portion out off. What she? I hope this session has been useful. Once you're done, you can just take on the print button over here. Key points? I think not. You know, you're working on a big spread shit in order to print a specific portion of the first to a filter and then select out the specific portion you want a printed in copy and paste it into a new spread. Shit. And then just print it from there. It's much easier that way. Okay, Hopefully, this chapter has been useful, and it's been very helpful for you. I'll see you in the next chapter. Okay, CIA. 11. Beginner Series - Data Security & Protection: Hi, everyone. Welcome back. Well, now it, Chapter 10 the last chapter off the Siri's. And today I'll be covering a very important topic on data security and protection. Now, we've been working day and night on our data spreadsheets. And if just one fine day, your leader, what she it gets, lend it in the hands of a wrong user. Then I believe the consequences would be crazy and would be disastrous. Okay, So in order to make sure that you protect your data properly and exercise that necessary that our security measures I'll be showing you in this chapter how you can protect your data. So let's not waste time and get started. All right? So right now you're looking at especially we've been looking at for quite some time. Let's say you want to protect your workbook, OK? In order to protect your workbook, what you need to do is kick on the top left corner, can foul and then taken protected workbook and then encrypt with possible it. Okay, so let's say you want a key in the password. Let's we call it password. 12 tree. Ok, P a s s w O r d 12 tree, OK? And you gotta repeat it again. P a s s W O R d 12 tree. Okay, so basically, that's it. The workbook is now protected. If I close it and I just click, save changes. And if I open it again, it's gonna ask me to key in the password. So if this work shit gets lender in any hands of any wrong users, you can just use this method for techno. What book? And it's gonna be very difficult for the person to guess your password. And as a piece of advice as well. Make sure your password is not something that's very easily figure out, but you something a little bit more complicated. So that is going to be much more difficult to break the password barrier. Okay, so that's one way to protect your workbook. Another way is to limit the functionalities off the workbook to use us. Okay, How you gonna do that is basically the same thing. Taken foul, click on protect what book. But then instead, off encrypting the entire world book, you want to just limit the functionalities off the friendship, so it's actually protect current. She and you know you can actually get put in a password again. We call it, maybe just simply pause. What? And then you choose what functionalities would you want to allow users to do? And it's Oliver, like this whole stretch of function. And it is in this lease over here, maybe just allowing him to delete columns, etcetera, like anything objects. And then you just select a fixed amount off perimeters that you can allow users to use. So once that's done, just click, OK? And then you re enter the password. That's it. You deem it that the functionalities off the worship. And if you wanna this workbook its final What you can also do is click on foul to come protect what book and then click on Marcus Final. So the moment you market as final this hop up message, you appear and will tell you that this document is already know mark this final and indicating it that anything is complete. And you know, this is the final version. If people were to edit it, there would be a problem top reminder that stops them from doing so. Okay, so when you click on it, it's gonna show the document is marked as final. So the author has marking this workbook as final and is going to discourage you from making any editing or any changes the document. Okay, so you protect your workbook. These are the measures you can take to make sure that you have headed in the right amount security measures to prevent any form off leakage off confidential data. All right, so that's pretty much about it for this chapter in this whole series. We touched on quite a couple of topics within this past 10 chapters, letting the basic foundations off excel, getting comfortable working with data. But most importantly, this cup of topics will build you a very, very, very strong preliminary foundation working with huge data sets. Okay, So stick around and join me in the next intermediate Siri's where I'll be covering more on data people's people table analysis and get your hands dirty on expectation analyses. I was See you in the next chapter 12. Intermediate Series - Introduction to Pivot Tables: Hello, everyone. Welcome to the first chapter off the Intermediate Series. If you recall what recovered in the beginning, Siris was mainly trying to firm up your basics and foundation in excel. Now, as we move over to the intermediate series will be taking a step much closer to the world off in Directive and dynamic Dash Watts, where this series will be gem Peck with much more exciting content. Okay, two kicks that this whole Siri's I'll be focusing chapter one. Crucially, on an introduction to pivot tables, you're gonna understand why pivot tables are fundamentally them core ingredient to all these charts you're looking at in the dashboards. All right, it starts with people tables. Okay, I'm also gonna show you a quick preview what people tables can do, as well as a live demonstration off building pivot charts in all these dash watts. Okay, so let's get excel, Statler, and I'm gonna show you how the magic happens. Okay, so right now you're looking at the dash about where I was showing in my introductory videos . I mean, if you click on any of this, um, selection criteria is a very interactive interface. With this buttons, you click here. The dashboard changes instantaneously. For instance, if you want Asia besides the U. S. A. You got to see the breakdown off Asia's revenues in terms of sales versus the U. S. A. And you can see the breakdown off all the sales relationship managers across the region, and you can see how that performing off course, we can see that smartphones are the most sought item here. It's contributing the most of the revenues. If we just zoom in on smartphones, we can see that 2016 was a much better year for smartphones in terms of the revenue trend started off the year, very strong. Took a dip midway through the year, and then it started picking back up. If you want to refresh the dashboard, just click. Reset it and then it goes back to normal. So it's a very intuitive, smart dash button off course. We want to build something like that. So drop this cause are being focusing All this little steps help built. Ah, that's what like that. But in this intermediate Siri's, we focus on the most important ingredient, and that is to get used to pivot tables. I'm just going to show you a quick demonstration right now How it all happens. I recall we were working on this spreadsheet in the beginner series and really comfortable working with huge amount of data. Let me quickly just show you a quick demonstration on how you can do a pivot table and do an analysis here. Okay, So for this pivot table, let's say we want to look at the sales volumes in the respective regions how they are faring across both the US So we want to see the difference from 26 then versus 25th in. Okay, So let me just quickly at a value setting and then put difference and your 25th in. Okay, and I'll just change this accordingly, Okay? I'm used to all these shortcut is Do not mind me. I'm just going to show you as a demonstration. You know, I'll be going down in all the steps a little bit better, a little bit more easier to understand later on. So for now, you know, just watch and see how it happens. Of course. Also be showing you in a Super Pro Series how you can get used to all this short cut so that you can be very, very, very fuss on the keyboard and be very ah, queen that with excel. All right, so, you know, basically, we want to build a dashboard similar to what we saw earlier. Okay, that's put legends on the top. Change this to millions millions, and let's put this over. Okay, um, we want to build a chart and fit it into the dashboard. So let me just quickly built something like that. Let's say we want to have this strong. It's a line in state in terms of the year on your performance as the secondary axes. And there we go. OK, so this is so like the rough idea we wanna have. Let's put it over. I'm just gonna copy and paste it. Put it over to our sexual dashboard. Okay, So if I paste it, you're something like that, and then I bring it your you know, it's gonna align it, bring it back down like debt, and then I want to change it to the same black in the face. Of course, because that's the theme that we're looking at. And then I want to change the fond to what the other charts are like, It's well, let me just put it like that. Okay. So, um, that means just do a little bit off massaging to make sure it looks pretty OK. Um Yep. All right. So I'm just gonna just do a little bit off tweaking to see that it looks pretty neat. We want toe at some greedy into the colors as well, so that it looks professional that you some of the preset ingredients. Okay. And this one will do it. You may be with greedy in and do it do all right. And in terms of the series, that's put this 50 and one to old. Put it right. Site by site. All right. There you go. Okay. I'm just gonna bring it back to the original view, and you will known. See that the chaat is insight there. All right. That's just a quick demonstration of how it all begins with a pivot table analysis where you decide what do you exactly want? A show from this pivot table, and in this case you wanted wanted to shore. Ah, break down off Two years of sales volumes broken down by the different regions and we can see over here that, you know, it's Asia that is contributing the most off the sales volumes. And we saw that in 26. Then there were 24% improvement from 2015. Okay, so it's a very intuitive way to look at it and having it incorporate. And the dashboard is definitely a very, very powerful technique through land. Okay, I'm gonna break it down and show you the steps. More killed fashion in the coming chapters. So stick around and I'll see you in the next chapter. All right? I hope this has been useful, CIA. 13. Intermediate Series - Understanding Pivot Tables: Hey, what's up? Everyone Hope all of you are doing great. This is Chapter two off the intermediate Siris on dashboard and in attics. Understanding pivot tables. All right, we saw in the previous chapter a brief introduction on how pivot tables work. Now, in this chapter will be focusing more on understanding the ins and outs off pivot tables. And at the same time, I will also be explaining in detail how this tool works and the different kinds of analysis you can do with pivot tables. Right ones. You've mustered pivot tables. You are set toe having a very strong foundation in building dash boats. Okay, so the key topics I'll be covering today Not sure if you have noticed, but pivot tables are actually broken down into this. Four elements people, people feels we call them. So they will be the filters, the columns, the rose and the values will be covering them. For by understanding their various report layouts, we have compact outline and tabula. And then finally, when there are changes in your pivot table source, we want to learn how we can refresh your people people. All right, so we retain the same analysis even when the data has changed. Okay, so let's get started. All right. Okay. So now I've got egg cell open and you're looking at the seams red shit that we have been building. Alright, Sold this. Stretch it. We want to do a pivot table analysis. Yeah. Okay. So how to do it with press control? A. Select all their data and then click on the insert tab and then click on pivot table. All right, it's gonna pop out this window asking you what is the range of data you want to select off course by default. Since we have really selected all the data is gonna have the already selected like that, you know, that might have green daughter lines. And then they're gonna ask you where you want to place your people. They will report. All right. In this case, let's just go here of a new work. Shit. Let's click. OK, And then it's going to launch a new work ship, and there is this pivot table little window here, and then a pivot table panel. Okay, You're gonna notice that they call it the pivot table. Feels these are all the data feels within the pivot table source where we saw earlier is actually all the columns here. Relationship manager, first name, last name, country, region and all the respective the column data feels and the different elements we have in the construct off appear a table. All right, we've got the filters, the columns, the rose and the values. OK, so let's get down to understanding them. Okay, So say, for instance, you want to do an analysis on Mylan Gender breakdown off your sales managers in the respective regions. So you've got the region's let's start with the region's you put them in the rose. So you notice that it will be showing as the respective regions Asia you can U s a in the rose and then maybe we wanna have on the columns the breakdown off gender, right, So male and female. And then we have the grand total. Okay, so we want to see the distribution between male and female, and then we go a hit with putting in the sales volumes. OK, so the numbers are all out. Let's just keep it tidy. We see that in total. Let's look at filters. Let's say we have look at both years. So the filters tapped basically allows you to select What are the different years we have? Okay. In this case, since we're selecting both 2015 and 2016 we're looking across Bovie us, and we see that in Asia you can us eight. A total off sales volumes we have is about 800 million off which Asia has the most, about 405 58 off sales volumes. And the latch bolt a tribute to the male population of about 400 out off 495. So, apparently this firm that selling electron ICS has the mill seal staff are generally producing much more in terms of this analysis. So basically the columns are showing in terms off the gender and then for the roles we see that the region a plant insight. Now what happens if you change and put the gender into the rose as well? All right. If we insert gender into the region, then we can see that gender. Since it's one, there are feel bureau region under the rose segment. We see that under every single region, we see the breakdown off gender. Okay, so what happens then if we turn the other way around, okay? Returning the other way around that we're gonna see that if we put gender first before region, then we would see the breakdown off regions. We didn't that gender, okay? And this doesn't just limits to two different roles. You can actually expand it. And then you can put, like, paradox here. And then you get to see the difference in gender and females rightly speaking about looking at just a female. He'll and they were full of by the mill. And then you see the breakdown in the regions Asia total, and then you get to see the breakdown in the products. All right? And you can also sort over here. Let's say you wanna sorted in ascending order. You can actually just right click, sort and put largest to smallest. All right. And you can see that Asia, the number one business, is actually attributed to smartphones with huge amounts. Sales volumes, 45 million. But of course, looking at what we saw a little where the male population was doing better. We saw that you know, male population we see close about five times the proportion off smartphones being so in Asia by Mills. So basically, that's about it. We can likewise do the same in the columns where we shifted, although the columns. So you know it's quite rhetorical in terms of understanding how it works with in columns and rows. If you want to just look at 2015 data, you can just click on the filter. That's the main use of the field that had to sort of, like filter out the data that you're gonna use and you just click on 2015. Then the numbers were all changed to be reflecting 2015 numbers and the values step as well . You can change it stayed off sales volumes. You can maybe change it to revenue and then Schiff up sales volumes. And instead of showing the actual numbers, you can summarize the values in cone and every church in different kinds of variations. For instance, you can do it as a calculation as well in terms of the grand total. So we know that 25th in the grand total awas 54 million. Now we want to just put it as a percentage form and everything against 100%. You can see it like that. Where is all month? Against the grand total? All right, so there's actually a lot of different values you can play around with off the road total as well you can. You can do it. Any changes accordingly. So that's the use off, even tables here. Now let's try and maybe do products and the revenues. Okay, F Which let's do it in the form off numbers like debt. Okay. And let's look at 2015 and 2016. Yeah, all right. So we're looking across the rest of two years and we see that total revenues we're looking at is 127 million. If we look at sales volumes, it's about 800 million. But in this case, let's look at sales revenues, and we see that this report is showing in the form off. Compact version came by default. The reports of a pivot tables always show as a compact form. If we want to show it in the form of ah outline form, maybe maybe it's best we use names instead to demonstrate this. So let's use names and then we shift away compact and then we change. This sort large is the smallest. Okay, so if we insert the first and last name, which is sort of like expending the Rose here under the compact version, it shows us on the breakdown that you know, Andrew Con His name is actually Washington and is showing here his name is Washington and his last name is Washington. And then this other revenues that he's making. But if you show it in outline form, then it extends one. Call them out. But then, knowing that you can, you see that Andrew's first name and last name are in the next column Enroll, respectively. But if you want to squeeze it all in one same rule, you can actually use a tabular form and that straight away, let's get a wheel Sub told those that straightaway brings the first name and the last name off the relationship manager in the same role. For instance, Louise hung his first name and last name. I like site by site. And there, Andrew Washington. So that's basically how the report layout works. You can pull go around with it if you show in outline form first name and last name gets shift that one roll in one column out. And then if you want to put in a compact form, they all get squeezed in the same column. But then in new, different roles. So personally, if we are showing our data like that, enough sense of a first name and last name obviously will think it's Nita and clearer to keep it in the same role so that we can see clearly. Like, for instance, George Campbell, Judge Campbell, his his over his his full name is here and then we can know the respective breakdown in data. Okay, so that's about it for report layouts, the different kinds of report layouts. Now, what happens if your data changes? In this case, I'm not sure of you guys notice, but there is actually a little bit of an air over here where we saw Andrew. His name is his last name. Should any for tea, but his name. He always during this Washington. So obviously there is some error in terms of the data, and we want to try to use this opportunity to make a correction. So we want to be correcting Andrews last name instead of Washington. We wanna put in his real name and that is a ton. All right, so let's go back to the raw data. Let's say we want to make the changes here. We do a filter, and then we search for Andrew. Whoops, Andrew. And then his last name shouldn't be Washington. So let's change it back to a ton. And then that's overwrite all the Washington and change it and then go back to the people table. Now we want to do a refresh on the pivot Tebow so that we are actually reflecting the right name now that the change has been made. All right, So in order to refresh it, take on, analyze and then refresh. There you go. So that let me just do it one more time. Click on, analyze and then click, refresh. And then name is now Andrew time. All right. Same thing. If you want to create a new column on the data spreadsheet, let's say we call it, uh, call it height. All right. Height. And we just, you know, put in, um, the height off everybody Hell, let's say we call it everyone issue no greater than 1.5 meters, OK? And we just, you know, copy and paste it all here so that you know, it's saying that all relationship managers have, ah, height graded and 1.5 meet us. And let's refresh it. We go to analyze and we click on refresh Notice that on the right hand side you see that now height is included in sight. And if we add in the height here, we can see that all our relationship managers have a height created and 1.5 meters. Okay, so that's about it. For this chapter, way learned about how we can use the respective people, people feels the filters, the columns, the rose and the values, and also the different report layouts. How you can refresh pivot table affecting the new people, people feels and as well as making changes to the data set. It can all be refreshed in your pivot table. All right, so that's it for understanding people. People's play around with it, and I'll see you in the next cost, where we will be starting to build pivot table ourselves. All right, see 14. Intermediate Series - Building a Pivot Table: Hi, everyone. I hope all of you are doing great. This is chapter three off the intermediate Siris for dashboard and epics building a pivot table. OK, we saw in previous chapters how dashboards all begin with people tables. Now, in this chapter, we're gonna focus in detail a step by step process. When hold, you can build the pivot table. You want exactly the way you want it. Okay, so we'll be breaking down the steps. Five very simple. Four steps and we'll be covering two off the very commonly used examples. Firstly, a time series table analysis for by a snapshot comparison breakdown analysis. All right, this is gonna be the most commonly used examples. Being in a corporate workspace or in projects or any form of analysis basically will be covering these two fundamental examples which will give you the necessary skills you need in order to build all kinds of people. Table announces. Okay, so let's get started with excel. All right, So this is the table that we've been working on for quite some time now that see, we wanna go through the time series analysis first. Okay. To build the time series analysis, bus you select all the data, press control a on your keyboard, select all the data and then you can go under the insert hat and then click on pivot table . You can actually use a short cut number and teacher here right now. Press alternate and envy on your keyboard, and it will appear this little window which asked you to create a pivot table. Okay, so you can either use your mouse click, insert a pivot table, or you can actually use your keyboard. Press alternate and V Okay. And that's gonna prompt you to select what data you would need to create the pivot table. Obviously, you select all the data, and then you wouldn't choose where you want to place the pivot table. In this case, that's place that in a new work ship. Okay, we placed it in a new work. Shit. Let's zoom in. And now we want to do a time series analysis. So depending on your preference, you can either put them various Munns in the columns, all you can put it in the rose. Okay, I think putting in the roses, Nita, I just put in the roles and then I want to select. Let's say we want to do it. Analysis on time series analysis on sales volumes. OK, we want to see how much sales volumes are we doing across the months. All right, let's change this in tow decimal place and give it a right comus. Okay. Now, um, this is basically about that already. Know I'm gonna excite some filters so that we know exactly what kind of data are we looking at? All right, so that's it. You just want to focus on the year 26. Then we put the younger filters. And then we felt out 2016 as in, we felt up out 2016 only click OK, And this will be a time series analysis off our sales volumes across the various months. Generally, February, March, April, me until December. And these we know covers all the regions because all the regions here obviously also like that. It covers across all relationship managers, and it covers across all products, and it covers across all different ranks, and it covers across all ages as well. So you can get on the necessary filters you one into the pivot table, and then it will allow you to do the necessary filters. You want accordingly. So let's say you would want toe only show with sales volumes off. Um, Japan. Okay, Just felt out Japan and then click. OK, and then you would see the sales revenues like that. All right, if you wanna get in some graphical representation to make it look a little easier to understand, basically can just go on conditional formatting, click data and then click on a data green bar. Or you can choose a blue bar or you can choose Orange Bar. It's up to you. In this case, let's go with the blue bar. We can see the breakdown across the months. All right, instead of countries, maybe you want to go and filter into H. You can also take a look here. Maybe we just want to look at the 29 years old. All right, look at 39 year olds and see how Oh, sorry, there's no 29 year olds that are selling and Japan, Let's do again. Let's say we want to do 29 year olds. OK, so we're selecting across all years. We see that in the year started off at a pick, and then it came down to what you're sort of like taking Islam, the sales volumes coming off a little. And then it picked again in November, all right, and with the total being 12 million, that's how you do your people. Table analysis. Let's say if you want to break down and short in terms off clusters, right regions, we didn't this month and want to see how it all fares out instead, off putting the regions in the filled up compartment. You can actually put the regions in the columns, and that would show you what is the breakdown Accordingly, and we can see over here that Asia, relatively on a month by month basis Asia, is actually showing the strongest start in January, followed by another one here in November that the rest off the months we saw that it's pretty much hovering around 10 million to 30 million. But then those was another two striking months where we saw Asia coming off. It's one of the top performers symptoms off sales volumes. All right, so that's how you do obsess l time series analysis on the sales volumes, and you can, if you wanna you know, sort of like, changed the way you showed the data. You can actually just switch it around, arrange it like that. And then we have, um, regions showing as the rose followed by the months showing in the columns. Okay. And that's how you can show your data. It's up to you how you want to represent it. Next, let's cover a snapshot comparison analysis and stayed off. You know what Time series analysis? We compare a snapshot. So when it's when it comes to the snapshot, we were not really focused on the time movement, which is the months or the years. We just want to see a breakdown right away. So, for instance, if we do a Regions, we can see that in 26 then this is the total sales volumes that we see in 2016. And let's say we want to do a breakdown snapshot comparison off the respective relationship managers within the regions. There you go. Basically, you just concert the relationship managers into the rose tat, and we can see that across all the relationship managers, this the sales volumes they the, uh, earning here. Okay, you can actually sort it as well, largest to smallest. And then this is the breakdown. Okay, so basically what we are doing, if we recollect everything, Step one is to first understand what end and decide What kind of analysis would you want to do? Is it a time series analysis? Or is it Ah, snapshot comparison breakdown analysis. And in this case, let's say we moving of the 2nd 1 A snapshot breakdown, analysis. Then we can see that. You know, Bebe can decide that we want to show the relationship managers under the respective regions . Okay, that will be the first step. Second is to make sure that you have the right data. All right. You want to make sure that you have all the data unit for sales volumes? Make sure you go into the raw table tap, and then you see that you have all the sales volumes you need properly, and there are no blanks here. Okay, for step three is to make sure that you are familiar with the pivot table fills and you know exactly which fill the put insight toe build this pivot table. And if you want to change from sales volumes, you want to change through the sales revenues instead, Basically, just pull out the sales revenues here. Okay? And instead, off relationship managers. You want to see the breakdown in the rankings? Okay, You want to see the breakdown rankings, and you want to do some form off sorting, largest of smaller. And we see that in Asia, managing directors actually earning a lot in terms of sales revenues. And it has always been a case where by the managing directors are just typically making the most sales activities across 2016. Yeah. So next we want to fine tune the data, feel settings to show your pivot table notices better. Maybe, instead of just showing the sales revenues in terms off absolute numbers, you can also do a breakdown analysis. And this is actually very common. Where you go into the field settings, you insert another revenue ville inside here. I just did that and then you click on it, pick value field settings, and then maybe you want to show the values s, ah percentage off grand total. Okay, so there's for sure. A breakdown just changed the percentage form. You were sure breakdown. And then we just changed the percentage. Okay, and just change it accordingly. But instead of using a shortcut, alternate will see a You can actually just click on the the column with as well. And then you can see that you know, this is the breakdown in percentage form off the sales revenues. Okay, so basically, you can play around with the value filled settings to show the right Atlantis issue, and then that's about it. Basically, if you want to change it toe time series analysis, you can also do the same. Instead, off taking the rank, we're gonna do a time series analysis across Asia. You can U s a same thing, put it across like that, put the months in the role step and then put the sales volumes in the value step. And then we show breakdown in the clusters. Right? So let's insert the region's inside the columns, and we also want to see a breakdown in terms off the percentage. So let's say we put the sales volumes inside the values that we change it into percentage form grand total click, OK, and we have it like debt. Okay, let's take a with this. Whoops. Sorry. Wrecked. So this shows that breakdown in terms off the pivot table analysis where we get to see what is the breakdown exactly. So it's up to you how you want to show your kind of analysis. And, you know, it's really endless possibilities to do pivot table analysis. And basically, once you get familiar understanding the rows and columns of a fever, you can build all kinds off. People table notes that you can even build very complicated ones whereby there are just basically a lot of categorization involved. And maybe instead of mines, you see the breakdown off regions fuss, and then you have a breakdown off countries and then you have a breakdown off gender. OK, but typically when the pivot table reports have a lot, a lot off categorization, then I would encourage you to use a different report. Leo. And that is that's the Step five, which is to basically change it to a tabular form. Okay, when you change it to a tabular form, let's say we removed the sub told those then you can see it slowly transcend and transpire into ah, tree diagram. All right, where you have you can see that this is this Asia's breakdown and then you have the country's breakdown and then you have to mill. Female breakdown Okay, in excel in the default pivot table styles. Usually they don't show the breakdown so clearly in the default pivot table style, like blue and people table style, like 16. Okay, if you want to see the breakdown in terms of the class, they're a little bit clearer. What I can advise is to select the plane one of the most basic one and that straightaway box out for you, the respective categories. And then you can get in your colors and color distinction up to you. Maybe, let's say, for the hate us, I want to put it in light blue. I can do it like you like that. And for the sub haters in the different regions, I can put maybe an orange like debt. Okay, it's up to It's up to you, hold. You want to do it and then maybe for a grand total, I will use blue again. I'll use blue again. Okay, so basically that's about it. You can break it down even further. Let's say even after mill and female, you want to break it down to the relationship managers names. And there you go. You have it all like that. But of course, the drawback in excel is that the colors were sort of, like tweak again. So maybe what you can do is put all Deunan again, and then you play around accordingly. Okay, so that's about it. We see that we can actually do a lot of different clustering, whereby whereby we can, you know, breaking down the different categories first by regions, then by countries, then my agenda, then by relationship manager. And to get on the final numbers across here. Maybe instead of just sales volumes, you want to add revenue site by site with it as well. And then you have it all like death. Okay, some of sales volume, some of sales revenue. All right, it's It's up to you. Hold. You want to play around with it? Okay. Soul. That's about it. For for For this chapter. If you want to keep it simple. Our advice, we just show one value so that it's Nita. All right. Okay, so that's about it. Just full these steps. This plus five steps. Firstly, to understand what kind of analysis you need Secondly, make sure you have the right data in the raw table. Make sure that you have all the right data you need. Next is to use the pivot table fields to play around to get the right people. People who would want to fine tune it, whether you wanna show it in absolute form. But you want to show it in, ascended from or you can actually show it in a count form as well to see how much data you have. And also, you can show it in average for Miss Valdes are all basically all the different kinds of calculation. What you have here, the most common being some which is basically show an addition off all the data you need. And then finally deciding on what kind of report me out you want. All right, be a tabular form which usually you need a tabular form. When data categorization gets all you can just keep it simple and commit to come back. For me, it's much more insight, much sink. Okay, so that's about that This chapter has been used. You just referenced on this chaplain. It's not building no tables, no gate. I'll see you in the next job 15. Intermediate Series - Designing a Pivot Table: Hi, everyone. Welcome back to the Siri's. We're now at Chapter four designing a pivot table. All right, from where we last left off, we learned how to build pivot tables. We learned what kind of analysis you can do with pivot tables, but in this chapter will be focusing a lot more in terms of the design aspect off pivot tables. All right, we saw that to build a professional looking dashboard. The design aspects of them Ah, well taken care off. So that means in terms of mastering the color schemes and stall report format to have your people tables in, it's crucial to understand the design elements off pivot tables. So in this chapter, I'm going to be covering all the different elements of it. Sit tight and listen truth. So let's get started it with Excel. Let me just open Excel. What you're looking at is the raw data that we've been working on and we have ah Daschle design that just built up here, which is in a clean, white, simple salt. Okay, for the raw data, let's quickly create a P, but okay, we create a PVA and we want to do a analysis on the poor ducks across the different regions , and we want to put it in sales volumes. So we want to understand what is the breakdown off sales volumes in the respective regions across the different kind of product that we're selling? All right, so let me just rename it pull ducks and we call this okay, by default, Excel is going to show you as some off sales volumes, but we we really know that obviously is going to show us some. So let's just rename it to sales volumes. All right? Now, let me just copy and paste it into the dash. What set up? We have it here, and we want to paste it into the dashboard analysis. Okay, so we know that for the first table there are two columns. Let's so it probably fits nicely into the first table. If it's very snug and nice. Okay. Depends on the overall color scheme. If you are doing a white skin, maybe what we want to do is just wrap it in a nice border. Just click on board those like debt, and it's no insight. Yeah, if you want to create a graph, just click insert and maybe just take a graph like that. And then you have a graph set up like that. Maybe we can also just choose one of the designs and then just hide it. And then we put it here like that. So that is the whole idea of a rough, sketched out version off. Um, graphical, we call this graphical analysis nemesis, um, in terms off the legends, we put it at the top Like it. Okay, so let's focus more on the pivot tables. We see that we can actually build graphs or charts or tables inside the dashboard. Ive the dashboard is, however, not white color. All right, See, it's a dark color. We want to select maybe a dark blue dashboard scheme, and then the wordings are all gonna be in white. We want to change the border. Two white in color. What? Sorry. Let's put this just Botham change it to white in color and then your people table will all change as well. So let's just remove this. We select it and paste it in. And assuming you like this color, you're finally of it. But if you're know it, then you can actually select a whole arsenal off design pools that excel already provides you. Once you click on the pivot table, click on the pivot table pools on top. We've got the analyzed head and we've got the design tap. And so click on the design tap under the pivot table styles. You see that they're all these different styles here. Click on this little a role that falls down, and then you'll be able to see that there is this light schemes that excel provides. There was this medium scheme that excel provides, and there's also this dark scheme that Excel provides. So this tree general schemes that all preset that for you in excel it's up to you which color scheme you would want to adopt. All right, so in this case, I would go for the doc Boo so that if it's not within the color scheme where the funds are in general, the hitters all in white and the skin is in dark blue. Okay, if you wanna make some slight adjustments yourself, you can actually do it as well. For instance, you want to sort the numbers accordingly, so you have smartphones showing as the top because it's in terms of sales volumes has the largest across all tree heiress. Maybe what you can do is right. Click on it, I thought, and I just the smallest. Okay, all right, in terms of graphs as well is kind of like the same as tables whereby you click on design. And then there's all this basic preset graphs designed for you. So it's up to you What kind you would want to select in order to build the stall that you want to have in your dashboard. All right. And you can actually customize the layout as well. Say, for instance, you wanna have, ah, fabulously. I'll show you using this table here. So let's say we remove it. This table we can see has four rows, right? Pharrell's So let's say we want to build something a little bit more congregated here. Um, we want toe. Typically for four rolls you we would fuse the tabula function so tabula we sure it's tabula. Let me just bring it. Bring it here off key soul. That's just true. The kind of people data where it my just nicely across. Let's say we want to Onley. Compare U K with U. S. A. And we want to see the difference in years here, and we just want to focus on accessories. All right, There you go. So you've got, um, 25th in total versus 2016 total across U K and U. S. A. And you want to align it all like debt? All right. So you can actually just copy and paste it in like that. Okay, so there you go. You've got to pivot tables added in in tow your dashboard already, and you can actually select a different kind of skull. So this is basically the tabular format where you see that in terms of styles is nicely broken down accordingly. And if you want to do your own customized style, what I reckon is the most useful method is to select the light beam where is completely white, completely clean, and then you can do your own customized design. Okay, let's say I want to put it as blue and black. Maybe instead, off Instead, off this blue, I would change it to orange or read. So maybe Rick can what gets a nice contrast to it like that and then maybe I'll go for a lighter shade off rate, maybe orange, for instance for the UK total and then for the U. S. A. Proto as well. Just put it like that. And then for 2015 total are maybe put it as, like, beach like bitches. Well, 2016 like debt like that. So basically, there's this different ways how you can build in your people's into your dashboards and it all looks very slick. Okay, so in terms off the people table star options, you notice that there is this other panel which shows you the kind of columns or he does a bandit rose. If you select Bendit rose, then it will all be bend it. It is selective until Roe heaters than the row heaters Asia u K U S A. Will not be showing. Probably see So you can just mass around with it, toggle it the way you want it, and then you would change according me. All right, so that's a wrap for this chapter. Hopefully, it has been very useful for you. You learned how you can do your own designing on your pivot tables. So that complements the overall color scheme of the dashboard. I hope it has been very helpful for you. Okay, I'll see you in the next chapter. 16. Intermediate Series - Refreshing your Pivot Data: Hello, everyone. Welcome back to Excel, Dash. What an ethics Intermediate Siri's. We're now in chapter five, refreshing your pivot table. All right, so this chapter is gonna be very crucial. Especially when your data set changes from time to time. For example, at the start of every new month, you might be receiving a new set of data streaming in into your data sauce. And when that happens, you know that you need to refresh your pivot tables. All right, so in this chapter, I'm gonna be teaching you some very effective tricks how you can refresh your pivot tables easily without having a heating. Okay, so sit tight and let's dive right in. All right? Right now you're looking at a pivot table that we have Just create that and it's reading off data exactly from the data source here. And take a look at the data set. It is actually reading it specifically from cell A one all the way to sell p 3265. So it's effectively just this their arranger. Okay, so if you have new data coming in is not gonna be reading it because it is literally just reading this fixed ranger. All right, if you want to be able to receive new data into your data source, you would want to expand this table or range that the pivot tables reading from. So in order to do so, basically, you can just drag and read off all the columns like that, and it's literally reading off column A to column p and then we click. OK, all right. So straight away, it's also reading the banks here. Okay, let's just create some new entries. For instance, has just create this new guy Jordan Thompson. Okay, Jordan Thompson. Ah, Jordan Thompson being his last name. He is based in New York. Ah u s a mill That's a hiss 40 years old, his a managing director. If he has sought smartphones as well on 26th in dissemble 2016 December and he has so all to get a four million worth off smartphones. And with that, he has earned a tote off 15% off the sales volumes. Okay, so just put it like debt and in terms of ah, simplifying it. Well, let's say we divide it by one million. That gives us 0.6 million Let me just copied the format over. And there you go. We have just created a new entry for Jordan Thompson coming in in December 2016. And he has on formula and wolf off cells. Okay, so let's just refresh the people. Click on, analyze and click. Refresh. There you go. We saw Jordan Thompson's increase in has brought in sales volumes of for Mylan here, and we double click on it. We can see this new entry. Yeah. So that's about that. We can see how you can effectively read off new data by expending that they are set to read off all the columns. Okay, not a method which is actually pretty used to as well is to read it off a table and state give you an example. Let's just remove all for mets. That's just clear. Pure form. It's all together. And then for meant it as a table into bluestone. OK, do star. Okay. And then there's just tidy up the phone a little. Okay, there we go. So, um, we have all the data set here now, and it's reading it off as a table. Okay. All right. So instead, off creating a new data from here. What you can do is just paste it into the next. Call them and let's say we just peace. A couple more in I just place it in like that and the table will read it accordingly. Notice that the end of the table is actually denoted by this little symbol mark here. And let's say we have Jordan Thompson earning smartphones in December. Here's what will make some sales in November, October in in September, in August, in July and also in June. Okay, let's just stagger hiss sales accordingly. Let's say he earned four million here Here. Cream ale in here, here on two million here. One million here on 800,000 year. It's, uh, 600,000 here and 400,000 years. Okay, so let's just expand it out. And we can't basically just created a new series of data for Jordan Thompson coming in. And we have injected that data into the table that we have just formatted. Now, if we want to be reading off the PVA from that, we can actually just copy and paste the name off the table. So let's call this table PVA data. Okay, we just create that? This is PVA data. And then, um, we click on pivot table and we read it off from pivot data PVA data. OK, so there you go. It is effectively reading off the data from Pivot Table. Instead, you click on change that are sauce notice that it's really reading it up from pivot table if we want at in any new roles, for instance, just like that. That's how. Just press tap, create a new role, and we call it every every bry Brian. Okay, Eric. Brian, uh, we call it from Hong Kong Asia Mill. That's a feed. Looking on testimony. Mill 45 the four. He's an associate director. You saw tablets 25th in. Let's see, he came in in 2015 and he so, um, in dissemble eight milion. Okay, so we just copy and paste it down like that. And there you go. That's just refresh. Click on analyzed, you can refresh. And there you go. We have editing Eric Brian and reading off the table instead. Okay, so, lastly, I'm just gonna teach you a quick shot Cut. How to refresh a pivot table in ST off clicking your people table. Click on analyzing. Click on Refresh. You can actually just use the shot. Cut your alternate air, Fife. That's what you see on the screen. Alternative, Fife. Alternatively, you can also use the keyboard shortcut and press alternate J t. Then kick on F and then kick on our or click on a. Okay. The difference between refresh all and refresh is basically refresh is only just pertaining to your own single people. Refresh all repos toe all the pivot table connected in the entire workbook. OK, so that's basically a read on how to refresh your given table data. Whenever you got new data coming into your data source use that he bought shortcuts to refresh your data. You become a pro in no night. I'll see you in the next chapter. See, I 17. Intermediate Series - Moving Pivot Tables: Hi, everyone. Welcome back to the Siri's. We're now it Chapter six, moving pivot tables. Okay, this chapter is gonna be extremely crucial. Especially when we want to move out. People, peoples around on their boards. So this chapter, we gonna learn how to move pivot tables within the same work. Shit. And also, we're gonna be covering how to move pivot tables from one work ship to another. All right, those will be covering how to cut copy and pays your people tables around Excel workbook. OK, so let's not waste time and let's get started. Ok, so right now you're looking at excel. Ah, pivot table that we've just created. And this is actually reading off the data from the raw data worksheet that we have. So to start off. Let's try moving a pivot table within the same work ship. All right? To move. Ah, pivot table right now and sets sell a 32 e nine. Okay, let's say we want to move it to sell a fault in. All right? And then we have it down like that. Okay, So, basically, just click on, analyze in the pivot table tools, click on, analyze, and then click on move, pivot table over here. All right, click on it, and then this window will pop up is gonna ask you where you want to move your people table . That's just save going to move existing work. Shit. And then just click on cell a 40. Okay, so once that is done, it will just move according me. Okay, if you want to move it back, Same thing. Just click on move, pivot table, and then click on sale a three. There you go. All right, so that's basically very simple. Just moving a pivot table around from one place to another within the same work. Shit. If you want to move your people table, toe another work shit, you can actually just click a move, people table toe a new work shit and then click. Yes, There you go. It's now in a new work. Shit. So let's just call this work kit B, which is a new option. If you have an existing walk. Shit, we call it work. Shit. See? And then, uh, let's just put it sending 1 60 that say it's over here. The tap colors red in color, the pivot tables and what should be. We want to move it over toe worksheet. See, um, then it will be the same thing. Analyze pecan move, pivot, table, click on worksheet, see? And then click on where and what should see You want to place the people table. If you want to place it in the meat middle of the worksheet, just click on F seven somewhere in the middle and then just click. OK, Is there a little bit off tiding the with cell with And there you go. Your pivot is now. And what should see right smack in the middle, okay, is no longer in what should be is longer and work should A It's knowing what should see. Okay, so that's how you move pivot tables within the same market and within worksheets in the same workbook. Actually, a simple method is to copy or cut and paste it around. All right, so your keyboard actually has this control X function, which is a cut effectively. We're cutting, cutting the entire pivot table, and then we pacing it wherever we want. Peace it. So let's say one apiece in the same. What book? Just click piece. There you go. So that's also another method. How you can move your pivot tables. You can cut it and then kick on wherever you want to have it paste it. There you go. Okay, So if you want a copy and paste that you want to create a duplicate, All right, so you click copy and then click on wherever you wanna have it duplicated to just click on pace. And then you have another people table off which, when you do your analysis here, you can do your own, filled the ring, say, for instance, you just want to select smartphones and tablets. Okay? And it's it's effectively another people table you have just created and you need not create it from scratch. So that's basically in a nutshell. What you can do with moving pivot tables around cutting, copying and pasting people tables. All right, Hopefully this lesson has been helpful. I'll see you in the next chapter. Okay, Have a good day 18. Intermediate Series - Sorting/ Filtering/ Arranging: What's up, everybody? Welcome back to the ultimate dashboard and ethics course. When I chapter seven off the intermediate Siri's salting, filtering and arranging. Okay, we're gonna be learning some very cool and important tricks in this chapter. How to sort and filter your data and arrange it just the way you want it. Okay, so that's going to give you the autonomy to be able to massage and move your data around just the way you want to express the analysis or the view in the dashboard. So this chapter is gonna be very useful in terms off presentation and polishing the look and fill off your analysis. All right, so let's get started with Excel. Let me just get it open right now, okay? You're looking at the raw data we have in order to start sorting or filtering the stretch it go to the home panel, click on sort and Filter, and then just click filter. It is going to give us all the drop down bottom selections and depending on how you want toe slice and dies hold. You wanna range or later, You can actually just play around with it from here. Okay. For instance, If, um comes off months, who's gonna sort it from smallest largest? Nothing. With 2015 January, and then it will be all in 2015. And as you scroll down, it's gonna be main June July as it leads all the way up to 2016 and it goes all in order. All right, so that's how you arranged in terms of months and comes off sales volumes. Let's say you want to sort it from smallest to largest, because right now it's obviously some are larger than the others were. Some are smaller and they're not. They're all jumbled up, and they're not arrange accordingly. If you want to sort it, just click on the drop down, but then and click smallest, largest. There you go. So if you scroll all the way down, if you just scroll all the way down, it's going to show you the largest number here. And if you go all the way back, I begin. It's gonna show on the smaller sales volumes here received by Emma Jones. Okay, has the same for revenue, basically largest to smallest, smallest, largest like that, and you can actually filter your data just the way you want it, Fire this pattern as well. For instance, if you just want to look at all the managing director's just click managing directors and click. OK, there you go. So all the data is now showing all managing directors, all right? And if you want to undo that, just basically click on the select all again. All right. So that's basically how you filter and sort your data. I'm gonna show you something that is much more interesting. And that is the custom sort. Okay, so let's click on the sort and filter button and then clear everything and then let's click on the custom sort. So the custom sought is basically gonna pop up this window panel where it is going toe allow you to inject a sequential sorting order off your data, for instance, you want to sort the region's first A to Z, and then you want to end another level and saw the sales volumes Okay, sales volumes smallest largest. So when that happens, you're gonna show all the Asia date our first and then we're gonna move down to UK data, and then we're going to go down to USA Data and they're all sorting accordingly from smallest to largest. So basically, the custom saw thing allows you to do this kind of layering in terms off salting experience . And you can actually just do the same for this in pivot tables. So, for instance, let me just do a demonstration for pivot tables. Let's remove the filter. Let's just quickly build a pivot table. Ah, here. And we want a range regions full of by the relationship managers and sales volumes like that. Okay, that media is to so sales volumes. You want to do a quick sort here instead of using the sort and filter you can actually just right click the people. All right, And then you can click on sort. Smallest, largest Oh, sought largest to smallest. Same thing for roll labels. You can sort a doozy. Ah, Zito A Even the haters is well, Asia UK and USA. You can sort from Zito a starting with us A. You can and Asia. So basically you can arrange it. How you wish. There is a lot, a lot of flexibility over here. Okay, so in a nutshell. Debts about that. In terms of this chapter, you can learn how to play around with the pivot tables or the raw data in terms of sorting . Let's play around with this tool sorting over here a doozy, custom something or even just using the filter button to do your sorting experience. Okay, so that's it. I'll see you in the next chapter. 19. Intermediate Series - Conditional Formatting I: Hello, everyone. Welcome to Excel Dashboard Analytics Intermediate Siri's. We're now it Chapter eight, and we're gonna be delegating the last three chapters of this series to the wool off conditional formatting. Right. So you must be wondering why is conditional formatting so important? Well, what I can see is that conditional formatting, if you must let it right, it can become a very useful skill you can at on through all the data analytics reports or kinds of dashboards that you're gonna build. You can add it as, Ah, nice, fine Polish touch to it so that it looks very professional. All right, so I'm gonna show you how you can do all of that in this last three chapters of this series for the first chapter, Chapter eight, what we're gonna do is we're gonna break it down and I'm gonna show you the highlights. Cells rule logic as well. Is that common bottom rule logic? All right. In a couple of different examples, I'm gonna be going to of you, so just sit tight and listen true, right? Let's get excel, Studdard. What you're looking at is a people table that I've just create that we're looking at all the sales volumes off all the relationship managers across the different products that they're selling in the year 2015. All right, um, so, you know, at first glance, when you look at a report like that, you see a lot of numbers, right? So it's not gonna be very professional, because when we talk about their professional report straight away, a very effective report is that you will highlight to you what you need to take note off at first glance. Okay, so with the help off conditional form a thing, you can achieve it. Okay, so let's say we want to be highlighting sales volumes that are greater than one million. Okay, so maybe one millions to low. Let's put it five million. All right, let's get greater then and we put five milion over here and there. Let's put it as green. Okay, So basically, Excel has attracted that this other cells that has five million sales volumes detect that and have highlighted here in green. If you want to see what happens if you change, the data said, let's remove the 26th in. Then the date I said changes accordingly. All right, we've moved back to 2015 did us that changes accordingly. If we look at both 2015 and 2016 then we see more sales volumes that are greater than five million. All right, So if you want to remove and do the conditional form anything, just click on the conditional formatting and then click clear rules from selected cells. Okay, so that's about it. Basically, for greater than same thing for less than and between an equal. That's just let me just show you for less than basically, let's say if you want to put less than five million, then all these remainder cells will be highlighted in and rate. Okay, so it's up to you if you want to put between, let's say one milion and 5005 million. Okay, there you go. These are all the sales volumes that are between one million and five million. All right, so, um, just a show you this thing about the equals to ah, this equals two is reading the value down to the exact decimals. So in this instance, we can see that look at the top formula bar here. You can see that all the numbers are sort of like they have decimal places. And, uh, it will not be very useful if you put the use that equal toe rule logic here. Okay, Unless you literally right down the respective decimal places. Okay. For instance, let me just show you 8,049,115 right? Let me just expanding out. Okay? Let me just expand it out like that. We see that there are old together. This is the total value here. We want toe. Identify this value. Say, for instance, you really want to identify this value out of this spreadsheet of numbers? Just going on highlight and click it quote, too. And then just enter it in. 80 for 91. 1469791915 Okay, There you go. No, you see that? It appears there. All right. The equals two button REITs, the value down to the exact decimal place. Okay, so basically only use that when you're dealing with whole numbers. I reckon that would be much more easier than having to deal with all these decimal places. Okay, so let me just remove all these rules. So that's about it. for highlighting cells with values. Four. Tax that contains dates and duplicate values is basically working around the same logic whereby you key in a criteria will say, for instance, tax. Let's say we want to put George all right, you put in George and then it will highlight accordingly. If you you do not like the rate fell, you can go to the green filled or you can go the yellow Phil. All right, it's up to you and same thing for day as well. Is that the same thing? I will not go through that. Let's say, for duplicate values on this is interesting. I've just added in tree additional rows here. You see, Brian has three million for two million 426,836 for sales volumes. We've got three rows here that are duplicates. Okay, if we want to check if they are duplicates, basically, just click on conditional formatting highlight cells to beget values and you feed it. They're all duplicated. That's why it's all highlight that. Okay, so if you want, this can be a very useful method to help you identify which values within the entire data set you're working on has duplicates involved. All right, Since that duplicates, I would just go here to remove them. All right, so I'll just clear them up next. We want to look at the top and bottom rule logic. All right, Pop. In bottom logic, say, for instance, I got all this data here. I'm gonna look at the top and bottom rule logic off pop, 10 items. There you go. Top 10 items. It was straight away, filled their it out for me, and then I want to mark the top bottom items as red in color. Right? So top bottom 10 items put it written color. Okay, so it is actually pretty good, because at first glance, you know what are the top performers and what other was performance? And then if you stretch around from 2015 to 26 then you can see how it works very intuitively. And it's so much better than static data formatting. Right? So this condition for my thing met that it's actually really useful. So likewise if I If I clear the rules and then I instead of top 10 items, I go for top 10% and I put as green and then, ah, bottom 10%. I put it as yellow sari rate read. So it helps you identify in terms of percentage like that. All right, we can put those that are below average and then those that are above average above average , putting as green. Okay, so that's it is very intuitive. Whatever kind off rule, if you want to get in, you can sort of incorporate it into your data spreadsheet or people table. It's all very, very useful. So hopefully this chapter has been very useful for you. We will move on to more advanced conditional formatting in the next chapters. All right, so I'll see you in the next cost. 20. Intermediate Series - Conditional Formatting II: Hello, everybody. Welcome back to Excel Dash. What analytics? Intermediate Siri's. We're now in chapter nine off the intermediate. Siri's conditional formatting tomb. Alright, In conditional formatting one, we learn how to use highlighting Selves, move logic and also the top bottom rule logic to implement some kind of conditional formatting into our work shifts. All right, so in this chapter, Chapter nine, we're gonna be working on a little bit more in the wall of conditional formatting. All right, we're gonna learn how use there are bars and also color skills toe at on the way how we can commend conditional formatting logic into our spreadsheets. So it's gonna be a little bit different, but in this chapter, we gonna learn how we can use them and this couple of different things to pay attention to in the wall of data, barras and color skills. Okay, so that's not waste time and let me just get excel open. So right now, what you're looking at is a simple symbol table. Just create that and we're going to see how we can use there are bars through to play around and create some kind of conditional formatting effect on these numbers we see in the table. OK, so let's say we want toe in some conditional formatting logic with regards through this spreadsheet here. All right. You see a couple of different names Aaron, Jason, Louis, Weenie, and Michelle and Simon and that this is their cells that they have achieved. Okay, so let's just click on this table. The values click on conditional formatting and then taken data bars. All right, When you click on data bars, you notice that there is this section off great and Phil and sold it, Phil. Basically great and feel. And so it fell. If you move your mounts around them, you will give you a quick preview off. How the data borrows will look like so, you know, just choose the color that you like most and you feel is most appropriate, and then just go ahead of it, right. In this case, I'm just gonna go a hit with the greed and feel blue in color, and then I just click on it. And then that's it. It's on a little bit off color to the conditional Phil. All right, North. Is that how much off the bars as in how long the bars are. It depends on the pills and things that you have selected. So, for instance, Aaron, he has the most cells achieve 1277 as opposed to Simon. So we are seeing that Simon's bar is approximately half off errands, and if you want to change the settings such a debt, you know that the bars do not overlap with the numbers. What you can do is basically take on conditional for my thing, take on manage rules and then click on Edit Rule. And then you can select the minimum and maximum that it detects from the numbers. All right, Right now it's set us automatic, so when it means automatic, it's straightaway fits off errands. Value s the highest and Simon's minimum value to be zero. Okay, If you want to set the lowers value and maximum is the highest value, then you click on OK can apply. There you go. So if we do the lowest value and highest value for men, we would see Simon as absolutely nothing because it's reading off that he has zero value because it's the lowers their okay, So for Aaron in this case, since it's off the highest value, it would show that his numbers is off the highest, and then they were arrange accordingly. So this doesn't really reflect very well. Maybe incomes of ranking on comes a relative ranking that makes more sense. But in terms of absolute comparison, then it doesn't make sense because we know that Simon over here he has $620 worth of sales . So what we can do? Maybe it's We click on the settings and then click on Edit Route, and then instead of lowers value, we click on number and we put at zero and the highest value, maybe be we can click it as number as well. And let's say we don't want toe over. Let the numbers So we want to create some kind of buffer. So we want to put maybe 2500. So we know that even for Aaron, his bar wouldn't exceed the numbers. It would be somewhere on here in the middle, because 1277 is approximately about half off 2500. So just click on OK and then click apply. There you go. All right. You would be able to see that it's all measured against the range. We've just create that as 0 to 2500. Okay, so the farthest end on the left, which means the minimum value being zero versus the maximum on the other end. 5 25 2500 Okay, so that's about it. And we see that Aaron being about half way 1277. It's sort of like in the halfway mark. And we see that Simon 620 is in the middle of 1277. So he's around here. So basically using this method clicking on the cells and then you manage the rules and then you added the rule and then you can select the minimum and maximum value. It allows you to be able to configure the length off the bar. All right, the length off the bar you wanna inject into your select that sells. So they're basically a couple stop here in terms of formula percent and percent all, but I guess if you're concerned about overlapping the numbers, then you can actually use this number method whereby you adjust the minimum and maximum number over here. If you want to just go ahead and overlap the numbers and putting back putting it back as automatic. We'll do okay if you see you back the automatic, you see that there are automatically decided errands. Value being the highest is the maximum, and the minimum would be zero. Okay, so that's about that. If you want to remove the values and then it just shows as number so you can see the relative ranking. What you can do is click on manage rules, click, add it and then click short bar only right to control bar only. Click. OK, take a play and he would just show jobs the bars. So that's how you can create date out. Bowers on your spaceship is very useful because you can have this integrated into your dashboard, and it looks very impressive as and when the numbers change, alright becomes a very dynamic dashboard that you're gonna create. So nothing that want to go true today. Site from data bars are color skills. Okay, so color skills the same thing. Let's just quickly remove all the rules here, and then we want to add in college skills, All right, so just mounts over on color skills, and you see that there's babying factors that affect the color skills as well. All right, and you can actually talk. Go the the definition and the configuration of this color skills, for instance, and we go hit with this one here. All right, click on green, yellow and red color skill. Take on it and then click managed the rules. A did rule, and you'll be able to see that the reason that it's screen is because it's closer towards the maximum value and the reason that it's red is because it's closest towards the lowest value, so colors that are within a yellow frame somewhere in between. So likewise you see here as well Simon's value is a visitor lowers. That's why his mark as Raid and then for errands because it's marked as green, his off costa highest value. You can see that it's showing out like that, Okay, and Winnie and you e there somewhere in the middle. That's why we call we put as yellow OK, so that also adds on ah, sudden kind of stall to the way you wanna use some kind of conditional formatting in your spreadsheets. So just keep in mind off this a couple of different form. It's all right, so that's basically about it. In the wall of data bars and color schemes, I hope this lesson happening very useful. I just given mine off this little tips and we will move on with the next chapter. We'll see you in the next chapter. 21. Intermediate Series - Conditional Formatting III: Hi, everybody. Welcome back to Excel, Dashboard anesthetics. We're now at the final chapter off the entire intimidate series. Today we're gonna be covering conditional formatting three. Okay, this is the last chapter of the entire topping on condition off anything. We're just gonna be learning just the last part of it and that it's mainly icon cells when and how to use conditional formatting as well as how to manage and clear the existing conditional formatting rules, sharing some tips on how to go about using conditional formatting. And also a main recap in summary, off what we have covered throughout the course in this series. OK, that's mainly mostly about pivot tables. All right, so you know, that's been agenda for this chapter. Let's quickly get excel open and dive right in. So this is excelled. Right now you're looking at a small table here. We want to just touch on conditional formatting for the last pot, and that's just click on conditional formatting and icon cells. We've covered the rest earlier in the previous two chapters, Click on Icons cells and you see that there is this different variations or icons that excel allows you to use in terms of symbols, right? Most of them they come in pairing off threes, some comfy in a pairing off five different kinds of symbols. And, you know, you can just play around with it, All right? Basically, what it does is that it breaks. The values that you have selected in this case have selected this on the role of all the column of all the values in the table. And if we just select maybe the circles All right, so the circles, basically what they do in the automatic value is that they were arranged the values in accordance to the relative comparison, and then they were rank it, which is the top 67% buses. The middle 30 treated 67% and then the bottom 33%. Okay, How it does is that it clicking conditional formatting quicker, manage rules and then click on edit room. And then you would see that in this case, values here are showing accordingly in this order whereby it's instead off. This is currently, like, you know, ah, four series by every clicker. That's why it's 2050 and 75 in terms of the divider. If you're clicking on a tree. Siri's Tree symbol version, for instance, Blank star half star in a full star. You see, that is 30 tree and 67 So just like nicely dividing it into three sections. Okay, um, if you click on the five Siri's whereby there five different symbols, then it splits it down into 2040 60 and 80 and that the various percent Vegas. Okay, so it's up to you how you would want to sort of, like, segregate your data. That's the whole idea of conditional formatting using icons. One thing to note here is that you cannot for icons as well. You can remove the values insight. For instance, if you just wanna short icon on Lee and remove the numeric values, they're just click OK, click apply, and that's it. It would only show asked the icons so you can actually squeeze it down. You know, just remove the hate us and then squeeze it down like that, and it gives you jazz and icon over there. Okay, it's after you also is to use your creativity in terms of how you wanna use this symbols to represent a data. In any case, you can build this within your dashboards as well. So that it looks gives it an extra different touch through it. OK, instead of just tables and charts, you have this very, very it symbols to sort of like at a different look to it. Okay, so basically, that's in a nutshell. What you you need to know about conditional formatting. If you would just want to remove all the rules off the off the cells, just click clear rules and click clear rules from select that sells and you remove all the conditional form a thing here. Okay, Conditional formatting can also stack. You can stack this. There are bars together with color skills, and then you can also add Cymbals together of it. All right, so it's sort of like creating a lot off symbolic representations on the data set, but it's up to you how you would wanna exercise that creativity. So it's up to you how you want to do it. In essence, I just want to share a tip here. That conditional formatting also takes up quite a bit of memory. So, for instance, if you're working off a huge debt, I said and you wanna have conditional formatting on every single cell of the data, said. It's gonna take up quite a bit of memory, so I guess it's is mostly applicable and useful. If the data said is not too huge, it's on. May need a final output whether the final output is off a certain number than the conditional formatting effect gets triggered. So you don't have to use it on all the cells in your entire date, I said. You just need a dual use it, perhaps in the final analysis. Okay, so in a nutshell, that's about it for conditional form anything. Okay, let's just wrap it up for this entire chapter. Let's do a quick recap on what we've learned in the entire cities. Intimidate seriously, mainly covered mostly on pivot tables, and we learn how to play around with the values as well. We saw in this Empire Intermediate Siri's how you can use pivot tables to create different kinds of effect graphically, and you can use different kind of designs as well the shape it just the way you would want it to look okay. And we also understood that you know, people tables are actually the the main foundation to the dashboard. Because if you want to build people charts, then that comes from your people tables. Okay, so that's basically in a nutshell. What there is to intimidate cities. Let's quickly wrap it up, and I'm gonna give you a quick introduction of what is going to be expected in the professional. Siri's okay, In the professional series, you're gonna be looking at the whole new world off pivot charts. Okay, We covered the PVA tables in the intimidate cities, but for the professional Siris are gonna be looking into how pivot charts work. We're gonna look at how we can build outstanding professional looking people, charts how slices work and how we can connect pivots together so that all your people tables and people charts are all connected. All right, we're also gonna be touching on data spot lines and effective visual analytics. So all of that, we're gonna jam pack it in the whole professional series, and it's gonna be a lot a lot of exciting stuff to look out for in the next series. Okay, so just stay tuned and keep a lookout, and I'll see you in the next series. All right? Care. CIA 22. Professional Series - Introducing Pivot Charts: Hello, everybody. Welcome back to Excel, Dashboard Analytics. Welcome to the professional. Siri's just a quick recap. What we covered in the beginners Siris was mainly focused on the basics and fundamentals of excel getting used to the interface before learning help built dash boats. Okay, whereas in the intermediate series, we learned that Excel, dashboards, anesthetics it actually all begins with pivot tables. So we're learning the foundation off, how to go about building pivot tables and her use data anesthetics with pivot tables. All right, So as we take one step forward, we move into the professional. Siri's will be learning the wall off pivot charts. Alright, we're gonna learn how to build, construct and format pivot charts. And then we was gonna be learning about slices, how to use slices with interconnected pivots. And finally it was the end of this series will be focusing a little bit more on visual analytics to create very impressive professional looking data, dash bots and unethical reports. Right. So basically, this professional Siri's will be bringing you one huge step closer to be able to build impressive, beautiful looking dash watts, and that's gonna be a lot off stuff covered in this series. OK, so sit tight and enjoy this. Siri's alright for checked the one we was starting with an introduction toe. People charts understanding what exactly a pivot charts and what can they do? Okay, so the key topics for this lesson would be to firstly understand on what are the different chart types or pivot charts? What are the different quick layouts elements preset stalls, excel provides? And also, what kind of pivot fills layout can you configure to create final chart output? Additionally, we're also recovering how you can build a second chart overly on top of your existing chart so you can create multiple effects there. Okay, so basically, that's about it for chapter one. Let's quickly get started. All right, So what you're looking at right now is quick. People analysis off sales volumes showing a breakdown off sales regions by products. Okay, so we've been working on the same data set that we went through in the Beginners and intermediate series, will be using the same one here, so it's familiar with you. All right, so in this pivot analysis, we're looking at sales volumes for 2016 verses 2015 and we showed a breakdown off the respective sales regions classified by the products. Okay, so once you're done with the pivot and this is something like that, you want to create a visual. People chat. OK, so to do that, basically, just click on the insert tab on the top left corner, click on insert tap and then school to the middle and click on pivot Cut. Okay, when you click on People, Child is gonna create this pop up window, and you're going to see that on your left column. There is this section where you can look at the different kinds off chart layouts possible . All right, ranges from columns two lines to Pie Bar era stock so phase many, many different kinds off people chats out there. All right, so in this case, let's just go here with the simple column one. There's also basically different variations off charts within the column type. Okay, let's go hit with the column is just click OK and straight away you will pop up small, though trapped like that. Okay. And then let me just enlarge it so that you can see it a little bit clearer. So this is the basic set up that Excel provides Once you launch a pivot chart, Okay, at the top left corner, you'll see that this is talking about sales volumes. So this is actually the value that we're looking at. In this case. The values that we're looking at is the sales volumes. So on the top left, it's showing sales volumes. And on the bottom left and on the right side is basically you see these little buttons here with arrow pointing down. There's basically a filter function where you can literally just filter off the type of data you want to be showing a pivot, for instance, yell if I just click on it and then you show me that there was 2015 data 2016 days, and I want to show okay. In this case, if I just click for 2016 data, it would change accordingly, just showing 2016 if I just want to see 2015. There you go. It shows 2015. Accordingly, we've honestly both or just click select all and then I click like that. Okay, same thing for region and products. It's basically works that seem okay. For instance, if I just want to smartphones on smartphones is gonna show jazz smartphones. Okay, so that's basically it is a little bit it zee. It's pretty common sense over here and comes up using the buttons. You can actually also create the filtering mechanism in your pivot table. For instance, if I just want to look at 2015 I can kick on a pivot table here, click on 2015 and that will affect the pivot table as well as the people track. So is the same logic over here you can actually do to fill the on the chart itself via these buttons, or you can actually do it on the people people itself. In his case, I think for more professional looking reports, you wouldn't really want to see these buttons showing up on the pivot charts. So what I recommend it would be a much cleaner, more professional approach would be to actually hide these buttons. So, for instance, the height all feel buttons on the chart. It looks like a typical normal Chuck right now, but what's behind the mechanics of this chart is actually the pivot table. So that's how the pivot table shot works. Okay, it's drawing off the logic from the Philly out that you have created in your people analysis table. Okay, so, for instance, if I just want to see accessories, I just came. Click your under the products. I just look at accessories and then it would just show accessories accordingly. Okay, so I prefer this is much more intuitive method. Now let's move forward into the mechanics and in a different configuration, set up off a chart. Looking click on your child, you notice at the top column there is this. People chart to section click on format, and you're basically able to select different kinds off for Mitt. This is biscuit for the outline. In this case, we're just stick to black. Not so much. Ah, over here in terms of the format is basically configuring your shape. Fill all of this and you could just play around with it and make the changes accordingly. But I think what is more applicable would be, um, let's first look at the design. There is a quick layout that you can actually just use. Excel provides two different kinds of quick layouts whereby they have different kinds of settings already preset and it's for your use. In this case, the most default layout one. There is a chap title followed by horizontal exes. But ago, exes, major grid lines and all of that you can do chat layout for, for instance, where there are data labels above each bar and some of them are a little bit more different . We have a data table at the bottom for Charlie out. Fife. Basically, if you want to see the different types off chat layouts, you can actually just mounts over the selection. And it would show a preview of how you look like. So if you see any that you particularly prefer, you can actually just click on it and you have it. And in terms off the charts stalls north is on the right side. Motto is the right side off the ribbon. You can see that they are all a lot off preset stalls that excel provides you for your people, Chuck. Okay, you want to see the full suite of them, just click on the drop down list like that, and they're all here. Okay. So usually this is a quick and easy approach to create a more professional looking shots, using some of excels presets styles that they provide. All right, in this case, nothing as you get more queen that with pivot charts, usually you want to create a one particular stall, all right, so create your own customized reflects style look and fill. Visual analytics will be covering that in the later part of this series. I think for now, let's just stick to some of the basic presets stalls that Excel provides. And then we just go a hit. So basically, that's what you have for chart styles. Let's look at this black one here, for instance. If you wanna at different chart elements, for instance, right now, what's lacking is a chop title, and you want to add a chart title hold you treat the chart table. Just click on a chart element on your left and it have a drop down list off all the various elements you have. So, for instance, if I want to clear a chart title, I'll just click above chart, and then I can call this sales breakdown by region across put ducks and then I'll just underlying it. There you go. Okay, so I just created a chuck title like that and comes off making the respective changes. See, for instance, this is the legend. I would want to configure my legend. I just bring it more towards the top, just opens up the format legend. And then now it's doing on the right side. I prefer to be on the top site, so I just click on top and then close it. There you go. So it's now on the top. If you want to change your phone sizes, and all of that can just make the changes over here. Um, so I will catch more on those details later on as we create customized looking charts. Now, moving forward, we can show you about the different chart types. So if I want to create a line graph, just get on line graph And there you go. It now becomes a lang graph. OK, if you want a treaty looking kind of column, you can actually create it like that. Okay, treaty looking column. Or you can do different kinds of variations like that. And then it's all in different styles. So basically, just play around with these functions, change chart types, and then you can actually create different looking chart views, and it's all up to your own user preference. OK, I usually go with the column and the bar because it's very clean, very neat, and it basically conveys the same message as the other chance. So I guess from a more professional point of view, if you want to create an impressive looking reports, I think 1.2 known as well is that you want to create very clear reports so you can choose a lot of very fancy for looking charts. But I think at the end of days it's also crucial to select chart that demonstrates the clarity of the message. Okay, so that's about it for this chapter, I hope that has been helpful. Please give you a glimpse off what people chats can do. It depends on the kind of analysis that you have firstly, create that with your PVA tables and then, from there people tables. If you want to create different looking charts, you could just create away create them. If you want to make changes, you can actually just make changes on your people tables, actually, so that's about that. I will be covering more details in the subsequent series. I'll see you in the next chapter, alright, CIA 23. Professional Series - Building a Pivot Chart: Hey, welcome back to the ultimate master cause for Excel Dash, What anesthetics today? Where? No, I checked the two off the professional. Siri's building A people, Chuck. Okay, so before we go in, just a quick recap on what we've covered in Chapter one. We learn about the various elements off people chat in chapter one in terms off the for men , in terms of the layout, in terms of various patterns and elements you can put inside your people. All right, so in this chapter, we gonna go through a step by step process how exactly, to build a people child. And what do you need to take into consideration and comes off the step by step process before you go here to build your child? All right, so we don't do that style right in. Okay, so in front of me is basically the Excel spreadsheet that we've been looking at for the past few sessions. Looking at this set of data, you know, it's huge, but you wanna quickly civil exactly what you need or what your one in order to express your child. So let's give an example. Let's say you want to make a comparison off the products that was sold in this two years 2015 to 2016. And how would that revenues looking like from 2015 to 2016? Okay, so it's basically a comparison analysis on the various products you have in terms of your data set. So let's think logically. How are you going to show that? What is the best way? Do you want to do a line graph? Do you want to do a pie chart? Do you want to do like a classic kind of data set? We don't plots and on all that kind of stuff. Remember that in terms of creating a pivot chart, the most important thing in general chatting is that you won. It would be very clear, and people can get a sense of what exactly you want express. That is the most clear way for the audience to receive the message. So in this case, let's go about creating the pivot table first before I show you what is the clearest way to express. Okay, so let's go ahead to quickly create the pivot table for us. Select the data set and then press control. It's like the hold it out said in sudden pivot table and this pop up appear likely, okay? And then zooming right in. You want to be showing the products across two years? Okay, so just drag products on the rose, drink your in the columns, and then correct the revenues into values. Okay? You've got the two years off revenues. He really over here and let me just remove the decimal places. Okay? So let's let's just remove the grand. Although it doesn't make sense adding up to we want to see a side by side comparison between the two years. Okay, so we can see that, you know, numerically in this there are set for centuries 2016. His job was 25th in, and laptops have dropped. But from a table point of view, you can exactly see the magnitude off the difference. So that's why you bring in a child. And in order to assure the difference of a child, let's say we create a chart by a line graph. Let's say we go here creating a people chart, and we use a line graph instead. Where he looks something like that, it doesn't really look that clear in terms of a side by side comparison between 2015 and 2016. So why not experiment with a pipe shot? You know, Doesn't exact solve it of a bar graph. Yeah, maybe it does. The bar graph, perhaps Xterra, um event paragraph. Not exactly. Scatter plot now? Not exactly. So I would see in terms off this side by side comparison. Example that we have probably a bar graph would really bring across that requirement in terms off how you want express. So there's two different kinds of bar graphs that you want to probably explore. I'd a horizontal bar graph or like a column kind of paragraph. Okay, I think for the purpose of this demonstration, that's go ahead with a column based one. And then let's click. OK, pull out the charge, you half young, and it gives you a sense from a young your comparison. How exactly is 2015 and 2016 but it doesn't look exactly very professional. The moment you create a picture like that, you need to do some kind of formatting in order to show it like a professional looking child. Okay, so step one, usually what I do is our right click all these bottoms and then I'll hide it. Next thing I would want to stall the chart based on the kind of theme that my presentation is like. So the quickest way to do it is to explore the preset stalls that Excel has already been built in for you. That is the fasters. So the various styles that excel has really building you can go here to export of beer styles that that they have. Alternatively, you can actually also create your own style, which I will go into that later. For instance, the legend. The legend is place on the site, and perhaps you wanna have it on the common state. So what do you do? You click on chat element on the left hand side of the screen, take on it and then click on legend and then take a talk and then you bring it back up. Let's say you want to add like, a chart title, take on shot gunshot title and then click a buff. And then you can you can type in exactly hole. You want to name this so you're on your percentage comparison off. Put on revenue. Okay, You've got that there, and then you just want to adjust the fund, bringing slightly smaller. You have that something like that. And what about the data labels? You can also bring it, you know, at the top instead. So it's clear, and it's likewise for these other one. You can bring it to the top, so that's about it. You can do all this customization on how you want to go about for mating your child, even for access. You can change this. You know there's so many zeros you want to minimize it. You can do a left leg click on former Texas, and then you can change the display units, toe millions so that you remove the zeros is Nita. And then it becomes like that. So it's very intrigued if what Excel has to offer, look at how we have transformed from the raw data set into exactly what we want to create. Here we go step to to create the pivot table and then step tree to create a people chart so numerically it's not easy to express what you want to express in the table. Buy them. From a visual standpoint, having the people chat is so much easier and so much clearer for the audience to digest information. All right, so I hope you know, this chapter has been useful. And if there's any questions, feel free to replay the chapter. Okay, so we'll see you in the next one. 24. Professional Series - Formatting Pivot Charts: Hi. Good morning, everyone. Welcome back to the professional. Siri's were No, it trapped the three off the series. And in this chapter will be learning how to customize the format off your people chance how to synchronize the over for men with your current presentation layout. So that is in sync with your overall preservation deck. Okay, so there's a lot jam packed in this chapter. We gonna be learning as well. What are the key? Important tips key. Important elements to take off when you want to make your chart new professional. Okay, so let's dive right in right now. What you're looking at is the people table that we have drawing off entire data set that we have been working on this well, so at this pivot table, first you want to create your people trapped inside recommended charts and just click one of the people chat your half year so I would just go to the history. Graham say your overall presentation layout is in green. All right, you're working for a company to the overall brand that has a great home to that. Now, if your company's overall brand and layout is really green color that perhaps you might wanna synchronize chart to look greyish as well. Now first thing, let's quickly just remove the field buttons because usually excel will have this feel buttons by default showing. So just click on film buttons and have that removed. Now you want to create a grave permit to be in sync with the overall style, so click on format area. Click on solid straight away. You can select the colors here. You want to go for a duck, Agree you can go for a doctor, Agree. Go for light. Agree you can go for, like the grease. Well, okay, I'll just go for a light. Agree. I want to have a nice touch to that. So it on a little bit of ingredient. I'll do some of the preset ingredients so that you know it's a little bit light at the top and then as a gold lower. It's a little bit doctor here, so just create toned this a little bit darker, a little bit darker. Yeah, so there's a kind of agreed informant. Now, next thing is the fun color, changing it from great more back. So take on the over chat for collecting on deck. Now take note that you can also synchronize the font in your chart by just click on the overall picture. You can also do that by, you know, having a customized for men on different aspects of the chat. Like, for instance, the labels Maybe I want on Boy, Maybe I want to downsize the labels. The legend. Sorry. And I want to bring the legends on top side. So just go to design. Go to chart Ellemann Goto legend and bring it to the top. So now it's looking at the top. I also wanna on bowl the site access, so just click control be on your computer or you can just click on the B button here. Okay, let's say that's too many zeros here as well. I want to get rid of the zeros. Very simple is click on form in excess display units. Just goes created millets that's done there. Okay, now you can actually make it look even more professional by having the data labels showing at a top so that it's clear what is the exact sales volumes you're seeing for 2015 verses? 2016. So just click on design, click on chart element. Click on their labels and click how you would want a short data labels. No, I usually go for outside end, so it's very clear just showing where the top, how the numbers are looking at you can also make it larger so that it's clear now. One thing about having a professional looking chart is that you want the information to be clear, so very off that also incorporate chat title. So then it's very clear what this chart is saying, right? So go design Goto chart Ellemann Go to Chuck title and just have it above chart. Okay, Say you would want to show the sales volumes Comparison leapt hops vs smartphones. Okay, and I just want Did lead reduce the size so that it is looking a little bit cleaner. It's just looking a bit bigger like that, even for the minor excess lines. You can also change it to look perhaps black, and then you want to make it a little bit transparent. Maybe nine. And also it changed a dash to be dotted lines. Yeah, okay, Even for the colors of your your column bars, you can also change it. Let's say I want to do reverses Black right, um can just go on rate and you can just change it to revisit blue. Maybe Boo blue. Yeah. There you go. Even for the gaps here, candles will change it. Uh, form it. There are Siri's and syriza overlap. Just put zero. There you go. It's in line with one another. No, if you want to just add a bit of fancy filled to that. What did it feel instead of doing a solid fill? You want to do a great and Phil, That's what you can do as well. So I like to use, you know, sometimes I just go for the linear, and then I will change it. Let's say I changed the different horns off. Just remove this. Bring it down. Changes the slightly lighter tone. There you go. And even so, different styles of rate as well. Lighter. You could do it. You could make some. Mitch, Your kid called doctor's bill? Yeah, same thing for the blue one. You can do that as well. Maybe just go with you can start with a preset, preset blue dark blue and then just told it. Don't. Accordingly. It is good for do do you hear? It starts from the top. Change this to Doc Abu and this faded double that. Yeah. Okay, so all in all, it is very simple you want? If you want to just underline this, it's also very easy on the line and it becomes professional looking there. So how long did that take that? Take only a few minutes and you can customize the format and style off your chart to look very professional Scheme of your presentation. That is not a great but maybe in light blue. You can also change that. Um, but a solid, changed it toe light blue version can even get greed into that and change the preset ingredient from agree toe like lighter blue. You know, like a blue like debt that you go. That is perhaps more in line more in sync with a style that is off, like the white dish blue form it. And that's pretty much about that. You know what other key elements of a chart? The phone is very important that colors are very important as well as the overall message yourself. People chat nail that best express the information that you want to be sharing. So in this case, we're comparing the sales volumes off laptops smartphones for 2015 2016. So just a simple his the grand chap form, it would be able to serve this purpose. Okay, well, that's it for this chapter. I hope that it's been very helpful. Let's quickly wrap this up and move onto the next one. 25. Professional Series - Moving Pivot Charts : Hi. Good morning, everyone. Welcome back to the professional. Siri's were No it Chapter four. And in this trap that we're gonna be learning how to move those people charts. We've created an excel on how to move them into PowerPoint. Okay, so this is gonna be one step closer to what we want to achieve. And that is the lunch. How to create your ideal excel dashboards. Okay, so in this chapter, you also be learning how the resize your people charts and synchronize the changes you make in your pivot table over your people shots. Okay, so let's quickly dive right back into excel. And this is a recap what we did in the previous session, we learn how to customize the formatting off your people charts. Now, what we want to do it now is to move it over to Paul Point. So click on your people, child press control and see that is the copy. And go to Paul Point and press Cointreau V. Okay. So you would have really just copy it. Your your people shot into PowerPoint. Now, notice that the size off what you've copied it is a little bit smaller. So in order to resize. It is very simple. Just click on your people chat, pick on the corners of it and just drag to the ideal size you want. Have it. So I wanna have it just to fit this picture and just drag and drop, and that would that would complete the re sizing. Okay, Now notice that the fund is a little bit smaller than than what you would want. So you have to increase the fun size as well. So let me just push it upto for 16. And there we go. We've just moved the PVA shot over from Excel over the pole point No. Is this in sync? Is this in sync from excel to PowerPoint? The answer is yes. Okay. So if your boss suddenly speaks to you and say that you know, instead of just looking at a comparison between laptops and smartphones, you want to be looking at the whole overview of your antipodean Venturi's. Well, in this case, we've got four products here. We want to see all four port ups and have a visual comparison. Just click, OK and go back to your Paul point. You would see that it's in sync. It changes right away. Okay, so you can actually do this with different kinds of charts. You can mix and match the different charts You can, in fact, change the layout as well can change it to a different style. If you want to change the style, you can also do that and same thing. Copy. Paste it into excel, paste it into excel, drag and drop it. Change the phone size. Maybe just aerial. Sixth in. There we go. It's there. And if you just want to show, maybe laptops, smartphones and tablets with pickle accessories and there we go. Accessories is going Okay, so that's really in Tweedy. If you can create slights after slights and you have just created a presentation that based on the different charts created from Excel Okay, so that's a rep for today's chapter. See you in the way 26. Professional Series - Introducing how Slicers work: Hi. Good morning, everybody. Welcome back to the professional, Siri's. But now that trump the five off the Siri's and have true the Siri's. Now this chapter chapter fired is very important because we're gonna be patching on slices . Slices are very, very, very crucial element to understand when you want to be building and excel dashboards. Okay, effectively, think of them as the linkage between your various pivot tables. You can use them as a smart filter to filled out exactly why you want to be showing on your desk. OK, so I'm gonna be showing you an example of my dashboard and how I'm using slices toe built the dashboard as well as to show you how you can even customize your own slices to your own styles and preferences, as well as how the slices are connected to multiple pivots and multiple tables in chance. So let's could be going to excel. This is the dashboard that I have created, and on the left and site, you can see that there are all these taps, your own regions, countries, products, periods, gender. These are actually all slices. And how does it work? It works like a filter Now, if you click on any of them, let's say I took on Asia and United Kingdom the dashboard. We just changed automatically. And if I worked my way through multiple slices, the data would change accordingly. Okay. You notice that the slices are all interlinked together as well as the dashboard. Okay, just let me quickly reset dashboard, and I'm gonna be showing you how is Excel Slices work. So to begin, it actually starts from your table. Your data table data. You quickly create a pivot table, and you can also create a people chat at the same time. And you want to insert your slicer? Not How do you do that? You click on insert gone slicer, and there you go. You can select the kind of slice of that you want. So let's say I want to be selecting a filter on country. Okay? Yeah, country. There we go. We got a slice of on country and say, for instance, I want to select all the countries I have the data showing up from all the countries. If I want to select just perhaps the 1st 4 countries, I get the derived from the force for countries and notice that it's update that in both the pivot table and the people. So there's basically how the slices work now. Earlier I mentioned as well, you can create multiple slices in order to do so, you need to create multiple Cuba tables and multiple fewer chats. So no one me just quickly move this pivot table over here so it's easier to see. And then maybe I would want to sort by rank, maybe not rang levels, but rank. I want to see what which relationship managers from which rings are bringing in the sales revenues. So maybe that would be interesting as well. So let me create in people chat years will click, insert and inside a truck. Yep. Okay. I'm gonna just take away all the people the buttons, and create one of use one of the simple templates here. Okay, just millions as well there. No. All the steps on how to configure your pivot table people chatter in the previous video. So not to worry. If you didn't really catch them, you can just go back to the previous tables. Are previous chapters. Did I leave those outside in? I was at a chart titles here above Chuck. So that's called Is, um, sales revenue by rings. Okay. And this is only showing data for let's keep it simple. That's just when you show the enough for 2016. Okay. 2016 2016. This is just the it up for 2016. Is Bill okay? Yeah. There we go. Okay. So you've got a PVA to people's no. And two people charts. Um, this slicer, if you want to connect it to all of them, you just click on options, take on report connections and notice. They will show that it's communal connected to people. Table one. You want to connect it to the second people as well? They're So if you said that, all of them boats, I think there's a bit of overlapping here, but yeah, I think there's a bit of overlapping in exhale, but let me just quickly fix that. Yeah, Okay. Uh, yeah. If I click on all it gets updated. Yeah, If I take on some of them, it gets perfected as well. So, in a nutshell, this is Holly works right. You create a slice of and it can be used to filter through your different multiple pivots, pivot tables and pivot chance that you have. So essentially, if we go back to the vest, what that I have built. It's actually a combination off. 123455 people charts and a combination of different slices. So that's exactly how you build the Excel dashboards. Okay, lastly, I'm gonna show you how you can customize your own license before we go customizing your own slices. There are actually some preset filters you can use here. It's not a really white selection you that Excel provides for you, but it's usually good enough. If you want to customize and build your own slicer on the right and site, you can do position size properties, often it. So if you want to increase that high, the with. If you want to create the number of columns instead of one column, you want to do three columns. You can do it like that as well. You can extend it so that it's clear the buttons. You can actually also increase the size of them. You want bigger buttons, you on smaller buttons. You can increase the whip as well. Not you can also increase the aspect. Racial and skill, the height and all of that. Okay, so there's many you many ways how you can configure your slices. Souls will not show. That's about it. That's it for this session and I'll see you in the next chapter CIA. 27. Professional Series - Interconnected Pivots: Hi. Good morning, everybody. Welcome to check the six off the professional Siri's. We're now at this, you know, slightly possibly know Junction on the professional series. Quite a lot on people, tables, people, charts, slices as well. In this chapter on interconnected PGAs, we're gonna be stringing all that would learn together. We're gonna be showing you how to start building on dashboards. What is the crucial information to show on your dashboard? We're gonna be deciding the pop summary information to show I'll be walking you through how ? I did that for mine as well. We're gonna be looking at how slices can be used to connect all the people tables and people chaps together. Is that the line to implement off your people's fit? The right team and the professional style of your dashboards? There's a lot to cover in this chapter. That's dive right into exile. No, it would be best if you can also create data said as well as start building your own dashboard alongside this course. Okay? I mean, my data set is set off sales revenue from this various products the sales managers have been selling for the past years in 2015 and 2016. I'm thinking on the dashboard that could really capture the key information. And what is the top summary Information your dashboard when it show. And for mine, I want to be showing cells breakdown by regions, by countries, by products, by minds, by gender as well as by locations. In fact, I think I was gonna have a filter on the age group and the ranks. So therefore, I decided to go here to build my dashboard based on this key filters. Okay, So now, once you've really created this set of data this side of peoples and this sort of framework for your own dashboard, what you need to do then is to frame up the charts, and how do you want it to look like? So for me, I decided toe created simple just a rectangular dashboard version which showcases the revenue distribution across the regional sales teams, revenue byproducts, regions, countries, and over of new trend across a year breakdown by the various months. Okay, why don't you get the from what going? You need to connect the pivots altogether. And that's what we did in the previous chapter using slices. So we kept on how you want to set slices quick inside, please slicer. And then once you've got your slices out once you got your slices out, remember to go toe options Goto report connections and then connect all the various p vets together. That's how you connect your slices so that it becomes in the connected like my dashboard here. Okay, so this is just an example. It gives you an idea how to create your own dash bots. Why don't you created the very slices? You can just put them by the sight like how it did them. And then once they're all interconnected, let me just show you options report connections and you can see that each of the slices are all connected with all the pivots. You can test it out. You can also, I can show you later how to create this reset dashboard, uh, mackerel. But basically, once your slices are all in place, it becomes really very quick and useful hole to use the dash. What? You just select the filter you want, and that's what just automatically change accordingly. So that becomes very useful. You can build slights from this you can build your own reports from here. See, I just generated this view. Let's say I want to generate a different view. I want to reset my dashboard and export Paul point again. And there we go. I can copy and paste that into my previous whole point, and it becomes super super useful. Once you know who has the skill of yours, you can create multiple dash what you can create a dashboard to help you produce most like necks can create in directive dashboard for your own personal use all for your user. What? This is holly sweetness and that's it. This is wrecked for this chapter will be going on into the next chapter as we discover more about building dash boats. 28. Professional Series - Professional Tables/Charts: Hello, everybody. Welcome back to the ultimate master Cause Off Excel Dishwater Analytics, Chapter seven off the professional series. Today we're gonna be pigeon You how to really make everything look more professional. So your cables, your chance we're gonna be showing you what exactly? To avoid what exactly? Techno Off to create more professional effect in orders. Vigils. Okay, so this is very important. Don't miss this chapter. This chapter is crucial because we'll be showing you What exactly do you need to do for us ? Would be to optimize your people and charter lineman's not create a professional effect as well as to keep this Ovaltine and style uniforms. Make sure the horns are allying. Make sure the phones are all uniforms. Also, remember to present your information in a concise, clear and intuitive manner. OK, so that's quickly dive right into the excel. I'm gonna be using my dashboard as a demonstration on what are the things to take note off ? So the first would be a lineman now a lineman in the foreign alignment in the tables, alignment in the charts. All of this is very important to create something that you want to make. It look professional. Okay, so we'll say. For instance, my phones are out of place instead of area. But Cal agree I have also Tom O hara or different kinds off funds all around, and it just looks messy. It looks clumsy. It doesn't look professional. So therefore, remember, whenever you're creating a presentation or PowerPoint or excel dashboard or anything, always make sure that the phones are all the same. Okay, the moment you decide on one phone, keep it the same as it is. So, for instance, I'm just gonna put all as Ariel 16. Okay, Same thing for the color. Like if you have white color Is your hate a phone for your charts? Stick the white. All right. Don't go blue on some black on some and white on the other. Alright, just keep everything uniform across. This goes for this one sizes as well, trying not to use different phone sizes. And this is clearly a different section off the entire presentation. For instance, like he does hate us. Why? Or titles. You can use different fund sizes. You can obviously use something larger, but for the most part of your presentation, try to keep it a same uniform one size so that it looks very neat, right? That the whole idea is that a Zlata xyz nita, it looks professional, and the standard of quality is very high. Okay, now we've covered a Lyman of covered bonds sizes. We also want to talk about colors now, choosing the exact colors off how you want to create your PowerPoint slides, you create your people tables or your chance or any visuals. Important thing is to use colors that I'm lying with one another. Know what I mean by colors that are in line with one another. It's if you're gonna be creating something that is generally blue color, then try to use different shapes of blue to create that uniforms style, right. If your presentation style is generally gonna be something off green color, then do not sound introduced yellow or bright rate because that totally distorts the the overall style off your look and feel off your presentation. So, for my case, what I was trying to create as my presentation style waas the whole idea off black and different shades of gray as the overall presentation style. So for phone wise, in order to create something that blends in in the midst of all degree and black would be to use a white phone, if not a slightly greyish fund that were very nice into the whole of all. Tempted in terms of the graphical colors. I use something that's a bit brighter so as to create a very clear and contrasting look out off the seemingly dowel graphs because I chose the color of grey as my overall team. So if I want if I want to create something that is more striking in the graphs in the charts, then I would use generally brighter colors like bright blue. Bright green generally tried to call in, trust it back as a greedy in into the darker side. So notice that the blues are a greedy in more that will lean towards the darker side of blue, the greens as well, even for the rich it will generally lean, keep towards the darker side of rich as it goes from one end to the other. That is to really drive in with the overall theme that I have, so that would be in sync with the overall style and therefore it creates a very professional look, So it's a little bit off trying error to deliver something that looks very professional. Obviously, if you can capture the essence off consistency a lineman as well as color scheme, I'm sure you will definitely cool much further in terms of creating professional. Okay, that's regret for this session. I'll see you in 29. Professional Series - Data Spark Lines: Hello, everybody. Welcome back to the Excel Dashboards, Analytics scores when I tripped that gate off our professional Siri's. And today we're gonna cover the last spark lines. Okay. Not many of you have cut off their spot lines. Perhaps when you're talking about Excel or Excel dashboards. But believe it or not, there are spotlights. Give me something pretty fancy to ed into your excel dashboards. Give it a more professional, Cassie. Look inside. Okay, so in this chapter, I'm gonna be running through What exactly are the rest back lines, how to use them And how to incorporate this? There are spark lines in your excel dashboards and finally help customize and form your own clear a spot times. Okay, so let's dive right into excel. And here's a set of data that we have been working on now in orderto create your data spot lines, let's quickly first create a pivot table inset PVA. That's gold. Okay, once you've got your people table up, say, for instance, we want toe take a look at all the favors products. So, in the respective years what? Sorry. And let's just draw the sales volumes, OK? And let me just go Bill for mating here. Okay, so we've got accessories, laptops, smartphones and tablets and this other the various ah, years here. 2017 28 and 1920 15 and 2016. Look, let me just pulled 2015 here and 2016 here. Okay, so it looks It looks in order. Yeah. 2015 2016 2017 2018 and 2019. Now we want you inside. Our data spotlights. How would we do that? All right. Very simple. Click on insert. Click on. Go to the column. On spot lines, click a line of your lying spat line. You've column spot line and your win loss spot line. Right. These are basically all many charts that are gonna fit within the cell. So let me just show you an example. I draw the data from 2015 all the way to 2019 and I click. Ok, there we go. The spot line for accessories. So I just scrolled out when Mira across for laptops, smartphones and tablets. And I have my smart light, my spotlights. Okay. So you can copy and paste this in tow. Existing dashboard is really very simple. You can also customize the for men off that Okay, hold. You customize the format. Very simple. Go to design and excel. You have, I would say, a fast election off various color schemes you can choose from. You can choose orange you can choose, Ray, you can just brown. It's up to you. I would just go for the simple blue one, and I like to at Marcus. So it's clearer where exactly how many data points do we have? So in this case, you see that we have five years 2015 2016 2017 2018 and 2019 We therefore half five data points there, and it's showing as the Marcus now, you can actually customize all of this Gonna design go to market color. You can change it. Maybe you wondered Black in color spotlighting column. Maybe you choose rate in color and it looks like that. Okay, you can customize just the way you want it. Okay, Although we look at column spot lines if you want to change the calm a spot lines, you can do it as well. Yeah. So this is in Rick color version. You can do negative points as rate and positive points as green, but in this case, everything is showing. Here is positive numbers, so that basically all showing a screen. Yeah, but what is the purpose of spot lines? Basically, it's basically to show a trend, okay? And whether use the column, spot lines, all the lines, but lines. It's very simple. You want to show a trend across your data points? In this case, it's best represent that whether is it a year on year basis? Or we can actually do a month on month basis version as well. Let me show you instead of years, let's go down to the months. Okay. Well, months is a little bit small. Uh, months. Okay. Okay. Maybe just the fuzz. Six first 10 months. Okay, maybe lesson that the screen is a little bit more. I just want to show you can do it with minds as well. And I want to create instead off products. Maybe I goal. And she's my relationship manager. And I want to see a trend off my relationship managers sales revenues across this entire period. Okay, maybe I just want to look at the you're off 20 19. Okay, so that's I just look at 29 10 and same thing. Go to insert, go to line and pull out. The data are from here. Okay? And there we go is drag and drop it down and you have it there. Ok, let me just adjust and show you accordingly. So 20 night, then Generally February, March, April, May, June, July. This are all the trends off the various relationship managers. Okay, Very simple. Very easy. This session has been useful for you. Any questions? Just feel free. Let me know. Or you can just rewatched the video as well. Believe this would be very helpful. Okay, we'll see you in the next chapter, soothe. 30. Professional Series - Effective Visual Analytics: Hello, everybody. Welcome back to the ultimate master course on exile. Dashboard analytics were now it Chapter nine off the professional Siri's. We're almost there, coming to the end of this and distract the hearing. The nine will be about how you can make your data analysis look, give a more impressive make it really stand out and look visually very appealing and effective for your audience. Okay, so that's quite a few points will be covering in this chapter today. Now, firstly will be showing you how to use the right charts or diagrams to create an effective visual summary, as well as telling you what are the most common, most important charts and graphs that you need to learn to become a professional in the Excel dashboards space and finally also show you how you some data labeling techniques and some highlighting techniques that you can use to make your visual analytics look much more effective. So all of this jam packed in this chapter right now, let's check it out in Excel. So I've excel open right now. I just want to quickly start by showing you under the insert tab, recommend that charts all chats. What are all the charts and diagrams that excel office. So we've got a really huge spectrum here, starting from the part we've got columns, lines, pie bar charts, area graph. Even the skit applaud many, many different funky ones as well. We've got even the radar Cremin, sunburst as well. So many different kinds of charts that you can look to create or explore in your own dashboards and your own pivot charts. Okay, I find that the most useful ones are really the column. All the bar effectively, they're the same thing. One is for tickle, whereas the other one is horizontal. All the line chart lang graph, which is great for your time series analysis, which shows the trend off the data that you're looking at the pie chart as well as really useful because it basically just shows you a breakdown off the various categories in sight . Now, in this case, we've got category one to treat four in this example, and you can see that the lighter orange and a blue one is the majority of this process. Perhaps the three most useful chance at the top most So I think so, long as you know how to deal with these charts. You can actually explore the rest yourself, but in this example today I'm gonna be showing you exactly how to build them. So starting with the pie chart, let's quickly show you how it's done on my left Inside here I have a pivot table. Really create that, growing off the data from our sales revenue summary and we're looking at a breakdown on the sales volumes across the various products. Now I want to create a pie chart. Just go to insert, click on the icon, insert pie, although not shot, and just click on it so this very quickly should sell your paycheck. Now you wanna label it so let's firstly take away to fill buttons because that usually gets in the way design at in a chuck title at the top, we called as sales volumes breakdown across products okay there, and I would also want toe put the legend on top, and once you're done, you can see the breakdown that the blue smartphones is really holding a majority of it. But this doesn't really give you a very effective analysis yet until you get in the numeric . So now the new Americans come about when you get in your data labels. And let's say I just put a data call out labels on the right side. Okay, so it's very clear shows you exactly what all the percentages you have smartphones calling out 57% off your total products. We've got laptops that's holding the lease there at 12% but there's basically gives you the idea that if you want to create an analysis using ah, chart that shows the information very clearly. Key thing is to truth the right shot as well as in the chart title and just organized the child a little. And in the data labels, and it looks very clean now. Of course, we can customize the format later to it to make it look much more professional looking. But that's not the point here. Today, on a point is really to show you that it's important to choose the right shot for the right purpose. Next, let's go over to the bar chart Now. The bar chart is something that is very, very useful now. It's even probably the most use, which I think so let me just show you why is that so? Let me just pays their zone. Okay, We're looking at a comparison. For example, 28 investors, 29th in sales revenue. Okay, you've got the breakdown, Chiana. People, table accessories, laptops, smartphones, tablets. Now you want to create a bar chart? Okay. Call them all bar chart. Let's go. So we've created one here. Now, first thing, get rid off the field buttons. Let's just very quickly do a little bit off tweaking. Move the legends on the talk. Let's add in a trap title as well above the chart. We call this sales revenue comparison. 28th in facist 20 night Did Yeah. Okay. And at in hard. Let me just formally access change this two million so it looks more concise. And let me in the data labels outs, Let's say okay, did our labels outside? All right, So Yep. Oh, let me put this. May be 60 and make it look. Fintor, this x 80. Okay, now, the point that I want to drive across here is that while the column abarca is effective to show the snapshot comparison off the products that you're looking to measure, this column or bar chart is actually even more useful than what you think, because you're able to add an overly on top. Now, this overly comes by means of a secondary excess. So let me just shrink it down here for a second. I'll show you What do I mean by debt Now going back to your pivot table say, for instance, you want to do a year on your comparison as well to show what exactly is the year on year comparison here? I mean, of course, we know that tablets revenue jumped up to 10 and from two. But what exactly is a young your revenue? Their friends Let me show you how to do it. Okay, so just click on value settings here, click on show Value s and that's Do go down to difference from Go to your because you're doing a union analysis and we're looking at 2018 verses. 2019. There we go. Okay, so maybe I can just take this out and I call this the year on young difference. There we go. So I paste it and maybe I would just change this into a line graph. Could change chart type. And for the younger one as the secondary axes are put us a line graph. Okay, there we go. Now back to your original Holum graphs. You know this, that there is this ugly gap here. We want to get rid of that goto data. Siri's go to serious over that. Put 50. There we have it. Yeah. Now, the blue and the gray doesn't look very appealing. In fact, this sexist here also doesn't look appealing that scary off the decimal places. And we want to change this color as well to maybe we do, uh, Dr Do and this one we do light of All right. Okay. You get the idea there, and it's just stick this out expended, and you have your truck like that. Okay, let's bring it back here. There. Now, you probably also want to include data labels here on the secondary access. So go to chart Ellemann and let's put it at the center and click on those little labels. And you probably wanna have it showing as a contrast to highlight. What is the what is the you on your variance? Okay. So very could be going to them there. Okay. In fact, you can do the formatting even better than this. I'm just showing you an example how I'm doing it. But the whole idea here is that the column graph is exceptionally useful. I feel, because is able to have an overly off the excess. Therefore, it is so commonly used in very professional reports out there when you're in the professional working well in the financial or in the consulting space, this is something that is so commonly used. So then this skill and impress your boss is it's gonna be very, very useful. So that's basically what your bar charts can do. Now let's move over to the line graph section, and you can see that the line graph is excellent for depicting a trend. All right is very good for trend analysis. So in this example here, we've got data on sales volumes form from generate 2015 to December 2016 whole, set off 12 months times two. We've got two years, so a total of 24 data points here, and it's very clear that this is showing a downtrend in sales volumes. Of course, I mean, this is hypothetical in terms off my data, but it's very good you get the idea that the line graph is what basically works best when you're trying to show a trend analysis and for the bar chart, it works best when you're doing a snapshot comparison. And the bar chart is really very useful as well, because you can actually add on that secondary access overly, which gives a second dimension off comparison there. And finally, for the part shot, it basically just breaks down the various components in your data set. Okay, so I hope this tree graphs here, which are the most important grabs that you need to learn. We'll give you a good sense of what visual analytics is all about and how you can actually make it more effective by incorporating the right graphs for the right being on set and for the right purpose off your analysis. Okay, so that's a rep for this session. I'll see you in the next trip to see you 31. Professional Series - Alignment & Arrangements: Hello, everybody. Welcome back to the ultimate master course. We're now at the final chapter off our professional Siri's, and in this trap that we're gonna be focusing on alignment and arrangement. So we're gonna be pissing up all the pivot charts and the people tables all together to fix up the dashboard. Okay, So I'm gonna be walking you through high blitz that by step so that you can see how I align multiple people charts that we were created as well as learned how to position or arrange it to a specific location so that it looks very neat and professional. Finally, Albiol's showing you my gross configure the orientation, all alignment of the Texas well so that the fine touches are all done and it's good to go. All right, so let's quickly just dive right back into exile. Now you can see that there's many taps here in yellow and rate are all the PVA charts that have already been created. Now what's left to be done is to really just fix it on throughout temperate here. Okay, so let me just very quickly start with the first people. Now. The first pivot is a breakdown on put up classes. We've got tablets, laptops, smartphones and accessories. We're gonna be bringing that all into the Excel dashboards. Okay, so very simple. Just click on your people chat, Quantrill. See on your computer click on the Excel Dashboards and Peace That insight. Okay, in this case, I already pre med. Where exactly do I wanna put those pivots? Insights? So let me start with the products. I'll just paste it here. Control of the on your keyboard and you would pace it there. Okay. Knicks 2nd 1 is on regions PVA breakdown by regions. Click on the pivot table. Same thing. Control C on your keyboard. Move over to the dashboard. Take on the area that you wanna have it control V and paste it there. All right, let's move onto the next one on countries, people by countries, Click on it. This is a reader Spider Graham, Pick on it and then moved over to the Excel dashboards. Take on countries area and then just paste it there. All right, now move to the final. The for fun. This is a breakdown off credit. Alice is by months. Pick on it. Move over to the Excel dashboards Month among trend Paste it there. And finally we have the breakdown by the sales managers. This is Ah, larger people break down many sales managers. Therefore, it looks pretty long. Gator, just click on it. Controls C on your keyboard. Move over to the excel dashboards. Click on a space that you can find and just place it there. Okay, so it looks like it's all a really you peace that inside the dashboard. But you you find that it's not very relevant light. So this is the part here where we need toe align it all together. Okay, so starting from, um, that said you didn't really do a good job in piecing it, and it's sort of like all over the place what you need to do very quickly. First, pull down control on your keyboard, click on the objects you want a line good of format, go to a line and click pop. There you go. You have just all push it to the top. Okay, next click on this and this people and then format alike left okay, and I think it's probably easier if you can create the hate us. So let's start with this part here. I'm just gonna quickly do a much and I'm gonna for meant this as a docker Black Dr Day so that it's zero Dr Dick and I'm gonna increase the phone to Bullet. Okay? Revenue distribution across regional teams. And I'm gonna do this the same for this. Maybe I pace it up to here much and increase the phone. So in this case, the funnest calibri 16. I would change all the Calibri 16 later. Now this one here, I'm gonna do the same much and the same for this much on the same for this much. There we go. No. Remember what we recovered in the previous chapters? It's important to for all of them to have some form of symmetry. So I think this one here it's looking a bit out of Please let me just go over it again and okay. All right. Pais. Copy it. And much. The format. Contrave pay special much to form it. All right, so you've sort of got the the I think it's easier to start with. Just one zoom in if you need to, so that you get a very good alignment on it and then just aligning accordingly. Okay. Now notice that the height off the 1st 4 peoples are not exactly the same. So you probably want to just align the first people first and then use that as a benchmark toe align the rest. Okay, so let's start with aligning it to the top. Been find out. What is the height off this people? So this people's tree 0.15 that's make everything Look, 3.15 There we go. So, no, it's one step closer. And then just hold on, chief, on your keyboard so that your only adjusting the with off your child and not the other stuff. Okay, just a wave Hold down shift on your keyboard. So it's very, very organized. Okay. Schiff, Cuba. There. So is looking very professional now, even for this, you want align it with this top people so that it's exactly the same press control. So, like this to people. Keep format a line left. Okay. And this last bit here, just oil chief on your keyboard, and she if it up a little Yeah, Okay. And there we go. There. So you zoom in. Yeah. Now it's as simple as that? How long did that take, Millie? Only about 111 minute, one of two minutes. Maybe 1 to 2 minutes, tops. So this is in a nutshell. What it looks like to align all the pivots altogether and have it arranged nicely on your dashboard. So it looks very professional now that that last step is to have it important over to power points. So just copy and paste the Empire area off your dashboard. Hold on control. See on your dashboard. Alternate tapped into the pool point shot and just paste it as a picture. They're based it now. You need to resize it so that it fits exactly your PowerPoint template. Just do a re Seiss. And then at this point, you're took form it, allying middle and allying center. So that will be exactly the center off your PowerPoint. Okay, so at this stage is only just one last step in order to check that, it looks well and good. Click on the F five button and see that it should look exactly the way you want it to look like. All right, so this is it. This is the rep for the entire professional Siri's. I hope that you let a thing or two on a lineman and arrangements, and you can see that this is just basically the final touches off Building Your Excel dish . What's first? Create the pivot school hit with the people tables, the people charts and then do you customize for making? And once all that is done, just think of how you want to arrange it and have it showcased on your dashboard. And when that is already the last part is, then align everything so they looked perfectly polished and professional. Okay, so this is the rep for the professional Siri's. I'll be looking forward to seeing you in my Super Pro Series that will be transforming you into an absolute expert. Stick around, and I'll see you soon. 32. SUPER PRO Series - 500% Faster with Shortcuts: Hello, everybody. Welcome back to the ultimate master course for Excel Dashboards. Analytics. Hope that your learning journey rivers have been a very smooth one so far. Now, before we kick off the Super Pro Series, that's just quickly do a recap what we've covered so far. Well, so we did the beginner, Siri's. We did an intermediate as well as the professional Siri's all right. So in the beginner series, we gave you a good introduction on how to use Excel. What are some of the more basic functions and how to basically familiarize yourself with the Excel interface? All right. And then we move over to the intermediate Siri's, where we looked at data a little bit more closer. So we learn how to actually analyze and built pivot tables from your data set and also learn how to use some conditional formatting and filtering and sorting through. Better analyze your data, okay, And then when we move over to the professional, Siri's things started to get a little bit more visual. We started learning how to build people charts and how to synchronize your people, chats altogether and in fact built multiple people chats and bring them up so that it forms the base off the dash sports. Okay, so now we're gonna be moving into the Super Pro Series, where we will up your excel skills to an expert level and also show how you can become super fast, super quick in excel and turn you in absolute beast in excel. All right, we're gonna be also showing how you can build in directive dashboards using macros and learn how to ultimate some of the functions in sight. Okay, so let's get started. We've checked the one. The one Steidel is 500% faster shortcuts. Basically, we're gonna be showing you how you can become super fast on your excel hold. You can get access to old important excel. Short cuts out there. And what are some of the mass know Most useful excel shortcuts. Finally, I'm gonna do a demonstration on how I work on my excel so you can get a sense of how fast you can be. And I just want to show you how you can practice so that you can become as fast as me or even faster. All right, So before we get started in excel, I'm gonna be flashing out this list off your must know, excel shortcuts. So I've compiled a list off two pages page one and page two, and in this fool issue can see what are some of the most common shortcuts I use. And I find his shortcuts extremely useful dealing with any new set off data. Okay, so I'm gonna be doing a demonstration later as well, so you can see for yourself how fast you can be using these shortcuts. All right, So have excel open right now. That's quickly dive, right? And so imagine someone trolls you this set of data and expects you to do some useful data analysis on it. Could be your professor. It could be your boss at work. Um, So you've been true in this set of data, and you see that it's all over. The place is really messy. You've got columns that are not in line. You've got roles as well that our school washed up together. You've got funds that are all over the place. And some phones even have different colors somewhat larger than the other. And some of the cells are actually even showing missing information. Some of the cells are empty as well as some cells have also duplicates. This is a duplicate with this. We've got all kinds of problems with this instead of data. And we won in. Your boss wants you toe provide the data analysis as quickly as possible. And you wanna come with something useful. Okay, So when you're faced with this challenge, if you use your mounds can straight away tell you that it's gonna be really slow. The key is to use excel shortcuts. Now I'm gonna show you how the shortcuts I've shown you a learning. Those two lists are three. Okay, so I've created a spare, duplicate spreadsheet, so I'm gonna work on just one of them, and I'm gonna show you how it all happens. All right, So that start with this spreadsheet first, think about your mounts and just focus on the keyboard. I'm gonna just rename it first alternate hit, or are rename s short cuts data. Okay. And then once you get a whole of it, voting will see a golden or are a you straightaway formatted accordingly and copy and paste . This pays a special and you've got it all a line. And then I want to freeze the top U S. Bill goal and then I want to do a field as well, so I can see very clearly. What is the first column all about? The first column is about relationship managers. The second columns just first names. That column is last names. Five columns is just about countries. Fifth column is adjustable some regions. Six column is a 1,000,000 Female agenda seven column is just some ages and then some rang levels and rings, Some products. Some years, minds. We've got different minds here and a breakdown on minds to get over the years, we've got some sales volumes, some sales revenues and some revenues. Okay, now, I'm not sure whether there are any duplicates inside your toe ordinate am to quickly check if there are any duplicates. And yes, true enough, there are some duplicates in this friendship. Now, I also want to check if there are any blanks. And yes, I see some blanks in this spreadsheets over here. Okay, so let me quickly just get rid of those blanks, okay? Those blanks belonged to this person, Erin, and let me just quickly final what Aaron is like, OK, so Aaron is actually Chung. So let me just go back and show and we've caught. I thought so. There we go. So this Chung guys from Taiwan, his Asian in this mill that's pays that now, But Okay, let's check again if there any blanks, No blanks. So this spreadsheet is fully online already. Okay. All right, so that's it. You have no salt, the spread shit. And it's looking good. No better way Create a pivot table. Okay, we call this PVA table. I want toe. Just highlight this to spreadsheets that created and yellow So I cannot defend shit that their own yellow, And I want to bring it out. Bring the I feel no and comes of a pivot table. You've got to use your mouth, decide what to add inside. So now I know that this set of data comprised all the sales volumes and sales revenues for this sort of relationship managers. I can also do relationship many years, or I can do a breakdown products and depends on what you were looking for. That same one into a comparison between year on year, um, sales revenues off the products from 2015 to 26. Then I can just very quickly do that. That me show you. OK, so that's it. Create a pivot chart. There we go. And that's it. You have created your pubic chart. You can use one off the Excel preset templates, or you can do your own designing. Okay, so that's really how the Excel shortcuts are so useful. Basically, we we took out this mess and we transform it into a pivot table really quick. You can even at in your chat title and chat, they'll chat form. It's up to you. It's really very simple from here. Okay, No, let's delete this spirit shit or the e l. And you get rid of them. Now, if you're using the mounts and you're not a queen that we've shot cuts, it would be sourced law. Yeah, I just no idea how to do it quick without knowing the shortcuts. Yeah, After you get used to the shortcuts, you will realize that it's able to save up so much time, and it's about to do things so quick. Okay, so now how do you do the shortcuts? I'm gonna show you a breakdown version off. How I do it Okay. Very simple. When you're dealing with the data, hold down, control a on your keyboard, and you select the whole set of data. Okay, press alternate or see a so that you align the length off the cell. Okay. In this case, it's already all the line. Let me just show you example how it works again. Okay, so alternate O C. A. And the length off the cell, we'll all be aligned toe whatever is filling up the cell. Okay. You notice that some of the heights of the cells are all different as well. We've got this role 20 that the height of the cell is so much more than the rest. Use the comment. Alternate or are we? Oh, are they will fix it up. Okay, Next is the funds. You notice that some of the phones are in different colors. Many ways to fix that one way is just control. A so like everything. Press alternate hitch F c on your keyboard and then just select the black phone and everything will be black. You can still like everything again. Press control. Be on your keyboard Presby again and you're on bull. Everything okay? Nix It is the born size press alternate on your keyboard. H f s. You can see there, the data labels you got you and then just click a tent. And now everything is fun. Size 10. Okay, some of the tells you are in italics as well. Just control a so, like everything. Undo the italics. Okay, any point in time you can just alignment, press older or are a or than you'll see a tow, align the cells. Okay. Now, you notice that there are some cells here that are in the highlighted in yellow? No. In order to remove that, just select everything. Presley, Akiba, alternate. Hey, each h and put nor Phil. There you go. No feel. All right. So now you've sort of, like, tidied up the data that they don't know. You want to Just check. By the way, there are some underlines here as well. So, like the whole data control Underline. Underline. Okay, control, you toe select underlined and control you again. Toe undo the underlying. All right. Now, let's say you want to centralize this data set. Press ordinance hitch E c. And you've centralized data set. Okay, You notice that there are all he does here, right at the top. What you can do is to, as you scroll down the spreadsheet is you notice that the haters gets taken away as you scrolled on a spreadsheet. So what you can do is press OTA w f r and you freeze the top room. So when you're scrolling down the spreadsheet, you see that the heaters air still all in. Please. No. You want a boulder? Hey, does just control shift on your keyboard press the right arrow button and press control be , and you have it all bold it Okay, Next you want to just check if there's any blanks and you glance across and you see that there are actually some blank cells in the spreadsheet. Know how do you go to those blank cells? Very simple. Select the whole data set. Press F five on your keyboard. Press Olden. It s which is special. And then press your key to select blanks. Okay? And what the commando do is that you would pick off the banks and you can see that on the screen here. Okay. So very simple. You want to get rid of those blanks? So at enough filter on your spreadsheet control. Be so like the whole spread. Shit. Press alternate Hitch s f. You have better on the filter. No. Hold on the alternate button on the patio column Press control space bar to select the whole column. Okay. And as you move through the cells, if not, just press alternate down arrow on the top hater. Now, press the end button on your keyboard and press space bar to select those blanks. Okay, Now you can see that there are only these cells here that are showing us blank. Okay, No, undo the blanks. And we saw that that guy is called Aaron. So let's such Erin. And you see that some of his name's airshow incorrectly in the other cells, whereas for these cell roll 30 tree as well as Rule 42 are showing us blank. So what you can very quickly do is go back to the top, hold on the alternate button and sought by eight z and screw down press control down your keyboard and you can straight away just do a copy and paste control. See and control V. And there you have really fixed up those empty cells. Okay, press alternate again and screw down to just undo this and press alternate. Hey, h s f toe under the filter. Okay. Lastly, just want to check if there any duplicates. And I remember we talked about the cell. The data said actually has some duplicates. You can see that the 1st 5 rows here selected are duplicates with this five rows. Now, in order to remove duplicates is really simple. Select the entire data set. Press alternate e and M, which is remove duplicates. You can see it removed the big eight year showing us m Now press em on your keyboard and the remove duplicates. We know we're appear. No, it is point what you can do as you move through the options. Just press tech when your keyboard tech, tech, tech, tech, tech. And then click. OK, And there we go. We've just removed the five duplicates here. So now you've got a data set that is clean off duplicates. The formats are align. Probably the width and the height off. The cells are all in consistency, and this data set is ready for your analysis. Okay, So how do you and analyze the data set what I usually do, which is which one I find very useful is just at on a filter. Bolton H s F hold down the alternate and the aero sign to see what other unit values inside this column. So we can see that the column name is called Relationship Managers and insight. All just names, right? So we know that this call them. Call him a the names of your relationship managers. Okay, press the escape button on your keyboard to get out of that screen. Now do the same for column B, and you can see that is the same. You scroll down this all fuss names, right fuzz names and then call them. See this All last names column D is all countries column E REGIONS COLUMN F Gender age is rankings explanation of the rankings. You're put ducks years your months, months with your years, sales volumes, revenue and revenue in USD Yeah, no, You want to create your pivot table to do your slice and dice and your people table analysis know how do you do that? Just take control e press the alternate button go to insert, which is n and then click on the which is pivot table. OK, press the enter button. You know that It's very small. I want to zoom in. I can just press alternate w Q which is to zoom Know that you see that the Cuban here rescue and you can zoom No, I can just zoom into maybe 100 and 50% and it's much clearer. All right, so the people table, you can really use shortcuts here. This basically you need to use your mouth to drag and drop in what you want to analyze. So maybe I want to analyze the various regions. I want to see a breakdown off the products in the regions and I want to see the sales volumes for the year off. 2016. Okay, there. Let me just fix up the form it. You can actually also do a sort here, alternate E s s. And you can sort values by largest to smallest. Yes s. That is the smallest. And from here you can alternate N c and create a chart accordingly. Okay, so it's really, really smart this tool and just remove the fill buttons called his put up seals by regions 2016. Okay. Yeah. And there we go. Right. If you want to. Just true some of the preset Excel designs. You can choose them on the lists of preset designs. It's all up to you. Okay, so that's that's a nutshell. How you can quickly analyze a huge set of data using shortcuts. It's really quick ones. You've gotten a hang off these shortcuts, Trust me. Get your hands dirty on this. Shortcuts. You will find it extremely useful in your life whenever you're working on Excel. Okay, there's a rep for this. Trapped. I'll see you in the next one. See you. 33. SUPER PRO Series - Formatting like an Expert: Hi, everybody. Welcome back to the Super Pro Series. I hope that all of you are having a good time so far. And the learning journey has been very small. Okay, let me know. Give me any fit back. You're encountering any problems and you're learning. Now we know each of the two off the Super Pro Series. I'm gonna be teaching you how you can form it. Excel like an expert. All right, I'm gonna be demonstrating, showing you a live example. I deal with the data. I'm gonna show you how you can just follow these steps that I do. And before you know it, you become an expert. All right, So this topic, we've got some key topics we're gonna cover. We're going to show you some shortcuts. Very important shortcuts, how you can copy and peace form. It's effectively. And I'm gonna show you my routine. I do as well when I deal with data sets, expression, especially the data sets are very messy. And I'm gonna show you how it clean it up, aligning resize it in a very effective manner. I'm also gonna show you how you can really use a freeze pain or top hitter role function, which really makes everything very neat and professional, as well as the zebra stripes format technique, which really improves the read of lead off your data. All right, so it's quite a lot of cover in this chapter. I'm gonna quickly dive right into excel. No, you can see the Excel screen here. It is showing you a set of data That is very messed up. We've got formats all over the place. You've got some data here that is off a larger fund than the others. You've got some cells that are showing a different phone in different color and the alignments all over the place. Okay, Now, what we want to do here is we want to quickly tidy the form it up, make it look professional, make it looks like Okay, so a couple of ways you can do that. Let's say you're dealing the excel right now. First, probably a very easy way is to select all the data breast control A on your keyboard and then press alternate. You see the top shortcuts still appearing. Press hate each go to clear e and clear form. It's f. Okay, that's it. Straight away. You have cleared it up. Now it looks back to its road arrested. Okay. No, I'm also gonna show you a different method where you can actually just copy and paste the format across your indicted as it. Okay. In this case, if you take a look at rule number two, the format of neuroma to is actually looking pretty. Okay. Now, I want to be copying this for men from rule number two across the entire data set off 3268 rows. Okay, now, how am I gonna do that? Let me go back to rule number two. So just press control Schiff, right on your keyboard. Select the entire roll press control, and then just select the whole data set control alternate V on your keyboard and then press T All right. The tea will select form. It's so effectively. What you're doing. Is that your pasting? The former off road to over to the entire data set? Okay, so that is the first step. Now. Next you want to align everything so control aids so, like everything. Press alternate O c a. On your keyboard. Alternate or are a tow align the high end with. So now the whole data set is looking very clean and very sleep. Now the next thing you probably want to do after re sizing and aligning, which which is a very good habit to half you wanna make the hater roll look more readable, right? So you notice that the top roll is freeze, right? But assuming the top row is not freeze, And as you scroll down the Excel spreadsheet, you would notice that it's very hard to understand what exactly is calling a B, C or D all about. If you lose sight off the hater, right? So let's go back up first. That's freeze panes. Press alternate. W F freeze panes are freeze pop rule. And there you go. You have really freeze the top rule. Whenever you scroll down your Excel spreadsheet, the top row is still there, so it's very clean, very professional. You can see that whenever you're scrolling down the data spreadsheet, you still get a sense. What are the columns all about? Now you want to be also making it clear what the haters are, so you can also press control, be bullet on your keyboard and just also a line again, O C a. Allying it so it looks very much better. In fact, you can get some colors as well breast ordinate hitch each on your keyboard and then at maybe at a different color. All right, now, this is something that's very useful now, this is not a technique that that a lot of professionals, working class professionals, what they do is that the tent of flash all the tax to the left and all the numbers through the right. So I give you an example. That's a sales volumes. Revenue in revenue here shot form. These are all numbers. So what they like to do is that they like to press alternate hitch A are they flash it older the right. Okay. In fact, they can also we know that there's a numbers so they want a thunders and do the number for men with a common style to format it with thousands separator. What they can do is just press ordinate, hitch key on their keyboard. And there you go. You've turned it into, um style with the commerce. Okay, Now you want to remove that. If you want to remove decimal places you can press Alternate hitch nine. All right, that will remove the decimal places. You want to remove it completely? Ordinary hits tonight as well. Okay. No. If you and then for Texas. Well, some professionals they like toe flush it all to the left. So all the hitch e l flush it to the left, and then the data set becomes more professional looking. Okay, it's up to you. Probably up to your own personal preference. Sometimes I like to go for a cent Central Lyman as well off the night hitch A C and everything is centralized. So sometimes I like to go for this style as well. Okay, finally, I'm gonna cover the zebra stripes technique. Now, the zebra stripes technique basically is creating a sense off alternation as you move from one role to the other room. All right, so let's say from roll number tree, I want to give it a slight grayish shit. Okay, so alternate hit change. Turn it into a slight grade, and then I pieced it, and then I will just copy and peace copy rose to and rose tree. Copy it and just paste it across the entire spreadsheet. Suppress do the copy and paste form that technique we talked about earlier. Hold down control ordinate V on your keyboard and then Presti there. So now if you scroll down the spreadsheet, you can see that is very neat. Right now, that form, it's all tied it up. And between each rule, the readability, it's much better like that. Okay, so that's a wrap for this session. I will see you in the next chapter chapter Tree. See you. 34. SUPER PRO Series - The 'Must-Know' Formulas: Hello. Everybody will come back to the ultimate master course for Excel Dashboards Analytics. We're now at the three off thes Super Pro Series. I hope that it's been a great time for you so far. Chapter Tree is gonna be really fun. It's basically all about excel formulas we've put together at least off your mass. No formulas that are super important that will transform you from a beginner intermediate to a super pro. OK, so these are the 10 actually more than 10 formulas here. But it's a whole lease off formulas that we think that the most crucial formulas you need to know. OK, so gonna be covering from some count meth logic formulas to even look up reference for Miller's content in eight Max mean and even nest that formulas within formulas. So we are gonna put them all together and even come up with more complicated examples. All right, so I'm gonna show you what all these formulas can do. Now, let's quickly go into excel. All right, so we've got Excel open right now. Starting from the top, we have some count and average formula. Okay, Sold some formula is really simple. Basically what it does is that it sums up all the numbers you have in the selected columns . Okay, so this selected columns here are cell D to two D seven. Just click equals some open bracket. Select the whole set off data. There you go. The some formal is really very simple. And go to account. The count basically counts on Lee numbers within the cell selector. So, for instance, over here, I count a cell eight to a seven and a 2 to 7. All just text, right. We've got apples, oranges, parents. What? The melons, grapes and bananas days are Is that old text? So it will return a number of zero now count A. As long as the cell is not empty, it will come to sell. So in this case, see if you use count A on a two to a seven, it fetches the output off six. Okay, So what about the count? If we try toe count, be to Toby seven. If we change this to B two to B seven than this will fetch six. Right, because it's counting six cells here. That half numeric values in sight now averages really simple. It just helps you to find the average of all the numbers within those cells selected. So just click equals average and then select the number of cells. You select Selby to Toby seven and you get the average of 19. Okay, you might be wondering, how do I show what is inside of the cell? Just click on a cell press F two on your keyboard and you can tell what formula is inside. Okay, so that's it for the first section. Some count and average. Now, let's move over to the second section. We have just a slight tweets to do these. Some counter every formulas. We gotta some if count if an average if it's basically just adding, ah, logic to the formula. So you can see here some if B two to see 11 you're basically looking up. Be to to see 11 this entire set of data and you are only finding the values from C two to C 11 if it has the work fruits inside from this entire array. Okay, which is basically the selection of the right here, and we've got a few set of data that is showing us fruits. We've got this as well as this. No, If you add all this together, you get 10.4 $10.40. So a quick way to double check is You're just dragging, drop selected, all here like that on your excel screen. And you can see that for this cells that are selected the sum is actually as well pointing 10.40 OK, at the bottom, right shows you the some off the cell selected as well known. So that's it for for some if now moving on to count. If count it is the same as well. You select the array of data and you only count the number of cells that have the word fruits in sight. So in this case, were selected array off Be to Toby 11 and we've got 1234566 cells inside. They have fruits. So therefore, the output you get from this count in formulary six Okay, we've got the average if as well. Now the average. If what's the same? Very similar to this. Some ever count. If Formula select the array, it would be too to see 11 and then you just want to find the average off the price off. Only the meats. So you put in meat here, and you only find that average off this as well as this. Okay, you can see both. Um, it shows you that as well. Average is 2.95 Okay, so that's it for the sum. If can't even average it for Miller's, let's move on to the If and if Errol Formula. Now, this is very useful. This if formula is so important, we use it so commonly in all kinds of data. Analysis is so over here You've got columns A, B and C showing just a simple set of data. We've got columns, a showing, the products that you have, the items that you're half. We've got some fruits meets as well, some other fruits and you've got the price right beside that. Now you can actually just change the tax inside here. You can change it to however you like. All right, you can say be low tree and over here you can see above three there and just copy and paste it down. You have the same effect. So that is basically just a text that shows up if the logic works or if the logic doesn't work, OK, and if era logic works very similar as well. So in this case, this other set of data we have got sales, sales, numbers, units as well as the average price. In this case, we've got for the second role we've got units showing up zero. So the average price, we're unable to tabulate that because the units assuring of a zero therefore obscuring of us an error and excel so that if era logic works that if we have an era, then it should return value. So in this case, we use the if era formula and say that if there's an era, we would show insufficient invent three. So in this case, only sell. See, Aidan has an era and therefore assuring us in suffering bank, for the rest are all showing s but normal value. Now moving on, we've got the end and the all function as well the end in our works as a logic function. So let me give you an example. You want to check if costs off apples and the cost of pairs are both above $1. So you just put in the end function and see to rejoice the cause of the apples. It is greater than one in Paris C three, it is grated in one. If both of these conditions are met, then it will show as true Okay, if both above $1 then he was us. True, if not, is gonna show us false. All right, So in this case, both Paris and apples are having a price of above $1. Therefore, this is showing its true now. What if we are just the values off Apple? So let's say we say Apple's only 90 cents, then it shows us false. Likewise, if pairs, maybe we give it is your point. It 80 cents is sure it's false. Now we needed to be both above $1. Then he was sure it's true. All right. No, this is the example for Paris and watermelons both below $5. So cell C three Paris and watermelons both below $5. Now, in his case, Paris and watermelons are both below five goes. We got pairs only at $1.60 or as watermelons are $2.50 or both of bill, $5 Therefore, it is showing up as true. Now what if both the melons go up to $6 there, It shows this false. So you get the idea for the end logic now that all large it works very similar. And the all logic is if I did a costs off apples or bananas above wonder. So basically so long as one criteria is met, you get a true now you only get a false if both criteria are not met. So in this case, we want to check if the cost off apples and the cause of bananas above $1. So we've got apples, apples above $1 bananas are below $1. But you just need one criteria to go true. In this case, apples is above $1. So this will be a true now. What if Apple is below $1.0.9 There it shows up as a false So just one criteria that will work. Okay, so in this case, check either costs off apples. All parents are above $2. In this case, apples and Paris are both below $2. Therefore, it shows up as a false so and then the old logic can walk upto as many criteria as you want in this case, and this is the example here. We're only showing two criteria. One on item one and one item to, but we can actually stretch that out to as many items as your one. So so have fun. Play around with that and you can see that this is very useful for Mueller. Connect to move on to the trim and the proper function. Now this is very useful, especially when you're dealing with data that is coming off from some system generated raw data. So I give you an example that say, we've got sell a two and a three. The data that you have it shows up a little bit. Walk shows that the most spaces in between the woods, so when you trim the cell, you remove the additional spaces. Now this additional spaces are not just within words, but also if there are before the words or if it's after the word. So if there are like spaces after what space is here, or if the space us before the what even so all this space, unnecessary space will actually get removed when we trim it. So let me just show you. If I just copy and paste the values here, you can see that there are no more additional space at the ends off this string of what's here. Okay, so this cream result basically just make things very neat. It removes all this unnecessary spaces, your half. And we also have the property formula on a proper formula. Basically, just takes away all the unnecessary capital letters you have in your text. Now, an example here is sell a six. You've got this. What old dictionary? Now? Old dictionary. You can see that the l in the d the tea and all A These are all unnecessary capital letters . By using the proper function, we use proper onto the cell and it just fix that completely. Okay, so this is all very useful formulas to really just tidy up your data set now moving on to V look up and index mission on the vehicle and the next match formulas are so useful, especially if you're working in a data analytics role. You would deal with huge amount of data. The vehicle function allows you to just scream. True your data very quickly and find out what you need to find in your data set. All right, so I give you a very simple example here. Let's say you want to search for the price off mutton. All right, we've got ah, set here with some fruits and meats. We've got apples. Parents? What? The melons, park fish. Some means we've got popped fish, mutton and some beef in order to find Muffin Martin is at this role here. Rolls seven. Cell C seven. That's your price. You don't get to 50. Use the V look up formula. So you look up. Sell e to it. Will you look up mutton into this array? Did this entire selection here go to column Tree, which is your first column second column in that column that your price and use false false will get you an accurate value off your selection. So there you go, Martin. Showing up as $2.50. So what have you changed available here? Let's say we want to find fish. How much is the price of fish fish they're creating? Oh, but we have it. I have it regular, or I can try something. Like what? The melons and you get to 50. What a man. Ensuring I was 2 50 Yeah. So it's really very simple. Now, I just want to do a demonstration here. How I do that. Okay, so let's see. I start from scratch. It was V. Look up, and then you select the value in these cases. Sell E to e to here, press Tacoma, the table or a table or a Is your data set? Basically. So? So let your data set. Now, You notice earlier I had the dollar signs. Um, at the showing up here. Basically, what it does, is it Fix us the selection off the data set. Because if you copy and paste this formula down your spreadsheet, you will still reference this fixate that there does it. So just press f on your computer, f four. And you know, there's all these dollar signs appear. Now this dollar signs go mean that you have fix this table or a specific to these data set . Okay, President Coleman button and then the call them. Right. So, first column, Second column in that column breast tree and then false. Okay, go to 50. Now assuming this, the cells are all empty, and that's a This is all empty. Now, if I just copy and paste copy and pays this formula in, you can see that it will still reference. And such this fix selection here. So that was what we did earlier with the F four on your keyboard. You fix the selection. All right, so we got fish showing up as true dollar. 80 cents. We've got oranges showing up. It's 1 30 So that's the view. Look up. None of us. That was very, very useful. Something very similar to the View Cup is the index match function. Now in next match function works almost identical to the view. Look up. Just that the formulas are slightly different. It's something same purpose to out. See, Now, over here, you've got let's say we want to screen Martin fish and oranges as well. Okay, let me just show you an example Here, So press f two. You see how the next match is constructed? First we do an index. Now the index is searching your entire data set. Sell a one to see 11 their entire data set, and then you do a Mitch. All right. You want to match so e night with your vertical column here first. So sell 81 as well to 11. Fix that. Fix that selection zero and then match your horizontal selection. In this case, you want to find price but 100 kilogram and show that selection as cell a one to C 10 Okay , let me just quickly do an example here. So it was Index a range, which is this Fix that press the F full button. Then you want to match? Look up value. Now we're looking up. Sell e ni here, which is available cell as well as the look up array, which is this vertical column here. You wanna fix that as well? Zero, Because we want to find the exact match and then met again, which we want to pick off. If eight. Which is your price? 100 kilograms. And then the Look up. A real which is this selection here. Fix it as well. And then zero. All right. Exact Mitch there to 50. So once you got that out, you see that matters is showing us to 50. Copy. Paste it down. Whoops. This f it should be. Absolutely. That's how you troubleshoot. Yeah. Okay. And then you just copy and paste it. You should see that. There you got tree 80 for fish and orange is showing up as 1 30 Okay, so this should be exactly identical to your vehicle results. And just have some practice on this two formulas. You can actually just stick to one and use that in all your your references is really, really useful. Later on, I'm gonna show you how you can use other forms within another formula, and that will be even more powerful. Okay, now let's move on to the concatenation formula. Now, the corn captain it and the end formula. Basically, what it does is that help to string tax together. So give you an example that they've got column a showing as the first name here column be showing as the last name and you've got You know that these guys, some of them use gmail, some of them use hot meal. Now, you wanna string them up and get that email address. Now, this is something very common, right? Like you've got a whole ease off first names and last names, and you want to just get a list off, connect that email address so that you can just send out a distribution. This. So how do you do that? You can use both the content in it and the end logic to build that list. So first, the content in eight, you can catch any cell a two, and then, given that they are e mails, have this dot between their first and last name as well as the character. So you just string them together, sell it to dot B two at cell C two and that should string up and get you your email. No, you only got an email for each names, right? You want toe link them up all together so that you can have a distribution list like that. How do you do that if you just break it down? We are just using the end for Miller now in outlook. We know that in order the string email addresses together, you need to have the semi colon Israel as a space. So just by doing that, we are able to string this email address with the one before, and that will give us to, you know, addresses linked together, and then we do the same. All right, let me just show you. Let's say old days are gone. I would just take this a copy and paste it down there. So let me just zoom out. You can see that for sell E seven, you would have a lists off all the email addresses linked together, And this is really useful, whether it will or at your own organizing some kind of event. This is very useful. Okay, so move on. We've have the maximum and the main function now, although we had this data set where we have the price off fruits as well as meat. And over here, we want to find out what is the maximum price in this data selection here. All right, so the maximum price off cause we have $3.80 as fish, right? So just select does use the formula Mexx, and select the entire selection. You would get the maximum price creating now for minimum. It works the same. Very simple. Just put mean, select the cells here, and he would get you the this Ah, smallest number here, and that is 80 cents. Okay, so This is Ah, really straightforward formula. No moving on to the left, right, Len or fine formula. This is very useful when you wanna alter the existing tax off the original cells. So I give you an example. Let's see, the original cell you have here is showing up as cell a one. Now, the left formula basically allows you to cut out, uh, the pseudonym of characters from this text message. Okay, so over here, let's say you want to extract out the 1st 4 characters, which is the I and Old Dino used the left cell a one and then four. So you are extracting out the 1st 4 characters now. It wants the same for the right. Very simple. Just use right cell a one and four. And that gives you dinosaur. Yeah, dining room for the left and saw for the right. Okay. What about Len? Now, Len allows you toe see how many characters are there in total in the tax. So this land is really useful because we usually use this with the fine formula. And the fine basically allows you to find out where exactly of the formula is the letter that you're finding. So in this case, let's say we want to find the character end. Okay? No, we know that character end in this. What dinosaur is at policemen number three. Right? One is the eyes two and threes is end. So if you put fine the character end and to sell a one studying from the study numbers one which is the he gets you number three, right? Number three shows you where n is. So if you're trying to find out what the characters left off N you can in this case, left off and you should actually do, um, fine. Minus one, right? Minus one. Yeah, and that would be just the I in this case, I didn't put the minus one. So it just shows you the characters up till end. So you shows up as D i n. Yeah. Now, if you want to go right off, and you need to use the total number characters, miners, where the placement off end is, and then you do the right off that. So in this case, you would get all sore, which is using the Len function. The land function will get you the number it which is the total sum of characters here. Miners, where the placement off end is which is using the find function. And that gets you three to get, you know, number five. So this Len, my Len a one minus. Find an a 11 We'll get you the number of five. So using the right formula where we learned earlier there five right characters off the what dinosaur is going to get you also. All right, so this is very useful as well. Especially if you've got a string off tax that you want toe pull out only certain aspects off the water text message. So instead of dinosaur, you can actually have multiple words here. And you can use this functions to extract bits and pieces out off the string of text. They only pull the text that you're looking for. Okay, so this is also very useful. Now, lastly, we want to move on to nest. That formulas nested formulas is, um, a compilation of what we've learned previously. Now I'm gonna show you an example off the V look up and the it function, which, personally, I think it's one of the most useful formulas when you're dealing with data sets. Yeah. So let's say we've got this big asset, your fruits and the meat and you've got the price here. And you want to check whether we have a sudden put out in stock. And if it if it's inside Oh, invent tree, then how much is this price? Okay, so I give you an example. Let's see. I mean, we know that we have ah set off fruits here, but we don't have things like mangles. So let me just put the what mango inside here. We know that straightaway is not in stock. Okay, so it is really very smart this formula If we put in grapes now we know grapes are inside inside this list grapes is over here and it shows up a tree 20. So how do you do that? You use the nest that be Look up inside the if function. So let me just show you. We use a V look up to check. Firstly, if it is inside the in Venturi and if it is inside the in Venturi, then we use the if function to say that if it is inside the in Venturi, it shows as in stock. Els we get an era, and if we do get an era, we use the if error formula to show that it is not in stock. Okay, so basically, that's how it works is really very smart this formula Go ahead to practice. Once you learn how to use this, you can just, you know, have it at your fingertips whenever you're working on new data sets. This is something so useful. All right? Same thing for the price. In this case, we're looking. We're doing the function off. If f tree, if it is in stock, then we do the V Luca. All right, if it's in stop, we do the V look up and we find column three, which is the price. So if it's in stock, we want to find out how much it costs. That's basically what this formula is telling you. So you also have to get a sense of how to understand the formula. And the the way to reach the string off formulas is that it starts from the right. All right, So if f tree, which is sell every if grapes are in stock, then we ve look up. What's column tree. So if great sign stop that. We want to find out what is showing up here in grips. Okay, so it shows grapes would have a price off tree 20. And there we go. Tree 20. Now, let's try again. If we do bananas bananas there, it's installed and is showing its 80 cents. All right, that's so many formulas out there in Excel. But we put together this leads that is going to help you get to a very professional level. And using Excel live, you learn and internalize all of these formulas. Here you will become very fast, very efficient. You become a super prole in excel. Okay, so I live here in this chapter, and I'll see you in the next one. So you 35. SUPER PRO Series - Dashboard Basics I: Hi. Hello, everybody. Welcome back to the ultimate master cause for Excel Deadbolt Analytics. We are now on being away true this Super Pro Series. We are now in chapter four, and Chapter four is really about building your dashboard. Okay, so we've covered a lot in the previous chapters the previous series. Now we're gonna put all that we have learned into this chapter and show you step by step, how to build your dashboard. Okay, So the key topics for today I'm gonna show you my thinking process behind how I frame up the structure of my dashboard and how I put the pieces altogether How I align the formats high structure and align the entire framework and polish up your dashboard. Okay, so I'm gonna show you all this step by step. Let's quickly get excel open. Okay? So this is the data said that we have right now, and the first thing you need to do the first thing you need to do is that you want analyze your data set. So from the left, press the ultimate down button and you can see what every column is. So press alternate down and you see that in this case, we've got data from all of our sales managers, relationship managers selling various products. Here. We've got accessories, laptops, smartphones and tablets. And there's a data compelled for two years 2015 and 2016 which tells us the sales volumes as well as the sales revenues. And we also have it separate that by regions we've got Asia U K as well as the U. S. A. And we have it also separated by countries so very quickly. The first thing that I wanna have in my dashboard is a breakdown off the region's right. I want to see which regions contributes the most revenue. I want to see which relationship manager is having the best revenue. I want to see which put excels the best. I want to see a month of mantra, and I want to see across the two years 2015 2016 which months were the best months? And also we deal was the best year 2015 and 2016 which was the better year. Okay, so that's a lot of data analysis that we can probably do, but at the top of my head does with a key data matrix that I wanted to show in mind. Dashboard. Now, once you have that in your mind, you go to the next step. The next step is to construct your pivot tables so very quickly. This from what I came out from the top of my mind. I quickly just built a couple of people tables here. Seals by countries, sales by product classes, sales across the very Zeman's and revenue by gender. A swell as the sales managers across the locations and also by age group. All right, I thought it was interesting to include the sales revenue by gender and age groups, but I think subsequently I decided to take it out. All right? Yeah. So once you have this already formulated out, then go here. Toe built your charts, the structure it Well, just have it. You know what? Just dropped that out in a very basic fashion. You don't have to publish it yet, so just built your charts out. I've got it here. Revenue byproducts, regions, countries are Mondawmin trend as well as, ah, distribution across your relationship managers and you can very quickly see felt some very useful information. You can see that Asia is ah is contributing the most in your region. You can see some of the relationship managers were really top performing in 2016. You've got this guy here, Andrew, That is from Singapore. And he's doing really well in 26. And you've got this guy from Hong Kong as well, Lewis, that is also a top of former. And you can see that your smartphones business were the main revenue driver in your entire business. Right? So very useful information here. You can see that the start of the year usually has the most sales. At least 2016 and then it tapered off. Mitt wakes with a yellow and pick back up. Okay, Now, once you have it all formulated visually across, then the next thing you need to do is to this site, the team and the style of your dashboard. Okay, In this case, I decide you can go for anything. You can go for a light style deem like colored style. In this case, I went a hit with a doctor starting. Um, I chose the color black and green so that it's a little more slick, a little bit more more than they're worth more fat, fashionable, I guess, and then built in the hate us. I wanted to do a sales analyses of the data set. So 2015 2016. And I wanted to show revenue by product sales, regions, countries, time series, Monta Leman revenue trend as well as the distribution off Cross your original seals. Teams are gonna see the breakdown of your sales managers can at some footnotes as well, so that then looks complete. So it notice that I have it formulated in, then skipped format rectangular landscape format because I wanted to eventually build the dashboard. Have it potentially put that over into a PowerPoint or Potter over in tow. Pdf. So that I can create multi slight report multiple slights dashboard. So it's very flexible in that manner. Okay, you can also do a portrait more, but it's it's entirely up to you how you want to to create the stall of it. All right, So once you have that, then you must be wondering, How am I gonna put your charts inside the dashboard, right? Because there's no way at this point you can align the charts inside perfectly. So not to worry. In fact, just go here, toe polish up your the style of your dad's. What's first? Okay, style of the visuals. Now, in this case since I chose her degree, I went ahead to create a great team for all your charts. And because you want your fun to be something contrasting so that it's very clear I chose white or just slight gravestone white for some of the colors. Give off a very professional look. Okay, now, once you have all of that, just go here to copy each visual chart and paste it inside your dashboard. Okay? Copy each one of them. Now, at this point, you will notice that the format is not gonna align, right? See some of the chatter already overlapping, or it's not fitting the original intended selection area off your dashboard. Right. But don't worry about that now. The key ideas years to adjust. Make sure you have it. Draft that just dropped it out because we will fix the alignment later and then just mentally overwrite the sites for a second. Okay? There. No. You want to make sure that each height, as you intend that is perfectly in line, right? So in order to do that. Click on the chart that you think is perfectly set. Alright, Goto format. And you see that? 3.51 So for all of them, click on for Met and align it to be creeping 51 there. No, they're all the night. And then for this as well. Just click on the chart on your left in a line left. Okay, so that's it. That's it is really very simple. Now, once you have all of that, you can quickly put it over the Paul Point. How are you gonna do it? Copy and paste it and then open up your Paul point. But but not power point. And that's quickly paste it in sight. And there we go. See? See how quickly waas to put it over from Excel into PowerPoint. Okay, so after which I also added in the haters, I didn't wonder the haters to be inside the charts, So I included a separate hate as well, and it's actually really simple to do as well. So you get a sense off how I created from scratch for us. Analyze all data, built the pivot tables, create the charts and once you have it all mapped out, then start formulating the background and then building the dashboard. Okay, so that's a wrap for this chapter. I hope that now you know the steps. How to start from scratch with your own data set how to score here to build your pivot tables, create your basic charts from the background, and then finally piece it all together and create your own dashboard. Okay? And once you're at this stage, don't worry. The next phase, the next chapters are gonna be even more interesting. You're gonna learn how to make it dynamic. How to even make it in directive for your users to play around with your dashboard. Okay, so stick around, stay tuned and let's move on to the next chapter. See you 36. SUPER PRO Series - Dashboard Intermediate II: Hello, everybody. Welcome back to the ultimate master costs for Excel Dashboards, Analytics. We're now in the midway off the Super Pro Series. We're happy to have all of you here today. So today we're gonna be touching on Chapter five Dashboard Intermediate to now, This is gonna be a continuation where we left off from the previous chapter. Previous trip the we learn how to build your dashboard. We put all the pieces together from what we've covered in the previous series and previous chapters, and we learn how to build your dashboard. Okay, so today in Chapter five, people on your dashboard dynamic are gonna introduce Slice it in your dashboard. Key topics for today is to build a dynamic dashboard. And on top of that, we also gonna learn how to troubleshoot your data. If any of your people Data's has troubles with the data connectivity, we also learned how to fix that. And finally, we also learn how to embed the slices into your dashboard. So it's actually going to be probably dashboard as well. All right. So a lot to cover in today's session. I'm going to quickly go back into excel. All right, so We have Excel open right now and we know that the picture of the Did I just recap from the previous chapter. We have built the people the pivot tables and we learn how to build your dashboard. Now, in today's session, we're going to connect all these people tables with your slices. We're gonna turn your dashboard dynamic so very quickly. Just start to insert the slights as you want. Now the slices, You know, in the previous chapter we studied in the professional series, the slices are gonna be used as a smart filter. All right, the slices work as a smart filter, So just think of what field does you want to create for your dashboard. And in this case, I want to do a filter on the region's. I want to do a field on the country's the Munns Products. Even agenda I want to see which gender is is potentially bringing more seals as well. And also the rings. All right, so I've got a couple of different slices created here Now. What you need to do next is the interconnector slices. So how do you do that? Very simple, right? Click the slicer. Go report connections and then take all the pivot tables that you want to connect them. So in this case, I just take all of my people tables, click OK and do that the same. In this case, I've really done that. So it's all connected. So just make sure that all of this pivot tables are all connected. You don't wanna have it unconnected because the moment it's not connected, then lose the connectivity so that I would just do a demonstration on that. But just make sure every single slice of that you have created all connected. So now, once after you have all your people slices all connected, you're good to go. Okay, so copy and pays then into your dashboard. Now, before you do that, you want to decide? Probably. Where do you want inside your slices, right. That's a dashboard. Have space for your slices. Do you want to put the slices as part of the dashboard, or do you want to put it at the site? So in this case, because we want to leave the slices at the site. So I'm just gonna put all the slices on the left hand side. You see, here on the screen. I just call it Filter. And I'm going a bit by bit. Put on my slices on the screen. Okay, So starting with the first let's pop over slices by regions. Just you can copy and paste it, or you can cut and paste. It is up to you. Let me just remove this. Okay, this out. All right. So that's putting the region's first full by the country's all right, and then go hit with the months copy and paste it in. So impressing my control C and control V on the keyboard put ups Move it over. Gender movie over. And also the ranks. Just move it over there. Okay, So pays them all into your dashboard, and then you can just do a bit of a lineman Just aligning a little cocaine just lining so that all of them are looking clean and neat. All right, there. Okay. Now you can just quickly test it. Okay? So let's say I just like Asia and UK. There you go. All right? Everything changes. If you do select all the countries, it changes accordingly. Okay. Um, yeah. So it's very smart. This slices with straightaway plan your dashboard into a dynamic version. Now, what is The data is not connected properly. For instance, if I missed out some of the connection, okay, I missed that. Some of the connection. See, if I tried toe play around the slices, you notice that the other people are not changing. That's because the report connections are not fully set properly. So you got to make sure that all your connections are all selected. Then the dashboard will be completely dynamic. Okay, so that's how you basically troubleshoot your data connectivity across all your slices and your pivots. Okay, So now, once you have all your slices in cooperating with your dashboard, the last thing is probably just align the form it. Okay, so this is just a bit of polishing on your slices. It's really up to you. But for me, maybe I just want to change it into a different style. Just a few of my overall templates. So you can go with some of the presets styles that they have in Excel. In this case, I'll just go with rate so that it set off jibes in with my overall dash. What steam? So that's it for this chapter. I hope that has been very helpful. Learning how to turn your dashboard dynamic Play around with it. It's very even. Know once you have this slices in your dashboard, you can look to create all kinds off reports very efficiently. If you're looking to just analyze, you know, part of the data and just want to see the breakdown across the various slices across the various breakdowns, it's super super efficient. Okay, so that's it for this chapter. I will see you in the next chapter where we will learn how toe ultimate and set some mackerels insight make it even more powerful. Okay, so stick around and I'll see you in the next chapter. See you. 37. SUPER PRO Series - Dashboard Expert Level III: Hello, everybody. Welcome back to the ultimate master chorus for Excel Dashboards Analytics. I hope that you're having a great time. We've asked so far. So we're not chapter six off the Super Pro Series, where we're gonna show you how you can take your dashboard skills to a whole new level. Okay, So previously in the previous chapters we taught you how to build your dashboard from scratch. And then we also introduced slices in the dash. But we make it dynamic so that users can slice and dice through the data so that their dash , but we just change automatically. Now, in chapter six off this series, we're gonna show you what macros can do exported to email, publish your dashboard from excel to PowerPoint and even save it into a pdf format. So your dashboard is not just gonna be reading Excel. You can skillet out many different Softwares outside. All right, So there's so much to cover in this chapter today that's not waste time and quickly move over to Microsoft Excel. All right, so this is where we left off from the previous chapter. You see, on your left hand side. These were all the slices we created in the previous chapter. Now moving on your right hand side. You see these new panel here? A lot of buttons. You can reset your dashboard, publish PowerPoint PF email regions. You can do some customers selections on all of this. This is basically what you can do if macros okay, macros allow you to customize your dashboard as well. Make it very interactive for your users. Okay, So say, for instance, you have done some selections. Now, this is the original data set. You just want to go in tow, Asia. Okay. So just going to Asia and can see that the data is showing just Asia. And in Asia, true enough, the smartphones businesses showing up to be the Bears. You've got all the relationship managers here showing up from Hong Kong, Japan, Singapore, Taiwan, and yeah, maybe you just want to focus on the third quarter results. So click on quarter tree and your data should show just the quarter three numbers. Okay, So, corsetry looking at only July, August and September and this other data correspondingly. Okay, so now ones. That is already. And you just want to reset your dashboard or you want to go back to the original selection . We're gonna publish Paul Point. It's all up to you. Let me just show you how you can publish a PowerPoint pick on a PowerPoint button going to PowerPoint. You have it ready in PowerPoint. Okay, you can even do multiple versions of that sort. Copy and paste this version. And let's say you really have got multiple versions laid out. Paste it in, and you've got multiple versions. Okay, you can go into full screen format and then just see all your multiple versions like that, right? So it's very powerful this export to PowerPoint format. You can also put it into PdF Click on your pdf macro, and it will launch out the shed folder. So let's say I just save it as Tests one. Okay, I save it, and it should save into the folder. Now. I quickly click on Test one, and it's your show in pdf format as well. So this mackerels are very useful. You can even do email for men as well. You can also just reset your dashboard, click on the macro button and quickly just reset your dashboard automatically. Okay, so the dashboard is really where powerful If you let him to use macros inside. And this is probably the highest level you can go of your dashboard already. Now I'm gonna show you in a nutshell. How built macros, or how you can insert your macro buttons. OK, go to develop attack on your Excel and then going to insert. Okay, You can insert many different kinds off boxers or buttons inside. What? I'd like Moses, the original button for Mitt. So let's say I want to look at the rankings off all my cells. Relationship managers, especially under associates. Right. So I just click, OK, and then a button appears. Now I want to change the tax off the button just in associate. Okay, there we go. Click on the format control and change that to whatever form you like. Okay, so maybe I want to go for larger phone. I want a bold it. I want to change it to blue color, and then I could Okay, there we go, Associate. Okay. Recent bold. Do a right click on your button. Just drag and drop it wherever you want it to be. So let's say I want to place it to the top right hand corner. Just pace it like that and that's it. Okay, so pick on it and it will select only the associates. You've got four associates here from Hong Kong, Singapore, England and Las Vegas. And these are all the performance, and you get to see the comparison easier brought in the move. Some of revenue here, outstanding months on the start of the year as well as the end of the year. So they're powerful. Whole neck rose can do all of that. Now you can build in multiple mackerels. You can also insert different kinds off Macro. There's so many you can even inside like a scrolling function. And then you can insert macron when top function and you can assign macron the bottom function. All right, so you know, macro seems very daunting at this level, but I'm gonna show you in the late a few chapters hold. You can understand macro easier and how you can change or alterna mackerel. VD a script. All right, So even if you don't know macro and you haven't gone through any programming class in school, I'm going to show that all to you. OK, so now what did The macro is not working properly. Okay, then you have to go into the V B A script. Let's say the poll point version. Just click right. Click on that. And let's say there's some issues of your PowerPoint formula in your macro. Just assign mackerel and then just click Edit. This was straightaway. Open up the V B A court off your macro. I'm gonna go true. Step by step. How to understand his macro? Better say, for instance, the range is not populating properly. Now, this is something very common. So you can you can pick off macro scripts from Google and they would have ready templates for you. Do you use right? So when they provide you the macro script or you can find those on the Internet, the key thing is we learn how to understand the scripts, those vb a scripts and then make the changes on on their scripts so that you can learn how to use that into your script. So in this example, here, you can see that it's showing you the range off n one toe a V 46. Now, if we just minimize this, go back to excel. This is showing exactly N one a v 46. Therefore, in this in this script is actually calling out the range that we have and 1 to 80 46 which was the original dashboard and having it transposed into PowerPoint. Okay, so that's basically how it works. Now, if you change that range, then the macro script is not gonna work. So likewise. If the macro script is not working right, you can go inside the court, and dangers make changes accordingly. Okay, So I will go through morning that minto how to understand and neck changes the macro script . But for now, just learned that you can use mackerels, built them into your dashboard and have it create wonders for you at this stage. When you're working on your dashboard, Don't just stop it slices, But try to challenge yourself, move onto the next step and insert some micro's insight, ultimate it and make it really powerful. Okay, so that's it for this chapter. For more mackerels are covered that in next two chapters. So stick around and see you in the next trip. See you. What 38. SUPER PRO Series - Modifying Macros / VBA: Hi, everybody. Welcome back to the ultimate master cause for Excel Dashboards Analytics were now in chapter seven off the Super Pro Series. So today it would be really about modifying your macro or what we call the V b A. Which is the according language for macros in Excel. OK, so a quick recap from the previous few chapters we learn how to pimp up your dashboard using macro is gonna make it very interactive. For users you can. It is, instead a couple of buttons and in some Mac Rose and what's amazing on your dashboard, you can just talk about it just the way you want your dashboard to look. You can even exporting to pdf the whole point. Email. There's so many different things you can do with those buttons you have. So it creates a very interactive, very smart in the face for your dashboard. And that's just the extra step you want to do if you want to create an impressive looking dashboard. Okay, so in this chapter, Chapter seven, we're gonna dive deeper into the world of mackerels, learn how to modify Mac Rose doesn't case that it's not a macro that you have built, but it's a macro that is given to you by someone else, and you want to learn how to modify them. OK, so I'm gonna show you how you can open up the visual basic editor, which is the interface help. You can modify those macros and also go through this step true function method to make any changes to those microbes. And lastly, if it's a macro that you do not want, how you gonna delete it? All right, so that's quite a lot of cover in this chapter today. I'm gonna quickly just died right back into excel. Okay, so this is where we left off from the previous chapter. We've got all the macros create that, um, on your right hand side. Now, how are you gonna modify your Medco's? Let's say, for instance, you publish it to Paul Point, but it's not looking up so well. All right, let's you put it the pulp win. But the range is showing inappropriately. How do you do that? Okay, it's really very simple. Let's go back to excel. Press alternate F eight on your keyboard. Okay, that's the short cut. Open an F eight. Alternatively, if you go on and go to shock. Are you gonna understand? Where is it exactly? Located. Take on the development hat and then click on Merkel's. Okay, so now once you have this small at the window popped out, you can see all the range of all the mackerels you currently have in your Excel. That is open right now. So click on any one of them that you wanna make changes. So let's say the Excel range to Paul Point. Mecca is not working so well. You wanna make some changes to that macro? So click on edit button and straightaway. It pops up the V B A script off the macro. So at this stage, you see that? You know, it looks very tacky. It seems like you need to know some calling language to really understand the macro that the true vision is not true. All right. Anyone can can really understand the macro. You just go from top to bottom and do a step by step method. Okay, so in this case, this macro is really trying to put an excel range to Paul Point and we create several objects in this macro and then all this green tax here is not really the courts. All right, this I just as a remark to describe what exactly the court is all about. And actually, that's a very good practice you should doing when you're doing your Merkel's so that it gives you an idea. What exactly is that lying talking about? Okay, so as I mentioned, like when you're dealing back rows, the key things to understand all the macro is that certain variables inside that it's important to think not often what to make changes. For instance, the range in this case you are trying to put over a selected range and excel to PowerPoint . So the range of causes, something that you can change right in this case is and one toe 80 46. It was exactly the range of the dashboard. If I put in a different range, let's say I put in a V 32 okay, and then I close the macro now to close the macro the way to do it. You can actually just close the close this window, or you can just press alternate que on your keyboard, and that would close it. Okay, I take on it again there, Right? That's how you make some changes that America about, of course, in that case, the the previous macros already done properly. And then now we kind of all that the range so that it's not showing up the full range. Okay, So that one you can you can very quickly just fix that up e v 46 and just scrolling down the V b A script as well. Just look at what is it exactly saying? All right. So, for instance, applications screen updating. If you put falls, then the screen we're not update. Whenever there's any changes in variables, I guess this part here is important. The setting of the ship. So the sequence of the V b A script works in a way that you are firstly selecting the range that you have in mind. And then you copy and you open up Paul Point. If it's not open, and then you copy it and then you paste it in PowerPoint. Now, when you paste in palm point is this select that positioning that you won. So in this case, if you want to fill up the full Paul point, you can just try negative 0.1 or negative 0.1 on the left, in the top, so that it's a light with the top left, most corner as its pivot point. And then it just kept just a fool with. And then, like Paul Point visible, it was true. It means that he will show PowerPoint the moment you launch this macro. And given that you've copy and paste that this application cut copy more is false so that you remove the earlier copied it before. Okay, so basically, that's how it works. So let's quickly try to run it again. And we amended the rich and their ego. It looks perfectly fine. All right, so that's how you you basically modify your mecca and you can do it the same across all the different macros here. Very simple. Just press alternate F eight on your computer and just open which ever mackerels you want to change. So, for instance, quarto 1234 Let's see. Quarter tree. Edit it What a tree is playing the macro on the slices, which is the slices on the months, and it's only showing true for slices 78 and nine right because quality tree is just July, August and September. So 78 and nine is correct. If you go for quarter four, it's only showing up true for October, November and December. So that's how it works, even for those slices that you're working on the ranks. All of that like true, false, false. True, it's only showing up true for your managing director, because is the managing director mackerel? If it's for the executive director Macro, it's only showing up true for executive director macro. OK, that's basically a nutshell. How you can make changes to your macro now. Lastly, if you just want to delete your macro is very simple. Look at all these objects that have mackerels assigned to them. Right, if you want to delete it very simple. Just a sigh macro and delete this. Okay, let this That's it. And once you delete that that when you when you click on the object you click on the button , there is no effect. Okay, so that's it. That's not shell of this chapter. Very simple. Very quick, very easy and hope that you learn a thing or two about to modify your mackerels. Don't be too overwhelmed by the BB. A script inside, You know, just take our time, understand what variables are talking about in those mackerels and then make changes if it makes sense, All right? Was case. You know, you could just restart the Marco and Okay, so that's it for this chapter. Hope that's been useful. I'll see you in the next chapter. See you. 39. SUPER PRO Series - Automate your Report: Hi, everybody. Welcome back to the ultimate muscle Cars for Excel Dashboards Analytics, now at chapter eight off the Super Pro Series were coming towards the end of this entire series and this chapter, Chapter eight, will be learning how to ultimate your report. Okay, so in the previous few chapters, we saw how mackerels in excel such a powerful tool, you can building mackerels into your dashboards and it becomes a very interactive and very smart dashboard for users to talk around with. Okay, so in this chapter, we gonna learn how to create your macros from scratch, how to assign those macros in excel and help to even combine multiple Macron's together to reveal a very powerful automation, sequins and excel. All right, so that's not waste time and let's quickly go into excel. So this is where we left off from the previous versions. We've got all slices on the left hand side. We've got all macros on the right hand side. Now for today's session, I'm gonna show you how you can create your macro from scratch. But before that, when we're building of mackerels, always make sure that you have your macro security settings set up, right? So how do you make sure your macro security settings are set up? Right? Take on a developer tab and then click on macro security and on the macro settings, make sure that you've got mackerels all at it and able. Okay, if the meccas had this able you won't be able to create any Macron's. You won't be able to modify ordeal of any macros. Okay, so that's the first thing make sure macro settings are enabled. Now, next, if you want to create an Excel macro from scratch, the easiest method to do if you're not familiar with VB, a script off macros is to record your macro. Now, the record macro function is over here under the development. You've got a thing off. What macro you want to create first. So, as an example, I'm going just demonstrate. Let's say we wanna show your dashboard numbers on Lee from U K and U. S. A. All right. Very simple click on the record macro function called is the U. K and U. S. Is selection. You want to create a macro such that one click of a button on the dashboard only shows UK and USA. So go to your slices. So, like you can u s A and then that's it. Press stop recording. Okay, let's just refresh the dash. What? Now? Once you've got that, really take on your button here that you have have created earlier. Now, this battle has no macros assigned to that, right? So clicking the button has no effect. Now, you want to assign your macron into this button, so right, click this button here, quick a sign, macro, and then scroll down to the macro that you've previously created. So that was you. Can us a selection kick on it and click? OK, so is the moment of truth. You want to make sure that your macro is working Fine. Just click on the button. And there you go. Right. That that's what straightaway shows They die only coming up from the UK and the USA and how you are. You can USA businesses performing in your dashboard. Okay, so that's a nutshell. Hold. You can create your macro If you want to see what exactly the macro is like. Press the alternate f it button. Select your macro that you've created click edit, and it goes into your macro tech. No, this is the script of your mackerel. The recording we did earlier. Basically, just look at the slicer off regions. And Jo's true for you can U S. A. And for Asia showing us false. So, basically is only picking off values coming up from U. K and the U. S. A very simple macro, but basically, you get an understanding home. The macro recording works. Okay, Now you can do that with more complicated functions, is very simple. Just record the macro and then just do all the actions. There is really, very simple. Now, the next thing that we want to focus on is combining macros. All right, you've created your mackerels, you know, one, Combine your macros. I've got an example here. Say, for instance, you want to pull out all the revenues coming out from your accessories business. You've got four products. You just want to see your accessories business. How the associate directors are doing for this two years 2015 2016. And once you have the best, what data? You want to publish it into Paul point. Okay. So, effectively, if you think, should a sequence off those actions. It is calling out a few sequins, right? The first is toe select accessories. Now, the second action would be to select associate directors, and finally, the third selection would be to publish it as PowerPoint. Okay, you can even add 1/4 selection, such that for the month of mind trained. You only want to see the first half off the data of you, or you want to see the second half of data is really up to you or for seven countries. You want to leave them out or seven countries you want to include. It's really up to you. So just make sure you you know all this sequence down and then you can start writing that macro. So let me show you how the macros written. Click a sign, mackerel, and just click edit. So I've really assigned the macro here. The macro. I call it 80 Accessories report in Paul Point. So the first macro I call on Waas to call the accessories macro and accessories Macro, basically was a recording off your slicer toe only select accessories. And then we call on the Associate Director Macro, which is to select the Associate Director And in this case, I've added in call Quarter to so called called to basically would only call out data coming out from April, May and June. And then finally, I call it the macro to Excel range for Excel Rings to Paul Point, which was to publish my dashboard Interpol point. Okay, so we've also got another line here application screen updating as false. So what this line does is that it prevents your screen from updating every single instance when there's a change in any variables. So if you put this line screen updating as false, what it does is that you reduce the leg. I reduce the processing time in that macro, which basically helps to optimize the speed of your mackerels. All right, so we've got all this former crows laid out. The sequence will be first selecting accessories. The second sequence will be to select associate directors and then finally quarter two numbers and then we put it over to a power point. Okay, So by combining altogether, let's take a look. How it looks like? No, just click on the macro and there you go straight away. It shows up in Paul Point. Okay? so this is very powerful. You learn the tricks off combining Macron's together. You learned the trick off, creating a macro from scratch. You can build multiple macro buttons and create a very, very powerful interactive touch. Okay, so I hope that this session has been useful today. This chapter has been very helpful in your cause of building your own dashboards. I would see you in the next chapter You 40. SUPER PRO Series - Publish in PPT/PDF/Emails: Hello, everybody. Welcome back to the ultimate muscle course for Excel Dashboards, Analytics. Now Chapter nine off our Super Pro Series. So we're coming to us the end of this entire series and this trap ledger today, Chapter nine is very exciting because we're gonna be learning how to optimize your Mac Rose . All right, So those Michael's we're learning the previous chapters. The publishing PowerPoint Pdf What emails. You're gonna learn what is the fasters way Creating those mackerels and what are some of the most common mistakes people make when they create them? Merkel's as well as understand this less is better concept in orderto optimize the speed of your macro. OK, so a lot to cover in today's session. That's quickly just dive right into excel. No, When you're working on Excel and you want to come out of a mackerel, most people would start from scratch. Director caught out themselves. If not, they try to record the bit by bit along the way, and they find out sometimes it doesn't work. Sometimes it will, and there's a lot off time spend on troubleshooting the macro to make it finally work. Now what I recommend most effective and fastest method to creating your macro is really to use the Internet, use Google use Yahoo used all kinds of such engines outside to find existing macro templates that are available on the Internet. And you'll be surprised because you're able to find a dozens plenty off macro templates out there on the Internet that you could use. So the key thing there is that you want to be ableto understand these MECO templates. So familiarize yourself with macros and learn to understand the macro VB language. And then when you are on the Internet, looking at the scripts that people right, you're able to see for what works and what doesn't work, what's suitable and what isn't suitable for your own script. Now, once you have that in mind, you're able to really save a lot of time by being able to build your MECO very quickly, using all these external sources and then finally piecing together a macro that you find it suitable. Okay, so I'm just going to show you a demonstration as well how you can further optimize your macro so that it really works faster. Okay, the last thing you want is a macro that is very Hey V is very lengthy. That's a lot of unnecessary logic functioning inside that slows your entire processing speed down. Okay, so let's kick it off with this example in the Excel to PowerPoint mackerel. So let's go on a sign. Merkel. We've already assigned the macro to this macro button, and that's click Edit is going to the V A Scripture. And basically you see that this macro here's I could pretty long right? The rule of thumb, I would recommend is that your macro should be at these lesser than one picture. This is a really almost one pitch. In fact, I think that's probably a lot of room to reduce the size of this macro. Okay, so just looking line by lying. The first line, of course, is the name of the Macro Excel range to Paul Point. That's let's say there are NGOs range. All these are just defining the perimeters of the macro wouldn't take up much processing speed now, in order to reduce the processing speed, we just look truth and time macro and final lines that do not metha all right, and we can take those out. So the first line killed me to select the range this workbook active range and 1 to 80 46. That's that's fine because that is the range of our dashboard that we want a copy. The next is to create an instance of PowerPoint on air a resume necks. So this is not really needed already because I have Paul Point. My system, right is asking us if Paul points already open or north. And if Paul Point is not found, then there should be an error message. But I really know that I have proper in my system. I can actually take this out. I don't really need this aero messaging so I can take it out. Yeah. So what's left is that we've got a line here that say's if Paul points already open. If it's open, then I will keep it open. If it's not, then I will open Paul Point. So this week your important those lines are young. Those errors we can actually just take it out. Now the lix line here is application, not screen updating. It was true. Now this part, you should actually be force If you want to optimize your your macro. Okay? by having it as true. Basically, every single change off variables or logic, etcetera will having promise Excel toe updated screen, and that takes up quite a bit of computing power. So by having this as false, you take that process out of the way and that's moved in your excel computing speak. Okay, The next part here is to create a new presentation. Now, this is important as well. So we wanna open up all point at us, like to the power point and copy and paste it into the daghestani. That positioning off your PowerPoint. So all this part here is actually useful? No. Finally, we want to make Paul point visible and active. And then we cleared a clipboard. Yeah. Okay, so in this example, yes. Like, if we want toe, optimize your macro, just reduce the Claddagh. This is how we do it. So we reduce about, I think, 1/4 off the entire script, and that's it. So let's just go ahead to test out the macro again. Click on it. And there we go. It's actually even smoother than before. So that's it. The rule of thumb here is really used. The internet, all right. Find existing macro templates out there that you could use and optimize it by removing those additional lights. Now, if you learn this concept off using Macron's, you can actually pretty much use it across all the different kinds of records out there. Weather is forming any kind of logic functioning you could just be able to create very fine . Optimize your okay, so the last thing you want is a very well created dashboard, but macron exposure down all right. I hope that this session today is very useful. It's going to just you the fastest way you can use to create your mackerels. Using the Internet to treat your Merkel's is gonna be so much faster, as well as learning how to optimize the speed of your macros. Basically reduced the clapper to really speed up your Mac rose. Okay, so by doing that, you'll be able to create more optimized macros, and that will really help to create a very powerful dashboard. OK, that's it for this chapter. I'll see you in the next one. See you 41. SUPER PRO Series - My Super Pro Excel Tips: Hello, everybody. Welcome back to the ultimate master cause for Excel dashboards, analytics when I the last chapter of our Super Pro Series. So this effectively is the last chapter of the entire course. I'm so happy that you've been with us here throughout this entire journey. And today I'm just going to use this chapter to sum up all that we've learned in the previous food chapter, the previous few Siri's as well as to share with you some of my super pro Excel tips. Now with this Excel Tips, when you walk on Excel, people gonna look at you in a different way, okay? We're going to see how fast and how incredibly productive your own Excel is just gonna be stunning for them. Okay? And you're gonna be able to build very impressive looking dashboards as well and create very powerful looking data analysis. All right, so with that in mind, let's quickly dive into exile. I'm going to show you this Excel tips. So the first step is to really challenge yourself to use excel shortcuts rather than using your mouse. All right, make it a point to always practice and use those exceptional cuts after a while. Give it some time. You're gonna be so fast on Excel just by using those shortcuts. You don't even really need your mouth again. People around you will be completely impressed by your speed in excel. All right, So I'm just gonna show you a quick recap on how to get in touch with those shortcuts. Basically, just press alternate on your keyboard, and the top panel here will show you the various shortcuts on what? To press. Okay, press hitch for home, right. Press end for insert press people. They'll press m for all the formulas, press Avery a data set and flushes you out. What, exactly? Depress. Promise you what? Depressed for those shortcuts. Okay, Now I'm gonna show you an example again, how we can use those shortcut so very quickly. Just tidy up your data spreadsheet in just a few seconds. Okay, so you've got a very message. It especially your very simple just Custer. Like everything. Alternate hitch, e f. And there we go. All right, hold on a hitch. A c just align it. And then for those numbers here often a hitch. K often a hitch. Nine alternation and get rid of those that small places and we fix it like that. Okay, so it's very, very quick. Always used those shortcuts. It's gonna save you so much time Now, the next tip here is to use those drop down filters whenever you're dealing a huge amount of data. So you've got this very big bet asset here very quickly at in the filter, often a hitch s f press alta down on your keyboard, and that will allow you to very quickly see what those columns are all about. Okay, Got names, countries, regions, gender H rankings. Okay, products your minds, etcetera. This technique here is so powerful, whenever you're dealing with huge amount that are sets, in fact, you can be dealing really, really huge data sets. And this technique here will help you to manage those their assets. Okay, now, very quickly. So she as well we've managed to find out that this name Emma Jay, has duplicates inside as well. Some of them have different spacings as well. So later I'm gonna show you how we can fix that up using the next tip. Okay, so the next tip is learning to trim, check for duplicates or blanks whenever you're dealing your spreadsheet. Now, this is a very, very good practice toe, kid. Alright, Practice this whenever you're dealing with any data set where in school, at work, in the office, wherever. Always remember these techniques. So let your whole data set Press F five on your keyboard, click the special button press s and then press key blanks. Such for all the blanks in your spreadsheet. First, we're just point out. Where are those blanks? Okay, so in this case, we've got that area there that's located with those blanks. Now, fix that blank. So we see that we have got to Emma's and one Aaron in order to very quickly civil. The Emma let me just go in. And my Emma, Emma and Emma, She's known as M I. Jones from Las Vegas, and apparently her name looks a little bit different. We've got different names for the same Emma Jones, so we can fix that up later. But we know for sure that Emma from Las Vegas and U. S. A. With Ghana Guys here, Erin, let's quickly go into Aaron, and we know that his from Ty one Okay, that's fixed. That okay, just use your keyboard. You become so much faster now. We saw that the M A lady has spaces inside. Use the trim function. It was him. Whoops. It goes trim, okay. And just pays this across all of that Que paces values so that you fix that in values comes and then copy. Paste it Here, there we go. Fix, fix the length and the width of it again. And there we go. We fix all the names off M I G. Okay, that's fixed up as well. All right, so use this technique and it also check for duplicates. Ordinate am check for duplicates. There we go. We remove those duplicates. So now your data is clean, okay? And the more banks and the one duplicate and it seems like the names are all looking fine. All right, That's how you clean up your data set in just this short period of time. Okay, So next once you clean up, those data said it's time to build your data. Analysis is you can always use your people tables, your charge, your slices and built up fancy. Impressive looking. Their analysis is engaged in this pools to perform very useful data nurses. Okay, so now we just get the role framework. How you want to perform your data analysis is get those slices in, get those charts in, and then finally align your stall. All right. Aligning to a style that you like in this case, I have my own style on designing my dash. What? So I went ahead with this black theme here. Now, in your own, there's what you can try out different kinds of teams as well. It's really up to you now. The key thing here is to make sure those visuals that you have on your dashboard, the charts, etcetera, make sure they are all well aligned. Okay, used the click on those charts. Okay? And make sure you have it well aligned. Go to align their allying top. All right, Make sure this and this is a line left a line left. All right, click on this. There's a line left my life. Right. Sorry. Make sure all the names here are old centralize. Okay. Uh, home. Whoops. Sorry. Make sure that all centralize. Okay. All right. There we go. So make sure it's fully aligned. Gives off a very clean and professional look. Now, lastly, this is probably one of the most important tips as well. Whenever you add in those mackerels in your dashboard, sometimes you realize those mackerels are actually slowing the automation process down. The reason behind is that the macros are very clatter. All right, There's a lot of unnecessary logic functioning lines in those VB a scripts that you need to clean up. So I did a demonstration a little with the V b A script on your export to PowerPoint macro member. We got insight. We saw that It's a huge, chunky macro. You don't really need certain functions inside. So go down line by lying. Press the effort button on your on your key, boy, and you get to see what exactly do you need and what exactly do you not need? All right. So get those lines that you don't need. Okay? I don't get this airline ticket. All fix up this airlines and this should be falls. Yes, correct. Okay. Once you have it teamed up. Yeah, it looks final. You able to save some speed there, Right? There's just a short example how you can clean up your vb a script But if you're dealing even more lengthy scripts out there, just go to line by line and see what you need and what you do not need. All right. It's always very good practice as well to end with the each line. So you know the logic of each line and question whether do you need those that lying insight or do you not know at nine inside? Okay, so basically, that's ah, not show of the six most powerful Excel Pro tips that I have that I use and with all these tips are very certain. If you use it, you practice it in your day by day usage in excel, you're gonna become a super pro, and the people around you very impressed by what you can do in excel. Okay, so that's it for this chapter. I'm so glad that you've been reversed, true and hire cars. Now, stick around. Stay in touch. I'm gonna be publishing my next course soon. See you. All right.