PEMDAS Rule

• Parenthesis
• Exponentiation
• Multiplication/Division
• Addition
• Subtraction

• FORMULA – is a statement which written by the user for calculations. Ex: =1+2+3
• FUNCTION – is a built-in formula by Excel. Ex: =SUM(1+2+3)

• To highlight duplicate values – use Conditional Formatting
• To get a number of duplicate values – use COUNTIF function.

• Slicer – is used to filter the Table, Pivot Table data visually.
• Timeline – is used to filter the dates interactively by Year, Month, Quarter and Day.

• Recommended Pivot Table – Based on the raw data, Excel will recommend some pivot table automatically.
• Recommended Charts – Same like as above, Excel will recommend charts (Ex: Column chart, Bar Chart, etc…) based on the data.

Names which we give for a cell/Range, Table will be managed by the Name Manager.

• COUNT – Counts the number of cells which contains only numbers except for blank cells.
• COUNTA – Counts the number of cells which contains alpha-numeric except blank cells.

Yes, it is possible. Using Pivot Table Data Modeling technique.

• VLOOKUP – Using VLOOKUP, we can retrieve the data from left to right in the range/Table.
• INDEX-MATCH – Using a combination of INDEX and MATCH, we can retrieve the data from left to right/right to left in a range/table.

Data > Get External Data section > Choose your data source

Option Explicit will force the user to declare variables. If the user uses undeclared variables, an error occurs while compiling the code.

Application – Workbooks – Worksheets – Range

A variant is the default data type in VBA.

Using F9 key.

Using $ symbol.

Ex: $A $1 – Here Locked Column A and row 1

The function will return a value whereas Subroutine may or may not return a value. The function can be called in the procedure. We can create custom functions using FUNCTION like as built-in functions.

No, it will not. VBA is Object based programming language, not Object Oriented Programming language.

ThisWorkbook – is the workbook where the VBA code is written.

ActiveWorkbook – is the workbook which is in Active state at present.

• Step by step execution – F8
• Breakpoints – F9
• Using Debug.Print
• Immediate Window
• Watch Window

• ADO –ActiveX Data Objects is a data access framework and is useful to get the data from the databases.
• ODBC – Open Database Connectivity is useful to get the data from the external database.
• OLEDB – Object Linking and Embedding, Database.

• #N/A
• #DIV/0!
• #VALUE!
• #REF!
• #NAME
• #NUM

Volatile functions recalculate the formula, again and again, so Excel workbook performance will be slow down. Volatile functions recalculate the formulas when any changes happen in the worksheet.

Ex: NOW (), RAND ()

Dashboards: Dashboard is a visual display of the data and these are dynamic and live, so data is being updated in real time and visuals can show changes from minute to minute.

Reports: Reports are not live and we use historical data to make reports. sometimes Reports are included with visuals such as Table, Graphs and Charts, Text, Numbers or anything.

Structured references – Instead of using cell references, we can use an Excel table name or the Column name for reference.

• .XLS
• .XLSX
• .XLSM (Macro-enabled workbook)
• .XLSB (Binary format)
• .CSV (Comma Separated Values)

In 2 ways we can pass arguments to VBA Functions

• ByVal
• ByRef

• Code Module: Default module to write procedures.
• User form: Helps to develop GUI (Graphical User Interface) applications.
• Class Module: Allows to create new objects.

The Collection object contains a set of related group of items as a single object.

Collections and Arrays are used to group variables. In Arrays, before using to start adding elements we normally set the size. But in Collection, we will not set the size, because we don’t know the number of elements in advance.

• Sheet Module
• Thisworkbook Module
• Code Module
• Userforms
• Class Modules

Below are the some tips –

• Turn off screenupdating
• Declare the variables and avoid “Variant” data type.
• Disable events
• Using the WITH statement.
• Avaoding the Select statement
• Select Case instaed of If Then

Implicit Declaration: When variables are used without declaration is called Implicit declaration.

Explicit Declaration: Declaring variables before using is called Explicit Declaration.

Arithmetic Operators, Comparison Operators, Logical Operators etc.

There are 2 types of arrays available in VBA.

• Single Dimensional Array: Single Dimensional array is used more often in the VBA. An array uses only one index.
• Multi-Dimensional Array: If an array has more than 1 dimension is called Multi-Dimensional Array.

Connection Objects are used to provide a connection between Excel and Databases with the help of ADODB Objects. So, we can interact with the database and can use the SQL queries to fetch the data from the database.

ADO stands for, ActiveX Data Objects.

VBA does not support all the OOPs concepts (VBA will support Polymorphism and Encapsulation, not supports Inheritance). Hence, VBA is called Object Based Programming Language.

Conditional Formatting is used to format cells/Range based on a condition/Conditions.

Ex: Highlighting a cell based on cell Value

Slicer is used to filter the Table, Pivot Table data. Instead of using Filters section in a Pivot Table, we can use Slicer.

Goal Seek – is used to achieve your goal by changing the dependent value.

Ex: If you have taken a personal loan, and if you can able to pay the EMI of 6K instead of 10K, how many months do you need to close your personal loan?

Scenario Manager – Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change.

UDF stands for User Defined Function, and these are custom functions. Using VBA, you can create your own functions and those can be used in Excel worksheets as normal built-in Functions.

• Assigning a macro to a shape.
• Assigning a macro to a button
• Run a macro from the ribbon
• Run a macro using a keyboard short cut key.

Using TRIM () function, we can delete duplicate spaces and gives unique/single space between words.

Data Validation – is used to validate the Data in a cell/Range. In Data Validation, we have criteria such as List, Whole Number etc. And have custom criteria option where we can give function/formula.

There might be different ways to find the duplicate values from a range. One of that is, using COUNTIF function we can find duplicate values.

Instead of VLOOKUP, we can use INDEX and MATCH function. Limits of VLOOKUP is we cannot fetch the data from the left side of the Lookup range. Using INDEX-MATCH, we can fetch the data any ways.

Using Calculated Field

• CONCATENATE( ) – used to join several text strings to one string
• TEXT( ) – Converting a value into text formatting
• PROPER ( ) – Arranging the characters in proper way.
• LEFT ( ) – Returns the specified number of characters from the starting character.