Excel 2013 Expert Part Two (77-428) Interview Questions

Earning the Microsoft Office Specialist 2013 certification shows employers that you’ve gained expertise in the many facets of the software. So, by completing the Microsoft Excel 2013 Expert Part Two (77-428) exam, you will distinguish yourself from most job applicants. But just passing the exam is not enough. You need to successfully pass the interview phase. And for this, you should have the necessary expertise, along with being able to generate a variety of specialized spreadsheets and modify their Excel environment to suit individual needs. Further, it is highly recommended to master your skills, essentially, for cracking the interview, in the below-mentioned areas: 

• Firstly, Managing multiple workbooks for use by multiple users, along with ensuring the safety of data
• Applying custom formats and layout to enhance the visual appearance of data in a worksheet
• Using advanced formulas, creating pivot tables, and multiple charts to summarize and analyze data in different forms

The good news is that you can increase your chances of getting the job by reading the list of top Excel 2013 Expert Part Two (77-428) interview questions,  below. Here we go!

1. Can you name the different data formats available in Excel?

There are typically five kinds of data formats available in Excel:

• Text Format – A text string is a special type of value that you can use to display data. When you have a substantial amount of text, use text format. This can include any combination of letters, numbers, and symbols.
• Number Format – Excel supports a variety of formats for positive and negative numbers, including decimal, integer, and thousands of separators. It also supports calculations involving numbers with different formatting.
• Date Format – In Excel, dates are saved in numeric format, but they can be formatted to display as dates. For example, 01-01-2019 is stored as 43466 but can be shown as either 01 January 2019 or 01-01-2019.
• Accounting/Currency Format – A number formatted as an accounting/currency number displays its value in a way that includes currency symbols (such as the dollar sign) and decimal places.
• Percentage Format – You can create a numerical format in Microsoft Excel that displays data as percentages. For example, you can create a format that displays 0.1 as 10% when applied to a cell containing the number 0.1.

2. What’s the easiest way to wrap text in an Excel cell?

Applying the wrap text option to a cell in Excel will keep any text from overflowing into multiple columns. Using this feature, you can make sure all the text sits within the confines of one cell (possibly causing the cell to expand in height). You can wrap text in a cell by selecting the cell, clicking on the Wrap Text button in the Alignment group, and choosing the option that you want.

3. How do you use Excel features to merge cells?

The most efficient way to merge cells in a selected range is by first selecting the cells that you want to merge, and then, clicking the Merge and Center button in the Alignment group on the Home tab. However, To merge cells efficiently in Excel, we do not generally use the ‘Merge and Center’ option. Instead, using the ‘Center Across Selection’ option is a better way to properly sort the desired cells.

4. Can you explain the use of the Format Painter tool?

Microsoft Excel’s ‘Format Painter’ tool allows you to apply the format of one cell (or a range of cells) onto another cell or range of cells, in your spreadsheet.

5. How would you get rid of all the formatting, without losing the contents of the cells?

Excel provides you with the ability to clear all formatting (including colors, borders, font styling, etc.) and just have simple data. This can be done by using the Clear Format command. You can clear formatting in Excel by selecting ‘Clear Formats’ from the Home tab in the Editing group, which appears when you click the ‘Clear’ dropdown. Moreover, you can even choose the ‘Clear All’ option.

6. How would you define conditional formatting?

Excel’s Conditional Formatting feature allows you to change the appearance of a cell, based on the value in it. For example, you can highlight all cells where the value is less than 30 with a red fill.

7. What is the easiest way to make text invisible in Excel?

Ideally, there are two ways:

• Firstly, by simply making the color of the font white, making it appear as if it’s invisible
• Secondly, the most recommended way is by altering the custom format. That is, by first selecting the cell, and pressing Control + 1. Then, the “Format Cells” dialog box will will displayed. Finally, type ;;; in the custom options field

8. Can you distinguish between a function and a formula?

Formulas are expressions you create in Microsoft Excel that calculate values. While functions are operations pre-programmed into Excel, such as SUM and AVERAGE, that do calculations for you. You can also create complex formulas using multiple functions to perform multiple calculations. For example, =A1+A2 is a formula whereas =SUM(A1:A10) is a function.

9. What are the top five Excel functions you feel most comfortable using in your day-to-day work? 

The top five should be based on your experience and comfort with the functions. For example, you can name the following: 

• VLOOKUP
• COUNTIF
• SUMIF
• IFERROR
• INDEX / MATCH

Further, explain the utility of these functions, and how comfortable you are using them, as an explanation for choosing the above functions.

10. How would you differentiate between absolute and relative cell references?

Relative References: Cell references that change when you copy and paste, called relative references. As an example, if you copy the formula in cell A1 and then paste it into cell A2, for example, the cell reference will change to A11. 

Absolute References: Cell references that do not change when you copy and paste, called absolute references. As an example, if you’re putting =$A$10 in cell A1 and then copying the cell A1 to cell A2, the reference would still remain $A$10.

11. What types of errors are you likely to encounter in Excel?

When working with Excel, the following six types of errors may arise:

• #N/A Error: It’s called the ‘Value Not Available’ error. We usually see this error while using a lookup formula, when it’s unable to find the value 
• #DIV/0! Error: It’s called the ‘Division’ error. This error occurs when we try to divide any number by 0
• #VALUE! Error: We are likely to encounter this error when we’re using an incorrect data type in any formula
• #REF! Error: It’s called the ‘Reference’’ error. It occurs when the reference in the formula is invalid
• #NAME ERROR: Any misspelled function can lead to the ‘Name’ error
• #NUM ERROR: It’s called the ‘Number’ error, and happens when we try to calculate a considerably larger value

12. What are some possible problems with using the VLOOKUP function in Excel? 

Some of the inherent limitations of the VLOOKUP function are:

• Firstly, we can not use it when the lookup value is on the right. For this function to work, the lookup value must necessarily be in the left-most column. 
• Secondly, if we try adding or deleting a new column in the data, it can give a wrong result. This primarily happens because the column number value is now referring to the wrong column
• Last but not the least, when we’re using large data sets, this function is likely to make our workbook slow

13. When is someone most likely to use the SUBTOTAL function?

When working with tabular data, the SUBTOTAL function comes in handy. It helps get a huge variety of subtotals, including AVERAGE, COUNT, MAX, MIN, STDEV, etc. Moreover, one greater advantage of the SUBTOTAL function is that you can ignore the hidden/filtered cells. Basically, when dealing with a heavy data set, you can filter it based on certain criteria or even hide some undesired rows. This SUBTOTAL function will by default get updated and you’ll see only the result from the cells you made visible. Besides, in case you don’t want to filter or hide the data in any cell you ignore, you have the option to do that too.

14. Can you define volatile functions, along with an example?

The recalculation function of an Excel formula is volatile (i.e., each time a cell in the workbook is changed, the formula is re-calculated). This can significantly slow down the workbook. The NOW() function to get the current date and time in a cell is a volatile function. Editing any cell in a worksheet recalculates all sheets, which slows down processing with large spreadsheets.

15. How would explain Pivot tables and when is it required to use one?

Pivot Tables are a grid-type summary report tool in Microsoft Excel that can be used to summarize huge data sets with just a few clicks. Using it is very simple, just dragging and dropping row and column headings, and you’re done.

Pivot tables are a tool that you can use in Microsoft Excel when dealing with large amounts of data. This feature lets you create a single table out of several data tables that show different subsets of information. 

16. Can you name the different sections in a Pivot Table?

Excel’s Pivot table has four different sections, namely:

• Values Area: It reports the value
• Rows Area: It is the heading to the left of the Values area

• Columns Area: It is the heading at the top of the Values area 
• Filter Area: This is an optional filter that is used to drill down in the data set

17. What is the significant distinction between Pivot Charts and Regular Charts?

Although Pivot Charts look amazing and are automatically updated when you update your Pivot Table data, they do not have the same level of customizability as regular charts. Because Excel’s regular charts are more flexible than its Pivot charts, it is better to use regular charts. Moreover, first customizing and then updating a Pivot Chart will likely result in the loss of customization. Nevertheless, despite these limitations, Pivot Charts remain a better option over regular charts for creating quick views.

18. How will you define a Pivot Cache?

Excel’s Pivot Cache is a hidden object that is automatically generated when you create a Pivot Table. It is a data storage area that holds a replica of the data source to speed up processing. The Pivot Cache allows it to work only with a replica of the data source, rather than with the original data source. Changes made to the Pivot Table are reflected in the Pivot Cache, not the original data source.

Microsoft Excel uses a pivot cache to make its pivot tables perform quickly when you have large amounts of data. You can drag items in the boxes on a pivot table, and the pivot table will update instantly.

19. Can you differentiate between Column chart, Bar chart, Line chart, and Scatter chart?

• Column chart: Excel’s column chart is used to plot numbers in vertical bars that can be used for comparing values over time or for comparing values in different categories. Clustered column charts are ideal to visually compare multiple categories or multiple years within the same category.
• Bar chart: It is an arrangement of horizontal bars used to compare values in different categories (e.g., product category sales) or to compare the same value across different categories (e.g., response to a survey).
• Line chart: It allows you to show more than one trend in your data over time. Line charts usually have multiple lines, and these lines often represent different categories.
• Scatter chart: Data from different products can be compared on a scatter chart to determine the best products. The best-performing products appear in the top right quadrant and the worst performing products are at the bottom left of the chart.

20. How useful are Pie charts reports/dashboards?

There are two approaches to this issue. Some people believe that pie charts should never be used, and others occasionally use them in reports and dashboards. Managers have different preferences when it comes to using pie charts. Some find them easy to read, while others don’t. 

If you’re working on some sort of corporate presentation and you need to show a breakdown, pie charts will prove useful. However, you must avoid using Pie charts when the difference in values is not significant (can be better visualized by a bar chart), or when there are too many parts (using a pie chart will make it look cluttered)

21. When would you recommend someone to use Water charts?

A waterfall chart is a visual representation of different values, both positive and negative, that are added together to get a final value. Waterfall charts, like other types of charts, show relationships between multiple values. Waterfall charts allow you to see how different numbers relate to the final outcome. You can use water charts in case you need to analyze your company’s net income. You can make all the cost components displayed in the waterfall chart. This will help you visually check how the net income of your company is derived from its revenues and also every cost that has been deducted.

22. What is the use of Combination charts in Excel?

Combination charts are created when two different data sets are put into a single chart. A popular example of this is showing a bar graph with a line graph together on one page. Combination charts help you present and compare two different data sets that are interrelated.

23. Why is a Bullet Chart highly recommended by experts in Excel? 

Bullet charts are amazing, widely recognized charting representations, which help you show performance against a target. Further, despite being power-packed with different information, it takes very little space in reports or dashboards. However, they are not the default chart types in Excel. You’re required to apply multiple steps in order to create bullet charts.

24. What are the steps involved in sorting data in Excel?

To sort data in excel, we employ the following steps:

• Firstly, select the data and then click on one of the two sort icons available in the Data tab.
• Then, select the data and click on the Sort icon. The sort dialog box will appear, where you need to specify the column to be sorted along with the criteria (either ascending or descending)
• Finally, apply the data filter, and then click on the filter. It will now display the data sorting options, along with the filter options

25. What do you understand by Data validation?

Data Validation is a tool that lets you specify a rule for data in a cell. For example, if you want the data in a cell to contain only numbers, then you can use Data Validation to make this happen. If you enter text or some other data in a cell formatted as a number, Excel will not accept it and will display an error message.

26. What is the use of the Advanced Filter?

As the name implies, Excel’s Advanced Filter is an upgraded version of the regular filter. It allows you to create more complex filtering criteria. Here is a list of some differences between the two filters:

• Firstly, while the regular data filter allows you to filter only the existing data set, the Advanced filter lets you extract the data set to other locations also
• Also, the Advanced Filter lets you use complex criteria in Excel, unlike regular filter

27. What is the tool, Scenario Manager used for?

Excel’s Scenario Manager can help you simulate different possible outcomes for a project if key variables change. If you have only one or two variables that can change, you can create a data table with these variables and their possible values. If you have 3 or more variables that can change, then scenario manager is the tool you need. For instance, Scenario Manager lets you keep track of the data that flow into your business model as well as the sensitivity analysis that helps you understand how changes in assumptions or inputs affect your financial results. 

28. What do you mean by Goal seek?

Goal Seek is a key feature for those who want to calculate optimal values for one or more variables when you know the values of others. It allows you to alter one value to reach a specific end result. For example, as you search for a car to buy, and want to know what monthly payment you can afford. Goal seek can help you determine how many monthly installments to pay on a car so that the total cost of the car (and associated monthly payments) is within the desired range.

29. How will you define Solver?

Excel’s Solver add-in is an advanced version of Goal Seek. It can calculate possible solutions to problems with many variables and constraints.

30. Can you mention some of the benefits of using VBA in Excel?

Although Excel comes with many features, it cannot do everything you need. VBA allows you to enhance Excel’s capabilities by creating codes that execute automated processes. Some of the things you can do with VBA are:

• Firstly, automating a set of tasks and thereby saving time
• Creating your own Excel functions as per need
• Also, creating and sharing codes with others, enabling them also to automate their tasks and save time
• Last but not the least, creating custom applications

Expert’s Corner

When you master all the features of Microsoft Office, your knowledge, efficiency, and productivity increase, elevating you to a higher level of professionalism and opening the door to new opportunities. We believe, the above-listed interview questions will prove fruitful when you’ll be facing the panel. Besides equipping you with the knowledge to make you ready for Excel 2013 Expert Part Two (77-428) exam, we provide you with practice tests that will help you develop the skills required to pass the actual exam.

Go take our multiple Excel 2013 Expert Part Two (77-428) Practice tests for Free, and crack the exam in your very first attempt!