Chapter Demonstrable Skills
After completing the Excel Chapter 1 portion of the Excel & Data Analysis unit, students will be able to:
| Demonstrable Skill | Learning Activities & Informal Assessments |
Formal Assessments (excluding Exams) |
|---|---|---|
| Enter and modify cell data. |
|
|
| Write basic mathematical formulas. |
|
|
| Select cells for inclusion in a formula using semi-selection. |
|
|
| Copy and move cell contents. |
|
|
| Use AutoFill to fill cell contents. |
|
|
| Display cell formulas. |
|
|
| Insert, delete, copy, and rename worksheets. |
|
|
| Apply font settings including size and color. |
|
|
| Specify page setup options such as orientation, scaling, headers, and footers. |
|
|
| Insert and delete rows and columns. |
|
|
| Adjust cell alignment, size, merging, and text wrapping. |
|
|
| Apply cell borders and use fill colors. |
|
|
| Apply number formatting to cells. |
|
|
Chapter Notes
Introduction
A spreadsheet is an electronic file that contains a grid of columns and rows used to organize related data and display results of calculations enabling interpretation of quantitative data for decision making [1].
A worksheet is a single spreadsheet that typically contains descriptive labels, numerical values, formulas, functions, and graphical representations of data.
A workbook is a collection of one or more related worksheets containing within a single file.
Excel Elements
Name box: It displays the address of the current cell, using name box you can assign a name to a cell or a range of cells [2].
Insert Function: Click to open the Insert Function dialog box. You can choose from different functions and build your formula using the insert function option.
Formula bar: It displays the contents of the current cell to view and edit.
New sheet: inserts a new worksheet to the right of the last worksheet.
Cell: The intersection of column and row is called a cell. Each cell has a unique cell address. The current cell is called active cell.
Enter Formulas into Cells
Formulas combine cell references, arithmetic operations, values and or functions used in calculations [2].
When the cell containing the formula is the active cell then the formula is displayed in the formula bar and its result is displayed in the cell.
Creating Formulas
Always start your formula with an ‘=’ sign. Do not include any spaces before or after an arithmetic operator.
AutoFill allows you to copy the contents of a cell or a range of cells through a row or column by dragging the fill handle [1].
Insert Cells, Columns, Rows
There are two ways to insert a new column or row [1]:
- Click in the column or row in which you want to insert a new column to the left or a new row above, respectively.
- Right click the column(letter) or row(number) heading for which you want to insert a new row above, respectively and select insert from the shortcut menu.
Note: When you insert a new column, it will be inserted to the left of the current column and when you insert a new row then it will be inserted above the active row.
Adjust Column Width
Adjusting column width: the number of characters that can fit horizontally using the default font or the number of horizontal pixels such that the contents of the cell are all shown. There are two types of adjusting the cells and each type has two different methods that you can follow [1].
To accommodate the longest label or value in a column: Position the pointer on the vertical border between the current column heading or the next column heading. When you see a two headed arrow, double click the border. Click on format in the Cells group on Home tab, select Autofit Column Width.
To widen 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.
Click Format in the Cells group on the Home tab and select Column width, in the Column Width dialog box, type the required value and click OK.
Adjust Row Height
The row height is the vertical measurement of the row [1]. To adjust the row height, you can use the same method you used for adjusting the column width
Applying Alignment and Font Options
Alignment refers to how data is positioned in the cell [1]. The Alignment group in the Home tab contains several features you can use to format data.
Merge and Center Labels
Sometimes we want to place a title at the top of a worksheet and center it over the columns of data in a worksheet. Select the range of cells across which you want to center the title and click Merge & Center in the Alignment group on the Home tab [1].
Wrap Text
You can use wrap text to make data appear on multiple lines by adjusting the row height to fit the cell contents within the column width. You can find Wrap Text Option in the Alignment group on the Home tab [1].
Apply Borders and Fill Color
A border is a line that surrounds a cell or a range of cells. You can use borders to offset some data from the rest of the data. Fill Color is a background color that displays behind the data. You can find these options on the Home tab [1].
Number Format
The default number format is General. You should apply a number format based on the values in a cell.
To apply Number Format: In the Format cells dialog box, click on Number dialog Box launcher and select the desired option.
To increase and decrease Decimal Places: Click Increase(or decrease) decimal is the Number group on Home tab [1].
Tips & Tricks
Entering formulas
The most basic thing to remember when writing a formula is to start with an equals (=) sign. When creating a formula, if you don't see a value when you are finished, or you see the formula aligned with the left side of the cell, go back and put an equals (=) sign in [2].
AutoFill
AutoFill dominates Homework #1 [2]. To use it: Create a formula (for example, =A1+B1) and enter it in a cell. Hover over the bottom left corner of the cell you entered the formula in with your mouse. Your cursor should turn into a black plus sign. Left-click, hold down, and drag the mouse to automatically fill in formulas for the entire column or row you need filled.
Resizing rows and columns
To AutoFit resize a row or column, double-click on the border between it and the next column or row where it is labeled [2].
References
- A. Poatsy, K. Mulberry, C. Krebs, L. Hogan, A. Rutledge, and E. Cameron, Exploring Microsoft Office 2013, 1st ed., vol. 1. Upper Saddle River, NJ: Pearson Education, 2013.
-
R. G. Phipps and B. M. Powell, “Excel Chapter 1,” Computer Science 101, May 18, 2016. Available: http://cs101.wvu.edu/materials/units/excel-data-analysis/excel-chapter-1/.