Excel Interview Questions and Answers Business Management

In Excel, you can Freeze Panes to keep an area of a worksheet visible while you scroll to another area of the worksheet. For example, you might want to keep row and column labels visible as you scroll through the data in the worksheet. Or, you might want to keep a title in place as you scroll down a long list of sales data. To Freeze Panes, select the cell below and to the right of the area that you want to keep visible as you scroll. Then, on the View tab, in the Window group, click Freeze Panes.

Depending on what cell you have selected when you click Freeze Panes, different panes are frozen: if you select one cell, only that row is frozen; if you select one cell in a row but not the header cells at the top of the worksheet, only that column is frozen; if you select multiple cells including header cells (for both rows and columns), multiple rows and columns are frozen. You can also freeze just the top row or leftmost column by clicking Freeze Top Row or Freeze First Column on the View tab.

To unfreeze panes, click Unfreeze Panes on the View tab. If multiple panes are frozen and Unfreeze Panes are not available, click Freeze Panes again so that no panes are frozen before you click Unfreeze Panes. For more information about freezing panes, see Keep headings visible when scrolling in Excel.

Moreover, the keyboard shortcut for this free function is Alt+W+F. To use it, simply select the cell where you want to split the screen. Then press Alt+W+F and Excel will automatically freeze the panes for you. You can also use this shortcut to unfreeze panes that have been previously frozen. Simply press Alt+W+F again and the panes will be released. This keyboard shortcut is a quick and easy way to Freezing panes in Excel without having to use the mouse.

When working with large amounts of data in Excel, it is often necessary to reference specific cells by their address. A cell address is a column and row number where the cell is located. For example, the address of the cell in the top left corner of the spreadsheet is A1. The column number comes first, followed by the row number. The address of a cell can be entered manually by typing it into the formula bar, or it can be selected using the mouse.

When multiple cells are selected, Excel will show the range of addresses in the selection as well. In many cases, it is also possible to use relative cell references, which will adjust based on the position of the formula.

For example, if a formula in cell A1 needs to reference the cell to its right, it can use a relative reference of B1. However, if that same formula were copied to cell B1, it would automatically update to reference C1 instead. Relative references are often used when creating formulas that must be applied across a range of cells.

In Microsoft Excel, you can change the width of a column in two ways: by dragging the column boundary or by changing the width in the Column width box. To resize a column by dragging the column boundary, position your mouse over the right border of the column until you see the cursor change to a double-line with an arrow on each side. Then, click and hold down your mouse button as you drag the column boundary to the desired width.

If you want to be more precise, you can change the width in the Column width box. First, select the column or columns that you want to resize. Then, go to Home > Format > Column Width. In the Column width box, type in the desired width and press Enter. The new column width will be applied to all selected columns. Whether you resize by dragging or by entering a value, the maximum column width is 255 characters.

You can also use keyboard shortcuts to resize columns. You can change column width by pressing Alt+O, then C (for Column) and W (for Width). Press Enter after you type your values.

Expect to come across this popular question in Excel interview questions for freshers.

There are a few ways to prevent someone from copying cells from your worksheet. One way is to protect the sheet, which will prevent anyone from making any changes to the sheet. To do this, go to the Review tab and click 'Protect Sheet.' You'll then be able to set a password for the sheet.

Another way is to hide the formulas in your cells. To do this, select the cells that contain formulas and then go to the Home tab and click 'Format.' In the 'Format Cells' window, go to the 'Number' tab and select 'Custom.' Then enter a formula in the 'Type' field that will display nothing when the cell is formatted (e.g. ";;;"). Finally, you can lock cells so that they can't be edited.

For this, select the cells you want to lock and then go to the Home tab and click 'Format.' In the 'Format Cells' window, go to the 'Protection' tab and check the 'Locked' box. Once you've done this, you'll need to protect the sheet again so people can't uncheck the box and edit the cells.

In Excel, both formulas and functions are used to perform calculations. However, there are some key differences between the two. A formula is a combination of values, cell references, and operators that results in a value. For example, the formula =A1+B1+C1 would add the values in cells A1, B1, and C1 together.

A function is a built-in operation that can be used to perform a specific calculation. For example, the SUM function can be used to add together a range of cells. Functions often take multiple arguments, which are values or cell references used as input for the function. Functions can also be nested, which means that one function can be used as an argument for another function. For example, the formula =SUM(A1:A10) would add all of the values in cells A1 through A10.

A spreadsheet is a computer application that simulates a paper worksheet, becoming increasingly popular in solving engineering problems. Users can easily insert, delete and manipulate data in it, as well as performing simple or complex calculations. A single sheet in a workbook is called a worksheet.

An Excel workbook may contain multiple worksheets; each worksheet may contain data and graphs. Graphs made on the same worksheet are called embedded charts, while graphs made on separate sheets are called chart sheets.

Spreadsheets offer various tools for data manipulation, including mathematical functions, statistical functions, and graphing tools. These features make spreadsheets an attractive tool for engineers. However, care must be taken when using spreadsheets because errors in calculation or logic can lead to incorrect results.

Users can navigate through a workbook by moving from one cell to another. A cell is the intersection of a row and column, and it is typically where you enter data or formulas. You can refer to cells by their addresses, which consist of the column letter and row number (for example, A1).

To select a range of cells, you click the first cell in the range and then drag to the last cell while holding down the mouse button. When you release the button, all of the cells in the range are selected.  

Formulas are used to perform calculations on data entered into a spreadsheet. For example, you can add two numbers by entering =5+10 into a cell. Functions are prewritten formulas that perform common calculations such as summing a range of cells or calculating the average of a range of cells.

Functions begin with an equal sign (=) followed by the function name and arguments (input values). For example, the SUM function calculates the sum of values in a given range; its syntax (the way it is written) is =SUM(range). Arguments are enclosed in parentheses and are separated by commas; for example, =SUM(A1:A5) calculates the sum of cells A1 through A5.

You can also create your own custom functions with Visual Basic for Applications (VBA), Excel's programming language.

The shortcut to add a filter to a table is Ctrl + Shift + L. This will bring up the Filter menu, which will allow you to select the cells you want to filter. You can also use the shortcut Ctrl + Shift + F3 to open the Filter menu.

There are two ways to create a hyperlink in Excel. First, you can select the cell where you want the hyperlink to appear and click the Insert Hyperlink button on the ribbon. This will open the Insert Hyperlink dialog box, where you can enter the URL or browse to the file or page you want to link to.

Alternatively, you can enter the URL or file path directly into the cell. To do this, simply type =HYPERLINK("URL or file path") into the cell. Either method will create a live link that you can click on to open the linked page or file.

Moreover, there is a quicker method by using the keyboard shortcut. To create a hyperlink, simply select the cell or range of cells that you want to link to, then press CTRL + K. This will open the InsertHyperlink dialog box. From here, you can select the location of the link, including another worksheet in the same workbook or an external file.

One of the most basic Excel interview questions; be ready for it.

There are majorly four different workbook protection types in Excel:

• Password protection
• Worksheet protection
• Workbook structure protection, and
• Cell-level protection.

Password protection is the most basic form of protection and simply requires a user to enter a password in order to open the file.

Worksheet protection prevents users from making changes to the structure of the worksheet, including adding, deleting, or moving cells. Workbook structure protection is similar to worksheet protection but also prevents users from adding, deleting, or renaming worksheets within the workbook. Cell-level protection is the most comprehensive form of protection and prevents users from making changes to the contents or formatting of cells.

Ribbon is a user interface element in Microsoft Excel that provides quick access to commands. It organizes commands into a series of tabs, each with its own set of controls. The tabs are designed to provide a consistent experience across Microsoft Office products, making it easy for users to find the commands they need.

The Ribbon was first introduced in Office 2007, and has been carried over into subsequent versions of the software. Although some users initially found the Ribbon to be confusing, it has become increasingly popular in recent years.
The Ribbon is comprised of several different tabs, each of which contains a specific set of commands. The tabs are as follows:
Home Tab: The Home tab contains the most commonly used commands in Excel, such as the commands for formatting cells and performing calculations.
 
Insert Tab: The Insert tab contains commands for inserting objects into an Excel spreadsheet, such as charts, hyperlinks, and images.
 
Page Layout Tab: The Page Layout tab contains commands for working with the page setup of an Excel spreadsheet, such as changing the margins and orientation.
 
Formulas Tab: The Formulas tab contains commands for working with formulas and functions in an Excel spreadsheet.
 
Data Tab: The Data tab contains commands for working with data in an Excel spreadsheet, such as sorting and filtering data.
 
Review Tab: The Review tab contains commands for reviewing an Excel spreadsheet, such as spell-checking and adding comments.
 
View Tab: The View tab contains commands for changing how an Excel spreadsheet is displayed, such as changing the zoom level or hiding/unhiding rows and columns.

Help Tab: The Help Tab in Microsoft Excel provides quick access to help topics, tutorials, and how-to videos.

Today, the Ribbon is one of the most widely used user interface elements in Microsoft Excel. Thanks to its intuitive design and easy-to-use controls, it helps users get the most out of the software.

To prevent someone from copying the cell from your worksheet, you can protect the worksheet or lock the cells. If you protect the worksheet, users will be unable to select locked cells, and be prompted for a password if they try to edit the worksheet.

To lock cells, select the cells that you want to lock, and then click Format > Cells. In the Protection tab, check the Locked checkbox. When you protect the sheet, you will need to enter a password to unprotect it. If you forget the password, it cannot be recovered.

There are several report formats available in Excel, including Compact, Tabular, and Outline Report Formats.

• Compact Report Format

The compact report format is the simplest of the three report formats. In a compact report, all data is displayed in a single column. This can be useful if you have a large amount of data to display in a small space. However, compact reports can be difficult to read and understand if you have more than a few rows of data.

• Tabular Report Format

The tabular report format is similar to the compact report format, but the data is displayed in multiple columns. This can make the data easier to read and understand, but it can also make the report more difficult to fit into a small space.

• Outline Report Format

The outline report format is the most complex of the three report formats. In an outline report, the data is organized into multiple levels, with each level representing a different category of information. This can make it difficult to find specific information, but it can also be very helpful if you need to organize a large amount of data.

Each of the three report formats has its own strengths and weaknesses. The best way to choose the right report format for your needs is to experiment with each one and see which one works best for you.

Yes, you can add new rows and columns to an Excel sheet. To do so, simply click on the cell where you want to add the new row or column and then click on the "Insert" tab at the top of the screen.

From there, you can select either "Insert Row" or "Insert Column" to add the new row or column. You can also right-click on the cell where you want to add the new row or column and then select "Insert" from the drop-down menu. Either way, adding new rows and columns to an Excel sheet is a quick and easy process.

Furthermore, adding new rows and columns to an Excel sheet can be done quickly and easily using the keyboard shortcuts "Ctrl+Shift+Plus". This shortcuts will insert a new row or column at the location of the active cell, respectively. To add multiple rows or columns at once, simply select the desired number of cells before using the shortcut. For example, if you want to insert two new columns, you would first select two cells side by side, then press "Ctrl+Shift+Plus". Similarly, if you want to insert three new rows, you would first select three cells in a row, then press "Ctrl+Shift+Plus".

Adding a note to a cell in MS Excel is a simple process that can be accomplished in just a few steps. First, select the cell to which you want to add the note. Then, click the "Insert" tab on the ribbon. In the "Notes" section, click the "Comment" button. A text box will appear in the cell, and you can type your note into this box.

Once you're finished, click the "Comment" button again to close the text box. Your note will now be visible whenever you hover over the cell. If you need to make changes to the note, simply double-click on the cell to reopen the text box.

In Microsoft Excel, you can add a note to a cell using the keyboard shortcut SHIFT+F2. This shortcut will open the Note dialog box, where you can type in the text of your note. Once you have added the note, it will appear in a small box next to the cell. You can view or edit the note by hovering your mouse over the cell.

The IF function in Excel is a powerful tool that allows you to make decisions based on specified conditions. For example, you could use the IF function to determine whether a cell contains a number or text. If the cell contains a number, the function would return the number; if the cell contains text, the function would return an error.

The IF function can also be used to compare two values and return one of two results based on the outcome of the comparison. For instance, you could use the IF function to compare the value in one cell to the value in another cell.

If the first cell is greater than the second, the function would return "TRUE"; if not, it would return "FALSE". As you can see, the IF function is a versatile tool that can be used to solve a variety of problems.

A staple in VLOOKUP interview questions, be prepared to answer this one.

Excel's LOOKUP function searches for a value in an array and returns a corresponding value from another array. The function can search horizontally (across columns) or vertically (down rows), but it only searches in one direction at a time. This makes it different from Excel's VLOOKUP and HLOOKUP functions, which can search in multiple directions. LOOKUP is also case-sensitive, so you must ensure that the values you are searching for are entered in the correct case.

When using the function, you need to specify the array to search, the value to look for, and the array to return the corresponding value from. If more than one value is found, LOOKUP will return the first match it finds. For example, if you have a list of employee names and ID numbers, you could use LOOKUP to find an employee's ID number based on their name. The function is particularly useful when you have large lists of data and need to find corresponding values quickly.

Macros are a great way to automate repetitive tasks in Excel. A macro is a piece of code that can be executed by clicking a button or running a command. You can use macros to speed up data entry, simplify complex formulas, and perform other time-saving tasks.

To create an Excel macro, you'll need to use the Visual Basic for Applications (VBA) editor. This is a tool that allows you to write and edit code. To open the VBA editor, press Alt+F11 on your keyboard. Once the editor is open, you can start creating your macro.

When you're creating a macro, you'll need to specify what actions should be carried out. For example, you might want to insert a row or column, copy data from one sheet to another, or format a range of cells. Once you've specified the actions, you can assign a shortcut key or create a button that will run the macro.

Macros can save you a lot of time when used correctly. With just a few lines of code, you can automate tedious and time-consuming tasks.

Pivot tables are an extremely useful tool for exploring and analyzing data, but they can sometimes be frustrating when they automatically sort the data in a way that you don't want. Fortunately, it's easy to disable automatic sorting in pivot tables. Just follow these simple steps:

1.Open the pivot table in Excel.

2.Click on the "Options" tab.

3.In the "PivotTable Options" menu, select "Sort and Filter."

4.Uncheck the "AutoSort" box.

5.Click "OK" to save your changes.

Now your pivot table will no longer automatically sort itself, and you can arrange the data however you like. Keep in mind, though, that this setting is only applied to the current pivot table if you create a new pivot table, you'll need to disable automatic sorting again.

In Microsoft Excel, there are four different functions that can be used to count cells: COUNT, COUNTA, COUNTIF, and COUNTBLANK. Each of these functions has a different purpose, and as a result, they can produce different results.

The COUNT function simply counts the number of cells that contain numeric values. It does not matter if the cells contain text or formulas, the COUNT function will only count cells that have numerical values.

The COUNTA function, on the other hand, counts the number of cells that are not empty. This means that it will count cells that contain text, numbers, formulas, or anything else. The COUNTA function is often used to get an approximate count of the total number of entries in a range of cells.

The COUNTIF function is a bit more specific than the previous two functions. This function allows you to specify a criterion, and then it will only count the cells that meet that criterion. For example, you could use the COUNTIF function to count the number of cells that contain the word "apple."

Finally, the COUNTBLANK function counts the number of blank or empty cells in a range. This can be useful for determining how many cells in a range still need to be populated with data.

This question is basic Excel questions and answers, be ready to tackle it.

Pivot tables are one of the most powerful tools in Excel. You can use them to easily summarize large data sets and to research trends. To create a pivot table, first, choose the data which you need to include. Next, go to the tab Insert and click on Pivot Table. In the Create Pivot Table dialog box, select where you wish to place the pivot table.

By default, Excel will create a new worksheet for the pivot table. However, you can also choose to place it in an existing worksheet. Once you have selected a location, click OK. Excel will then display the PivotTable Field List. You can also choose how to summarize the data, such as by sum, average, or count. Once you have selected all of the fields, click OK. Excel will then create your pivot table.

For detailed steps, check below:

1. Open your Excel workbook and select the data you want to include in your pivot table.
2. Click the Insert tab on the ribbon and then click PivotTable.
3. In the Create PivotTable dialog box, make sure that the selected data is correctly identified in the Table/Range field.
4. Choose where you want to place your pivot table in an existing worksheet or in a new worksheet—and then click OK.
5. In the PivotTable Field list, drag the fields that you want to include in your pivot table into the areas where it says Drag fields between areas below.
6. When you're finished, click OK.
7. Your pivot table will appear on the worksheet! To see different views of your data, simply click on the field name in the PivotTable Field list and then choose from one of the options under Show Values As.
8. To format your pivot table, click on any cell within the pivot table and then click on the Design tab on the ribbon. From here, you can change things like colors, fonts, and layouts.

In addition, to create a pivot table, you can also take a quicker way which is to select the data you want to include and then press the keyboard shortcut ALT + N + V. This will bring up the Pivot Table dialog box, which will allow you to choose the fields you want to include in your pivot table. Once you have selected the fields you want to use, simply click OK, and your pivot table will be created. You can then use the pivot table to quickly summarize your data and look for trends.

Excel supports two wildcard characters - the question mark (?) and the asterisk (*). The question mark represents any single character, while the asterisk represents any sequence of characters. For example, the following formula would return all cells that contain a four-letter word: =COUNTIF(A1:A10,"????).

Similarly, the formula =COUNTIF(A1:A10,"*a*) would return all cells that contain the letter "a", regardless of the number of letters before or after it. Wildcards can be useful for finding data that contains a specific pattern, but they can also make it difficult to find an exact match. For this reason, it is important to use them carefully.

Nesting IF statements in Excel refers to placing an IF statement inside another IF statement. This is often done to increase the number of conditions that can be tested for, or to test for multiple conditions within a single cell. For example, you might want to nest an IF statement inside another IF statement to test for two different conditions, such as "if the sales total is greater than $5,000, then give a 10% discount; otherwise, give a 5% discount."

By nesting one IF statement inside another, you can create far more complex conditional formulas than would be possible using a single IF statement. However, it is important to remember that each additional IF statement will add to the overall complexity of the formula, and may make it more difficult to troubleshoot errors.

Pivot Tables are one of the most powerful features in Microsoft Excel. You can use them to summarize large sets of data and explore different relationships within the data. One of the key features of Pivot Tables is the ability to dynamically change the data source. This can be done by simply changing the range of cells that are selected in the "Data Source" field.

For example, if you have a Pivot Table that is based on a data set that contains 100 rows, you can change the Data Source to include only the first 50 rows. This can be useful when you want to compare two different sets of data side by side. To do this, simply select the range of cells in the Data Source field, and then click "OK." The Pivot Table will automatically update to reflect the new data source.

Excel pivot tables are a powerful tool for data analysis, allowing you to summarize large sets of data and explore relationships between different data points. One of the advantages of pivot tables is that they can be created from multiple sources of data. For example, you could create a pivot table that includes data from multiple worksheets, or even from multiple workbooks.

To do this, simply select the data in the pivot table, and then click the "Create PivotTable" button. In the "Create PivotTable" dialog box, simply select the additional data sources, and then click OK. Excel will then create a pivot table that combines all of the selected data sources.
28. How do you create a column in a pivot table?
You can add columns to a pivot table in Excel to help further analyze your data. To do this, first select the cell in the pivot table where you want the column to be added. Then click the "Insert" tab on the ribbon and click "PivotTable." In the dialogue box that appears, select the "Fields, Items, and Sets" tab.

Next, drag the field that you want to add as a column from the "Choose fields to add to report" pane to the "Column labels" pane. You can also choose how you want your data to be sorted and whether or not you want it to be subtotaled. Once you have made your selections, click "OK" to add the column to your pivot table.

Pivot tables are a powerful tool in Excel that allow you to summarize and analyze data from multiple worksheets. To create a pivot table, you first need to select the data that you would like to include in the table. This data can come from multiple worksheets, as long as all of the worksheets are in the same workbook. Once you have selected the data, go to the Insert tab and click on Pivot Table.

In the Create PivotTable dialog box, make sure that the correct data range is selected and then click OK. Excel will automatically create a pivot table with the most common options selected. You can then change these options to customize the table as needed. For example, you can choose which columns and rows to include, as well as how to summarize the data.

By using pivot tables, you can easily analyze large amounts of data from multiple worksheets and effectively summarize your findings.

PivotTables are a great way to quickly summarize data, but by default they will automatically sort your data alphabetically. If you want to keep your data in its original order, there are a few steps you can take.

First, make sure that your data is formatted as a table. This will give you more options for controlling the sort order. To do this, select your data and go to the Insert tab on the ribbon. In the Tables group, click on Table.

Next, go to the PivotTable Tools tab and click on Options. In the Sort group, click on Custom Sort.

Under Order, select Values in selected order. This will keep your PivotTable sorted according to the order of your data in the underlying table. You can also choose to sort by labels or by values, depending on how you want your PivotTable to appear.

The syntax of Vlookup is as follows: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The lookup_value is the value that you want to look up in the first column of the table array. The table array is the range of cells that contains the data that you want to look up. The col_index_num is the column number in the table array from where the matching value should be returned.

The [range_lookup] is a logical value which specifies whether you need Vlookup to find an exact match and an approximate match. If omitted, Vlookup will default to an approximate match. An exact match must be found in order for Vlookup to return a result. If an exact match is not found, Vlookup will return #N/A. You can use the wildcard characters * and ? to find an approximate match.

An asterisk (*) matches any sequence of characters, and a question mark (?) matches any single character. For example, if you want to find an approximate match for "sm*th", you can use "sm?th" as your lookup value. Vlookup will then return the closest match that it can find.

Compound interest is interest earned not only on the initial principal, but also on the accumulated interest of previous periods. In other words, compound interest is "interest on interest." To calculate compound interest in Excel, you can use the FV (future value) function. This function calculates the future value of an investment based on periodic, constant payments and a constant interest rate.

For example, let's say you want to know how much money you will have in 10 years if you invest $500 today at a 5% annual rate of compound interest. In this case, you would use the following formula: =FV(5%,10,-500). This formula would give you a future value of $1,276.28 after 10 years.

Keep in mind that the FV function assumes that payments are made at the end of each period. If your payments are made at the beginning of each period, you'll need to adjust the formula accordingly. For example, if payments are made at the beginning of each year, you would use the following formula: =FV(5%,10,-500,1).

This would give you a future value of $1,259.34 after 10 years. As you can see, even a small change in the timing of payments can have a big impact on the future value of an investment.

Exact match is a function in MS Excel that allows you to compare two cells and return either a "TRUE" or "FALSE" value, depending on whether the cells are an exact match. For example, if you were comparing the cells A1 and B1, and both cells contained the text "Hello world", then the formula would return a "TRUE" value.

However, if even one character were different - for example, if cell A1 contained the text "Hello world!" and cell B1 contained the text "Hello world" - then the formula would return a "FALSE" value. Exact match is often used in conjunction with other functions, such as IF or COUNTIF, to take action based on whether two cells are an exact match.

Excel's VLOOKUP function is a powerful tool for finding specific information in a large spreadsheet. However, sometimes the data you're looking for doesn't fit neatly into the predefined categories that VLOOKUP expects. That's where wildcards come in. Wildcards are symbols that can stand in for unknown characters in a text string.

The two most common wildcards are the question mark (?) and the asterisk (*). To use a wildcard with VLOOKUP, simply enter the wildcard character in the cell where you would normally enter the search term. For example, if you're trying to find a customer named "Smith" but aren't sure of the exact spelling, you could enter "Sm*th" in the search field. VLOOKUP will then return all records that contain a word that starts with "Sm" and ends with "th", such as "Smith", "Smyth", and "Smithers".

You can also use wildcards to find partial matches within a cell. For example, if you're looking for a product code that starts with "ABC", you could enter "ABC*" in the search field. This would return all records that contain a word that starts with "ABC", such as "ABC123" or "ABCD56".

Wildcards are a versatile tool that can be used to find all sorts of information in an Excel spreadsheet. So next time your search terms don't seem to be returning the desired results, try using a wildcard character and see if that does the trick.

Pivot tables are one of the most powerful features of Microsoft Excel. Essentially, a pivot table is a way to summarize large amounts of data by condensing it into a smaller, more manageable format. Pivot tables can be used to quickly calculate totals and averages, and they can also be used to group data by categories.

For example, if you have a list of sales data, you could use a pivot table to calculate the total sales for each product category. Pivot tables are an extremely versatile tool, and they can be customized to suit any need. With a little practice, pivot tables can save you hours of time when working with large datasets.

There are four main types of text alignments in MS Excel: left, right, fill, and distributed. Left alignment is the default setting in Excel, and it simply means that the text is aligned along the left side of the cell. Right alignment is the opposite of left alignment, and it means that the text is aligned along the right side of the cell.

Fill alignment means that the text is evenly distributed across the width of the cell, while distributed alignment means that the text is evenly spaced out across the entire row (including any empty cells). Each type of alignment has its own uses, and you can experiment with different alignments to see which one works best for your needs.

This is a frequently asked advanced Excel interview questions and answers.

The AND function in Excel returns a TRUE value if all conditions are TRUE, and a FALSE value if any of the conditions are FALSE. For example, the formula =AND(1>0, 2>1) returns a TRUE value because both conditions are met. The formula =AND(1>0, 2<1) returns a FALSE value because one of the conditions is not met. The AND function can be used to test multiple conditions at the same time.

In order to do this, simply enter the desired conditions separated by commas. For example, the formula =AND(A1>0, A2>0, A3>0) will return a TRUE value if all values in cells A1, A2, and A3 are greater than 0. If any of the values are less than or equal to 0, the function will return a FALSE value. The AND function is often used in conjunction with other functions such as IF or COUNTIF in order to create more complex formulas.

The SUBSTITUTE function is used when you want to replace a specific text string with another string. For example, if you wanted to replace all instances of the word "dog" with the word "cat", you would use the SUBSTITUTE function. The REPLACE function, on the other hand, is used when you want to replace a specific character or set of characters at a specific location in a text string.

For example, if you wanted to replace the third character in the string "1A2B3C" with the letter "X", you would use the REPLACE function. In summary, the SUBSTITUTE function is used to replace a specific text string with another text string, while the REPLACE function is used to replace a specific character or set of characters at a specific location in a text string.

There are two ways to pass arguments to a VBA function. The first is by using the Call statement. For example, if you have a function named "MyFunction" that takes two arguments, you would use the following syntax: Call MyFunction(argument1, argument2). The second way to pass arguments to a function is by using the Application.Run method. This method takes a variable number of arguments, which are passed to the function as an array.

For example, the following code would call MyFunction with three arguments: Application.Run "MyFunction", argument1, argument2, argument3. When using the Application.Run method, you must enclose the function name in quotation marks.

The SUM function in Excel allows you to add values in a range of cells. For example, if you have a column of numbers, you can use the SUM function to calculate the total. The SUMIF function is similar, but it allows you to specify criteria that must be met for a cell to be included in the sum. For example, you could use the SUMIF function to sum only the cells that contain positive values. This can be useful for quickly calculating totals without having to examine each individual cell.

You can create shortcuts for most frequently used formulas in Microsoft Excel by using the "User Defined" function. This function allows you to create a custom keyboard shortcut for any formula you frequently use. To use this function, open the "Formulas" tab and select "User Defined."

Then, enter the keyboard shortcut you would like to use for the formula in the "Shortcut" field. Finally, enter the formula in the "Formula" field and click "OK." The shortcut will now be saved and can be used anytime you need to enter the formula into a cell. Keep in mind that shortcuts are not case-sensitive, so you can use whichever casing you prefer.

Expect to come across this popular question in the advanced Excel test for interview.

In Microsoft Excel, the three main ways to get data into cells are the .text, .value, and .value2 properties. The main difference between the three is that the .text property always returns a string, while the .value property can return either a string or a number depending on how it is formatted.

The .value2 property always returns a number, but it doesn't take into account certain number formats, such as dates and times. As a result, it is generally best to use the .value property when getting data from cells. However, if you know that the cell will always contain a string, then using the .text property can be more efficient.

In Microsoft Excel, ThisWorkbook refers to the workbook where the code is running, while ActiveWorkbook refers to the frontmost workbook. In VBA, we can use ThisWorkbook to change properties of the workbook where the code is running, or to reference that workbook in worksheet formulas. Activeworkbook can reference the frontmost workbook – regardless of whether or not the code is running in that workbook.

Another way to think of it is that ThisWorkbook always refers to the “parent” workbook, while Activeworkbook can refer to any open workbook. For example, we could use ThisWorkbook to save the currently open workbook, or close it. But we could also use Activeworkbook to save or close whichever workbook is currently active.

In general, ThisWorkbook is more commonly used than Activeworkbook, since it’s more specific and less likely to cause errors. But there are situations where using Activeworkbook might be more convenient. For instance, if we have a macro that needs to run on all open workbooks, we could use Activeworkbook to loop through each one. Or if we need to reference a specific worksheet in another open workbook, we could use

ActiveworkBook.Sheets(“Sheet1”).Range(“A1”) . Ultimately, it’s up to the programmer to decide which method is best for each situation.

You can find the last row and column in VBA by using the End command. To find the last row, use End(xlDown). This will take you to the last row containing data in your spreadsheet. To find the last column, use End(xlToRight). This will take you to the last column containing data in your spreadsheet. You can also use these commands to find the last cell in your spreadsheet.

Simply use End(xlUp) for the last row and End(xlToLeft) for the last column. Remember that these commands only work if your data is contiguous. If there are blank rows or columns in your data range, these commands will not work correctly.

A triangle is a three-sided polygon, and its area can be calculated using the following formula: Area = (1/2) * base * height. In Excel, you can create a user-defined function to calculate the area of a triangle. To do this, open the Visual Basic Editor (VBE) by pressing Alt+F11 on your keyboard. Then, in the Code window, enter the following code:

Function TriangleArea(base, height)
 TriangleArea = (1 / 2) * base * height
End Function

This code defines a function named TriangleArea that calculates the area of a triangle based on the values of the base and height arguments. To use this function in a worksheet, enter the following formula in a cell:

=TriangleArea(base, height)

Replace base and height with the cell references for those values. When you press Enter, the formula will return the area of the triangle.