Microsoft Excel Training

You cannot completely eliminate bugs in your programs, but there are a few tips that will help you keep them to a minimum:

i. Use an Option Explicit at the beginning of your module: Doing so will require that you define the data type for every variable that you use. It’s a bit more work, but you’ll avoid the common error of misspelling a variable name.. And there’s a nice side benefit: Your routines will often run faster.

ii. Format your code with indentation: Using indentation to delineate code segments is quite helpful. If you have several nested For Next loops, for example, consistent indentation will make it much easier to keep track of them all.

iii. Be careful with On Error Resume Next: This statement causes Excel to ignore any errors and continue. In some cases, using this statement will cause Excel to ignore errors that shouldn’t be ignored. Your may have bugs and not even realize it.

iv. Use lots of comments: Nothing is more frustrating than revisiting code that you wrote six months ago, and not having a clue as to how it works. Adding a few comments to describe your logic can save you lots of time down the road.

v. Keep your subroutines and functions simple: Writing your code in smaller modules, each of which has a single, well-defined purpose, makes it much easier to debug them.

vi. Use the macro recorder to help you identify properties and methods: If I can’t remember the name or syntax of a property or method, it’s often quicker to simply record a macro and look at the recorded code.

vii. Consider a different approach: If you’re having trouble getting a particular routine to work correctly, you might want to scrap the idea and try something completely different. In most cases, Excel offers several alternative methods of accomplishing the same thing.

viii. Understand Excel’s debugger: Although it can be a bit daunting at first, you’ll find that Excel’s debugger is an excellent tool. Invest some time and get to know it.

Our day to day work involves excel and to complete our work fast we should have adequate knowledge of keyboard shortcuts. I am going to give you simple but useful tip which will helps you to navigate excel and perform tasks much quicker without touching the mouse.

Use arrow keys in combination of Ctrl/Shift key to traverse your Excel sheet:

• Ctrl + Down arrow: Data Region Down.
• Ctrl + Up arrow: Data Region Up.
• Ctrl + Right arrow: Data Region Right.
• Ctrl + Left arrow: Data Region Right.

Use Shift key with the above combination to select the data e.g. Ctrl+Shift+Down arrow to select the data range from current cell to last filled cell of the data region.

Some more useful shortcuts:

• Ctrl+ Space Bar: Selects the entire column.
• Shift+ Space Bar: Selects the entire row.
• Ctrl + PageUp: Previous sheet.
• Ctrl + PageDown: Next sheet.
• Ctrl + Home: Selects cell A1 of the active sheet.
• Ctrl + 1: Opens Format Cell window.
• Ctrl+Shift+1: Changes the cell format to Number.
• Ctrl+Shift+2: Changes the cell format to Time.
• Ctrl+Shift+3: Changes the cell format to Date.
• Ctrl+Shift+4: Changes the cell format to Dollar.
• Ctrl+Shift+5: Changes the cell format to Percentage.
• Ctrl+Shift+6: Changes the cell format to Scientific.

And finally Ctrl + Z to undo what ever mess you have done the excel sheet while practicing these shortcuts.

Syntax:

DGET(Database, Field, Criteria)

The DGET function syntax has the following arguments:

• Database Required: The range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

• Field Required: Indicates which column is used in the function. Enter the column label enclosed between double quotation marks, such as "Age" or "Yield," or a number (without quotation marks) that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

• Criteria Required: The range of cells that contains the conditions that you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column.

Remarks:

• If no record matches the criteria, DGET returns the #VALUE! error value.

• If more than one record matches the criteria, DGET returns the #NUM! error value.

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Have you every tried Slicer option in Excel? There are lot many features in excel which go unnoticed even if they are visible
As you know, one of the powerful tool in excel is Pivot table which summarizes your data and we know the drag and drop features of it.

Use Slicer option in Pivot table instead of Report Filter to filter down categories. Slicer works exactly the same way the Report Filter works however, slicer will provide you a front-end UI lists with button click option for easy use. This helps the user to quickly select the required categories just by clicking them, which also looks appealing.

To insert a slicer, execute the following steps.
1. Click any cell inside the pivot table.
2. On the Analyze / Options tab, in the Filter group, click Insert Slicer.

Hold Ctrl button to select multiple categories

Thanks!!!
Rakesh