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.

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.

Online MCQs MS Excel with answers

1. The interface provided by a matrix of rows and columns for manipulation with numeric data is called.

 
 
 
 

2. A cell is defined as

 
 
 
 

3. 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?

 
 
 
 

4. _________ is the correct syntax of the IF() Function.

 
 
 
 

5. Which option allows you to display all numbers that are less than 0 (negative numbers) in red color in MS Excel?

 
 
 
 

6. ____ happens when you select a cell in MS Excel and type “=B25”

 
 
 
 

7. 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?

 
 
 
 

8. _____ Excel function returns TRUE or FALSE based on two or more conditions

 
 
 
 

9. ____ type of chart is good for a single series of data.

 
 
 
 

10. 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.

 
 

11. 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?

 
 
 
 

12. If cells A1 and A2 are empty, what will be the value of the following Excel Formula? =A1 * A2

 
 
 
 

13. The VALUE function converts a numeric value into a text string in a spreadsheet.

 
 

14. 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.

 
 
 
 

15. A data analyst creates an absolute reference around a function array. What is the purpose of the absolute reference?

 
 
 
 

16. Which symbol is used to specify the fixed columns or rows in the formula?

 
 
 
 

17. 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?

 
 
 
 

18. What happens when you click on CTRL + X after selecting some cells in Excel?

 
 
 
 

19. What is an Excel feature that displays only the data in column (s) according to specified criteria?

 
 
 
 

20. ____ is not a function in Excel.

 
 
 
 

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

Writing Excel Formulas (2016)

by

Writing Excel formulas is a little different than the way it is done in mathematics class. All Excel formulas start with an equal sign (=), that is, the equal sign always goes in that cell where you want the answer to appear from the formula. Therefore, the equal sign informs Excel that this is a formula not just a name or number. Let us start with writing Excel Formulas.

The Excel formula looks like

= 3 + 2

rather than

3+2 =

Writing Excel Formulas and Cell References in MS Excel

The example of a formula has one drawback. If you want to change the number being calculated (3, and 2), you need to edit it or re-write the formula. A better way is to write formulas in such a way that you can change the numbers without changing or re-writing the formulas themselves. To do this, cell references are used, which tells Excel that data/ numbers are located in a cell. Therefore a cell’s location/ reference in the spreadsheet is referred to as its cell reference.

To find a cell reference, simply click the cell of which you need cell reference, and from NAME BOX (shown in the figure below), see the text, such as F2.

Writing Excel formulas 1

F2 represents the cell in the $F$ column (horizontal position) and row 2 (vertical position). It means cell reference can also be found by reading the column heading (at the topmost position) of the cells and row number (at the leftmost position). Therefore, cell reference is a combination of the column letter and row number such as A1, B2, Z5, and A106, etc. For the previous formula example, instead of writing = 3 + 2 in cell suppose (C1), follow this way of cell reference and formula writing:

In cell A1 write 3, and in cell B2 write 2. In the C1 cell write the formula such as,

= A1 + A2
Excel Formula 2

Note that there is no gap between A & 1 and A & 2, they are simply A1 and A2. See the diagram for a clear understanding.

Updating/ Writing Excel Formulas

Upon wrong cell references in the Excel formula, the results from the formula will be automatically updated, whenever the data value in relevant cells is changed. For example, if you want to change data in cell A1 to 8 instead of 3, you only need to change the content of A1. The result of the formula in cell C1 will automatically be updated after the updation of the data value in A1 or B1.

Note that the formula will not change because the cell references are being used instead of data values or numbers.

Data Analysis in R Language

Read more about Creating Formulas in MS Excel and Operator Order of Precedence