Exam 77-427: Microsoft Excel 2013 Expert Part One Interview Questions

Expert-level candidates for the Excel 2013 test should have a thorough understanding of the Excel environment and the ability to instruct others on how to use the capabilities in Office Professional Plus 2013’s Excel 2013. They should be familiar with and demonstrate how to use the application’s sophisticated and specialized capabilities.

Candidates should be able to design, maintain, and distribute professional spreadsheets for a number of specialized purposes and scenarios, as well as to adapt their Excel environment to match their demands and boost productivity. Custom company designs, financial charts with many axes, amortization tables, and inventory schedules are all instances of workbooks. Accountants, financial analysts, data analysts, commercial bankers, and other professionals could be considered for the position.

1.) In Excel, how many different data types are there? Please name a few of them.

Ans. Microsoft Excel supports eleven different data formats for data storage. Example:

Number — This type of data is saved as a number.
Currency – Stores information in the form of money.
Date – Dates are used to store data.
Percentage — This type of data is stored as a percentage.
Text Formats — Saves information as a string of characters.

2.) What is Microsoft Excel, and how does it work?

Ans. Microsoft Excel is a spreadsheet application that allows users to store, organize, calculate, and manipulate data using formulae on a spreadsheet system divided into rows and columns. It also gives you the option of using an external database to perform analysis, generate reports, and so on, saving you a lot of time.

3.) What exactly is a ribbon?

Ans. In MS-Excel, the ribbon refers to the topmost part of the application, which comprises menu items and toolbars. CTRL+F1 can be used to show or hide the ribbon. The ribbon replaces the toolbars and menus and runs across the top of the application. On the top of the ribbons are numerous tabs, each with its own set of commands.

4.) In Excel, specify the order of operations for evaluating formulas.

Ans. In Microsoft Excel, the order of operations is the same as in traditional mathematics. The name “PEMDAS” or “BEDMAS” is used to describe it.

Brackets or Parentheses

Exponent
Multiplication
Division
Addition
Subtraction

5.) Describe the basics of a spreadsheet.

Ans. A spreadsheet is similar to a ledger sheet on paper. It is made up of rows and columns with cells at their intersections.

6.) In MS-Excel, what are the two macro languages?

Ans. VBA and XLM (Visual Basic Applications). XLM was used in earlier Excel versions. VBA was first introduced in Excel 5 and is now widely utilized.

7.) Is there a way to stop someone from copying a cell from your worksheet?

Ans. Yes, it’s conceivable. You must go to Menu bar >Review > Protect Sheet > Password to prevent your worksheet from being duplicated. You can prevent others from copying your sheet by entering a password.

7.) What is the best way to enclose text within a cell?

Ans. You must first choose the text you wish to wrap, then go to the home tab and clickwrap text to wrap the text within a cell.

8.) Demonstrate how to use a macro in Microsoft Excel.

Ans. Iterating through a set of jobs is done with macros. Users can develop macros to automate their repetitive tasks and instructions. Depending on the user, macros can be created or recorded.

9.) In an Excel sheet, how can you quickly add up the number of rows and columns?

Ans. In an Excel spreadsheet, you may find the total sum of the rows and columns by using the SUM function.

10.) Describe a few useful Excel functions.

Ans. The following are the functions in Excel that can be used to manipulate data:

SQRT, DEGREE, RAND(), GCD Logical Functions IF, AND, FALSE, TRUE Math, and Financial Functions
NOW(), DATEVALUE(), WEEKDAY(NOW()) are all date and time functions.
Pivot tables with Index Match – VLOOKUP and INDEX MATCH

10.) Is there a way to stop someone from copying a cell from your worksheet?

11.) In MS-Excel, what are the two macro languages?

Ans. VBA and XLM (Visual Basic Applications). XLM was used in earlier Excel versions. VBA was first introduced in Excel 5 and is now widely utilized.

12.) What is the procedure for creating a new Excel worksheet?

Ans. You should use the insert worksheet tab at the bottom of the screen to create a new Excel worksheet.

13.) In MS-Excel, what is the purpose of the NameBox field?

Ans. By typing the range name or cell location in the name box, you can return to a specific region of the worksheet.

14.) Describe a few useful Excel functions.

Ans. The following are the functions in Excel that can be used to manipulate data:

SQRT, DEGREE, RAND(), GCD Logical Functions IF, AND, FALSE, TRUE Math, and Financial Functions
NOW(), DATEVALUE(), WEEKDAY(NOW()) are all date and time functions.
Pivot tables with Index Match – VLOOKUP and INDEX MATCH

15.) What does a red triangle in the upper right corner of a cell mean?

Ans. The red triangle shows that the cell has a comment linked with it. You can read the entire comment by hovering your mouse over it.

16.) What are the three different report formats available in Excel?

Ans. The sorts of report forms are as follows:

Compact Report Tabular

17.) How would you implement a dynamic range in Pivot Tables’ “Data Source”?

Ans. To provide a dynamic range in Pivot tables’ “Data Source,” first establish a named range with the offset function, then base the pivot table on that named range.

18.) What is the best way to resize the column?

Ans. To resize the column, first, modify the width of one column, then drag the right-hand boundary of the column header to the desired width. The alternative option is to select Format from the home tab and then select AUTOFIT COLUMN WIDTH under the cell part of Format. The cell size will be formatted when you click this.

19.) Describe pivot tables and how they are used.

Ans. A pivot table is a technique for quickly summarising vast amounts of data. It sorts, counts, totals, or averages the data in the spreadsheet automatically and presents the results in another spreadsheet. It helps you save a lot of time. Allows us to connect external data sources to our Excel spreadsheet.

20.) What is the procedure for creating a new Excel worksheet?

Ans. You should use the insert worksheet tab at the bottom of the screen to create a new Excel worksheet.

21.) In MS-Excel, what is the purpose of the NameBox field?

Ans. By typing the range name or cell location in the name box, you can return to a specific region of the worksheet.

22.) What can you do to prevent the pivot table from losing column width when it is refreshed?

Ans. By modifying the pivot table parameters, you may prevent format loss in a pivot table. Turn on “Enable Preserve Formatting” and disable “AutoFormat” under “Pivot Table Options.”

23.) Describe the different methods of Excel workbook protection.

Ans. A worksheet can be protected in one of three ways in Excel:

A password is required to open a worksheet.
Adding, removing, concealing, and unhiding sheets are all protected.
Changes in window size or position are protected.

24.) In pivot tables, how do you prevent automatic sorting?

Ans. To turn off automatic sorting in pivot tables, do the following:

More Sorting Options can be found by going to > More Sorting Options. Right-click ‘Pivot tables’ > ‘Pivot tables’ > ‘Pivot tables’ Select’sort menu’ > ‘More Options’ > ‘Sort automatically’ > deselect.

25.) What do MS-Freeze Excel’s Panes mean?

Ans. Freeze panes are used to lock any row or column. Even if we scroll the sheet vertically or horizontally, the locked row or column will remain visible on the screen.

26.) What is Excel’s IF function?

The IF function is used to execute the logic test. It determines whether or not specific criteria are true. If the condition is true, the result or output will be as expected. If the condition is false, the result or output will be as expected.
For instance, suppose you choose a cell and want it to display “more than five” when the value is true (=5 or 5) and “less than five” when the value is false (5). You can display the result by using an IF condition.
IF= (Logical test, value if true, value if false)
If (A1>5, “Greater than five,” “Less than five”), then

27.) Do you know how to make Excel function shortcuts?

Ans. Yes. Above the home button, the ‘Quick Access Toolbar’ can be modified to show the most commonly used shortcuts.

28.) What is the difference between MS-SUBSTITUTE Excel’s and REPLACE functions?

Ans. In a string, the SUBSTITUTE function replaces one or more instances of old text with new text.

SUBSTITUTE is a syntax word (text, oldText, newText, [instanceNumber])
The REPLACE function replaces a portion of a text string with a different set of characters.
REPLACE REPLACE REPLACE REPLACE REPLACE REPLACE RE (oldText, startNumber, NumberCharacters, newText)

29.) In Ms-Excel, what is the difference between COUNT, COUNTA, COUNTIF, and COUNTBLANK?

COUNT is a function that counts cells that contain numbers, dates, or any other value that is stored as a number, except blanks.
COUNTA is an abbreviation for Count. All are used to count any cell value that contains numbers, text, logical values, etc., but not blanks.
COUNTBLANK counts cells that are blank or have an empty string.

30.) What are the differences between left, right, fill, and dispersed alignments?

Ans. Left/Right alignment aligns the text to the cell’s left and right edges.

Fill the cell with the same text again and over again, as the name suggests.

Spread the text across the width of the cell with the distribution.

LEFT	FILL	DISTRIBUTED
Ab	abababababababab	a b
A	aaaaaaaaaaaaaaaa	A

31.) Which keys will you use to move to the previous and next worksheets?

Ans. The keys Ctrl + PgUp and Ctrl + PgDown will take you to the previous worksheet, while the keys Ctrl + PgDown will take you to the next sheet.

32.) What is the purpose of Excel’s LOOKUP function?

Ans. The LOOKUP function in Microsoft Excel returns a value from a range or an array.

33.) In MS-Excel, how can you apply the same formatting to each sheet in a workbook?

Ans. Choose ‘Select All Sheets’ from the ‘Worksheet tab’ menu. Any formatting you’ve done so far will be applied to the entire workbook. Select only the sheets that require formatting to apply to a specific group of sheets.

34.) To calculate the day of the week for a date, which function is used?

Ans. WEEKDAY () returns the weekday for a given day, counting from Sunday.

Let’s say the date at A1 is December 30, 2016.

WEEKDAY(A1,1) => 6 WEEKDAY(A1,1) => 6 WEEKDAY(A1,

35.) What are the advantages of utilizing formulas in an Excel spreadsheet?

Ans. Calculating numbers in an Excel sheet not only assists you in calculating the final sum up’ of the numbers, but it also calculates the number substituted by another number or digits automatically. Complex computations, such as payroll deduction or averaging the student’s results, become simple with an Excel sheet.

Calculating numbers in an Excel sheet not only assists you in calculating the final sum up’ of the numbers, but it also calculates the number substituted by another number or digits automatically. Complex computations, such as payroll deduction or averaging the student’s results, become simple with an Excel sheet.

36.) If you need more than two conditions or wish to analyze the list using a database function, what filter will you use?

Ans. Advanced Criteria Filter will be used to evaluate the list or if more than two conditions need to be tested.

37.) How can I quickly return to a specific section of a worksheet?

Ans. Using the name box, you can quickly return to a specific area of the worksheet. To return to a specific region of a worksheet, type the cell address or range name in the name box.

38.) What does Excel’s AND function do?

Ans. The AND function, like the IF function, performs a logical function. The AND function will analyze at least one mathematical expression in another cell in the spreadsheet to determine whether the output will be true or false. It is possible to observe the output of many cells in a single cell by using the AND function.

Example: If you have two cells, A1 and A2, and the values in those two cells are both greater than 5, you want the result to display as ‘TRUE’ in cell B1 if the value is greater than 5, and ‘False’ if any of the values are less than 5. You can accomplish this by using the AND function.

39.) In Excel formulas, what is the “What If” condition?

Ans. The “What If” condition is used in Microsoft Excel formulas to modify the data and produce different results.

For example, if you’re buying a new automobile and want to figure out how much tax you’ll have to pay, you can utilize the “What If” function. There are three cells in this example: A4, B4, and C4. The first cell will have the amount, the second cell will contain the tax percentage (7.5%), and the final cell will determine the precise tax amount.

40.) How do you stop pivot tables from sorting automatically?

Ans. To turn off the automatic sorting in pivot tables, go to Tools > Options > Options > Options > Options > Options > Options > Options >

Select “More Sort Options” from the drop-down menu. Right-click “Pivot table” > “Pivot table” > “Pivot table” > Select “Sort” from the drop-down menu > Select “More Options” from the drop-down menu. “Sort automatically when the report is prepared” should be deselected.