Microsoft Excel

Use of Statistics and Data Analysis in Microsoft Excel, use of Excel Built-in Statistical Functions, Analysis ToolPak, and creating Sheets for different statistical Analyses and Tests.

MS Excel Quiz Questions 4

by

The post is about MS Excel Quiz Questions. It contains 20 multiple-choice questions covering the basics of MS Excel, filtering and sorting in MS Excel, Cell Reference, If condition, Hlookup, vlookup, xlookup, and text formatting. Let us start with the MS Excel Quiz Questions now.

Online MCQs about MS Excel quiz questions with Answers

1. How is a cell reference made as absolute in a formula?

 
 
 
 

2. What happens when one uses the median calculation but selects an even number of values in a range?

 
 
 
 

3. What should you do if you have data in MS Excel that is in all upper-case letters and want to change it so the first letter of each word is capitalized?

 
 
 
 

4. After filtering a column and getting the results, in which two ways can you return to showing all the data in a column? Select two answers.

 
 
 
 

5. What is the first thing you should do when checking spelling errors in Excel?

 
 
 
 

6. If you have multiple filters set, how can you clear all of them at once?

 
 
 
 

7. Formula errors in Excel are preceded by a hash symbol (#). What does it mean when multiple hash symbols exist in a cell?

 
 
 
 

8. After enabling Filtering, where can one see and access the filter controls?

 
 
 
 

9. What is one of the easiest common errors or inconsistencies to fix when importing data?

 
 
 
 

10. In this HLOOKUP function, =HLOOKUP (B3, A2:B12,1), what does the number 1 indicate?

 
 
 
 

11. There are two methods to locate and remove duplicated rows in Excel, what is the easiest way?

 
 
 
 

12. What does VLOOKUP stand for?

 
 
 
 

13. What feature can you use to fix text that appears in mixed case?

 
 
 
 

14. Which statement best describes the purpose of filtering the data?

 
 
 
 

15. What is the purpose of the IFS function in Excel?

 
 
 
 

16. The IF function applies to one or two conditions, but what if one needs to apply multiple conditions?

 
 
 
 

17. How to remove all empty rows at the same time in an imported spreadsheet?

 
 
 
 

18. Which of the following formulas contains a mixed reference?

 
 
 
 

19. The difference between HLOOKUP, VLOOKUP, and XLOOKUP is how they look for data. How does each look for data?

 
 
 
 

20. When you use nested functions, what is required for each of the functions?

 
 
 
 

Online MS Excel Quiz Questions with Answers

MS Excel Quiz Questions with Answers

  • What happens when one uses the median calculation but selects an even number of values in a range?
  • How is a cell reference made as absolute in a formula?
  • Formula errors in Excel are preceded by a hash symbol (#). What does it mean when multiple hash symbols exist in a cell?
  • What is one of the easiest common errors or inconsistencies to fix when importing data?
  • What is the first thing you should do when checking spelling errors in Excel?
  • What feature can you use to fix text that appears in mixed case?
  • How to remove all empty rows at the same time in an imported spreadsheet?
  • What does VLOOKUP stand for?
  • After enabling Filtering, where can one see and access the filter controls?
  • The IF function applies to one or two conditions, but what if one needs to apply multiple conditions?
  • The difference between HLOOKUP, VLOOKUP, and XLOOKUP is how they look for data. How does each look for data?
  • Which of the following formulas contains a mixed reference?
  • What is the purpose of the IFS function in Excel?
  • In this HLOOKUP function, =HLOOKUP (B3, A2:B12,1), what does the number 1 indicate?
  • If you have multiple filters set, how can you clear all of them at once?
  • There are two methods to locate and remove duplicated rows in Excel, what is the easiest way?
  • Which statement best describes the purpose of filtering the data?
  • When you use nested functions, what is required for each of the functions?
  • After filtering a column and getting the results, in which two ways can you return to showing all the data in a column? Select two answers.
  • What should you do if you have data in MS Excel that is in all upper-case letters and want to change it so the first letter of each word is capitalized?

Exploring Data Distribution in R Language

Online MCQs about Computer

MCQs MS Excel 2

by

The post is about MCQs MS Excel which serves as a valuable resource for enhancing skills and preparing for exams such as MS-CIT, entrance exams, and university assessments. This quiz encompasses both fundamental and advanced aspects of MS Excel to assist professionals and students alike. Let us start with MCQs MS Excel.

Please go to MCQs MS Excel 2 to view the test

Microsoft Excel is a spreadsheet software that is an integral part of the MS Office software Package. A software that is capable of performing calculations from daily life to advanced level. One can perform calculations from basic mathematics to advanced including statistical and financial computations.

Online MCQs MS Excel

Schools, colleges, Universities, and Examination and Testing conducting authorities such as FPSC, NTS, KPPSC, PPSC SPSC, and others, are adding computer MCQs in their test patron to check CIT and computer-related knowledge of the applicants. MCQs MS Excel covers basic topics of Microsoft MS Excel, it especially focuses on MS Excel for statistical computations.

MCQs MS Excel

  • MS Excel function VLOOKUP can have problems when used on data values that have leading and trailing spaces. What function can be used to eliminate these spaces?
  • A function is created using data values from a specified array. It works correctly only some of the time. You verify that the function was used correctly and you ask a colleague for their input. They ask if you locked the data array. What does this mean?
  • A data analyst uses the SUM function to add together numbers from a spreadsheet. After getting a zero result, they realize the numbers are text. What function can they use to convert the text to a numeric value?
  • When using VLOOKUP, there are some common limitations that data analysts should be aware of. One of these limitations is that VLOOKUP can only return a value from the data to the left of the matched value.
  • A data analyst locks the rows and columns in their spreadsheet by wrapping their function’s data array in dollar signs ($). Why would they do this?
  • When writing a function, a data analyst wraps a table array in dollar signs. This is an __________, which is used to lock the array so rows and columns don’t change if the function is copied.
  • The VALUE function converts a numeric value into a text string in a spreadsheet.
  • A data analyst creates an absolute reference around a function array. What is the purpose of the absolute reference?
  • The interface provided by a matrix of rows and columns for manipulation with numeric data is called.
  • A cell is defined as
  • __________ is the correct syntax of the IF() Function.
  • __________ type of chart is good for a single series of data.
  • What is an Excel feature that displays only the data in column (s) according to specified criteria?
  • __________ is not a function in Excel.
  • Excel function returns TRUE or FALSE based on two or more conditions
  • What happens when you click on CTRL + X after selecting some cells in Excel?
  • __________ happens when you select a cell in MS Excel and type “=B25”
  • Which symbol is used to specify the fixed columns or rows in the formula?
  • Which option allows you to display all numbers that are less than 0 (negative numbers) in red color in MS Excel?
  • If cells A1 and A2 are empty, what will be the value of the following Excel Formula? =A1 * A2

Note that most of the questions in the quiz apply to almost all versions of MS Excel. The questions included in the MCQs MS Excel Quizzes are asked as part of the computer operation general knowledge section of the basic computer awareness test.

SPSS Analysis and Statistics for Data Analysts

R Programming

Online MCCQs Test Website

Operator order of precedence and Creating Formula in Excel

by

Creating Formula in Excel

Creating customized (user-defined) formulas in Microsoft Excel is not too difficult. For creating formulas just combine the references of your data with the correct mathematical operator (such as -, +, /, *, and ^).

Operator Order of Precedence

The order of mathematical operations determines in which order the mathematical operations are carried out. If more than mathematical operators are used in the formula, there is a specific order (sequence) that Microsoft Excel will follow to perform (compute) these mathematical operations. However, to change the order of operations, brackets (parenthesis) are used in the Excel formula. The easy way to remember the order of operations (precedence) is to remember the acronym: BEDMAS (PEDMAS), that i.e.,

The Operator order of precedence (operations) is:

Bracket or Parenthesis
Exponents (^)
Division (/)
Multiplication (*)
Addition (+)
Subtraction (-)

Suppose, the following is the screenshot of an Excel sheet. The formula is also shown in the formula bar. As an example, addition (+), division (/), and multiplication (*) operators are used.

Operator order of precedence

The formula in the screenshot performs the computation in the following order,

  • $E1/F1$ will be computed (the answer is 1.5),
  • the answer of $E1/F1$ will be multiplied by value of $G1$ (answer is $1.5*2 = 3$)
  • the answer of $E1/F1 * G1$ will be added to $D1$ (answer is 7)

Any operation(s) enclosed in brackets (parenthesis) will be carried out first followed by any exponents. After that, Excel will consider division or multiplication operations to be of equal importance. The operations are performed in the order they occur left to right in the formula. A similar sequence is also performed for addition and subtraction. Both (addition and subtraction) are considered equal in the order of operations. The operator that appears first will be computed first.

order of precedence bracket

For example, see the screenshot The sequence of operations is

  • The first bracket will be computed, that is, multiplication will be performed $(2 *2 = 4)$
  • E1 will be divided by the answer from the multiplication of $F1$ and $G1 (3/4 = 0.75)$
  • Lastly, $D1$ will be added to the answer $0.75 (4 + 0.75 = 4.75)$

Now check the sequence in the following screenshot

order of precedence bracket

For Creating formula in Excel, see the link Creating Excel Formula

Learn R Programming

Online MCQs Test Website

Creating Formulas in MS Excel and Changing Data

by

Changing the data

Before Creating formulas in MS Excel, you need numeric data in different columns or rows of the Excel sheet. Suppose you want to enter a few numbers in a column. Before entering these numbers, you should confirm the cell reference where you need to enter the data. Let’s start by entering numbers in Microsoft Excel‘s cells $A1$ and $A2$. For this purpose follow the steps given below

  1. Click on the cell $A1$
  2. Type 3 from the keyboard
  3. Press the ENTER or DOWN ARROW key on the keyboard. You will be in Cell A2
  4. Now type say 2 from the keyboard and press the ENTER key

Suppose you want to add these numbers in Cell $C1$. You need to write a formula in cell $C1$. After writing the correct formula the content of Cell $C1$ will immediately change to adding two numbers typed in $A1$ and $A2$ and used in $C1$ as formula content.

excel-data-and-formula

Creating Formulas in MS Excel

In this section, we will learn about Creating Formulas in MS Excel. In Excel, each formula begins with an equal sign ($=$), see the picture below

Creating Formulas in MS Excel

Therefore, when creating formulas in Excel, ALWAYS start by typing the equal sign. The equal sign is typed in the Cell where you want the answer to appear. Like the image above, follow these steps

  1. Click on cell $C1$ with ARROW keys from the keyboard or with the mouse pointer.
  2. Type the equal sign in cell $C1$.
excel-data-and-formula

After typing the equal sign in step 2, you have two choices for adding cell references to the spreadsheet formula. Note that cell reference is the name of the cell you want to use in the formula. $A1$ and $A2$ are cell references of numbers 3 and 2, respectively.

  1. You can type these references in or,
  2. You can use an Excel feature called Pointing
excel-data-and-formula

Pointing allows you to click with your mouse on the cell that contains the data or approach a cell reference using the keyboard ARROW keys containing your data to add. This will add cell reference to the formula.

After typing an equal sign in cell C3 in step 2:

  1. Click on cell $A1$ with the mouse pointer to enter the cell reference into the formula
  2. Type a plus (+) sign. You can also use other operators such as for multiplication use you have to use the * symbol, for division/symbol, and for subtraction use $–$, etc.
  3. Click on cell $A2$ with the mouse pointer to enter the cell reference into the formula
  4. Press the ENTER key on the keyboard

The answer 5 should appear in cell $C1$.

Note if you have more than one row or column of data then you need to perform calculations on each row or column cell. It is often possible to copy the first formula to other cells. The easiest way is to copy formulas with the file handle.

See also Creating Formulas in Microsoft Excel

Learn about Primary and Secondary Data

R Programming and Data Structure in R