Comprehensive Guide to Data Analysis and Visualization in MS Excel

According to Forrester Research, more than 81% of the businesses use MS Excel. MS Excel is one of the crucial tools for a data analyst.

Excel provides us with all the features and functionalities through which we can clean, transform, model and analyze data.

And lastly, we can get some valuable insights. It has a lot of different features. For example, we can use Power Query to transform data.

We can use Power Pivot to model data. Lastly, using Pivot Tables and Charts, we can create amazing dashboards in MS Excel.

That's why WS Cube Tech has brought a complete free course in which we will learn how we can analyze and visualize data using AI-driven features.

This course covers all the topics from beginners to advanced. Also, if you want us to bring a free course on Analytics

with MySQL, you can comment in the comment box and show your support. And soon we will bring a playlist of Data Analytics with

MySQL for you. Also, if you want to become a successful data analyst and want to learn live data analytics from our experts, you can

call on the given number or fill the form given in the description. Not only that, you get 2 demo classes for free with each of

our patches. So let's get started. MS Excel is one of the amazing softwares.

And in today's video, I am going to give an introduction to MS Excel and a beginner's guide which will help you to get started with MS Excel.

So hello everyone and a very warm welcome to WsCube Tech. My name is Ayushi Jain. And from today, we are going to start a complete new

journey on the course of MS Excel. In this entire course, we are going to learn different functions that are used in MS Excel, different formulas

that we use in MS Excel, how we can create charts, how we can analyze data, we will learn about the pivot table, we will discuss different topics in this entire course.

And in today's video, we are going to talk about the introduction to MS Excel, and how we can start working on MS Excel.

So let's get started. First of all, all we need to do is go to the search bar and write Excel.

As soon as Excel opens, we have to go to the blank workbook. As soon as the blank workbook opens, a page like this will open here.

Now, let's go from top to bottom one by one. First of all, let me talk here. On the top, we have Home, Insert, Page Layout, Formulas,

Data, Review, View, Help. And if we select more things in the future, a few more new columns can also be created here.

So this whole area is called a ribbon here. In the ribbon, you will get different tools for everything. Like in Home, you will get data alignment tools, formatting

tools, there are tools for insert, delete, and format here. If you want some general formulas here, you will get them in the case of numbers here.

If you want to edit cells, we have an option here. Basically, we have a ribbon of Home. In Insert, again, we have some new icons.

Suppose if you want to insert a table, if you want to insert a picture, shapes, icons, or if you want to create a pivot chart, there are many such options here.

Similarly, in Page Layout, Formulas, Data, Review, View, we also have different tools that will help us throughout the course.

So on the top, we have a ribbon. Just after the ribbon, you will see that we have an option here where I am typing anything.

Whatever I am typing, it is being reflected in the cells below. This area is called a formula bar here.

So the second thing we have here, basically, if we want to give a formula to any column, if we want to give a formula to any cell in which we want to get some value, then we

will do all those things in the formula bar. We can pass formulas here. Moving ahead, the next thing over here is, like I select

the same thing, so here you will see that a whole grid has come to me. This whole grid is called a spreadsheet grid.

So all the things that I have selected here, even including the first cell, all this comes in the spreadsheet category here.

At the bottom, you will see that we have sheet 1. You can create multiple sheets here by clicking on this plus symbol.

And if you want to delete it, all you need to do is right-click on it and click on Delete. Again, right-click on it and click on Delete.

Just below that, you will see the status bar here. In the status bar, we have the option of zoom in and zoom out.

So let's keep it on 100%. If you want to zoom in and zoom out the cells here, then we can use the status bar below for that.

Also, if you have a table, you have to show the sum of the table, you have to show the average, you have to show the count of values, then all those things will be easily

visible in the status bar. On the top, we have the save button. You can save your worksheet by using Ctrl-S.

Or you can use this button here to save. As soon as you click on Save, you will be redirected to this page.

You have to save some data somewhere on your desktop. And here I have a folder named GNSX. If I want, I can save it here.

So in this way, we can save the data here. Coming back, to come back, there is a back arrow here. You can come back to your spreadsheet from here.

Now we have to talk about data entry here. You can start entering data from anywhere for data entry. It is not necessary that you start from here.

If you want, you can pick anything from the center. On the top, if you want to give any heading here, like these are your monthly expenses.

So, like I want to give here, January month expenses. So I can give it here like this. If I want to increase its size here, then I can increase

the size. Like if I want to increase its width here, I can increase its width like this.

Suppose you want to give any color here, then you can give color here. For color, I can just select this cell.

Let's go to home. Here you can see the paint option. Whatever color you want to pass here, you can pass that

color here. You can change the text color here. You can change it here.

Suppose I'll go with white. Again, if you want to increase the size of the cell here, then you can increase the size of the cell here like this.

So the size of the cell will increase here. So suppose you want to merge it here into different cells, then you can start selecting from here.

You will get the Merge in Center here. You can click on Merge in Center here. You have to increase the text size.

You will get the text size here on the top. So, January month expenses. I want to create a sheet here like this.

In this, we will start with providing the headers. First of all, let's take the date here. After the date, you have to move to the next cell here.

You can also use the right arrow key or you can also use the tab here. So, in the next arrow, we can take it here that you can

give my expense category here. In which category did I spend here? Plus, I have to give subcategory.

For example, I gave food here. I ordered food from Zomato here. Or I paid by going to the restaurant.

How to give subcategory here? So, you can mention it here. How much did I price it here?

And lastly, payment mode. I also want to see how much I am doing transactions with UPI, how much I am doing transactions with cash and how

much I am doing transactions with cards here. So, I can add payment mode here. Now, you will notice here that what is happening to me here.

For example, this is the date. This is the subcategory. Subcategory is not able to take up the space completely

here. So, in that case, I will go to E here and double click. Now, you will see that the subcategory is fixed here in the alignment.

Same goes with payment mode. The cell of payment mode has increased according to its width.

So, we can do these things here. In the case of date, I will zoom in a little bit. So, in the case of date, we start from the date here.

Also, you will notice that as I am choosing cells here, I am getting C here. C is in the column and 5 is the number of rows.

Which number of rows are we in here? Again, let's zoom in a little bit. So, let's start with the date.

First of all, I mention the date here. For example, I started here from 1st January. 1st January 2023.

After that, we mention the category here. Let's say my first expense was on food. I ordered food from Zomato.

I paid the price, let's say 300. And the payment mode was UPI. So, my data went into the first category here.

Again, you can see the alignment of the data here. 2023 is hidden here. Let's double click here.

So, you can see that the width of the cell has been adjusted according to your data. Also, you will notice that some things are aligned towards

the left and some things are aligned towards the right. So, you will notice that the numbers are aligned towards the right in the numbers category.

Else, the data in the text format or the date format is aligned to the left. So, you can also change the alignment here.

For example, you can pick any cell here. If you want the center alignment, you can choose the center here.

Moving ahead, I will talk about one more thing here. I will zoom out the data. You can't understand the headers or the table content by

looking at the data. So, in that case, if I select 4, it will select the whole row of 4.

Whatever changes I want to make in this row, let's say I want to increase the size of the text. So, I can increase the size of the text here.

I want to keep it bold so that it looks a little different. So, I can keep it bold. Now, it's a little better to understand here.

Let's increase the size here. And the rest of the data is the content inside the table. So, that is the table data.

Similarly, you can start inserting data inside the next row. For example, I want to write 2nd January here. 2nd January, 2023.

And then again, my expenditure was on, let's say, grocery. In the grocery, let's say, we bought fruits and vegetables. So, fruits and veggies. I will mention here that the price

that I have paid is let's say 400 and because it was a fruit vendor, I have paid it in cash. In this way, you can add data. Suppose you want to add a

row above here, then we have a shortcut for that too. Suppose I select it here, I will choose Alt-I and Alt-R, then I will have a row inserted above the row which I had

selected here. So if you want to add more data here, you can add it here. The same goes for column. If you want to choose a new

column for the column, then you can use the symbol Ctrl-Shift-Plus to add a new column. And if you want to undo this, then you can use Ctrl-Z to

undo it. Also, Excel provides you with a very good function that if you know that it is tabular data, then as soon as you

select it here, then you can press Ctrl-T, which will convert all your data to a table. In this, you can say that my table has headers, that is, it

will read the first row of your table as headers. And as soon as I click OK on it, I have data in a table format.

As soon as the table is created here, we have a lot of options at the top right. You can choose any color you want here and you can go with

the color that you already have. Which is a similar color from your January month expenses. So you can choose different categories here.

Another way to add a table was that you do Ctrl-Z here. So whenever you want to undo, press Ctrl-Z, select the table here and then press OK again.

Again, your table will be created here. So both the methods work over here that you can also use Ctrl-T to create a table.

Or you can directly go and click on Insert and go for Table. Lastly, we will click on Ctrl-S to save it. And wherever you want to save it, for example, I want to

save it in the datasets as January data. So as a January data, I will save it. And then whatever changes you want to make, you can save it

by using Ctrl-S here. The first function that I am going to discuss here is Autofill.

Autofill, as the name itself says, automatically fills the values in the order in which you are arranging it. For example, if I have numbers here.

If I start from here, for example, this is 1. Let's say this is 2. Now all I have to do here is drag it here by holding the

Ctrl key. So I will get the values here. So it increases the values here by understanding the

pattern. Not just numbers, if I talk about dates here. So last date is 15.

Suppose I am dragging it. Here I am dragging it by pressing the Ctrl key. So it is giving me 30th January 2023.

The data up to this point will easily open it for me and make fields like this. So guys, this is what Autofill is.

You can use Autofill on numbers. You can also use Autofill on dates. Moving ahead, let's talk about the second function here.

The second function of Autofill is FlashFill. Let's understand what FlashFill is. Similarly, the work of FlashFill is to understand the

pattern here and fill that pattern. Suppose I don't want the full name here, but I want the first and last name here.

So what can I do here? I can write John here. So I will write John here.

Maybe I will start writing Eugene here. You will see that this FlashFill is coming from FlashFill. All the first names are here.

Same goes for Yang. If I write Yang here. And I write H-U-A-N-G here.

You will see that the surnames are here automatically in every column. Now I can categorize this as first names and last names.

So first name and this one is last name. Let's set the size. And here we are done.

We don't have to sort them separately. If you want a small method, that is FlashFill. Suppose you don't have an option here.

In that case, you can go to Fill. In Fill, we have the option of FlashFill. You can choose and use FlashFill here.

And I am able to do it directly. In your case, you can go here and enable it. So this was the second basic function that you should know.

Moving ahead, let's talk about the third function here. The second function here is not just names. But if I talk about January here.

Now I had to take the data of January in such a way that I had to write January here. Let's say I just wanted first here.

Because I have written first here. Again, I want first here too. So you will automatically see that I will get suggestions

here. And I write second here. As soon as I press enter, all the dates that I have written here will come here.

So it works with text. It works with dates. Whatever pattern you see, whatever pattern Excel

understands here. It can FlashFill with that pattern. So I don't need it.

So I take control of it here. Moving ahead, let's talk about the third function here. The third function that we are going to discuss here is

text-to-columns. Text-to-columns means that you have data in some text format here.

Like I have comma separated data here. It is such a sheet in which you have electronics and devices.

And in this way, whatever categories are written here, which are written in a paragraph. You have to separate it into columns.

Then how you can do it is very simple. Suppose this is the data. Now I will choose the entire column here.

In the data, I have the option of text-to-columns here. Delimited means that it is separated from something. So here this comma is separated.

After delimiting, I will click next here. Next, you will see here that I have the option of comma chosen here.

As soon as I choose a comma, I have Afghanistan in one column and its abbreviations in the other column. And now let's click next here.

Next, we have to give a general here, we have to give a text here. We can also define this here.

What will be its destination? We can choose the destination here. So the destination would be, let's say, E column.

And again, let's increase it. And lastly, I finish it. So you will see here that I have some data.

Here are country names and abbreviations. Now we remove the abbreviations from the country names here. And we add abbreviations here.

So sorted. How easily we have converted any text into columns here. This function is very basic.

They can do literally wonders. If you had to do this one by one here, then it would have been a long task.

So guys, formatting the data here is very simple in Excel. Like if you want to format the data on the basis of anything, as of now, if I start here first with the font.

If we talk about font here. So I have to change the font of the cell here. If you want to increase the size, suppose this is a header.

I have to increase the size of the header here. So particularly, I have to increase the size of this here than this.

And if I want to increase the size of this whole thing, in that case, I will select this whole cell. And whatever size I want here, I will mention it here.

Again, let's define their width here. So here you can mention the sizes. If you want to apply any color to them, you can give a font

color. If you want to give a different font style to all of them. That they have a slightly different font style.

So I'll go with this one maybe. So in this way, we can give font style here. Similarly, if you want to bold something, if you want to

italic, underline, double underline. We also have these methods. If we want to give a border to something, for example,

particularly I have to give a border to this cell here. Then give a border at the top, bottom border, left border, right border, no border, all borders.

So if I have to give all the borders here, you can see that I have borders here. If I have to undo this, all I need to do is press ctrl z.

If I select them and then give a border here. Then I can do the same here. I can only give a border on selected cells here.

Okay. I need all this data here in bold. So again.

Although for now we have already set the font size here. But if you want bold and italics, you can do that here. And normal data.

If I talk about bills, let's make it bold. So you can see that it is bold now. Same goes with italics.

There will be a line here in the underline. In the case of double underline, we will add double lines here.

So we will remove all of them from here. Okay. We also have text color and font color.

Moving ahead and let's talk about their alignment here. If you want to set an alignment of a cell, you can definitely set an alignment.

Like I am talking about the category here. Now I want this bill to be aligned in the center. So all we need to do is pass the center alignment here.

Okay. If I want that I want this left or right alignment. So we can also take the right alignment here.

Again, all you need to do is select this. And right alignment, left alignment, center alignment. On the upper side, we have the alignments inside the cell.

In the cell also, you need data on the upper side. You need data on the center side. You need data on the down side.

So these are the different alignments. Also, you will notice here that I have bills written in these three cells.

So if I want, what can I do here? I can select these three here. Similarly, you can see that we have bills inside these

three. So if we had something like this here. I have bills.

Bills. And there are bills. Okay.

Now this is data inside the table. That's why we cannot merge and center it. But if I choose this.

Now if I click on Merge and Center. If we click OK, you will see that it has come to Merge and Center.

If I want to keep it in the center, we can keep it in the center. We have to give left alignment and right alignment.

We can also do that here. So this is all possible with the help of alignments. Moving ahead, if I talk about the amount here.

So in the amount, if I want to add their currency here. So all I need to do is select this. Here we can pass currency.

That rupees in Indian currency. If we had value in percentage style, we can pass that. If we want to increase or decrease the number of decimals,

then again you need to select the cell, decrease the number of decimals, if you want to increase then you can increase the number of decimals as well.

If we open this, then you can increase the number of decimals. All you need to do is click on this arrow over here.

This is how we can increase the number of decimals. Also, you can see here that we have different categories, general number, currency, so basically these are the

different types of data types which are in our per column. So, if we want to handle them, for example, let me talk about the date here.

For the date, let's open it here. Let's go to the date. For now, our date is 12 January 2023.

Now, it is coming in this format. If you want it in any other format, for example, you want the whole date to be written on it, then you can click on

OK, then the whole date will be visible here. Similarly, if I go back here, if I want the date in short, I want the date in the format of year, month and date, all

these things are possible with the help of formatting cells. So, let's take it in short, I click on OK, so I will get the date here in short.

Sorting data according to your priority is very important. And in today's video, we are going to discuss what are the different methods in Excel with which we can sort data.

So, hello everyone and a very warm welcome to WS Cube Tech. My name is Ayushi Jain and in this video, we will be talking about different methods to sort the data in Excel.

So, let's get started guys. So, guys, sorting data is very important according to your priority, according to your needs.

And Excel provides us with different methods, different ways with which we can sort the data. Also, it changes with the data types that we are handling.

For example, if I take a case here, suppose I have dates here. Now, I have the dates in ascending order here.

Suppose, I want to go with the descending order. If I had got these dates randomly here and I had to store it in descending or ascending.

So, all we need to do is click on any cell here. We will go to a home page. On the home page, you will see the option of salt and

filter on the right-hand side. And oldest to newest, if you want to go to the newest data from the oldest.

So, I will choose oldest to newest here. So, it will be sorted in that way. If I go to newest to oldest, then here 19th January is the

newest. And while 1st January is the oldest data. So, in that way also, we can sort it here.

In the same case, in the same table, you will see that I have the option of food. Let's zoom in a little bit.

So, in the same place, you will see the option of food here. In the option of food, the data we have here is in the text format.

So, again we have to sort it. So, if you want to sort it in A to Z ascending order. As soon as I choose A to Z, you will see not just this

column, but all the columns along it have been sorted here in the ascending order. So, B comes first.

That's why bills first, then clothes, then essentials, then food and lastly grocery. So, in this way, our data has been sorted here.

Same goes with amount. In the column of amount, I will choose if I want to filter the data on the basis of amount, then largest to smallest.

I want the lowest payments to come first. So, we will start from 10 here. Lastly, it will go up to 16,000.

And if you want or not, you should get the highest payment first. And then it will keep decreasing.

In that case, again you have chosen the cell. Select and sort. And here we will go to largest to smallest.

So, the highest values will come up first. And then it goes down to the lowest values. This is how basically it works.

Also, there are different methods on which you can do sorting. For example, you can sort the data on the basis of color.

For example, if you see in the payment mode, I have different colors here. For example, UPI color is a bit peachy.

And if I talk about the card color, the card color is a bit green. And blue one goes for cash.

So, I will choose this first. To choose, you have to go to this column. If you go up, you will see an arrow here.

As soon as I click on it, the entire column will be chosen. If I choose G, then the entire column will be chosen. But I have to choose this particular column in this table.

So, I will choose this. All I need to do is go on data. You will also see the sort option here in the data.

We choose this sort. After choosing the sort, we will get a pop-up here. In the pop-up, we have to sort by on whose basis you have

to sort. So, I have to sort it on the basis of payment method, on the basis of payment mode.

I have to do it on the basis of cell values. I have to do it on the basis of cell color. Font color is also an option here.

Or conditional formatting icon is also an option. So, font color is black here. But cell color is different here.

So, we will choose cell color here. In cell color, which one do you have to keep first? First, we have to keep the card on top.

If you want, you can also keep it on the bottom. And as soon as I click on OK, we will get the card on top. As soon as I click on OK, you will see the card on top.

But now you can also give multiple levels here. As soon as I select it again, I sort it. Here, the add level has come.

First, you have given that you want the card. Next, you want blue or peach here. So, we can also show that here.

Again, we have to choose the payment mode. Here, we have to choose cell color. Next, let's say I want cash here.

As soon as I click on OK, next, we have cash and lastly, we have UPI. So, we can also sort the data on the basis of color.

Now, not just this, but if you want, as we have sorted it on the basis of color, similarly, we can also sort two columns together.

For example, if I want clothes here, but I also want ascending or descending order in clothes. First, I have to sort it on the basis of category.

Let me show you after sorting it. Suppose, I want to sort this food here. Again, let's go to home, sort filter.

Let's put A to Z. Now, I have to sort the bills in ascending order. In bills, I have 1,074 first, then 1650 and then 16,000.

So, I want ascending order here. So, I have to put two levels of sorting here. So, for that, what will we do here?

Suppose, we will choose the sort again. In choosing, we have taken the category here. We have to go on the basis of category.

Cell A to Z should be there. This is our first level. Second level, what do we want here?

We want to get the amount here. We want to get the cell values in the amount and smallest to largest.

As soon as I click on OK, bills are here. In bills, we had 16,000 in the amount. But now, we have 1,074, then 1650 and then 16,000.

And same goes for clothes. We have clothes here. Here, we have 1,090.

We have 1,000 here because we have to add L in the spelling of clothes. And then, maybe I will just go for sort.

We have already sorted. Let's click on OK. 1,000 is above and 1,890 is below.

In essentials, the lowest value is 50. Then 120. In essentials, food and oil is 120.

Shampoo is 780. And same goes for food. In food, we will start with the lowest value.

The highest value is 890. In the table, you can also sort in multiple columns. We are using sort in two places.

On one side, we are taking sending order in categories on A to Z. And on the other side, we are taking ascending order on

numbers. So, these things can also work easily. Let's get started, guys.

First of all, I will open Excel. We will continue on the worksheet that we are already working on.

First of all, I am going to talk about filtering the data. To filter the data, we have different parameters. Also, we have different types of data filter.

For example, let's say I want data in payment mode. I want data only for UPI or only for cash. Here, you can see that

I can go to filter, then filter. We also have a shortcut that we can press CTRL, SHIFT and L. By pressing CTRL, SHIFT and L, you can see that filters

have been added. You can apply any filter here. For example, I want only cash data here.

I will discard the rest and press OK. And only cash data will be visible here. So, basically, this is how it works.

Similarly, if I want both cash and card data, then both cash and card data will be displayed here. Again, I will choose Select All.

I will press OK. So, all the data will be displayed here. Again, if you want to remove the filter, press CTRL, SHIFT

and L. Now, you can remove the filter. Again, we will choose a cell here.

Remember, as I have chosen outside, if I press CTRL, SHIFT and L, this will generate an error here that this can be applied to a selected range.

So, we have to choose any cell in the table. Now, we can press CTRL, SHIFT and L. In this way, the filter will be added.

Moving ahead, if I want to work on the text, the text is displayed here. Now, if I talk about the amount,

I have to work on the amount. Again, I can choose the filter that I want to apply here. You can remove Select All.

Now, what is the problem here? The problem is that I don't want to sort in ascending or descending, as you can

see here we have an option for sorting. So we don't want to sort but we want to filter. Let's say I want values above 1000, where I have shopped

above 1000. In that case, we can put number filters here. We can put greater than or equal to.

So greater than or equal to, I want to put 1000 here. As soon as I click on OK, all my expenses above 1000 will come here.

Like cylinder bill, mobile bills, house rent, dress or another dress. So this way I have data that the value is above 1000.

Again, we have to remove the filter. If you remove the filter from amount, the filter will be removed here.

Again, as I am telling you, let's click on this. Let's see number filters. So in number filters, we have a lot of values here.

You need top 10, you need a value between any range. Not equal to, greater than equal to, lesser than equal to. We have all these filters available here.

Similarly, now let's talk about text here. I would like to go into more detail in text. Like we talked about choosing from UPI Cash Card.

But if I open its drop-down, let's talk about text filters. I want text filters to begin with here. That is, whatever text I have here starts with C.

As soon as I click on OK, cylinder, tea and chocolate all start with C. So I will get the data of these three here.

Similarly, if I want to put any other filter here, I will click on text filters. You need ends with, you need contain.

Someone is getting double O, someone is getting double E. You need any such data. So we can put it here easily.

This is text filters. Let's clear it from here. And then let's talk about date filters here.

So we also have date filters here. If you want to put a filter on the basis of date. Now, on the basis of date, you need any date here.

Like we are talking about January here. But if you are dealing here in different years, in different months.

So you can easily get that. You need January data, you need February data. You need 2022, you need 2023, you need 2024.

We can add all these things here easily. So in date filters, we also have a lot of options here. Like you need someone's equal date.

You need before, you need after. You need tomorrow's date. So today let's say 11th of February.

So in 11th of February, you will not get tomorrow's date. But if I say here, next week, next month, this week, this month.

So how will you get all these things basically? If you are talking about the current scenario here, you need that.

You need the value of the last quarter here. You need the values of this quarter here. You need the values of this year.

You need the last year. So you need all the dates of January. Right now, we have this data of January.

So if you want all the dates of January. There are four quarters here. First three months, next three months, then next three

months. And lastly, last three months, we have data like this here. So we can apply a lot of types of filters here on dates.

And lastly, I am going to talk about the filter here. The way we shorted with color, we can also filter things by color.

Like I put it here, filter by color. I need a filter here. I just need the color peach.

So all the UPI payments will be visible. Similarly, again, I clear the filter here. I need filter by color here.

Green. So the green color column will be visible here. So this way we can apply different filters in MX Excel.

I will clear it here. You can also apply a filter here in the data. So I removed the filter here.

And if I put the filter again, the filter will come here again. If you want to clear it, you can clear it from here too.

Any filter that we have applied. And if you want to put the filter here again, remove it. So for that, again, the filter option.

So first of all, if we talk about basics here. So in basics, when we talk about basic operations. That is addition, subtraction, multiplication and division.

And if we talk about Excel here. So Excel gives us so much easiness. Like if I click on both of them here.

So you will see down here that I have the sum here. That is 350. The count has come that there are two cells.

Which I have selected. And the average value of both of them is 175. Similarly, if I right click here.

So you will see a lot of things here. If you want the minimum value here, you can get it here too. If you want the maximum value, you can get it here too.

Like I have clicked on maximum here. You can see that the maximum value here is 200. So whatever things you want to add here.

You can add all those things here. You have to add the numerical count. You have to add the minimum value.

Or whatever you don't want to be here. You can also remove it from here. So this is the first thing.

How we can get basic functions and basic things here. Secondly, if we want to apply the addition formula here. Between the cells.

So it is a very simple task for that. What do we have to do here? Like I want the addition of both of them here.

All I need to do is write equal to. After equal to, which is our first cell? A2.

And we will put a plus symbol in this. And we will select the second cell. That is A3.

A3 means here. A2 and A3 means here. That A is in the column.

2 is the row number here. 3 is the row number here. And as soon as I press enter here.

The addition of both of them. That is 350. I will get it here.

Similarly, we can do the same thing with functions too. That we have functions in Excel. For functions, all you need to do is.

So I am going to take the value in the fifth cell here. In the fifth cell, again we will put equal to. We will use the sum here.

Whose sum do we want to get? Again, we will choose the cell here. This one.

Now we do not need to apply the operation here. Because we have used the function of the sum name here. So I will put a comma.

I will pass A3 here. We close the parentheses here. As soon as I press enter.

Again, I will get the same output here. That here we have just put equal to an operator. And we have used the sum function here.

The same goes for subtraction. For subtraction, all you need to do is. Equal to.

After equal to, we will select the first cell. Minus the second cell. As soon as I press enter, the output here.

That 200 minus 150 is 50. Next, we will talk about multiplication here. If I talk about multiplication here.

Both of them have to be multiplied here. Again, for the multiplication operator. I will put equal to here.

We have to select equal to the first cell. And for multiplying, we will be using the asterisk symbol. Then we select the second cell here.

As soon as I press enter, I will get the multiplication. Also, what we can do is. Write equal to.

Product. And product of. Again, we will choose the cell here.

By putting a comma. The second cell. And I will close the parentheses.

As soon as I press enter. Again, I will get the product here. So both methods are here.

You can also use the function here. In this case, I have used the product function here. In this case, I have used the sum function here.

Or you can also use the operators directly. Lastly, I am going to use the division operator here. For this, I will be writing equal to.

Select the cell. For dividing, slash. That is a forward slash.

And as soon as I press enter. I will get the division here. That 12 when divided by 3.

Gives us the quotient 4. So these are our basic operations. Basic formulas.

Basic functions. Now, next thing. I will talk about cinema hall here.

Here, the way to solve the board mask is. First solve the brackets. Then order.

Then divide. Multiply. Add.

Subtract. So even the formula. You have to give in this format.

The things you have to solve first. On priority. That will go in the brackets.

In the brackets. Here. Brackets.

Order. Divide. Multiply.

Add. Subtract. It will work here.

Now here. As if I take an example. As an example.

I am going to talk here. Like I have food here. Seats.

Seat price. That is 250 here. Three seats.

Then. Seat price. 350.

Seats. Seat price. Multiply.

Seats. Seats. OK.

And then add. Seats. Seats.

OK. Then add. Seats.

Seats. Seats. Seats.

Seats. Seats. Seats.

Seats. Then, multiply. Seats.

Seats. Seats. Seats.

If we want we can close it in brackets We will close it in brackets And inside this we will add 250

And when I will press enter, I will get 1300 output Which we actually wanted So basically the follow rule here is of baud mask

Like we have been learning since childhood That is brackets, order, divide, multiply, add and subtract So I hope guys these things will be clear here

How we can build basic formulas here How we can use basic functions here If we will drag in the status bar below

Then how we will get the value For example, if I drag this here Then I will get sum, maximum, count and average

Even if I drag this whole thing here Again count, average, maximum and sum value will be obtained here

So we can do these things here So guys for text functions I have already opened a sheet

In which I have prefix, first name and last name First of all, we will talk about all the functions one by one We will understand their explanation

And then we will know them practically For example, I will talk about concatenate function Concatenate function works here

If you have different columns and you want to merge them Then you can use merge function You can also merge data from merge function

you can use concatenate method to get all the data in one cell. How to use concatenate method?

All you need to do is write equal to concatenate. After concatenate, select the first cell, comma, second cell, comma, third cell.

Close the bracket and press enter. You will notice that all three cells are joined. Now you will notice that there is no space between the

three cells. To give spaces, simply add two commas. In between these commas, we will give spaces like this.

As soon as I press enter, you will see that the names have come here. After doing this, as soon as you double click, all the data

in this column will come as it is. Moving ahead, let's talk about the next x function. That is lower case.

If we want to convert all these to lower case, all you need to do is use lower function. For that, we will write equal to lower.

We will pass in lower that I want to convert this to lower. As soon as I press enter, the data will come in the lower case.

If you want the same data in the upper case, we will use the same method. Equal to upper.

By mistake, I have pressed enter. As soon as you see the formula, you have to click on the tab button.

After upper, we have to select a cell and convert it to upper. As soon as I press enter, it will be converted to upper.

Next is proper. Proper means that the first letter will be capitalized and the rest of the letters will be small.

Because it is the format of our proper name. For that, again, equal to proper. And we will choose this cell here.

Let's close it and press enter. So now we have data in the proper format. Similarly, length function.

Now the length function works by counting the number of letters here. For example, let's put the length function on the first

name. So I will put equal to length here. After choosing the length, we have to choose the cell here.

Let's close it. As soon as you press enter, its length has come. All the cells need this data.

Double click, you will get this data in all the cells. Just you have to go in the corner, double click, double click and double click.

So we have all the length of names here easily. If you want to adjust them, you can do it. To make it look much better.

After that, the next thing we have is left, right and mid. Now I will explain to you what is left, right and mid. So let's do one thing in the case of upper.

In the case of upper, I make some changes here. Instead of upper E2, let's take another cell here. I take upper B2 here.

And same goes for all of them. And you will notice that as I have made changes here. Because I had taken the data of this cell here.

So dynamically it has changed here too. Now let's move towards left, right and mid. Let's understand the concept of left, right and mid here.

You need some letters from the text. In that case, you need the first letter of the text. You need 2-3 values of the starting of the first.

So in that case, we can use the left. All you need to do is right, left. And we need the text here.

We will choose the text of this cell. If you do not pass the number of characters. The default value is 1 here.

So the first letter will come here, that is G. Similarly, if you pass the number of characters here. No, you need 3 values.

And then we will press enter. So John will come. But because its name is 3 letters here.

So the full name has come. But in the case of others, the starting value of 3 letters will come here.

This you can do with the months. Suppose you have 3 values of the starting of the month. This is January, February, March, April.

So Jan, Feb, March, Mar will come to you. So if you want data like this. Then you can easily obtain it.

Suppose with the right, if you want the data of the right. That is, you need 3 values of the last in this text. You need the value of the last.

So in that case, all you need to do is right. Equal to. We will write right.

To choose the cell, you can choose any cell. I will choose this cell here. Close and press enter.

So the last text is N. So the last letter N will come to us. And the same goes for all of them.

By double clicking it. Mid means here. If you want any value of mid here.

Then you can define mid here. Like. If we want the mid value of any name.

So for mid, we will take here first. Equal to. We put mid here.

Now from mid, the parameters have come here first. Okay. We want text here.

Which text do we want? We want the starting value and the number of characters. Okay, so I want the text here.

So let's say this one. Starting value is of center. So it starts from 2.

If you want to start from 5, you can. Start from 2. And come to me up to 3 characters.

I will press enter. So there were not 3 characters in this. But in the case of the rest, 3 characters will come to me

in the middle. Which starts from 2. So UGE came to me.

UBE comes in the ribbon from 2. So 3 characters came to me here. So if you want to obtain this, you can easily do it with

the help of Excel's text functions. These were some basic functions. Let's talk about some more advanced functions.

Because it is associated with text. In these, first comes Find function.

So for find, I will take a text here. Let's write fine here. We have to find.

Like I have to find some data from this name. So I have to find which number is O. To find, we will write here.

I will press tab here. What we have to find? We have to find alphabet O.

From whom we have to find? Let's say we have to find from John. So we will define cell here.

Let me try once and show you these two things. As I write here, it will tell me on 2 number. But instead of this, instead of G2, it would have been F2,

in which John is also written. Let's change this formula a little. F2 We will not get this value here.

But while at the same time, if we would have seen this for G2, we would have got the value here. Because it is case sensitive.

Similarly, search is not case sensitive. Like this, we have a search. If I write the same thing here, that is, is equal to we

will search alphabet O. From whom we have to search? Let's say, I will take the same concept here.

Let's say, I have to search from F2. As soon as I press enter, I will get this on 2 number, which I was not getting in

this case. So search is not case sensitive here. But find is case sensitive.

Next, we have replace here. If we have to replace any value. Like here,

John is written. I want JIN. So in that case, we will use is equal to replace. And after replace, from which text we want this?

So, I will take here, John. You can see the formula here.

From where it should start? It should start from 2nd number. How many numbers you have to replace?

I have to replace only one number. That is, O has to be replaced. From whom I have to replace?

I have to replace it with I. I will pass I here, in double quotes. As soon as I press enter, instead of John,

I has not come in small. So, I will add it in small. And again, I will press enter.

Instead of John, it has become JIN. So, this was replace. And lastly, we have one more text function.

There are many text functions, but we are covering some basic and important text functions. Lastly, we have substitute.

In substitute, I will take here, is equal to substitute. In substitute, we apply the formula here. That is equal to substitute.

In substitute, we have text, old text, new text. That is, you have to give old text in any text. And what new text you have to add, you have to mention here.

How many times it should happen? For example, in Eugene, 3 times E is coming. Let's try substitute on this.

I am taking here, first G3. On whom I am talking? On whom I am talking?

It should convert in I. We will pass I in double quotes. As soon as I will close it,

I will enter. In that case, we will have E, U, G and I. Again, I guess,

I have taken in capital. Instead of capital, we will add in small. Again, I will press enter.

So, it has become Eugenie. And if you want only one instance, to convert only once, in that case, you can give fourth parameter here.

That is one time. Again, I will press enter. So, you will see last value here.

Earlier, it was UIGINI. Now, it is UIGINI. It means, it has been converted only once.

So, these are some of the text functions, which we have seen in this video. I hope, you don't have any doubt or question in these text

functions. So, let's get started guys. I am going to take an example here.

Let's zoom in. So, here I have some data in this format. I have employee data, employee full name, job title,

department. gender, age, hired age, country. First of all, we will talk about the basic conditions.

If you want to apply any basic condition of greater than or less than then how can you apply it? We are going to see that here.

So, if you want to apply greater than condition in that case you don't have to do anything. If you want that here you can get all the ages which are

greater than 50. So, all the employees whose age is more than 50 can be found here.

All you need to do is greater than 50. As soon as I press enter it will give me true. It will tell me how the conditions are working here.

Whatever condition we have given is it true or false? If I double tap on the corner it will give me true and false for all.

Where our condition is satisfying it is true and else it is false. But what if I have to give data according to me that if it

is more than 50 then that person is a senior person. Or we have to give them a bonus that by the end of the year whoever is above 50 we have to give them a bonus of 5% or

10% of their salary. So, in that case what we will do here as I am going to use if condition we will write is equals to if our condition

will be if the age is greater than or equal to 50 in that condition I will write bonus and I will write no bonus here.

Now, you will notice that whenever we have to write a text we will write it in double quotes otherwise, if we have a column or a number then we have to pass it directly.

So, I have given a condition here that if the age is greater than 50 then in that case greater than or equal to 50 in that case I have to show bonus here else I have to

show no bonus. As soon as I press enter bonus will come here double click here so, wherever we have to give a bonus for example, the

first three ages are 55, 59, 50 there is a bonus at the age of 26, no bonus at the age of 55, 57, bonus at the age of 27, 25, 29, 34, 36 at the age of 27, no bonus

So, in this way also we can use if condition statement Now, along with if we have two more operations that is AND and OR

AND and OR means the two conditions if you have more than two conditions which you need to satisfy for example, I want list of all the employees which are in IT department

and in United States So, I have to include employees of United States in a column so, I will use AND here how to use AND?

First, we will give if here with equal to we will put AND here now, in AND we can give as many logicals as we want for example, as you can see here we have logical 1, logical

2 and so on so, my first logic here is department C2 should be equal to IT so, we will give IT in double quotes I guess I have pressed enter by mistake so, double equals

to we will give IT here second condition is second logical that country should be United States I can also use single equal to in Python we use double

equal so, I have used double equal here but we can also use single equal to and for our country we will select a column that country should be equal to United so, in that

condition our first condition is one should be of United States and other should be of IT if these two conditions are true in that case, we have to include it in our program

else, we don't have to include it so, in that case, we will write included else excluded we will it here when I will press enter first one is included

because it is in IT department and United States now, we will double tap here you will see, second one is excluded even though it is in IT department but it is not in United

States it is in China so, it is excluded third one is also excluded because it is in United States but it is from finance department so, AND means if both conditions are

satisfied only then it will be executed only then it will pass true else, it will give another let's see the formula first, let's talk about AND what we

have passed here is IT department and United States country in that case, it is included which is

IF part else, it is excluded so, in this way, our conditional statements work here I guess, I have made some mistake here so, let's press ctrl

z so, in this way, our AND operation works here lastly, let's talk about OR operation in the concept of OR operation, let's consider let's say, this time

I have to give a condition here that all the female employees and all the employees may be male, but they are in China so, I have to include their data in this program

all female employees AND all female employees or any person who is in China in that case we will give a condition here that if I will put

here that is equals to if we will use OR statement here in OR, what will be the first logical that either they are female that means, our gender here that should be equal to

I guess, I have entered here so, that should be equal to female and my second logical should be that person should be from China so, G2 should be equal to China that means,

that country should be China now, out of these two conditions one should be satisfied, either she is female or she is from China even if she is male, but she should be

from China in that case, we will include her here, I will write included, else I will write excluded as soon as I enter here the first one is included why?

she is female for the second one we will double tap here the second one is also included but she is male but she is in China, so she is included next is female which is

included female which is included in excluded, we will see here in excluded, she is from United States and male, so she is excluded so, if one of the two conditions is

satisfied here, either she is female or she is from China in that case, we will use OR here, when we need one of the two conditions now, it is

not necessary here to give only two conditions in OR to give only two conditions in OR the number of logicals you want to increase you can increase that number of logicals

so, here I have put some different categories that if we want to use different functions here, then how we can use them, first of all if I want to get today's date then I

have to use a simple function of date function that is today, so I will write here is equals to today open close the parenthesis as soon as I press enter, you will see

I have got today's date that is 13 February 2023 similarly, now function brings date and time here like, if I write here is equals to now and open close parenthesis we will

press enter so, we got today's date as well as today's time that is 4.10 similarly, if you want day here whatever date we have put here we want its day here, so in that case what

we can do here, we can write is equals to day, open the parenthesis here serial number means we will pass column, whose date we will equal, I will

to know that here I have the first parameter of date that is day is 13 after 13 comes next thing, here month if we want month here then we have a very simple method that

again we pass month here that is equals to month for month also we will pass date here I will close it as soon as

I press enter, I will get month that is 02, if you want year here again for that we have is equals to year we pass value here and we will get year here that is 1023, suppose

you have not given values like this you have written here 18 06 2022 you need a combined date here in that case what we can do here we will write here is equals to date

I guess I have pressed enter so is equals to date in date you can see the format here first comes year then month and then date so year comma month comma day and I will close it

here and press enter, so here I will get 18 06 2022 similarly you want to get current hour here in that case we can pass here hour that is equals to hour we pass date here

that I want current hour here or if after hour I will pass my serial number here and as soon as I close it, I will press enter then it will be 16, 16

it is 4 o'clock, if we talk about minutes here you will see that automatically data is also changing here because here we have used functions of now, today if I open tomorrow,

then tomorrow's date will come here if I see now, then tomorrow's time will come here I have added the date format items in here.

If we talk about minutes here, I want minutes here, so in that case, simply equals to minutes.

Let's choose our date here. Let's close it. If we press enter, we will get minutes here.

I guess I have done something wrong here. I have taken minimum value instead of minutes. So, I will take minute here.

So, this one is the minute. In minutes, we will pass the value here. Let's close it and press enter.

I will get minutes here. As you can see, we have got minutes in this format. Moving ahead, if I talk about seconds here.

Again, we have a function called seconds. So, I will take seconds here. Seconds.

Let's pass seconds here. I guess I made a little mistake here. I have taken seconds here.

Let's remove it from here and then we will press enter. We will get value in seconds here. Similarly, moving ahead, let's talk about some other

functions here. For example, I want to get the date 3 days later or 3 days earlier.

So, we have a very simple method for that. Suppose, what we have to do here. You have a date.

We will take the date column here. So, today we have a date column. I will pick the date column here.

We have to minus 3 from this. As soon as we press enter, today's date is 13. If I will get minus 3 here, then the date will be 10th

February 2023. Similarly, if I have to get plus, then in this case, we will write plus in this formula.

Let's press enter. 13 plus 3 is 16th February 2023. Same goes for months over here.

In the case of months, if we have to add months, then we have to add a separate function here. That is edate.

I will use edate here. I don't know why, but we press enter again and again. So, edate.

We have to give the start date here. So, this is our start date. Comma, how many months do we have to add in this?

Let's say, we have to add 3 months. As soon as I press enter, I have the format here. Let's change the numbers and set the short date.

So, you will see 13 here. Today is 3 months, it will be 13. As today's date is 13.

And 13th of May 2023. Similarly, if you want to minus, you can do that. Okay.

Now, if we want to increase the years, then in that case, what can we do? In other cases, we have directly added edate here.

But here, we don't have any other function. We will have to use edate only. How will we use it?

We will write edate here. Okay. Let's give the starting date here.

This is the starting date. Comma. What can we do for months here?

Let's say, I have to add 3 years here. 12 multiplied by 3 will be months. So, you can write 12 times 3 here.

You can close it. Let's close it here too. And press enter.

We will get the value here. Again, let's convert it to the short date here. As soon as it is converted to the short date, it is 2023

here. And let's add 3 years. It will be 2026 here.

Similarly, we can also minus the values. Just do this much that if you want to increase the years, you can do 3 times 12 here.

Because there are 12 months in a year. So guys, these were some basic date and time functions. If you want to get the date from these, you can easily get

the day too. For example, if I talk about now here. I have to get the day here.

There is a very simple method to get the day. We will go to custom here. We will go to custom and go to more number formats.

You can see here. In custom, we are getting day, month, year, hours and minutes.

Let's remove it from here. I start writing here. We have 2013 here.

If I pass one more day here, I will get here that it is Monday. As soon as I do OK here,

I will get values like Monday, Tuesday, Wednesday. So as an example, I am taking a sheet of expenses here.

So this is January expenses sheet. Where I have dates, category, subcategory. How much amount has been spent on it.

Whether that payment has been done in cash, UPI or card. So we have all these details here. Now I have to get the sum here first.

Whose sum do I have to get? Obviously, we will get the sum of the amount here. So for the sum of the amount, all you need to do is select

this. You will also get the sum at the bottom here. The sum is 30,149.

You will get the average value. You will get the count that it is 29. You will get the maximum value here.

You will get all these things here. If you want to do this formula, you just have to write that you have to get the sum here.

Whose sum do I have to get? We are going to take the whole range here. Because this is the range of the table.

So as soon as I press enter here, 30,149 has come here. But what if this table was not there and it was just a range like this.

Suppose this range would be like this. 34, 67, 88. I have taken 3 values here.

If I want to get their sum, I can easily get their sum by writing. Now in this case, we don't have to do this.

We will put equals to sum here. And as I have chosen the whole range here, in this case, we can also choose by dragging these three numbers.

And as I will close here, I guess I have made a little mistake here. So I will choose it again here.

And by choosing these three, we will press enter. So the sum of these three will come here. Suppose I make the value 0 here.

So I will have the sum of these two here. The sum of these two will remain here. I will also make the value 0 here.

So I will get only 34 here. So not just on a table, but it can also work on any range. You just have to drag and choose the range for which you

want to get the sum. Okay. So let's remove this part from here and move towards here.

So we have got the sum here. But what if I want to get the sum of all my UPI payments. When will the sum be the payment of UPI?

So for that, we will write SUMIF here. First of all, we have to take the range here. What is the range here?

The payment mode is our range. What will be the criteria after putting a comma? What should it be equal to?

I want all the UPI payments here. So for UPI, I will pass UPI in double quotes here.

Comma. Now we have to put a sum on the sum range. We have to put a sum on the amount.

We will close it and press enter. So for all my UPI payments, the count here is 23,682. So in this way, we can get the value sum here on the basis

of any one condition. But what if we have to get it on the basis of multiple conditions?

So in that case, what can we do here? We can use SUMIF here. I want the payment from UPI while at the same time, it is

the category of food. I want the sum of food category from UPI. I want the sum of food category from UPI.

I want the sum of food category from UPI. For that, we will use SUMIF here. Now the formula itself tells that we have to get the sum

from the sum range. So we need the sum from the amount. Next is criteria range.

What should be the payment mode? UPI. Which criteria are we on?

UPI. Now we have the next criteria. The next criteria is the category that we should have.

That should be food. So we will mention food here. As soon as I enter here,

I will get the sum of UPI payments. The total is 1712. The total is 1712.

What if it was in cash? Let's make it easy. I will write cash instead of UPI.

As soon as I enter, I will get 530. There are some less payments here.

Out of all the cash payments, I have only one payment in food. Rest of the cash payments, there is no food in them.

So we have two categories in total SUMIFs. One is payment from cash and the other is in food category. The two multiple conditions that we have here, that comes

in the category of SUMIFs. You can add more if conditions here. The same goes for count.

If we want to get the total count, all we need to do is write equals to count. Who do we need the count for?

Let's say we need the count of payment. Let's close it and press enter. There are 29 values here.

I need the total payments from UPI. I need the count of all UPI payments and the count of all cash payments.

In that case, we can write count if. I have pressed enter again by mistake. Let's write count if here.

After count if, we need to choose the range. What should be the criteria for the range here? It should be cash.

I will close the parenthesis and press enter. I have 9 payments from cash. So out of 29, 9 payments are from cash.

Similarly, if you want to give multiple if conditions in cash, you need to give multiple if conditions with count. In that case, we will use count if here.

Let's see the formula. We are going to take count if here. First of all, what will be the criteria range?

Our cash payment should be what? Cash. Payment mode should be cash.

Second criteria is category should be food. Let's take category. I made a small mistake here.

Let's choose a category and put a comma. What should be the category here? Capital FWD.

Close it. Let's press enter. So in countif, only one payment is like this in food

category. Like we saw in sumifs that only one payment was like this. We had only one sum which was in cash category.

That was 530. We got the same thing in countif that only one cash payment is like this.

Which is in food category. Rest are in different categories. So this is how sum, sumif, sumifs work and countif,

countifs work. Similarly, average, averageif and averageif also work. So guys this was my expenses sheet which I had created for

January. Now I have to validate this data. So in that case, suppose I enter some wrong data in the

date. Suppose I enter more or less amount. Or I write letters instead of digits.

Suppose I make a mistake in payment. So there can be many mistakes. Suppose I write February instead of January.

By mistake I wrote 02. Or I wrote 2022 instead of 2023. So because of these things we don't have any errors.

For that we will do the same thing. I will write date here. I have made a column here.