Interview Questions

Advanced Excel Interview Questions and Answers

Advanced Excel Interview Questions and Answers

Interview Questions January 12, 2019

Prepare for your Advanced Excel job interview with this list of top Advanced Excel Interview Questions and Answers for Freshers and Experienced which is Prepared by 10+ Years Experienced Advanced Excel Experts.

Advanced Excel Interview Questions and Answers

Advanced Excel Interview Questions and answers for beginners and experts. List of frequently asked Advanced Excel Interview Questions with answers by Besant Technologies. We hope these Advanced Excel Interview Questions and answers are useful and will help you to get the best job in the networking industry. This Advanced Excel Interview Questions and answers are prepared by Advanced Excel Professionals based on MNC Companies expectation. Stay tuned we will update New Advanced Excel Interview questions with Answers Frequently. If you want to learn Practical Advanced Excel Training then please go through this Advanced Excel Training in Chennai and Advanced Excel Training in Bangalore

Best Advanced Excel Interview Questions and Answers

Besant Technologies supports the students by providing Advanced Excel Interview Questions and answers for the job placements and job purposes. Advanced Excel is the leading important course in the present situation because more job openings and the high salary pay for this Advanced Excel and more related jobs. We provide the also Advanced Excel online Training for all students around the world through the Gangboard medium. These are top Advanced Excel Interview Questions and answers, prepared by our institute experienced trainers.

Advanced Excel Interview Questions and answers for the job placements

Here is the list of most frequently asked Advanced Excel Interview Questions and answers in technical interviews. These questions and answers are suitable for both freshers and experienced professionals at any level. The questions are for intermediate to somewhat advanced Advanced Excel professionals, but even if you are just a beginner or fresher you should be able to understand the answers and explanations here we give.

Q1) What are the ways to create a dynamic range?
  • Creating a Table
  • Using OFFSET and COUNTA Functions
Q2) What is the order of operations that Excel uses while evaluating formulas?

PEMDAS Rule

  • Parenthesis
  • Exponentiation
  • Multiplication/Division
  • Addition
  • Subtraction
Q3) Difference between FUNCTION and FORMULA in excel?
  • 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)
Q4) How will you find duplicate values in a column?
  • To highlight duplicate values – use Conditional Formatting
  • To get a number of duplicate values – use COUNTIF function.
Q5) What is the use of Slicer and Timeline in Excel?
  • 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.
Q6) What is Recommended Pivot Tables and Recommended Charts?
  • 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.
Q7) What is the Name Manager in Excel?

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

Q8) 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.
Q9) Is it possible to make a single Pivot Table for multiple data sources?

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

Q10) 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.
Q11) How would you get the data from different data sources?

Data > Get External Data section > Choose your data source

Q12) 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.

Q13) What is Excel object Model?

Application – Workbooks – Worksheets – Range

Q14) What is the default data type in VBA?

A variant is the default data type in VBA.

Q15) How will you inspect a function/formula in Excel?

Using F9 key.

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

Using $ symbol.

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

Q17) Difference between Function and Subroutine in VBA?

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.

Q18) Does VBA support OOPs concepts?

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

Q19) 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.

Q20) How will you debug codes in VBA?
  • Step by step execution – F8
  • Breakpoints – F9
  • Using Debug.Print
  • Immediate Window
  • Watch Window
Q21) 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.
Q22) What are the different types of errors that you can encounter in Excel?
  • #N/A
  • #DIV/0!
  • #VALUE!
  • #REF!
  • #NAME
  • #NUM
Q23) What are volatile functions in Excel?

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 ()

Q24) 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.

Q25) 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.

Q26) 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)
Q27) How will you pass arguments to VBA Function?

In 2 ways we can pass arguments to VBA Functions

  • ByVal
  • ByRef
Q28) 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.
Q29) What is Collection in VBA?

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

Q30) 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.

Q31) What are the modules available in Excel VBA?
  • Sheet Module
  • Thisworkbook Module
  • Code Module
  • Userforms
  • Class Modules
Q32) 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
Q33) 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
Q34) What are the ways in which a variable can be declared in the VBScript
language?

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

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

Q35) Name some of the operators available in VBA?

Arithmetic Operators, Comparison Operators, Logical Operators etc.

Q36) What are the types of arrays available in VBA?

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.
Q37) 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.

Q38) 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.

Q39) What is Conditional Formatting in Excel?

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

Ex: Highlighting a cell based on cell Value

Q40) 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.

Q41) 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?

Q42) What is Scenario Manager in Excel?

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.

Q43) 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.

Q44) What are the ways to run a macro?
  • 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.
Q45) How do you remove duplicate spaces from a cell?

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

Q46) 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.

Q47) How will you find the number of duplicate values in a range?

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.

Q48) 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.

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

Using Calculated Field

Q50) Can you name some Text Functions?
  • 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.

Besant Technologies – Chennai & Bangalore Branch Locations

Besant Technologies - Velachery Branch

Plot No. 119, No.8, 11th Main road, Vijaya nagar,

Velachery, Chennai - 600 042

Tamil Nadu, India

Landmark - Reliance Digital Opposite Street

Besant Technologies - Tambaram Branch

No.2A, 1st Floor, Duraisami Reddy Street,

West Tambaram, Chennai - 600 045

Tamil Nadu, India

Landmark - Near By Passport Seva

Besant Technologies - OMR Branch

No. 5/318, 2nd Floor, Sri Sowdeswari Nagar,

OMR, Okkiyam Thoraipakkam, Chennai - 600 097

Tamil Nadu, India

Landmark - Behind Okkiyampet Bus Stop,

Besant Technologies - Porur Branch

No. 180/84, 1st Floor, Karnataka Bank Building,

Trunk Road, Porur, Chennai - 600 116

Tamil Nadu, India

Landmark - Opposite to Gopalakrishna Theatre

Besant Technologies - Anna Nagar Branch

Plot No:1371, 28th street kambar colony,

I Block, Anna Nagar, Chennai - 600 040

Tamil Nadu, India

Landmark - Behind Reliance Fresh

Besant Technologies - T.Nagar Branch

Old No:146/2- New No: 48,

Habibullah Road,T.Nagar, Chennai - 600 017

Tamil Nadu, India

Landmark - Opposite to SGS Sabha

Besant Technologies - Thiruvanmiyur Branch

22/67, 1st Floor, North mada street, Kamaraj Nagar

Thiruvanmiyur, Chennai 600041

Tamil Nadu, India

Landmark - Above Thiruvanmiyur ICICI Bank

Besant Technologies - Siruseri Branch

No. 4/76, Ambedkar Street, OMR Road, Egatoor, Navallur,

Siruseri, Chennai 600130

Tamil Nadu, India

Landmark - Near Navallur Toll Gate, Next to Yamaha Showroom

Besant Technologies - Maraimalai Nagar Branch

No.37, Ground Floor, Thiruvalluvar Salai,

Maraimalai Nagar,Chennai 603209

Tamil Nadu, India

Landmark - Near to Maraimalai Nagar Arch

Besant Technologies - BTM Layout Branch

No 2, Ground floor, 29th Main Road,

Kuvempu Nagar,BTM Layout 2nd Coming from Silkboard,

AXA company signal, Stage, Bangalore - 560 076

Karnataka, India

Landmark - Next to OI Play School

Besant Technologies - Marathahalli Branch

No. 43/2, 2nd Floor, VMR Arcade,

Varthur Main Road, Silver Springs Layout,

Munnekollal, Marathahalli, Bengaluru - 560037

Karnataka, India

Landmark - Near Kundalahalli Gate Signal

Besant Technologies - Rajaji Nagar Branch

No. 309/43, JRS Ecstasy, First Floor,

59th Cross, 3rd Block, Bashyam Circle,

Rajaji Nagar, Bangalore - 560 010

Karnataka, India

Landmark - Near Bashyam Circle

Besant Technologies - Jaya Nagar Branch

No. 2nd Floor,1575,11th Main Road,

4th T-Block, Pattabhirama Nagar,

Jaya Nagar, Bangalore - 560 041

Karnataka, India

Landmark - Opposite to Shanthi Nursing Home

Besant Technologies - Kalyan Nagar Branch

No.513, 4th Cross Rd

2nd Block, HRBR Layout,

Kalyan Nagar, Bangalore - 560 043

Karnataka, India

Landmark - Opposite to kalayan nagar Axis Bank

Besant Technologies - Electronic City Branch

No. 7, 3rd Floor, Ganga Enclave,

Neeladri Road, Karuna Nagar, Doddathoguru Village,

Electronics City Phase 1, Electronic City,

Bangalore - 560100, karnataka, India

Landmark - Adjacent to HDFC Bank and State Bank Of India

Besant Technologies - Indira Nagar Branch

No.54, 1st Floor,

5th Main kodihalli,

Bengaluru, Karnataka 560008, India

Landmark - Behind Leela Palace Hotel,

Besant Technologies - HSR Layout Branch

Plot No. 2799 & 2800, 27th Main,

1st Sector, HSR Layout,

Bengaluru, Karnataka 560102, India

Besant Technologies - Hebbal Branch

No.29, 2nd Floor, SN Complex,

14th Main Road, E-Block Extention, Sahakara Nagar,

Bengaluru, Karnataka -560092, India

Scroll Up