COUNTIF function – Excel Formula

0

Here you are going to learn about the way how you can use countif formula in excel spread sheet. Countif formula is very handy to count totals for numeric values in excel.

 

SUMIF function – Excel Formula

0
Sumif Function in Excel
Sumif Function in Excel

Here you are going to learn the way how you can use sumif function in Excel spreadsheet to easy the calculation process in the office activities. Sumif is one of the key functions in MIS activities and other office activities like HR Payroll, Call center activities, Banking, Finance and accounting activities.

Sumif can be used to calculate (sum) totals based on particular condition. Example if you have fruits list with their amounts per 1000 (1 Kilo gram) grams. In that list Apples, Oranges, Pomegranates and Butter fruits were included with repeatedly with their various rates in different Markets. Now I need the total amount of Apples from all markets. Here is the perfect situation to use Sumif function. Please follow the below snap shots to understand more clear way. Also if you need download Excel file with the same example by clicking article ending download link.

 

Apple sum using Sum if function as shown in the below

Sumif Example snap shot 1
Sumif Example snap shot 1

Sum if Syntax as shown in the below snap shot 2

Sumif Example with Syntax Snap shot 2
Sumif Example with Syntax Snap shot 2

Sumif Syntax Explanation :

Range, Criteria, Sum Range are syntax elements

Range:

In Sumif, range is data range which should be started from Criteria related column on wards as shown in the above. In the above case Column B has criteria (Apples). Hence we have selected from Column B to Column E. As Amounts are presented in the Column E.

Criteria:

Criteria is one which should be presented in the condition, in the above case Apples are the criteria. Hence we have selected Apples related cell B2 (or you can given hardcoded text like “Apples” either way it works this function but ideal way is better to give cell value).

Sum_range:

Sum_range is numerical range which is column E in the above case as amounts are presented in the column E.

 

Note: If you need more details on this just click on this link.

Download Sumif Example in excel spreadsheet

 

If you still have any doubts on the above sumif example, please comment below. As soon as we get time, our team is here to help you to understand more better way.

 

 

 

 

 

How do you refresh a pivot table – Excel Formula

0
Refresh Pivot Table
Refresh Pivot Table

Whenever you do changes in the source data of the pivot table, you need to refresh in order to show the updated data. Here you can learn the way you can refresh the pivot table in excel.

There are two methods to refresh pivot table in excel. You can follow below screen shots to understand in clear cut manner.

1st Method:Please observe the below source data of the below pivot table. Source data row number is 12th and here we are going to add 2 lines of the data to find changes in the below pivot. Below pivot table row number also 12th before update data in the source data sheet.

Pivot table data source to show pivot table option in excel
Pivot table data source to show pivot table option in excel

 

Pivot table before update
Pivot table before update

In the above pivot table right click and you can find refresh option as shown in the below screen shot

Refresh by right click on pivot table
Refresh by right click on pivot table

Once refresh the data you can see updated data that 2 rows were increased in the below updated pivot table

Updated pivot table after source data update
Updated pivot table after source data update

2nd MethodIn the same pivot table we can go to data menu Refresh all option as you can find the refresh option.

Refresh option by clicking on data menuu
Refresh option by clicking on Data menu

Before refresh ensure that you have increased source data by click on option menu in the above screen shot. If you want to do the exercise you can download pivot table example excel file from our blog

More about refresh pivot

Where is pivot table in Excel – Excel Formula

0
Pivot Table
Pivot Table

Pivot table is one of the key options in excel. When you have huge date and need to be checked particular selected columns of data then this option can be very useful. You can find more details in the below

Step 1

Please assume the below is the source data and select the entire data by clicking Ctrl +A or manually to choose pivot table

Pivot table data source to show pivot table option in excel
Pivot table data source to show pivot table option in excel

Step 2

Select Insert Menu and there you get Pivot table option as shown in the below screen shot

Pivot table option in the Insert Menu of Excel
Pivot table option in the Insert Menu of Excel

Step 3

You get below create Pivot table option window and there you need to select either Existing Worksheet option or New Worksheet option then clock OK button

Create Pivot Table Option
Create Pivot Table Option

Step 4

Then you get pivot layout here in the above case I have selected new sheet. Hence pivot layout arrives in the new worksheet as shown in the below

Pivot Layout and Pivot Table Field List
Pivot Layout and Pivot Table Field List

Step 5

Drag the Pivot field list related column names based on your requirement. Here in the below pivot example I have taken Names column as Row Label and English subject as Values

Pivot Table Final Output
Pivot Table Final Output

Note 1: Hope you understand the way how and where you can select the pivot table in excel. You can download the above example from the below. If you have any doubts about pivot table or any excel related doubts write comments below and I will try to answer for your queries.

 

Download Pivot Table Example [xlsx] format

How do you enable macros in excel – Excel Formula

0
Enable macros in excel
Enable macros in excel

If we need to automate excel activities using excel macros, we must need to enable macros excel

There are two ways to enable excel macros in order to explain please arrange below excel sample macro code in the excel back end macro window. To get below window in excel, please follow below steps

    • Open excel
    • Click on ALT + F11 (shortcut) then you can see below macro window there you can type any macro or please type below code for testing
  • Then find below window
Sample macro code to show you how to enable macros in excel
Sample macro code to show you how to enable macros in excel
  • Save the excel file with any name. While saving keep the Save as type Excel Macro-Enabled Workbook
Excel Macro-Enabled to show you how to enable macros in excel
Excel Macro-Enabled to show you how to enable macros in excel
  • Save the file and close
  • Open the saved file here in the above case I have saved as Enable macro example. Hence I open the same file as shown in the below
Enable this content window for options button
Enable this content window for options button to explain how do you enable macros in excel concept
  • Now click on Options button in the above to see below screen shot related window

 

Enable this content window to explain how do you enable macros in excel
Enable this content window to explain how do you enable macros in excel
  • Click ok.

Note: The above methodology is the best way to enable macros in excel

 

There is another way also to enable macros in excel. But this is not advisable process. Here I am going to show you in simple way

  • Click on file window option as shown in the below screen shot
Excel options to enable macros in excel
Excel options to enable macros in excel
  • Then click on excel options in the above screen to get below screen shot related window

 

Trust Center window to enable macros in excel
Trust Center window to enable macros in excel
  • Click on Trust Center option in the above screen then click on Trust center settings in the same window to get below window
Enable all macros to show you how to enable macro 2nd process
Enable all macros to show you how to enable macro 2nd process

Note : Hope you understood the way you can enable or disable.

Do you want to know more click here

If The Search Column For A Vlookup Contains A Duplicate Value – Excel Formula

2
Vlookup function with duplicate values
Vlookup function with duplicate values

This articles would be clear your doubt on Vlookup with duplicate values with details explanation. Vlookup can provide many variety of results.

Usually Vlookup would be used to search right side values in the data using left side value. Here left side value as key to search the corresponding same value related right side value.

When we have duplicate key values or right side values. Vlookup function returns you first occurrence of the value. Please find below image to understand in more detailed

 

If you see the above image, Trump name is there twice and formula written for Trump’s values. Since it is there for twice. It is returning first instance related value. Usually in this case Vlookup does not fit to pull details. If you get as such requirement, you need to slightly modify the vlookup functionality using user defined functions. Sometimes we get requirement to pull all details of key value. This is very rare case. Still we need to know the way how we have to do that. Stay tuned, I will provide you the way how we can create to pull all the duplicate values for key value in vlookup.  Hope you understand vlookup behavior with duplicate values

Why Is My Vlookup Not Working – Excel Formula

0
Vlooup #NAME Error
Vlooup #NAME Error

Here you are going to learn the way how we have to fix the issue when your Vlookup is not working. If you would like to learn about VLOOKUP in detailed click here. First you need to the error types that occur in excel. First one is syntactical error and the second one is logical error for any formula these are the main error types occur. Hence first check syntax of the VLOOKUP that you have entered in your sheet. It should be like =Vlookup(lookupvalue, tablearray, columnindexnumber, false)

Lookupvalue: The value which you need to search in particular data range.

TableArray: This would be your lookup value related column to result existed column (Example: if you have lookup value in ‘A’ column and results are in column ‘C’, then your table array would be A:C.

Column Index Number: This is your result column number in the above example, your column index number is 3. Because A is first column, B is second column and C is your result column that is 3rd column.

False: This indicates zero always (that is known as approximate match)

Hence in the above example if we consider, we can get syntax in the below manner

if you are looking result in the same sheet

=Vlookup(A1,A:C,3,0)

if you are looking result in the different sheet then you need to add sheetname in the table. Please find below for syntax for your reference

=Vlookup(A1,Sheet2!A:C,3,0)

If you miss any thing in the above for any scenario, you get an error that would the syntactical error.

Now let’s understand logical error. If you enter syntax correctly still if you are getting error as “#N/A” then your vlookup working correctly. Still you need to check whether you have selected correct column or not in the respective syntax line. For example in the above described example. Instead of A:C , you selected B:C in the table array. that would be logical error and you will not get results. Because you have not specified look value related column in the table array.

If you enter column index number as 4 in the above example, then also vlookup throws an error. Then you need realize that you have not give correct number to lookup the value in the formula. Then your error would be #VALUE.

if you enter 1 in the last block instead false or zero, then also your formula throws an error. This is how you need to figure it out to work your vlookup correctly.

 

How To Delete Duplicates In Excel – Excel Formula

1
Deleting Duplicates In Excel
Deleting Duplicates in Excel spread sheet

We have multiple ways to delete duplicate values in excel.  We have remove duplicate option itself in 2007 Excel version or higher versions.  If we need to delete manually in the sense, we have to follow below methods to do this.

1st Method:

  • Sort data either in A to Z or Z to A methodology in the required column
  • Next to that column keep formula = currentcell = Below cell.

(Note: For example if you need to identify duplicate values in the 1st column. Then in the 2nd column, in cell B1 you need to keep the formula as =A1=A2, then drag this till end of the data of the 2nd column)

  • If you get TRUE as result in the cells of 2nd column, those all are duplicates. You can just filter those to delete.

2nd Method:

Assume that you have to find duplicates in the 1st Column, then  just select B1 cell and keep formula  as “=Countif(A:A,A1). Then drag this till ending data cell of 1st Column. If you get more than one as number in the 2nd Column, those all are duplicates. Now you can delete by filtering

3rd Method:

We have direct option as remove duplicates, there you can just select and go to Data Menu -> Select Remove duplicates option to Choose column ->Click Ok. Then also it deletes duplicates.  Here you can use shortcut as Press ALT +A +M to get remove duplicates window after selection data by CTRL+A.

How to find Last Row in Excel VBA – Excel Formula

0

There are several ways to find last row in the excel VBA. Finding last row is very important in the Excel VBA programming to work with ranges in spreadsheet. Based on your user or client requirement, you can use the below any one of the method in order to get last row value into one variable. The same can be used in particular worksheet in excel macros.

Usedrange.Rows.Count :

In case if you are going to update data in the worksheet by yourself as part of your automation, you can use this method to get last row of the particular excel spreadsheet.  However this method can be used only when you create new worksheet or created by your program related worksheet.

Example:

Dim Lastrow as long

Lastrow= Activesheet.usedrangerows.count

Or

If worksheet name is Summary then, you can define as shown in the below

Lastrow= Sheets(“Summary”).usedrange.rows.count

Or

Lastrow=Worksheets(“Summary”).usedrange.rows.count

 

Advantages of Usedrange.Rows.Count :

There is no requirement to specify any particular column or column letter to grab last row in this method. You can directly assign to one variable and use the same in the respective worksheet.

Disadvantages of Usedrange.rows.count:

If you use this predefined method in existing input excel spreadsheet, there are chances to get incorrect last row. Sometimes if excel spreadsheet downloaded from web portal or any other source, you get some garbage values in sheet.

Cells(rows.count,columnnumber).end(xlup).row:

 Using this also you can get last row and use the same in entire VBA program for the respective worksheet.  To use this, you should have non blank cells in any one particular column range. Then we can use that column number in this method as shown in the below example

Dim Lastrow as long

(Note: Better to define lastrow as long. It is best practice, because number of rows can be exceeded integer datatypelimit. If you know that rows compulsorily lesser than 32K, then you can go with integer)

I am assuming 1st column has non blank cells within the range)

Syntax: Lastrow=Sheets(“Summary”).cells(rows.count,1).end(xlup).row

Advantages of cells(rows.count,columnnumber).end(xlup).row:

This method always provides you accurate value of last row in particular column range. Hence you can use this in input files in excel macro or VBA.

Disadvantages of cells(rows.count,columnnumber).end(xlup).row:

We have to take care to use non blank cells column. Else there is a chance to get incorrect value into last row.

Range(rows.count,”columnletter”).end(xlup).row:

 In this method also we should have at least with column range with non blank cells.

Dim Lastrow as long

Syntax: Lastrow=Sheets(“Summary”).cells(rows.count,”A”).end(xlup).row

Note: Here I consider column “A” has non blank cells.