IC3 Lesson 10: Using Microsoft Excel
What is a "table"? How does the data need to be arranged in the worksheet to create a table?
A TABLE is a rectangular block of data in which the rows have a common structure and format. You can define this block of data as a "table", but there must not be any blank rows or columns in the block and each column must have a header (label).
What is a "cell"?
A cell is the intersection (a meeting point) of a row and a column and it can contain one value or formula. The value can be text or a number.
What is a chart? How do you create one?
A chart is a pictorial representation of data in a worksheet. To create one you must FIRST select the data from the worksheet that you want to include in the chart and THEN select the type of chart you want to use.
What is a file in Excel called?
A file in Excel is called a WORKBOOK.
What is a "formula" in Excel? How do you write a formula to enter into a cell?
A formula is a calculation using numbers or other data in a cell, or using numbers in other cells. You must start a formula with an equal (=) sign.
A file in Excel can have more than one "page". What is a "page" called?
A page in a workbook is called a WORKSHEET.
What is an Excel database?
A simple database (one worksheet) can be created in Excel.
How many rows and columns does an individual "page" in Excel contain?
A worksheet in Excel contains rows numbered from 1 to 1,048,576 and columns assigned letters from A to Z then AA to ZZ and then then AAA to ZZZ, and so on up to XFD.
What is the difference between "absolute" and "relative" cell addresses?
An ABSOLUTE CELL ADDRESS refers to a fixed location on the worksheet. A formula in a RELATIVE CELL ADDRESS will adjust when it is copied and pasted into a new location and will perform the same calculation on the cells in the new location.
What are Area, X Y Scatter, Stock, Surface and Radar charts good at displaying?
Area - Compares a continuous change in volume. X Y (Scatter) - Determines data patterns. Stock - (from Stock Exchange) Displays high-low-close data; requires at least 3 sets of data. Surface - Displays trends in values with a 3D presentation and a continuous surface. Radar (or Spider) - Determines patterns or trends with points matched up by lines.
What do you have to be careful of before inserting or deleting rows, columns or cells?
Before doing any of these actions, check whether it will affect other parts of your worksheet that are not visible on the screen.
Before you print a worksheet or workbook, what should you do?
Before printing, you should check that your data, spelling and grammar are correct. Check that all of the data is included in the print area (click "File", "Print Area"). Then click "File", then "Print" and check your orientation, margins, layout, font size, column and row margins, etc.
On a worksheet, how are the columns and rows identified? How do you identify the "active" cell?
COLUMN HEADINGS - Sequential letters at the top of each column identifies the columns. ROW HEADINGS - Sequential numbers on the left side allow you to identify rows. ACTIVE CELL - A thick border around a box indicates the current cell.
What are the combinations of keys you can use to move around in a worksheet?
CRTL+HOME - Moves to cell A1. CRTL+END - Moves to the last cell containing data. CRTL+G, or F5 - Displays the "Go To" dialog box to move quickly to another cell.
What are the actions required to change the formatting of numbers, including decimals?
Click on "Format", "Cell", "Number".
How can you apply border, colors and patterns to cell(s)?
Click on "Home", then on the "Borders" or "Fill" icon.
You can create a number of different types of charts in Excel. What are some types of charts and what are they good at displaying?
Column chart - Compares values over time, or categories, in a vertical presentation. Line - Compares continuous trends. Pie - Compares parts of a whole. Bar - Compares values over time, or categories, in a horizontal presentation
How can you sort and arrange the data in columns and rows in an order that you select?
Data is usually sorted in columns, but you can sort by rows also. On the Home tab, click "Editing', then "Sort & Filter".
How do you delete rows, columns, or cells?
Deleting the CONTENTS of a cell doesn't affect the structure of a worksheet. However, deleting a CELL does affect the structure of a worksheet. On the Home tab, click "Cells", then click the arrow for "Delete", or: Click the row or column heading you want to delete, then click on CTRL+- on the Numeric keypad.
In a worksheet, how do you select a row, a column, multiple rows, or multiple columns?
Entire row or column: Position the cursor over the row number or over the column number. When it becomes an arrow, click once. Multiple rows or columns: Follow instructions for single rows/columns and drag cursor to other rows/columns.
In a worksheet, how do you select an entire worksheet? How do you select cells that are not adjacent (next to) each other?
Entire worksheet: Click "Select All". Non-adjacent cells, columns, or rows: Click the first selection, hold down CRTL, and select the remaining ones.
Excel can work with spreadsheets created in earlier versions of Excel and those created with "generic" (no brand name) file types. What are examples of generic spreadsheet file extensions? How is the data formatted in these file types?
Examples of generic file types are .csv and .tsv. Files saved with .csv (comma separated values) extensions display data in columns separated by commas. Files saved with .tsv (tab separated values) extensions display data in columns separated by a tab character.
What do use "filtering" for? How do you filter data?
FILTERING can be used to find specific data in a worksheet. You can use the AUTOFILTER (found under the "Data" tab) and specify what data you want to find.
What is formatting? When can you change the format of cell(s)?
FORMATTING refers to changing the appearance of data. You can change the format of a cell or a range of cells at any time, either before or after you enter data.
How are formulas formatted?
Formulas follow the format = FUNCTION (numbers/values/cell references)
Is there a limit on the amount of text you can enter into a cell? Does text align (line up with) the left or right side of the column?
Labels can be up to 32,767 characters long. Text aligns with the left side of the column by default. You can format data in a cell by clicking on "Format", then "Cell" (or "Row" or "Column") and changing the default settings.
What are some features of the workbook page in Excel?
NAME BOX - shows the address of the active cell. For example, where column A and row 10 intersect is cell A1. INSERT FUNCTION - Opens a dialog box that will help you choose and insert a built-in function. FORMULA BAR - Use to view the contents of the active cell. SELECT ALL - Use to select all the cells in the worksheet.
When you enter numbers, do they align to the left or right side?
Numbers align with the right side of the column by default. You can format dates (or any data) in a cell by clicking on "Format", then "Cell" (or "Row" or "Column") and changing the default settings.
What are the options for formatting a table?
Once you have created a table (select cells, click "Insert", then "Table"), you will see options for formatting the table.
How do you identify the worksheet you are currently on? How do you move around the current worksheet? How do you navigate between worksheets?
SHEET TAB - The name of the current or active worksheet that is on the screen. SCROLL BARS (right and bottom) - Use these to move around the current worksheet. TAB SCROLLING - Arrows to navigate between the worksheets in the workbook.
What are the single keys you can use to move around in a worksheet?
Scroll Bars - Click the arrows at either end of the scroll bar to move one row or column at a time. LEFT, RIGHT, UP, DOWN arrows - moves one cell at a time. HOME - Moves to column A in the current row.
How do you insert rows, columns, or cells?
Select the row below or a column to the right of where you want to insert a new one. Then click the Home tab, then "Insert", then "Row, "Column" or "Cell".
How do you delete rows or columns from a table?
Select the rows or columns to be deleted, then on the Home tab, in "Cells" group, click "Delete".
In a worksheet, how do you select a single cell or a group of cells?
Single cell: Click the cell. More than one cell: Click the first cell and drag the cursor to the last cell, or Click on the first cell, hold down SHIFT, and click the last cell.
What are some common functions or formulas already programmed into Excel?
Some common formulas are =SUM: Adds up the values of the chosen cells =AVERAGE: Calculates an average value of the values in the chosen cells. =MIN: Displays the lowest value in the chosen cells. =COUNT: Counts the number of values in the chosen cells. =MAX: displays the biggest value in the chosen cells. There are many more functions available under the Formulas tab, but you should remember these five.
What is the "Office Clipboard"? How do you use it? Are there any limits on how you use it?
The CLIPBOARD is used for temporary storage of cut or copied data. You can cut or copy more than one cell range and keep up to 24 cell ranges in the Clipboard at one time. You can paste any or all of these items in any sequence. When you cut or copy the 25th item, the first one will be erased. You can "Paste All", "Clear all" or click on "Options" in the Clipboard dialog box.
When you are entering dates, can you choose the format?
The default format of the date value is m-d-yy, but you can change it. You can enter dates as all numbers or as text (spelled out). The format can be month/day/year, or month/day or month/year. You can change it under Format/Cells/Number.
What are the rules for creating a database in Excel?
The first row must contain field names (titles) at the top of each column and they must be formatted differently from the other data. Each column must contain the same kind of data in every row. Each row (record) contains all the fields of data. There can be no blank rows.
There is an "order of operations" that Excel uses in calculating a formula. What is it?
The order in which Excel makes the calculations is called the "ORDER OF OPERATIONS". The order is: 1. multiplication, 2. division, 3. addition, and 4. subtraction.
What are your choices for sorting data?
The sorting options are: A TO Z (ascending order, A to Z, 0 to 9). Z TO A (descending order, Z to A, 9 to 0). CUSTOM SORT - You can decide which column to sort first, second, third, etc. FILTER - Search for specific data and display only those cells that match. CLEAR - Delete the filter and show all items. REAPPLY (the filter again).
There are 3 kinds of data you can enter into spreadsheets. What are they?
The three types of data that you can enter into a worksheet are: LABELS (text; default alignment: left), VALUES (numbers; default alignment: right), and FORMULAS - Formulas can be a cell reference (its location) or a calculation command for numerical data.
How do you add columns or rows to a table?
To add a new column on the right, or a new row at the bottom, next to the last one containing data: Enter data and it will be automatically included, as long as the "Total Row" feature is not activated. To add/delete within a table, click on the column to the right, or the row below, where you want to insert the new one and click the Home tab, then "Cells", "Insert", "Row" or "Column".
How do you change the height of a row?
To change the row height of selected rows, on the Home tab, click "Format", then "Row Height". Type a value, and click "OK". Or: Put the mouse pointer on the bottom edge of the row header and when you see a double-headed arrow, click and drag the mouse.
How do you change the width of a column?
To change the width of a column: On the Home tab, click on "Cells", then "Format", then "Column Width", or: Put the mouse pointer on the line at the right edge of the column and when you see a double-headed arrow, click and drag the mouse.
How do you close a workbook?
To close: Save all your changes first, then click the File tab, then "Close", or CTRL+W, or CRTL+F4. Closing a file prevents accidental changes and frees up system resources for other files.
How do you enter data in a cell? Where do you type it? When you are finished entering data in a cell, what must you do?
To enter data in a cell: First select the cell by clicking on it once. Type the label, value or formula in the cell or in the Formula Bar. When your data is correct, you must hit: ENTER, which will move the selection to the cell below it; TAB, which will move the selection to the next cell to the right; or one of the arrow keys.
How do you open a workbook?
To open: On the File tab, click "Open", then locate the file or click "Browse". Select the file and click "Open". or: Click the File tab, and click on a file from the list of "Recent Workbooks". All files in Microsoft Office applications that are received over the internet are opened in Protected Mode. "Read-Only" and "Protected Mode" work the same way in Excel as they do in Word.
How do you save a workbook you have revised?
To resave and replace a previously saved workbook, click on "File", then "Save", or click on the disk icon ("Save") in the title bar. See note in previous answer about selecting it before saving it.
It takes a lot of effort to create a useful workbook. You should save it frequently when you are working on it. What are the actions to save a new workbook?
To save a new workbook, click on "File", then "Save As". You must give it a name, decide where to put it in your file directory, and if you want a file format other than .xlsx, and then click "Save". Note: If you have several files open on your desktop, make sure you select the new workbook first, by clicking on the Title Bar. If you don't, you may save another open file as your new workbook.)
How do you use "Undo" and "Repeat"? How many previous commands can you change?
Use UNDO to undo commands in the reverse order in which you performed them (last one is undone first). You can undo a maximum of 100 commands. Use REPEAT to reverse an Undo (to go back to what you had before the Undo). You can repeat a maximum of 100 Undo commands in reverse order.
How can you change the alignment of data horizontally and vertically within a cell?
Use either: "Format", "Cell", "Alignment", OR the alignment icons under "Home" in the Quick Access Toolbar.
What happens when you enter more text in a cell than the column width can display?
When you enter more text in a cell than the column width can display, the remaining text will spill over to the adjacent box, if it is empty. If it is not empty, the remaining text is not displayed. If the width of a column is not wide enough to display all the digits of numerical or date information in a cell, a hash tag (#) will show.
When you open Excel, what do you see? What can you do next?
When you open Excel, you see the Backstage View. You can choose a blank workbook or a template and click "Create". You can open a recently used or shared Excel file, or take a tour of Excel.
How can you change the order of operations? (If the answer to this question doesn't display correctly, go back and try again.)
You can change the order by putting parentheses around calculations to be done first. For example: 3 + 2 - 1 / 5 * 2 (3 plus 2 minus 1 divided by 5 multiplied by 2) will be calculated as (5*2) = 10, then (1 / 10) = 0.1, then (3+2=5), then (5-0.1) = 4.9. If you change it to: (3+2-1)/(5*2), the order of operations would be: 5*2=10, then (3+2-1)=4, then 4/10 = 0.4.
How can you preview how your data will look in different chart types?
You can click the "Recommended Charts" tab to preview how your data will look in different chart types. Any chart can be shown in 2D or 3D.
How do you copy and move data? What are the places that you can move it to?
You can select and then cut, copy and paste the contents of cells the same way you do in Word. You can move data in cells from one location to another in the same worksheet, to a location in another worksheet in the same workbook, or to a location in a worksheet in another workbook.
How do you specify which cells you want the formula to work on?
You can select the cells using the mouse or type the range of the cell locations into the cell, e.g., =SUM(B2:E24). You can also select non-adjacent cells to be included in a Function.
Each chart has a default layout that you can modify. What are some of the changes you can make?
You can use the commands on the Chart Tools Ribbon to change the layout of your chart. You can add labels and titles to horizontal and vertical axes (measurement lines); add a chart title, data labels, grid lines, a legend (explanation) of the chart, and a data table beneath the chart. You can display error bars which show the margin of error of the data. [Margin of error = a small difference in the data in case of miscalculation]
