keySkillset

In this second blog of the series, here we continue with the top 20 questions and answers for Excel intermediate level. After browsing through the basic Excel questions, this is the perfect time to tackle these intermediate level Excel interview questions and answers. So, here is the list of the next 20 Excel questions and answers.

Meanwhile, here’s what we have posted related to this blog. Check out other blogs in this series.

Top 20 Excel intermediate questions and answers

1. What is the function of a Slicer in Excel?

The purpose of an Excel Slicer is to swiftly and effectively filter Pivot Tables and Tables. To access the feature in excel follow the below steps: For pivot table, Analyze tab -> Filter section → Insert slicer. For table, Design tab → Tools → Insert slicer.

2. What are the steps to create a Pivot Chart in Excel?

Follow these steps to create a pivot chart based on a pivot table:

Begin with a pivot table and click on any cell within the pivot table.
Navigate to the "Insert" tab.
Opt for the 'Pivot Chart' choice.
Select an appropriate chart type to represent the data derived from the pivot table.
Alternatively Analyze tab → Tools section → Pivot chart

3. What is data validation in Excel?

Data Validation confines the allowable input for specific cells or cell ranges. By accessing the 'Data' tab and selecting 'Data Validation' under 'Data Tools', users can specify the type of validation desired. For instance, applying data validation to the 'Name' column to exclusively permit text entries will trigger an error if a non-text value is entered. The user can also select to show the input message when clicked on the cells and also show any error message when an invalid data is entered. The error message type can be information, warning or stop from proceeding further.

4. How do you add a calculated field to a pivot table?

To add calculated fields in a pivot table, follow these steps:

Ensure your pivot table is selected. Create a pivot table from your dataset if you haven't already.
Navigate to the "Analyze" or "Options" tab on the Excel ribbon (the tab name may vary depending on your Excel version).
Locate the "Fields, Items & Sets" or "Formulas" button within the "Calculations" or "Tools" group. Clicking this button will trigger a drop-down menu.
From the drop-down menu, select "Calculated Field." This action will open a dialog box.
In the dialog box, provide a name for your calculated field in the "Name" field. This name will be used as the field name in the pivot table field list.
Enter the formula for your calculation in the "Formula" field. The formula should be constructed based on the available fields in your pivot table.
Click "OK" to apply the calculated field to your pivot table. The field will now appear in your pivot table's field list, and the calculated values will be displayed accordingly.

By following these steps, you can easily add calculated fields to your pivot table, allowing you to perform custom calculations and analysis on your data.

5. What’s the difference between a pivot table and a VLOOKUP function in Excel?

A pivot table serves as a versatile tool for data reporting, analysis, and summarization, providing valuable insights and dynamic reports by efficiently organizing and condensing large datasets.

The VLOOKUP function, on the other hand, focuses on searching for specific data based on criteria or a key. For instance, it can retrieve associated information from a dataset, such as pricing linked to a product code. It is also to be noted that the lookup value should always be in the left most column of the selected dataset.

When it comes to data organization, pivot tables create structured tables comprising rows, columns, and values, enabling data grouping and summarization across various dimensions.

In terms of data manipulation, pivot tables offer comprehensive options for data grouping, filtering, and computation within the table itself. In contrast, the VLOOKUP function primarily excels at fetching specified values based on lookup conditions.

In the output stage, pivot tables generate aggregated representations of data, presenting organized categories and summarized values in dynamic, interactive reports. On the other hand, the VLOOKUP function provides singular values for computations or display based on lookup criteria.

6. What is a nested IF statement in Excel?

Nested IF statements are employed when multiple conditions need to be evaluated within the context of the IF() function. In scenarios where the initial IF function's TRUE/ FALSE value requires further testing, it can be substituted with another IF function.

7. How do you provide Dynamic Range in ‘Data Source’ of Pivot Tables?

Utilizing a Dynamic Range within the data source of pivot tables ensures adaptability, enabling the pivot table to seamlessly accommodate new data upon automatic refresh.

To establish this dynamic range, you can create a Named Table. Navigate to the "Insert" tab and choose "Table." Within the "Table Design" options, assign a name to the table as required in Properties section, under Table name..

8. Which are the different wildcards used in Excel?

In Excel, there are three main wildcards: asterisk (*), question mark (?), and tilde (~).

Asterisk (*): The asterisk wildcard represents zero or more characters. For instance, "ex*" would match "Excel," "expertise," and any other words that start with "ex" followed by any number of characters.
Question Mark (?): The question mark wildcard represents any single character. For example, "R?in" would match "Rain" or "Ruins," where the question mark can stand for any character in that position.
Tilde (~): The tilde wildcard is used to escape or treat a wildcard character as a literal character. For instance, if you want to search for an actual asterisk in your data, you would use "*" to indicate that the asterisk should not be treated as a wildcard.

In summary, these wildcards provide powerful tools for searching and matching patterns within Excel data.

9. Various errors that occur in Excel are as follows. What are the errors?

Find here the list of errors that occur in Excel:

#DIV/0
#NAME?
#N/A
#NUM!
#NULL!
#VALUE!
#REF!
####
Circular reference

10. What is the significant thing about the SUBTOTAL function?

This function is designed to operate on vertical columns of data and is not intended for use with horizontal data. It's important to emphasize that applying the SUBTOTAL function and subsequently hiding a row will not impact the overall process. It allows the user to obtain custom totals based on the filtered data from the dataset.

11.How do you Freeze panes in Excel?

Freeze panes is a feature in Excel that allows you to maintain the visibility of specific rows and/ or columns while scrolling through a worksheet. To utilize freeze panes, follow these steps:

Go to the "View" tab located in the Excel ribbon
Locate and access the "Freeze Panes" option within the "Window" group.
If you intend to freeze the initial two columns of your data, begin by selecting the cell in the third column.
Then, click on 'Freeze Panes'. This action will be indicated by the appearance of a prominent gray border.

By employing freeze panes, you can ensure that your chosen rows and columns remain in sight as you navigate through your worksheet, offering improved clarity when working with sizable datasets.

12. What is UDF in Excel VBA?

A User Defined Function (UDF) in VBA, also known as a custom function, enables you to craft personalized functions within Excel. By utilizing VBA, you have the ability to design custom Functions (UDFs) that seamlessly integrate into worksheets, functioning akin to standard functions.

This proves particularly valuable when the array of existing Excel functions falls short. In such instances, you possess the capability to fashion bespoke UDFs tailored precisely to address your distinct requirements.

13. How do you modify a data source for a chart?

To modify a chart, begin by selecting the desired chart. Clicking on it will activate the chart, prompting the appearance of the Excel ribbon's contextual tabs: "Chart Tools" encompassing "Design," "Layout," and "Format." Ensure the "Design" tab is chosen.

Under the "Design" tab, locate the "Data" group. Within this group, opt for the "Select Data" button or alternatively, right click on the chat and click on ‘select data’ . Upon selecting the "Select Data Source" option, a dialogue box will emerge, comprising two segments: "Legend Entries (Series)" and "Horizontal (Category) Axis Labels."

Navigate to the "Legend Entries (Series)" section and click the "Edit" button to modify the data series, the showcased data points. This will trigger another dialogue box, known as "Edit Series." Within this box, you possess the capability to adjust the series name, series values, and series axis labels. The data range that defines the series can be edited here. Simply input or select the new range to update the cell range within the "Series values" column. After modifying the series data, exit the "Edit Series" dialogue box by clicking "OK." If needed, alterations to the category (X-axis) labels can be made within the "Select Data Source" dialogue box. To achieve this, access the "Horizontal (Category) Axis Labels" section and click the "Edit" button. A dialogue box titled "Axis Labels" will appear. Here, you can modify the range of cells defining the category labels. Adjust the cell range within the "Axis label range" box if necessary. After completing the requisite adjustments to the data source, close the "Axis Labels" dialogue box by clicking "OK."

Concluding the process, press "OK" to preserve the modifications and refresh the chart with the updated data source. This final step is executed through the "Select Data Source" dialogue box.

14. What does it mean to hide a sheet in Excel?

When you "hide" a sheet, it becomes invisible in your sheet navigation. Despite being hidden, the sheet remains present and accessible, unless additional measures are taken to safeguard the workbook. This feature is beneficial for concealing or "archiving" older information, even though the data remains within the file. To hide a sheet, right click ont the sheet name in the worksheet tabs and choose hide.

15. What does “merge” do in Excel?

Merging entails consolidating multiple cells into a solitary cell, serving a formatting purpose. For instance, you can merge cells in an entire row to craft a title.

16. What is the “Go to special” option in Excel used for?

The "Go To Special" feature facilitates precise and faster navigation to designated cells or cell ranges within a worksheet based on the criteria chosen. To access this option, navigate to the Home Tab ➜ Editing ➜ Find and Select ➜ Go To Special.

17. How do you group dates in the pivot table by month?

Here are the steps to group dates in the pivot table by month:

Choose any cell within the date column.
Perform a right-click on the selected cell, then opt for the "Group" function.
This action will trigger a pop-up window dedicated to date grouping.
Within this window, designate "Month" as the grouping criterion, and subsequently, click the OK button.

Similar steps can be employed to group dates within a pivot table based on years, quarters, and days.

18. How can you Un-Group dates in Excel?

If you want to get back your dates or want to ungroup dates you can do that with the “ungroup‘ option.

Select a cell from the data column.
Right-click.
Select “Un-Group”. The same steps can be followed for ungrouping any data.

19. How do INDEX MATCH work in Excel?

INDEX MATCH is a powerful combination of the INDEX and MATCH functions. This amalgamation yields a value by leveraging the capabilities of both functions. It's important to recognize that while INDEX retrieves a value based on specified column and row numbers, MATCH operates by locating the position of a cell. The synergy between these functions enables efficient and flexible data retrieval in Excel. The output of the MATCH function shall be input of the INDEX function.

20.How to apply the transpose function in Excel?

Follow these steps to apply the transpose function to selected cells:

Select cell range where the desired output is required
Since transpose is an array formula, ensure you've selected the precise number of cells to accommodate the transposed data.
Enter the formula "=TRANSPOSE(A1:F5)" (adjust the range as needed) into the formula bar.
Press Ctrl+Shift+Enter simultaneously. This signifies that you're entering an array formula.

The data will be transposed accordingly, and you'll observe the transformation in the selected cells.

Conclusion

In this second installment of our blog series, we've delved into the realm of Excel intermediate questions and answers, equipping you with essential knowledge to navigate complex tasks. By mastering functions like Slicers, Pivot Charts, and dynamic data validation, you're poised to elevate your Excel prowess. We've unraveled the intricacies of calculated fields, wildcards, and error handling, empowering you to manipulate data with finesse. Additionally, we've demystified freeze panes, UDFs, and advanced chart manipulation techniques, paving the way for efficient data analysis. Armed with these Excel intermediate interview questions and answers, you're primed to excel in the world of Excel, making informed decisions and unlocking hidden potentials in your data-driven journey.

‍