Advance excel interview questions and answers

1. What is Microsoft Excel?

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.

2. What is ribbon?

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.

3. What is the easiest solution 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

4. How many report formats are available in Excel and what are their names?

In Excel, we have three formats available:

Compact
Report
Tabular

5. What is a Dashboard and what are the important things we should keep in mind while creating a dashboard?

Dashboard is a technique used to present important information through graphical representation. It is helpful in presenting huge data in a single computer screen so it can be monitored with a glance.

There are few things which should be taken care of, while preparing the dashboards:

1) Minimum distraction

2) Simple, easy to communicate

3) Important data

4) Few Colors

5) Relevant graphs

6) Dashboard should be on single computer screen

6. Is it possible to prevent someone from copying the cell from your worksheet?

Yes, it is possible. To protect your worksheet from getting copied, you need to go into Menu bar >Review > Protect Sheet > Password. By entering a password, you can secure your sheet from getting copied by others.

7. What are charts in MS-Excel?

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.

8.How can you sum up the Rows and Column number quickly in the Excel sheet?

By using SUM function, you can get the total sum of the rows and columns, in an Excel worksheet.

9. Explain few useful functions in Excel.

Following are the functions available in Excel for manipulating the data:

Math and Financial Functions – SQRT, DEGREE, RAND(), GCD
Logical Functions – IF, AND, FALSE, TRUE
Date and Time functions – NOW(), DATEVALUE(), WEEKDAY(NOW())
Index Match – VLOOKUP and INDEX MATCH
Pivot tables

10. Can we create shortcuts to Excel functions?

Yes. ‘Quick Access Toolbar’ above the home button can be customized to display most frequently used shortcuts.

11. What is the use of LOOKUP function in Excel?

In Microsoft Excel, the LOOKUP function returns a value from a range or an array.

12. How can you apply the same formatting to every sheet in a workbook in MS-Excel?

Right Click ‘Worksheet tab’ > Choose ‘Select All Sheets’. Now any formatting done will be applied to the whole workbook. To apply to a particular group of sheets, select only those sheets that need formatting.

13. Which function is used to determine the day of the week for a date?

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

14. What is the benefit of using formula in Excel sheet?

Calculating the numbers in Excel sheet, not only help you to give the final ‘sum up’ of the number but, it also calculates automatically the number replaced by another number or digit. Through Excel sheet, the complex calculations become easy like payroll deduction or averaging the student’s result.

15. What is the “What If” condition in Excel formulas?

The “What If” condition is used to change the data in Microsoft Excel formulas to give different answers.

e.g.You are buying a new car and want to calculate the exact amount of tax that will be levied on it then you can use the “What If” function. For instance, there are three cells A4,B4, and C4. First cell says about the amount, the second cell will tell about the percentage (7.5%) of tax and the final cell will calculate the exact amount of tax.

16. How can you disable the automating sorting in pivot tables?

To disable the automating sorting in pivot tables,

Go to > “More Sort Options”> Right Click “Pivot table” > Select “Sort” menu > Select “More Options” > Deselect the “Sort automatically when the report is created.”

17. What is the AND function does in Excel?

Like IF function, AND function also does the logical function. To check whether the output will be true or false the AND function will evaluate at least one mathematical expression located in another cell in the spreadsheet. If you want to see the output of more than one cells in a single cell, it is possible by using AND function.

18. Explain Spreadsheet and its Basics.

A spreadsheet can be compared to a paper ledger sheet. It consists or rows and columns and their intersection called cells.

19. How many data formats are available in Excel? Name some of them.

Eleven data formats are available in Microsoft Excel for data Storage. Example:

Number – Stores data as a number
Currency – Stores data in the form of currency
Date – Data is stored as dates
Percentage – Stores numbers as a percentage

20. How Would You Provide A Dynamic Range In “data Source” Of Pivot Tables In Ms Excel?

To provide a dynamic range in “Data Source” of Pivot tables, first create a named range using offset function and base the pivot table using a named range created in the first step.

21.How Cell Reference Is Useful In The Calculation?

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.

22. How You Can Resize The Column?

To resize the column you have to change the width of one column and then drag the boundary on the right side of the column heading till the width you want. The other way of doing it is to select the Format from the home tab, and in Format you have to select AUTOFIT COLUMN WIDTH under cell section. On clicking on this the cell size will get formatted.

23. What is a function in Excel?

If you’re a frequent Excel user, functions are probably second nature to you. You’ve used SUM, AVERAGE, and even VLOOKUP so many times that you don’t even think about what a function actually is when you’re creating spreadsheets.

As such, this question may come as a surprise to you — especially because describing what a function is can be a bit difficult if you never think about it.

Here’s an easy way to do it: think of a function like a recipe. It’s used to combine a bunch of ingredients — which may not taste particularlly good individually — into something much more useful.

The name of the function is like the title of the recipe. It describes what the function does, like take a SUM or an AVERAGE.

The arguments of the function describe what ingredients go into it. Individual functions can take any number of arguments, from one to an infinite number. It all depends on the function.

Finally, the output of the function is what comes out the other side: a useful quantity that can show you important data, or be used as an input to other functions in your spreadsheet.

24. What are some best practices when creating complex models in Excel?

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.

25.Do PivotTables have any drawbacks? How can they be solved?

Of course, no Excel feature is without its drawbacks, and there’s a chance your interviewer will dive deeper into your PivotTable knowledge by asking you to explore some of their weaknesses. This will help the recruiter assess your in-depth experience on one of Excel’s most important features — after all, PivotTables can’t be used for everything!

If asked about the drawbacks of PivotTables, consider the following:

Input data needs to be formatted properly. PivotTables can only be used in specific situations in which the input data set appears in flat file format— meaning that it’s broken down to it’s most granular level. If data is already summarized on a table, PivotTables may not be the best way to analyze it.
PivotTables need to be refreshed if input data changes. This can lead to confusing and errors when using PivotTables as part of larger dashboards.
PivotTables are easily modified, so it can be difficult to recreate your calculations. There are many times during which you’ll arrive at an answer using PivotTables, then have a difficult time recreating that answer if a supervisor asks to see your work in more detail. The flexibility of PivotTables can be a double-edged sword!

26. Is It Possible To Call A Macro From The Condition True Or False Side Of A Worksheet Formula? I.e. If(a2=”ok”,run Macro1,run Macro2)?

Basically, the answer is No. You can write functions in VBA that you can call from worksheet cells, but these functions can only return a value. They can’t modify other cells or alter any part of the Excel environment. (You may be able to use a worksheet change event to call the macro.)

27.How Can I Unprotect A Vba Project Using Code?

You cannot. A workaround is to simulate keystrokes with the Send Keys method.28.Is It Possible To Change The Color And Font Of The Sheet Tabs?
Yes we can change the color of sheet tabs. By right clicking on sheet tabs and you will get option change color but i didn’t find any option to change the font of sheet tabs.

29. How To Prevent Someone From Copying The Cell From Your Worksheet?

If you want to protect your worksheet from being copied, go into Menu bar > Review > Protect sheet > Password.

By entering password you can prevent your worksheet from getting copied.

30. How You Can Sum Up The Rows And Column Number Quickly In The Excel Sheet?

By using SUM function you can get the total sum of the rows and columns, in an excel worksheet.

31. Is There A Way To Apply The Same Formatting To Every Sheet In A Workbook In Excel?

Yes. To do this, you will need to right click on one of the worksheet tabs and then choose Select All Sheets. After you do this any formatting that you apply or text you enter will show up on all the sheets in your workbook. In order to eliminate certain sheets from the changes, hold down the Ctrl key and click on the tab of the worksheet you want excluded from the others. You can also group sheets by holding the shift key and selecting the worksheet tab.

32. What are some best practices when creating complex models in Excel?

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.
33. What are the most important data formats seen in Excel, and how are they used?

Your Excel interview may start out with an easier question like this one. If you’ve spent any time in Excel, you’ve almost certainly had a chance to experiment with different data types, and will likely be familiar with some of the most frequently-used:

Numbers. Numbers are one of the most frequently-seen data types in Excel. They can be formatted with a customized number of decimal places, and appear with or without commas separating the thousands digits. Numbers can be added, subtracted, divided, multiplied, or included in formulas and functions that accept numerical inputs.
Dates. Excel can display dates in any number of ways, including the classic US-style MM/DD/YYYY format. Dates can be added or subtracted using standard addition and subtraction, and can also be manipulated using a slew of date-based functions. Interestingly, dates in Excel are technically also stores as numbers, with each date represented as the number of days elapsed since January 1, 1900. For example, the date May 6, 2019 is stored in Excel as the number 42,129, because there are 42,129 days between January 1, 1900, and May 6, 2019.
Percentages. Numbers can also be formatted as percentages, which multiplies the given number by 100 and adds a percentage sign at the end. For example, the number 0.08 is equivalent to the percentage 8%.
Strings. Text is stored in Excel in a format called a string. Strings of text can contain standard characters such as letters, numbers, and punctuation; strings can be manipulated via text-manipulation functions like MID and RIGHT.
34. Is it possible to prevent someone from copying the cell from your worksheet?

Yes, it is possible. To protect your worksheet from getting copied, you need to go into Menu bar >Review > Protect Sheet > Password. By entering a password, you can secure your sheet from getting copied by others.

35. What are charts in MS-Excel?

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.

36.How can you sum up the Rows and Column number quickly in the Excel sheet?

By using SUM function, you can get the total sum of the rows and columns, in an Excel worksheet.

37. Explain few useful functions in Excel.

Following are the functions available in Excel for manipulating the data:

Math and Financial Functions – SQRT, DEGREE, RAND(), GCD
Logical Functions – IF, AND, FALSE, TRUE
Date and Time functions – NOW(), DATEVALUE(), WEEKDAY(NOW())
Index Match – VLOOKUP and INDEX MATCH
Pivot tables
38. What does a red triangle at the top right of a cell indicate?

The red triangle indicates that some comment is associated with the cell. Hover the mouse over it, and you can read the full comment.

39. Do PivotTables have any drawbacks? How can they be solved?

Of course, no Excel feature is without its drawbacks, and there’s a chance your interviewer will dive deeper into your PivotTable knowledge by asking you to explore some of their weaknesses. This will help the recruiter assess your in-depth experience on one of Excel’s most important features — after all, PivotTables can’t be used for everything!

If asked about the drawbacks of PivotTables, consider the following:

Input data needs to be formatted properly. PivotTables can only be used in specific situations in which the input data set appears in flat file format— meaning that it’s broken down to it’s most granular level. If data is already summarized on a table, PivotTables may not be the best way to analyze it.
PivotTables need to be refreshed if input data changes. This can lead to confusing and errors when using PivotTables as part of larger dashboards.
PivotTables are easily modified, so it can be difficult to recreate your calculations. There are many times during which you’ll arrive at an answer using PivotTables, then have a difficult time recreating that answer if a supervisor asks to see your work in more detail. The flexibility of PivotTables can be a double-edged sword

40. How can you resize the column?

To resize the column, you should change the width of one column and then drag the boundary on the right side of the column heading till the width you want. The other way of doing it is to select the Format from the home tab, and in Format you have to select AUTOFIT COLUMN WIDTH under cell section. On clicking on this, the cell size will get formatted.

June 25, 2018

0 Responses on Advance excel interview questions and answers"

top
ETLHIVE is a Trademark of Itelligence Infotech Pvt Ltd.   *PMP®, PMBOK, PMI, PgMP, CAPM, PMI-RMP, and PMI-ACP are registered trademarks of the Project Management Institute, Inc. *ITIL®, IT Infrastructure Library, Swirl Logo, Prince2 are registered trademarks of AXELOS Limited.
21st July- Demo Session - P.Saudagar - Hadoop : 2.00 pm | Data science : 2 pm | Abinitio : 11 am | Informatica : 1.30 pm | Java : 9 am | Selenium : 9 am | Angular JS : 5 pm | Mean Stack : 5 pm| Nal Stop - Hadoop : 5.30 pm | Python : 11 am | Kharadi - Hadoop : 12 pm | Angular JS : 2.15 pm | Python : 11.30 am | Tableau 4.30 pm | Angular JS : 4.30 pm | Vashi(Mumbai) : Hadoop : 11 am | Data Science : 1 pm | Java : 4 pm | Blockchain : 4 pm