Basic Excel Overview

Part 1

© Mike Splane 2017

Link to Part 2

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

 

Video – for the MAC - Excel Basics 

Time = 7:08

https://www.youtube.com/watch?v=kkNY1ysaOe0&spfreload=10

 

Contents

Overview

Cell Names. 1

Moving Around the Worksheet. 1

Entering Characters, Dates, and Numeric Data. 1

Editing Cell Contents. 1

Copying and Pasting. 1

Formatting Cells from the Home tab. 1

Working With Columns and Rows. 1

The Format Dialog Box. 1

Copying Formatting. 1

The AutoFill Handle. 1

Working with Formulas. 1

Creating Formulas. 1

Order of Operations (PEMDAS). 1

Formulas Combining Strings of Text. 1

Displaying Formulas. 1

Ranges - Working with Large Groups of Cells. 1

Range Notation. 1

Relative Cell References. 1

Mixed and Absolute Cell References. 1

Commonly Used Formulas. 1

Worksheets. 1

Formulas Using Cells in Other Worksheets. 1

Error Messages. 1

Glossary. 1

 

Overview

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 and Pasting

There are several ways to copy and paste. Start by selecting the cells you want to copy from. Use the mouse to select the cells that you want to copy from by clicking on one cell, or by dragging across multiple cells using the mouse.

WARNING! Do not mouse over the text in one cell or in the formula bar area.  That will delete the cell contents!

  • Press Ctrl + C. Highlight the area to paste 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 paste into. Click on the Paste icon.
  • Or, click the Copy icon. Highlight the area you want to paste 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
  • You can also select a cell or range of cells by 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 pasted 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 of the gird, above the 1 in the first row, 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 text and numbers are displayed within a cell.
  • Other tabs give you more options.

Copying Formatting

  • Select a cell with the desired format. The selected cell is referred to as the source cell.
  • Click the Paintbrush icon on the Home tab.  It is labeled Format Painter.
  • Next, use your mouse to select the range of cells you want to apply the format to and click on the mouse. The source cell’s format will be copied into the other cells.
  • A short cut is to click on the source cell, click on the Format Painter icon, and then press one of the arrow keys to copy the format into an adjacent 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.

Autofill Handle Video     Time = 1:17

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

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, is displayed in cell C1. The formula bar will display the formula, =A1+ 5.

Creating Formulas

Remember: formulas always start with = signs! When you enter these statements or formulas in cell C1 this 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 14 because 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 appear in the formula box located above the worksheet grid 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.

Ranges - 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 word “range” refers 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.

Range Notation

When you want to use a range in a formula, rather than listing every cell in the range there is a shortcut, called range notation.

To use range notation, type the range’s top-left cell address, a colon, and its bottom-right cell address.

In the following table, Range 1 is described in cell notation as B3:C4, Range 2 as E2:E4, and Range 3 as C6:E7.

You can also assign a text name to a range and use it instead of using range notation.

 

 

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.

 

Mixed and Absolute Cell References

Adding dollar signs to cell references is useful if you want to create a set of formulas that always refer to cells in the same row, the same column, or the same cell.

The letter or number part of the cell address that has a $ in front of it never changes when the formula is copied into other cells.

 

Cell references can have dollar signs in front of just the number: = C$3, or in front of just the letter: = $C3. These types of cell references are called “mixed” 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.

 

Cell references can have dollar signs in front of both the letter and number: = $C$3. These types of cell references are called “fixed” or “absolute” cell references.

For a formula with a fixed reference to a specific cell, or a specific range of cells, use an absolute cell reference: $C$4.

One use for an absolute reference is to divide numbers in several cells by the same divisor, to find percentages. For example = A4 / $B$7.

 

You can combine absolute, mixed, and relative cell references in a single formula.

In the following table, the formula in cell C4 was pasted 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

Commonly Used Formulas

There is no one right way to write a formula; getting the correct result is what matters.

Use this table with the sample formulas below:

 

A

B

C

D

 1

25

28

95

 

2

42

91

14

 

3

3

4

5

 

 

SAMPLE FORMULAS: Spaces are not necessary in formulas, but were included in the examples to make them easier to read.

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 sheet

= Sheet2!C4

The contents of cell C4 in Sheet2. 

 

Worksheets

Look at the bottom of your worksheet. In older versions of Excel, you are given three worksheets to work with. In newer versions you are given one worksheet.  Click on the + icon to add more.

To move into a different worksheet just click on the worksheet label.

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 typing 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.

Error Messages 

Error Messages Video      Time = 4:28 

https://www.youtube.com/watch?v=1_tr5IK6LpY

 

Error messages usually 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, or a misspelled function name.

·         #VALUE !     You used the wrong data type as the argument for a function.

·         #N/A            Excel couldn’t find a value in a list. This often appears as the result of a LOOKUP type of function when the source cell is empty, or when the items in the reference table are not in alphabetical order.

·         #NUM !        The calculated number is too huge or tiny to display.

·         CIRCULAR    If a cell reference in a formula refers to the cell where the formula is entered, Excel will not be able to use the formula.

 For example, if =C4+5 was entered into cell C4. The “circular “error message will appear in the status bar.

For some versions of Excel for the the MAC, a circle icon will appear in the cell, which can easily be mistaken for a zero, without giving you any error message. BOO Microsoft!

Glossary

AutoFill Handle

The small black plus sign that appears when you move the mouse pointer over the lower right corner of a cell or group of cells.

Cell References

Cell references are pointers to a specific cell in the worksheet. They consist of a letter (or letters) representing the column and a number representing the row. For example C4.

Cell references can include dollar signs.  C4, C$4, $C4 and $C$4 all refer to the same cell. 

Excel automatically alters a cell reference when a formula is copied into other cells, except for the parts of a cell reference that are preceded by the dollar sign. Those stay the same.

Cell references with one dollar sign are referred to as mixed cell references. Those with two dollar signs are called fixed cell references. 

Cursor

A shape showing the location of the mouse pointer on the screen. The shape changes depending on what you are trying to do. For example, when you use the AutoFill handle the cursor changes to a small black plus sign.

Dialog Box

A screen that will open when you need to make complex changes to part of your work. The dialog box will appear automatically when you click on menu items or icons if Excel requires more information to complete a task.

You can also manually open dialog boxes by clicking on the “dialog box launcher” icons at the bottom of the ribbon. These dialog box launcher icons look like tiny squares with an arrow inside. The arrow will be pointing down and to the right.

Formula Bar

The wide white bar that appears above the cells.

Function Keys

Most keyboards contain a row of keys labeled F1, F2, F3 … through F12. These keys are called function keys. Pressing a function key performs a task. For example pressing F1 is the shortcut for opening the help page and pressing F12 opens the file-save dialog box.

Help Options

To get help finding a solution to a problem, or to get more information on how to perform a task, you can click on the question mark found in the upper right part of the screen. Pressing the F1 function key will also work.

Hot Keys

Keyboard shortcuts are also known as hot keys. For example pressing the Shift key and the F11 function key together will add a new worksheet to the workbook. I show the shortcuts in brackets [Shift + F11]

Keyboard Shortcuts

Usually you click on an icon to perform a task, but you can often perform the same task by typing a short series of keystrokes. If you move the cursor over an icon and hold it there you will see the shortcut for that icon.

Keyboard shortcuts to find the icons used in these lessons will be shown inside brackets and in red font. [ALT H  B] Press the keys one at a time, in a sequence, not all at the same time!  The plus signs are not typed when you use the shortcuts. The letter combinations ALT, ESC, CTRL, TAB, HOME, END, and SHIFT each refer to a single keystroke.

Pressing the ALT key will display several keyboard shortcuts at the same time.

Note: The MAC does not follow the same set of keyboard shortcuts. Shortcuts starting with ALT are only for use in a PC.  For keyboard shortcuts starting with CTRL use the Command key.

Get help here:

Office for Mac Keyboard Shortcuts - Excel

https://www.youtube.com/watch?v=60tEVwXiTEs

25 Basic Mac Keyboard Shortcuts

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

Menu

When you click on the right-hand button on your mouse (right-click) a list of options will appear on the screen. The list is called a menu. To use the menu, click on any item in the list. To close the menu, click on any other part of the display area.

If you see a small triangle, pointing down, on the right hand side of an icon, clicking on that triangle will open a menu. This type of menu is called a dropdown list.

Name Box

A small white box that appears above column A, next to the formula bar.

Quick Access Toolbar

A small rectangle appearing in the upper left corner of the screen, containing a small group of weird symbols. The symbols are called icons.

Range

A group of cells that form a rectangle.

Ribbon

The area across the top of the screen that is about an inch high and is covered with weird symbols. The symbols are called icons.  You can move the cursor on top of an icon to see what it will do when you click on it.

The ribbon is too long to show all of the icons at the same time, so it is divided into sections. The names of these sections are shown in tabs just above the ribbon. Clicking on a tab will change which set of icons you see.

In Office 2010 the first tab in the ribbon is named File and the second tab is named Home. The ribbon can be hidden. If you do not see it, you can unhide it by clicking on the icon on the upper right side of the window that looks like a smile.

The ribbon for Office 2007 has some small differences from the 2010 ribbon. The first tab in the 2007 version, File, is shown as a circle with a four-color flag inside it. The method of un-hiding the ribbon is also different. To unhide the ribbon click on the right end of the quick access toolbar

If you are working with a graphic, additional tabs will appear on the ribbon. Icons on these tabs are used for formatting and fine-tuning the graphic.

Right Click

Mice and most laptops touch-pads have two buttons. The right one can be clicked on to display a popup menu. If your laptop touch-pad does not have the two buttons, you may be able to tap it with two fingers, at the same time, to show the pop-up menu.

Saving your work

If you want to keep your Excel worksheets to use them again you need to save the Excel workbook as a file.  Pressing the F12 function key is a quick way to access the Save As dialog box. In the dialog box you create a name for the file, select a format for the file, and select a location for the file. The most commonly used locations for storing files are either on the desktop or in the My Documents library.

Screen Clip or Screen Shot

Both these terms refer to the same thing, a picture showing the contents of an open window on the desk top.

The keyboard shortcut method to create a screen shot is to:

1. Click on the window you want to take a picture of.

2. Press the ALT key and the Print Screen key to take the picture. (Command Shift 4 in a  MAC)

3. Switch to your Word document window.

4. Press Control and the V key to paste the screenshot into your document.

Scroll Bar

Two rectangles, one horizontal and one vertical, at the edge of the screen. Clicking in the scrollbar will cause the area of the worksheet that is displayed to shift up or down, or left or right.

Status Bar

A rectangle across the bottom of the screen that contains icons and information about your Excel worksheet.

Troubleshooting Methods

 

If Excel freezes as you are trying to perform a task, or the icon you are trying to use is inactive, you are probably in the middle of some other task within Excel. Hit the ESCAPE key two or three times and try again.

If you make a mistake while working in any Office application you can undo your most recent step by pressing Ctrl and Z at the same time. You can continue pressing this keystroke combination reverse multiple steps.  Pressing Ctrl and Y is the shortcut to redo a step.

If your application freezes completely, press CTRL and ALT and DEL at the same time. Open the Task Manager. Closes the application in the task manager. Reopen the application.

Workbook

A set of Excel worksheets (pages) saved together in one file.

Worksheet

An Excel page.  You can see the names of each worksheet at the bottom of the screen. The names are shown on tabs. Clicking on a tab will move you into that page.

A worksheet name can contain up to 31 characters. It must not contain a colon, back slash, forward slash, question mark or asterisk. Forbidden  : \ / ? * The name can contain spaces but that can cause problems when writing formulas so it is good practice to not use them.

You can click and drag on worksheet tabs to change their order, or right-click on them for a menu listing other worksheet-related tasks.