BUAD 1305 SHSU Paul Allen Excel Chapters 1-4
Skills Covered:
- Enter Text - Use Autofill to complete a sequence - Enter values - Enter a date - Clear cell contents
Nonadjacent Range
A collection of multiple ranges (such as D5:D10 and F5:F10) that are not positioned in a contiguous cluster in an Excel worksheet.
AutoComplete
A feature that searches for and automatically displays any other label in that column that matches the letters you type.
Formula
A formula combines cell references, arithmetic operations, values, and/or functions used in a calculation.
Range
A group of adjacent 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 (such as G5:H10).
Sheet Tab
A visual label that looks like a file folder tab. A sheet tab shows the name of a worksheet contained in the workbook. When you create a new Excel workbook, the default worksheet is named Sheet1.
Columns, Rows and Cells
A worksheet contains columns and rows, with each column and row assigned a heading. Columns are assigned alphabetical headings. The intersection of a column and a row is a cell. Each cell has a unique cell address, identified by first its column letter and then its row number. (Ex: C6)
Name Box
An element located below the Ribbon and displays the address of the active cell. Use the Name Box to go to a cell, assign a name to one or more cells, or select a function.
Formula Bar
An element located below the Ribbon and to the right of the Insert Function command. Shows the contents of the active cell. You enter or edit cell contents here or directly in the active cell. Drag the bottom border of the ________ ___ down to increase the height of the Formula Bar to display large amounts of data or a long formula contained in the active cell.
Review and Print a Worksheet
Before printing a worksheet, you should display a preview to ensure the data will print correctly. The Print Preview helps you see if margins are correct or if isolated rows or columns will print on separate pages. After making appropriate adjustments, you can print the worksheet.
Paste
Cell contents and all formatting from copied cells
Keep Source Formatting
Cell contents and formatting from copied cells
Keep Source Column Widths
Cell contents, number and text formatting, and the column width of the source data when pasting in another column
No Borders
Cell contents, number formatting, and text formatting except borders
To select all cells in a worksheet
Click Select All or press Ctrl+A twice
To select current range containing data, including headings
Click on the rage of data and press Ctrl+A
To Edit the contents of a cell
Click the cell. Click in the Formula Bar or press F2 to put the cell in edit mode. The insertion point displays on the right side of the data in the cell when you press F2. Make the changes to the content in the cell. Click or press Enter.
To clear the contents of a cell
Click the cell. Press Delete or click the cell, click Clear in the Editing group on the Home tab, and select the desired option (see Figure 1.7).
To select an entire column
Click the column heading
To select an entire row
Click the row heading
Insert Function
Click to display the______ ________ dialog box to search for and select a function to insert into the active cell. The Insert Function icon changes from gray to green when you position the pointer over it.
New Sheet
Click to insert a new worksheet to the right of the current worksheet.
Picture
Creates a picture image of the copied data; pasted data is not editable
Linked Picture
Creates a picture with a reference to the copied cells; if the original cell content changes, so does the picture
Paste Link
Creates a reference to the source cells (such as =$G$15), not the cell contents; preserves number formatting but not text formatting
Delete a column or row
Delete-> Column/row
F5 or Ctrl+G
Display the Go To dialog box to enter any cell address.
Zoom Control
Drag the zoom control to increase the size of the worksheet onscreen to see more or less of the worksheet data.
To select a range
Drag until you select the entire range. Alternatively, click the first cell in the range, press and hold Shift, and click the last cell in the range. Or type it in the name box A green border appears around a selected range
Formulas & Number Formatting
Formulas and number formatting, such as Currency, but no font formatting, such as font color, fill color, or borders
Formulas
Formulas, but no formatting, from copied cells
View Controls
Icons on the right side of the status bar that control how the worksheet is displayed. Click a view control to display the worksheet in Normal, Page Layout, or Page Break Preview. Normal view displays the worksheet without showing margins, headers, footers, and page breaks. Page Layout view shows the margins, header and footer area, and a ruler. Page Break Preview indicates where the worksheet will be divided into pages.
Sheet tab navigation
If your workbook contains several worksheets, Excel may not show all the sheet tabs at the same time. Use the buttons to display the first, previous, next, or last worksheet.
Insert a column or row
Insert->Insert Sheet Rows/Columns
Ctrl+Home
Make cell A1 the active cell
To Enter Text in a cell complete the following steps
Make sure the cell is active where you want to enter text. Type the text. If you want to enter a numeric value as text, such as a class section number, type an apostrophe and the number, such as ′002. Make another cell the active cell after entering data by completing one of the following steps: Press Enter on the keyboard. Press an arrow key on the keyboard. Press Tab on the keyboard. Keep the current cell active after entering data by completing one of the following steps: Press Ctrl+Enter on the keyboard. Click Enter (the check mark between the Name Box and the Formula Bar).
Ctrl+End
Make the rightmost, lowermost active corner of the worksheet-the intersection of the last column and row that contains data—the active cell. Does not move to cell XFD1048576 unless that cell contains data.
Down Arrow
Move down one cell in the same column.
Left Arrow
Move left one cell in the same row.
Right Arrow
Move right one cell in the same row.
Tab
Move right one cell in the same row.
Home
Move the active cell to column A of the current row.
Page Up
Move the active cell up one screen
Up Arrow (on keyboard)
Move up one cell in the same column.
Formatting
Number and text formatting only from the copied cells; no cell contents
Paste Special
Opens the Paste Special dialog box (see Figure 1.32)
Apply Font Attributes
Select font options: Font options include serif or sans serif fonts, as well as monospaced or proportional fonts. The Font group on the Home tab contains all the font selections. Change text appearance: Format characters by applying bold, italics, underline, font color, text highlighting, and text effects.
Format A Paragraph
Select paragraph alignment: Align paragraphs to be left or right aligned, centered, or justified. Select line and paragraph spacing: Line spacing refers to the amount of space between lines within a paragraph, whereas paragraph spacing is the amount of space between paragraphs. Select indents: Options for indenting paragraphs include left indent, right indent, hanging indent, and first line indent. Set tab stops: Use tabs to indent the first line of the paragraph, or to arrange text in columns, including leaders if desired. Apply borders and shading: Borders and shading draw attention to selected paragraphs. Create bulleted and numbered lists: Itemized lists can be set apart from other text with bullets, while sequential lists are formatted with numbers.
To select nonadjacent range
Select the first range, press and hold Ctrl, and select additional range(s).
How to move a range
Select the range, click Cut in the clipboard, (the data you cut remain in their locations until you paste them somewhere else), Select Destination>ENTER or Paste, Ensure the destination range, Click on the top-left corner of the destination range and Paste
To Copy a Range
Select the range, copy, select destination and paste
Skills for Managing Worksheets
Skills Covered: -Copy or Move a Worksheet -Rename a Worksheet -Group Worksheets -Set Page Orientation -Select Scaling Options -Set Margin Options -Create a Header or Footer -View in Print Preview -Print a Worksheet
Creating Formulas
Step 1: Cell Reference instead of typing 2. Use the order of operations- (),^,*/,+- Use semi-selection to increase accuracy/decrease typing time (pointing) 3. Duplicate the formulas with fill pointer 4. Display cell formulas to double check them
Text
Text is any combination of letters, numbers, symbols, and spaces not used in calculations.
Select Page Setup Options
The Page Layout tab on the Ribbon contains options for setting margins, selecting orientation, specifying page size, selecting the print area, and applying other settings. Specify page options: Page options include orientation, paper size, and scaling. Set margin options: You can set the left, right, top, and bottom margins. In addition, you can center worksheet data horizontally and vertically on a page. Create headers and footers: Insert a header or footer to display documentation, such as your name, date, time, and worksheet tab name. Select sheet options: Sheet options control the print area, print titles, print options, and page order.
Active Cell
The current cell, which is indicated by a dark green border.
Column Headings
The letters above the columns. For example, B is the letter above the second column.
Row Headings
The numbers to the left of the rows, such as 1, 2, 3, and so on. For example, 3 is the row heading for the third row.
Status Bar
The row at the bottom of the Excel window. It displays information about a selected command or operation in progress. For example, it displays Select destination and press ENTER or choose Paste after you use the Copy command.
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.
Transpose
Transposes Data from rows to columns and columns to rows
Tip: Transposing Columns and Rows
Transposing so the data in the first column looks like labels of the first row or labels for the first column.
To use Auto Fill to complete a series of text (such as month names)
Type the first label (e.g., January) in the starting cell (e.g., cell A1) and press Ctrl+Enter to keep that cell the active cell. Point to the fill handle (a small green square in the bottom-right corner of the active cell) until the pointer changes to a thin black plus sign. Drag the fill handle to repeat the content in other cells (e.g., through cell A12).
Paste- Values
Unformatted values that are results of formulas, not the actual formulas
To Paste a Range
Use the paste arrow in clipboard and preview how it will look and click the option u want
Values
Values are numbers that represent a quantity or a measurable amount
Paste- Values and Source Formatting
Values that are the results of formulas, not the actual formulas; preserves number and text formatting
Enter
When you enter or edit data, click ______ to accept data typed in the active cell and keep the current cell active. Enter changes from gray to blue when you position the pointer over it.
Cancel
When you enter or edit data, click _________ to cancel the data entry or edit, and revert back to the previous data in the cell, if any. __________ changes from gray to red when you position the pointer over it.
Adjust Row height
You can adjust the row height—the vertical measurement of the row—in a way similar to how you change column width by double-clicking the border between row numbers or by selecting Row Height or AutoFit Row Height from the Format menu (refer to Figure 1.26).
Semi-Selection
a process of selecting a cell or range of cells for entering cell references as you create formulas. Semi-selection is often called pointing because you use the pointer to select cells as you build the formula.
Page Down
move the active cell down one screen
Flash Fill
uses data in previous columns as you type in a new label in an adjoining column to determine what to fill in