Excel Interview Questions, Excel Interview Questions and Answers

Excel Interview Questions and Answers, Excel Interview Questions and Answers Freshers, Excel Interview Questions and Answers, Excel Interview Questions

Before getting on to the Excel interview questions, the student must know that the Excel is a continuously varying field which needs the students as well as professionals to upgrade their skills with the new features and knowledge, to get fit for the jobs associated with Excel. This post related to Excel Interview Questions and Answers, Excel Interview Questions and Answers Freshers, Excel Interview Questions and Answers, Excel Interview Questions will help you let out find all the solutions that are frequently asked in you upcoming Excel interview.

Over thousands of vacancies available for the Excel developers, experts must be acquaintance with all the component of Excel technologies. This is necessary for the students in order to have in-depth knowledge of the subject so that they can have best employment opportunities in the future. Knowing every little detail about Excel is the best approach to solve the problems linked with problem.

APTRON has spent hours and hours in researching about the Excel Interview Questions and Answers, Excel Interview Questions and Answers Freshers, Excel Interview Questions and Answers, Excel Interview Questions that you might encounter in your upcoming interview.  All these questions will alone help you to crack the interview and make you the best among all your competitors.

First of all, let us tell you about how the Excel technology is evolving in today’s world and how demanding it is in the upcoming years. In fact, according to one study, most of the companies and businesses have moved to the Excel. Now, you cannot predict how huge the future is going to be for the people experienced in the related technologies.

Hence, if you are looking for boosting up your profile and securing your future, Excel will help you in reaching the zenith of your career. Apart from this, you would also have a lot of opportunities as a fresher.

These questions alone are omnipotent. Read and re-read the questions and their solutions to get accustomed to what you will be asked in the interview. These Excel interview questions and answers will also help you on your way to mastering the skills and will take you to the giant world where worldwide and local businesses, huge or medium, are picking up the best and quality Excel professionals.

This ultimate list of best Excel interview questions will ride you through the quick knowledge of the subject and topics like Sorting, Data Forms, Filtering Data, Totals and Subtotals Total, What if’ analysis. This Excel interview questions and answers can be your next gateway to your next job as a Excel expert.

These are very Basic Excel Interview Questions and Answers for freshers and experienced both.

Q1: What is Microsoft Excel?
A1: Microsoft Excel is an electronic spreadsheet application that enables users to store, organize, calculate and manipulate the data with formulas using a spreadsheet system broken up by rows and columns. It also provides the flexibility to use an external database to do analysis, make reports, etc. thus saving lots of time.

Q2: What is ribbon?
A2: Ribbon refers to the topmost area of the application that contains menu items and toolbars available in MS-Excel. Ribbon can be shown/hidden using CTRL+F1. The ribbon runs on the top of the application and is the replacement for the toolbars and menus. The ribbons have various tabs on the top, and each tab has its own group of commands.

Q3: How can you wrap the text within a cell?
A3: You must select the text you want to wrap, and then click wrap text from the home tab and you can wrap the text within a cell.

Q4: Which are the two macro languages in MS-Excel?
A4: XLM and VBA (Visual Basic Applications). Earlier versions of Excel used XLM. VBA was introduced in Excel 5 and mostly used now.

Q5: How cell reference is useful in the calculation?
A5: In order to avoid writing the data again and again for calculating purpose, cell reference is used. When you write any formula, for specific function, you need to direct Excel the specific location of that data. This location is referred as, cell reference. So, every time a new value added to the cell, the cell will calculate according to the reference cell formula.

Q6: Which function is used to determine the day of the week for a date?
A6: WEEKDAY () returns the day of the week for a particular date counting from Sunday.
Example: Let date at A1 be 12/30/2016
WEEKDAY(A1,1) =>6

Q7: What are left, right, fill and distributed alignments?
A7: Left /Right alignment align the text to left and right most of the cell.
Fill as the name suggests, fill the cell with same text repetitively.
Distributed, spread the text across the width of the cell.

Q8: Explain the difference between SUBSTITUTE and REPLACE function in MS-Excel?
A8: The SUBSTITUTE function substitutes one or more instances of old text with the new text in a string.

Syntax: SUBSTITUTE(text, oldText, newText, [instanceNumber])
Example: Let text at A2 be Guru99,Guru99

SUBSTITUTE(A2,”9?,”8?,1) =>Guru89,Guru99
SUBSTITUTE(A2,”9?,”8?,2) =>Guru88,Guru99
SUBSTITUTE(A2,”9?,”9?) =>Guru88,Guru88

The REPLACE function swaps part of the text string with another set of text.

Syntax: REPLACE(oldText, startNumber, NumberCharacters, newText)
Example: Let text at A2 be Guru99

REPLACE(A2,5,1,”00?) =>Guru009

Q9: What are charts in MS-Excel?
A9: To enable graphical representation of the data in Excel, charts are provided. A user can use any chart type, including column, bar, line, pie, scatter, etc. by selecting an option from Insert tab’s Chart group.

Q10: What does a red triangle at the top right of a cell indicate?
A10: The red triangle indicates that some comment is associated with the cell. Hover the mouse over it, and you can read the full comment.

Q11: What is the use of NameBox in MS-Excel?
A11: Name Box is used to return to a particular area of the worksheet by typing the range name or cell address in the name box.

Q12: What are three report formats that are available in Excel?
A12: Following are the types of report formats

• Compact
• Report
• Tabular

Q13: Is it possible to make Pivot table using multiple sources of data?
A13: If the multiple sources are different worksheets, from the same workbook, then it is possible to make Pivot table using multiple sources of data.

Q14: What could you do to stop the pivot table from loosing the column width upon refreshing?
A14: Format loss in a pivot table can be stopped simply by changing the pivot table options. Under the “Pivot Table Options” turn on the “Enable Preserve Formatting” and disable “Auto Format” option.

Q15: Can we create shortcuts to Excel functions?
A15: Yes. ‘Quick Access Toolbar’ above the home button can be customized to display most frequently used shortcuts.

Q16: What is the use of LOOKUP function in Excel?
A16: In Microsoft Excel, the LOOKUP function returns a value from a range or an array.

Q17: Does each cell have unique address?
A17: Yes, each cell has a unique address depends on the row and column value of the cell.

Q18: What is the use of comment? How to add comments to a cell?
A18: Comments are used for a lot of reasons:

• Comments are used to clarify the purpose of the cells.
• Comments are used to clarify a formula used in the cell.
• Comments are used to leave notes for others users about a cell.

To add a comment: Right click the cell and choose insert comment from the cell menu. Type your comment.

Q19: What is the difference among COUNT, COUNTA, COUNTIF and COUNTBLANK in Ms-Excel?
A19: COUNT is used to count cells containing numbers, dates, etc. any value stored as number excluding blanks.

COUNTA or Count All is used to count any cell value containing numbers, text, logical values, etc. any type of value excluding blanks.

COUNTBLANK count blank cells or cells with an empty string.

COUNTIF and COUNTIFS count cells matching a certain criteria.

Q20: What does the IF function in Excel?
A20: IF function is used in Excel to check whether certain conditions are true or false. If the condition is true then it will give the result accordingly and if the condition is false the result or output will be different.

Q21: How many rows and columns are there in Microsoft Excel 2003 and later versions?
A21: You can see the number of columns, rows, cells for Microsoft Excel version 2003 and later versions in the following table:

Q22: What is the syntax of Vlookup?
A22: Vlookup Syntax:
1. VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Q23: What is the order of operations used when evaluating formulas in Excel?
A23: Just like in standard mathematics, Excel uses an order of operations when evaluating different operators within the formula bar. You’ll almost surely recognize the acronym PEMDAS — it’s the order of operations taught in mathematics classes worldwide, and it’s also the order that Excel uses. PEMDAS stands for:

• Parentheses
• Exponents
• Multiplication
• Division
• Addition
• Subtraction

When evaluating formulas, Excel always processes operators in this order. If you find yourself receiving an unexpected result from your mathematical formulas, double-check to make sure that parentheses are used properly to achieve the results you want.

Q24: In your opinion, what are a few of the most useful functions in Excel? How do you use them?
A24: This is a tricky question, because it asks you to use your subjective judgement rather than answering objectively. As such, you’ll have a wide range of latitude in your response — and you should have a well thought-out reply prepared that demonstrates both your proficiency with Excel and your wide range of past experience using spreadsheets.

Here are a couple of our top recommendations for features, formulas, and functions to discuss:

• INDEX MATCH. VLOOKUP and INDEX MATCH are two of Excel’s most important and commonly-used functions. As veteran Excel users know, they’re used to look up values from an external table, and are important parts of automating your work with dynamic spreadsheets. One of the two is bound to come up in any Excel interview, but if you get this question, we recommend bringing up INDEX MATCH. It’s a slightly more useful function, and also lacks many of the disadvantages of VLOOKUP, like the inability to insert new rows and columns into your sheets. If you go with this function, outline how you’ve used it in the past to dynamically lookup values and populate columns of data that would otherwise need to be manually copied and pasted.
• IF statements. IF statements are another staple of any Excel veteran’s arsenal. Bring them up to let your interviewer know that you’ve created advanced spreadsheets that make decisions based on criteria calculated in real-time.
• PivotTables. PivotTables are an extraordinarily useful tool, and if you’re applying for a job that requires intermediate or advanced Excel knowledge, they’re sure to be an important criteria used by your interviewer. You may have used PivotTables for any number of things in the past, but be sure to emphasize how useful they are when quick, accurate calculations are necessary based on large sets of data with hundreds or thousands of rows.

Q25: What is a PivotTable, and when would you use one? What are the key PivotTable ‘sections’ into which users can drag columns?
A25: As one of the most-used Excel features in business settings, PivotTables are sure to come up during any in-depth Excel interview. Be sure you’re prepared in advance with a firm grasp of what exactly PivotTables are, and why they’re useful in practice.

Simply put, a PivotTable is a tool used to summarize large quantities of data quickly and easily. It can help you analyze a data set of tens, hundreds, or even thousands of rows with minimal effort using a number of pre-defined functions — like SUM, COUNT, and AVERAGE.

There are many use cases for PivotTables, but they’re most handy when you need to analyze a large data set quickly. If you’ve got high-level, one-off questions on a massive data set — for example, “how many cookies did we sell in February of last year”, or “which salesperson closed the most deals this March”, chances are a PivotTable is the perfect way to answer them.

Each PivotTable is composed of a number of key sections, into which the columns of a target data set can be bucketed:

• Report filter. This section allows us to filter our table by one or more criteria. For example, we can only show data in our Pivot Table for the month of January.
• Column labels. This section allows us to summarize data across columns, placing data labels along the top of the screen.
• Row labels. This section allows us to summarize data across rows, placing data labels along the side of the screen.
• Values. This section allows us to specify what we’re summarizing — for example, total sales or number of items ordered.

Q26: What are some best practices when creating complex models in Excel?
A26: Excel can be used for simple calculations, but it’s most effective when constructing complex mathematical models that help predict outcomes, project financial results, or track data over time. If you’re interviewing for a highly analytical role, there’s a good chance your recruiter will ask about how you can use spreadsheets to accomplish these more difficult tasks.

When talking through your answer, be sure to mention the following modeling best practice, which help keep your spreadsheets clean, organized, and flexible:

• Create multiple tabs. Keeping different pieces of your model (for example, inputs, outputs, and calculations) on separate tabs can help with model organization, particularly if you’re planning to hand your spreadsheet off to someone who has never seen it before.
• Use dynamic inputs. When constructing a model in Excel, values should never be hard-coded into cells — especially if they are flexible assumptions that may change down the line. Always keep assumptions and inputs on their own tab, and use cell references rather than hard-coded values to pull them into your formulas.
• Add a table of contents. Large models can be extraordinarily complex, and adding a table of contents to the beginning can help keep things organized and easy to use for yourself and your supervisor.
• Comment aggressively. You are the person who understands your models the best, but other people in your organization will doubtless be using them, too. So, be sure to over-comment and explain your calculations line-by-line so that they are as easy to follow as possible for other users.

Q27: What is the easiest solution to reduce the file size?
A27: Below are the steps to reduce the file size:

• Find the last cell that contains data in the sheet. Delete all rows and columns after this cell
• To delete the rows, press the key Shift+Space then press Ctrl+Shift+Down on your keyboard
• Rows will get selected till the last row. Press Ctrl+- on the keyboard to delete the blank rows
• To delete the column, Press the key Ctrl+Space then press Ctrl+Shift+Right Arrow key on your keyboard
• Columns will get selected till the last row
• Press Ctrl+- on the keyboard to delete the blank columns

Excel Conclusion Interview FAQs

We know the list of Excel Interview Questions and Answers, Excel Interview Questions and Answers Freshers, Excel Interview Questions and Answers, Excel Interview Questions is overwhelming but the advantages of reading all the questions will maximize your potential and help you crack the interview. The surprising fact is that this Excel interview questions and answers post covers all the basic of the Excel technology and you have to check out the FAQs of different components of Excel too.

However, you will be asked with the questions in the interview related to the above mentioned questions. Preparing and understanding all the concept of Excel technology will help you strengthen the other little information around the topic.

After preparing these interview questions, we recommend you to go for a mock interview before facing the real one. You can take the help of your friend or a Excel expert to find the loop holes in your skills and knowledge. Moreover, this will also allow you in practicing and improving the communication skill which plays a vital role in getting placed and grabbing high salaries.

Remember, in the interview, the company or the business or you can say the examiner often checks your basic knowledge of the subject. If your basics is covered and strengthened, you can have the job of your dream. The industry experts understand that if the foundation of the student is already made up, it is easy for the company to educate the employ towards advance skills. If there are no basics, there is no meaning of having learnt the subject.

Therefore, it’s never too late to edge all the basics of any technology. If you think that you’ve not acquired the enough skills, you can join our upcoming batch of Excel Training in Noida. We are one of the best institute for Excel in noida which provide advance learning in the field of Excel Course. We’ve highly qualified professionals working with us and promise top quality education to the students.

We hope that you enjoyed reading Excel Interview Questions and Answers, Excel Interview Questions and Answers Freshers, Excel Interview Questions and Answers, Excel Interview Questions and all the FAQs associated with the interview. Do not forget to revise all the Excel interview questions and answers before going for the Excel interview. In addition to this, if you’ve any doubt or query associated with Excel, you can contact us anytime. We will be happy to help you out at our earliest convenience. At last, we wish you all the best for your upcoming interview on Excel Technology.