Excel Chapter 1
Sheet Tab Navigation Buttons
if your workbook contains several worksheets, excel may not show all of the sheet tabs at the same time. Use the buttons to display the first, previous, next, or last worksheet.
status bar
located at the bottom of the window and contains information pertaining to the document such as page number, word count page layout, and the zoom slider
What does Ctrl + end do?
make the rightmost, lowermost active corner of the worksheet-the intersection of the last column and row that contains data-the active cell. cell must contain data to be selected
In merge options what does merge cells do?
merges a range of cells on multiple rows as well as in multiple columns
In merge options what does merge and center do?
merges and selected cells and centers data into one cell
In merge options what does merge across do?
merges the selected cells but keeps text left-aligned or values right-aligned
What does the tab key do?
move right one cell in the same row
What does the home button do?
move the active cell to column A on the current row
What does the page up button do? Page down?
move the active cell up one screen move the active cell down one screen
What happens when you choose the formatting paste option?
number and text formatting only from the copied cells no cell contents
What happens when you choose the paste special paste option?
opens the paste special dialogue box
How do you widen a column to accommodate the longest label or value in a column?
point to the right vertical border of the column heading. When the pointer displays a two-headed arrow, double-click the border. For example, if column B is too narrow, double click the right vertical border of the column B heading or click format in the cells group on the home tab and select autofit column width
How do you display cell formulas in the worksheet?
press ctrl and the grave accent (`) key or Click show formulas in the formula auditing group on the Formulas tab
How do you select a nonadjacent range?
select the first range, press and hold ctrl, and select additional ranges
In merge options what does unmerge cells do?
separates a merged cell into multiple cells again
cell address
the unique identifier of a cell starting with the column letter and then the row number, such as C6
row height
the vertical measurement of a row
What happens when you choose the transpose paste option?
transposes data from rows to columns and columns to rows
What happens when you choose the values paste option?
unformatted values that are the results of formulas, not the actual formulas
What is the difference between text and value entries?
value entries can be the basis of calculations but text can not
What happens when you choose the values and source formatting paste option?
values that are the results of formulas, not the actual formulas preserves number and text formatting
What happens when you choose the values and number formatting paste option?
values that are the results of formulas, not the actual formulas; preserves number formatting but not text formatting
cancel
when you enter or edit data, click Cancel to cancel the data entry or edit and revert back to the previous data in the cell
enter
when you enter or edit data, click enter to accept data typed in the active cell and keep the current cell active.
How do you enter text in a cell?
-make sure the cell is active -type the text. If you want a numerical value to be read as text, type an apostrophe before the number ('002) -Make another cell the active cell after adding the data by either pressing enter, pressing an arrow key, or pressing tab or keep the current cell active after entering data by either pressing ctrl+enter or clicking enter in excel
How do you insert a new column or row?
1. Click in the column or row 2. Click the insert arrow in the cells group on the home tab 3. Select insert sheet columns or insert sheet rows
How do you enter a formula?
1. Click the cell 2. Type an equal sign (=) followed by the arithmetic expression using cell references instead of values. Do not include any spaces in the formula 3. click enter or press enter
How do you use semi selection to create a formula?
1. Click the cell where you want to create a formula 2. Type an equal sign (=) to start a formula 3. Click the cell that contains the value to use in the formula. A moving marquee appears around the cell or range you select, and Excel displays the cell or range reference in the formula 4. Type a mathematical operator 5. Continue clicking cells, selecting ranges, and typing operators to finish the formula. use the scroll bars if the cell is in a remote location in the worksheet, or click a worksheet tab to see a cell in another worksheet. 6. Press enter to complete the formula
How do you copy a formula to other cells using the fill handle?
1. Click the cell with the content you want to copy to make it the active cell. 2. Point to the fill handle in the bottom right corner of the cell until the pointer changes to the fill pointer (a thing black plus sign) 3. Drag the fill handle to copy the formula
How do you delete a column or a row?
1. Click the column or row heading for the column or row you want to delete 2. Click delete in the cells group on the home tab
How do you paste data from the clipboard into a different format?
1. Click the paste arrow in the clipboard group 2. point to command to see a screen tip and a preview of how the pasted data will look 3. click the option you want to apply
How do you complete the workbook?
1. Document the workbook as thoroughly as possible (Include the current date, your name, assumptions, and the purpose of the workbook either on a separate worksheet or in the properties section) 2. Save and share the completed workbook
How do you enter and format the data?
1. Enter the labels, values, and formulas in excel (Product name to indicate the values of each product, cost, retail price) 2. Format the numerical values in the worksheet (align decimal points in columns of numbers and add number formats and styles) 3. Format the descriptive titles and labels (add bold and color to headings so they stand out)
How do you plan the worksheet design?
1. State the purpose of the worksheet (store data about products on sale, and calculate important details) 2. Decide what outputs are needed to achieve the purpose of the worksheet (Columns to calculate the retail price, sale price and profit margin) 3. Decide what input values are needed to achieve the desired output (cost the company pays its manufacturers, the markup rates and the proposed discount rates)
How do you unhide column A or row 1?
1. Type A 1 in the name box and press enter 2. click format in the cells group on the home tab, point to hide and unhide and select unhide columns or unhide rows
How do you use autofill to complete a series of text?
1. Type the first label (ex. January) in the starting cell (ex. cell a1) and press ctrl+enter to keep that cell the active cell 2. Point to the fill handle 3. Drag the fill handle to repeat content in other cells
How do you use autofill to fill a sequence of consecutive numbers?
1. Type the first number in the starting cell (ex. F1) and press Ctrl+enter to keep that cell the active cell 2. Drag the fill handle to fill the content in other cells. Excel will copy the same number for the rest of the cells 3. Click autofill options and select fill series. Excel will change the numbers to be in sequential order, starting with the original value you typed
How can you use autofill to fill a sequence of number patterns? ex. 5, 10, 15, in rows G1-G7
1. Type the first two numbers of the sequence in adjoining cells 2. select those two cells containing the starting two values 3. Drag the fill handle to fill in the rest of the sequence
How do you apply cell style to a cell or range of cells?
1. click cell styles in the Styles group on the home tab to display the cell styles gallery 2. Position the pointer over a style name to see a live preview of how the style will affect the selected cell or range 3. Click a style to apply it to the selected cell or range
How do you insert one or more cells?
1. click in the cell where you want the new cell 2. Click the insert arrow in the cells group on the home tab 3. select insert cells 4. Select an option from the Insert dialog box to position the new cell and click ok
How do you edit the contents of a cell?
1. click the cell 2. click in the formula bar or press F2 to put the cell in edit mode 3. Make the changes to the content in the cell 4. click or press enter
How to clear the contents of a cell?
1. click the cell 2. press delete or click the cell. click clear in the editing group on the home tab and select the desired option
How do you increase or decrease the indent of data in a cell?
1. click the cell that contains data 2. click increase or decrease indent in the alignment group
How do you wrap text within a cell?
1. click the cells or select the range of cells that contain labels that need to be wrapped 2. click wrap text in the alignment group
How do you merge and center cells?
1. enter the text in the top-left cell of the range 2. select the range of cells across which you want to center the label 3. click merge and center in the alignment group on the home tab
How do you hide non adjacent columns or rows?
1. press and hold ctrl while you click the desired column or row headings 2. use any acceptable method to hide the selected columns or rows
How do you hide a column or row?
1. select a cell or cells in the column or row you want to hide 2. Click format in tje cells group on the home tab 3. point to hide and unhide 4. select hide columns or hide rows, depending on what you want to hide
How do you delete a cell or cells?
1. select the cell (s) 2. click the delete arrow in the cells group 3. select the delete cells to display the delete dialog box 4. click the appropriate option to shift cells left or up and click ok
How do you apply a fill color?
1. select the cell or range that you want to have a fill color 2. click the fill color arrow on the home tab to display the color pallette 3. select the color choice from the fill color palette.
How do you unhide a column or row?
1. select the columns or rows on both sides of the hidden column or row (ex. if B is hidden select A and C) 2. click format in the cells group on the home tab point to hide and unhide, and select unhide columns or unhide rows, depending on what you want to display again
How do you move a range?
1. select the range 2. Click cut in the clipboard group to copy the range to the clipboard 3. Ensure the destination range-the range where you want to move data-is the same size or greater than the size of the cut range 4. Click in the top left corner of the destination range, and use the paste command. If any cells in the destination range contain data it will be overridden
How do you copy a range?
1. select the range 2. click copy in the clipboard group to copy the contents of the selected range to the clipboard. A moving dashed green border surrounds the selected range and the status bar displays: select destination and press enter or choose paste. 3. Ensure the destination range is the same size or greater than the size of the copied range 4. click in the top left corner of the destination range where you want the duplicate data and click paste. 5. press ESC to turn off the moving dashed border around the originally selected range
Formula Bar
A bar located between the ribbon and the worksheet in which users can edit the contents of a cell.
workbook
A collection of one or more related worksheets contained within a single file.
range
A group of adjacent cells or contiguous cells in a worksheet. A range can be adjacent cells in a column (such as C5:C10), in a row (such as A6:H6) or a rectangular group of cells (G5:H10)
Input area
A range of cells in a worksheet used to store and change the variables used in calculations
Worksheet
A single spreadsheet that typically contains descriptive labels, numeric values, formulas, functions, and graphical representations of data.
sheet tab
A small tab at the bottom of a worksheet that allows you to move from one worksheet to another within the same workbook.
Insert Function
A toolbar button that displays functions by category that will assist you in writing the formula.
When do you format the worksheet?
After entering the data and formulas
Spreadsheet
An electronic file that contains a grid of columns and rows used to organize related data and to display results of calculations, enabling interpretation of quantitative data for decision making.
Paste Options button
An icon that displays in the bottom-right corner immediately after using the Paste command. It enables the user to apply different paste options.
How do you select all cells in a worksheet?
Click select all or press ctrl+A twice
How do you select an entire column? A row
Click the column heading or the row heading
Name Box
Displays the cell reference, which is the location of the active cell in the worksheet. It is located on the left side of the formula bar.
What does CTRL + Home do?
Make cell A1 the active cell
Column Width
The horizontal measurement of a column in a table or a worksheet. In Excel, it is measured by the number of characters or pixels.
Cell
The intersection of a column and a row in a table or worksheet.
semi-selection
The process of using the pointer to select cells while building a formula. Also known as pointing.
output area
The range of cells in an excel worksheet that contain formulas dependent on the values in the input area
Select All
The triangle at the intersection of the row and column headings in the top-left corner of the worksheet. Click it to select everything contained in the active worksheet.
Non-Adjacent Range
a collection of multiple ranges (D5:D10 and F5:F10) that are not positioned in a contiguous cluster in an excel worksheet
column heading
a column letter above the grid that identifies each column
formula
a combination of cell references, operators, values, and/or functions used to perform a calculation
AutoFill
a feature that helps you complete a sequence of moths, abbreviated months, quarters, weekdays, weekday abbreviations, or values. Autofill also can be used to fill or copy a formula down a column or across a row
AutoComplete
a feature that searches for and automatically displays any other label in that column that matches the letters you type
row heading
a number at the far-left side of a row that can be clicked to select the entire row of cells
value
a number that represents a quantity or a measurable amount
Flash fill
a productivity feature that enables you to enter data in one or two cells to provide a pattern which is used by excel to complete the data entry
order of operations
a rule that controls the sequence in which arithmetic operations are performed. Also called the order of precedence (PEMDAS)
active cell
a selected worksheet cell that is ready for data entry
fill handle
a small green square at the bottom-right corner of the active cell. You can position the pointer on the fill handle and drag it to repeat the contents of the cell to other cells or to copy a formula in the active cell to adjacent cells down the column or across the row
text
any combination of letters, numbers, symbols and spaces not used in Excel calculations phone numbers and social security numbers are viewed by excel as text
How do you differentiate between text and values?
by aligning decimal places and adding defining characteristics ($,%)
What happens when you choose the "paste" paste option?
cell contents and all formatting from copied cells is copied
What happens when you choose the keep source formatting paste option?
cell contents and formatting from copied cells
What happens when you choose the keep source column widths paste option?
cell contents, number and text formatting, and the column width of the source data when pasting in another column
What happens when you choose the no boarders paste option?
cell contents, number formatting, and text formatting except boarders
You should use ____ _______ instead of values in formulas where possible
cell refrences
How do you select a current range containing data including headings
click in the range of data and select control +A
How do you navigate to a new cell?
click it or use the arrow keys
New sheet button
click to insert a new worksheet in the workbook
What happens when you choose the picture paste option?
creates a picture image of the copied data pasted data is not editable
What happens when you choose the linked picture paste option?
creates a picture with a reference to the copied cells; if the original cell content changes, so does the picture
What happens when you choose the paste link paste option?
creates a reference to the source cells (such as $G$15) not the cell contents; preserves the number formatting but not text formatting
What does f5 or Ctrl G do?
display the go to dialog box to enter any cell address
How do you adjust the width of a column to an exact width?
drag the vertical border to the left to decrease the column width or to the right to increase the column width. As you drag the vertical border. Excel displays a Screentip specifying the width from 0-255 characters in pixels or click format in the cells group on the home tab, select column width, type a value that represents the maximum number of characters to display in the column width box in the column width dialogue box and then click ok
How do you select a range?
drag until you select the entire range. or click the first cell in a range, hold shift, then click the last cell
What happens when you choose the formulas and number formatting paste option?
formulas and number formatting, such as currency, but no font formattin g, such as font color, fill col, or boarders
What happens when you choose the formulas paste option?
formulas, but no formatting, from copied cells
