How do you refresh a pivot table

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 blogMore about refresh pivot

Where is pivot table in Excel

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

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

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 correspending same value related right side value.

When we have duplicate key values or right side values. Vlookup function returns you first occurance 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 behaviour with duplicate values

Why Is My Vlookup Not Working

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

0
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

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.

How To Become Excel Macro or VBA Programmer From MIS Executive

0

Here you are going to learn the way how you can become VBA Programmer from MIS Executive job. We are going to provide you easy ways to become VBA Programmer and Excel VBA / Macro Programmer Qualities. When you have already became expert in Excel formulas, template and dashboards. Once you learn excel formulas, reports then you are very near to become a Excel Macro or VBA programmer. There are two ways to become a VBA or Excel Macro expert. First one recording macro procedure and second one is Writing VBA coding as per VBA standards. All you need to do initially if you have support, you can start with small project and get help from senior guy. Else you can start small excel activity automation with the help of google. Initially it is very tough to do each and every line of the code. But once you have done each and every thing in that particular small project then you learn many things which repeats in every Macro or VBA program. VBA programs are very tough when you do not start. Once you start all VBA programs become easier for you. This is kind secret behind VBA Program or any other programming language. Keep this in mind to start learning VBA Programming.

How To Become VBA or Excel Macro Programmer from MIS Executive Job
How To Become VBA or Excel Macro Programmer from MIS Executive

Excel VBA or Macro Programmer Qualities:

When you get one activity for automation purpose, first we need to see value added things and non value added things in that particular activity. This is essential task for VBA specialist. And once we identify non value added things then we have to eliminate those things from the activity first by automating those sub processes. Once we do this, then exact process would be remained. And we have to see alternative ways to reduce steps or providing accuracy or creating control checks. Also there is no necessary to write code for every activity, because sometimes without writing code we can automate processes like creating Excel formulas or creating templates or creating some dashboards or any other way. We need to choose simplest way to automate the process always.

As a VBA developer, when we get the automation requirement from any of the team members, we need to identify the Judgmental cases and non judgmental cases in order automate. As of now we have not find any best solutions to automate the judgmental cases. Hence we have to automate all the non judgmental cases. Sometimes judgmental cases would be in between the process. At that time, we need to automate till the non judgmental case ending then we have to stop the middle judgmental case for user to do that manually. Once he completes the judgmental case manually. Rest of the process would have been automated. That’s why user can just run the macro and finish the task.

Judgmental case:

Judgmental case is nothing but thinking ability. Like there will be situations like we have to think and then we need to do that thing according to the input.

Example 1 (Judgmental case):

we have to study the comments and need to do the activity based comment sentences.

Example 2 (Judgmental case):

we have see the image and do the activity based on the image. Here VBA Macro cannot see or think as of now. Hence it cannot be automated.

Examples (Non Judgmental case):

Copy pasting data, repetitive tasks.

Once we automate the process by using Excel macro programming, we need to check for error possibilities to arrest them and keep the control in order to check by user whether the particular task done by macro correctly or not. If we can do this, your programming style would be excellent and reliable macros are being developed by you.

Modifying the Existing Macro:

Sometimes, we need to modify the existing macro which is very tedious task to VBA Developer. Then we need to check the code line by line first and understand the process which was automated by different VBA Macro specialist. Once you we understand the process, then we can respond to associate who has given the task to yourself to do modify the macro.  Based on existing, you need to build the new code in case if it requires, or you just do simple changes to and deliver the project to given person. This is how you need handle the existing macros while modifying.