# Quia - Excel Lesson 4 Review (2023)

AB
All formulas begin with which character?=
In order to display all formulas in your worksheet, what do you do?Click the Show Formulas command button on the Formulas tab.
An equation that performs calculations on values in a worksheet is also known as a/an _______________.formula
A value is usually a number or which of the following?cell address
Calculation operators include which of the following?arithmetic, comparison, text concatenation, reference
In order to view the formula in the active cell, what do you do?Double-click the active cell.
Parentheses inside of parentheses are known as which of the following?nested
Which of the following describes the performance of the first calculation of a formula?on the innermost set of parentheses
Which calculation would be performed first in this formula? (((3+1)*((2+2)-2)*(1*1)))* (4+5)(2+2)
During formula modification, you can revert back to the original formula by pressing which key?Esc
When modifying a spreadsheet, to revert back after pressing the Enter key, what do you click?Undo button
Which of the following is the arithmetic operator for addition?+
Which of the following is the arithmetic operator for subtraction?-
Which of the following is the arithmetic operator for multiplication?*
Which of the following is the arithmetic operator for division?/
A/an _______________ is a number or text value that is entered directly into a formula.constant
A/an _______________ is a symbol or name that represents something else, such as a cell address or range of cells.variable
A/an ______________ is an equation that performs calculations on values in a worksheet.formula
In order to enter the editing mode and edit your function, which key do you press?F2
A cell’s location in the worksheet is known as the ________.cell reference
Any formula that includes cells whose values change should include which of the following?cell reference
When a cell identifier adjusts automatically if you make changes to the worksheet, it is called a/an ________.relative cell reference
Which of the following are method(s) of adding a cell reference into a formula?pressing the escape key, clicking on the desired cell, & typing
Which of the following is true of cells in a formula?They can be anywhere on a worksheet.
A specific cell or range of cells that does not change location regardless of the formulas location is known as a/an ________.absolute cell reference
A/An _________________ uses a specific column or row reference but not both.mixed cell reference
An absolute cell reference requires which symbol?\$
Which of the following is an absolute cell reference?\$A\$1
Which of the following is a mixed cell reference?A\$1
When copying a formula, which of the following apply?The relative reference automatically adjusts. & The absolute reference does not adjust.
Cell references in another workbook are considered ______________________.external references
When referencing data in another workbook, you must use which of the following under which conditions?square brackets ([]) and both workbooks must be open
Which of the following descriptors apply to cell ranges?contiguous, noncontiguous, groups of cells, sizes can be can be changed, can be named, & can be used in formulas
How can you view named ranges?by viewing the Name box next to the formula bar
Which of the following is true of naming a range of cells?It enables you to find the location quickly.
Which of the following is a method of naming a range of cells?On the Formulas tab, select Define Name.
Which of the following statements applies to named cell ranges?They cannot be included in a formula.
The Name Manager, where named cell ranges can be edited, is on which tab?Formulas
In the Paste Name dialog box, in order to add all of the named ranges and address data, click ____________.Paste List
Which is the formula to add all cells in a range called SALES?=Sum(Sales)
In the Defined Names group, you can add Named Ranges to formulas by selecting which of the following?Use in Formula
Which is the formula to multiply all cells in a range called DATA?=product(DATA)
Which of the following is not an arithmetic operator?]
In Excel, what is the result of =1 + 3 * 2 / 2 - 1?3
Per the order of operations, which of the following is calculated firstNegative number (-)
Which of the following refers to an unnamed range in the current worksheet?=SUM(C2:E12)
Which of the following shows a formula for a reference to another worksheet in the same workbook?=Q3Expenses!A19
Which of the following shows a formula for a reference to another workbook?=[Media.xlsx]MasterList!\$D\$10
Which of the following is an acceptable name for a named range?subtotal_west
Which of the following is an example of an absolute cell reference?\$A\$9
Which of the following is an example of a mixed cell reference?A\$9
Which of the following can you not do using the Name Manager?Enter values into a range
To allow Excel to distinguish formulas from data, all formulas begin with an equal sign (=).True
Regarding a named range, the scope of a name is the location within which Excel recognizes the name without qualification.True
Excel recognizes a construct like 3+4= as a legitimate formula.False
Range names may begin with the caret (^) character.False
You cannot use a named range in a formula that references another worksheet.False
Range names cannot be the same as a cell reference, such as C10 or \$D\$8True
Once you name a range, you can change the size of the range using the Name Manager.True
You can create a new range by selecting the cells and typing a name in the Name box next to the formula bar.True
The order of operations determines which parts of a formula are calculated before other parts of the formula.True
The formula = 6 * 2 / 3 produces the same result as =6 * (2 / 3).True

## FAQs

### What is the first character that must be typed into a cell if one wants to add a formula to that cell? ›

All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.

What is an acceptable name for a named range? ›

Regarding a named range, the scope of a name is the location within which Excel recognizes the name without qualification. Excel recognizes a construct like 3+4= as a legitimate formula. Range names may begin with the caret (^) character. You cannot use a named range in a formula that references another worksheet.

Is the location within which Excel recognizes the name without qualification? ›

The scope of a name

All names have a scope, either to a specific worksheet (also called the local worksheet level) or to the entire workbook (also called the global workbook level). The scope of a name is the location within which the name is recognized without qualification.

Which command enables you to automatically assign range names to a block of cells? ›

Ctrl + Shift + F3 to create named ranges from selection. F3 to get a list of all Excel names in a workbook.

How do you check if a cell starts with a number in Excel? ›

The following formulas can be used to check if the first character of a cell is number or a letter.
1. =IF(ISERR(LEFT(cell_location,1)*1),"Letter”, “Number") In this formula, we are calculating the value of a cell's left most character multiplied by 1. ...
2. =IF(ISNUMBER(VALUE(LEFT(cell_location, 1))),"Number", "Letter")
Sep 16, 2022

How do you check if a character in a string is a number in Excel? ›

How to use ISNUMBER in Excel? We use the ISNUMBER function to test if a value is a number. It will return “true” when the value is numeric and “false” when non-numeric. Let us consider the syntax “=ISNUMBER(G1).” It returns “true” if the argument “G1” contains a number or a formula that returns a numeric value.

What character is not allowed in range names? ›

The name string must begin with a letter or underscore character. Remaining characters in the name can be letters, numbers, periods, and underscore characters. Spaces are not allowed, but two words can be joined with an underscore (_) or period (.).

What is not allowed in names of ranges? ›

Named ranges can include numbers but cannot include any spaces.

What are the rules for naming tables in Excel? ›

Rules for Naming Tables in Excel

The name must begin with a letter, underscore, or backslash. The remaining characters can be letters, numbers, underscores, or periods. You can't use the names “C”, “c”, “R”, or “r” as these are designated shortcuts in Excel.

What is not allowed in range name in Excel? ›

The following are not allowed: Space characters are not allowed as part of a name. Names can't look like cell addresses, such as A\$35 or R2D2. C, c, R, r -- can't be used as names -- Excel uses them as selection shortcuts.

### What 3 types of data can be entered in a spreadsheet? ›

The three types of data you can enter into a cell are data, labels and formulas.

What is a hidden name in Excel? ›

The Document Inspector found hidden names in your workbook. These names can store hidden information about Solver scenarios. For example, when you use the Solver add-in to run a scenario, it may store information about calculation parameters and other sensitive data as hidden names in your workbook.

How do I categorize data into groups in Excel? ›

Select the data (including any summary rows or columns). On the Data tab, in the Outline group, click Group > Group Rows or Group Columns. Optionally, if you want to outline an inner, nested group — select the rows or columns within the outlined data range, and repeat step 3.

What do blue row numbers indicate in Excel? ›

When the row numbers in your worksheet turn blue, this indicates that some rows are filtered out. To unhide such rows, simply remove all filters on a sheet. This is how you hide and undie rows in Excel.

Which Excel functions can split text? ›

You can use the LEFT, MID, RIGHT, SEARCH, and LEN text functions to manipulate strings of text in your data. For example, you can distribute the first, middle, and last names from a single cell into three separate columns.

How do you use if cell contains text then formula in Excel? ›

To check if a cell contains text, select the output cell, and use the following formula: =IF(ISTEXT(cell), value_to_return, ""). For our example, the cell we want to check is A2, and the return value will be Yes. In this scenario, you'd change the formula to =IF(ISTEXT(A2), "Yes", "").

What is Vlookup command in Excel? ›

In its simplest form, the VLOOKUP function says: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

How to do a VLOOKUP step by step? ›

1. In the Formula Bar, type =VLOOKUP().
2. In the parentheses, enter your lookup value, followed by a comma. ...
3. Enter your table array or lookup table, the range of data you want to search, and a comma: (H2,B3:F25,
4. Enter column index number. ...
5. Enter the range lookup value, either TRUE or FALSE.

How do you pull specific text from a cell in Excel? ›

How to extract a substring in Excel
1. Use the LEFT, RIGHT and MID functions. You can use the LEFT, RIGHT and MID functions to extract specific text from a cell. ...
2. Use the TRIM function. Sometimes, instead of wanting to remove text from a string, you want to remove spaces. ...
3. Use the MID and FIND functions. ...
4. Use Flash Fill.
Feb 3, 2023

How do I find hidden special characters in Excel? ›

Office button --> excel options --> Advance --> under the display tick show control characters.

### What's the best way to repeat row and column headings when printing a document? ›

Print row or column titles on every page

On the Page Layout tab, in the Page Setup group, click Page Setup. Under Print Titles, click in Rows to repeat at top or Columns to repeat at left and select the column or row that contains the titles you want to repeat. Click OK. On the File menu, click Print.

What is ribbon in Excel interface? ›

The ribbon is a set of toolbars at the top of the window in Office programs designed to help you quickly find the commands that you need to complete a task.

Which cell format is used by default in Excel? ›

By default, all worksheet cells are formatted with the General number format. With the General format, anything you type into the cell is usually left as-is. For example, if you type 36526 into a cell and then press ENTER, the cell contents are displayed as 36526.

How do I create conditional formatting in Excel? ›

Select the range of cells, the table, or the whole sheet that you want to apply conditional formatting to. On the Home tab, click Conditional Formatting. Click New Rule. Select a style, for example, 3-Color Scale, select the conditions that you want, and then click OK.

What are the letters at the top of a spreadsheet called? ›

These letters and numbers are called row and column headings. To refer to a cell, type the column letter followed by the row number.

Can you name a group in Excel? ›

When you group columns in Excel, you have the option of giving the group a name. This can be helpful if you want to be able to easily identify the group later on. To name your group, simply type the name you want to use into the "Name" field under "Group Properties" and then click "OK."

What is considered good practice when naming worksheets in Excel? ›

Important: Worksheet names cannot:
• Be blank .
• Contain more than 31 characters.
• Contain any of the following characters: / \ ? * : [ ] ...
• Begin or end with an apostrophe ('), but they can be used in between text or numbers in a name.
• Be named "History". This is a reserved word Excel uses internally.

What are the two basic rules to give table name? ›

The rules for naming database objects (such as tables, columns, views, and database procedures) are as follows: Names can contain only alphanumeric characters and must begin with an alphabetic character or an underscore (_). Database names must begin with an alphabetic character, and cannot begin with an underscore.

How do you lock a cell? ›

Lock cells to protect them
1. Select the cells you want to lock.
2. On the Home tab, in the Alignment group, click the small arrow to open the Format Cells popup window.
3. On the Protection tab, select the Locked check box, and then click OK to close the popup.

What is difference between function and formula in Excel? ›

Formulas are defined as a statement written by any user, either simple or complex whereas a function is a type of formula which is pre-defined. On the other hand, functions are predefined formulas that are already there in the sheet.

### What is the easiest way to name a range in Excel? ›

Create a named range from selected cells in a worksheet
1. Select the range you want to name, including the row or column labels.
2. Click Formulas > Create from Selection.
3. In the Create Names from Selection dialog box, select the checkbox (es) depending on the location of your row/column header. ...
4. Click OK.

What are the 4 major parts of Excel? ›

• Worksheet - rectangular grid of rows (numbers) and columns (letters)
• Cell - intersection of row and column.
• Cell reference - unique address, coordinates of the intersection of a column and row (B7)
• Gridlines - horizontal and vertical lines.

What must all formulas begin with? ›

A formula always begins with an equal sign (=).

What two keys can you use to move within a spreadsheet? ›

Keyboard shortcuts for navigating in cells
To do thisPress
Move one cell up in a worksheet.Up arrow key
Move one cell down in a worksheet.Down arrow key
Move one cell left in a worksheet.Left arrow key
Move one cell right in a worksheet.Right arrow key
20 more rows

Can Excel pick a random name? ›

Excel is a powerful tool for selecting random names. Whether you prefer to assign a random number to each name or use the RANDBETWEEN function, Excel makes it easy to select a random name from a list quickly and easily.

How do I check if a name is true or false in Excel? ›

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. For example: =IF(A2>B2,"Over Budget","OK") =IF(A2=B2,B4-A4,"")

What is pivot in Excel? ›

A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. A PivotTable is especially designed for: Querying large amounts of data in many user-friendly ways.

How do I group duplicates in Excel? ›

How to combine duplicate rows in Excel
1. Start Merge Duplicates.
2. Step 1: Select your table.
3. Step 2: Choose key columns with duplicate records.
4. Step 3: Pick columns with the values to merge.

What is the purple mark in Excel? ›

A purple triangle in the lower-right corner of a cell indicates that a smart tag is in the cell. If you rest the mouse pointer over the triangle, the Smart Tag Actions button appears. Click the arrow next to the button for a list of smart tag options.

What do red numbers mean in Excel? ›

Excel has some inbuilt number formats that make it super easy to make negative numbers red in Excel. When you apply the 'Number' format, it adds two decimals to the numbers and makes the negative numbers show up in red.

### What is the blue dot in the corner of an Excel cell? ›

The mark is used to resize the table comprising the data, though resizing automatically occurs when data are added to a cell immediately below or to the right of the table.

What does spill mean in Excel? ›

#SPILL errors are returned when a formula returns multiple results, and Excel cannot return the results to the grid.

What is a delimiter in Excel? ›

A delimiter is the symbol or space which separates the data you wish to split. For example, if your column reads “Smith, John” you would select “Comma” as your delimiter. Select the delimiter within your data.

What is the first character when typing a formula? ›

A formula in Excel always begins with an equal sign (=). Excel interprets the characters that follow the equal sign as a formula.

When starting a formula What is the first thing you type in the cell? ›

Simple formulas always start with an equal sign (=), followed by constants that are numeric values and calculation operators such as plus (+), minus (-), asterisk(*), or forward slash (/) signs.

What is the first character function in Excel? ›

LEFT returns the first character or characters in a text string, based on the number of characters you specify. LEFTB returns the first character or characters in a text string, based on the number of bytes you specify.

What are common errors in Excel? ›

• Multiple tables.
• Multiple tabs.
• Not filling in zeros.
• Using formatting to convey information.
• Using formatting to make the data sheet look pretty.
• Placing comments or units in cells.
• More than one piece of information in a cell.

What is the Bodmas rule in Excel? ›

Rules For Using Formulas

Excel follows the BODMAS rules of arithmetic to determine the order in which calculations in any given formula are performed. The order is – Brackets Of, then Division, then Multiplication, then Addition, then Subtraction.

What is the correct order of operations for entering formulas into Excel? ›

When evaluating a formula, Excel follows a standard math protocol called "order of operations". In general, Excel's order of operation follows the acronym PEMDAS (Parentheses, Exponents, Multiplication, Division, Addition, Subtraction) but with some customization to handle the formula syntax in a spreadsheet.

What is the most common task performed with Excel? ›

SUM functions. Probably the most frequently used function in Excel (or any other spreadsheet program), =SUM does just that: It sums a column, row, or range of numbers—but it doesn't just sum. It also subtracts, multiplies, divides, and uses any of the comparison operators to return a result of 1 (true) or 0 (false).

### What does '!' Mean in Excel formula? ›

Beta. The exclamation mark (!) in an Excel formula is used to separate the sheet name from the cell reference. For example, if.

What are basic formulas in Excel? ›

Simple formulas always start with an equal sign (=), followed by constants that are numeric values and calculation operators such as plus (+), minus (-), asterisk(*), or forward slash (/) signs. Let's take an example of a simple formula. On the worksheet, click the cell in which you want to enter the formula.

How do I merge columns in Excel without losing data? ›

Merge Cells in Excel Without Losing the Data

If you don't want any separator, you can simply leave it out and use the formula =CONCATENATE(A1,B1). Alternatively, you can use any other separator such as comma or semi-colon. This result of the CONCATENATE function is in a different cell (in C1).

How do I insert a word in an Excel cell? ›

Click inside the cell of the spreadsheet where you want to insert the object. On the Insert tab, in the Text group, click Object. Click the Create from File tab. Click Browse, and select the file you want to insert.

Top Articles
Latest Posts
Article information

Last Updated: 26/10/2023

Views: 6286

Rating: 4.4 / 5 (65 voted)

Author information