Excel

Question 1

How can you search for a specific text in the whole workbook in Excel?

Answer 1

Use the “Ctrl + F” keyboard shortcut to open the Find and Replace dialog box, then select “Workbook” in the “Within” dropdown menu.

Question 2

Which of the following options correctly describes how to replace all occurrences of a specific text in Excel?

Answer 2

Click on the “Find and Select” button in the Home tab of the ribbon, select Replace, enter the text to be replaced and the replacement text, then click on “Replace All”.

Question 3

What is the correct way to quickly remove all comments from an Excel workbook?

Answer 3

Use the “Inspect Workbook” feature and click the “Remove All” button by comments’

Question 4

What happens to the data in merged cells in Excel?

Answer 4

The data from the upper-left cell is retained, and the data from the other cells is deleted.

Question 5

What is the purpose of the Format Painter tool in Excel?

Answer 5

To transfer the formatting from one cell to another cell or range of cells

Question 6

Which is a mix reference where with cell A2, the row is fixed but the column will adjust when the cell is copied or filled to other cells

Answer 6

A$2

Question 7

What’s the syntax to populate a new cell with the text from B2 followed by “ is the “ and the text from cell D2?

Answer 7

=CONCAT(B2,” is in the “,D2,” Department”)

Question 8

How Many Excel Data Formats Are Available? Name Them.
Six. They include:

Answer 8

Excel workbook: .xlsx
Excel macro-enabled workbook: .xlsm
Excel binary workbook: .xlsb
Template: .xltx
Template (code): .xltm
XML data: .xml

Question 9

What Does the Red Triangle in the Cell’s Upper Right Corner Indicate?

Answer 9

A red triangle in the upper right corner of a cell indicates that a cell comment has been attached to this cell. If you hover over the cell with your cursor, the comment will be displayed.

Question 10

Explain the Order of Operations Used in Excel.
Excel’s order of operations roughly follows the acronym PEMDAS (parentheses, exponents, multiplication, division, addition, subtraction).

Answer 10

() | Parentheses
: , | Reference operators
– | Negation
% | Percent
^ | Exponentiation
* / | Multiplication and Division
+ – | Addition and Subtraction
& | Concatenation
><= | Logical comparisons

Question 11

Is It Possible To Use Multiple Data Sources To Render PivotTables?

Answer 11

Yes, data can be imported from a variety of sources by accessing the Data tab and clicking Get External Data > From Other Sources. Excel worksheet data, data feeds, text files, and other such data formats can be imported, but you’ll need to create relationships between the imported tables and those in your worksheet before using them to create a pivot table.

Question 12

What Is VLOOKUP? What’s the main benefit of using XLOOKUP instead?

Answer 12

VLOOKUP is a predetermined function in Excel that allows the user to find data within a table corresponding to a particular row.

The VLOOKUP function can only move in one direction, from left to right. Therefore, the information you wish to seek out must be located in a column to the right of the lookup value’s location. With XLOOKUP, the data can be on either side.

Question 13

Is VLOOKUP Look Up Case-Sensitive ?

Answer 13

no

Question 14

What Is a PivotTable?

Answer 14

PivotTable brings together information from different locations in a workbook and displays it in a separate table. It can be used to highlight or easily compare data from different angles without the need to manually create extra tables.

Question 15

Assuming you have a table with product names in column A and their corresponding prices in column B, write and XLOOKUP statement to find the price for product “Apple”

Answer 15

=XLOOKUP(“Apple”, A1:B10, 2, FALSE)

Question 16

What is the difference between COUNT, COUNTA, COUNTBLANK and COUNTIF functions?

Answer 16

COUNT: Counts how many cells within a specified range contain numerical data. It will ignore (not count) any cells that are blank or contain text or symbols only.

COUNTA: This function counts how many cells within a specified range contain data of any type (the cell is filled, will not count empty cells).

COUNTBLANK: This function will count the number of blank cells within the designated range.

COUNTIF: This function will count only the cells whose value meets a certain condition specified by the user.

Question 17

How Do You Find Duplicates in a Column?

Answer 17

select the target range of data and navigate to the Style group on the Home tab and click the arrow next to Conditional Formatting. You will then be able to choose Highlight Cell Rules, Duplicate Values, and enter the values you wish to find duplicates of. This will highlight duplicates of the values you entered.

Question 18

What does the Text To Columns function do?

Answer 18

It allows you to split the contents of a cell or a range of cells into multiple columns based on a specified delimiter. Ex: “a b c” would be split into three cells with values “a”, “b”, “c” if the feature was used with a delimiter of “ “.

Question 19

What are the similarities/differences between the FIND and SEARCH functions?

Answer 19

FIND(find_text, within_text, [start_num])
SEARCH(find_text, within_text, [start_num])

Similarities:
Both are used to return the position of a specific character or substring within a text string. Both return #VALUE! error if: the value of the find_text argument is not found, the start_num argument is greater than the length of within_text, or Start_num is equal to or less than zero.

Differences:
FIND is case sensitive, SEARCH is not
FIND doesn’t allow the use of wildcards, SEARCH does