Basic Excel Overview

© Mike Splane 2017

 

Videos

Excel Overview (#1)

Time = 9:59

https://www.youtube.com/watch? v=eI_7oc-E3h0

 

Calculation Basics & Formulas (#3)

Time = 13:47

https://www.youtube.com/watch? v=RgvdCHjOKYg

 

Glossary

 

Cell Names

An Excel spreadsheet is similar to a tic-tac-toe grid, only much bigger. The squares in the worksheet are called cells. Every cell has a name. Select a cell with the mouse and you will see a letter highlighted at the top and a number highlighted on the left. The name of the cell is the combination of that letter and number. The name of the cell also appears in the Name Box, above column A.

Moving Around the Worksheet

Pressing Tab moves the cursor to the next cell on the right, Enter moves it to the next cell down, Shift + Tab moves it to the next cell on the left. You can also shift the cursor with the arrow keys and the Home, End, Page Up, and Page Down keys. Use the Ctrl key with the arrow keys to move the cursor the maximum distance. You can also move the cursor into a new cell by typing the cell address in the Name Box. Double-clicking on a cell wall moves you to the edge of a row or column of data.

Entering Characters, Dates, and Numeric Data 

Select a cell. Enter text or numbers by typing in the cell, or in the formula bar that is centered directly above the worksheet. Enter dates as mm/dd or mm-dd for the current year. Enter the date as either mm/dd/yyyy or mm-dd-yyyy for other years.

Editing Cell Contents

·        Select the cell and then click in the formula bar to edit its contents, or select the cell and type in a new entry, or select the cell; then press F2 to edit directly in the cell, or select the cell and double click to edit directly in the cell.

·        Click and drag to move the contents of a cell.

·        Use shortcut keys to undo or redo a step: Ctrl + Z for undo, Ctrl + Y for redo.

Copying

There are several ways to copy and paste. Use the mouse to highlight the contents in the cells that you want to copy from. Do not highlight the formula bar area!  That will delete the cell contents!

·        Press Ctrl + C. Highlight the area to copy into. Press Ctrl + V. Whatever you pasted is still on the clipboard, so you can paste multiple times with this method.

·        Or, click on the Copy icon. Highlight the area you want to copy into. Click on the Paste icon.

·        Or, click the Copy icon. Highlight the area you want to copy into and press Enter, or highlight the upper left corner of the area you want to copy into and press Enter. This will clear the clipboard

·        Move the cursor around until you get a small black cross in the lower right hand corner of the highlighted area.  Click and drag (down or across) to highlight the area you want the formula copied into. Release the mouse. 

Formatting Cells from the Home tab

·        The Font group contains icons for text options (Font, Size, Bold, Italics, and Underline) you’re probably familiar with from Word. It also contains an icon to add borders to cells. The “A” icons grow/shrink the font size.

·        The Alignment group contains text alignment options (Left, Center, Right, Indent, and Decrease Indent). It also has an icon to merge/unmerge cells. Other icons let you position text vertically in the cell, wrap text, or rotate text.

·        The Numbers group contains icons for formatting numbers as accounting style, percents, or comma style. Two icons increase or decrease the number of decimal places. A pull-down menu gives you other formatting options.

·        The Styles group provides options to apply special formatting.

Working With Columns and Rows

·        The Home tab, Cells group has two icons, insert and delete, used to manage formatting for columns and rows. Options include deleting, inserting, changing column width or cell height, and hiding/un-hiding columns.

·        You can find these same options by right-clicking on a column or row heading.

·        You can also use the mouse to change row heights and column widths. Just click and drag between the row or column indicators.

·        To resize a column’s width to match its widest content, double-click to the right of its column-letter indicator.

The Format Dialog Box

·        To quickly select large areas of the worksheet

·        mouse over large groups of cells,

·        or click on one or more column letters to select columns,

·        or click on one or more row numbers to select rows,

·        or click on the gray cell in the upper left hand corner, above the 1, to select all of the cells in the worksheet.

·        After you have selected cells to format, right-click and select format cells to open a dialog box. You can also access this dialog box by pressing Ctrl + 1

·        The numbers tab in the dialog box is used to change the numbering style.

·        The alignment tab lets you choose how letters are displayed within a cell.

·        Other tabs give you more options.

Copying Formatting

·        Select a cell with the desired format. This is called the source cell.

·        Click the Paintbrush icon on the Home tab. 

·        Drag the mouse across cells, row numbers (to select a row), or column letters (to select a column) to apply the format. The format will be copied into the selected cells. Try using the arrow keys to quickly apply the format to any cell next to your source cell.

The AutoFill Handle

·        You can use a built-in feature of Excel, the AutoFill handle, to enter a series of dates; to extend a series of numbers; to copy formulas; and to copy text.

·        Position the cursor in the lower right hand corner of a range. A small + appears. Drag in any direction.

Working with Formulas

A worksheet consists of a set of cells (boxes) aligned in rows and columns. Each cell can contain a string (letters or numbers mixed with letters), a number, or a date. Each cell is referred to by the letter of the column it appears in, followed by its row number. 

 

Column A

Column B

Column C

Column D

Row 1

3

5

 

 

Row 2

Text Message

March 18, 2002

 

 

·        In Column A Row 1 the content of cell A1 is a number with the value 3.

·        In Column B Row 1 the content of cell B1 is a number with the value 5.

·        In Column A Row 2 the content of cell A2 is a label “Text Message.”

·        In Column B Row 2 the content of cell B2 is a number, 37333. The number has been formatted to appear as a date. Why is it a number? So you can add and subtract dates or easily put them in calendar order. Excel assigns a number to each date by counting the number of days since January 1, 1900.

·        A cell can also contain a formula. A formula is a mathematical equation. Formulas always start with an = sign. The result of calculating the equation will appear in the cell. The actual formula appears in the formula bar area, above the worksheet, when the cell containing the formula is selected.

·        If you use a cell name (called a cell reference) in a formula, Excel uses the value found in that cell to calculate the result of the formula. For example, if you enter =A1 + 5 as a formula in cell C1 Excel will look into cell A1. It sees a 3 in that cell, so it converts the formula into the form = 3 + 5. The answer, 8, will be displayed in cell C1. The formula bar will display the formula, =A1+ 5.

 

Remember: formulas always start with = signs! When you enter these statements or formulas in cell C1, here is what will be displayed:

 These strings

in cell C1

Display this

 in cell C1

 

These formulas

in cell C1

Display this

in cell C1

5+3

5+3

 

=5+3

8

B1 + 3

B1 + 3

 

=B1 + 3

8

A1+B1

A1+B1

 

=A1+B1

8

5+B1

5+B1

 

=5+B1

10

 

Order of Operations (PEMDAS)

If a formula contains multiple calculations, Excel calculates the parts of the formula in this order: everything in parentheses, then exponents, then multiplication and division, and finally addition and subtraction, always moving from left to right. For example = 3+4*2 will display 11 because Excel would do the multiplication first, while = (3+4)*2 will display 14because Excel would do the addition inside the parentheses first.

Formulas Combining Strings of Text

To combine text strings use the ampersand (&) symbol in the formula. Strings must be enclosed in quotes. For example =”Splane”&”,”&”Mike” would display Splane,Mike. You don’t have to type a string, instead you can use a cell reference to a cell with text in it.  If cell A4 contained the name Splane, and cell B4 contained the name Mike, you could combine the names using the formula =A4&”,”&B4. Note that cell references are NOT in quotes.

Displaying Formulas

Select a cell and its formula will be displayed in the formula box located above the worksheet area. To view all of the cells as formulas, press Ctrl + ~ (tilde). The tilde key is in the upper left corner of the keyboard. This same keystroke combination returns you to the normal view. There is also a checkbox on the View tab where you can reset the worksheet to display formulas.

 

Working with large groups of cells:

·        Rather than listing all of the cells in a large block of the worksheet you can use a shortcut method to describe any group of cells that form a rectangle.

·        The term range is used to refer to a rectangular block of cells.

·        Enter the cell address from the upper left-hand corner of the range, followed by a colon and the cell address from the lower right-hand corner. Excel will recognize this as a range and will include all of the cells inside its borders when it performs calculations.

·        You can also assign a word to describe a range. Start by mousing-over the cells in the range. Then type the word you want to use to describe the range into the name box and press enter key. The name box is right above column A in the worksheet.

·        If you use a range name in a formula, do not include quotation marks.

 

 

A

B

C

D

E

F

1

 

 

 

 

 

 

2

 

 

 

 

Range 2

 

3

 

Range 1

Range 1 

 

Range 2

 

4

 

Range 1

Range 1

 

Range 2

 

5

 

 

 

 

 

 

 6

 

 

Range 3

Range 3

Range 3

 

 7

 

 

Range 3

Range 3

Range 3

 

8

 

 

 

 

 

 

 

Relative Cell References

  

C

D

E

Comment

4

= A1+B2+C3

 

= C1+D2+E3

Letters change when you copy a formula into a different column.

5

 

 

 

6

= A3+B4+C5

 

= C3+D4+E5

Numbers change when you copy a formula into a different row.

The formula in cell C4 was copied into cells E4, C6, and E6. Note how the letters and numbers change.

 

Cell references, when written like those in the table above, are called relative cell references. Cell addresses written in this format will change when the formula is copied into other cells. The row-indicator numbers change when the formula is copied into a different row. The column-indicator letters change when the formula is copied into a different column.

 

Absolute and mixed cell references

You can write cell references in a different form. Adding dollar signs to cell references tells Excel that you don’t want those cell references to change when the formula is copied. The part of the cell reference following each $ will not change.

The dollar sign does not change the result of the formula. = A1+B2 and = $A$1 + $B$2 will display exactly the same thing.

Cell references can have dollar signs in front of just the number, = C$3, or in front of just the letter, = $C3. These are called “mixed cell references.”  Cell references with dollar signs in front of the letter and number, = $C$3, are called “absolute cell references.”

If you want to create a set of formulas that will always refer to either one specific row: A$1, or one specific column: $B2, use a mixed cell reference.

For a formula with a fixed reference to a specific cell, use an absolute cell reference.

You can combine absolute, mixed, and relative cell references in a single formula. In the following table, the formula in cell C4 was copied into other cells. Observe which row and column numbers changed and note the effect of the dollar signs.

 

 

C

D

E

Comment

4

= A$1+$B2

 

= C$1+$B2

Notice that only the A and the 2 (in cell C4) change. Because of the $ symbol, the others don’t change.

5

 

 

 

6

= A$1+$B4

 

= C$1+$B4

One use for an absolute reference is to divide numbers in several cells by the same divisor, to find percentages. The formula can be written once, using an absolute cell reference to refer to the cell containing the divisor. Then it can be copied into other cells. For example = A4 / $B$7.

 

Working with Multiple Worksheets

Look at the bottom of your worksheet. You are given three worksheets to work in when you open a new Excel file. Click on a worksheet tab to move into a different worksheet.

By right-clicking on a worksheet tab, you can insert and delete worksheets, move and copy worksheets, rename a worksheet, select multiple worksheets to work on simultaneously, and add colors to the tabs.

Formulas Using Cells in Other Worksheets

To write formulas that refer to cells in other worksheets, add the worksheet name, followed by en exclamation mark, before the cell address. For example, =Sheet2!C4 will show the contents of cell C4 in Sheet 2. If a worksheet is renamed, Excel automatically corrects any cell references that referred to cells in the renamed worksheet.

Some common error messages: 

Error messages start with a pound sign (#).

·        #####   If you see railroad tracks, your column is too narrow. Solution - widen the column.

·        #DIV/O!  You are dividing by an empty cell or zero. Solution - fix the formula’s denominator to refer to a cell that contains a non-zero number.

·        #REF! Your formula refers to a range or cell that no longer exists, due to a change in the worksheet.

·        #NAME?  Your formula contains text that Excel doesn’t recognize. This could have many causes. Usually it’s a typo or missing punctuation in a formula.

·        CIRCULAR  If a cell reference in a formula refers to the cell where the formula is entered, Excel will not be use the formula. For example, if =C4+5 was entered into cell C4. The “circular “error message will appear in the status bar.


Examples of commonly used formulas: There is no one right way to write a formula; getting the correct result is what matters. Spaces are not necessary in formulas, but were included in the examples to make them easier to read.

 

This table is used with the sample formulas below:

 

A

B

C

D

 1

25

28

95

 

2

42

91

14

 

3

3

4

5

 

Type of Equation:

Entered in Cell D3:

Result Displayed in D3:

Addition of Two Cells

= A2 + B3

46

Addition of a Constant

= B1 + 25

53

Addition of a Row of Cells

= SUM (A1:C1)

148

Addition of a Column of Cells

= SUM (B1:B3)

123

Addition of a Range of Cells

= SUM (B1:C3)

237

Addition of Scattered Cells

= SUM (A2,B1,C3)

75

Subtraction of a Constant

= C1 – 10

85

Subtraction of a Cell

= B2 – B1

63

Multiplication by a Constant

= A3 * 20

60

Multiplication of Two Cells

= B3 * C3

20

Multiplication by a %

= A1 * .40

10

Multiplication by a %

= B1 * 25%

7

Division by a Constant

= C1 / 5

19

Division by a Cell

= A2 / C2

3

Exponentiation (Squaring)

= B3 ^ 2

16

Exponentiation (Cubing)

= A3 ^ 3

27

Square Roots

=SQRT(A1)

5

Square Roots

= A1 ^ 0.5

5

Cube Roots

= B1 ^ (1/3)

3.036589

Increasing by a Percentage (4%)

= A1 + (A1 * .04)

26

Increasing by a Percentage (4%)

= A1 * 1.04

26

Increasing by a Percentage (4%)

= A1 + (A1 * 4%)

26

Decreasing by a Percentage (8%)

= A1 - (A1 *.08)

23

Decreasing by a Percentage (8%)

= A1 *.92

23

Decreasing by a Percentage (8%)

= A1 – (A1 * 8%)

23

Calculate a Percentage (Part/Sum)

=A3 / $D$3

25% (Format as a %)

Average of a Column

= AVG (B1:B3)

41

Average of a Row

= AVG (A3:C3)

4

Average of a Range

= AVG (B1:C2)

57

Formula referring to a cell in another worksheet

= Sheet2!C4

The contents of cell C4 in Sheet2. 

 

 

 

Name _______________________________      Date___________       Section _______

Check for Understanding – Excel Concepts 1

Y/N I understand how to navigate the Excel worksheet.

Y/N I understand how to format cells

Y/N I understand how to enter (and edit) data into cells

Y/N I understand how to copy and paste in Excel

Y/N I understand how to write and use formulas.

Y/N I understand how to what a range is.

Y/N I understand cell references.

Y/N I understand error messages.

 

Note: I do not need to see a screenshot for this assignment