Microsoft Excel 2010
Chapter 1
Computer Information Systems
Creating a Worksheet & an Embedded Chart
INTRODUCTION (Ex 1) Most organizations collect vast amounts of data. Often, data is consolidated into a summary so that people in an organization can better understand its meaning. An Excel worksheet allows data to be easily _______________ and _______________. A _______________ conveys a visual representation of data.
PROJECT – WORKSHEET WITH AN EMBEDDED CHART (Ex 1 – 2) The first step to creating an effective worksheet is to make sure you understand what is required. The person or company requesting the worksheet should provide their needs in a requirements document. A requirements document includes the following:
Microsoft Excel 2010
Chapter 1
Computer Information Systems
Creating a Worksheet & an Embedded Chart
OVERVIEW (Ex 5) After carefully reviewing the requirements document, the next step is to design a solution or draw a ____________________ of the worksheet based on the requirements, which include:
SELECTING A CELL (Ex 7) To enter data into a cell, you must first select it. The easiest way to select a cell, or make it active, is to use the mouse to move the _______________ _______________ sign mouse pointer to the cell and then click. An alternative method is to use the _______________ keys that are located on a standard keyboard. An arrow key selects the cell adjacent to the active cell in the direction of the arrow on the key. You know a cell is selected, or _______________, when a heavy _______________ surrounds the cell and the active cell reference appears in the Name box on the left side of the formula bar.
Microsoft Excel 2010 Computer Information Systems
Chapter 1 Creating a Worksheet & an Embedded Chart
ENTERING TEXT
In Excel, any set of characters containing a _______________, _______________, or space is considered text. Text is used to insert titles, such as worksheet titles or column and row titles, into the worksheet.
AUTOCORRECT (Ex 9)
The AutoCorrect feature of Excel works behind the scenes to correct common mistakes when you complete a text entry in a cell. AutoCorrect makes three types of corrections: o
Corrects two _______________ _______________ letters by changing the second letter to lowercase
o
_______________ the first letter in the names of days
o
Replaces commonly _______________ words with their correct spelling
ENTERING NUMBERS (Ex 13)
In Excel, you can enter numbers into cells to represent amounts. A number can contain only the following characters: Numbers: ___________________________________________________________________________ Other Characters: ___________________________________________________________________
CALCULATING A SUM (Ex 15)
Excel can add, or sum, the numbers within a range of cells with the ___________________. A range is a series of two or more _______________ cells in a column or row. For example, the group of adjacent cells B4, B5, B6, B7, and B8 is called a range.
TO SUM A COLUMN OF NUMBERS Click the cell in which the total should be located to make it active Click the __________ button on the __________ tab in the __________ group. Click the Enter box in the formula bar or press Enter on the keyboard
USING THE FILL HANDLE TO COPY A CELL TO ADJACENT CELLS (Ex 16)
To use the same SUM function in multiple, adjacent cells, you can copy the SUM function from the first cell into the range of cells. The cell being copied is called the __________ __________ or __________ __________. The range receiving the copy is called the __________ _________ or ___________ _________. The range in each SUM function entry uses cell references that are one column to the right of the previous column. Excel automatically adjusts the cell references for each new position. Each adjusted cell reference is called a relative reference.
Microsoft Excel 2010
Chapter 1
Computer Information Systems
Creating a Worksheet & an Embedded Chart
TO COPY A CELL TO ADJACENT CELLS IN A ROW The _______________ _______________ is the small black square located in the lower-right corner of the heavy border around the active cell. o o
o
With the source area active, point to the fill handle to activate it. Drag the fill handle – do not release the mouse button - to select the destination area. A shaded border will appear around the source area and destination area. Release the mouse button to copy the SUM function from the active cell to the destination area and calculate the sums.
FORMATTING THE WORKSHEET (Ex 20)
You format a worksheet to emphasize certain entries and make the worksheet easier to read and understand.
TO CHANGE A CELL STYLE (Ex 22) Click the cell to make it active. Click the ____________________ button on the __________ tab in the __________ group to display the Cell Styles gallery. Point to a style to see a live preview; click the style to apply it to the active cell. Other Ways to Format: o Font Style o Bold a Cell
o o
Font Size Font Color
TO CENTER CELL ENTRIES ACROSS COLUMNS BY MERGING CELLS (Ex 28)
Centering a title (worksheet title and subtitle) across the columns used in the body of the worksheet improves the worksheet’s appearance. To do this, multiple cells in the range are combined, or merged, into a single cell that is the width of the columns in the body of the worksheet. _______________ cells involves creating a single cell by combining two or more cells. Select the first cell in the range, then drag to highlight the remaining cells to be merged and centered. Click the _________ & _________ button on the _________ tab in the _______________ group.
TO FORMAT NUMBERS IN THE WORKSHEET (Ex 31)
The numbers in the worksheet should be formatted to use a dollar-and-cents format. Select (highlight) the range of cells to be formatted – the first row.
Microsoft Excel 2010 Computer Information Systems
Chapter 1 Creating a Worksheet & an Embedded Chart
Click the ____________________ Number Format button on the Home tab in the Number group.
Select the next range of cells – the remaining numbers (do not include totals). Click the _______________ _______________ button on the Home tab in the Number group. Select the final range of cells – the totals. Click the Accounting Number Format button again.
TO ADJUST THE COLUMN WIDTH (Ex 33) Point to the boundary between the column A heading and the column B heading above row 1 to change the mouse pointer to a _______________ _______________ _______________ icon. Double-click on the boundary to automatically adjust the width of the column to the width of the largest item.
TO USE THE NAME BOX TO SELECT A CELL (Ex 34) The Name box is located on the __________ side of the _______________ bar. To select any cell, click the Name box and enter the cell reference of the cell you want to select.
ADDING A CHART TO THE WORKSHEET (Ex 36)
When a chart is drawn on the same worksheet as the data, it is called an ______________ chart. Excel derives the chart scale based on the values in the worksheet and then displays the scale along the vertical axis, which is also called the __________ or _______________ axis of the chart. Excel also determines the increments of the scale automatically.
TO ADD A CLUSTERED CYLINDER CHART TO THE WORKSHEET (Ex 38) Select the range of data to be included in the chart – in this case, do not include the row totals or column totals. Go to the __________ tab on the Ribbon. Click the _______________ button in the _______________ group to display the gallery. Click the Clustered Cylinder chart type to add the selected chart type to the middle of the worksheet. Reposition, resize, and format (style) the chart as needed.
CHANGING THE WORKSHEET NAMES (Ex 42)
The sheet tabs at the bottom of the window allow you to view any worksheet in the workbook. To change the worksheet names:
Microsoft Excel 2010
Chapter 1
Computer Information Systems
Double-click the sheet tab to be changed.
Creating a Worksheet & an Embedded Chart
Type in the new name, and press Enter. Right-click the sheet tab to display a shortcut menu, then point to Tab Color to change the color of the tab if desired.
TO PREVIEW AND PRINT A WORKSHEET IN LANDSCAPE ORIENTATION (Ex 46) Go to the __________ tab on the Ribbon to open Backstage view. Click the _______________ tab. Verify the printer name that appears on the Printer Status button. Click the _______________ _______________ button in the Settings area and then select Landscape Orientation to change it.
AUTOCALCULATE (Ex 48)
You can easily obtain a total, average, or other information in a range by using the AutoCalculate area on the status bar. DESCRIBE THE FUNCTIONS OF THE AUTOCALCULATE COMMANDS BELOW:
COMMAND
FUNCTION displays the average of the numbers
Count displays the number of cells that contain numbers displays the lowest value displays the highest value Sum
TO USE THE AUTOCALCULATE AREA TO DETERMINE A MAXIMUM Select the range of cells. __________-click the AutoCalculate area on the status bar to display the Customize Status Bar shortcut menu. Click Maximum on the shortcut menu.
CORRECTING ERRORS AFTER ENTERING DATA INTO A CELL
If the entry is short, select the cell and retype the entry correctly. If the entry is long and the errors minor, use the Edit mode to edit the contents directly in the cell, which is known as in-cell editing.
Microsoft Excel 2010
Chapter 1
Computer Information Systems o o
Creating a Worksheet & an Embedded Chart
The keyboard is usually in Insert mode, which means Excel inserts the character and moves all characters to the right as you type. Press the Insert key to change to Overtype mode, which means Excel will replace the character to the right of the insertion point.
FOR ADDITIONAL PRACTICE, COMPLETE THE LEARN IT ONLINE EXERCISES AT: SCSITE.COM/EX2010/LEARN ON YOUR OWN Label the parts of the Microsoft Excel 2010 window displayed below.
3 2
4
7
1
8 1
5
6
1. ____________________
5. ____________________
2. ____________________
6. ____________________
3. ____________________
7. ____________________
4. ____________________
8. ____________________
WORD BANK
Active Cell Columns
Formula Bar Name Box
Ribbon Rows
Status Bar Worksheet Tabs
