`

What is VLOOKUP?

Hello all, We are back with the most interesting and useful tool for the MS Excel, you might be searching for.Vlookup is a great tool for MS EXCEL, that helps us in looking up the desired data from the tables in seconds.Vlookup searches for the information vertically into columns and returns the desired value in the same row. It saves our time and maintains the accuracy of the data so needed.

How can we use VLOOKUP Function??

There are four arguments you need to put up inside the parenthesis of VLOOKUP function, but before that, you need to know the syntax of it.

VLOOKUP SYNTAX:
                 
=VLOOKUP("Vlookup Value",Range,Column Number ,Match)

V LOOKUP FUNCTION, HOW TO USE V LOOKUP

Let us discuss these arguments in detail below,

1. VLOOKUP VALUE: This is the name of the item you are looking for, it can be anything from your table and as it is in text format, it would be enclosed by double quotes.

2. RANGE : This is the cell that contains the data,that is like you start your table from cell or column A and its row 2 and end your table at column B and its row 20,then you will write your range as,"A2: B20", where colon separates the 'range from' and 'range to' as arguments.

3. COLUMN NUMBER: Column number is the column which contains your answer or can say which contains your return value like you are searching for the price of any item, then column number here would be the number of that column which contains prices of different items.

4. MATCH: It is an Optional Argument, which describes to the VLOOKUP Function of Excel, to look up either for an Approximate match of user's request or for the Exact match.If you want an Exact Match you can specify "FALSE" in the argument, otherwise "TRUE" for an Approximate Match.By default, VLOOKUP takes TRUE as an argument.

How VLOOKUP Function works practically?

Now, Let us do an example to implement  VLOOKUP Function on our own,

Consider this Spreadsheet, there are five columns as Supplier ID, Part Number, Part Name, Part Price and Status.Let us say we need to look up If we have "Oil pan"  in stock or not.

V LOOKUP FUNCTION, HOW TO USE V LOOKUP


Let's write lookup function step by step for it,

NOTE: We can write the VLOOKUP Function in any blank cell of the SpreadSheet.

1. VLOOKUP VALUE: For the Problem, we are given, we need to specify Oil pan as our Vlookup Value in double quotes, So we can write it as,
                                         
                                        =VLOOKUP("Oil pan"

2. RANGE: Now, we need to specify our range, as we can see our table starts from A2 and ends in E11, So we can write it as,
                                    
                                      =VLOOKUP("Oil pan",A2:E11

3. COLUMN NUMBER: The Return Value we are expecting is, either the item is "In stock or not", that is we are looking for the Status of the Oil pan, which is column number 5. So, We would specify it as,

                                     =VLOOKUP("Oil pan",A2:E11,5

4. MATCH: Now, this is an optional argument of VLOOKUP Function, So let us specify it as Exact Match, that is False and close the parenthesis of VLOOKUP Function.

                                     =VLOOKUP("Oil pan",A2:E11,5,FALSE)


V LOOKUP FUNCTION, HOW TO USE V LOOKUP


Now, Press the Enter Button and you will get your result as "Out of Stock".

V LOOKUP FUNCTION, HOW TO USE V LOOKUP



This is how you can manage your Complex Spread Sheets to frequently get the results using VLOOKUP Function.Practice it on easy tables first and then try to Implement on Complex ones.
Bookmark this website for more useful Information and tips related to Excel Queries and share with your Colleagues and Friends who are facing problem in understanding and implementing Excel Functions.If you have any question regarding this post then you can freely post it to us, We'll be happy to answer you.

Hello Everyone, Today we will discuss how to wraps the text in the cells in MS Excel. MS excel is a powerful tool to design the data in a well manner. If we want to display the data in a proper manner or want text to appear on the multiple lines in a cell, we need to apply the wrap text function in the cells, We can also enter a manual line break for appear text in multiple line.

Procedure for Wrapping Text in Excel 

There are two procedure for wrapping the text in the cells in MS Excel. First option is to used Wrap Text Function and another option is to used enter a Line Break.

  • Wrap Text Function
  • Enter a Line Break

Wrap Text Function

Wrap Text Function is a procedure that is applied on the cells for wrapping the data of the cells of the excel worksheet. If you want text to appear in the multiple lines or want to appear the data in the visualize mode then you can apply the wrapping on the texts in the cells. For that you need to apply few steps as discussed below:

  • Select the cells in the worksheet that you want to apply Wrap Text.
Wrapping Text in excel, excel wrap Text
  • After Selecting the data, go to the Home tab and in the Alignment Group Click the Wrap Text.

Enter a Line Break 

This is second manual function for wrapping the data. Its also used when entering the data in the cells in the worksheet because this is manual function. If we are typing in a cell and want to appear the text in the next line then we have to press ALT+ENTER to come in the new line. After pressing ALT+ENTER text are appear in the next line. 

wrapping text , excel wrap text in cells


If you want to start in a new line of text at a specific point in a cell then double click in the cell and select the location where you want to break the line in a cell  and then press ALT+ENTER.


Above all information is provided for wrapping the text in the cells in excel worksheet. We hope you will like and agree with our information. This above information is very helpful to you. If you like our post please share with your friends and colleagues. You can also share on social media website like Facebook, twitter and Instagram. If you have any question regarding this post then you can freely ask to us. We'll be happy to provide your answer.
Hello Everyone, Today we'll discuss a topic How to Recover Unsaved Excel File. This article helps you to know how to recover your whole excel data when you forget to save the file or accidentally close the documents. Just imagine you have been creating a very important document in excel or making a business report and suddenly excel crashed before saving it but you don't need to panic. Microsoft excel has featured in where you can easily Recover Excel Document not saved, just go ahead and read this article. 


Guidelines For Excel File Recovery 

There are some steps to recover your unsaved excel file if you close the file before saving it.
  • Open Excel Workbook then click the File tab.
  • In File tab, click on Recent.

Recover unsaved excel file, Enable AutoRecover Excel File, Recover excel file, Protect  excel file

  • Scroll to the bottom to your recent documents and Click on "Recover Unsaved Workbooks".
  • A dialog box appears, just select the file and open that is temporarily stored in your computer.


Another Method to Recover Excel File

You can also access a file by using these steps:
  • Open Excel Workbook then click the File Tab.
  • In File tab, go to Info.
  • In Info, Click Manage Versions.
Recover unsaved excel file, Enable AutoRecover Excel File, Recover excel file, Protect  excel file

  • Then click Recover Unsaved Workbooks.
  • A dialog Box appears and will see the list of unsaved files then select the file and Open that is temporarily saved on your computer.
  • Then Click on Save As and save the file permanently to your computer.

Enable AutoRecover and AutoSave in Microsoft Excel

There are some steps to enable AutoRecover and AutoSave your excel file so that you can access your file when you forget to save your excel file .
  • Open the Excel  workbook and click the File tab.
  • In file tab, Select Help then Click on Options.
Recover unsaved excel file, Enable AutoRecover Excel File, Recover excel file, Protect  excel file

  • Click Save in the Excel Options dialog box.
  • Now Make sure the "Save AutoRecover information every x minute" and "Keep the last autosaved version If I close without saving" check boxes is selected.

Thus, in this Post, we discussed How to Recover Unsaved Excel File or How to enable AutoRecover and AutoSave in Excel File so that we can easily get our data if we close the excel file accidently. So don't panic if you forget to save your  important data, you can help to recover your data by this information. If you like this information then you can also share with your friends, colleagues and also post on facebook, twitter, Instagram or other social media website. If you find any mistake in this post then you can freely write your suggestion in the comment section so that we can improve our mistake early.
Hello everyone, Today we'll discuss how to Create a Sparklines or Mini Chart in Excel. A sparkline is a tool/cell  that provides the visual(graphically) representation of data. Excel 2010 Sparklines is used to represent the data (a data which were taken in a rows or column ) creating a new cell (in row or column) and provides the tiny graph for each row or column to analyze the  data and highlights the data values to create a sparklines we need to organize data in a rows or column. It is used to show trends in a series of values, such as seasonal decreases or increases, economics cycles and to highlights a maximum or minimum values of the data.in Excel Sparklines chart have different charts and each chart has its own advantages. This tutorial helps you how to create the sparklines in various types different data and create a chart by using it.


Create a Sparklines | Inline-chart 

Sparklines are small, lightweight graph, that fits into a single cell .when we represent our data in a graphically form or chart form we need to organize data in a rows or column, it is easily understood and it clearly shows the lowest or highest point,economically cycle.here we discuss some point about the chart.So,here we understand how to create sparklines in excel by following some few steps which are:
  • Select an empty cell or rows or column cell  in which you want to show the "Sparkline".
  • Click "Insert" tab. 
  • In the sparkline group, select the sparklines types.
  • A Dialogue box appears, asking for the data range.
  • Click on the icon Data Range and enter the range.
  • Now, click ok.
  • The chart will be shown in the spreadsheet.


  • if you want to show the sparklines in multiple cells, Select the Location range.
  • Click,ok


So, this was all about how to use Excel Sparklines. If you wish to make a sparklines chart that looks different than the simple chart, then after you create the sparklines chart , you can control which data values are points(such as high or low,first or Last and any negative data values). there are many options available .You can do Formatting, add markers, axis etc to enhance the sparklines. Excel sparklines chart allow creating sparklines in the type of Line,Column,Win/LossSo, we will discuss all types of charts and their formatting .



Types Of Sparklines

Line Chart

These Sparklines are displayed in the format of simple lines ,in this data points are connected with lines to see whether a increases or decreases over a time which is easy to read.The line in excel is used to display the trends over time. A line chart is a default option for sparklines,there is no need to specify this as it would be redudant.you could change the sparklines color,markers colors,style of line.To create a line sparklines follow some steps:
  • Select the range of the input data,
  • Click on the insert tab.
  • Select line from the sparklines.
  • Select the data range where you want to insert the Line sparklines.
  • Click  Ok.




  • Highlight the highest point/low point in the chart.
  • Select any color /style from the drop-down list.
  • Get the result.


Column Chart

These sparklines are displayed in the column of bars.If the data which we will enter have a positive value then the bar column will be lying in the upper axis,or if the data consist of negative value then the column will lie below the axis and data have a zero value then column will not be displayed and empty space would be left at the data point and you can change the sparkline color ,style .To create a column sparklines follow some steps.
  • Select the range of an input data.
  • Click on the insert tab.
  • Select column from the sparklines.
  • Select the data range where you want to insert the column sparklines.
  • Click,Ok.
  • Select any color/style from the drop-down list.
  • Get the result.



Win/loss Sparklines:

These sparklines similar to the column but the difference is that these only represent profit/LossIt shows a positive or negative situation of data in a distinctive way. It is very easy to create.
To create a win/loss sparklines follow some steps:
  • Select the range of input data.
  • Click on the insert tab.
  • Select Win/Loss from the sparklines.
  • Select the data range where you want to insert the Win/Loss sparklines.
  • Click Ok.
  • Select the marker color, change the color of negative values.
  • Click on the negative point and select a color.
  • Get the Result.



Conclusion:

Thus, in this post, we discussed How To Create a Sparklines In Excel. Sparklines are simpler  than Charts but not a versatile. Sparklines are the tiny graph that fit into the one cell and gives you the clear graphical presentation of the data. We hope you will like this article, if you like this article then you can share with your friends and colleagues. We will update the information on the regular basis. You can also share this post on social media websites like facebook, twitter, Instagram or other social media websites. If you have any doubt regarding this post you can freely write your query in a comment section. We will revert back to you as soon as possible.

Hello Everyone, Today you'll find How to use SUMIF Function in Excel. SUMIF Function used to add the specified cells based on the single or multiple supplied criteria. Sometimes we require conditional sum in Excel then we can use this function to add all numbers in a particularly given range. SUMIF function based on the three-parameter- range, criteria, and sum_range. Every parameter has own functionality and also depends on SUMIF multiple criteria.

SUMIF Function Syntax

The SUMIF Function in Excel has the following syntax:
=SUMIF(range, criteria, sum_range)
  • Range: The range of cells calculated by criteria. It is required entity mean required for the function to work. Cells in every range must be numbers, names, arrays, references that include numbers and not contain blank and text value.
  • Criteria: Criteria is a condition that must be met for a cell and it is also required entity. The criteria should be a number, expression, cell reference, text or function. It can be simple or multiple criteria. 
  • Sum_range: sum_range is the range of cells that sum together. It is optional means, not a mandatory entity. If you skip this entity then uses range as a sum_range. 

How to Use SUMIF Function

Excel SUMIF Function used to calculate the sum of the cells in a range that be specified under certain criteria.  Here we'll discuss this function with an example of the sales table.
                                 SUMIF function, SUMIF formula, SUMIF multiple criteria, SUMIF data range
If you want to find the total sold quantity from above table then you can simply find using SUM function like =SUM(B6:B11) and get the result is 26. But you want to calculate total sales amount with criteria that you want to add all sales amount where the sales quantity is 5 and greater than 5. For that we need to remind three things:
  • The first requirement is the range. In this example, the first requirement is B18:B23 and we have the range of sold quantity of every day. It will check if they meet the criteria.
  • The second parameter is criteria or user condition like we want to add all sales amount where the sales quantity is greater and greater than 5. For this example, the second parameter is ">=5".
  • The third requirement is sum_range. In this example, the third parameter is C18:C23. SUMIF function adds all the sales amount in C18:C23 range if meet the criteria.
After applying SUMIF formula in excel worksheet, it returns the outcomes 2140 means the total sales amount is 2140 where the sales quantity is greater than or equal to 5.


Conclusion
Thus, in this post, you learn How to use SUMIF Formula in Excel. We hope you'll like this post. If you like this article then you can share with your friends, colleagues, and your relatives. We'll update the information on the regular basis. You can also share this post on facebook, twitter, Instagram or other social media website. If you have any query regarding this post then you can freely write in the comment section. We'll inform you as soon as possible.

Categories

Blog Archive

Booking.com