50 MSExcel Tips and Tricks for Excel Experts - The Excel Expert Series Vol 1

1. Introduction : Hello. If you've ever thought to yourself, that is a better way to do this while using Excel, then you know that you're probably right. You may be a newbie on an expert in Excel, but may still find yourself stuck at times on simple scenarios like cleaning the data, formatting the incorrect dates, removing errors are blank rows in your dataset. You may take a long time for tasks like cleaning the formatting, removing the get pivot data from your formulas are just hiding unwanted charts in your presentation. Does that can be accomplished in just a few clicks using some hidden shortcuts. Now, XL could give you exactly what you need to separate yourself from rest of your colleagues by working smarter or being more efficient. And my role here is to give you the tools that will differentiate you and grow your chances to be a value piece to your company. Impress your boss and wow your colleagues. Welcome to the amazing Excel Tips and Tricks, volume one. Impress your boss. And while your colleagues, Hi, my name is Sue, has dark color and I will be the instructor for this course. I'm a computer engineer and a quality expert and a diehard fan of Microsoft Excel. With our two decades of experience of using it day in and day out, either in multiple courses online starting from basic to advanced Excel, dashboard and PivotTable masterclass and advanced dashboard for professional presentations. In this fast-paced course, you will learn some of the coolest tricks in Excel that will make you look like an Excel expert and that to under two hours. The best part of this course is that you can find the best Excel Tips and Tricks will not only save you time, but could also give you the confidence in working with Excel. And even if you're an advanced Excel user, that is something for you in this course. For easy understanding and bite-size learning. Every lecture covers only five tips so that you can practice those steps before you proceed to the next lecture. All the resources needed for your side-by-side learning and practice are attached with this course. I would highly recommend you download them first before you start. If you need any help on the course, Let me know in the discussion section and I will be more than glad to help you on that. Also, I will be adding new tips and tricks off into the course. So you're learning never stops. Remember, there's only 86,400 seconds in a day. And you can make time or save time. You can only spend it and invest it. So learn these amazing tricks and make the maximum out of your Excel skills. I'm super excited to see you inside the course. See you there. 2. Add Initials, Compare Data, Filter Totals, Frequency Distribution and Running Totals.: Now before we go ahead and start this course, I want you to go ahead and download this sheet from the resource section and open it and go to the index section of a, you know, all the topics that I have covered in this course are available in this particular sheet over here. And every time you don't really have to search that topic in this particular sheet, I have gone ahead and created a separate index for this. Let's say if I'm doing the 28th lecture or I'm showing the maybe the 29th trick in this particular course, all you have to do is just open this index sheet and then click on lecture number 28. And it will direct you to the sheet. And once you are done with the lecture, all you have to do is click on the back to index button. And it will take you back to the index that you can go ahead and proceed to the next lecture. So now that you're familiar with the sheet that we are going to work throughout this particular course. Let's start off with our first lecture. So I'm gonna go ahead and click on the trick number one. This will bring me to this particular sheet, which has a lot of data on it. And I want you to get a little familiar with this data because Bohr off, I'm going to make use of this particular data in most of the lectures. Of course, in some of the lectures, the data will be different. However, you will observe that in most of the lectures, similar data has been used often. So let us start off by first looking at this data, will you? Okay, So I have the serial number, I have the names, I have this date. I have the categories, subcategories, the age of the person listed over here. Then I have the date of his joining, and then I have this sales figures. That is the sales done by this particular team member for a particular period, let's say a particular year or maybe a period of six months or so. Now, my boss comes to me and says, out of this particular list, I want you to go ahead and separate the first and the last name of the team members. And along with that, I also want you to go ahead and add your initials over you. Now you might think that, okay, there are a lot of commands available in Excel, and I can use them in order to go ahead and separate the first and the last name from you. But one of the simplest way of doing this is I will just come down to this particular row just below my firstName. And I will just type the first name. The first name on serial number one is Ben. So I will type Ben over here and I will come down to the second cell. And then once I start typing, you will observe that Excel is auto suggesting me the firstName of all the team members that I have in this particular list. Now, all I need to do is just press Control, Shift and the down key. And you will observe that Excel has gone ahead and auto-fill all the first names for me. Similarly, I will go ahead and just type Berets over u, which is the last name of serial number one. And once I come down, if I just type C, you will observe that Excel is suggesting me the last name of all the other team members. All I need to do now is just press Control Shift and down. And all the names have been copy. Similarly for my initials, what I'm gonna do is I will just type the initials for my first team member. I will type B, P. Okay? I will then once again type K and C. But even before I type C, excel is auto suggesting me the initials for all the other team members. Once again, I will press Control Shift and down. And the initials for all my team members have been copied to the list. Okay, simple, isn't it? Let's move on to our next tip. Now let's say you have two different lists with you. And your boss comes to you and says, I want you to compare the two different lists. And let me know if there are any duplicates in this particular list. Then how are you going to do that? Alright, now the list is pretty small, so probably you may say that it will hardly take me two to three minutes to just go ahead and just do a head-to-head comparison of these two particular list. But let's say if there were like thousands of names in this particular list, then how would you go ahead and do the comparison? It's pretty simple. All you need to do is just select the first list, press the Control button and select the second list as well. And now, click on your Home tab. Go to conditional formatting. And then I will go ahead and select this particular option that says highlight cells with rules. And then come down to this last option over here, which says duplicate values. And let's see what happens when I click on that. The moment I clicked on that, it pops up a window that says Format cells that contain duplicate values with a light red fill with dark red text. And right now you can see that all the duplicate names in both the list have been highlighted in the red font. Similarly, if I go ahead and click on this drop-down over here, I also have an option called as unique. If I click on unique, Let's see what happens. Now. It will just go ahead and highlight those names which are unique to both the list. So I have this option of either selecting the duplicates. Are the unique values in two different lists using this particular command. Okay, let's move on to our next tip. So now we come back to the same data that we are using for most of our lectures. So I have this same sales data. And what I want to do over here is just do a quick analysis of this particular data. And probably just want to see what am I sales as per the state or what is my sales as per the categories? So I just want to look at this sales value is based upon maybe the state category or the subcategory. So how can I go ahead and do that? So what I will do is I will just click anywhere on this particular data over here. And then I will click on the Home tab and then click on this particular option that says Format as Table. I will select any one of the formats. So let's say I select this and I will keep this particular option check that says my table has headers. And you will observe that it has already gone ahead and selected all the data in my tables over you. I will just go ahead and click on OK. And the moment I did that my data has now been converted into a table. Now I want to look at the totals. So what I'm gonna do is I will just select the table over here. Go to this option that says Table Design. And I will click on total row. The moment I did that, you will observe that at the bottom of the table, the totals have been added. Okay. I can go ahead and add a few more totals. Like maybe I can go ahead and select the average of ages, et cetera, wherever there are text values, definitely this command is going to be of no use to me. So I will just remove this as of now. Currently, I'm only interested in looking at the totals. So what I'm gonna do is I will just select this particular row or you press Control X. Then I will go to the top of the screen and I will paste it over here. So I will just press Control V. Okay, so now my totals have appeared at the top of the screen. So every time I don't really have to scroll to the bottom of the page to look at these totals. And now, if I go ahead and make any changes to the table over here, first of all, let me just make this font slightly dark so you can see it. Okay. Now let's say I want to look at the sales only for Kentucky State. So I will just deselect all and come to the bottom and select Kentucky. And then click on Okay. And you will observe that it is only showing me the sales total for Kentucky. Let me just go ahead and remove the filter. Let's say I want to look at sales for furniture only. So I will just de-select this. Select furniture. Click on okay. And within my furniture, I want to look at the subcategory of chairs. And you will observe that every time I'm going ahead and changing my filters, that totals are changing accordingly. This is one quick way of using filter totals on your datasets frequency distribution. Now let's say your boss walks up to you and says that, you know what? I wonder, frequency distribution of all our employees age wise. Now you might think that, okay, it would involve a lot of complicated calculations. But know, in order to go ahead and create a visual frequency distribution table, you do not really need to do any kind of complex mathematics. So you, all you need to do is just select this particular column, press Shift Control down. So it will select all the data. Click on Insert, and then select this particular option that says Histogram. Once I click on it, you will observe that it has gone ahead and created the frequency distribution table for me for all the age groups, okay? And it has created the groups all by itself. And you will also observe that it has gone ahead and created this frequency distribution automatically. It has gone ahead and created this bins automatically, starting from 18, which could be possibly the smallest age in this particular entire dataset, and ending on 71.2. Now it has gone ahead and created this bins automatically. We can go ahead and change it. We can right-click on it, click on Format Axis. And right now since it is set to automatic, I will go ahead and select binwidth. And I will specify the bin width as let's say five. Okay? And you will see that my frequency distribution changes accordingly. Now let's say my boss says, I also want to see the category wise sales in my frequency distribution based upon the age. So all I need to do now is I will just delete this particular table from you. I will select all my categories by pressing Shift Control down. And then I will also select the age groups over here. Okay? And now what I will do is I will once again click on Insert. But this time what I will do is since I'm selecting two different columns over here, I will select box and whiskers. And you will observe that box and whiskers now shows me my frequency distribution based upon the categories over here. Okay, This is the edge-wise distribution based upon the categories. And now let me just add data labels to them. And you will observe that in my furniture section. My age group is between 1856 in my office supplies, my age group is between 1966. And in my technologic category, my age group is between 2957. Okay. So in my office supplies section OER, my office supplies category, the age distribution is DM maximum. So this is how you can go ahead and make use of histograms or box plots to plot frequency distributions on your tables without using any formulas. Now once again, we are looking at the same data, we're okay. And my boss says that I need the running totals of all the sales value. Now what do I mean by running totals? So I would need the sum total of these two values. Over here, I would need the sum total of these three values. I will add the sum total of these four values. So I just need a running total in the next column, next to my sales data. Okay, so how do I go ahead and add that? All I need to do is just press Control, Shift and down and all my data is selected. And if I come down to the bottom of the data, I see this Quick Analysis button over here. Okay? Or the Quick Analysis pop up. I will click on it. And then I will go to totals. And then I will just scroll to the extreme right over here. And I will select running totals. Now, you need to be very careful there are two running totals over here. One is based upon the row and the other one is based upon the column. We're interested in looking at the running total based upon the column. So we're going to click on that. And the moment I did that, you will observe that the running total has appeared on the column next to my sales data. I have the very same data, but over a year, it is a summation of the first two values. If I click on the third section, it is the summation of the first three values. Ok, so on and so forth. So this is how you can go ahead and quickly add running totals. Do your data using the Quick Analysis function of Microsoft Excel. I hope you enjoyed this particular lecture and I shall see you in the next one. 3. Currency Conversion, Data Bars, Forecasting, Quick Charts and Quick Access Tools: Hey, welcome back. Let's say you have this particular data and you have the sales figures over you. And let's say you deal with a few countries here and there, and your businesses spread out across the globe. And for some reason you need to go ahead and calculate your sales figures in different currencies. Then you also need to keep in mind that these currency values keep on changing every day. So you want something wherein you just put in the sales data and automatically you keep getting the conversion numbers so that you can send the respective reports to the respective stakeholders. So what you can do is, you would say, okay, it's pretty easy. What I can do is I can just go ahead and use the multiplication function over here. But there is also an alternative way that we will learn in this particular lecture over you. So what I will do is I will just go ahead and copy all this data. Press control C. Okay, and I will go ahead and paste this data. We'll now what I need to do is I just need to select this particular cell, press control C to copy it. And then I will just come down to this particular cell over here. Press Shift Control and down, right-click on it. Click on paste special. And then I will select, Multiply, and then click on, Okay. And you will observe immediately all my dollar values are being converted into rupees over you. Now let's say I'm looking at this particular data once again, the same data that I have been using throughout the course till now. Let's say I'm looking at the sales figures and I'm interested in doing a quick eyeballing or maybe just like a quick analysis in terms of looking at my outliers. Maybe something like who am I best sellers or who are my bottom performers? Or who are my best performers are somebody who is sitting in the mediocre range. So how can I really quickly do that without even getting into a lot of calculations or maybe converting into different tables or pivot tables of that sort. So what I can do is I can just go ahead and select this particular sales data. You go to conditional formatting and then go to Color Scales. Now, I have various options over here. Okay, so let's say I select this particular option over your variant. It says green, green and white color scales. So apply a color gradient to a range of cells. The color indicates where each cell value falls within that particular range. And when I go ahead and select this particular color scales, what is going to happen is my extreme values will be highlighted very quickly and I can really look at them and identify who are. These are my best salespeople are, these are my bottom salespeople. So let's go ahead and click on it. Now what happens is when you start looking at this data, depending upon the kind of color scales that has been used, you can actually make some kind of a guesswork in terms of who are the best people or who are the bottom performers in your team. So if I scroll down, I can see that, okay, seven lakh Rubin is one of my best sellers, or probably constitutes one of my best sellers. If I scroll down at around nine lakh dollar of sales, Gary's and this key is one of my best sellers in my team. And similarly, if I start looking at the lighter shades, maybe somebody like Nellie Joseph. He is probably somebody who needs to really work upon his sales skills or maybe someone like Sean O'Donnell who's only done say it's worth 31 thousand and I really need to work closely with them. Similarly, in the case of Ken Black was only done, seeds were 2920. So basically, when you use color scales, you can just quickly eyeball through the data and make a quick analysis in terms of whatever the kind of inference that you want to draw out of that particular data. Similarly, what you can do is you can go ahead and use different color scales if you want. See what you can do is you can click on conditional formatting, go to Color Scales. And then maybe you can just select this particular kind of scale like green, yellow, red, wherein all the greens are the ones who are your bestsellers. Yellow is something that is probably in the mid-range. And red are the team members whom you really need to work very closely for, especially for someone like Ken Black, because you can see the darker shade of red over year. So definitely someone to actually look out for and worked very closely with. So basically you can use colors case for doing a quick analysis of your data. In case you really don't have the time and you are only interested in looking at the outlier is real quick. Now this is a kind of a different data that we're looking at. Okay. So let's assume that this is a call center data from Jan 2022, December 2021. Okay. And based upon this particular trend, I'm interested in knowing what is the possibility or what is the probability of getting a certain number of calls in the month of January and March. Okay. So what I'm gonna do is I'm going to just go ahead and select this particular data. Okay? And then I will click on the Data button over here. Then I will click on forecast sheet. When I click on forecast sheet, you will see that it has gone ahead and popped up a chart in front of me. Now, this is broadly based upon the past data. It is going ahead and making a prediction in terms of what the future trend looks like. But what it is telling me is this particular line denotes. The amount of calls that I can focus that probably I will receive in the mantle Jan, February, March. These lighter lines basically represent the lower confidence level and the upper confidence level of these are basically statistical terms. But our confidence level is currently at around 95 per cent. How do I know that? If I click on Options, it shows me the confidence level of 95 per cent. Now what this basically means, if I have to give you a gist of it, is Excel is telling me that with 95% confidence, it can tell me this is going to be my future trend of calls based upon the past trend. If I want, I can go ahead and even change this value from 95% to 99%. Okay? And that will bring me more closer and it will give me a better forecast in terms of predicting the amount of calls that probably I can receive in the next three months. And what happens is when I go ahead and click on Create over here. Let's see what happens. Okay, it basically goes ahead and creates this particular chart for me. And it has also gone ahead and created a separate Excel sheet for me, wherein it is telling me that for the month of January and March, I can forecast that I will receive calls somewhere in this particular range over here. Okay? And this is my lower confidence boundary, and this is my upper confidence boundary. So basically what it is telling me is the maximum number of calls that I can receive is probably 13,673. The least I can receive a 6,980, but there is a very high probability that I will be somewhere close to 10,326. And this is happening with around 95% confidence. So this is how you can go ahead and do the forecasting based upon the past trend using the forecast feature in Microsoft Excel. If at all, you must have gone ahead and attended any of my dashboard related courses. I have taught very complex dashboards that can be used for, you know, your, your stakeholder meetings are using for your professional presentations. But there are different ways in which you can actually go ahead and create a quick interactive chart just for a quick analysis, because you really don't want to go ahead and build big dashboards in one go, you just probably want to do a quick analysis of the data that you have at your hand. And that is where these interactive charts can come really handy. So let's say if at all I'm only interested in looking at the sales data where you are and I just want to do a quick analysis of the sales distribution. So what I'm gonna do is I'm going to select this particular data we are. I will go to the Home tab over here, and then I will click on Format as Table. Okay? And then I will just go ahead and select this option. My table has headers and then I will click on, Okay, Let me just make this a little dog or you click on Home, and I will change the font to black. Okay? Now what I will do is I will just go ahead and select my sales figures over here by pressing Control Shift and down. And then I will click on Insert and add a line chart to you. Now, this is definitely not the best chart that I can go ahead and create because it has lots and lots of data for me. So let's see how we can play around with this. So I'm going to just right-click on it. I would click on cut and then I will paste it over u. Ok. And now let's play around with this particular chart a little bit. So what I will do is I will select this table, click on Insert, and then click on slicer. Let's say I will go ahead and create a slicer based upon category or subcategory. Okay, then I will place my license with you. Now if I go ahead and change the slices, let's see what happens to my chart. Okay, So now what is happening over here is when I'm going ahead and clicking on the buttons on my slices of my pivot table is shrinking in size, so I need to go ahead and fix that first. So let me just go ahead and remove the filters from you. So what I will do is I will just right-click on my chart over here. We'll do Format, Chart Area. Click on this particular option that says Size and Properties. Click on Properties. And then I will select this particular option that says don't move or size with cells. Okay? And then I will close it. And now if I go ahead and change the buttons on my slicer over here, Let's see what happens to the chart. Okay? You will observe that by jar changes accordingly. Okay, So this is how we can actually go ahead and use interactive charts to do some real quick analysis without building complicated dashboards. Okay, let's clear the filters. And let's move on to our next step. Now let's say if you're looking at this particular data value and you want to run some kind of an analysis on this particular data, but probably you run out of ideas. So then this is where the quick access tool can really come in handy to you. Okay. Let me just go ahead and delete this initials because probably I won't need it over here. Okay, let's see how the quick access tool can come handy to meet. So let's say I want to run some kind of an analysis on my sales data. But I'm not really sure that you know, what kind of an analysis do I really want to run? So what I can do is I can just go ahead and select my sales data, press Control Shift and down. Then I will go ahead and click on this Quick Analysis button over here. When I click on this Quick Analysis button, it gives me various options. Like do you want to go ahead and format the data based upon data bars? Or do you want to put some kind of a color grid over there? Do you want to add some icon sets over there? Do you want to go ahead and convert this into some kind of a chart? Okay, so let's see what happens if I go ahead and click on Chart. Okay, it will immediately go ahead and create a chart for me in case I want to run some analysis. Based upon looking at this particular chart, I can just expand this chart and have a look at it. Okay, Let me just go ahead and delete this. I can click on totals and use various kinds of mathematical formulas and run some kind of an analysis based on the same. I can go to the table section and it will ask me if I really want to go ahead and convert it into a table or a pivot table or do something more kind of an analysis on that. It will also give me an option of going ahead and creating sparklines, something that we will learn later in this particular course. So basically if you run out of ideas and you really want to run some kind of an analysis and get some kind of a meaningful output out of the large, cumbersome data that you have at your hand. Then Quick Analysis tool is something that can come in really handy to you because it will just go ahead and pop up ideas in front of you based upon which you can actually start your basic analysis of the data. So I hope you enjoyed this particular lecture and I shall see you in the next one. 4. Printing Headers, Percentage Conversion, Page Breaks, Auto Decimals and Add Line Breaks.: Hey, welcome back. Now let's say you have this data in front of you and you want to go ahead and print this data. So all you have to do is just click on File and then click on print. And it will show you a print preview of the data. Now, if you observe this data over here, I can see the headers on the first page. But as I scroll down, I don't see my headers on the second page. Okay. Now I know that there are multiple ways of going ahead and ensuring that your headers are printed on all the pages. But one of the quickest way of doing that is let's look at this particular trick over here. So what I will do is I have my headers over here. Okay? So what I'm going to do is I will select this entire row, and then I will go to my name box. And I will type, print, underscore titles and hit Enter. That's it. Okay, and now let's go ahead and click on File. Click on Print. And this is my first page. Let me scroll down to the second one. And you will observe that my headers are now appearing on all the pages in case I want to go ahead and print multiple rows. Then what I have to do is I have to select two rows over here. Then go to my name box and type print titles. At the same time. If at all I want a row and a column to be printed all the time, then I will just go ahead and press Control. I will select the column as well. Once again, go to my name box and type, print underscore titles and whatever will be a selection. It will be printed on all the pages. Now let us say I have these numbers on my screen over here, and I want to go ahead and convert them into percentages. My photo, I go ahead and click on Home and click on percentages. It will actually go ahead and convert it into actual percentages. And now I will have to go ahead and divide all these numbers by a 100 in order to get the percentages that I want. Now that is a simple way of going ahead and doing this. All I need to do over here is I will just select a blank cell over here, and I will type one per cent over u. Ok? And then I will select this particular cell over here. Okay? I will copy it by pressing Control C. And now what I will do is I will just press control and select this entire column over here. Right-click on it, click on paste special. And I'm going to select Multiply, and then click on, Okay. And you will observe that all my numbers are now converted into percentages. Let's move on to our next trick. Now, let's say this is the data that I have and I just want to go ahead and print this data. Now let's see what happens when I click on file and I click on Print. You will observe that only part of my data is getting printed OVO, okay, the last column that it is printing is dead joined. Okay. And then at the bottom it is printing up to row number Thirty-nine. And everything else is then moved to the next page. And you will also observe that if I scroll down further, the columns that we're missing are getting printed on my third page. And this is not the way I want to go ahead and print my data. So how do I go ahead and fix this? So I will just go back over here. Okay? And the simplest and the coolest way of going ahead and solving this problem is using a page break. So basically what happens is Excel divides the entire worksheet into different pages. And if I want to go ahead and have a look at it, all I have to do is click on View, go to Page Layout. And you will observe that this is how the layout of my pH is arranged by Excel, by default. And this is not the way I want to go ahead and print my data. So how do I go ahead and fix this? So what I will do is I will just stay on the View tab over here and click on Page Break Preview. Okay? And you will observe that it is now showing me some dotted lines over here. Now this is where my page break has been inserted. Now, all I need to do is I just need to go ahead and point my arrow over there. And then just scroll this towards the right. And you will observe that my page is now incorporating all the columns that we're missing initially. And at the same time, Let's say I want to go ahead and print all my data on one page only without it going ahead and overflowing on another page. I can just scroll it down. And you will observe that all my data is now fitting on one single page. Okay? Now if I go ahead and click on File, then click on Print. You will observe that my entire data is getting printed on one single page only. Okay? There's one more thing that I want to share with you over your, let's say if I go to the normal view over you, and I want to go ahead and insert a page break somewhere randomly on my worksheet. So let's say if at all I want to go ahead and add a page break after my row number ten. So what I will do is I will just go ahead and select this particular cell over here. Go to my Page Layout tab. And I will go to this particular button that says Breaks. And when I click on this drop-down over here, it gives me various options. So let's say if I go ahead and click on this particular option over here that says insert page break. Let's see what happens. Okay? So right now it has gone ahead and insert a page break on my row number ten. Now in fact, all I want to go ahead and have a look at that. I can click on View and then click on Page Break Preview. Okay? And you can observe that it has gone ahead and insert a page break over there. If I go ahead and click on File and then click on Print, you will observe that due to this page break, this is how my data will be printed. Okay. Let me just go back. Let me go to my normal view. And then I will once again go to my page layout, two breaks, and then I will remove all page breaks. Okay. In case you have gone ahead and added a lot of page breaks to yours worksheet. And you want to go ahead and clean all the page breaks in one book, then all you can do is just go to the Breaks tab once again and then click on reset all page breaks. Once you do that, you will observe that my worksheet has been set to the default settings. Once again, you will observe that these two columns won't be printed. Lets see that. Go to File, go to Print. And you will observe that all the columns after my date of joining, I'm moving to the next page. Okay. Let's go back and let's insert a page break. Once again. Go to Page Layout, go to the View tab, click on Page Break Preview. And then I will go ahead and add the page break over here. And then I will add a page break or were you okay, if I click on File and click on print, everything gets printed on one single page. So this is how you can go ahead and make use of the page break property in Excel in order to go ahead and define the kind of data that you want to print on a single page. Let's move on to our next step. Now let's say this is the kind of data that you have at your hand. And you now have been assigned the task of going ahead and filling up the price for this particular data. And let's say you have to fill up the price, somewhat like this. Okay? So basically you have to go ahead and add the decimal manually, okay? There is a decimal just before the last two values, okay? But instead of doing this manually every time there is a trick that you can adopt. So all you have to do is click on the File tab over you. Click on options, come down to the Advanced tab over here. And over here, we have this particular option that says automatically insert a decimal point after two places. I can go ahead and change this value if I want. So I will put a checkmark on it and then I will define the value as two in case I want to go ahead and increase or decrease, I can always go ahead and increase or decrease this value. But as of now, since I need the decimal only after two places, I will just go ahead and click on okay. And after this, I will just have to go ahead and enter the data. So let's say I have to enter 12345 and I click on Enter, see what happens. Okay, it goes ahead and adds the decimal automatically. Let's say I go ahead and add another value and I hit Enter. You will observe that it picks up the decimal places automatically. Now let's say I have been provided with the data in this particular manner wherein I have the first name, I have the address of the person, I have the name of the state, and then I have the phone number. Now, I have been assigned with the task of going ahead and putting this data in a systematic format. And when I say systematic format, it is something like this. I want my name first, then I want my address on the second line. Then I want the state on my third line, and then I want the phone number, something like a label. Now if I go ahead and try to do this data cleaning activity manually, it is going to take me a lot of time because right now maybe the data looks small, but let's say this data is running in thousands, then this is gonna be a very time-consuming activity. Now let's look at this shortcut of going ahead and accomplishing that. So what I can do is I can just go ahead and select this entire data. We'll then I will press Control H. This will pop up the Find and Replace box in front of me. Now, all I have to do over here is I have to tell Excel to find all the commas. Because after name, there is a comma after address, there is a comma after the state, there is a comma. So my data is basically separated by a comma. So I will tell Excel to find the Commerce first. And then I will tell Excel to go ahead and replace it with a line. Okay, Now how do I go ahead and do that? I have to do is press Control and J. Ok. Now when I press control J, you will observe that a tiny dot is appearing on my screen. I don't know whether you are able to look at it, but it exists over there. Okay? And then I will just go ahead and click on Find and Replace. Okay? So basically I will say Replace All. And then I will click on Okay, and I will close this. But where is my data gone? All I can see is the first names. All I need to do is I have to just select this data and then click on Wrap Text. Okay? And you will observe that my data is now arranged in the format that I wanted. All my cameras have vanished and every data after the comma has been moved on to a separate line. So in case if I want to go ahead and make use of this particular format for printing labels or something like that. Then maybe I can go ahead and make use of this particular trick as well. So I hope you enjoyed this lecture and I shall see you in the next one. 5. Clear Formatting, Split Screen, Move Data, Group Sheets and Split Windows.: Hey, welcome back. Now let's say we come across this kind of a sheet where we have done a lot of different kinds of formatting. Okay, so we have added some underlines, have gone ahead and applied it Alex and underlines to this particular data we are, we have gone ahead and applied some color grids as well. Or maybe we have gone ahead and change the headers, do a green shade and meet the headers bold a little bit. Now what I want to do is I want to go ahead and clear all the formatting from this particular sheet. Now it is practically not possible for you to do this one-by-one. So ya comes the shortcut trick that will do it for you in one go. All you need to do is just select any cell on this particular worksheet. Press Control a. And if you want, you can press Control a once again because that will select the entire worksheet. Okay? And after this, what I'm gonna do is I'm just going to go ahead and press the Alt key. And then I'm going to press H, E, and F key. And you will observe that all the formatting on this page is gone. Okay, let's just redo it once again. So what I'm gonna do is I'm going to select the data by pressing Control a. And then I will just press Alt H, E, F. And the data is formatted all in one go. But let's say you want to go ahead and use the options within your ribbon to go ahead and do that. Then there is a way of going ahead and doing that as well. Let me just press Control Z and bring the formatting back in case I want to go ahead and remove all the formatting, all I have to do is click on the Home tab over here, and then go to the clear option over here, and then click on Clear Formats. Okay? And it does exactly the same. All we did was we used a shortcut over here. But when you use the Clear Menu, ensure that you don't click on this clear all option because that will clear all the data that you have on your sheet as well. Okay, So let's say if I click on that, you will see that all the data on my sheet is gone. Okay, let me press Control Z. So this is how you can go ahead and clear all the formatting on your worksheet in one go or in One-click. Let's move on to our next step. Now let's look at this large dataset that we have at our hand. Okay? And you will see that this data is spread across 50 different products and it is spread across 50 different days. Now if I tried to go ahead and look at this data, you will observe that I will get lost in this data. One, because this is a very large dataset. And when I start scrolling across this data, you will observe that my headers vanish. And because the headers are not there, I really don't know what data am I looking at? So this will actually go ahead and confused me. Now, one of the coolest way of going ahead and solving this problem is selecting the sheet, clicking on the View tab, go into the Freeze Panes option over here, and then clicking on Freeze Panes. Now once you do that, you will observe that your row and your column headers are frozen. Okay? And no matter where you scroll on this particular data, you will always be able to see the headers. So you're not lost in this particular data. But now let's go ahead and look at a different scenario altogether. Let's say I want to go ahead and compare my data for day number ten with the data for my day number 30. Okay, now how do I go ahead and achieve that? Because these two datas are at two extreme ends of my dataset. Now that is a very simple way of going ahead and achieving that. All I have to do is I have to select this particular sheet over here. And then I will click on this particular option over here. Under the View tab itself, there is an option called as a split. I will click on Split. When I click on Split, you will observe that these horizontal and vertical lines have appeared over u. Now I don't want these horizontal lines because I just want to go ahead and compare my data vertically. I want to compare the data for day number ten, WE Day number 30. So what I will do is I will just drag it and take it at the top. This particular data, I will just drag it to day number ten over u. Ok. And now you will observe that what Excel has done is it has gone ahead and split my screen into two different parts. Okay, you will see that there is one scroller on the left and then I have another scroller on the right. Okay. Since I want to go ahead and compare the data of day number ten, WE Day number 30, I will just scroll the data on my right-hand side and bring the data for day number 30 jets next to my day number ten. And now I can go ahead and do a head-to-head comparison of my data for day number ten with the data for day number 30. Now, one more advantage of this is that let's say if at all I go ahead and make any changes to the dataset on one sheet over you. Okay, so let's say I go ahead and select this data, and I format this data and I changed maybe the color to yellow. You will observe that the same changes will be reflected on the data on my right-hand side because it is one and the same sheet. Only thing is Excel is now going ahead and splitting the screen and giving us an opportunity to have a look at two instances of the same data, just in case you want to go ahead and do a head-to-head comparison of the dataset. And let's say once you are done, finished doing the comparison, you can always go back to your View tab. Click on the Split button over here, and the data will once again be restored to its original format. So this is how you can go ahead and make use of the split function in Excel in order to go ahead and have multiple instances of your same dataset. Just in case you want to go ahead and do some kind of a comparison or want to have a systematic look at the data and do some kind of an analysis, which practically wouldn't be possible because your data is spread across the entire worksheet. Okay, so I hope you liked this trick. Let's move on to our next step. Now let's say we're looking at this particular data that we have on our screen. Okay? It is a product wise data for 15 days. And I have 15 products and have 15 days data in front of me. Now let's say I want to go ahead and move this data for day number three before my day number one. Now the traditional way of doing that would be going ahead and clicking on Insert and then adding an extra column over there, going ahead and copying this data, pasting it over here, and then going ahead and deleting this data. Okay? But then there is a shortcut way of going ahead and doing that. So what I'm gonna do is I'm going to just going to go ahead and restore my data to the way it was. Okay. Since I want to go ahead and move this data for day number three before my day number one, all I have to do is just select the data, press the Shift key on my keyboard, and then I will come to the edge of this particular selection. The moment I come to the edge, you will observe that my cursor changes into this particular for directional arrow. Now, all I need to do is select this data. Okay, and drag it. And you will observe that my data for day number three has moved just before my day number one, let's say if I want to go ahead and move these three columns altogether, okay? And I want to place them before the data for day number eight. So once again, I will do the selection, press the Shift key on my keyboard, drag the data, and place it over here. And my data has moved. I can do the same thing for moving my data on my rows as well. So let's say I want to go ahead and move the data for product number eight and number nine, and place it before my product number five. So I will select this data, press the shift key, select the data, and drag it up. And you will see that my data has now moved. Now one of the advantages of going ahead and moving the data is that just in case you have gone ahead and applied certain formulas are calculations to a sheet. The formulas and the calculations will remain unaffected. It's just that your data will move without affecting any calculations or formatting on your data sheet. Let's move on to the next step. Now I want you to have a look at this data. We're now my data is spread across three different sheets. But this is a similar kind of a data. Okay? You will observe that my sheet number one looks very similar to sheet number two and my sheet number three. Only thing is the values are different in all the different sheets. Now let's say I want to go ahead and apply certain formatting to all the three sheets. And the formatting is similar. So I will have to go ahead and separately do the formatting for each and every sheet. Okay? But that is a shortcut way of going ahead and achieving that. All I have to do is group these sheets together. So for that, what I'm going to do is I will just press the Shift button on my keyboard. I will select my sheet one, and then I will select my sheet three. Okay? And now all my sheets are grouped together just in case I wanted to skip sheet number two. Then what I would have done is I would have pressed Control selected by Sheet1 and then press control and select it xi3 because I didn't want it to sheet two. But in this particular instance, I want all the three sheets together. So I will press the Shift key and I will select all the three sheets. Okay? And now I will go back to my sheet number one and do some formatting with you. So let's convert this into bold. I just go ahead and give this particular products or different shade. Okay. I'm just doing some kind of a random formatting over you. Okay? I will just go ahead and add the sum total over here. So for that, what I will do is I will press Alt equal to and it will give me the sum. I will just go ahead and drag this. So I get sum for all the different columns. And then let me just go ahead and format this a little bit. And let me just give it a different shade over here. Okay? Let me just give a differentiate to my headers as well. So let's say I go ahead and select this. Okay, so I've gone ahead and done the formatting on my sheet number one. Now let's go ahead and see what has happened on Sheet number two and number three. You will observe that the similar changes have happened on my sheet number two and number three as well, including the sum total that I have gone ahead and calculated on my sheet one, and the formatting that I had done to the sum. So this is how you can actually go ahead and group multiple sheets together, apply a type of a conditional formatting to one particular sheet. And the same will be replicated on all the sheets that you are grouped together. I hope you enjoyed this trick. Let's move on to the next one. Now, let's look at this same data that we saw in a previous lecture. I have this similar kind of data on my sheet one, cheat sheet three. Now let's say I want to go ahead and do a head-to-head comparison of the data that I have on my sheet, one with the data that I have on my cheat too. Now for that, I will have to keep toggling between my data every now and then. And then this comparison will become a very tedious job. Okay? Or the other option would be I will have to go ahead and copy this entire data from my sheet to paste it somewhere on my sheet one, and then do a head-to-head comparison. But then Excel provides us with a very cool trick that does the job very easily for us. All I have to do over here is go to my view tab over you. And the first thing that you need to observe is the name of my sheet over here is data for split-screen and split window. Okay? Now what I'm gonna do is I'm gonna go ahead and click on new window. The moment I did that, you will see that the name of my data has changed. Okay? Initially it was data for split-screen and split window. But now you will see this number two over here. Okay, so what Excel has basically done is it has gone ahead and created a second instance of the same window for us. Now how do we go ahead and use it for doing a nektonic comparison? Simple, I will go back to my view tab once again. Then I will click on arrange all. When I click on arrange all it asked me, how do I want to go ahead and arrange the data so you can select whatever type suits you, but I prefer the vertical one. So I will click on vertical and then I will click on, Okay. Okay. And the moment I did that, you will observe that now I have two instances of the same data on my screen split into two different parts. Okay, It is the same data but showing me in two different windows. And now I want it to go ahead and do a comparison of Sheet1 and cheat too. So I'm going to select my sheet one on my first window. And on my second window I'm going to select the sheet two. Okay, and now I can just go ahead and have a look at this data just next to each other for the comparison purpose. Okay, now just similar to what we did in the split-screen option, any changes that we do in any one of the sheets, please reflect on your original data. So let's say if I go ahead and select this T2 over here, go to my Home tab, and I change this formatting. And let's say I select a yellow shade over here. You will also have the same changes will reflect in my data on my original window as well. Once I'm done comparing, all I have to do is just close one of this window. And you will observe that my original data is back. Once again, the name of my file has changed to the original name. So this is how I can go ahead and make use of the window function in Excel in order to go ahead and compare two different sheets on one single worksheet, just by splitting the screen and putting them on two different windows. So I hope you enjoyed this lecture and I said See you in the next one. 6. Subtotal, Dynamic Subtotal, Large and Small Command, Slicer Connections and Chart Subtitles: Hey, welcome back. Now one of the best ways of going ahead and getting totals for data that has been filtered is using the subtotal function rather than using the sum function. Now let's say I have the sales data where you, okay? And I'm gonna go ahead and make use of this subtotal function. So let's see what is the advantage of that. So I would just go ahead and add subtotal. And then when I open the column, it is giving me various options. So I'm going to select sum over u and nine is for some. So I'm gonna go ahead and select nine over you. And then it will ask me for which particular column do I wonder subtotal. So I'm going to select this one. And then I will close the brackets and hit enter. Okay, So now it is giving me the subtotals for the entire sales values that I have a wheel. Okay, Let me just convert it into dollars. Now let's say I go ahead and filter this data. So I will just go ahead and click on Data, and then I will click on Filters. And let's say I go ahead and filter this data based upon categories such as furniture. When I do that, you will see that my value will change accordingly. Now within the furniture, Let's say I'm only looking at the data for California. Then you will see the subtotal function will only pick up the values for the filtered rows that I have within my filter criteria. So this is the advantage of going ahead and using the subtotal function rather than using the sum function. Let's move on to our next tip. Now since we are working with subtotals over here, let's look at one more trick that I can go ahead and apply it to my subtotals so that I can use the subtotals for giving me dynamic values based upon my filtering criteria. Now, what I'm gonna do is I'm going to make use of this lysosome. But then how do I go ahead and apply slices to this particular data that I have? So all I have to do is I have to select this data. We'll click on the Home tab. And then I will click on format as tables. And then it will ask me to select a particular format. So I'm going to select this particular format. And I will also select this option that says my table has headers and then I will click on, Okay, now my data has been converted into tables. Let me just go ahead and highlight this. And just, let me make it a little bowled over u. Ok. And now let me go ahead and add the subtotal function over here. I would say subtotal open brackets. I'm going to select the number nine, give a comma. And then I will select my entire column over here. Close the brackets, and hit enter. Okay? And now I will go ahead and convert it into dollars. Okay? And now I will go ahead and add some slices to my data. So I will select the data, go to Insert, and then I will select slices. And let's say I select the slices for State and I select this license for category. Okay? I will place my categories over here. I will place my state over here. Let me just add just this little bit. Okay? And now if I go ahead and make any selections within my slices, let's say I select office supplies. Office supplies only for Illinois. Then you will see that my subtotals will act dynamically based upon the filter criteria and then it will return the value accordingly. This is how we can go ahead and use the dynamic subtotal function along with the help of slices by just converting our data into data tables. Let's move on to our next step. Now we all know that within a particular data value, in order to get the maximum and the minimum, we use the max and the Min function. So let me just go ahead and make use of the max function over you. And let me find out the highest sales value. Or rather let's do one thing. Let's find out the highest age within my age criteria. Okay? So I will just go ahead and select my entire data range over here and then close the bracket and hit Enter. Okay? So it says that 66 is the highest it within my age column over you. But let's say I want to know what is the second largest after 66. Now what function do I use for that? Because I know how to go ahead and make use of the max and the Min function. But in order to get the second or the third largest value, what function can I use? I can make use of the large and the small function over u. So I'm going to say is equal to large. Then it will ask me in which area I want to go ahead and find that data. So I'm going to say find it in the eighth section over here are my age column over here. I will give a comma, okay? And then it will ask me which values I want the second largest value, okay? In case I want the third largest, I will give the number as three. In case I want the fourth largest value, I will give the number as four, but I want the second largest value. So I'm going to say number to close the bracket and hit Enter. And it says after 66, the next highest value in my age column is 58. Similarly, let's find out the smallest age in our age column over here. And for that I will make use of the Min function. Okay? And the smallest age is 18. But let's say I want second smallest or let's say, let's make it third, okay, so I'm gonna say the third minimum value in my head section. So what I'm gonna do is I will say equal to small. Then it will ask me for the edit. So I'm going to select this area where you give a comma and type the number three and then close the bracket. Okay? So it says after 18, the third largest value is 25. If I go ahead and make it two, it was 19. So the second largest value is 19 and the third largest value is 25. In case if there is a tie, let's say for the second value than the second and the third value will be the same. Let's move on to our next step. Now let's say I use this particular data and I created two pivot tables. One for the seats based upon my subcategory, and the second one for the seeds based upon my states. Then I went ahead and created two charts over here. So one is a bar graph. And then for my statewide data, I went ahead and created a line chart. And then I went ahead and added a slicer to my data. So let's see what happens when I start playing with the slicer. So if I go ahead and apply the slicer for my office supplies, you will see that this particular data is changing, but nothing is happening to my line chart over here. If I go ahead and clear the filters, okay, the only changes that happened are within my bar graph over here, and nothing really happens to my line chart. Now, why is that? The answer is very simple. Now, these two charts have been created using two different pivot tables. Now if I go ahead and select this particular pivot table and click on the pivot table. Analyze that, you will see that this pivot table has a name Pivot Table one. If I select this particular pivot table, it has the name pivot table to. But then if I go ahead and look at my slicer, right-click on it, and then click on report connections. You will see that this particular slicer is only connected to my pivot table, one over u. And therefore, any changes that I make within my slicer over here are only reflected in my chart, which is made using the data for Pivot Table one. If I want to go ahead and make use of the same Slicer and see the changes in my line chart as well. Then I will have to go ahead and create a connection between pivot table to the slicer. So I will just put a checkmark over here and then click on, Okay. And now if I go ahead and make any changes over here, you will observe that the changes start reflecting both in my bar graph as well as in my line graph. So you can always make use of the report connection options within your slices in order to go ahead and create connection with multiple pivot tables so that you can use the same slicer for slicing and dicing the data in multiple pivot tables. Now let's look at this particular data that we have over here. Okay, so what I've done is I've used this particular data. Based upon this data, I've gone ahead and created a pivot table, pivot table based upon the subcategories. And then I have gone ahead and created a bar graph out of this. Okay, Now, every chart has a title. But what if I want to go ahead and add a subtitle to it? If I click on the chart and then click on Chart Elements, it gives me the option of adding a chart title. Okay? But it does not give me any option of going ahead and adding this subtitle to my chart. So how can I go ahead and achieve that? Very simple. What I will do is I will select my chart over here. Click on Insert, and then I will click on Text Box. So are you, okay? I will add the text box over you. Let's say, I want to say that this is a subcategory wise seeds. Okay? So all I will do is I will just format this text box over here. And then I will just place it over here. Okay, That's it. So that is one very cool way of going ahead and adding any additional information to our chart, because the chart element gives us very limited options. So I hope you enjoyed this lecture and I shall see you in the next one. 7. Sparklines, Icons, XLOOKUP, Adding Special Characters and Rearranging Pivot: Hey, welcome back. Have you heard about sparklines? Sparklines is one of the coolest way of adding small charts to your data table. Which actually takes up a very little space. But can give you a lot of insight in terms of the ups and downs in your data. Now, let's look at the data that we have at our handover you, and this is what we call as a spotlight. Now a sparkline can be aligned as well as a column chart as well. Now how do we go ahead and create that? It's very simple. What I need to do is I just need to go ahead and select the entire data where you, okay? And then I will click on Insert, go to sparklines. So I'm going to select the line, sparkline over here. And then it will ask me the location range. Now, I can go ahead and create the sparkline table on this same worksheet or I can go ahead and add it to a different worksheet altogether. I'm going ahead and creating it over your itself. Okay, so I'm going to select this range and hit enter and enter one more time. With my sparklines are created. Now with a little bit of formatting, I can go ahead and give it a nice look and feel so I can select this entire range of where you go to my sparklines. And then I will select maybe a darker shade over here, something like this. Okay, and then I will change the weight. So let's put it to N14, okay, and then I can go ahead and add high and low points as well. And with that, I have gone ahead and created this path lines for my dataset over you. So as I mentioned, they take up very little space, but at the same time can give you a lot of insight and can be used as micro charts as well. Now let's say you have gone ahead and created this wonderful chart for your presentation and you're probably going to go ahead and add it to your dashboard somewhere. But you can also go ahead and spice it a little bit. Now I have already mentioned that you can go ahead and add a subtitle to it. And for that, what you can do is you can go to Insert and then click on Text Box. And then I will just go ahead and add a text box over here. So I can say like maybe subcategory by series and just paste it somewhere over here. But at the same time, what I can also do is I can add a few icons to my data charts. And for that, what I can do is I can click on Insert. And excel gives us a lot of ready-made icons to spice up or dashboards and presentations. So I can click on icons over here. It will take some time and it will load up the data. And let's say, I want to go ahead and add this particular icon to my data where you, I will just select on it, click on Insert, and then I will just adjust it somewhere on my chart and paste it. Okay, I can even go ahead and change the colors for it as well. Okay. So maybe I can just change the colors from you and give it whatever shade and color that I want based upon the design of my chart. Okay, As of now, I will leave it to black. So that is how you can go ahead and make your chart look more professional and more beautiful by adding a little bit of spice to them. Now we are all familiar with the x lookup function. And if you're not, then this tip is especially for you. Now, x lookup is basically a built-in function in Excel 365. I'm using a version of Excel 2016. So I have gone ahead and added a small add-in that will help me to make use of the x lookup function. But instead of using the lookup function, I will have to go ahead and use the dx lookup function because I'm using an added. If you're using a version of Excel O365, then you can straight away use the lookup function. Or you can Google and search for the add-in that you can add to your existing Excel version and start using the lookup function as a dx lookup function. Okay, and as I mentioned, I'm using Excel 2016 with an add-in. I'm going to make use of the dx lookup function over here. Now, I'm interested in looking at the sales done by Ben Paris. So I'm gonna make use of the lookup function and let's see what x lookup returns. Okay, so I'm gonna make use of the dx lookup function. Rather. I'm going to say dx lookup. Then I would select bend Paris over you, give a comma. And then I will select this particular column over here. And since my sales data is away, I'm going to select this particular data. And then I will close the bracket and hit Enter. Now it returns the value as two lakh, fifty-six thousand dollars to 56,130, to be more precise. But what happens if I'm interested in looking at the last entry done by Ben Paris, because Ben Perez has three entries over u and x lookup will only return the first instance of Ben Paris in my dataset. And I'm interested in looking at the last entry over here, which is this particular entry. So how will I go ahead and do that? Once again, I will make use of the x lookup function. So I will say dx lookup, open brackets. I will select bend Perez, give a comma. I will select this first column over here, and then I will select the Sales column over you. But now what I'm gonna do is I'm interested in the last value over you are the last entry of Ben Paris in my dataset. So I'm going to say comma, comma, comma. Okay? I'm gonna give three commerce and then I'm going to type minus one and then close the bracket and hit Enter. And that gives me the last entry of Ben Paris in my dataset. So this is another cool way of going ahead and making use of the x lookup function. Cool, isn't it? Now, let's say you come across a situation where your boss comes to you and gives you a data in this particular format. Okay, Then he says that just next to every name, I want a special character to be added, something like a colon or maybe just a hyphen or a dash baby. So now if I start doing this manually, this is gonna be a very cumbersome task for me. How can I go ahead and do that very quickly? Very simple. So what I'm gonna do is I'm going to select this entire data will, I will right-click on it. Click on Format Cells, go to costume. Okay, under general, I will just delete it. And I will say at the rate, I will open brackets and then I will add the character that I want to go ahead and add. So let's say I want to go ahead and add a colon after every name. So what I'm gonna do is I will type the colon over here, close my double-quotes over here, and then click on, Okay. And you will see that the colon has been added to all my names over here. Now instead of that, let's say if I wanted to go ahead and add any other character. So first of all, what I will do is I will click on Format Cells. I will just go ahead and delete this. Click on Okay, so I get the original value. Now let's say I go ahead and select this entire data range. Go to format cells, go to costume, genre. Okay? And then I will just go ahead and type other open double-quotes. And this time I will just give a dash away you, okay? And close the double-quotes and then hit Enter. And just after all the names, you will see a dash has appeared. Cool, isn't it? Let's move on to our next tip. Now let's say I've gone ahead and created a pivot table over here. This is a PivotTable based upon the subcategory. Now, by default, Excel arranges all the entries within a pivot table in alphabetical order. But let's say I want to go ahead and randomize it. Let's say I want something like furnishings to be at the top. So what I need to do is just need to select my furnishings or you, Okay, Come to the edge of that particular cell. The moment I do that, you will observe that my cursor, I change it to afford directional arrow. Okay? Just pick it up and drag it at the top. Okay. Let's say I want my chairs to be at the bottom. I will just select them. And then I will just drag them down. Okay, So this is how you can go ahead and randomize the entries within your pivot table and put them in places of your choice. So I hope you enjoyed this particular lecture and I shall see you in the next one. 8. Date Conversion, Adding Emojis, Formatting Data Bars, Using Spellcheck and Hiding Charts.: Hey, welcome back. Now one of the main idea in Excel is cleaning the dates. Because let's say you go ahead and ask people to go ahead and fill up some information. They go ahead and fill up the dates in all the random fashion. And then when you try to go ahead and change the format of the date, it does not change. For example, if I'm looking at this particular column over here, and I tried to change the date format to view. Okay, Nothing really happens because the data has been entered in a fashion where Excel does not understand that this is a date. It is looking at this particular information as a text. So how can we go ahead and fix this? Now if we try to do this manually, it is going to take a **** lot of time. So there is a shortcut trick that can solve the problem for us and get us the date in the required format. All I need to do over here is just select this entire range over. You. Click on the Data tab, and then I will click on this particular button over here that says Text to Columns. Then I will just click on Next. Just go ahead and click on Next one more time. And then when you come to this particular page over year veteran, it says column data format. Select that date button over here, and then select the format in which you want to go ahead and C or D data. So I want to see my data in DD MM YY format. So I'm going to click on that. And then I will just go ahead and click on Finish. And now if I just go ahead and look at this particular date over here, it is in the date format and the format that I really want to go ahead and look at this data. And now if I want to go ahead and change the format of the date, it is possible. I will select this range, click on the drop-down, and select short date. Once again, I can click on it and select Long Date. Okay, easy, isn't it? Let's move on to our next tip. Now, let us assume that this is a customer satisfaction data for all my employees. And I want to represent this particular data in this emoji format. Okay, it looks a little cool. And if need be, I can go ahead and add it to my dashboards as well. So let us say I go ahead and set a benchmark. Wearing anything about eighty-five percent is considered to be a good seaside. And anything below 85 per cent is considered to be a bad CSAC or something that needs improvement. How can I go ahead and add these wonderful emojis to my data? Very simple. And for that, what I'm gonna do is I'm going to type is equal to IF. And then I will say, it might see sat is greater than or equal to 85. Then I will go ahead and open double-quotes over here. Okay, then press Windows and the dot key on my keyboard that will pop up the emoji menu for me. Now if my seaside is more than 85%, then I want a smiley over here and then I want a thumbs up. Okay? And then I will go ahead and use my double-quotes and then give a comma. And I will say, if my seaside is less than 85, then I want it to be represented by. And then I will again press the Windows key. And this time I will select a frown face and a thumbs down. Okay? And then I will just use my double-quotes and then I will close the brackets and hit Enter. Okay? And you will observe that now it is showing me that my seaside is more than 85%. So I have a smiling face and a thumbs-up. I can just go ahead and drag this formula down. And you will observe that wherever my Seasat is less than 85 per cent, it is showing me a frown emoji with a thumbs down. Okay, let me just drag it over here as well. Okay. And now I can go ahead and play around with this. I can even go ahead and change the colors if I want. So let's say I want this particular shade over here, or maybe green or something. Then I even I can go ahead and change the colors. Okay, so this is how you can go ahead and add emojis to your dataset or to your dashboards. Someone told you that one of the coolest way of going ahead and representing your numerical data is adding data bars to it. So I have this sales data and I go ahead and select the Data. Click on my Home tab, go to conditional formatting, go to Data Bars, and I go ahead and select data bars over here. But then I observed that in some places, for example, for Brazil, China, Hoffman, my data bar is coming over my numbers and this really doesn't look cool. Okay, I want to go ahead and fix this. So how can I go ahead and do that? Very simple. What I'm gonna do is I will once again select this entire data range, go to my conditional formatting. Click on Manage Rules. Okay? Then I will select this particular rule and then I will click on Edit Rule because this rule has already been applied to my dataset. I will click on Edit tool. And over here you will see this particular option that says minimum and maximum. And by default, automatic is the setting that has been selected. What I'm going to do is I will just click on the drop-down over here and I will change it to number, and I will keep my minimum to 0. Then I will click on the maximum, and then I will change it to number once again. Now, I have to go ahead and give the maximum value. Now over your 48 thousand is my maximum value. So let me just go ahead and add a maximum value over here, something like 65 thousand. Okay? And then I will go ahead and click on Okay, click on, Apply, and then click on Okay. And now you will see that my data bars are not overlapping with my numbers. So it looks a little cool. And you can even go ahead and change the value from 65 thousand to maybe seventy thousand, eighty thousand, till the time you are satisfied with the look and feel of your dashboard. So I hope you like this one. Let's move on to our next one. Now, whenever you're entering data in an Excel sheet, excel does not pop up those wiggly lines that tell us that we have made a typo error, we have made a spelling error. However, there is a shortcut that we can use in order to go ahead and run a spell check on our dataset. All I have to do over here is I have to just go ahead and click anywhere on my dataset over here. And then I will press the F7 key. Okay? And now it will pop up this paycheck menu for us, wherein we can just go ahead and run a random spellcheck and go ahead and rectify any typos or spelling errors that we might have made within that particular data. Okay, let me just select this and then let me just press F7. So it is telling me I have made a spelling error over here so I can just go ahead and fix this. Okay. Fix this one. Then it tells me that the spell check is complete and you're good to go. Okay, so this is how you can go ahead and run a spell check within your Excel data sheet. Now, let's say this is a particular data that you want to go ahead and present it to a particular audience. But then while presenting this particular data, you don't want to go ahead and show this particular chart. Okay? Now, you would say, Okay, then let me just go ahead and delete it, or probably just go ahead and paste it somewhere else. But Excel gives us a very wonderful feature wherein we can just go ahead and hide certain elements on our datasheet. Now in order to go ahead and hide this particular chart, all I need to do is just click on my Home tab over here. And then I will come to the extreme right-hand side where it says Find and Select. And then I will click on selection pane. Now when I click on selection pane, it shows me all the different elements that are available on this particular worksheet. Now when I click on this particular chart, it tells me that the name of the charges charged three. Okay. And you will see this particular icon over here. If I just go ahead and click on it, you will observe that my chart disappears. When I click on it one more time, my chart appears. Okay, So if at all I want to go ahead and hide this chart, I can just hide the chart and then close the menu and the chart won't be visible. If at all I want to bring the chart back. Once again, I will go back to my Find and Select, click on selection pane. And then I will just click on this particular icon over here and my chart is back. This is one cool trick to go ahead and hide certain elements on your worksheet. Just in case you want to go ahead and hide them for certain presentation or for certain audience. So I hope you enjoyed this particular lecture and I shall see you in the next one. 9. Get Web Data, Conditional Formatting Trick, Remove GETPIVOTDATA, Randomize Data and Using WEEKDAYS: Hey, welcome back. Now many a times what happens is we have to go ahead and pull up some information from the Internet or from the web. And then we have to base that information in our Excel sheet. For example, if I'm looking at this Wikipedia page over here, I have the list of all the countries and their population over you. So I want this particular table to be pasted in my Excel sheet. So you would say, okay, then I would go ahead and copy this, and then I would go ahead and paste this. But let me tell you if the information is too much. One, Excel is going to take a lot of time to go ahead and paste this information. And secondly, it will be pasted in a manner where in you will have to go ahead and manually format each and every column and row to fit the data properly. However, there is a quick way of going ahead and doing that. What I need to do is I will just go to my Excel sheet over here. Then I will select any particular cell, and then I will click on data. Then I will click on from the web. Now it is asking me what is the URL from where I want to go ahead and pull that information. So what I will do is I will go back to this particular sheet over here. I will copy this URL, come back to my Excel sheet, and I will paste this URL. And then when I go ahead and click on, Okay, Let's see what happens. Now. What Excel is doing is it is reading the information from the particular URL that we have just provided. And then it will pop up some options for us. Let's wait and watch. Okay, so now Excel says that this particular page has a table. If I click on that table, you will observe that. Okay. Is this the information that I'm looking for? No. I will go to another option over you. Is this the information or the table that I'm looking out for? Yes. This is the table that has the name of the countries, their population. So what I'm gonna do is I'm just going to select this particular table over here. And then I will click on load. Now, Excel is pulling up that information and it has gone ahead and pull that information from the web and pasted it in an Excel format for us. And now we can go ahead and play around with this data and do any kind of analysis that we wish to. So this is one of the quickest way of going ahead and pulling up any information from the web when you need it. Now let us look at another scenario for you. I have this data over here in my category section. I have furniture. Now let's say I want to highlight only those row wherein the category is furniture. Now if I try to do this manually, this is gonna be a cumbersome task for me. However, within conditional formatting, we can just tweak the formula and get the required output. Now, I'm interested in getting all those rows highlighted there. My category is furniture. So what I'm gonna do is I'm going to select all this data we're okay. Then I will go to the Home tab and then click on conditional formatting. And then I will click on New Rule. Okay? And under new rule, I will select this particular option that says, use a formula to determine which cells to format. And once I click on that, it is asking me the description. So I'm gonna go ahead and click over here. And I'm going to say, Okay, I want my category equal to furniture. Now, before I go ahead and do that, I need to log the columns. For that. I'm going to go ahead and press the F4 key twice. Okay? And now you will observe that my columns are locked, but my rows are not locked. Okay? And then I'm going to say is equal to give double-quotes. And then I will type furniture. You have double-quotes again and hit Enter. And now it will ask me what format do I want to go ahead and apply. So I will click on Format, click on the fill option, and then I will say, Okay, let's highlight all those cells with this light green sheet. And then click on Okay. Click on Okay one more time. And now you will observe that all those rows where my categories furniture has been highlighted. So this is one cool conditional formatting trick. Now you have it in your kitty. Let's move on to our next tip. Now let us look at this particular data value. Now, I have this sales data over you. And now I'm selling two different products, product one and product two. Now, based upon the employees that I have, I have gone ahead and created the sales report for each and every employee for my product one and product two, I have gone ahead and created a pivot table for that. Now this particular pivot table gives me the sum of product one and product two for each and every employee. And now I'm interested in going ahead and adding these two values so that I get the final total or the final sum. Now if I try to go ahead and use the formula where you will observe that long GETPIVOTDATA formula comes over here. Now the disadvantage of get pivot data is let's see if I go ahead and hit Enter. It will pick up the value from this particular cell. But now if I try to go ahead and drag this formula, you will observe that because this is not a relative reference, it is going ahead and pasting the same value from me in all the cells that I'm dragging. Now I want to go ahead and fix that. I want to fix two problems. One, I need a relative reference. And secondly, I don't want to see this long GETPIVOTDATA information in each and every cell. Okay, So how can I go ahead and fix that? Okay, so first of all, let me just go ahead and delete this. Now, what I'm gonna do is I'm going to select my pivot table over here. And then I will go to this particular tab that says PivotTable Analyze. And then towards the left-hand side of my screen under the Options section, I will click on this particular option. That's it, generate pivot data. As of now, it has a checkmark on it. I'm going to uncheck it. And now if I try to go ahead and say equal to this particular cell, you will observe that it picks up a relative reference. And now I can even go ahead and drag this formula and it will pick up the value accordingly. Okay? And now I can go ahead and sum the two values. So I'm gonna set this plus this and hit Enter. And now I can just go ahead and drag this formula, okay? And I will get the sum for both my products in one cell itself. Okay? So this is how we can go ahead and remove the get pivot data and then go ahead and apply whatever mathematical formulas we want to go ahead and apply it to our existing pivot tables. I hope you enjoyed this trip. Let's move on to our next one. Now. I have a list of around 50 employees. Okay? Now, my boss comes up to me and says for any reason, I want to go ahead and change the sequence of these names every week. Okay, So for example, when Perez is at number one right now, maybe next week, he might be on number ten or number 15 for whatever reason, maybe for rostering peppers or whatever. So I want to go ahead and randomize these names. Now I can do that manually, but let's say if it has thousands of names, than doing this manually is gonna be quite a task. Now how can I go ahead and fix this? Very simply, what I'm gonna do is I'm going to just go to the next cell next to the names over here. And I'm going to type R-A-N-D for random, okay? Open brackets and close brackets and hit Enter. Now what it does it, it will produce random digits between 01. Okay? I will just go ahead and copy this. Okay? And now you will see that it has gone ahead and populated random numbers for all the cells which are next to my name's over you. And now all I need to do over here is go to Data and then click on. Okay. And you will observe that the names get randomized. Another alternative is pressing the Alt key, press the a key on your keyboard and then press S and a and hit Enter. Okay? And you will observe that it will just go ahead and randomize the names. So just in case you want to go ahead and randomize certain data on your data sheet. This is one cool trick that you can apply. Now let's say I have a set of dates over you. And I'm interested in knowing what was the day of the week on this particular day. And then I also want the month. Okay. Now getting the month is not so difficult. But then getting the weekday for a particular day is going to be quite a task. So that is a quick way of going ahead and accomplishing it. For a week day. What I'm gonna do is I'm going to type text. I will open brackets. I'm going to select this particular data where you give a comma, open double-quotes. And since I want the week day, I'm going to type di, di, di, di, give double-quotes and then close the brackets and hit Enter. It tells me that on this particular date it was a Thursday. Similarly, if I want the month, then what I need to do is I just need to type text. Open brackets, select the date, give a comma, open double-quotes. And now I will type M, m, M, m four month, okay, So n, n, n, n, this will return the month for that specified date. I will close the bracket and hit Enter. So now it tells me that it was month of February. If I go ahead and drag this, will give me the month for all the dates that have been specified. So this is how you can get the weekdays and months for a specified date within your Excel sheet. So I hope you enjoyed this particular lecture and I shall see you in the next one. 10. Replace All Errors, Using Proper Command, Autoformat, Split Header Cell and Color Code Numbers.: Hey, welcome back. Now, many a times, Excel sheets that have mathematical calculations will show these kind of errors. Okay? Now, many a times before we go ahead and present the report, we have to go ahead and remove these errors. So either we change it to a 0 or maybe we just change it to a blank cell. But then let's say if at all I have to do this thing manually, it is going to be quite a task. There is a simple way of going ahead and handling that. What I can do over here is I can just select any cell within this particular dataset that I have over here. Press Control a. So my entire dataset will be selected. And now what I'm gonna do is I'm gonna go ahead and press Control G. Okay, this will pop up the go-to menu. So I'm gonna go ahead and click on special. And then I will go to Formulas. I will just go ahead and uncheck numbers, text, and logicals. And I'm only going to keep a check mark on errors. Okay? And then I go ahead and click on, Okay. You will observe that only my errors are highlighted. Now, I'm gonna go ahead and press the Control key, select one of the cells over here. And then I'm going to type 0 over there. Okay? And then I'm just going to go ahead and press the Control key one more time and hit enter. And you will observe that all the cells where there was an error is now being replaced by a 0. Okay, let's try something else. Let's say I go ahead and select this particular cell over here. And then I go ahead and press the backspace. So I just create a blank silhouetted. Then all I have to do is press Control and then hit Enter. And all the cells where there was an EDR are now replaced with a blank cell. So this is a quick way of going ahead and removing errors in your data presentation. Let's say you get this kind of data in front of you, wherein you have names of certain people or maybe your employees or whatever. Now you have been assigned a task of going ahead and cleaning this data and putting it in a proper format. Now when I say proper format, what I mean is only the first alphabet should be in capital, and then the rest should be in small letters. Okay? So for example, if I'm looking at Ben Perez, then be for Ben should be capital and then P for parish should be capital and everything else should be in small letters. But since we are going ahead and learning how to put it in a proper format, let us also understand two more scenarios. Let's say I want to go ahead and convert this name into uppercase. So what I'm gonna do is I will just use the upper command, open brackets, select the name and close brackets, and you will see that everything has been converted into uppercase. All I would need to do now is just drag and drop. Okay? Let's say I want everything in lowercase, then I will just go ahead and use the lower command. Open bracket, select the name, and then close the bracket and hit enter. Okay, So I got everything in lowercase, and now I'm looking to put everything in a proper manner. Okay, veteran only the first alphabet will be capital and everything else will be in small case. So I'm gonna go ahead and use the proper command over you. Open brackets, select the name, close brackets and hit enter, okay, and then just drag the data. Okay, So this is how you can go ahead and put the data in uppercase, lowercase, or uppercase. Now let's look at this particular scenario. Let's say based upon this particular data, I've gone ahead and created this particular report. Okay, maybe I used tools like PivotTable and created this particular report. So I have statewide sales of all the categories that I have. I have furniture, office supplies, technology, and then I have the grand totals. Now let's say I want to go ahead and present this data to someone. Then I will have to put it in a nice format. Now, putting it in a format will be a manual task. But how if I tell you that there is a shortcut way of going ahead and doing that. All you need to do is press Alt O and a key on your keyboard. And this will pop up the auto format venue. Within the auto format menu, we have various ready-made format that we can use to go ahead and present a report. Let's say I go ahead and select this particular format, and then I go ahead and click on, Okay. And you will see that immediately the format of my report has changed. If I want another format than what I need to do is just press Alt and then select another format. Let's select this one, then click, Okay. And you will see that the format changes automatically. Okay, So this is one handy trick. Good to know information, just in case you want to go ahead and do a quick formatting of the data that you have at your hand. Now, this is going to be a very simple one, but a very handy been many a times we go ahead and present our data in this particular format. Okay, So I have the products and then I have the day's over u. But then we are always confused. How do we go ahead and put the day and the product in this particular section, which is the intersection of the rows and the columns. So then here is a quick fix that can come really handy. Just select that particular cell, which is the intersection of your rows and columns. Then under the Home tab, go to the Borders menu. Ok, click on the drop-down over here. Then go ahead and click on more bodies. Then over here, what you can do is you can go ahead and select this particular option over here, and then click on, Okay. So this will go ahead and put a line across that particular cell. Okay? Now that being done in the upper section of my cell over here, I want to go ahead and mention this. So I'm gonna go ahead and select this particular cell. And I'm going to say days. Okay? And then at the bottom, I want to go ahead and mention products. So for that, what I'm gonna do is press the Alt key and enter key. And now I will say products. Okay? And now all I need to do is just adjust this a little bit. So I'm gonna go ahead and hit space. And with that, I have gone ahead and added the days as well as products in this particular cell, which is the intersection of my rows and columns. So this is how you can go ahead and split the header and then add your respective rows and column headers in one single cell itself. Now, before we go ahead and end this particular lecture, how about learning this cool trick? But you can go ahead and represent your numbers in emoji format and at the same time, ensure that all your positive numbers are represented in a green shade, whereas all your negative numbers are represented in red. Here is the cool trick of going ahead and accomplishing it. So all I need to do over here is just go ahead and select this data. Right-click on it, click on Format Cells, then click on the Number tab, and then click on Custom. Okay, over here, where it says Gender, just go ahead and delete it. Open box brackets and type, color. The number ten. Close the brackets, and then type 0, and then press the windows and the dot keoyo that we'll go ahead and pop up the emoji menu for you. So now over here, select thumbs up. Okay, Now go ahead and give a semicolon over you. Then open another box and then type color the number three. Close the box. And now go ahead and type a minus, then a 0. And once again press the windows and the dot key, which will pop up the emoji menu once again. And now go ahead and select the thumbs down. Emoji, Yo-Yo. And now let's go ahead and hit Okay. And with that, you will observe that all the values that are positive are showing in a green shade with a thumbs-up. And all the values that are negative are showing in red with a thumbs down. Cool trick, isn't it? So I hope you enjoyed this particular lecture and I shall see you in the next one. 11. Add and Remove Blank Rows, Visual Sales, Timestamps and Lookup Command: Hey, welcome back. Now let's say one fine morning your boss comes to you and says that this is the data that we have at our hand. And all I want you to do is go ahead and add a blank row after each entry that is there in this particular dataset. So you would say, okay, so let me just start doing this and I will start inserting one blank row at a time. Now, looking at this data, definitely it is going to take you some time. But if the data is running in thousands, then how much time is it gonna take? You would probably end up spending your entire day doing this simple job. And yet is a trick that can come really handy to get this thing done in a few seconds. All I need to do over here is just add a helper column over here. And I will just go ahead and add numbers over you. Okay? So I will just select this and then I will double-click on it. Okay, so I have numbers 200. What I'm gonna do now is I'm going to copy these numbers. Then I will paste it one more time over u. Ok. And now what I will do is I will select this entire row where you go to data. Then I will click on Sort. Click on Sort one more time. And I will say sort my data on the basis of helper, okay, from smallest to largest. And then I will click on Okay. And you will observe that now all my numbers have been sorted. But because of this sorting, there is a blank row that has been inserted after every entry. Now, all I need to do over here is right-click and delete this helper column. And my task is done. I have gone ahead and added a blank row between each and every entry that I have over here. Cool, isn't it? Let's move on to our next tip. Now here is a quick visual sales report that I have gone ahead and created for a restaurant. Okay, let's assume that the restaurants cells, B cells and burgers. And I have gone ahead and created this small report over here that gives me a head-to-head comparison between the sales of pizza and burgers without actually using any charts. So how did I go ahead and accomplish this? Let's go ahead and do that collectively. And now what I'm gonna do is I'm going to make use of the repeat function over here. So I'm going to say repeat open brackets, double-quotes. And then I'm going to type a pipe. The pipe is basically along with your backslash key. You press the Shift key and press the backslash key and that will produce the pipe and then close it with double-quotes, give a comma, and now select this particular value here. So I wanted to repeat the pipe number of times as the value of the pieces. So I'm going to go ahead and close the brackets and hit enter. Okay? And then I will just go ahead and drag this. And now let's go to the Home tab. And then I will select a blue shade for this. Okay? Now similarly what I'm gonna do is for my burgers, I'm going to say is equal to repeat. Open brackets, double-quotes, pipe, double-quotes, give a comma. And then I'm going to select that number for burgers. Ok? And then close the bracket and hit Enter. And this particular value, I'm gonna go ahead and change the font to read. Okay, and then just double-click and expanded. Okay? Now what I need to do over here is I will go ahead and select both these values over here. And now what I will do is I will go ahead and change the font to label. Okay? Now just double-click over here. So both these columns fit together. And now what I'm gonna do is I'm going to select this particular column over here. And then I will just go ahead and align it to the right. Okay, That's it. And my visual chart is ready. Okay. Now looking at this chart, I can go ahead and do a head-to-head comparison of my pizza and burgers sales for each and every employee. Quick way of going ahead and creating a visual sales report. Cool, isn't it? Let's say every day you have to go ahead and create a report. And at the end of the day you have to go ahead and add a timestamp. So you have to actually go ahead and manually type the date and the time at which this particular report was created. So actually you can go ahead and use a very quick shortcut that can do this job for you. All you need to do over here is select the cell where you want to go ahead and paste the date and just press Control. And the semicolon. You will observe that the date has appeared. It picks up the date from your system date. Actually. Now I want to go ahead and paste the time. So what I'm gonna do is I'm going to press Control Shift and the colon and hit Enter. So it picks up the time for my system time and prints a timestamp in the selected cell. So this is how you can go ahead and quickly add timestamps to your reports. Now in the previous lecture, we saw how we go ahead and add blind grows to our Existing database. Now over here, we have an altogether different problem. We have a lot of blank rows in between our data. And our objective is to go ahead and delete these blank rows in one go. So how can we go ahead and achieve that? So what I'm gonna do over here is I'm gonna go ahead and select this entire column over here. Press the F4 key on my keyboard. Click on special, and then I will select blanks, and then click on, Okay. And you will observe that all the blank cells within my selection are highlighted. Now all I need to do over here is go to the Home tab. Go to the delete button over here. Then click on this particular option that says delete sheet rose. And you will observe that all the blank rows have been deleted. Let me just go back. And let me also show you a shortcut of doing this. So what I can do is I can press the Alt key, press the d key and the Alt key on your keyboard, and it does the same thing. So this is how you can go ahead and remove blank rows from your database. Now here is a quick look-up tool that I want to share with you before we go ahead and close this lecture series. Now let's say that this is a database of students who have appeared for a certain test. Okay? These are the percentages code by individual students. Now based upon this particular grid, I want to go ahead and give them grades. Now how can I go ahead and do that automatically? Very simple. What I can do is I can use the lookup function, wherein I can just say is equal to lookup. Then I will select this particular value because all my grading will happen based upon the percentage is code by the student. And then I will go ahead and give a comma. And then I will select this particular row by pressing the Control key. Then give a comma. And then I will press the Control key and select this particular column as well. And then just go ahead and close the bracket and hit enter. Okay, So now you will observe that based upon the percentage criteria, the student has been given a grid. Now, all I need to do is within this particular formula, I need to go ahead and freeze the rows and columns. And for that, what I'm gonna do is I'm gonna go ahead and select this particular selection. Press F4, to freeze the columns and rows. Okay, you will observe that the dollar sign has appeared before the columns and rows, which means they are frozen. And now I will hit Enter. And now I can just go ahead and drag this formula. You will observe that just based upon the percentages, exit has gone ahead and allotted the grid to the respective students. So this is a quick way of going ahead and using a lookup table as and when time permits, I may go ahead and add new stuff to this particular course as well. So I hope you enjoyed this entire lecture series and learned a lot of new stuff. Happy learning stabilised and God bless you.