Microsoft Exam 77-728: Excel 2016 Expert Interview Questions
Microsoft Exam 77-728: Excel 2016 Expert: Interpreting Data for Insights certification demonstrates that you possess advanced skills in Excel 2016, in front of your employer. For you to successfully pass the interview, you must have expertise in the core exam objectives, especially a depth of spreadsheet knowledge. Saying so, you ought to be efficient in using spreadsheets. Further, as an expert, you should be capable of building spreadsheet models, dashboards, and automated templates.
This article summarizes an exhaustive list of top Exam 77-728: Excel 2016 Expert Interview Questions, to help you gain confidence to face the panel. So let’s begin!
1. How are spreadsheets and workbooks different?
A worksheet is a single-page spreadsheet where you can edit and manipulate data in your Excel file. A collection of individual worksheets (pages in a book) that comprise one workbook is referred to as a workbook. It is very similar to a book (workbook) and each individual worksheet (page in the book) within the workbook.
2. In what ways are workbook templates beneficial?
A template can streamline the process of creating a workbook and make it easier to use when customizing workbooks. If you save a workbook as a template, you can create a new workbook based on your original design by opening the template.
3. What is the performance impact of conditional formatting in Excel?
Although many Excel users find conditional formatting helpful, it can slow down your spreadsheet. This is most apparent when you work with large data sets, or repeat Conditional Formatting assignments multiple times.
4. In Excel, why should I sort and filter data?
Excel offers several convenient methods for locating information within your worksheet. By sorting you can sort a worksheet by using information from one or more columns. And filtering excludes unwanted data from your view.
5. Is it possible to conditionally format a pivot chart?
No, you can’t use conditional formatting to a chart. For data points that go beyond a certain value, you’ll need to use a different chart type for the data points. To do this, you’d create another series for the new chart and plot the new series on the chart. However, if you want to apply conditional formatting to your pivot charts, you’ll need to create a separate pivot table in its own worksheet or workbook.
6. Do you know how to preserve the formatting of a pivot table?
- Firstly, you need to right-click on a cell in the pivot table, and then the PivotTable Options.
- Now, you need to remove the checkmark from Autofit Column Widths On Update, on the Layout & Format tab, present in the Format options
- Then, you can add a checkmark to the Preserve Cell Formatting on Update option
- Finally, click OK to preserve the formatting
7. Can you explain the concept of a custom function in Excel?
Excel’s custom function feature enables developers to add new Excel functions by defining them as part of an add-in written using JavaScript. Users can access custom functions in the same way that they access Excel’s built-in functions, e.g., SUM().
8. What is the purpose of using a LOOKUPs function?
The LOOKUP function is used for searching the first row or first column of an array for a value and then returns a value from the same position in the last row or column of the array. When you use this function, you need to put values that you want to match at the beginning of the array.
9. Are LOOKUPs and VLOOKUPs the same?
The key difference between the VLOOKUP and LOOKUP functions is that the LOOKUP function is available to perform both vertical and horizontal lookups, unlike VLOOKUP which is limited only to vertical lookups.
10. Why do we conduct data analysis?
Data analysis boils down to analytical thinking, logic, and communication skills. It’s used to obtain concrete information through the examination of data, leading to informed decisions.
11. In what way is data analysis conducted?
There are five steps involved in analyzing data:
- Step One: Asking Right Questions
- Step Two: Collecting data
- Step Three: Data Cleaning
- Step Four: Analyzing the given Data
- Step Five: Interpreting the output Results
12. What are the most basic methods of analyzing data?
There are two primary data analysis methods qualitative and quantitative. Qualitative analysis is used when working with opinions and feelings while quantitative analysis utilizes a scientific, mathematical approach to problem-solving. Business leaders and decision-makers should consider using a combination of qualitative and quantitative techniques to gain insights from business data.
13. What is the value of business intelligence?
Essentially, Business intelligence is a very wide concept that combines business analytics, data mining, data visualization, data tools, and infrastructure to help organizations to make data-driven decisions. BI has gone through various eras of popularity, each with its own definition.
14. In what ways does business intelligence benefit organizations?
- Faster insights, easier-to-use dashboards
- Increased efficiency can save you time, money, and effort.
- Using data to make business decisions
- Helps us improve the customer experience
- Brings out the best in employees
- Makes a company more trustworthy and efficient.
15. What are the components of the business intelligence life cycle?
Business intelligence lifecycle management is a process that incorporates business users into the design process and focuses on generating data models, database objects, data integration mappings, and front-end semantic layers directly from business user input.
16. Which types of business intelligence tools exist?
Tools and applications used in business intelligence are:
- Firstly, Ad hoc analysis
- Online analytical processing (OLAP)
- Mobile BI
- Also, Real-time BI
- Operational intelligence (OI)
- Then, Software-as-a-service BI
- Open-source BI (OSBI)
- Finally, Embedded BI
17. Can you recommend some good business intelligence platforms?
Some of the best business intelligence platforms shall be:
- First and most importantly, Microsoft. Power BI.
- Tableau Desktop
- Also, Dundas. BI
- Sisense
- Finally, Zoho. Analytics
18. Which formula errors are most common?
There are chiefly six common formula errors in Excel. These are:
- ###### Error (#######)
- Name Error ( #NAME? )
- Value Error ( #VALUE! )
- Division Error ( #DIV? 0! )
- Null Error ( #NULL! )
- Reference Error ( #REF! )
19. What is the utility of the date and time functions in Excel?
One of Excel’s most useful features is the ability to work with dates and times. The program’s robust set of date and time functions makes it easy to add days or months to particular dates, create dynamic fields based on those dates, and perform calculations using times, such as figuring out what time an event occurred.
20. How would you automatically enter the date and time in Excel after data entry?
To enter today’s date, press CTRL + SHIFT + ;. You’ll get the current date and time in the cell. To enter both values, press CTRL + ; first, then SPACE, and then CTRL + SHIFT + ;
21. How would you describe advanced charts?
A more advanced chart can help you compare more than one set of data at once. For example, you may have two sets of data that you would like to compare on the same chart. You can create your basic chart with one set of data then add more datasets to it and apply other items (such as formatting) to the chart.
22. What is the utility of Tableau in data analysis?
Tableau Software is an analytics software that makes it easy to analyze, share, and collaborate on data. People love the natural-user experience of Tableau. It’s fun to use. And everyone loves the visual results. People can connect to their data in minutes, ask new questions, and share insights across the organization.
23. How is the un-sorting of a pivot table possible?
Right-click one of the cells in your pivot table, point to PivotTable Options and then select Totals & Filters. In the Sorting section of the dialog box, remove the checkmark from “Use custom lists when sorting.”
24. Can you explain the term slicer to me?
Slicers provide buttons you can use to quickly filter data in tables or PivotTables. By displaying the current filtering state, slicers make it easy to understand what data is being displayed.
25. Can you elaborate on how one can utilize a pivot chart?
By making use of Pivot Charts, you can easily analyze large amounts of unsummarized data in different ways. Instead of filling up several (or more) chart sheets, data can be entered individually into a single pivot chart without needing to enter the same information many times.
26. Can you differentiate between Pivot Table and Pivot Chart?
With Pivot Tables, you can sum up your data and view it in a grid, both in horizontal and vertical columns. Whereas the pivot charts are interactive representations of the data in your Zoho Creator application.
27. What is the main purpose of using named ranges?
A range name is a cell reference or a formula result that can be used in place of a value. They can be used instead of a hard-coded value in a formula and can make formulas easier to understand.
28. What is the meaning of the term concatenate in Excel?
Concatenate is a synonym for “to combine”. This function lets you combine text from different cells into one cell. As a simple example, one can use concatenate function to combine the text in column A and column B to create a combined name in a new column.
29. Can you explain the difference between the terms concat and concatenate?
The CONCAT function joins cells or strings together, but it does not locate or ignore delimiters, and the CONCATENATE function replaces the CONCAT function. However, the CONCATENATE function will remain available to maintain compatibility with earlier Excel versions.
30. What are the five essential functions available in Excel?
Every professional should be familiar with these five functions of Excel/Sheets:
- First and most importantly, the VLookup Formula
- Concatenate Formula
- Also, Text to Columns
- Remove Duplicates
- Last but not the least, Pivot Tables
