Excel 1- 10

Characters to display specific formats (10) , (comma)

A comma is used to scale down a number by a factor of 1000 if it follows a zero.

Characters to display specific formats (10) , (comma)

A comma is used to separate thousands if it precedes a # sign

Characters to display specific formats (10) . (period)

A period separates decimals from the integer portion of the number. The number of zeroes determines the number of decimal places displayed.

* Columns (01) The first column is column A. The 27th column is ____, the next column is AB and so forth. The last column is ______ by letter and ______ by number

AA XFD 16,384

*NAVIGATING IN EXCEL (04) Pressing the _______ key combination can take you from one opened Application to another.

ALT+TAB

4) If there is a formula" =Sum (B3:B8)" in cell B9, what is the formula when you copy it into B12? a. =Sum (B3:B8) b. =Sum (B6:B11) c. =Sum (B6:B8) d. =Sum (B3:B11)

B

5) In a formula, how do we freeze a row or column reference? a. We prefix the appropriate row or column reference with a pound (#) sign b. We use parentheses with the appropriate row or column reference c. We prefix the appropriate row or column reference with a dollar sign d. The formula cannot be adjusted

C

* SELECTING ONE OR MORE CELLS （04） To select such non-contiguous ranges, keep pressing on the ____ key while mousing ______ on desired cells (or ranges of cells).

CTRL click

* NAVIGATING IN EXCEL (04) Ctrl + other keys Press Ctrl and pressed ____ twice, it selects all cells beyond the first range with data.

CTRL + a

NAVIGATING IN EXCEL (04) Ctrl + other keys Selected range bold or un-bold

CTRL + b

NAVIGATING IN EXCEL (04) Ctrl + other keys Copy data

CTRL + c

* NAVIGATING IN EXCEL (04) Ctrl + other keys Copies data from the cell above to the current cell highlight.

CTRL + d

NAVIGATING IN EXCEL (04) Ctrl + other keys Find data

CTRL + f

* NAVIGATING IN EXCEL (04) Ctrl + other keys Identify a particular cell or cells ( serves the same function as F5)

CTRL + g

* NAVIGATING IN EXCEL (04) Ctrl + other keys Find and replace

CTRL + h

NAVIGATING IN EXCEL (04) Ctrl + other keys inserts current time

CTRL+ :

NAVIGATING IN EXCEL (04) Ctrl + other keys inserts current date

CTRL+ ;

* NAVIGATING IN EXCEL (04) Ctrl + other keys Toggles between presenting values and presenting the corresponding formulas

CTRL+ ~

* NAVIGATING IN EXCEL (04) Ctrl + other keys Hides the active column

CTRL+0

* NAVIGATING IN EXCEL (04) Ctrl + other keys Opens the Format Cells dialog box.

CTRL+1

* NAVIGATING IN EXCEL (04) Ctrl + other keys Hides the active row

CTRL+9

NAVIGATING IN EXCEL (04) If you have more than one workbook open, you can move or navigate from one WORKBOOK to another by pressing ______.

CTRL+F6

* NAVIGATING IN EXCEL (04) Go to cell A1, press ______. Go to the last cell where contains data* press _______. A single space in a cell counts as _____.

CTRL+HOME / CTRL+END data

* SELECTING ONE OR MORE CELLS （04） To select an entire worksheet, Keyboard: ________________ Mouse: Click the_____ at the top left of the worksheet.

CTRL+SHIFT+SPACEBAR square

* SELECTING ONE OR MORE CELLS （04） To select an entire column, the keyboard: ___________ will select the active column. Mouse: Click the _____ letter (A, B etc.)

CTRL+SPACEBAR column

NAVIGATING IN EXCEL (04) Ctrl + other keys Selected range in italics or not in italics

CTRL+i

NAVIGATING IN EXCEL (04) Ctrl + other keys Opens the hyperlink dialog box

CTRL+k

NAVIGATING IN EXCEL (04) Ctrl + other keys Opens a file

CTRL+o

NAVIGATING IN EXCEL (04) Ctrl + other keys Opens the print dialog box

CTRL+p

NAVIGATING IN EXCEL (04) Ctrl + other keys Save the active workbook

CTRL+s

NAVIGATING IN EXCEL (04) Ctrl + other keys Underlines selection

CTRL+u

* NAVIGATING IN EXCEL (04) Ctrl + other keys Paste anything copied to clipboard

CTRL+v

* NAVIGATING IN EXCEL (04) Ctrl + other keys Closes the current workbook

CTRL+w

NAVIGATING IN EXCEL (04) Ctrl + other keys Redo an action

CTRL+y

NAVIGATING IN EXCEL (04) Ctrl + other keys Undo an action

CTRL+z

NAVIGATING IN EXCEL (04) Ctrl + other keys Creates a new workbook

CTRL=n

The ways to apply the format: C__________ Formating Format P________ C__________ P________

Conditional Formatting, Format Painter Copy Paste-Special

* Giving Range Names to Multiple Cells (08) Excel accomplishes the task of ranging each cell by giving a name to each row (from the labels in the left column) or by giving a name to each Column (from the labels in the top row). When both row labels and column labels are available, Excel uses the intersection of the row and column to identify the intersecting cell.

Mouse: FORMULAS ribbon Defined Names group Create from Selection Check on Top Row and Left Column. Click OK.

DISPLAYING DATA - FREEZING OR SPLITTING A SCREEN (05) To split a row:

Mouse: VIEW ribbon Window group Split

* 2) You are in cell J10. If you put =R[-5]C[-2]-3+R[6]C[2], this is equivalent to: a. =H5-3+L16 b. =H3-2+L12 c. =J3-5+K16 d. =J3-3+K16

a switch to R1C1 format > copy and paste the equation, then switch back to A1 format

Entering data directly in a worksheet (05) The convention is to execute operations in the following order: P E M D A S

Parentheses Exponents Multiplication Division, Addition Subtraction

LOCKING AND PROTECTING A WORKSHEET (09) We can also hide formula in a cell by clicking on the 'hidden' option in the ________ Tab in the '_______' command. Note that locking cells or hiding formulas have no effect until you protect the _____ (Review tab, Protect group, Protect Sheet button).

Protection/Format Cells worksheet

* Cells (01) In the R1C1 referencing system, a cell is addressed with the ____ number first, followed by the _______number. The cell 'K10' in the R1C1 format will be referred to as the ______ for row 10 and column 11.

R10C11

PROTECTING A WORKBOOK (09) Two other options: an option to make the file '____' and an option to make a ______ of the file. Making a file 'read-only' means that we cannot make changes to the file. When we open the file, we will be asked if we want to open the file as read-only.

Read-only back-up copy

* Minimizing the ribbon (02) Minimizing the ribbon can be accessed by the commands on the top right of the Excel window which is called _______. There are 3 commands to minimize the ribbons ______, ______, and ______.

Ribbon Display Options No ribbon Ribbon tabs only Ribbon tabs + commands

CUSTOMIZING THE QUICK ACCESS TOOLBAR (QAT) (02) The QAT can be positioned (位置） _____ or _____ of the ribbon. A user can ____ or _____a command from the existing QAT. The QAT can simultaneously have ____ commands from _____ ribbons.

above or below remove or add multiple different

Advantages of Range Names （08） 1. Range names are ______________references. We need not be concerned about using dollar signs to make our reference absolute. 2. a range name is absolute not only with reference to the row and the column but also with reference to the _________. 3. We can ______ data easily by referring to the range name. 4. By providing logical names to ranges, it is easy for us to use a value in a formula. For instance, we could call a cell having the value 5% "RateOfInterest". Then we can apply the "RateOfInterest" to our loan amount to calculate the interest amount. 5. Range names facilitate navigation. We can quickly* go to a named cell. (Pressing F5 or CTRL-G invokes the GoTo command and leads us to the named cell).

absolute worksheet update

R1C1 FORMAT FOR CELL REFERENCE (06) USE A1 format: A reference to the __________ and comes first 1 reference to the ________ comes the next. The formula looks like "=A2*B2"

column / row

Importing Data from another File (05) A delimiter may be used consecutively e.g., 10,,,,20 (the number 10 separated from number 20 by four commas). This may mean that the number 10 in one column is followed by three blank ______ and then by the number 20. It could also mean that the number 10 should be followed immediately by the number ___.

columns 10

THE RIBBON SYSTEM (02) The ribbon system refers to the collections (or strips) of _________ buttons. Each ribbon is headed by a _____ at the top of the workbook. Each ribbon group appropriates ______. When a workbook opens, the default tab is the _____ tab.

command tab functionalities HOME

Importing Data from another File (05) Delimited data are fields (or values in columns) separated by t____, s______, s_____, c______ or other characters. Fixed Width are fields that aligned in columns in the ____ width.

commas tabs semicolon space Same

Operations with a constant (10) Copy Paste-Special can also be used to add, subtract, multiply, or divide a range of values by a _______.

constant

Worksheet (01) The 'active worksheet' refers to the worksheet where the _____is. That is, if you were to enter data, it would be entered into the active worksheet in the active cell.

cursor

* THE RIBBON SYSTEM (02) Numerous ribbons have so many controls, it is difficult to remember each command and its location. We can make this easier for ourselves through _____.

customization

***3) What shows when you use the "split" command? a. All rows below and all columns to the left of the active cell b. All rows above and all columns to the right of the active cell c. All rows below and all columns to the right of the active cell d. All rows above and all columns to the left of the active cell

d

1) "Save As" dialog box leads us to options to: a. Create a password b. Make the file ''Read-Only" c. Make a back-up copy d. All of the above

d

1) Excel allows you to create which of the following types of series? a. Growth series b. Linear series c. Date series d. All of the above

d

1) What are the advantages of range names? a. Range names are absolute references b. We can update data easily by referring to the range name c. Range names facilitate navigation d. All of the above

d

2) In Excel, we can a. lock a cell b. protect a worksheet c. protect the entire workbook d. all above

d

2) The scope of range names can be set to: a. A worksheet b. A workbook c. Cannot be restricted d. Both A and B

d

2) Which of these statements about the Data Validation tool is FALSE? a. It can be used to enter information corresponding to specified lists. b. One can specify which dates can be entered in a cell. c. One can choose which error message is displayed in case of an invalid entry. d. It can be used to round numbers to a user-defined scale

d

2. Add-ins can be created by: a. Microsoft b. Third-party providers c. The user d. All of the above

d

3) A number format has how many parts? a. 1 b. 2 c. 3 d. 4

d

3) What functionality is available in Data Validation? a. Circle invalid data b. Provide an error message when invalid data is entered. c. Input Message to guide the user as to the type of data that is acceptable. d. All of the above are available

d

3) Which of the following is FALSE? a. When you protect a worksheet, adding a password is optional b. While protecting multiple worksheets, each worksheet will have to be protected separately c. When using the "always create a backup" option the backup file is generated in same folder as original file d. Using a password provides a high level of security in protecting a worksheet

d

3. In Excel, the text data type includes: a. Letters b. Numbers c. Symbols d. All of the above

d

4) The format to display 1234.59 as 1234.6 is: a. ####.#0 b. ####.-# c. ####.0# d. ####.#

d

4) What options can we specify while validating data in Excel? a. The kind of data to be entered (e.g., whole number, decimal, date or time) b. The values of data that are acceptable (e.g., length of text that should not be exceeded) c. Options to provide suggestions to the user to indicate acceptable data and the message to be shown to the user in case of invalid entry. d. All of the above

d

4. Which of the following is FALSE? a) QAT stands for 'Quick Access Toolbar' b) The default buttons on the QAT can be removed c) The QAT can be positioned above or below the ribbon d) Adding a command to the QAT changes the availability of the command in its original location

d

5) If I want to create a split view of my worksheet, and I want only a vertical split: a. The active cell must be anywhere in column A after row 1 b. The active cell must be anywhere in column A c. The active cell must be anywhere in row 1 d. The active cell must be anywhere in row 1 after column A

d

5) We can define a range name from which of the following? a. 'Name box' on the formula bar b. 'Create from selection' c. 'Define Name' d. All of the above

d

4. How many columns are there in one Excel 2013 worksheet? a. More than 19000 b. More than 18000 c. More than 17000 d. More than 16000

d a worksheet has 16,384 columns

* Importing Data from another File (05) We can get data into Excel from another electronic data source such as a ____, _____, or even the_______. Excel allows for obtaining data from a variety of sources.

database text file internet

* Number (03) A number can be presented in different ways: in _____, _____, and _____ notation, etc. in a ______ symbol.

decimals / fractions / scientific currency

* Removing a button from a ribbon (02) We cannot remove individual buttons from a ______ group in a ribbon. However, we can remove the ______ ____ group as well as individual buttons from a ________ group in a ribbon.

default (= built-in) whole default custom

Importing Data from another File (05) The characters used to separate columnar values by tabs, space, or columns are known as __________. (A legacy system stores this kind of data; older technologies).

delimiters

Characters to display specific formats (10) 0 (zero)

displays insignificant zeros if a number has fewer digits than there are zeros in the format

Characters to display specific formats (10) # Number Sign

displays only significant digits and does not display insignificant zeros ###, hide 1000

Workbook (01) An Excel file is a _____. A workbook is composed of one or more ______. The default name is _____ and it can be changed later while saving. Once the Excel application is opened, you can have s____ workbooks.

workbook / worksheets Book1 several

LOCKING AND PROTECTING A WORKSHEET (09) The password just protects the ______ to the worksheet but does not provide for any high degree of ________.

changes / security

Alerts the user in one of three ways （07） Show _______ message, when users want to continue with data that do not conform to the rules we specified. The user can choose to correct the data or to continue without corrections.

Warning

THE FILE SYSTEM IN EXCEL (03) Excel uses different file extensions to indicate different functionalities. xlsx is the default file extension for a ________ in Excel 2013.

Workbook

* CUSTOMIZING THE DEFAULT WORKBOOK (03) Any workbook that you placed in the _____ folder is opened when Excel ____.

XLSTART starts

* 3) One advantage of using keyboard short-cuts is that some of these same short-cuts are available in other applications. a. True b. False

a

1) How can we copy only the cell formatting (without the cell values)? a. Use Copy Paste-Special b. Copy and paste c. Either a or b d. Neither a nor b

a

1. Add-ins: a. Provide additional functionality to Excel b. Are programs without which Excel cannot do basic functions c. Both A and B are true d. Neither A nor B is true

a

2) It is possible to create your own keyboard short-cuts. a. True b. False

a

2) What formatting is helpful in highlighting a specific range of values? a. Conditional formatting b. Editing c. Alignment formatting d. Format Painter

a

3) Which of the following is a valid range name? a. JamesBond007 b. 007JamesBond c. James Bond 007 d. JamesBond 007

a

3. What does the command "Reset only selected ribbon tabs" accomplish? a) Removes all customizations in a selected ribbon b) Removes all customizations in all ribbons c) Removes all buttons in a ribbon d) Removes default groups in selected ribbon

a

5) To prevent modification of selected cells in a worksheet, you will need to: a. lock the selected cells and then protect the worksheet b. protect the workbook and then lock the cells c. protect the worksheet and then lock all cells d. lock all cells and then protect all worksheets

a

3. To what does "active worksheet" refer? a. The worksheet where the cursor is b. The first worksheet c. The worksheet with the most cells with data d. None of the above

a The 'active worksheet' refers to the worksheet where the cursor is.

* THE FILE SYSTEM IN EXCEL (03) In the file name "ThisFile.xlsx", the file name is ______ and the file extension is _____ . The file extension tells us what _____ of file it is. The file extension .xlsx indicates it is an ____ file.

'ThisFile' / '.xlsx' type / Excel

Importing Data from another File (05) If you want to ensure that the value '10' is imported into one column and the next value '20' comes in the immediate next column. What option should be used?

'Treat consecutive delimiters as one'.

Importing Data from another File (05) A file with values that are separated by commas is often referred to as a ____ file. Also, it refers to 'character separated values' as you can use characters other than a comma to separate one value from another.

'csv - comma separated values

DATA VALIDATION RULES USING A FORMULA (07) * The formula to prevent users from adding ANY space in a text string in cell A1 is:

=A1=SUBSTITUTE(A1," ","")

DATA VALIDATION RULES USING A FORMULA (07) The formula to require the user to enter only a text string that is exactly 4 characters long is:

=AND(ISTEXT(A1),LEN(A1)=4)

ENTERING FORMULAS AND FUNCTIONS (06) Excel indicates the respective sums for each row by using _______.

"ALT="

Rows (01) In Excel 2013, a worksheet has (how many) _____rows arranged horizontally, However, you can hide any row or multiple rows.

1,048,576

Customizing the ribbon (02) Two ways to access the options to customize a ribbon:

1. Right-click on any tab "Customize the Ribbon" 2. FILE > Options > "Customize Ribbon" 3. Right-click on any buttons "Customize the Ribbon"

Columns (01) In Excel 2013, a worksheet has (how many) _____ columns arranged vertically, The number of columns is ____ and cannot be reduced or increased. However, you can ____ any column or multiple columns. The convention in Excel 2013 is to represent columns by letters. The first column is column A. After 26 columns (A, B, C, D ...), the 27th column is AA, the next column is AB and so forth. After 702 columns (26 single letter columns plus 26x26 double letter columns), columns are lettered with three letters: AAA, AAB, etc. The last column - column 16,384 - is XFD.

16,384 fixed hide

Filling Data in a Series (05) Even numbers filling function: enter the starting value is 2, the step number is _____, and the stop number is _____.

2 / 24

DATA VALIDATION RULES USING A FORMULA (07) The formula to prevent users from adding a space before or after a string in cell A1 is =_____

=A1 = TRIM(A1)

Adding a custom tab or a custom group (02) How to add a tab or group into a ribbon?

> Right-click any tab > select "Customize the Ribbon" > right-click any tab (in the right window) > Choose Add New Tab or Add New Group as desired

COPY PASTE-SPECIAL (10) To apply Copy Paste-Special:

> Select the cell range for copy > Right click on selected cell range to copy the cell range > Select the cell range to apply > Click on Paste in Clipboard group > Paste Special > Formats to apply

***STARTING EXCEL QUICKLY (01) To create a shortcut in the taskbar To create a shortcut on the desktop To create a keyboard shortcut

> Type 'Excel' in search window >Right click on Excel > Click "Pin to Taskbar" Start Menu > Right-click Excel > Select "Send to" > Desktop > Click the Start button > Type 'Excel' in the search window > Position cursor on 'Excel 2013' and right click > Click on 'Properties' > In the properties dialog box, click on the shortcut tab > In the shortcut key box, press the desired key on the keyboard; > Keyboard shortcuts automatically start with 'Ctrl', or 'Ctrl' + 'Alt'; click OK If prompted for an administrator password or confirmation, type password or provide confirmation Make sure that the key combination used for creating the shortcut is not the same as any existing keyboard shortcut; in this case, the keyboard shortcut created will not work

* Displaying numbers along with text in a cell (10) We can use "@" symbol to combine numbers and text. For instance, we can type "Loss of "_____" in 2015", Then, when we type $1000 in the cell, it is formatted as "Loss of ______ in 2015". How to apply in command?

@ $1000 Home Cells Format Cells Custom Type: "Loss of "@" in 2015" Ok

3) What does an absolute reference do? a. The row and/or column reference will not change if the formula containing the reference is copied to another location b. The cell will always contain the absolute value of any number entered into it c. The cell reference changes if the formula containing the reference is copied to another location d. It is the only way a formula can be used to refer to a cell on another spreadsheet

A

* RECENTLY OPENED FILES (03) How to adjust the numbers of workbooks you wish to see? File Menu > Options > _____ > Display > yype the # in '____________' box.

Advanced 'Show this number of Recent Documents'

Adding an existing button to a tab (02) We can add a command/button that is available but not shown presently in a group. We can add it from the option of '__________'. However, Excel does not allow users to modify ______ groups in the ribbon.

Commands Not in the Ribbon built-in

NAVIGATING IN EXCEL (04) Ctrl + other keys Selects the entire range.

Ctrl +a

* NAVIGATING IN EXCEL (04) Ctrl + other keys Cuts the selected cells.

Ctrl+x

* Copying and Pasting Data (05) In cell A1, these ten values (2 3 4 5 6 7 8 9 10 11) are pasted in one cell and they are separated by spaces. To command Excel to put these values in separate columns:

DATA ribbon Data Tools group Text to Columns Delimited >Next Space > Next Format (Text or Date) or General if no specific format is required Finish

Importing Data from another File (05) Import data from a text file (e.g., with a file extension such as .txt or .csv). How to command Excel to get data from another file:

DATA ribbon Get External Data group From Text Text Import Wizard: Delimited or Fixed Width

Importing Data Using Query Editor (05) How to import data using Query Editor? We then select the column with the data and then use the "________" command.

Data Get Data Launch power Query Editor New Source File Txt/CSV LOAD

Data Validation option ( 07) Set a rule in Cell A1 that meets the criteria for a 9-character string beginning with "OPRE "

Data Ribbon-->Data Validation-->Settings-->Allow: Custom We would then enter - in the 'Formula' window: =AND(LEFT(A1, 5) ="OPRE ",LEN(A1) =9) This will ensure that the first five characters in the value entered in cell A1 are "OPRE " (OPRE followed by a space) and then followed by 4 characters. The length of the entire string is nine characters. (Note that we are not checking whether the four characters to the right are numbers.)

Date Formats (10) D Dd Ddd Dddd M Mm Mmm Mmmm Mmmmm Yy Yyyy

Day of the month in number (1 to 31) Day of the month in number (01 to 31) (two digits for the numbers less than 10). Day of the week in letter (the first three letters) e.g., Sun Day of the week in letter ( full letters ) e.g., Sunday Month in number (1 to 12) Month in number ( 01 to 12) (two digits for the numbers less than 10 such as January will be shown as 01) Month in letter (the first three letters) e.g., Jan Month (in full letters) e.g., January Month as J-D ( only the first letter) Year in number ( two digits of the year) Year in number ( four digits of the year)

Importing Data from another File (05) Two formats of importing data: _________ or ________.

Delimited Fixed Width

Time Formats (10) h hh [h] or [hh] m mm [m] or [mm] s ss

Displays the hour as a number from 0 to 23 只显示超过24以内的数字 Displays the hour as a two-digit number from 00 to 23 只显示超过24以内的数字 Displays the cumulative hours elapsed 显示累计总小时，而且只显示小时，不显示分钟 Displays the minutes as a number from 0 to 59. Note that this character only represents minutes if used within a complete time number format code 只显示59分钟以内 Displays the minutes as a number 00-59 and always displays it as a 2-digit number. Note that this character only represents minutes if used within a complete time number format code 只显示59分钟以内 Displays the cumulative minutes elapsed Displays the seconds as a number from 0-59 Displays the seconds as a two-digit number from 00-59

DATA VALIDATION RULES USING A FORMULA (07) built-in rules to validate data include： Whole number: Enter only whole numbers between a ___ and ____ Decimal: Enter decimal values between a ___ and ____ List: Enter values from a ______list Date: Enter dates between a____ and ____ date. Time: Enter dates between a ____ and ___ time Text Length: Enter text between ____ and ____ characters.

Enter only whole numbers between a minimum and a maximum Enter decimal values between a minimum and a maximum Enter values from a specified list Enter dates between a start date and an end date Enter dates between a start time and an end time Enter between a minimum and maximum number of characters

INTRODUCTION (05) There are many ways to get data into Excel. Some methods include:

Entering data directly in a worksheet Copying and pasting data Importing data from another file Filling data in a pattern Linking a worksheet to an internet source (not covered in this course)

Displaying Range Names (08) Once we have named ranges in our workbook, it would be helpful to list all the range names and the cells to which they refer. How to paste range names

FORMULAS ribbon Defined Names group Use in Formula Paste Names

PROTECTING A WORKBOOK (09) How to protect a workbook？

File Save As... Choose a location for the file, and enter a file name Tools General Options -->gives us the password options Save

SELECTING ONE OR MORE CELLS （04） * To select a group of cells with special characteristics using (what) ______ function. For instance, we can select all the cells that have comments. Keyboard:_________.

GoTo CTRL+G --> Special --> Comments

Number Formats (10) How to view the custom format?

HOME Ribbon > Cells Group > Format Cells > Number > Custom

CONDITIONAL FORMATTING (10) Conditional formatting refers to formatting one or more cells when a specified condition is true, such as greater, equal to or less than a given value; we can also choose the top10, or 1op 10% of the values and so forth. How to introduce a conditional format?

HOME ribbon Styles group Conditional Formatting Highlight Cells Rules Greater Than [desired value]

LOCKING AND PROTECTING A WORKSHEET (09) How to lock/unlock a worksheet?

HOME ribbon Cells group Format Format cells check or uncheck the 'Locked' field

LOCKING AND PROTECTING A WORKSHEET (09) How to protect a worksheet?

HOME ribbon Cells group Format Protect sheet Select Lock the cells or unlock the cells

Displaying numbers along with text in a cell (10) Using IF condition, apply one or more formatting for an entire worksheet or group of cells.

Home Cells Format Cells Custom Type: [<=70]0"is poor";[>=90]0"is excellent!";0"is good" Ok

Displaying numbers along with text in a cell (10) How to display both text and numbers in a cell? For example, to display a positive amount as "$100 Profits" and a negative amount as "-$100 Losses."

Home Cells Format Cells Number Custom Type: $0" Profits";-$0" Losses" Ok

CONDITIONAL FORMATTING (10) Use the New Formatting Rule to create a condition of our choice?

Home Ribbon Styles Group Conditional Formatting Manage Rules

Alerts the user in one of three ways (07) Show_______ message lets the user know that data does not conform to rules。

Information

* THE FILE SYSTEM IN EXCEL (03) xlam indicates that the file is a ______ _____ type of file. This add-in may be created by _____ company, _______ developers and _____. An add-in may have several ______ embedded within it.

Macro-enabled add-in Microsoft / third-party / user macros

THE FILE SYSTEM IN EXCEL (03) xlsm is the file extension for a workbook has _____. The file type is ______.

Macro-enabled workbook

Displaying Range Names (08) The simplest way to do this is to use the _____ and delete the range names.

Name Manager

* Removing a button from a ribbon (02) To remove all customizations made to the tabs, groups, and the Quick Access Toolbar from the ribbons, we can "__________" option.

Reset all customization

* Removing a button from a ribbon (02) To remove customizations in a ribbon tab in bulk, We can use "___________" option.

Reset only selected Ribbon tabs particular/bulk

* CUSTOMIZING THE QUICK ACCESS TOOLBAR (QAT) (02) To Add Commands to the QAT: To remove a command from the QAT:

Right-click on any command > Click on 'Add to Quick Access Toolbar' > On the QAT, right-click on the command > Click on 'Remove from Quick Access Toolbar'

* SELECTING ONE OR MORE CELLS （04） To select an entire row, Keyboard: __________ will select the active row. Mouse: Click the _____ number (1, 2, etc.)

SHIFT+SPACEBAR row

Importing Data Formatted as a Table Word (05) FINAL EXAM*** convert a Word document with a table to text, Step?

Select the table Table Tools dialog box will pop up Layout menu Convert to Text *先要convert THE TABLE TO A FORMAT WITHOUT A TABLE, THEN CONVERT TO A TXT. DOCUMENT. ***

CUSTOMIZING THE LIST OF PREVIOUSLY USED FILES (03) How to open the previously used files?

Start Excel. Click on the FILE menu. you will see a list of workbooks recently used. The last workbook used is on the top.

Using Paste Special to Skip Blanks in Microsoft Excel (10) Column B shows the original prices and Column C contains the updated prices. If there is no value is shown in Column C that means the price of the product remains unchanged. Now, you need to update the price information in Column B based on Column C. You can manually update the price for each product. Evidently, that is not an efficient way especially when you have a large number of items to be updated. If you copy the range C2:C17 and paste it on to B2:B17, the blank cells from Columns C will overwrite the values in Column B. Steps?

Step1: Select range C2: C17 Step2: Right click the range and select Copy Step 3: Select Cell B2 Step 4: Right click on B2 and select Paste Special Step 5: Click "OK"

Alerts the user in one of three ways （07） Show ______ message, when the values from being passed to the cell

Stop

Text (03) When the numbers are not meant to perform mathematical operations on a number, we are using that number as ______. E.g., the phone number is composed of several digits.

Text

1) In general, navigating with the mouse is faster than navigating with keyboard keys. a. True b. False

b

* Copying and Pasting Data (05) If you have multiple spaces separating two values, you need to check '_____________". This will ensure that Excel ignores consecutive separators. Otherwise, you will get a number of blank columns.

Treat consecutive delimiters as one

Entering data directly in a worksheet (05) Embedding a value in a formula is known as ____________ and can contribute to errors.

hardcoding

DATA VALIDATION Data validation helps us validate the _____of the data. Data validation helps us in the following ways: 1. When users enter the data, Data Validation guides the users in data ___ allowed, such as Whole Number, decimal, List, Date, Time, Text Length) 2. ____ that data entered conform to rules that we have specified( circle invalid data prior entered) 3. _____ the users. (eg. Stop, Warning, Information)

accuracy criteria/type Checks Alerts

SELECTING ONE OR MORE CELLS （04） Any data and that you enter is entered into the ____ cell. Similarly, any formatting you do is done to the _____ cell.

active / active

Entering data directly in a worksheet (05) If you need to refer to a value, you should refer to the cell ______ and not include the _____ as part of a formula.

address / value

* Characters to display specific formats (10) ? (question mark)

adds spaces for insignificant zeros on either side of the decimal point space # ?/?

Note that we can add buttons only _____ we have created a group.

after

Displaying Range Names (08) Use the 'Paste List' command _____ we have created all the required range names. Once a list is pasted, it does not _______ to reflect new range names or changes to existing range names.

after / update

APPLYING DATA VALIDATION RULES AFTER DATA ENTRY (07) The reason we need two steps is that if we create validation rules for a range of cells that already contain data, Excel does not ________ apply these rules to existing data. We need to select the command '_____' to check if existing data meet the new rules. When we invoke this command, Excel ______ invalid data with a _____ circle.

automatically Circle Invalid Data highlights red

* 4. Which of the following is the file extension for a macro-enabled workbook? a. Xlsx b. Xlsm c. Xls d. None of the above

b

** 2) A number that may be used in a formula should be: a. embedded (hard-coded) in the formula itself b. placed in an individual cell c. placed in a separate workbook d. entered manually every time a model is solved

b

1* . Which of the following statements is FALSE about the ribbon? a) Each ribbon can be customized by adding new groups of functions b) You can add a button to a default group c) Both A and B d) Neither A nor B

b

Remove button from a ribbon (02) We cannot remove _____ from the default groups in a ribbon. However, we can remove an entire default _____. We can also, remove individual buttons from a ______ group in a ribbon.

buttons group custom

1) In the R1C1 format, cell address XEV1048563 is: a. R1048563C15732 b. R1048563C16359 c. R1048563C16376 d. R1048563C15723

c

1) Which one of these is an acceptable data validation rule? a. To be valid, text has to be a specific length b. To be valid, a number has to be between 100 and 200 c. Both A and B are valid rules d. Neither A nor B is a valid rule

c

2. A custom group of commands can be: a) Created within an existing ribbon b) Added to a new ribbon c) Both A and B d) Neither A nor B

c

4) How would you give a name to each cell in a large number of cells? a. Name each cell individually b. Copy and paste the cell c. Select desired cells, formula->define names group->create from selection d. Select desired cells, right click->define names group->create from selection

c

4) The 'password protection feature' can be applied to which of the following? a. Worksheet only b. Workbook only c. Either worksheet or workbook d. Neither worksheet nor workbook

c

4) Which command is used to put delimited values in separate columns? a. Numbers to Columns b. Words to Column c. Text to Columns d. Data to Columns

c

5) If data is entered, and then data validation rules are created, then: a. Incorrect data is deleted automatically. b. Incorrect data is circled automatically. c. Nothing happens until we choose 'circle invalid data' d. The cell color changes to red to indicate wrong data

c

5) Which of the following statements is FALSE with respect to number formats? a. A number format can have up to four sections of code b. All four-code sections do not need to be included c. If you specify only one format code, it is used for text d. Text, in a code section, needs to be separated by double quotes

c

5. We can minimize the ribbon to show: a) No ribbon b) Ribbon tabs only c) Both A and B are correct d) Neither A nor B is correct

c

5. Which of the following is TRUE? a. Text can include numbers b. Within one cell, different parts of text can be presented in different formats c. Both A and B are true d. Neither A nor B is true

c

5. What column comes after column ABZ? a. ACZ b. BCZ c. ACA d. ACC

c The first column is column A. After 26 columns (A, B, C, D ...), the 27th column is AA, the next column is AB and so forth. After 702 columns (26 single letter columns plus 26x26 double letter columns), columns are lettered with three letters: AAA, AAB, etc. The last column - column 16,384 - is XFD.

2. Excel opens a new workbook with: a. 5 worksheets1 b. 6 worksheets c. As many sheets as are specified as a default value d. None of the above

c Excel opens a new workbook with the number of worksheets that has been specified as a default

* 1. The number of rows in a spreadsheet: a) Can be increased by inserting additional rows b) Can be increased up to limits provided by the computer's memory c) Cannot be increased d) Can be increased only if the number of columns is also increased

c The number of rows is fixed and cannot be reduced or increased. However, you can hide any row or multiple rows.

* SELECTING ONE OR MORE CELLS （04） You can click and drag a _____ to select a group of cells. Also, you can use a key combination: ___+___+____ Arrow from the current cell to the leftmost cell.

cell CTRL+SHIFT+LEFT ARROW

DISPLAYING DATA - FREEZING OR SPLITTING A SCREEN (05) When we select a _____ and freeze rows and columns, all rows ____ the active cell, and all columns to the _____ of the active cell is frozen.

cell above left

Range Names (08) In addition to providing an address for a cell, Excel gives us the option to provide a name to a ___ or a _____ of cells. The easiest way to give a name to a cell is to enter the name in the ______ in a worksheet.

cell* / group* Name Box

LOCKING AND PROTECTING A WORKSHEET (09) Protect a blank cell such that no one can enter data in that cell. Hence, it is more appropriate to speak about protecting a cell than about protecting data. Protecting a cell is a two-step process. The first step is to lock the ____or cells. The second step is to protect ______.

cell/worksheet

ABSOLUTE REFERENCING (06) To freeze the respective row or column, we prefix the appropriate row or column reference with a ____ sign in front of a row or column reference makes that reference an absolute one.

dollar

LOCKING AND PROTECTING A WORKSHEET (09) The 'protect sheet' feature is a worksheet feature. It means that _______ worksheet will have to be protected _____.

each/separately

Filling Data in a Series (05) We can use Excel's 'Fill Series' functionality to fill in data according to a pattern. For instance, if we wish to enter odd numbers from 1 to 25 in column one, we would: First,

enter the value 1 in cell A1. Then: HOME ribbon Editing group Fill Series Enter a starting value in a cell Click Columns Click Linear Show Step value as 2 Stop value as 25 Click OK

ENTERING FORMULAS AND FUNCTIONS (06) In Excel, you can use formulas and functions to work with data. We start a formula or a function with an ____ to sign.

equal '='

Data validation is more helpful with regard to catching ______ of data type rather than data accuracy. For instance, if you type a number for age in a column for first name, this error can be caught. However, if you type the wrong name in the column for the first name, this error cannot be caught as easily.

errors

* NAVIGATING IN EXCEL (04) CTRL+UP or CTRL+DOWN takes you to the _____ cell or _____ cell in a column where is blank (no data)

first / last

COPY PASTE-SPECIAL (10) Copy-Paste-Special is useful for more than copying formats and conditional formats. In addition to formats, it can be used to copy _____, v____, c_______, data _______ rules, n_______formats, and other options.

formulas values comments validation number

Number Formats (10) A number format has ____ parts: _______ numbers, _____ numbers, ____, and ____. Just as a function in a formula has a ________ separating two arguments, the parts in a number format are separated by a ______.

four positive negative zeroes text comma semi-colon

THE RIBBON SYSTEM (02) Within any ribbon, commands are organized in _______. Commands related to font name, font size, font color, and related commands grouped in the _____ group.

groups Font

Importing Data from another File (05) The Text Import Wizard dialog box gives us the option of indicating whether our data has__________ and whether the file is _______, and what type of delimiter is used.

headers delimited

DISPLAYING DATA - FREEZING OR SPLITTING A SCREEN (05) Once the top row is 'frozen' it remains visible as we scroll down the worksheet. When the left column is frozen, the _____ for rows are visible as we scroll to the _______of the worksheet.

headers / right

Time (03) Time can be represented in formats as follow: a. ____ , _____, and _____ b. ____ and ____ only c. ____ hours or _____ hours

hours, minutes, and seconds; in hours and minutes only; in 12-hour or 24-hour

Other Data Types (03) Other data types that are not commonly used are _____ and ______.

hyperlinks images

Importing Data Using Query Editor (05) In some versions of Excel, we may need to use a Query Editor to _____ data. Where the Query Editor dialog box is located?

import Get External Data' from the Data ribbon.

DISPLAYING DATA - FREEZING OR SPLITTING A SCREEN (05) When we freeze rows and columns on a screen, you cannot scroll _____ the frozen area. If you need to do this, you want to split the screen rather than freeze it. When you invoke the split command, Excel splits the screen such that all rows above the active cell, and all columns to the left of the active cell, are in the split portion of the screen.

inside

Cells (01) The _____ of a row and a column is a cell. We refer to a cell by referring to the ___ and ___. The default reference type is called A1 referencing. In the A1 format, the column is represented by a ____. This letter precedes 先于 the row _____. The reference 'K10' refers to column K and row 10. 'K10' is also known as the cell _______.

intersection row and the column letter number address row column R10C11

Range Names (08) A worksheet consists of rows and columns. We can refer to any cell on a worksheet by specifying the row and column at the _____ of which we have the cell.

intersection***

DATA TYPES IN EXCEL (03) The information we enter in a cell can be one of several types: _______, ______,_____,______, and _____are the most common.

number, text, date, time, and formula

Formula (03) A formula can be a combination of a. ______ with _____ b. _______ with _____ c. ______, _____, and ________

numbers with operators (e.g., =5+5) letters with operators (e.g., ="John" & "Doe") letters, numbers and operators (e.g., ="James Bond" & " 007")

Rules for Naming Ranges (08) Range names have to follow certain rules: >> A name must begin with a_____ as long as we do not use ___ or ___ by itself (either in the upper or the lower case) >> A name starting with "R" or "C" followed by a number is also not valid if it appears to be a cell address (e.g., "R5000" can be interpreted as column "R" row 5000 and hence is not valid. However, "_____" is valid as there is no row 5000000 >> A name can also start with an ________ character (_), or a ______ (\). >> Remaining characters in the name can be letters, numbers, periods, and underscore characters >> A cell reference such as ___ cannot be used as a range name. Excel assumes we want to go to that cell. >> The length of a range name can vary from 1___to ___ characters >> _____s are not allowed as part of a name.

letter / "R" or "C" R5000000 underscore / backslash G5 1 to 255 Space

* Text (03) Text includes _____, _____ marks, _______, and ______. We can begin a number with an _____ which will enter a number as text.) Text PLS +Numbers

letters, punctuation, symbols, and numbers

Invoke 调用 data validation （07） Data validation can also be used to enter information corresponding to specified____. This ensures that only ____ names are entered, and prevents errors in spelling. How to invoke data validation by referring to a list of values:

lists permitted DATA ribbon --> Data Tools group --> Data Validation -->Settings --> List

* Number (03) A number permits _______ operations. For example, we can add two numbers together.

mathematical

Text (03) Text refers to information that does not permit _______ operations.

mathematical apostrophe (')

THE FILE SYSTEM IN EXCEL (03) Excel file names are made up of two parts: the file _____ and the file _____.

name / extension

NAVIGATING IN EXCEL (04) You can also press CTRL+PGDN to go to the _____ worksheet or CTRL+PGUP to go to the _________worksheet.

next / previous

Cells (01) Each cell can contain a ______, _______, or _______. A cell can have a reference to another cell in the ____ worksheet, to a cell in _____ worksheet in the same workbook, or to a cell in a worksheet in a ______ workbook.

number / text / formula same / another / different

Transposing a set of numbers (10) 横变竖，竖变横 We can also use Copy Paste-Special to transpose a set of numbers. Transposing refers to copying values originally in rows and columns to columns and rows. To do this:

o Copy the values we want to transpose o Invoke Copy Paste-Special and select transpose

Operations with a constant (10) Rather than have these numbers in so many dollars we may want to express these numbers in the '000s. An easy way to do this is to:

o Enter 1000 in another cell o Copy this value (1000) o Select the range (with values in dollars) o Invoke Copy Paste-Special and select divide o Values are now expressed in the '000s of dollars o The 1000 that we entered earlier can now be deleted

Importing Data Using Query Editor (05) After importing data to Query Editor, all the data is imported into the ____ column. To organize the data using ________ function in Data Tools.

one Text to columns

ENTERING FORMULAS AND FUNCTIONS (06) Type =sum(B3:E3). Excel indicates that the SUM function requires at least _____ argument, with a second argument [shown in square brackets] as _____.

one optional

Worksheet (01) Each workbook must have at least _______ worksheet. You can add additional worksheets as needed. Each worksheet has a Tab, visible above the _____ bar.

one tab / status

Formula (03) In the 5+5 example, the + sign is an ______ In the "John" & "Doe" example, the & sign is an ______ In the James Bond 007 example, the number 007 is treated as _______

operator operator text

CUSTOMIZING THE QUICK ACCESS TOOLBAR (QAT) (02) The QAT does not have an effect on the availability of the command in its ________ location.

original

PROTECTING A WORKBOOK (09) We can easily ______ the read-only requirement (click 'No'). We can also check 'Always create back-up'. This will make a backup copy of the file (look in the same folder as the original file for "Backup of filename".

override

LIMITATIONS OF DATA VALIDATION (07) Let us say that we have two ranges of cells, Range A and Range B. We have created validation rules for Range A and no rules for Range B When we copy/paste from Range B to Range A, Everything in the Range B replaces the Range A. The Data validation rules in Range A will be ______. For example, when data is filled in a pattern, the validation rules are not applied. The date will be overwritten.

overwritten

* Text (03) Different parts of the text can be presented in different ____ in a ______cell. By default, Excel aligns text to the _______.

parts / formats same left

THE FILE SYSTEM IN EXCEL (03) The file name is the set of characters up to the last ____. The file extension is the set of characters _____ and _____ the last period in a file name.

period from / including

RECENTLY OPENED FILES (03) If there is a particular workbook that you want to be always available and not to be dropped off from the list by '________________' it to the list of previously used workbooks. How to do it?

pining File Menu Recent Workbooks Position the cursor on the workbook that you want to 'pin' Click on the pin icon that appears to the right of the file name The direction in which the pin points changes to indicate that it is now pinned In Figure 8, the horizontal pin of the highlighted file indicates that the file is not pinned

Worksheet (01) Clicking on the ____ sign will add a new worksheet to the active workbook. When you have many worksheets and cannot see all the tabs, you can click on the______and______ arrowheads at the bottom left of the worksheet. This will help you navigate to a different worksheet.

plus left and right

Number Formats (10) Part 1: format for a ______ number e.g. ___________ ; Part 2: format for __________ number e.g. ______________; Part 3: format for_______ e.g._______; Part 4: format for _______e.g. ____________

positive; #,###.00_) negative; [red](#,###.00) zero; 0.00 text ;@ " product discontinued"

* Entering data directly in a worksheet (05) We can also use parentheses () to indicate which operations to be calculus first. This is referred to as the ________ of operations.

precedence

PROTECTING A WORKBOOK (09) Making a backup copy of the file is strongly ______ for important files. Making a backup copy is particularly important when running a ________in Excel because it is not possible to undo.

recommended macro

Adding an existing button to a tab (02) We can neither ______ nor ______ a button from an existing group. Hence(/therefore), if we want to add a button, create a _____ group first, then ____ the button to the custom group. The custom group can be created within an _____ribbon or added to a _____ ribbon.

remove / add custom add existing new

* Number (03) By default, Excel aligns numbers to the _______. However, the user can align the number differently.

right

R1C1 FORMAT FOR CELL REFERENCE (06) R1C1 format refers to a cell using numbers for both the ____ reference and the ________ reference. The formula looks like "=RC[-2]*RC[-1]" To switch between A1 and R1C1 ?

row / column FILE --> Options --->Formula --->Working with formulas ---> R1C1 reference style

* NAVIGATING IN EXCEL (04) Within a workbook, click on ________ tab can move one worksheet to another.

sheet

Importing Data from another File (05) Advantage of storing data as a .csv or .txt: 1. CSV or TXT file is that it makes the ____ much smaller. 2. The data can be read by programs that are meant to read ____ (e.g., Notepad or Word).

size text

ENTERING FORMULAS AND FUNCTIONS (06) Enter =sum(B3:E3 and press enter. There is a small ______ at the bottom right of the cell. If we double click on this square Excel automatically fills in the formula.

square

* WHY R1C1 NOTATION? easier to _______ from Lotus 1-2-3 to Excel. easier to ____ errors in our formula notation. easier to _____ working with macros

switch find facilitate

* NAVIGATING IN EXCEL (04) CTRL+UP or CTRL+DOWN takes you to the ____ or _____ of a range of cells containing data.

top or bottom

Date (03) The year can be expressed in ____ digits or in _____ digits. Month and day can be expressed in ______. Dates are considered _____ in Excel.

two / four letters numbers

Number Formats (10) A number format is simply the way a number is presented. The underlying ______ is not changed. Excel offers a number of built-in number formats. A commonly used format is to present a number with (or without) a certain number of decimal values and with (or without) a 1000 separator.

value

* Entering data directly in a worksheet (05) When entering data, you should enter one ____ in one cell. That is, a cell should contain a ______. A variable will refer to that cell.

value constant