Top Advanced Excel Interview Questions and Answers

on

I am going to share basic Advanced Excel Interview Questions with Answers. It's really helpful for Advanced Excel fresher and Advanced Excel experienced candidate for getting the right Advanced Excel job.

Top Advanced Excel Interview Questions and Answers


Difference between ThisWorkbook and ActiveWorkbook in VBA?

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

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

Explain ADO, ODBC and OLEDB

  • 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.

How do you remove duplicate spaces from a cell?

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

How to run faster your VBA macro?

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

How will you debug codes in VBA?

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

How will you fix/lock the cell/range reference?

Using $ symbol.

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

How will you inspect a function/formula in Excel?

Using F9 key.

How would you add a new column to an existing pivot table for calculations?

Using Calculated Field

How would you get the data from different data sources?

Data > Get External Data section > Choose your data source

Is it possible to make a single Pivot Table for multiple data sources?

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

Name some of the Excel formats which an Excel file can be saved?

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

Name some of the operators available in VBA?

Arithmetic Operators, Comparison Operators, Logical Operators etc.

VLOOKUP Vs INDEX-MATCH

  • 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.

What are the different types of errors that you can encounter in Excel?

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

What are the error handling techniques in Excel VBA?

Item
On Error Goto 0
On Error Resume Next
On Error Goto [Label]
Err Object
Err. Number
Err. Description
Err. Source
Err. Raise
Error Function
Error Statement

What are the modules available in VBE?

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

What are the structured references in Excel?

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

What are the ways to create a dynamic range?

  • Creating a Table
  • Using OFFSET and COUNTA Functions

What is a Goal Seek in Excel?

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?

What is a Slicer?

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

What is a UDF in VBA?

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.

What is COUNT and COUNTA?

  • 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.

What is Data Validation in Excel?

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.

What is Excel object Model?

Application – Workbooks – Worksheets – Range

What is the difference between an Array and Collection?

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.

What is the difference between Report and Dashboard?

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.

What is the use of Option Explicit in VBA?

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

Which function will you use instead of VLOOKUP?

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.

Which object is used to work with Databases in the VBA?

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.

Why VBA is not Object Oriented Programming language?

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.

Comments

Post a Comment