Showing posts with label Excel. Show all posts
Showing posts with label Excel. Show all posts

Sunday, 20 September 2015

Thursday, 14 August 2014

vlookup match formula in excel

vlookup match formula in excel:

vlookup match formula in excel

" VLOOKUP MATCH is an improved version of your basic VLOOKUP or INDEX MATCH formula."

Need of vlookup match: 

  • Using VLOOKUP MATCH allows you to perform a matrix lookup – instead of just looking up a vertical value, the MATCH portion of the formula turns your column reference into a dynamic horizontal lookup as well.  

  • VLOOKUP MATCH is mainly useful for situations where you intended to perform heavy editing on your data set after you’ve finished writing your formula.  

  • VLOOKUP MATCH gives your lookup formula insertion immunity; whenever you insert or delete a column within your lookup array, your formula will still pull the correct number.

    Working:
    vlookup match formula in excel

     

Read More
Posted on 7:31:00 am
Categories:

Friday, 1 August 2014

Data Analysis in Excel

 Data Analysis in Excel: 

Data Analysis in Excel

 

Excel is product of Microsoft used to store data in tabular format. So there is high requirement of data analysis.
There are various tools available to manipulate data in excel some of them are listed here with step by step screen shot.

Data Analysis in Excel:

1. Sorting:

It is used to sort raw data according to requirement.

2. Filter:

Filter is used to filter data according to their need.

3. Conditional Formatting:

It is used to apply formatting according to condition.

4. Charts:

chart is used fro data representation in effective and simple manner.

5. Pivot Tables:

Best way to represent the data. Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the

significance from a large, detailed data set.

Formulas:

Read More
Posted on 8:03:00 am
Categories:

Conditional Formatting in Excel

Conditional Formatting in Excel:

Conditional Formatting in Excel


How to use conditional formatting on raw data in excel.

Step 1.

Select a list from column on which you want to apply the conditional formatting.


Step 2.

Click on home tab in excel and select conditional formatting tab. Choose condition from drop down.


Step 3.

Put your condition accordingly.
Read More
Posted on 8:01:00 am
Categories:

Filter in Excel

Filter in Excel:

Filter in Excel

How to use filter on raw data in excel.

Step 1.

Click on any cell in the column to filter data.


Step 2.

Click on Data tab in excel and select Filter tab. Click on column header.



Step 3.

Check option accordingly appeared in the image.

Read More
Posted on 8:01:00 am
Categories:

Sorting in Excel

Sorting in Excel:
Sorting in Excel:

Steps to sort raw data is as follows:

Step 1.
Click on any cell in the column which you want to sort.


Step 2.

Click on Data tab in excel and select Sort.


Step 3.

Choose column from drop down and select order of sorting.



Step 4.

Result appeared as if sorting is applied on first column and A->Z.

Read More
Posted on 8:01:00 am
Categories:

Pivot Table in Excel

Pivot Table in Excel:

Pivot Table in Excel


Select whole table and Go-to insert tab and click on pivot table. 



Drag and drop column accordingly.


On pivot table you are able to Filter and conditional formatting and chart also.
Read More
Posted on 8:00:00 am
Categories:

Index formula in excel

Index Formula:

index formula in excel

Array: is a range of cells or an array constant.

    If array contains only one row or column, the corresponding row_num or column_num argument is optional.
    If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.

Row_num : Selects the row in array from which to return a value. If row_num is omitted, column_num is required.

Column_num : Selects the column in array from which to return a value. If column_num is omitted, row_num is required.

Read More
Posted on 8:00:00 am
Categories:

index match formula in excel

index match formula in excel:

index match formula in excel

If there is a different scenario in vlookup.

Primary key column is on right side vlookup are unable to fetch data. so here we use index match combined to get resultant data.
Syntax
=INDEX(D5:G10,MATCH(J6,H5:H10,0),1)
Read More
Posted on 7:05:00 am
Categories:

Match Formula in Excel

 Match Formula in Excel:

 Match Formula in Excel

 MATCH(lookup_value, lookup_array, [match_type])

    lookup_value : Required. The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.

The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

    lookup_array:  Required. The range of cells being searched.
    match_type:  Optional. The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
Read More
Posted on 7:03:00 am
Categories:

Friday, 25 July 2014

Magic of V-Lookup

Magic of V-lookup:Magic of V-Lookup

Solution of following questions.
  • How to use vlookup

  • Working of Vlookup?

  • Introduction to vlookup?

As the name implies, V-Lookup, stands for Vertical Lookup, which is used to find specific information column wise vertically that has been stored in excel sheet.

Let’s study the Formula/Syntax of V-lookup function. It consists of four parts.

Lookup_value - This is the index which we have to search in the table.

Table_array, col_index_num - This is table array in which we search the look up value . This argument is a named range or a reference to a range of cells. Here, in above our table range is cell H2 to J7.

Col_index_num- Enter the column number of the table_array from which you want data returned from means which data you want to print as result. In above example, column index is 2 since value of name is 2nd position.

Range_lookup:
  A logical value (TRUE 1 or FALSE 0 only) that indicates whether you want VLOOKUP to find an exact or an approximate match to the lookup_value.

    If TRUE(1) or if this argument is omitted, VLOOKUP will use an approximate match if it cannot find an exact match to the lookup_value. If an exact match is not found, VLOOKUP uses the next largest lookup_value.
    If FALSE(0), VLOOKUP will only use an exact match to the lookup_value. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, an #N/A error is returned.
Read More
Posted on 12:00:00 am
Categories: ,

Friday, 11 July 2014

Read Excel File in C#

Read Excel File in C#:


Read Excel File in C#

Design a form in Visual studio add a button ,Add a folder upload and paste an excel data file.
Button Click event write the below code:

Coding:

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OleDb;
 
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string target = Server.MapPath("~/Upload");
        if (FileUpload1.HasFile)
        {
            FileUpload1.SaveAs(System.IO.Path.Combine(target, FileUpload1.FileName));
            string connString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Text;", System.IO.Path.GetDirectoryName(target + "\\" + FileUpload1.FileName));
            string cmdString = string.Format("SELECT * FROM {0}", System.IO.Path.GetFileName(target + "\\" + FileUpload1.FileName));
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdString, connString);
            DataSet dataSet = new DataSet();
            dataAdapter.Fill(dataSet);
            GridView1.DataSource = dataSet.Tables[0];
            GridView1.DataBind();
        }       
    }
}

If this is not satisfactory for you write us to get assistance Contact Us
Learn more about excel:Shareyourconscience
Read More
Posted on 8:18:00 am
Categories: ,

Open URL in Excel Using Marcro

Open URL Using Macro:

Open URL in Excel Using Marcro

Excel is very popular product developed by Microsoft. This LOC is working in all version of excel.
Please follow the steps:
Step1: Insert a shape and type name for  it.
Step2: Right click on shape and select  assign Macro
A popup window appears click on new button .
A Visual Basic interface appears:
Paste the below code .
Save the sheet.

Open URL in Excel Using Marcro
Paste the url(which you want to open) in B7 cell .
If this is not satisfactory for you write us to get assistance Contact Us
Learn more about excel:Shareyourconscience
Read More
Posted on 7:25:00 am
Categories:

Friday, 27 June 2014

Excel-Learning-Part-1-Introduction

Excel Tab Home:

Introduction:

Cell:Area where pointer points is called cell.
cell
Excel-Learning

Copy: To copy particular cell . Cut: To cut particular cell. Paste:To paste particular cell.
Format Painter: It is used to copy format of cell/cells and apply it to area of interests.
Font Selector: Select font from drop down.Size Selector:Select size of font.
Font Size Increase/Decrease: Click to increase/decrease the font.
Bold,Italic,Under Line. Border Selector: Select border according to your choice.Cell Color: Change cell color,Font Color: Change font color.
Alignment:To change alignment.
Orientation:Horizontal, Vertical.
Increase/Decrease Intends:Increase intends between border.


Excel-Learning
This portion is for currency format choose currency format accordingly. 
Conditional formatting: This is used to change formatting according to certain condition.
Format as table: To format the data in to table select any temple from here.



Insert: Insert Cell,Row,Column.
Delete:Delete Cell,Row,Column.
Auto Sum:Formula to sum cell.
Fill:To fill cell.
Clear: To clear format ,content,comment hyperlink
Sort & Filter:Sort the data accordingly.
Find & Search:To find particular value.
Posted By:Tanuj Kumar 
Wants to learn about Technology Goto Geniuslake
Read More
Posted on 9:17:00 pm
Categories:

Tuesday, 1 October 2013

Unhide all sheet in MS EXCEL 2010 Using Macro By:Tanuj Kumar

Unhide all sheet in MS EXCEL 2010 Using Macro

Unhide all sheets in MS Excel 2010 using Macros.

Goto view>>Macros>>view macros>>create
then copy paste the below code then save the sheet.

Dim all_ws As Worksheet
For Each all_ws In ActiveWorkbook.Worksheets
all_ws.Visible = xlSheetVisible
Next all_ws

confirm to save.
Now run the create Macros.




Posted By:Tanuj Kumar 
On:Shareyourconscience

Read More
Posted on 1:01:00 am
Categories: