Untitled

Bubble Chart Bubble chart in excel is a variation of scatter plot. In scatter plot we use points for representation while in bubble chart data points represented by bubble. Like scatter plot, bubble plot is also not using category value as axis. We will use Bubble chart when we have at least three or more sets of data to represent in the chart. Out of these three dataset, two will be…

How to create a Pareto Chart in Excel Pareto Chart is the presentation of Pareto principles also known as 80/20 rules, the law of the vital few, or the principle of factor sparsity is used in statistics and quality control. This principle explains 80% of the consequences come from 20% of the causes( the vital few). This 80/20 proportion can be changed in practical life based on the actual…

How to combine text in excel Combining text in excel from different cells can be done in different ways. We can use ‘&’ to combine or can use CONCATENATE or CONCAT function to combine texts from different cells. But in all these methods when we are using some delimiters we need to put delimiter multiple times with every text also there was no provision of treating blank cells. How to combine…

The TEXT function in Excel is used to convert a numeric value into text and allows you to display that number in a specific format. This is particularly useful for creating more readable numerical displays, formatting dates, or currency values.

text function in excel The TEXT function in Excel is used to convert a numeric value into text and allows you to display that number in a specific format. This is particularly useful for creating more readable numerical displays, formatting dates, or currency values. Syntax for TEXT Function The syntax for the TEXT function is is mentioned below TEXT(value, format_text) value: The numeric…

What is the AGGREGATE function in Excel What is the AGGREGATE function in Excel – The AGGREGATE function in excel is a conglomerate of functions. We can use 19 functions from this single function. This is just a “Function of Functions” which incorporates multiple functions. It was introduced in the 2010 version. So if you are using an older version then it would reflect #NAME? errors. The excel…

how to use sumproduct in excel The SUMPRODUCT function in Excel is a powerful tool that allows you to multiply corresponding elements in ranges or arrays and then sum the products. It’s often seen as a more advanced or complex function, but it can be incredibly useful for various calculations and data analysis tasks. Here’s a breakdown of how it works: Basic…

Understanding HLOOKUP a very less used but very important formula

hlookup formula in excel HLOOKUP stands for Horizontal Lookup and is a useful function in Excel for retrieving data from a table by searching a row for the matching data and returning the value from the corresponding column. It is often used in conjunction with other excel lookup functions to perform efficient data retrieval across tables. There is huge difference between VLOOKUP and HLOOKUP…

VLOOKUP Function in Excel: Step-by-Step Process and Real-Life Examples

VLOOKUP Function in Excel Have you ever found yourself spending hours searching for specific data in a sea of numbers and spreadsheets? Well, we have the perfect solution to save you time and simplify your workflow: the VLOOKUP function in Excel! Whether you’re an Excel novice or a seasoned pro, this powerful tool can revolutionize how you analyze and organize your data. In this blog post, we’ll…

How to use MATCH function in Excel In this blog, we will be discussing MATCH function in excel. MATCH function basically helps in finding the position of a value. It will not fetch any number but will provide only the position of the lookup value in excel. This function is very helpful when it works with other functions like OFFSET/INDEX. Let us understand the anatomy of excel MATCH function. It requires 3 input values as can be seen in the below picture.

match function excel

How to use MATCH function in Excel match_type can be explained as below:

Match function option In example 1: we will try to find out the position of USA in the country list. Here USA is lookup_value the list of countries is lookup_array and we will match the exact value so will use 0 as match_type .

Match fucntion excel Note: - MATCH function is not a case sensitive function. - If MATCH function doesn't find any match it will return #n/a error. - We can use WILD CARD when match_Type is 0 and Lookup value is text

Wild card for excel match function In example 2: We will use wildcard * which will help in getting position based on any character. Here we took example of China where we used * in the initial position. This function will try to match hina in the list and will provide the position for the 1st match.

How to use HLOOKUP in Excel HLOOKUP is one of the functions provided by Microsoft Excel which helps us find a value for a particular reference value. Its functions are the same as VLOOKUP, just the difference exists in terms of fetching data horizontally instead of vertically. This formula also requires 4 inputs as mentioned below:

We will take an example to understand this in a better way. As we see in the below table we have data for student's marks achieved in different subject. Now if I wanted to fetch data for a particular student for a particular subject then we will use HLOOKUP to get it. Here we took Steve as look_up value and will try to get data from the table(table_array) for the subject Botany which is 5 rows( row_index_num) below the names of students. Finally we will get value as 76.

What is a scatter plot? Scatter plots also known as scatter charts and scatter diagrams used to show relationships between two variables and the relationship between two variables is called their correlation. It's a 2-D plot and it plots data points on a horizontal and a vertical axis to show how much one variable is affected by another. In the scatter plot, all data points are represented by dots in the plot. If the points are close to making a straight line in the plot then it is considered as highly correlated variables. While if they are equally distributed all over the plot then their correlation is weak or zero. When to use scatter diagram When we have a pair of numerical data and we are trying to determine if the both variable is related or not. How to create scatter plot in excel To create a scatter plot in excel we need to first select our data the go to Insert >> Insert Scatter (X,Y) or Bubble Chart >> Scatter.

Scatter Chat in Excel After clicking this option we will get a scatter chart as below.

Bubble Chart Bubble chart in excel is a variation of scatter plot. In scatter plot we use points for representation while in bubble chart data points represented by bubble. Like scatter plot, bubble plot is also not using category value as axis. We will use Bubble chart when we have at least three or more sets of data to represent in the chart. Out of these three dataset, two will be representing as X and Y axis while the third data point represented by the size of bubble. There are two types of Bubble chart available in excel 1)Bubble and 2) 3-D Bubble. How to create Bubble Chart in Excel Like other charts after selecting our data we will go to Insert >> Insert Scatter (X,Y) or Bubble Chart >> Bubble or 3-D Bubble. It is to be noted here that we will select only data points not the categorical values neither column header. A new Bubble chart will come as below.

Bubble chart in excel In the above chart we can see value of X coordinate is represented in X-axis while Y coordinate is represented in Y-axis. While Population is represented by the size of bubble. One point to be noted here is that if we want to show category on the plot then we need to go in Chart element >>Data Labels >> More Options as illustrated in the below image.

Data Labels A new box will open on the right side. We will select values from cells, a pop up will emerge which will ask to select a range.

Excel Pivot Table Pivot table is the most powerful tool of excel by which we can create a report or summarize our data in a very meaningful way. It is used usually with big datasets. In this article, we will be discussing the basics of Excel Pivot Table and step by step method to create Excel Pivot Table. What is an Excel Pivot Table We can think about Pivot Table as a report but it differs from the traditional reports which are static in nature. Pivot tables are interactive in nature and it is dynamic as well. We can group data into categories, filter data to include or to exclude some categories, can break date data into months or years and even we can create charts from it. How to create Pivot Table in excel To create a pivot table in excel we will select any cell on our dataset then we will go to the Insert tab then PIvot table and then in the drop-down click on Pivot table.

Excel Pivot Table After choosing the above-mentioned options we will get a new pop-up box as shown in the below image.

How to create a pivot table In the above image, we can see it asks to select a table or range. In our case, our data is in the table so we would give the name of the table. Then it asks for using an external data source which is not required in our case as we have our data already with us. Now it asks where you want to place the pivot table report. We can place it either in a new Worksheet or in the Existing Worksheet. For the existing worksheet, we need to provide the location. We will select a new worksheet. After selecting these options we will click OK. Now after clicking OK we will be in a new sheet which will be looking like as below image.

Pivot Table Layout First option on the upper right side Choose fields to add to report has all columns of the data set and we can select any column name to show in our reports. In the lower right side we can see there are four boxes.Lets understand these options. Filters: Filters are use filter our data. It will reflect at the upper left part and will contain different categorical values to select from. If we don't select any value all dataset will reflect while if we choose a particular value it will reflect data for that value only. We can you Slicer as well to filter our data. Columns: If we put a column in Columns box it will reflect all unique value horizontally from that column. It helps us to understand data for each unique value. Rows: It also like Columns when we put a column in the Rows it will reflect unique values from that column. Values: It shows values aginst the Columns and Rows value. There are multiple options by which we can Summaries value field by. - Sum - Count - Average - Max - Min - Product - Count Numbers - StdDev - StdDevp - Var - Varp Let us understand it by the below example.

Pivot table example In the above example we have taken Product as a Filters,so we we will choose any product the value in the below table will get changed accordingly. In the below image when we choose Product as Accessories then all values in the table got changed.