Programming Logic

Preparation tips for Excel Interview

Microsoft Excel is one of the commonly used tools for data handling and processing. Its features like calculation, pivoting tables, plotting graphs and charts and making custom computational program with Visual Basics are used across industries by beginner MIS to mid management for reporting and top management for analysis and forecasting. Excel is an extremely tool to learn if you are taking job in any industry, and particular where you need to deal with data. Xamnation provides Excel interview preparation help for data calculation, graphics, using complex functions and many more.

How to prepare for Excel Interview

Everyday millions of people use Microsoft Excel in their day to day work life. Prime uses of Excel are to do statistical and Mathematics computing, and get meaningful insights by applying various mathematic functions or VBA programming of data.  In Excel, you have to work on different worksheets; each of them contains cells where you will put your data for analysis by selecting different ranges and applying appropriate functions on data. You can also format the cells and tables, plot graphs and charts and save and print data in desired format. You can apply Keyword shortcuts, do data validation and many more in Microsoft Excel.

Commonly used functions in Excel

Main functions and term in Excel

  • Count and Sum: Count the selected cells and get sum of selected cells
  • Logical: Use binary operators like AND, OR, NOT and conditional term like IF
  • Cell References : Select range of cells, you can use symbol like $ to select complete row or column
  • Date & Time: Use data and time in desired format. You can customize time format too.
  • String: Manipulate string(s). You can get substring, or concatenate 2 strings and many more
  • Lookup & Reference: Use Vlookup and Hlookup to get data from another set of cells in same or different worksheet
  • Financial: Use financial functions in excel
  • Statistical: Use statistics functions like average, mean, etc in excel
  • Round: You an round to nearest shortest or largest number
  • Array Formulas: Perform multiple calculation in range of cells
  • Sort: Sort data numerically or alphabetically in case of string
  • Filter: Filter data using different conditions
  • Conditional Formatting: Format cells and give different color code on basis of different conditions
  • Charts: Create different charts, graphs, plot showcasing your data
  • Pivot Tables: Use pivot table functionality to analyze data better
  • What-If Analysis: Try different values for formulas
  • Solver: Find optimal solution for decision problem. This is inbuilt tool in Excel

Key functions in VBA programming in Excel

  • Create Macro: Automate your repetitive task by writing macros
  • MsgBox: Dialog box to inform user on the program
  • Workbook and Worksheet Object: Work on the worksheet object, and property of object on VBA progam
  • Range Object: Range of cells in the program on which we are writing program
  • Variables: Declare and initialize variable to be used in VBA program
  • If Then Statement: Conditional programming in Visual basic
  • Loop: Repetition of program over range of cells using iterative or recursive loop
  • Macro Errors: Deal with macro error in excel
  • String Manipulation: Handle string operation in VBA
  • Date and Time: Handle date and time in VBA
  • Events: Handle user generated event in VBA
  • Array: Group of variable defined in VBA
  • Function and Sub: User defined function in VBA. Function returns value while Sub does not.

Commonly asked advanced MS Excel interview questions

  • Assume that you will make an analysis and you cannot use pivot tables. Instead, which formulas can you use to replicate pivot table basic behavior?
  • How do you use weighted mean in an excel?
  • What is the use of Name box in excel?
  • Have you ever used array formulas? Why do we need it?
  • Do you record macros? Can you edit them?
  • How to use/query external data set?
  • How can we merge multiple cells text strings in a cell?
  • How to sort just one column without sorting the rest in excel
  • How do you connect your excel with another excel file on another computer? Is it possible?
  • If you have a big data to analyze (hundred thousands of records) and your calculations are taking too much time every time you change something on the file. What can you do about this?
  • How to check 2 columns to check whether both having same data/not?
  • How and why to use INDEX & MATCH instead of VLOOKUP?
  • How to use Data Modeling for creating Pivot Table
  • Do you write VBA code besides recording macros? For example, can you write a basic loop to check a range of cells and if met specific condition hide their columns?
  • Let’s assume you are going to implement a pricing for product quantity. But, you don’t want it to be linear. Instead, you will decrease the price per pro

Looking For Excel Interview Help?

Are you finding Excel interviews tough to crack, and are need of technical assistance from experts. Or are you looking for advanced excel questions and answers. Or do you need to solve a complex excel assignment for job interview?

You don’t need to worry anymore. We, at Xamnation, are providing excel interview help. You can choose from course options like excel question bank for interview, and one to one training with trained mentors or solving your own excel assignment.

Get excel interview help for:

  • One to one excel training ;
  • Advanced excel questions and answers bank;
  • Refresher course on important excel concepts;
  • Mock excel test for interview
  • Practice assignment for job interview;

Leave a Comment

Your email address will not be published.