EXCEL Interview Questions

The term "ribbon" refers to the region at the top of the application that houses the MS Excel toolbars and menu items. With CTRL+F1, the ribbon can be displayed or hidden. The toolbars and menus have been replaced by the ribbon, which runs across the application's top. The top of the ribbons has a number of tabs, and each tab has its own set of commands. It has numerous tabs, including File, Home, View, Insert, and others. The ribbon can be altered to fit your requirements. Right-click on the Ribbon and choose "Customise the Ribbon" from the menu that appears. The following window appears as shown below. Each of these options is available for you to select or deselect.

Source: SpreadsheetWeb

Excel has three wildcards:

• Asterisk(*): It represents zero or more characters. Ex*, for instance, might stand for Excel, Extra, Expertise, etc.
• Question mark(?): It represents a single character. R?in, for instance, might be either Rain or Ruin.
• Tilde(~): A literal asterisk (*), a literal question mark (? ), or a literal tilde (~) can be created by adding a tilde (~) before a wildcard character to negate its effect.

The types of values that a person may enter into a specific cell or a range of cells are limited by data validation. Choose the "Data Validation" option under "Data Tools" on the Data tab. Choose the data validation type you want to use. For example, a 'Name' column has data validation applied to it so that it will only take text entries. It will throw an error if you enter anything other than text.

To fill in the results column, use the IF() function and a check with an AND condition.

Source: Simplilearn

• A string can have one or more instances of any old text substituted with fresh text using the SUBSTITUTE function.

Syntax: 

SUBSTITUTE(text, prevText, newText, instanceNumber)

Example: 

Let text at A1 be happy77

SUBSTITUTE(A1,”7″,”8″,1) =>happy87

SUBSTITUTE(A1,”7″,”8″,2) =>happy88

• The REPLACE function changes any portion of the old text string with new text.

Syntax: 

REPLACE(oldText, startNumber, NumberCharacters, newText)

Example: 

Let text at A1 be happy77

REPLACE(A1,6,1,”00″) =>happy007

VBA and XLM (Visual Basic Applications). XLM was utilized in Excel's past editions. VBA was first used in Excel 5 and is now primarily used.

Users can automate straightforward, repetitive chores and instructions using macros. For instance, rather than having to manually calculate sales, profits, losses, etc. every day, you could develop a macro that automatically calculates them at the end of the day and use it going ahead. Macros can be recorded and played at a later time or written down for use. Either go to the Developer tab and select Record Macro or go to the View tab to access it.

One of Excel's most effective tools, a pivot table is always helpful when examining data. Excel pivot tables let you easily count, compare patterns, and confirm data trends while also analyzing large amounts of data. 

A pivot table summarises your data. The information is presented as a chart. You can report on and investigate patterns using a pivot table and the data you've already given it. When working with large datasets, pivot tables are useful. To process data and information in our Excel spreadsheets, pivot tables are versatile and useful reports.

The following are some of the features of Excel pivot tables:

• Allow the precise facts you need to evaluate to be displayed
• Provide several vantage points to view the data
• Allow you to concentrate on crucial details
• Data comparison is quite useful.
• Pivot tables may recognize a variety of patterns, connections, data trends, etc.
• They can produce data instantly.
• Reliable reports
• Act as the foundation for pivot charts

Source: Excelcampus

It's not as difficult as it first appears to make a pivot table. To get the most out of this process, it is necessary to be aware of all its specifics.

• Make the data ready
• Put your information in the excel sheet.
• To construct a pivot table, highlight your cells.
• In "Row Labels" or "Column Labels," drag and drop a field.
• Drag a field into the "Values" section.
• Adjust your calculations.

While creating a pivot table, keep these in mind:

• Each of the columns' first-row headings should be distinctive.
• There should only be one type of data in each column.
• Rows can only include information for a single recording.
• no empty rows
• Columns shouldn't be left entirely empty.

• The data needed to create the pivot table must be kept apart from the other data in the sheet.

Select the table, then click on the Insert tab to create a pivot table. Then choose the Pivot Table command, and the following window will appear: 

Click OK after specifying the location where the table will be created. You will observe that a blank pivot table has been created once this is complete. Moreover, the PivotTables Fields window, which aids with pivot table configuration, will open.

The following are a few differences between Pivot charts and standard charts:

• The row/column format: A Pivot Chart's row/column orientation cannot be changed using the Select Data Source dialog box, in contrast to a normal chart. Instead, you can achieve the same result by pivoting the Row and Column labels of the corresponding PivotTable.
• Chart type: A Pivot Chart can be changed into any form of a chart, with the exception of a xy (scatter), stock, or bubble chart.
• Source of data: Pivot Charts are based on the data source of the related Pivot Table, whereas standard charts are tied directly to worksheet cells. In contrast to a standard chart, the Pivot Chart's Choose Data Source dialog box does not allow you to alter the chart's data range.
• Formatting The majority of formatting, including newly added chart elements, structure, and style, is kept when you reload a Pivot Chart. Trendlines, data labels, error bars, and other modifications to data sets, however, are not kept. After being applied, standard charts retain their formatting. Although you can't directly modify the data labels in a Pivot Chart, you may still do it by increasing the text's font size.

Excel uses slicers to easily filter Pivot Tables. Even more, you can link numerous slicers to numerous pivot tables.

Do the following to add a slicer to the pivot tables:

• Choose any cell in the pivot table by clicking it.
• Select Analyze, then Filter, then Insert Slicer.

The following are the steps to split a column into two or more:

• Choose the cell you would like to split. Then, click on Text to Columns under the Data tab.
• Decide on a delimiter.
• Choose the location where you wish to display the split after selecting the column data format.

On a worksheet, a specific cell is identified by its cell address. It is determined by the appropriate column letter and the corresponding row number.

The highlighted cell's address is D3 because, as shown, it is in row 3 and column 'D'.

Instead of using values while generating a formula, we use cell references. In this method, as we alter the values of the cells, the formula's outcome also alters. References come in two varieties:

• Relative Reference: Relative references indicate the precise location of the cell, and it is the row and column in which the cell that has a value or a formula is situated. If we try taking that same cell to relocate it to another cell, worth the redundancy, it will refer to the new cells according to where they are located. As a result, we can claim that Excel predetermines relative references. Example: See the illustration below, where there is a formula written in C9 and copied to C10. As you can see, C10, as opposed to A9 and B9, displays the total of A10 and B10.

• Absolute Reference: An exact address is represented by the absolute reference. In other words, it constantly refers to the same cell, making it impossible to change them during copying. Absolute references are crucial since they are fixed, which is excellent when using Excel functions or formulae. Example: The $ symbol must come before the column and row numbers if you want to use absolute referencing. The row and column addresses do not change when absolute cell references are used; they stay the same. Look at the illustration in the following image:

Image Source: Edureka

With freeze panes, any row or column can be locked. Even if we vertically or horizontally scroll the sheet, the locked row or column will still be visible on the screen. Observe the instructions below to freeze panes in Excel.

• Choose the Rows and Columns you want to Freeze first.
• Then choose Freeze Pane from the View tab.
• Now, you have the following three options to pick and choose which rows and columns to freeze.

To restrict someone from copying a cell from our worksheet, we need to follow the steps given below: 

• Select the data you wish to safeguard first.
• Press Ctrl, Shift, and F. It then shows the Format Cells tab. Select the Protection tab. Click OK after selecting Locked.
• Then, click Protect Sheet from the Review menu. To secure the sheet, enter the password.

Gif Source: WPS Academy

• Formula:
  • The user types in the formula, which looks like an Excel equation. Based on the user's preference, it might be any kind of calculation. It takes longer to manually type a formula each time you need to make a computation. 
  • Example: B1 + B2 + B3
• Function:
  • An Excel function is a built-in computation that has been predefined. Using functions makes conducting computations quicker and more comfortable.
  • Example: SUM(B1 : B3)

PEDMAS is the term used to describe the Excel operation order. The precedence list for an Excel operation is displayed below.

• Parentheses
• Exponentiation
• Division/Multiplication
• Addition
• Subtraction

As can be seen above, the exponentiation process is performed after the data in the parenthesis is processed. Following that, either the division or multiplication procedures may be performed. The outcome is then added and finally subtracted, to provide the result.

• COUNT: The number of cells with simply numerical values is counted. Blank cells, special characters, and cells with string values will not be included in the calculation.
• COUNTA: The number of cells with any kind of content is counted. Numeric data, special characters, and string values contained in cells will all be counted. A blank cell will not be taken into account, though.
• COUNTBLANK: It solely counts the number of blank cells, as the name would imply. Content-containing cells will not be taken into account.

Use the CONCATENATE command to combine text strings that are contained in numerous cells into one cell. Up to 30 text pieces can be joined using the Excel CONCATENATE function, which returns the result as text.

The Excel Concatenate function has the following syntax: CONCATENATE (text1, text2, ..)

The following formula can be used to CONCATENATE the values of two cells, A2 and B2: CONCATENATE (A2, B2). There will not be any delimiters used when combining the values. Use a space (" ") to demarcate the values: CONCATENATE(A3, " ", B3).

You must right-click a cell and select add comment from the cell menu to add a comment to it. Write your comment in the space provided for comments. There is a comment associated with that specific cell if there is a red triangle in the upper right corner of the cell. Right-click the cell and choose "Delete Comment" from the cell menu to get rid of a comment.

Spreadsheets are software programs that facilitate effective data organization, calculation, and sorting. A spreadsheet consists of rows and columns spread throughout. The total number of rows and columns on an MS excel worksheet is 1,048,576 rows by 16,384 columns.

There is a worksheet (labeled "Sheet1") as shown below, and we also notice a "+" sign somewhere at the bottom, which indicates we can create a new sheet. We can add, rename, remove, hide, show, and perform other actions on sheets. Worksheets are added by default as Sheet1, Sheet2, etc. Such sheets are simple to rename as necessary.

Excel's VLOOKUP (which stands for Vertical Lookup) function is used to find and produce data from a specific set. To make use of this functionality, we need to arrange the data vertically because V is for Vertical in VLOOKUP. Whenever we need to find a certain amount of data from a vast amount of data, VLOOKUP is tremendously helpful.

The VLOOKUP function in Excel has a lookup value and starts searching in the left-hand column. VLOOKUP will move right, or into the search value row if the value of that search appears first. It keeps on until the specified column number is returned. This function compares estimated and true search values. Nonetheless, the default match is approximate.

Here,

• lookup_value: The value that you wish to check for is known as the lookup value.
• table_index: The set of data to be taken from is the table index.
• col_index_num: col index num specifies the column you wish to extract the value from.
• range_lookup: Logical value, i.e., TRUE or FALSE, is the range lookup (TRUE finds the closest match; FALSE will check for an exact match)

Indeed, you can use VLOOKUP for several tables. If you have two search tables, create named fields for each of them and use the IF function to choose from each table in accordance with a predetermined set of criteria.

Source: Excel Tip

The user can use VLOOKUP to search for a value in a table's leftmost column. The value is then returned in a left-to-right way. On the other hand, the user can search for data in a row or column using the LOOKUP function. It shows up the value in a different row or column.

The IF() function in Excel does a logical test. If the test results in true, it returns a value; if the test results in false, it returns a different value. Whether the condition holds for the full chosen range determines what value is returned.

Source: My Online Training Hub

Cell values described by a particular condition or set of criteria are added by the SUMIF() function.

Syntax: =SUMIF(range, criteria, [sum_range])

Source: Excel Practice Online

Note:

• range (necessary): the set of cells you want to subject to criterion. Each range must include only numbers, names, arrays, or references containing numbers. Values that are blank or text are ignored. Dates in the typical Excel format could be found in the selected range.
• criteria (necessary): A number, expression, cell reference, text, or function that specifies which cells will be added can all be used as criteria
• sum_range   (optional): the actual cells to add if you wish to add cells that are not listed in the range argument. The cells that are given in the range parameter are added by Excel if the sum_range argument is not present (those cells to which the criteria are imposed).

The 'Data Validation' option found in the Data tab can accomplish this.

• Select the cells in which the drop-down lists are to be added.
• Select Data, then click on Data Validation.
• Select List from the menu under Allow.
• Give the input for the items (separated by a comma) in your dropdown list in the Source tab.

The dropdown list is ready.

Source: Trump Excel

Cells that meet the criterion are highlighted in this case using conditional formatting. Choose "Conditional Formatting" from the home tab, then "Greater Than option" under the Highlight Cells Rules section. Choose the color for the highlighted cells and specify the criterion.

You may utilize conditional formatting or the COUNTIF() function to identify duplicate values in a column. 

• Conditional Formatting: Choose "Highlight Cells Rules" under Conditional Formatting after first selecting the Home tab. Next, select "Duplicate Values."
• COUNTIF(): To determine whether the values in a given column are repeated, you can implement a COUNTIF() method.

You may eliminate duplicate values by choosing the 'Remove Duplicates' option from the Data Tools menu on the Data tab.

Absolutely, more than one basis table can be used to build pivot tables. Follow these instructions to do this:

• To launch the Pivot Table Wizard, press Alt+D followed by P.
• When you click Next after selecting the Multiple consolidation ranges option, another dialog box will appear.
• Choose the option "I will create the page fields" and then click "Next".
• The relevant ranges must all be added in the following window.

• Once that is finished, select the region where you wish to construct the table by clicking Next and then clicking Finish.
• You will observe that the pivot table was produced by combining the two tables.

You can also build a pivot table for tables from different sheets if both sheets are from the same worksheet. Use the same procedures as in Q31 to construct a pivot table from two separate sheets. After you define the tables, navigate to the appropriate sheet and choose the tables you want to combine.

ExcelLibrary can be used to create Excel files in C# without the need to install Microsoft Office. It is an open-source, cost-free, and basic library on Google Code. It is a PHP ExcelWriter port. It includes a DataSetHelper that makes using DataSets and DataTables easier. ExcelLibrary supports the older Excel format (.xls files), and support for the more recent 2007/2010 formats will be added.

The alternative is EPPlus. It has NPOI and is compatible with Excel 2007/2010 format files (.xlsx files). Pivot Tables are also supported by EPPlus.

That is a common problem encountered by Excel users. But, you can include a '=' before the double quotes to prevent Excel from turning certain text values into dates automatically. The data must be text due to this operation. Example: =”2022-11-27″.

Indeed, using a spreadsheet in the.xls format, we can decrypt the password for an Excel VBA project. Due to the secure nature of.xlsx files, this won't function with them. Using a hex editor, change the password entry in the file.

• Create a fresh .xls file.
• Set a simple password, such as "abcd," in the VBA section.
• Save the document, then exit
• Verify the file size.
• Use a hex editor to view the file.
• Start copying the lines with the ensuing keys: CMG=…., DPB=…, GC=…
• Now that we don't know the VBA password for the excel file, we need to back up that data.
• Using the hex editor, launch the file
• Put the lines you just copied from the dummy file in there.
• Save the excel document, then quit.
• Open the excel document where the VBA code has to be checked. The password is abcd.

What-if analysis is a technique for changing one or more cellular formulas to examine how the changes affect the worksheet results. Three different What-if approaches for analysis are available in Excel: Scenarios, Goal Seek, and Data Tables.

Data tables and scenarios offer a selection of inputs for potential results. While several variables can work with scenarios, a limit of 32 input values is allowed.  Data tables only work with one or two variables, but they can all take on a variety of different values. Unlike Scenarios and Data Tables, Goal Seek takes outputs and determines prospective inputs to the same.

Source: Wmfexcel

The data that is available in the Pivot Tables are automatically sorted by Excel. If you do not want Excel to do this action, select More Sort Options from the drop-down option for the Row Labels or Column Labels. The Sort dialog box appears as it opens. Deselect the option for Automatic Sort by selecting More Options.

The F8 key can be used to debug a VBA program line by line. Also, you have the option of setting a breakpoint to stop the execution anywhere.

When you press F8, the code will begin to be executed from the beginning and continue until the conclusion, executing the following line each time. The highlighted line and the yellow arrow indicate the current point of execution.

When there are no exact matches for the provided lookup_value, VLOOKUP will fetch values to get an approximate match. Set the range_lookup value to TRUE for a rough match. Keep in mind that for VLOOKUP to do an approximate match, the table must be ordered in ascending order. In this case, VLOOKUP basically starts by searching for a roughly matching value to the specified lookup value before stopping at the value that is the next largest. It then enters that row to return the value from the designated column. 

Example:

• Choose the target cell, then enter "=".
• Deploy VLOOKUP.
• Add the lookup value to the list of parameters.
• Use TRUE as the range lookup value.
• The function will be =VLOOKUP (55, A12: C15, 3, TRUE)

The lookup value is 55 and the next largest value near the lookup value that is present in the first column is 40. Hence, the output is ‘Second Class’.