Lesson 1- Final Cut Pro Basics & Layout

A typical Final Cut Pro workflow would look something like this:

- Planning: Includes setting up an editing system, acquiring media, determining the scope of effects, and picking out a log and capture method.

- Setting up: Configuring software, setting up hardware. 

- Logging and Capturing

-Logging: the process of identifying which shots on tape you want to have on your disk for use in editing. This involves labeling, descriptive notes,  and comments to organize your original source material.

-Capturing: the process of getting your source media from your camcorder or deck onto your computer's harddrive. 

Quicktime, audio, and graphics files can also be imported at any time during a project.  

- Editing: Editing includes, organizing clips, editing clips into a timeline, fine-tuning edits, adding transitions, and adding music tracks. Most editors begin with a rough cut which is a quickly assembled timeline of events.

- Mixing Audio: includes cleaning up dialogue, adding sound effects, music, voiceover, and adjusting or mixing the levels of all the different clips. This can also be done through other programs such as Soundtrack or Logic. 

- Adding Effects: this tends to be one of the most time-consuming tasks in final cut pro. Effects can include things such as applying filters, color correction, creating titles, compositing to combine multiple levels of images, motion effects,  blue-screen, changing the speed of clips, etc.

- Outputting: This includes print to video and quicktime exporting for DVDs or the web.

Building Blocks:

- Media files: located on your hard disk, these correspond to the footage you captured.

- Clips: these are files that reference your media files and tell final cut where on your disk it can find the media.

- Sequences: holders in which you can arrange clips in chronological order to convey a story or message.

- Bins: Used to organize clips. This includes not only video, but audio, graphics and generated flips as well.

- Projects: include all the elements of a movie (clips, sequences, and bins). 

Initial Setup:

When you first open FCP, you have to specify certain settings to tell final cut how to interact with your video setup. Easy setup is a collection of settings FCP uses for controlling a video device, capturing media, editing, and exporting video. East setups are based on the most commonly used video configs (such as DV-NTSC & DV-PAL).  

- NTSC: national television systems committee, is the TV and video standard used in the america, taiwan, japan, and korea. 

- PAL: Phase alternating Line, the television standard used in most of europe, brazil, argentina, and china. 

- SECAM: a standard based on PAL, used in France, haiti, vietnam, poland. This does not work with FCP, you can use FCP to edit in PAL and later convert to SECAM. 

General layout of FCP:

- Browser (command 4): usually on the left top of the screen. Used to organize the clips in your project. Where your Bin is located.

- Timeline (command 3): usually located at the bottom portion of your screen. Used to edit and arrange your sequence. 

- Viewer (command 1): Located on the middle-top of screen. Used to preview clips before you place them in a sequence on the timeline. 

- Canvas (command 2): Located at the top right of the screen. Plays back changes you make to your sequence in a timeline. 

- Tool palette: middle-right of screen. These are your tools to edit and modify items in the timeline.

- Audio Meters (option 4): middle-right of screen. Let you easily monitor audio levels. 

Lesson 2- Setting Up A Project

Creating and Saving Projects and Sequences

The first thing you want to do in FCP is create a new project. All the elements of your movie, including clips and sequences, are stored in a project. 

When you create a new project a tab called "untitled project 1" is created in the browser to represent your project. A new black sequence is also automatically created and named "sequence 1"

To create a new project, choose "File" from the man menu and then select "new project" (Shift command N)

To create a new sequence, choose "File" from the main menu and then select "New." After, click "Sequence" from the pop-out menu. You can also hit command N on your keyboard. Enter a new name for your sequence and hit Return, give the sequence a descriptive name so you know what it contains. 

Saving often in Final Cut Pro is key, to save a project make sure the project's tab is first selected in your browser. Then, go to "File" in your main menu and click "Save Project" (Command S). IF you haven't yet named the project a dialog box will appear for you to enter a name and save location for the project. By default, the project will be saved in your "my documents" folder.

Opening and Closing Projects and Sequences

Opening and closing projects and sequences works very similarly to most other programs. One thing to note is that by default, FCP opens the last project you worked on when you open up the application. 

Projects

To open a project go to "File" in the main menu and then click "Open." Locate and select the project file and then click "choose." 

To Close a project click the projects tab to bring it to the front. Choose "file" from the main menu and then click "close project (CTRL W) ….OR…. right click the project's tab then choose "Close tab" from the shortcut menu. 

Sequences

To open a sequence in the browser, double click the sequence you wish to open. A tab with a sequence name will then appear in the timeline. 

To close a sequence right click the sequence's tab in the timeline, then choose 'close tab' from the shortcut menu.

Renaming a clip or sequence

Renaming clips and sequences only changes the filenames in your project, it does not change the names of the source media files on your computer's hard disk.

select the clip or sequence in the browser, click the item's name, then type a new name. 

* If you rename a clip's media file, the link between the clip and the media file can break. In this case, the clip is said to be offline, and a red slash appears over the clip's icon n the Browser. If this happens you need to reconnect the clip and it's media file. 

Selecting items in the browser

To select a single clip, simply click on the item.

To select a group of adjacent clips, select and teem, press and hold down the shift key, then select the last item.

To select multiple, nonadjacent clips press and hold down the command key while clicking multiple items.

 Drag your mouse over multiple clips 

Using Bins

Bins are similar to folders for your browser. They can be use to group clips and organize your project.

To add a bin, click on the tab for the project to which you want to add the bin. 

Choose "File" from the main menu, select "new" and then click "bin" from the side menu that appears. 

You can also right click the name column, then choose "new bin" from the shortcut menu.

Enter a name for the new bin and press return.

Deleting or moving items from the browser

Deleting a clip from a project does not delete that clip's media file from your hard disk, nor does t delete any other affiliates of the clip appearing in that project, including clips used in sequences. 

To delete a clip, sequence, or bin from a project, do one of the following: Select the item & then ht delete, or right click the item you want to delete and then choose "Cut" from the shortcut menu. 

Specifying Settings and Preferences

Preferences  are options that allow you to modify how particular FCP features behave. This can be changed frequently.

Settings apply to capture, sequence, rendering, real-time, output, hardware, and format configs in FCP. Once you set up for a particular video format and device, these settings are adjusted infrequently. 

Viewing User Preferences

A preference modifies how a particular FCP feature behaves. Most can be turned on or off at any time, while others accept a value  (such as a number) of undo levels. 

To open the user preferences window, choose "final cut pro" from the main menu and then click "user preferences" (or option Q)

The user preferences are divided into several tabs

General: Settings in this tab control a variety of features, such as alert messages during capture, the number of undo levels  allowed, and autosave vault and auto render options. 

Editing: this tab contains preferences useful while editing, such as trimming and audio keyframing options. 

Labels: lets you change the names associated with the colored labels that are available within final cut pro.

Timeline options:  The default display options used when a new sequence is created. This tab is where you specify the default number of video and audio tracks for new sequences.

Render Control: Allows you to choose the render quality of new sequences you create.

Audio Outputs:  Lets you choose the default audio output preset used for new sequences. If none of the audio output presets match your audio config, you can create your own custom preset. 

The timeline options, render control, and audio outputs tab contain the default prefs used when you create a new sequence. Once a tab has already been created you change it's settings by choosing "Sequence" from the main menu then clicking "settings." Options in the user preferences establish the default preferences for NEW sequences, not ones that exist already.

Viewing System Settings

System settings cover settings mainly related to computer setup you are using and how FCP interacts with it. System settings affect fundamental components of your FCP system.

To open the system settings go to "Final Cut Pro" in your main menu and then click "System Settings" 

Just as the user preferences have tabs, so do the system settings:

Scratch Disks: This tab lets you choose where you want to capture video and audio media files, and where to store the render and cache files that FCP creates. You can also specify other settings related to the size of captured and exported files and the minimum space allowed on scratch disks. You can specify a max. of 12 scratch disks.

Search Folders: When Reconnecting clips to media files, FCP uses the folders assigned in this tab to search for media, allowing you to limit your media search to specific media drives and folders. This is good if you have a lot of disks connected but want to restrict which folders FCP uses. 

Memory & Cache:  this tab can be used to specify various memory usage settings, including the percentage of RAM available for use by FCP.

Playback Control: These settings balance visual playback quality and the number of effects that can be played back in real time. Playback control settings affect all sequences and projects currently open. 

External Editors: FCP allows you to directly open clips' media files in other applications. This tab allows you to assign media file types, such as still images , or audio files, to external apps for editing and processing outside of FCP.

Effect Handling: Allows you to assign real-time effects processing to third party video interface cards (hardware processing) or to FCP (native software processing). Each codec that appears ere can be assigned to a video interface card or FCP. 

Lesson 3- Logging & Capturing

Capturing Video

The process of bringing your content from a media source to your computer is called CAPTURING. The related progress is called logging, this involves entering descriptive information about the clips you plan to capture or already have captured. You do this using the Log & Capture window. 

There are two ways to go about logging and capturing. One way is to capture everything and then log later using columns in the browser, the other way is to use the log and capture window to log all of the descriptive information before capturing. The 2nd way is ideal, because it reduces the amount of extra video captured thus reducing the amount of space wasted on your hard disks. 

Determining a method:

1) Log a clip as you view your footage, and then capture each clip as you go. *Most thorough, but most time consuming.

2) Log all of your clips as you view your footage, then capture them all at once as a Batch Capture

3) Capture an entire tape, and then add logging information after. *Most popular, but least organized method. 

Logging Tab

Log Bin: Contains the name of the bin indoor project where logged clips will be placed. There can be only one logging bin at a time, no matter how many projets are open. IF you click the button the bin opens in its own window. 

Up: Click to change the logging bin to one that is a level up from the currently selected logging bin. If the logging bin is at the highest level, clicking the button assigns the project itself to be the logging bin. 

New Bin: Click this to create a new bin inside of the currently selected bin and set it as the logging bin. 

Reel:  the reel name corresponds to the actual tape that the source media is on. all clips require a reel name. Make sure you enter the proper reel name before you capture. The reel name is necessary any time you need to go back to your original tapes. 

Slate: This increments the latest number or letter in the field. if the current field doesn't end with a number, a number "1" is appended. IF the field ends it a space and a single character (such as "A"), the character is incremented alphabetically. 

Name: This field is g enervated automatically from the description, Scene, Shot/Take, and Angle Fields-- but only the fields whose checkboxes are selected contribute to the clip name. Underscores in the clip name separate the content of each automatically included field.  The clip name "Man Talking 3_23_2" breaks down into the description "man talking" scene "3" shot/take "23" and angle "2." 

Checkboxes: Indicate fields you want to include in the name of the clip

Log Note: Comments and editorial notes about the clip in the field

Good: This checkbox is for clips you believe are worth capturing, or if you want them to be remembered that you consider them useful for the project. You can use the "Good" property in the browser to search for these clips.

Prompt: this has FCP confirm the clip name, log notes, and good property before the clip is logged.

Markers: Markers within clips note significant parts for future reference.

disclosure triangle:  views or hides marker list and controls

Marker: enter a name or comment to go with the marker in this field. The marker name remains until you change it.

Set Marker: Once you've set the marker In and Out points, click this button to create a new marker.

Set Marker In: Set an in point, or enter a timecode value in this field.

Set Marker out: Set an out point, or enter a timecode value in this field.

Update: Use this to make changes to a marker. Click to select the marker in the list, make your changes, then click the update button. The marker in the list then displays the new info. 

Clip Settings Tab

Video: enables video capture.

Audio: enables audio capture.

Input channels: shows the number of audio channels available from the device you are capturing from. If you are logging and no device is connected, you can choose the number of channels your capture device has. 

Preview: MOnitors audio while you log and capture. Incoming audio is routed to the audio output specified in the output area of the sound pane in system prefs. 

Individual channel meter: each audio channel meter shows the current level of the incoming audio signal.

Capture audio channel control: Tells FCP to capture the corresponding audio channel. 

Stereo/mono control: Tells FCP to capture the two corresponding audio channels as a stereo pair. When disabled, you can select one or both audio channels for capture. In this case, each channel is captured and handled as a discrete mono channel. 

Capture Settings Tab

Device Control: If you are capturing clips from a different format then specified as a device control preset in your current Easy Setup, you can change format in this pop-up menu. 

Capture/Input: lets you change the capture preset in your current easy setup and specify the video input settings if available. 

Scratch Disks: This button lets you change your scratch disk before you capture media. I.E. to add another external drive to your system since you last set up FCP. 

Logging a Clip

**Make sure to enter the correct Reel name in the log and capture window**

1) Play the tape, use the transport jog and shuttle controls to navigate through the footage. 

2) Set the In point to indicate the beginning of the clip you want to capture by doing one of the following: 

Press I, click the "mark in" button, enter a timecode value in the clip in Point timecode field at the bottom of the log and capture window. 

3) Set the out point for the end of the clip you want to capture by:

Pressing O, clicking the "mark out" button, or entering a timecode value in the clip out Point timecode field at the bottom of the log and capture window. 

Entering Logging Information

Once you've set in and out points for a clip from your video tape, you can enter logging info that you can use later when you edit. 

1) Use the log bin to choose where logged clips are stored

2) In the Reel field, enter in the reel name of the current tape that's in your DV camcorder or Deck and press Return.

3) to automatically increment the reel number by one, click the slate button next to the reel field. 

4) Enter a brief description of the clip in the description field

5) enter scene and shot/take numbers

6) enter a camera angle number if this footage was part of a multicamera shoot.

7) click the slate button next to any fields you want to automatically increment by 1

8) select the checkbox next to any of the above properties you want to include in the name of the clip.

9) In the log note field, enter editorial comments or notes.

10) Select the good checkbox if you think the current clip is noteworthy

11) Select the prompt checkbox to make FCP confirm the clip name, log notes, and good property before the clip is logged. 

At this point you have two options -- 1) to capture the clip, 2) to log the next clip and then later batch capture the clips. 

Lesson 1

Microsoft Excel Lesson 1

What is Microsoft Excel?

Excel is a spreadsheet program that you can use to organize data and numbers.

What can you do with Excel?

Excel lets you create spreadsheets that can perform complex calculations instantly. You have complete control over your spreadsheets. Size, color, style, etc are all customizable. Excel also can create graphs, charts, and tables. You can import charts from other programs and create a report to illustrate the data.

To Start Excel double click on the excel icon on your desktop or navigate to "Microsoft Excel" using your Windows Start Menu.

The Excel Environment

Excel consists of two windows: the application window, and the workbook window.

The Application Window- is the outer frame of the program. It consists of the Title bar, quick access toolbar, ribbon, formula bar, and the workbook window. When you close the application window, Excel will close. It will then prompt you to save any open spreadsheets.

Workbook Window- the default spreadsheet object in Excel. Consists of three spreadsheets by default. The workbook window opens up within the application window. This is where you create and modify spreadsheets. You can have multiple workbooks open in excel at the same time. The workbook window also contains the scroll bar, the "First Sheet," "Previous Sheet," "Next Sheet," and "Last Sheet" buttons. You can close the workbook window without closing Excel.

A screenshot of the Excel Environment can be found on page 8 of your textbook.

Title Bar: The blue bar that runs at the top of the screen. This is the standard Windows title bar that you see at the top of every program. It allows you to open, close, minimize, and maximize the screen.

Ribbon: allows you to perform all of the commands available in the program. The ribbon is divided into tabs, these tabs each have different groups of commands. Click on the buttons, boxes, and menus in each group to execute a command. Double-click on the active tab within the ribbon to hide or show the contents of the ribbon. The primary tabs in the ribbon are: File, home, insert, page layout, formulas, data, review, view, and add-ins. There are also contextual tabs that appear as you do certain tasks.

File Tab & Backstage View: replaces the file menu in older versions of excel. Clicking the File tab in the ribbon opens a view of the file called ‘backstage view.’ You can perform all of your file management here. Including things like: save, open, and create new documents.

Scroll Bars: The scroll bars function similarly to any other program on the computer, it lets you change the view from one end of the document to another.

Quick Access Toolbar:located above the ribbon by default. Click the ‘customize quick access toolbar’ button at the right end of the toolbar and then select the ‘show below the ribbon’ command. The quick access toolbar gives shortcuts to commonly used commands. You can also customize it to add commands you use most frequently.

The Formula Bar: located underneath the ribbon. Consists of the "Name Box" which displays the address of the currently selected cell in your spreadsheet. To the left of that is the "Insert Formula" button which looks like the function sign (fx). The White bar after that button is where you can see, edit, or create a forumla to enter into your currently selected cell in the spreadsheet. They will also display here so you can edit or change the formulas after creating them. This bar is where you can see the contents of formulas.

The Workbook Window: Displays the currently selected spreadsheet file. Whichever sheet you select from the "Spreadsheet Titles" section is shown in this workbook window. A spreadsheet stores information much like a database table. A single sheet in a workbook is arranged as a grid that contains 16,384 columns, and 1,048,576 rows. The first 26 columns are labeled A through Z. After that they are named AA through AZ, then BA through BZ, and so on (up to AAA through XFD). The rows are sequentially numbered 1 through 1,048,576. The intersection of a column and a row is called a cell. Cells are where you enter information. They are referenced by their column and row location within the spreadsheet using an ID known as a "cell address."  Which is the Cell Column Letter + the Cell Row #.  You can only enter data in to the currently selected cell. When a cell is selected it will have a thick black border around it and its cell address will appear in the "Name" box in the formula bar. You can only have one cell active at a time for data entry, for formatting you can have multiple cells. You can also select multiple cells to manipulate data inside (For example adding a range of cells with data together).

Status Bar: The bottom of the application window is the status bar. It provides you with information about the spreadsheet you currently have open. It will also tell you what type of mode you are in. There are three modes in Excel: READY, EDIT, or ENTER. Ready mode means Excel is ready to do anything. This is the mode you want to be in before you begin a task. Edit mode means you currently have a cell you are editing, but have not moved from the cell in order to finish entry. Many commands are unavailable while in edit mode. Enter mode is much like edit, but is shown when you are entering information into a blank cell.  The status bar also contains the zoom slider and views shortcuts. Choose which statuses you wish to display by right-clicking your mouse anywhere in the status bar to view the ‘customize status bar’ panel.

Workbook View Buttons:  three buttons located towards the lower right corner of the application in the status bar. Click the buttons to change the working view of your workbook. By default, excel opens in ‘normal’ view. Switch between different views in your workbook by clicking on the view buttons. The views are “normal, page layout view, and page break preview.”

Zoom Slider: use the zoom slider to adjust the magnification level of the workbook. Click the plus or minus buttons, slide the bar, or enter in a percentage to change the zoom levels.

The mini toolbar:The mini toolbar only appears when you select text within a cell and hold your mouse pointer over it. It will appear as a small dimmed-out toolbar, if you roll your mouse over it it will appear solid. You can then select the buttons to perform commands 

Lesson 2

Creating Basic Spreadsheets

Creating a new Workbook

Click the File tab within the ribbon and select the ‘new’ command. In the available templates section that appears to the right, you can choose to start a new workbook from the templates available OR you can choose to create a new blank workbook. Select your choice and then click ‘create.’

Saving Workbooks

To save for the first time you must click "Save As…" in the file tab of the ribbon. This brings up the "Save As" Dialog box. In this dialog box you should create a name for your spreadsheet and a location for the file to be saved. All subsequent saves can be made using the ‘save’ command (in the file tab of the ribbon, or in the quick access toolbar). Excel will save the changes automatically to the existing file.

Closing Workbooks

To close a workbook, either go to file tab in the ribbon and then click "close" or hit the X in the upper-right corner of the workbook window. DO NOT click the red X on the title bar.

Opening Workbooks

To open a workbook, go to the file tab in the ribbon and then click "open.” This brings up the "open dialog box." The "look in:" bar lets you pick the location of your file. The main window lets you see the contents of the folder you are currently looking in. "My documents" is the most frequently used location to save excel files. To open multiple workbooks at once, select both files by holding down the CTRL key if they are not adjacent. If the files are next to each other, you can hold the shift key to select the first and last file-- all files in between those files will be selected as well automatically. After you are done selecting which files you'd like to open click the OPEN button on the lower right.

Selecting Cells

When you open a new workbook, the active cell is always cell "A1." You can use the mouse to click into any cell to make it the active cell. You can also use the keyboard to move the active cell cursor.To see a list of keyboard strokes and their corresponding movements in regards to the active cell see page 26 in your textbooks.

Entering Text into a Cell

Any combo of numbers and letters are treated as text in excel, not numbers. Text automatically left aligns in the cells. To finish an entry you MUST leave the cell and go back to "ready" mode. For example, if you add data to a cell without leaving it this limits your ability to format and modify the information in the cell because options are limited in both "edit" and "enter" mode.  Long text entries appear to spill over into the next cell if the next cell is blank. If you move into the next cell and begin typing, the previous cell's entry will look as if it has disappeared. This is normal, it has not.

Entering #s into a Cell

In excel there can be no text in numeric entries. Numbers should be entered with no formatting. Only use digits and decimals. Any number signs, commas, etc should be added later when you format cells. You can however, type a minus sign in front of a number to indicate a negative. Numeric entries also have issues with cell content versus cell display. Long numerical entries will turn to ##### signs or become expressed in scientific notation. This happens when the entry is too long to be displayed correctly in a column. You can widen a column to fix the display. However, this will not fix an entry that changed to scientific notation. In this case, you have to change the formatting to get it back to normal.

Autocomplete

If you have a column of unbroken data (no empty cells) that has the same text values repeating, excel will offer to fill them in for you as you type down the column.  For example, if you were listing people by departments n your company, you would have a column of departments that would probably contain duplicate entries within the column. With the "Autocomplete" feature, excel notices the first few letters of a repeating entry being typed in, it will try to finish the entry for you by displaying its suggestion in a black highlight. If you want to use the suggestion press "Enter" on the keyboard to accept it. If you do not want to use it, keep typing and the suggestion will go away.

Pick from List

If you have a column of unbroken data (no empty cells) that has the same text entries repeating in various ways, Excel can display a list of previous entries within that column, in alphabetical order, that you can pick from. That way you don't have to retype duplicate entries within that column. Right click the cell at the end of the list and select "Pick from List..." A list of all the prior entries in that column show up for you to choose from.

Using the “window” command group:

The window command group is to help you manage multiple workbooks. In excel, multiple workbooks can be opened at once to accomplish tasks such as copy-pasting data between them.

Click the ‘view’ tab in the ribbon, inside the window group there are commands that allow you to manage your open workbooks.

The new window button opens your current workbook in a new window. This can be useful when you want to use two different views of your workbook simultaneously in two separate windows.

“Arrange All” arranges all open workbooks automatically on the screen. There are multiple organization layouts for this button including tiled, horizontal, vertical, and cascade.  Click the option you’d like and then click OK.

To view information in two different sections of a very long worksheet in a workbook simultaneously you can select the cell that is below the row and to the right of the column  that you want to freeze. Click the “freeze panes’ button and choose the ‘freeze panes’ command from the drop down menu. Now when you scroll through the worksheet, the information in the frozen panes will not scroll. You can also select “freeze top row” or “freeze first column” commands from the freeze pane drop down in order to simply freeze the top row or first column in your worksheet so you can view row or column tittles when looking at data. To unfreeze the panes, choose the unfreeze panes command from the drop down menu of the freeze panes button.

 The “split” command gives you the ability to split the excel worksheet into different panes. Each pane contains it’s own vertical and horizontal scroll bars so you can scroll each pane separately to view different information on either side. Click the “split” button in the window group to split the current worksheet into four separate panes. Click the split button again to remove the split.

To split the screen in half, click into any cell that is below the row, or to the right of the column where you want the split to appear. Double-click the small ‘split’ button at the right end of the horizontal scroll bar to create a vertical split, or double click the small ‘split’ button at the top of the vertical scroll bar to create a horizontal split.

Remove a split from the worksheet by double-clicking it.

Using Ranges

A group of selected cells is a Range. Ranges are used as a reference to the information stored in the cells in the group.  For example, you may have a column of sales figures. You could reference the range of all cells in that column that contain sales data and then add them together to find the total sales in the selected range.

You can reference either single ranges or multiple ranges. Multiple ranges can be either next to each other or non-adjacent. When you make a range reference, which is a critical aspect of writing formulas, each range is individually identified by its upper left cell address and lower right cell address. Separated by a colon symbol. For example, to reference the range of cells from A2 through A5 you would write A2:A5. To refer to multiple cell ranges simultaneously, separate the references with a coma. So if you wanted to references B3 to C5 simultaneously you would want to write A2:A5, B3:C5.  This is useful both for writing formulas and for actions that require you to select cells prior to implementing the action.

When a single range is selected, it will appear with a black border around the cells in the range. Those cells will turn a light purple or blue color. The active cell within the range will appear as a normal white cell, but will be encased within the black border as well. A range will stay this way until you click into another cell, or move your active cell w/ the keyboard arrows. This will deselect the range. You can name permanent cell ranges for spreadsheets that you use frequently. This can make the function of the range appear more clearly, and allow you to use the name of the range in place of the range addresses.

Selecting Ranges

You can use either your keyboard or the mouse to select a range. With your keyboard, select the first cell in the range, hold the SHIFT key on your keyboard and then move the cursor with your keyboard arrows to extend the range until it is the size you like. Then release the SHIFT key on your keyboard. With your mouse, click and drag from the first cell in the range to the last cell. A quicker method is using a combination of both. Click into the upper left cell in your range and press and hold the SHIFT key on your keyboard, then click the cell in the lower right corner of your range. All the cells between the two will be selected.

Selecting multiple non-adjacent ranges

To select multiple non-adjacent ranges use the CTRL key on your keyboard along with your mouse. Select the first range that you would like, next hold down the CTRL key and then use the mouse to create another range. Release the CTRL key when you are done selecting all of your ranges. If you click without holding the CTRL key or using the arrow keys you'll deselect all of the ranges.

Entering info into a selected range

The cell within a selected range that is white is the active cell. This is the cell you can enter information into. You can move to the next cell within the selected range  or ranges by using the TAB key to move right or the ENTER key to move down. Holding down SHIFT then pressing either of those two keys will move the acetic cell cursor in the opposite direction.

Using AutoFill

AutoFill allows Excel to automatically fill in a repeating pattern that you establish. I.E. months in a year, days in a week, or any repeating numerical pattern. Select the cell or cells that establish the pattern. Look for the "Fill handle" in the lower right corner of the active cell or cells. Move the white cross cursor towards this box, and try to fit the upper left corner of this cross up to the lower right corner of the cell or cells where the fill handle is. When the cursor is in place, it will turn into a thin black cross (+). This is the fill handle cursor. Now, click and drag the handle out either horizontally or vertically to fill in the selected range. If the cell contains the beginning of a pattern that Excel recognizes it will fill in the range with the rest of that pattern. The names of the rest of that pattern will appear as you drag over each cell in the range in a small yellow tag called a screen tip.

If the pattern is not recognized Excel simply copies the first cell's contents into the rest of the cells in the range. This is actually a very good way to copy the same information into a row or column of cells quickly.

Excel will recognize user-established patterns. (I.E you type 3 into a cell and then 6 into the ex cell and select both as a range, you could drag out the rest of the pattern which would be increasing by 3.

Lesson 1- Video Production Basics

There are Four Main Stages in Video Production

            - Development

            Development is the stage in which you begin creating the idea for the project. This includes but is not limited to creating an outline, and a script.

            Typically, a film or TV production would have an outline, a pitch, and a treatment before it is turned into a script.

                        -Outline: An outline is almost exactly what it sounds like. It is a step by step synopsis or outline of the film's script. It is usually kept short, to a few pages.

                        -Treatment: A treatment is a summary of the film. Usually about one page long. It is very similar to doing a book report on the film.

                        -Pitch: A pitch is a short one paragraph description of the film, meant to peak the interest of investors and producers. It can also be used for promotional material.

The Script or Screenplay contains the dialogue and instructions for the production. When you write a script, you want to include descriptions of characters, dialogue between characters, character actions, and scene descriptions. However, you do not want to include camera direction unless you know you will be the person directing the film. Someone who writes the script is called a Screenwriter. The act of writing a script is Screenwriting. If you are interested in screenwriting I have included a link on script formatting and a link to example scripts.

            - Preproduction

            Preproduction is the stage where you begin to prepare the script for production. It mainly entails planning. This includes but is not limited to; storyboarding, assembling a crew, props, costumes, and budgeting.

                        -Storyboard: A storyboard is an illustrated scene by scene play-out of the production. It is usually made by Concept Artists.

- Production

            Production is the stage which most people consider the most exciting. This is where the film or video is created and shot. At this point, everyone on the crew should know how each scene should be set up and what the basic look of the film or video will be. Each Crew member has a specific job.

                        -Director: the director is in charge of managing the entire production and the ultimate vision of the piece.

                        -Producer: The producer is typically in charge of the organization aspects of the production and getting all of the crew members to the correct place at the correct time.

                        -Director of Photography: The director of photographer is also known as the cinematographer or the DP. The DP is the head cameraman, he will often have assistants.

                        -Gaffer the gaffer is in charge of the lighting.

                        -Art Director:  The art director is in charge of the artistic choices in the film, including props and backgrounds.

- Post-Production

            Post-Production is the stage where the raw footage that was shot during post production is then turned into a final film. This includes but is not limited to logging the film, editing the film, special effects, and soundtrack.

                        -Logging: Logging the film involves taking the raw footage and organizing it in a manner that is easy for you to handle.

                        -Editing: Editing the film involves going through each of the takes, and deciding which are good and which are bad and in what order to put them in. Editors have one of the largest impacts on a film's final look.

                        -Special Effects: Special effects can be done in both production and in post-production. Special Effects in production include in-camera tricks, certain types of animation, puppetry, etc. Special effects in post-production typically deal with compositing, computer animation, and motion work.

                        -Soundtrack: The soundtrack is another extremely important aspect of video production. The soundtrack includes the music, sound effects, and dialogue of the film or video. Even though it is often overlooked, a good soundtrack can completely change the mood of an entire film.

Pre-Production Basics & the 5 C's

Recap

-Outline: An outline is is a step by step synopsis of each shot. It is usually kept short, to a few pages.

-Treatment: A treatment is a summary of the film. Usually about one page long. It is very similar to doing a book report on the film.

Formatting:

-Introduction: the introduction should establish the scene and introduce the setting and character.

-Action/Conflict: What is the character doing in the environment? How is the character affected?

-Resolution: How does it end? How the conflict or action is resolved.

Outline:

An outline should be a numbered list of each scene (or shot) in the video. It should describe the location and the action of the video.

For Example:

1) Convenience Store (night):  Establishing shot of convenience store. Clerk drums fingers, bored.

2)Convenience Store: Man with mask covering face enters store. Yells at Cashier. Makes cashier empty register.

3)Convenience Store: Clerk presses emergency button to alert cops while Man looks out window.

4)Convenience Store (Exterior): Cops Arrive, Man is handcuffed.

Treatment:

Describe the story in simple paragraph form. Including where things take place, what action takes place, and brief character descriptions. The treatment should be no longer than two pages. For the length of video we are doing in this class, you should probably only write about a paragraph or two.

For Example:

The scene opens on a convenience store, it is an average establishment in a desolate location. It is night. The Clerk is inside the store, bored. He is an average teenager. Suddenly, a Man who has his face covered by a mask bursts in the door. The Man is tall, it is hard to see his features. The Man yells at the clerk and demands the cash from the register. The Clerk obliges and opens the register. The Man looks out the window to see if anyone is coming. While, he is looking out the window the Clerk hits the emergency button. The Cops arrive and get the situation under control. The cops are wearing typical blue uniforms, one has a dog. The man is led out in handcuffs

Five C's

- camera angles, cutting, composition, close-ups and continuity

Camera angles

- birds eye (extremely high shots, i.e Helicopters and cranes)

- high angle (camera above subject)

- eye level (camera @ eye level with subject)

- low angle (camera below subject)

- oblique/canted angle (camera tilted, oblique angle is 45 degrees)

Camera angles & cutting

- 180 degree rule

Guideline that states two characters in the same scene should have the same left/right relationship. Cutting across the imaginary axis is called crossing the line. The shot on the opposite side is called a reverse angle.

- 30 degree rule

Guideline that states cameras should be over 30 degrees apart when cutting between shots

Cutting

- eyeline match

Audience wants to see what the character on screen is seeing. Match the character's eyeline to cut to an object or person he is looking at.

- shot reverse shot

One character looks at another (often off screen) and other character is shown looking back at first. B/c they are shown looking in opposite directions the viewer assumes they are looking at each other.

- cutting on action

When an editor cuts from one shot to another that matches the firsts action and energy. This gives the impression of continuous time even if the two shots were done hours apart. Makes a visual bridge.

Composition

- rule of thirds

Image should be imagined as divided into nine equal parts by two equally placed horizontal lines and two vertical ones. Important compositional elements should be placed along these intersections.

- balance

- tone

Closeups

- traditional closeups (shoulders up)

- extreme closeups (macro...showing only a small portion of an object or person; for example just the lips or eyes or nose)

Continuity

- action line

Line which the action occurs

- establishing shot

Sets a space for the characters to operate in

- continuity editing

To establish a logical coherence between shots.

Temporal continuity... temporal continuity pertains to the time which the action occurs. Does the action occur over a few minutes, a month, days? etc

Spacial continuity... special continuity refers to the space that the action exists in. For example, if your character turns right into a door... are they entering into the next room from the correct side?

Diagetic sound / avoid ellipses.... Diagetic sound includes the background sounds such as cars passing by, wind, and other ambient noises. It also includes the soundtrack. Use sound over cuts between shots to keep continuity.

Match on action...match on action is almost identical to cut on action. You should match the action's energy and path.

Temporal discontinuity / purposeful ellipses (fades and dissolves)...purposefully interrupting continuity-- for example flash backs. Some movies are also shot out of order on purpose, for example you may already see the results of an action-- but the movie is about how the character got to their results. Ellipses can be used to show that time has passed. 

Additional Links:

Screenwriting Wiki

Celtx- Free Screenwriting Software

Treatment Examples

Film Treatment Wiki

Studio Lighting & 3 Point Lighting

Studio Lighting & 3 Point Lighting

Using the studio lights is fairly simple, however there are some safety rules you should always follow.

1. Always use gloves when touching lighting equipment
2. When adjusting lights using a ladder, always lock the ladder if there is a lock and use a spot to hold the ladder securely.
3. Let lights cool before touching them
4. Do not touch the bulbs of the lights with bare hands. The oils on your hands can actually cause them to explode.
5. When using a C-stand always remember to put down a sandbag on the lowest tripod leg.
6. Yell out “striking” before turning on lights.

All of the studio lights are numbered, to turn on a light simply look up at the ceiling there will be a number associated with the outlet it is plugged into. Switch the on and off switch of the corresponding number that is on the electrical box in the control room.

Lights in the Studio:

Fresnels (mole & baby moles): primary lights in studio. Best used as key lights, typically used for fill or back lights with diffusion.

Ellipsoidal spotlights: used to spotlight a person/object… more dramatic then key and fill lights

Rifa: hooded soft light that produces less shadows. We use this often for more “natural” looking light. Can use as a key or a fill.

Mole Softlite: used to create diffused shadowless light, usually used as a fill light.

Omni: multi-purpose, portable light

Ambient Light- The light already present in a scene, before any additional lighting is added.

Incident Light- Light seen directly from a light source (lamp, sun, etc)

Reflected Light-Light seen after having bounced off a surface

Color Temperature-A standard of measuring the characteristics of light, measured in kelvins.

Contrast Ratio- The difference in brightness between the brightest white and the darkest black within an image

Key Light- the main light on the subject, providing illumination and contrast

Fill Light- A light placed to the side of the subject to fill out shadows and balance the key light.

Back Light-A light placed at the rear of the subject to light from behind.

Hard Light- Light directly from a source such as the sun, traveling undisturbed onto the subject being lit.

Soft Light-Light which appears to “wrap around” the subject to some degree. Produces less shadows or softer shadows.

Spot-A controlled, narrowly-focused beam of light.

Flood- A broad beam of light, less directional and intense then a spot.

Tungsten- light from ordinary light bulb containing a thin coiled tungsten wire that becomes incandescent (emits light) when an electric current is passed along it. Tungsten color temperature is around 2800k to 3400k. Also known as an incandescent light.

Halogen-Type of lamp in which a tungsten filament is sealed in a clear capsul filled with halogen gas.

Fresnel-A light which has a lens with raised circular ridges on its outer surface. The Fresnel lens is used to focus the light beam

Practical Light: Any source of light that is not explicitly supplied by the photographer for the purpose of taking photos. The term usually refers to sources of light that are already available naturally  (the sun, moon, lightning) or artificial light already being used (to light a room).

“Striking!” is called before turning a light ON or OFF. When you hear this term, turn away from the light source to prevent hurting your eyes.

Blocking: Refers to the precise positioning and movement of the subject with in a scene.

Barndoor: This piece of lighting equipment gives you the ability to control and direct the light on a certain object or detail on a subject.

Century Stand (C-Stand): Is a metal stand that is commonly used for various rigging and lighting tasks.

Gaff Tape:Gaffer tape or gaffer’s tape is a strong, cotton cloth tape used in theater, film and television productions. While related to duct tape, it differs in that it can be removed cleanly because it uses a synthetic rubber adhesive rather than a natural rubber adhesive.

Gels: Transparent color material made of polycarbonate or polyester that are used to change the color of the light.

GOBO: A metal object that is placed in front of the light and projects a design.

C-47is the fancy term for a clothespin. Used for hanging gels, diffusion, etc.

Three Point Lighting

A standard method used in video, film, etc. By using three separate positions, one can illuminate the shot's subject however desired, while also controlling the shading and shows produced by direct lighting.

Key Light, shines directly on the subject and serves as its principal illuminator; the strength, color, and angle of the key determines the shot's overall lighting design.

Fill Light, also shines on the subject, but from the side angle relative to the key and is often placed at a lower position than the key (about at the level of the subject's face). It balances the key by illuminating shaded surfaces, and lessening or eliminating the Chiaroscuro effects, such as the shadow cast by a person's nose upon the rest of the face. It is typically softer and less brighten than the key light, and more to a flood.

Back Light, shines on the subject from behind, often to one side or the other. This gives the subject a rim of light which helps to separate the subject from the background.

Additional Links
 

video explaining how to use hard light

video explaining three point lighting

some general lighting information

Green Screen & 5 Point Lighting

Green Screen & 5 Point Lighting

Green Screen Vs Blue Screen

Chromakey bluescreen was created for film, because it is the furthest color in the visual spectrum from red (which is the predominant color in human skin tone). However, as the industry made the move to digital, green became the favored backdrop of the two. Video cameras are most sensitive to the color green, which is similar to the human eye’s own sensitivity to green light. Because of this fact, the greenscreen produces the cleanest “key” and is now the most popular backdrop color to use.

Themost important factor for chomakey is that there needs to be very distinct color separation between your subject (the foreground) and the screen (your background). So if you are shooting someone with blue eyes, or wearing blue clothing, you will want to go with the greenscreen. If you are shooting plants, or anything else that is predominantly green, you will want to go with a bluescreen. Both of these colors will key out nicely with virtually any type of chromakey editing software.

Greenscreen needs less light than the bluescreen, meaning you can get away with having fewer lights. Also, if you plan on shooting outdoors, use a greenscreen so you don’t have to worry about accidently keying out the sky on a bluescreen.

Tips for Greenscreen:

• Place your subject at least 5-6 feet away from the screen for a better key.  If the subject is closer, you will get a green halo effect around your subject that is difficult to key out.

• Light your screen evenly to minimize shadows that can cause problems during keying process.

• Make sure your screen isn’t wrinkled. Wrinkles will not key out properly. A quick ironing job should do the trick.

5 Point Lighting

Five point lighting for green screen is very similar to three point lighting, except that you will also need to light the screen.

The goal in five point lighting is to light the green screen as evenly and as smoothly as possible, and to provide light on your subject that creates separation from the background.

If you have uneven lighting on your green screen, shadows will show up and it will be a lot harder for you later to key out the green color in post production. If your subject is not well lit, they will appear to fade into the background.

Your key light should be at about a 45 degree angle from the camera, facing the subject. The fill should be on the opposite side and also at a 45 degree angle. Your back light can be placed off to the side so that it hits the subject, but is out of view of the camera.

The other two lights remaining are for lighting the screen. You want to place each of these lights angled so that their beams cross each other—creating a flood on the screen.

Additional Links

Green Screen Set Up

Studio Introduction: Cameras & Recording

• Power On
• On Rack: Turn on Power #1 and Power #2, Next turn on For-A power. If using a backup tape, turn on Sony Deck. If using the computer for CG graphics, turn on computer at the very bottom of the rack.
• Next, power on the large TV with the remote that is kept at the base of it.
• Power on the For-A switcher, the power button is located on the back of the switcher on the left.
• Lastly, turn on the two AJA decks located behind the switcher by pressing the individual power buttons. When the AJAs start up the display should read “99%” in the upper right.

• Set up Cameras
• Position, zoom, and focus the cameras
• The TILT lever is located on the left side of the circular part of the tripod, loosen this to tilt the camera.
• The PAN lever is located on the back of the tripod, near the bubble for leveling the tripod. Once again, loosen the lever to pan.
• When the camera is how you want it, use the locks on the wheels of the tripod to keep camera in place.

Camera Lingo

• Tilt- vertical movement of the camera angle, is the camera pointed up or down
• Pan- horizontal movement of the camera, left or right.
• Optical Zoom- a change in the lens focal length that gives the illusion of the camera moving closer or further away.
• Digital zoom: digital zoom moves closer by cropping an image and then expanding it back up to the original aspect ratio. 
• truck/track a movement which stays a constant distance from the action, especially a side-to-side movement
• dolly the camera is mounted on a cart which travels along tracks for a very smooth movement
• pedestal-moving the camera position vertically with respect to the subject
• follow- camera physically follows the subject at a more or less constant distance.

• Recording and Stopping
• To record, simply hit the record button on the AJA (red button with white circle)
• To stop, simply hit the stop button on the AJA (white button with black square)

• Ejecting
• To Eject, FIRST hit the SLOT button located on the lower right of the AJA. Do not eject until the display reads “no slot” where 99% used to be. Once it displays “no slot” you can hit the eject button on the upper left and slide the disc forward out of the AJA recorder.

• Power Down
• When powering down, there is one thing that is most Important. THE AJA RECORDERS SHOULD BE POWERED DOWN FIRST.  To shut down the AJA recorders, use the power button on the lower right hand side. You must hold down the power button until it says that the AJA is shutting down. If you release it while it says “shutting down in 2 seconds” for example, it will not actually shut down. Make sure the display is totally off before preceding to power down the rest of the equipment.
• Make sure to turn off the For-A switcher, large TV,  Sony deck (if used), CG computer (if used), For-A power on rack, Power #1 on rack, and Power #2.

Setting Up Audio in the Studio

Audio Set Up

• There are four kinds of mics in the studio, the lavaliere mic, the stick mic, the cardoid condenser mic, and the boom mic.

The lavaliere mic is a small mic that can mostly be hidden by clothing.

The stick mic can be left around the studio to pick up ambient noises or held and talked into. 

The cardoid condensor mic is usually used for voice overs or singing but can be used in the studio as a stylistic choice. 

The boom mic is usually used mounted on a C-Stand to capture audio from multiple people at once without the hassle of wiring up each person.

• The first step, would to be to check which XLR cables are plugged into the audio board. The XLR cable will have a corresponding number, which you can find by looking at the audio board. This will be the same number you will use to identify which track to adjust on our Audio mixer.
• If using a lavaliere mic, plug the adapter into the XLR first, then the mic into the adapter. When using the stick mic, cardoid condenser mic, or boom mic the XLR plugs directly into the back of the mic.
• Next, raise the master audio track on the Audio mixer located inside the control room to the line marked “U”
• After, raise the individual mics to the line marked “U” as well.
• Lastly, have your talent do a count down to test the audio. If the audio is too loud overall... turn down the master track. If one subject is too loud, and another is too soft… raise and lower the individual mics accordingly. 

Lesson 1- Video Production Basics

Power Point v 2010

What is Powerpoint?

PowerPoint is a presentation software program that is part of the Microsoft Office package.

Powerpoint Environment (pg 6-13)

Presentation slides appear within the application frame. The application frame consists of the title bar, the ribbon, the status bar, and the presentation slides.

The Title Bar: The bar that runs across the top of the window. The title bar displays the name of the program you are working in, the name of the presentation you are working on, and the minimize/maximize/and close buttons.

The Ribbon: The ribbon is the main tool available to use in powerpoint, it lets you perform all of the commands in the program. The ribbon is divided into tabs, within the tabs are different groups of commands and menus. For advanced options, you can click the dialog box launcher button that appears on the lower right corner of each group on the currently active tab.

To hide the active tab in the ribbon double click, double click again to show the tab.

The Quick Access Toolbar: located above the ribbon by default. However, you can move it below the ribbon by clicking the “Customize access toolbar” button at the right end of the toolbar and then selecting “show below the ribbon.” The toolbar has common commands such as ‘save,’ ‘undo,’ and ‘redo’ but you can easily add buttons for functions that you use the most.  The easiest way to do this is to right click a command you’d like to add and select “add to quick access toolbar.”

Scroll Bars: Scroll bars appear both vertically and horizontally along the right and bottom sides of your presentation slide. The arrows indicate which direction the scroll slides when you click them. Arrows at the ends of scroll bars let you move through the slides, clicking and dragging the box inside the scroll bars lets you move more rapidly. Double-pointing up and down arrows at the bottom of the vertical scroll bar let you move to the “previous” or “next” slide.

Views Button Group: The views button group is used to change the view of your presentation. Views include “normal,” “slides,” and “outline.”

The Zoom Slider: in the lower right corner of the application window is the zoom slider. It is used to change the magnification level of the slides. This does not modify the presentation in any way, but only changes the size at which you view it.

The Status Bar: At the bottom of the application window is a horizontal bar that contains the zoom slider & the views button group. This lets you see various statuses within powerpoint…such as the current slide number and total number of slides. To see what statuses are currently shown or hidden, right click on the status bar to view the ‘customize status bar’ menu.

The Mini Toolbar: When you select text within the presentation and hold your mouse pointer over it, you will see a dimmed out toolbar appear next to the selection. You can roll your mouse pointer over the faded-out toolbar to make it appear solid. Then you can select from the commands available for quick formatting.

Creating Basic Presentations (page 21-23)

Creating New Presentations

Click on the “file” tab in the ribbon and select the “new” command. This will display the “available templates” panel at the right side of the view. Choose to start a new presentation from one of the many available templates OR you can choose to create a new blank presentation.

Closing Presentations

To close a presentation simply click the X in the upper right corner of the application window. This will also close the program if you do not have multiple presentations open. To keep the application open click the “file” tab on the ribbon and then click “close.” Office will ask you if you want to save your presentation, click “yes” “no” or “cancel.” Yes will take you to the save dialog box,  No will close the presentation without saving, and cancel will return you to your previous screen.

Saving a Presentation

Click on the “file” tabe on the ribbon. And then click the “Save as” command if this is the first time you are saving, or the “Save” command to save an updated version. If it is the first time you are saving, the “save as” dialog box will appear. Here you need to give the presentation a location where it will be saved and a file name.

Opening a Presentation

To open a presentation click the “file” tab on the ribbon. Then click the “open command.” This will open the “open” dialog box. Navigate to the location of the presentation you want to open, you should see an icon for the presentation file along with the files name. To open the file from the location you selected click the icon for the file and then click the “open” button in the lower right corner of the dialog box. Alternatively, you can also double-click the icon of the file shown in the open dialog box.

Lesson 2- Creating Basic Presentations

Creating Basic Presentations Cont. (pg 23-25)

Inserting New Slides

To add a new slide that contains a “title and content” slide layout click the “new slide” button in the slides group on the home tab in the ribbon. In order to create a slide with a different layout, click the drop-down part of the new slide button to show a menu that displays the various slide layouts that you may want to apply. Click on the name of one of the slide layouts in the menu in order to create a new slide with the selected layout.

Applying Slide Layouts

To Apply a new layout to an already existing slide, select the slide and click the “slide layout” button in the slides group on the home tab in the ribbon. The slide layout dropdown menu will then appear and you can click on the slide layout that you wish to apply.

To reset your current slide’s layout back to its default placeholder position and format, click the “reset” button in the slides group on the home tab in the ribbon.

Using the “save and send” features

Click the “file” tab in the ribbon to access backstage view. Select the “save and send” command from the command panel. In the middle section you will see various options for saving and sending a completed presentation. Click on any of the options to display more info in the far right panel. You can then make your selection to either “send using an email,” “save to web,” “share to sharepoint,” “broadcast slide show,” “publish slides,” “change file type,” “create pdf/xps document,” “create a video,” “package presentation for cd,” or “create handouts.”  Then follow the on screen instructions to finish sending the presentation.

Using Text (pg 39)

Adding Text to Slides

You can add text to slide placeholders, text boxes, or shapes.

Placeholders, text boxes, and shapes that contain text have two modes. Firstly, they can be formatted, edited, and moved as objects and secondly, they allow you to edit, add, remove, and format the text they contain.

The object’s mode is shown by it’s border. A dashed border indicates that the placeholder is in “text edit” mode. When the placeholder is in this mode, you can select and change the text inside of the object. A border that is solid indicates that the object is in “object mode”. The object itself, including all text within, is selected and can be modified in this mode. You can click directly on the border of an object to place it in “object mode. You can place your mouse over any of the text within the object and then click to switch into “text editing” mode.

Placeholders are easy because you can simply click into the placeholder and enter your text. The other advantage is that they show up in the outline view of your presentation.

Text within textholders and shapes do not show up in your outline. To insert a text box, click the “text box” button in the text group  on the insert tab in the ribbon. Click and drag over the area in the slide where you want to insert the text box. It will insert in “text edit” mode, so you can immediately type the text. Powerpoint will remove your textbox from the slide if you do not immediately fill text in.

To add text to a shape that is drawn, click on the shape in order to select it and then begin typing your desired text. To insert a shape into your slide click the “Shapes” button in the illustrations group in the insert tab in the ribbon. Click on the shape that you would like from the drop down menu. Click and drag over the area in your slide where you want to place the shape. It will insert itself when you release the mouse. Type the text you want to add to the shape.

Lesson 3- Using Text

Using Text pg 39-43

Adding Text to Slides

You can add text to slide placeholders, text boxes, or shapes.

Placeholders, text boxes, and shapes that contain text have two modes. Firstly, they can be formatted, edited, and moved as objects and secondly, they allow you to edit, add, remove, and format the text they contain.

The object’s mode is shown by it’s border. A dashed border indicates that the placeholder is in “text edit” mode. When the placeholder is in this mode, you can select and change the text inside of the object. A border that is solid indicates that the object is in “object mode”. The object itself, including all text within, is selected and can be modified in this mode. You can click directly on the border of an object to place it in “object mode. You can place your mouse over any of the text within the object and then click to switch into “text editing” mode.

Placeholders are easy because you can simply click into the placeholder and enter your text. The other advantage is that they show up in the outline view of your presentation.

Text within textholders and shapes do not show up in your outline. To insert a text box, click the “text box” button in the text group  on the insert tab in the ribbon. Click and drag over the area in the slide where you want to insert the text box. It will insert in “text edit” mode, so you can immediately type the text. Powerpoint will remove your textbox from the slide if you do not immediately fill text in.

To add text to a shape that is drawn, click on the shape in order to select it and then begin typing your desired text. To insert a shape into your slide click the “Shapes” button in the illustrations group in the insert tab in the ribbon. Click on the shape that you would like from the drop down menu. Click and drag over the area in your slide where you want to place the shape. It will insert itself when you release the mouse. Type the text you want to add to the shape.

Basic Object Manipulation (pg 40)

To select an object on your slide, simply click it. For text-containing objects click the border to put it into “object mode” rather then the text area. To select text, click and drag over the text area.

To resize an object, place the mouse pointer over one of the eight white boxes or circles that surround the border of the object. These are resizing handles. Click and drag the handles to resize the object.

To move a selected object, place the mouse point over the border where there is not a resizing handle. The pointer will turn into a four-pointed arrow. Click and drag to move it to its new location. You can also move an object by selecting it and using the arrow keys on your keyboard.  To “nudge” an object, hold down the ctrl key and use the arrow keys to move the object to the desired location.

To delete an object simply hit “delete” on the keyboard.

Font Formatting

There are two ways to apply font formatting. The first way is in ‘object’ mode, any changes you make to your font while in object mode will apply to all the text within that object. The second way to apply font formatting is in ‘text edit’ mode, this lets you select which text to apply the formatting changes to.

Once the selection is made, the buttons that are available in the font group in the “home” tab on the ribbon apply to font formatting. You can also use the mini toolbar to explore your options.

Lastly, you can click the font dialog box button in the lower right corner of the front group in the ‘home’ tab of your ribbon. This opens up the font dialog box which will give you more advanced options and different lesser-used ways to change your text.

Paragraph Formatting

There are two ways to apply paragraph formatting. The first way is in ‘object’ mode, any changes you make in object mode will apply to all the text within that object. The second way to apply font formatting is in ‘text edit’ mode, this lets you select which paragraph to apply the formatting changes to when you have a multi-paragraph text block.

Once you have selected your object or text, you can click the buttons available in the paragraph group in the ‘home’ tab of the ribbon.

Lastly, you can click on the ‘paragraph’ dialog box located in the lower right corner of the paragraph group in the ‘home’ tab of your ribbon. This opens up the paragraph dialog box, which gives you more advanced options to control the looks of your paragraph.

Applying Custom Bullets and Numbering

First select the object or paragraph you would like to change. To apply custom bullets or numbering click the drop-down button enxt to either the bullets or numbering buttons in the paragraph group on the ‘home’ tab in your ribbon. Then select “bullets and numbering…” to open the bullets and numbering dialog box.

Depending on which button you clicked you will either be taken to the “bulleted” or “numbered” tab. You can click on either tab to switch. Here you can completely customize which type of bullets or numbering you would like to use…including the look of the bullets or numbers, the color, size, or image.

When you are done customizing, click OK.

Using Tabs

Make sure that the ruler is turned on by checking the ‘ruler’ checkbox in the show group on the ‘view’ tab in your ribbon. You must be in text edit mode for the tab button to show up. The Tab button is a toggle button that switches between the four types of tabs each time you click it. The available tabs are left, center, right, and decimal.

To set a tab, click the toggle button until it displays the type of tab you wish to set. Then, click on the part of the ruler indicating the position of where you would like the tab to be on your slide. This will place a tab stop. Now, when you hit tab on your keyboard while typing, this will tab the text to the correct stop.

You can set tabs either before or after you start typing. I recommend setting them before.  If not, select which paragraph you would like to apply these rules too BEFORE setting your tabs.

To delete a tab stop click and drag the stop you set off the horizontal ruler.

Setting Text Box Options

Click either the ‘text direction’ or ‘align text’ drop down buttons in the paragraph group in the ‘home’ tab of the ribbon. Then click “more options…” This will open the ‘format text effects’ dialog box and display the ‘text box’ category from the listing of formatting choices shown at the left of the dialog box. On the right, you can set properties.

In the ‘text layout’ section, you can use the vertical alignment” drop down to select the desired vertical alignment for text in the object. You can use the “text direction” drop down to rotate the text within the text-containing shape.

The “autofit” section will let you either resize the text to fit the object, resize the object to fit the text, or disable the feature.

To set the internal margin used by the text containing object change the amount of spacing shown in each spinner box for the ‘left,’ ‘right,’ ‘top,’ and ‘bottom’ sides of the object. You can check the ‘wrap text in shape’ checkbox make the text wrap within the shape.

To add multiple columns of text to an object click the “columns…” button to open up the columns dialog box. Enter the number of columns to set into the shape and the amount of spacing between each column. Click OK to set the columns in your object.

Checking Spelling

Click the “spelling” button in the proofing group on the ‘review’ tab in the ribbon to open the spelling dialog box. 

Basic Studio Rules

• An employee of ECTV should be present
• Do not unplug anything or walk behind consoles
• If you are unsure, please ask
• USE GLOVES WHEN HANDLING LIGHTING EQUIPMENT
• If using the ladder, make sure you lock the bottom and have someone nearby to spot.
• Do not change settings on studio cameras
• No food or drink near equipment
• Place sandbags on bottom-most leg of c-stands

Powering on the control room, setting up cameras, and powering down

• On Rack: Turn on Power #1 and Power #2, Next turn on For-A power. If using a backup tape, turn on Sony Deck. If using the computer for CG graphics, turn on computer at the very bottom of the rack.
• Next, power on the large TV with the remote that is kept at the base of it.
• Power on the For-A switcher, the power button is located on the back of the switcher on the left.
• Lastly, turn on the two AJA decks located behind the switcher by pressing the individual power buttons. When the AJAs start up the display should read “99%” in the upper right.

Set Up Cameras

• Position, zoom, and focus the cameras
• The TILT lever is located on the left side of the circular part of the tripod, loosen this to tilt the camera.
• The PAN lever is located on the back of the tripod, near the bubble for leveling the tripod. Once again, loosen the lever to pan.
• When the camera is how you want it, use the locks on the wheels of the tripod to keep camera in place.

Power Down

• When powering down, there is one thing that is most Important. THE AJA RECORDERS SHOULD BE POWERED DOWN FIRST.  To shut down the AJA recorders, use the power button on the lower right hand side. You must hold down the power button until it says that the AJA is shutting down. If you release it while it says “shutting down in 2 seconds” for example, it will not actually shut down. Make sure the display is totally off before preceding to power down the rest of the equipment.
• Make sure to turn off the For-A switcher, large TV,  Sony deck (if used), CG computer (if used), For-A power on rack, Power #1 on rack, and Power #2. 

Video Production Basics

Video Production Basics

There are Four Main Stages in Video Production

            - Development

            Development is the stage in which you begin creating the idea for the project.             This includes but is not limited to creating an outline, and a script.

            Typically, a film or TV production would have an outline, a pitch, and a treatment before it is turned into a script.

                        -Outline: An outline is almost exactly what it sounds like. It is a step by step synopsis or outline of the film's script. It is usually kept short, to a few pages.

                        -Treatment: A treatment is a summary of the film. Usually about one page long. It is very similar to doing a book report on the film.

                        -Pitch: A pitch is a short one paragraph description of the film, meant to peak the interest of investors and producers. It can also be used for promotional material.

The Script or Screenplay contains the dialogue and instructions for the production. When you write a script, you want to include descriptions of characters, dialogue between characters, character actions, and scene descriptions. However, you do not want to include camera direction unless you know you will be the person directing the film. Someone who writes the script is called a Screenwriter. The act of writing a script is Screenwriting. If you are interested in screenwriting I have included a link on script formatting and a link to example scripts.

            - Preproduction

            Preproduction is the stage where you begin to prepare the script for production. It mainly entails planning. This includes but is not limited to; storyboarding, assembling a crew, props, costumes, and budgeting.

                        -Storyboard: A storyboard is an illustrated scene by scene play-out of the production. It is usually made by Concept Artists.

- Production

            Production is the stage which most people consider the most exciting. This is where the film or video is created and shot. At this point, everyone on the crew should know how each scene should be set up and what the basic look of the film or video will be. Each Crew member has a specific job.

                        -Director: the director is in charge of managing the entire production and the ultimate vision of the piece.

                        -Producer: The producer is typically in charge of the organization aspects of the production and getting all of the crew members to the correct place at the correct time.

                        -Director of Photography: The director of photographer is also known as the cinematographer or the DP. The DP is the head cameraman, he will often have assistants.

                        -Gaffer the gaffer is in charge of the lighting.

                        -Art Director:  The art director is in charge of the artistic choices in the film, including props and backgrounds.

- Post-Production

            Post-Production is the stage where the raw footage that was shot during post production is then turned into a final film. This includes but is not limited to logging the film, editing the film, special effects, and soundtrack.

                        -Logging: Logging the film involves taking the raw footage and organizing it in a manner that is easy for you to handle.

                        -Editing: Editing the film involves going through each of the takes, and deciding which are good and which are bad and in what order to put them in. Editors have one of the largest impacts on a film's final look.

                        -Special Effects: Special effects can be done in both production and in post-production. Special Effects in production include in-camera tricks, certain types of animation, puppetry, etc. Special effects in post-production typically deal with compositing, computer animation, and motion work.

                        -Soundtrack: The soundtrack is another extremely important aspect of video production. The soundtrack includes the music, sound effects, and dialogue of the film or video. Even though it is often overlooked, a good soundtrack can completely change the mood of an entire film.

Studio Lighting & 3 Point Lighting

Studio Lighting & 3 Point Lighting

Using the studio lights is fairly simple, however there are some safety rules you should always follow.

1. Always use gloves when touching lighting equipment
2. When adjusting lights using a ladder, always lock the ladder if there is a lock and use a spot to hold the ladder securely.
3. Let lights cool before touching them
4. Do not touch the bulbs of the lights with bare hands. The oils on your hands can actually cause them to explode.
5. When using a C-stand always remember to put down a sandbag on the lowest tripod leg.
6. Yell out “striking” before turning on lights.

All of the studio lights are numbered, to turn on a light simply look up at the ceiling there will be a number associated with the outlet it is plugged into. Switch the on and off switch of the corresponding number that is on the electrical box in the control room.

Lights in the Studio:

Fresnels (mole & baby moles): primary lights in studio. Best used as key lights, typically used for fill or back lights with diffusion.

Ellipsoidal spotlights: used to spotlight a person/object… more dramatic then key and fill lights

Rifa: hooded soft light that produces less shadows. We use this often for more “natural” looking light. Can use as a key or a fill.

Mole Softlite: used to create diffused shadowless light, usually used as a fill light.

Omni: multi-purpose, portable light

Ambient Light- The light already present in a scene, before any additional lighting is added.

Incident Light- Light seen directly from a light source (lamp, sun, etc)

Reflected Light-Light seen after having bounced off a surface

Color Temperature-A standard of measuring the characteristics of light, measured in kelvins.

Contrast Ratio- The difference in brightness between the brightest white and the darkest black within an image

Key Light- the main light on the subject, providing illumination and contrast

Fill Light- A light placed to the side of the subject to fill out shadows and balance the key light.

Back Light-A light placed at the rear of the subject to light from behind.

Hard Light- Light directly from a source such as the sun, traveling undisturbed onto the subject being lit.

Soft Light-Light which appears to “wrap around” the subject to some degree. Produces less shadows or softer shadows.

Spot-A controlled, narrowly-focused beam of light.

Flood- A broad beam of light, less directional and intense then a spot.

Tungsten- light from ordinary light bulb containing a thin coiled tungsten wire that becomes incandescent (emits light) when an electric current is passed along it. Tungsten color temperature is around 2800k to 3400k. Also known as an incandescent light.

Halogen-Type of lamp in which a tungsten filament is sealed in a clear capsul filled with halogen gas.

Fresnel-A light which has a lens with raised circular ridges on its outer surface. The Fresnel lens is used to focus the light beam

Practical Light: Any source of light that is not explicitly supplied by the photographer for the purpose of taking photos. The term usually refers to sources of light that are already available naturally  (the sun, moon, lightning) or artificial light already being used (to light a room).

“Striking!” is called before turning a light ON or OFF. When you hear this term, turn away from the light source to prevent hurting your eyes.

Blocking: Refers to the precise positioning and movement of the subject with in a scene.

Barndoor: This piece of lighting equipment gives you the ability to control and direct the light on a certain object or detail on a subject.

Century Stand (C-Stand): Is a metal stand that is commonly used for various rigging and lighting tasks.

Gaff Tape:Gaffer tape or gaffer’s tape is a strong, cotton cloth tape used in theater, film and television productions. While related to duct tape, it differs in that it can be removed cleanly because it uses a synthetic rubber adhesive rather than a natural rubber adhesive.

Gels: Transparent color material made of polycarbonate or polyester that are used to change the color of the light.

GOBO: A metal object that is placed in front of the light and projects a design.

C-47is the fancy term for a clothespin. Used for hanging gels, diffusion, etc.

Three Point Lighting

A standard method used in video, film, etc. By using three separate positions, one can illuminate the shot's subject however desired, while also controlling the shading and shows produced by direct lighting.

Key Light, shines directly on the subject and serves as its principal illuminator; the strength, color, and angle of the key determines the shot's overall lighting design.

Fill Light, also shines on the subject, but from the side angle relative to the key and is often placed at a lower position than the key (about at the level of the subject's face). It balances the key by illuminating shaded surfaces, and lessening or eliminating the Chiaroscuro effects, such as the shadow cast by a person's nose upon the rest of the face. It is typically softer and less brighten than the key light, and more to a flood.

Back Light, shines on the subject from behind, often to one side or the other. This gives the subject a rim of light which helps to separate the subject from the background.

Additional Links
 

video explaining how to use hard light

video explaining three point lighting

some general lighting information

Green Screen & 5 Point Lighting

Green Screen & 5 Point Lighting

Green Screen Vs Blue Screen

Chromakey bluescreen was created for film, because it is the furthest color in the visual spectrum from red (which is the predominant color in human skin tone). However, as the industry made the move to digital, green became the favored backdrop of the two. Video cameras are most sensitive to the color green, which is similar to the human eye’s own sensitivity to green light. Because of this fact, the greenscreen produces the cleanest “key” and is now the most popular backdrop color to use.

Themost important factor for chomakey is that there needs to be very distinct color separation between your subject (the foreground) and the screen (your background). So if you are shooting someone with blue eyes, or wearing blue clothing, you will want to go with the greenscreen. If you are shooting plants, or anything else that is predominantly green, you will want to go with a bluescreen. Both of these colors will key out nicely with virtually any type of chromakey editing software.

Greenscreen needs less light than the bluescreen, meaning you can get away with having fewer lights. Also, if you plan on shooting outdoors, use a greenscreen so you don’t have to worry about accidently keying out the sky on a bluescreen.

Tips for Greenscreen:

• Place your subject at least 5-6 feet away from the screen for a better key.  If the subject is closer, you will get a green halo effect around your subject that is difficult to key out.

• Light your screen evenly to minimize shadows that can cause problems during keying process.

• Make sure your screen isn’t wrinkled. Wrinkles will not key out properly. A quick ironing job should do the trick.

5 Point Lighting

Five point lighting for green screen is very similar to three point lighting, except that you will also need to light the screen.

The goal in five point lighting is to light the green screen as evenly and as smoothly as possible, and to provide light on your subject that creates separation from the background.

If you have uneven lighting on your green screen, shadows will show up and it will be a lot harder for you later to key out the green color in post production. If your subject is not well lit, they will appear to fade into the background.

Your key light should be at about a 45 degree angle from the camera, facing the subject. The fill should be on the opposite side and also at a 45 degree angle. Your back light can be placed off to the side so that it hits the subject, but is out of view of the camera.

The other two lights remaining are for lighting the screen. You want to place each of these lights angled so that their beams cross each other—creating a flood on the screen.

Additional Links

Green Screen Set Up

Lesson 1- Getting Acquainted

The Word Environment

The word screen consists of two windows: the application window and the document window. The document window opens up within the application window and allows you to create and modify documents. You can close this window without closing the entire application; allowing you to work on multiple documents at once.

If you close the application window, word will close entirely. It will also prompt you to save.

The Title Bar

The bar that runs across the top of the window. The name of the document that you are working on is displayed here. The title bar also contains the minimize, maximize/restore down, and close buttons on the right side. These buttons affect the display of the application window.

Ribbon

The primary tool that is available for you to use in word is the ribbon. It allows you to perform all of the commands available in the program. The ribbon is divided into tabs. Within the tabs are different groups of commands. The commands can be accessed either through the use of buttons, boxes, or menus that are in each group.

The button in the lower right corner of some groups will give you advanced option dialog boxes.

You can double-click on the active tab within the ribbon to both hide and show the contents of the ribbon.

Click on tabs to switch the groups displayed. The default tabs are “file,” “home,” “insert,” “page layout,” “references,” “mailings,” “review,” “view,” and “add-ins.”

In addition to the default tabs available, there are also special contextual tabs that appear within the ribbon when you have a particular type of object selected.

The File Tab and Backstage View

The File Tab within the ribbon has replaced the file menu from previous versions of word. Click the file tab in the ribbon to open a view of the file called backstage view. In this view, you can perform all of your file management.

The commands that are shown at the left side of the view are the file management commands that are available for your program. These include “save,” “save as,” “open,” “close,” “info,” “recent,” “new,” “print,” “save & send,” “help,” “options,” and “exit.” When you click on a command in this section, more options for the selected command appear to the right in the backstage view.

You also have access to your basic file management commands such as “save,” “save as,” “open,” and “close.” These commands will still return you to your document and open any additional dialog boxes that may be required.

To open the default options for word, click the “options” command in the file tab.  You can also manage any add-ins for the program here. Lastly, there is the “exit” command which exits the word application.

The Quick Access Toolbar

Located above the ribbon by default. You can also place it below the ribbon by clicking the “customize quick access toolbar” button at the right end of the toolbar and then selecting the “show below the ribbon” command. Reset it to its default location by clicking the same button and then choosing the “show above the ribbon” command.

By default the toolbar gives you quick access to the following commands: “Save,” “undo,” “redo (repeat),” However, you can add buttons to this toolbar that you use the most often.

To add a button to the toolbar right click on any command or function in the ribbon you’d like to add, and then choose “add to quick access toolbar.”

To remove a button right click on the button you wish to remove, and then choose “remove from quick access toolbar.”

You can more thoroughly customize the toolbar by clicking the “customize quick access toolbar” button and then choosing “more commands…”

The Ruler

Microsoft word provides you with a ruler that you can use to set tabs within a document and assist in the placement and positioning of document text and document objects. The ruler appears both horizontally and vertically in the “print layout” view, but only appears horizontally in the “draft” view.

To turn the ruler on and off simply click the “view ruler” button that appears above the vertical scroll bar at the right edge of the document window. Alternatively, you can also toggle the display of the ruler by selecting the view tab in the ribbon and checking or unchecking the ruler checkbox in the show group.

The Scroll Bars

The scroll bars can appear both vertically and horizontally along the right and bottom sides of your document window. They have arrows at the each end that point in the direction in which they will scroll the document when you click them.

You can click the arrows at the ends of the scroll bars to move through the document, or you can click and drag the box inside the scroll bars to move more rapidly.

At the bottom of the vertical scroll bar is the “previous,” “select browse object,” and “next” buttons. The double arrow buttons that point up are the previous and next buttons. These move you up or down one page at a time. The up and down arrows on your keyboard achieve the same thing.

The button between the previous and next buttons that shows a small circle in it’s button face is the “select browse object” button.  The button is used to select by which type of object you’d like to jump through the document when you click the next and previous buttons.

The Document View Buttons

The group of five buttons located in the lower right corner of the application. You can also find the view buttons in the “view” tab of the ribbon within the document views group.

Click the buttons to change the working view of your document. By default word will open in “print layout” view. This view allows you to see the document in the manner in which it will appear when printed. It shows margins, page breaks, and header and footer information

You can switch between different views as needed. The views are:

Print Layout

Allows you to see the document in the manner in which it will be printed.

Full Screen Reading

Used to maximize the amount of space available for reading the document.

Web Layout

Used when creating documents that are intended to be published to the web.

Outline

Help you organize the hierarchy of ideas in a master document, such as an outline or agenda.

Draft

Focuses on the body of text that comprises the majority of most documents. You will not see any additional information, such as margins, or the header and footer data when working in the “draft” mode.

The Zoom Slider

In the lower right corner of the application window is the zoome slider. You can use this to change the magnification level of the document. This does not modify the document in any way, but only changes the size at which you view it.

The Status Bar

At the bottom of the application window is a long, thin, horizontal bar called the status bar. You can use the status bar to view statuses that are capable of being monitored by word. You can choose which statuses you wish to show or hide. By right clicking with your mouse anywhere on the status bar to view the “customize status bar” panel you can see the available tools and statuses that you can show or hide. Checked items are currently being shown in the status bar. Items without a check are not being displayed, click the name of any object to toggle the display on or off.

The Mini Toolbar

The mini toolbar appears when you select text within the document and hold your mouse pointer over the text. It is a small dimmed-out toolbar next to your text selection, you can roll your mouse pointer over it to make it appear solid.  You can then select from the buttons to apply quick formatting to the selection.

Lesson 2- Creating Basic Documents

Creating Basic Documents

Creating New Documents

To create a new document, click the file tab in the ribbon. Select the “new” command from the panel on the left side of the backstage view. This will display the available templates panel at the right side of the view. You can choose to start a new document from one of the templates available, OR create a new blank document.

In the Available templates panel, select a category to see templates in it, and then select a template. To return to the original display, you can click the back button that appears at the top of the panel.

To create a new blank document, select the “blank document” icon from the available templates section. Then click the “create” button in the lower right corner of the window.

Saving Documents

When you save the document for the first time you must use the save as dialog box. To open the dialog box, click the file tab in the ribbon, and then click the save as command from the panel.

In the save as dialog box you need to give word both a location where the document will be saved and a file name.  When you are ready to save the document, click the Save button in the lower right corner of the dialog box. Once you have named and saved the file, the name will appear in the document’s title bar.

When you save changes in the future, you will not have to re-enter the location and name. To save changes to a document you have already saved once before, you can either click the file tab in the ribbon and then click the save command OR you can click the save command in the quick access toolbar.

Opening Documents

To open a document, you will want to know where the document is located first. Once you know where it is located, you open it by using the “open” dialog box located on the file tab in the ribbon.  In the open dialog box that appears, you can navigate to the location of the file you want to open. You should see an icon for the document file along with the document’s file name in the files and folders shown in the “open” dialog box. To open a file from the location you have selected, click the icon for the file you want to open, and then click the open button in the lower right corner of the open dialog box. Alternatively you can also double click the icon of the file in the dialog box.

To simultaneously open multiple files at once that are adjacent to each other use the shift comment to select multiple files. If you want to select multiple files that are non-adjacent use the CTRL button to select them.

If you are trying to access a recently opened document you can click the file tab in the ribbon and then click the “recent” command. You can then click on the name of the recently opened file that appears in the listing in the center of the backstage view.

Entering Text

When you open a new document, the insertion point cursor appears in the upper left corner of the document. This insertion point identifies where any text you type will appear.

ENTER on your keyboard will begin a new line or end a paragraph

BACKSPACE will delete the previous characters behind the cursor

DEL or DELETE will delete characters in front of your cursor.

TAB will indent lines.

Moving Through Text

Once you have characters on the page, there are many different ways to move the cursor. Using the mouse is the simplest way, click where you want the insertion point to be. You can also use the keyboard arrows.

Selecting Text

When you select text, changes you make will only affect the selected text. This includes formatting, replacing, and deleting the text.  Text will appear highlighted, indicating that it is selected.

Selecting text can be done in various ways:

• clicking and dragging with the mouse
• With the keyboard and mouse: place the cursor at the beginning of the text you want to select, hold down the shift button on your keyboard and then click your mouse pointer to the end of the text you want to select. Release shift when you are done.
• With the keyboard only: Move the cursor to the beginning of the text you want to select, hold down the shift button on your keyboard and then use your arrow keys to increase or decrease the selected area. Release shift when you are done.
• Make multiple non-adjacent text selections: simply make your first selection using whichever method you prefer. Hold down the CTRL button on your keyboard while making additional selections.

Non-Printing Characters

Spacebar, tab, and enter keys all create characters in your document just like letters on your keyboard do. You cannot see these characters, but they can sometimes be problematic when accidently selected.

To view the non-printing characters in your document click the “home” tab in the ribbon, and then click the “ ¶ ” button within the paragraph group.  To hide the non-printing characters in your document again, click the same button to turn characters off.

Managing Multiple Documents

To see your options for managing multiple documents, click the view tab. At the right end of the tab is the window group. This group contains the commands that let you manage your open windows.

The new window button opens your current document in a new window. This lets you do things like use two different views of your document simultaneously in two separate windows. Changes made in one window will appear in the other.

The Arrange All button arranges all open document windows side by side onscreen.

The Split button splits the current document window into two panes. After clicking the button you need to click into the document at the location where you want to the split to be placed. You can click the “remove split” button to remove the split when done.

The view side by side button lets you compare multiple open documents side by side with each open document taking an equal portion of the screen to display.

The synchronous scrolling button toggles the ability to simultaneously scroll multiple documents that are being compared side by side on or off. 

If you have the view side by side featured enabled and then resize or reposition either of the open document windows, you can click the “reset window position” button to reset the placement of the open windows.

The switch windows button lets you view a drop-down menu that shows all of the documents that are currently opened in word. Select the name of the document in the list you wish to switch to.

Lesson 3- Basic Editing Skills

Basic Editing Skills (pg 35-37)

Deleting text

You can use the backspace or delete keys on your keyboard to delete text while typing.  To delete more than one character at a time, select the text first and then use the delete or backspace keys.

To replace text, select the text you would like to replace and simply begin typing the new text.

Cutting, Copying, and Pasting

To move or copy text, or other selected document elements from one place to another, Word uses the clipboard feature to manage this. Click the “home” tab in the ribbon and you will see the clipboard group at the left end. In this group the actions cut, copy, and paste are there to help you move or copy text in your document. These are the same buttons you will use to move or copy other selectable document elements such as Clip art.

Cut: to move selections from one point to another

Copy: makes a second copy of a selection

Paste: inserts either copied or cut selections into the document at a chosen location.

If you display the office clipboard, you may cut or copy multiple objects before pasting them by using the clipboard as temporary storage for the items. If you do not use the clipboard, then you can only copy or cut one object at a time. You can display the clipboard by clicking the clipboard pane launcher button in the lower right corner of the clipboard group in the home tab. The clipboard will hold your cut and copied objects until you either clear the clipboard or quit word. Once the object is copied or cut you can then paste the object over and over.

Undoing and Redoing Actions

The Undo button is located in the quick access toolbar by default. It allows you to reverse the last command or last few commands that were performed. The drop down arrow next to the undo button contains a list of your last few previous actions. You can reverse anything on the list by simply clicking on the action. However, the actions must be reversed in the order that they were executed.

The Redo button is located next to the undo button, it is the opposite of the undo command. It will redo any action that is undone.

Finding and Replacing Text

Use the commands in the editing group of the home tab in the ribbon to quickly find and replace document content. This is good for making minor, repetitive changes.

To perform a basic search for the text in your document you can click the “Find” button to open the navigation pane on the left side of the document window and display the search document search box at the top of the window. Type the word or phrase for which you want to search within your document into the search box. Word will automatically find and highlight the text you enter, as well as display the matching result locations within the results pane below the search box. Click on any result shown in the pane to jump to that selected section within the document.  Click the small X button at the right end of the search box to clear your search.

You can also search for text within your document and then replace the text with another text entry of your choosing. To do this click the replace button within the editing group on the home tab in the ribbon. This opens the “find and replace” dialog box and displays the replace tab. This allows you to replace the text that you find with other text you enter.

Lesson 4- Basic Proofing Skills, Font Formatting, & Paragraph Formatting

Basic Proofing Tools

The Spelling and Grammar Tool

Word provides you with a tool to help you quickly identify and correct misspelled words and grammar errors. This is the spelling and grammar tool. This tool identifies words and phrases in the document that it believes are misspelled by comparing the words to an internal dictionary. Red wavy lines appear under misspelled text. Green wavy lines appear under phrases or sentences that word has identified as grammatically incorrect. The wavy lines will not print.

The spelling and grammar dialog box can be found by clicking the spelling and grammar button in the proofing group on the “review” tab of your ribbon. The dialog box will then move through your document from your insertion point onward identifying misspelled words and grammatically incorrect phrases.

Setting Default Proofing options

You can customize many features of the spelling and grammar tool. You can view the proofing options for Microsoft word by clicking the options button when using the spelling and grammar dialog box. You can also access the proofing options by clicking the file tab in the ribbon and then clicking the options button, and then choose the proofing category.

Font Formatting

Formatting Fonts

Formatting the font within your document gives your document a finished and polished appearance. You can add bold, italics, underlines to emphasize selected text, change the font size or color, etc. First, select the text you would like to apply formatting to, and then use the buttons in the font group of the home tab in the ribbon to apply the formatting you desire. OR simply hold your mouse pointer over selected text, and wait for the mini toolbar to appear.

Advanced font formatting options are found in the font dialog box, which you can get to by using the button in the lower right corner of the font group in the home tab of the ribbon.

You can also set the font formatting properties you would like before you type the text. If you make the choices before typing the text, anything you type after will appear with the formatting specified.

Font Dialog Box

Used to adjust font formatting options that are more advanced than those found in the ribbon.  Use the various drop-down selectors and check boxes to apply the formatting you desire. View the changes in the preview area at the bottom of the font dialog box.

Once you have set the formatting options for a font that you would like to set as your default font, you can click the “default..” button in the lower left corner of the font dialog box to set the currently displayed font formatting as the new default font used for new documents created in the future.

The Format Painter

When you are formatting you may want to simply copy the formatting from one selection and paste only the formatting onto the other text selection. This means, instead of copying the words themselves, you’d only copy things like font case, size, bold, color, etc. The format painter button in the clipboard group of the home tab in the ribbon allows you to copy and paste just the formatting.

To copy the text formatting from a selection, first select the text that has the formatting that you want to copy. If you only want to copy the selected formatting to one other location, you can click the format painter button in the clipboard group of the home tab in the ribbon. If you want to copy the selected formatting to multiple other locations, then you will double-click the same button. After that, click and drag over the other text areas you wish to apply the formatting you just copied.

If you double-click the format painter to past to multiple locations, you will need to click the button once again to turn the feature off.

Formatting Paragraphs

Aligning Paragraphs

Alignment refers to the appearance of the left and right sides of the paragraph. By default, word aligns to the left. You can change this alignment so that the right sides are symmetrical (right alignment), or that the lines are centered with even space on both sides (center alignment), or justified (both left and right sides are aligned). With justified paragraphs, the last line is never truly justified by default, because this tends to look odd. To force the last line to be justified, put the cursor at the end of the last line in the justified paragraph and then press “shift + enter” on your keyboard.

To apply paragraph alignment, select either the entire paragraph or simply click into the paragraph you want to align. In the home tab on the ribbon you will see the paragraph group that contains the paragraph alignment buttons.

Alignment is like any other formatting technique in that you may set it prior to writing the text, or you can apply it after it is written. So you can click the button first and then begin to type, or select the paragraph and then press the appropriate alignment button.

Indenting Paragraphs

Hitting the Tab button on your keyboard will add a tab that is a half inch. You can also increase or decrease the indentation of the left side of your paragraphs by using the increase and decrease indent buttons located in the paragraph group in the home tab of the ribbon.

Simply put the cursor in the paragraph you want to increase or decrease the indentation of and then click either the increase indent or decrease indent button. This increases or decreases the indentation by increments of a half inch.  

Another way of adding paragraph indentation is to use the four indentation markers shown on the horizontal ruler. Display the horizontal ruler, and look for the left indent, first line indent, hanging indent, and right indent markers. You click the indent marker that you want to set, and then drag it to the location on the horizontal ruler at which you want the indentation to occur.

The left indent marker indents the left side of the paragraph.

The right indent marker indents the right side of the paragraph.

The first line indent marker is what you usually think of when you think of a paragraph indentation. It only indents the first line of a paragraph (from the left side).  It sets the indent for the tab key.

The hanging indent marker is less used. It is mainly used for bibliographies or works cited. With a hanging indent, all of the lines except the first one will be indented to the position at which you set the horizontal ruler.  Make sure that the first line indent is at the left margin of the page for the hanging indent to be effective.  When you set the hanging indent, make sure you click and drag the upward-pointing marker that looks like a “home plate” in baseball. The small square marker below is actually the left indent marker.

Line Spacing and Paragraph Spacing

Line spacing is the amount of space allotted to go between each line in your paragraph. Paragraph Spacing is the amount of space to be inserted before and after the paragraphs in your document. Adjust the default settings in the indents and spacing tab of the paragraph dialog box. You can access this area by clicking the paragraph dialog box button in the lower right corner of the paragraph group on the home tab in the ribbon. Select the indents and spacing tab to view and change the settings.

You can also set all of the properties for paragraphs in this dialog box. The General section deals with paragraph alignment. The indentation section is for indentation of the paragraph and paragraph spacing. The Before and After boxes determine the amount (in points) of spacing between the paragraphs.  Set both the amount of spacing before and after each paragraph by using the spinner arrows next to each box. Also, there is a drop-down box that allows you to set the actual line spacing for your paragraph.

Any changes you make to the spacing, alignment, and indentation will appear in the preview window at the bottom of the paragraph dialog box. When you set up your paragraph properties, press OK to apply changes.

To quickly change  your line spacing, use the line spacing button in the paragraph group to select from a list of frequently used line spacing sizes.

Lesson 1

The Excel Environment

Excel consists of two windows: the application window, and the workbook window. 

The Application Window- is the outer frame of the program. It consists of the Title bar, the Menu bar, the Status bar, Toolbars, and the workbook window. When you close the application window, Excel will close. IT will then prompt you to save any open spreadsheets. 

Workbook Window- the default spreadsheet object in Excel. Consists of three spreadsheets by default. The workbook window opens up within the application window. This is where you create and modify spreadsheets. You can have multiple workbooks open in excel at the same time. The workbook window also contains the scroll bar, the "First Sheet," "Previous Sheet," "Next Sheet," and "Last Sheet" buttons. You can close the workbook window without closing Excel.

A screenshot of the Excel Environment can be found on page 7 of your textbook.

Title Bar: The blue bar that runs at the top of the screen. This is the standard Windows title bar that you see at the top of every program. It allows you to open, close, minimize, and maximize the screen. 

Menu Bar: Below the title bar. Contains the commands "File," "Edit," "View,"Insert," "Format," "Tools," "Data," "Windows," and "Help." Each command has a submenu of commands that will expand if you click on it. Commands followed by an ellipses have a pop up dialog box. Any commands that are not currently available will be grayed out.  The menu bar also has "minimize," "maximize," and "close" options that work on the level of your workbook, rather than the level of your application. To access the contents of the menu bar via your keyboard, hold down "Alt" and the first letter of the underlined menu command. The menu also contains shortcut keys listed next to some more common sub-commands, these are located to the right of the sub-command (i.e. Copy = CTRL + C). 

Standard Toolbar: contains many command options that are standard across Office products (word, excel, powerpoint). Includes "New," "Open," "Save," "Print," "Copy," "Cut," "Paste," "Undo," And other common buttons. The standard toolbar can be customized to your liking. If you click the buttons they will perform the tasks assigned to the button. If you do not know what task a button performs you can hover your mouse over the button for several seconds and it will tell you what those buttons do. By default word displays the standard and formatting tool bars, if you right click any toolbar in view word will display a list of all available toolbars. You may also select View|Toolbars from the menu bar to display the toolbar list.

Formatting Toolbar: Displays many of the common spreadsheet formatting tasks such as Font size, style for the cell content , cell color, cell content alignment, etc. There are two ways the toolbar can be displayed: FLOATING and EMBEDDED. A floating toolbar will be detached from the main tool section, you can move it around. Embedded will be attached to the standard toolbar. 

Scroll Bars: The scroll bars function similarly to any other program on the computer, it lets you change the view from one end of the document to another.

The Formula Bar: located underneath the formatting toolbar. Consists of the "Name Box" which displays the address of the currently selected cell in your spreadsheet. To the left of that is the "Insert Formula" button which looks like the = sign. The White bar after that button is where you can see, edit, or create a forumla to enter into your currently selected cell in the spreadsheet. They will also display here so you can edit or change the formulas after creating them. This bar is where you can see the contents of formulas. 

The Workbook Window: Displays the currently selected spreadsheet file. Whichever sheet you select from the "Spreadsheet Titles" section is shown in this workbook window. A spreadsheet stores information much like a database table. A single sheet in a workbook is arranged as a grid that contains 256 columns, and 65,536 rows. The first 26 columns are labeled A through Z. After that they are named AA through AZ, then BA through BZ, and so on. THe rows are sequentially numbered 1 through 64,536.  The intersection of a column and a row is called a cell. cells are where you enter information. They are referenced by their column and row location within the spreadsheet using an ID known as a "cell address."  Which is the Cell Column Letter + the Cell Row #.  You can only enter data in to the currently selected cell. When a cell is selected it will have a thick black border around it and its cell address will appear in the "Name" box in the formula bar. You can only have one cell active at a time for data entry, for formatting you can have multiple cells. You can also select multiple cells to manipulate data inside (For example adding a range of cells with data together). 

Status Bar: The bottom of the application window is the status bar. It provides you with information about the spreadsheet you currently have open. It will also tell you what type of mode you are in. There are three modes in Excel: READY, EDIT, or ENTER. Ready mode means Excel is ready to do anything. This is the mode you want to be in before you begin a task. Edit mode means you currently have a cell you are editing, but have not moved from the cell in order to finish entry. Many commands are unavailable while in edit mode. Enter mode is much like edit, but is shown when you are entering information into a blank cell. 

Task Pane: The task pane changes depending on what you currently have open. It is a graphical interface for more complex tasks and is only available in XP and 2003 versions of Office respectively. You can keep the task pane open or choose to work without it. 

Creating Basic Spreadsheets

Creating a new Workbook

Click the "New" button on the standard toolbar to create a new blank workbook. You can also select "File" from the menu bar and then click "New.." You will be presented with the "New" Dialog box. You can select the "workbook" command in the "General" tab, and then click OK to create a new blank workbook.

Saving Workbooks

To save for the first time you must click "Save As…" in the file menu. This brings up the "Save As" Dialog box. In this dialog box you should create a name for your spreadsheet and a location for the file to be saved. All subsequent saves can either be from the File Menu and then click "Save" or the "Save" button on the standard toolbar that looks like a floppy disk.

Closing Workbooks

To close a workbook, either go to "File" on the main menu bar and then click "close" or hit the X in the upper-right corner of the workbook window. DO NOT click the red X on the title bar.

Opening Workbooks

To open a workbook, either go to "File" on the main menu bar and then click "open" or click the "open" button on the standard formatting toolbar. This brings up the "open dialog box." The "look in:" bar lets you pick the location of your file. The main window lets you see the contents of the folder you are currently looking in. "My documents" is the most frequently used location to save excel files. To open multiple workbooks at once, select both files by holding down the CTRL key if they are not adjacent. If the files are next to each other, you can hold the shift key to select the first and last file-- all files in between those files will be selected as well automatically. After you are done selecting which files you'd like to open click the OPEN button on the lower right.

Selecting Cells

When you open a new workbook, the active cell is always cell "A1." You can use the mouse to click into any cell to make it the active cell. You can also use the keyboard to move the active cell cursor. To see a list of keyboard strokes and their corresponding movements in regards to the active cell see page 18 in your textbooks.

Entering Text into a Cell

Any combo of numbers and letters are treated as text in excel, not numbers. Text automatically left aligns in the cells. To finish an entry you MUST leave the cell and go back to "ready" mode. For example, if you add data to a cell without leaving it this limits your ability to format and modify the information in the cell because options are limited in both "edit" and "enter" mode.  Long text entries appear to spill over into the next cell if the next cell is blank. If you move into the next cell and begin typing, the previous cell's entry will look as if it has disappeared. This is normal, it has not. 

Entering #s into a Cell

In excel there can be no text in numeric entries. Numbers should be entered with no formatting. Only use digits and decimals. Any number signs, commas, etc should be added later when you format cells. You can however, type a minus sign in front of a number to indicate a negative. Numeric entries also have issues with cell content versus cell display. Long numerical entries will turn to ##### signs or become expressed in scientific notation. This happens when the entry is too long to be displayed  correctly in a column. You can widen a column to fix the display. However, this will not fix an entry that changed to scientific notation. In this case, you have to change the formatting to get it back to normal. 

Autocomplete

If you have a column of unbroken data (no empty cells) that has the same text values repeating, excel will offer to fill them in for you as you type down the column.  For example, if you were listing people by departments n your company, you would have a column of departments that would probably contain duplicate entries within the column. With the "Autocomplete" feature, excel notices the first few letters of a repeating entry being typed in, it will try to finish the entry for you by displaying its suggestion in a black highlight. If you want to use the suggestion press "Enter" on the keyboard to accept it. If you do not want to use it, keep typing and the suggestion will go away.

Pick from List

If you have a column of unbroken data (no empty cells) that has the same text entries repeating in various ways, Excel can display a list of previous entries within that column, in alphabetical order, that you can pick from. That way you don't have to retype duplicate entries within that column. Right click the cell at the end of the list and select "Pick from List..." A list of all the prior entries in that column show up for you to choose from.

Zooming the Spreadsheet

To change the magnification of the spreadsheet (if you want a close up of a particular piece of text you are working on or if you want to zoom out to see the document in its entirety) use the drop down "Zoom" button in the standard toolbar to select a percentage or select "Selection" to fit it to a range of selected cells. You can also select "View" from the main menu bar and then click "zoom..." to enter a percentage from the "zoom" dialog box. 

Managing multiple Workbooks

You can open multiple workbooks at a time in Excel. To make a workbook active click the tab that belongs to that workbook. You can arrange the windows manually, or you can use the "window" command from the menu bar to arrange the open workbook windows. The command's sub-menu consist of all the workbooks that you have open. The one with the check mark in front of it is the currently selected workbook. 

Renaming Workbooks

To change the name of a workbook once it's already saved, go to "File" on the main menu bar and then click "Save As..." this brings up the "Save As" dialog box. Enter a new name and/or location for your file. 

Using Ranges

A group of selected cells is a Range. Ranges are used as a reference to the information stored in the cells in the group.  For example, you may have a column of sales figures. You could reference the range of all cells in that column that contain sales data and then add them together to find the total sales in the selected range. 

You can reference either single ranges or multiple ranges. Multiple ranges can be either next to each other or non-adjacent. When you make a range reference, which is a critical aspect of writing formulas, each range is individually identified by its upper left cell address and lower right cell address. Separated by a colon symbol. For example, to reference the range of cells from A2 through A5 you would write A2:A5. To refer to multiple cell ranges simultaneously, separate the references with a coma. So if you wanted to references B3 to C5 simultaneously you would want to write A2:A5, B3:C5.  This is useful both for writing formulas and for actions that require you to select cells prior to implementing the action. 

When a single range is selected, it will appear with a black border around the cells in the range. Those cells will turn a light purple or blue color. The active cell within the range will appear as a normal white cell, but will be encased within the black border as well. A range will stay this way until you click into another cell, or move your active cell w/ the keyboard arrows. This will deselect the range. You can name permanent cell ranges for spreadsheets that you use frequently. This can make the function of the range appear more clearly, and allow you to use the name of the range in place of the range addresses. 

Selecting Ranges

You can use either your keyboard or the mouse to select a range. With your keyboard, select the first cell in the range, hold the SHIFT key on your keyboard and then move the cursor with your keyboard arrows to extend the range until it is the size you like. Then release the SHIFT key on your keyboard. With your mouse, click and drag from the first cell in the range to the last cell. A quicker method is using a combination of both. Click into the upper left cell in your range and press and hold the SHIFT key on your keyboard, then click the cell in the lower right corner of your range. All the cells between the two will be selected.

Selecting multiple non-adjacent ranges

To select multiple non-adjacent ranges use the CTRL key on your keyboard along with your mouse. Select the first range that you would like, next hold down the CTRL key and then use the mouse to create another range. Release the CTRL key when you are done selecting all of your ranges. If you click without holding the CTRL key or using the arrow keys you'll deselect all of the ranges. 

Entering info into a selected range

The cell within a selected range that is white is the active cell. This is the cell you can enter information into. You can move to the next cell within the selected range  or ranges by using the TAB key to move right or the ENTER key to move down. Holding down SHIFT then pressing either of those two keys will move the acetic cell cursor in the opposite direction. 

Using AutoFill

AutoFill allows Excel to automatically fill in a repeating pattern that you establish. I.E. months in a year, days in a week, or any repeating numerical pattern. Select the cell or cells that establish the pattern. Look for the "Fill handle" in the lower right corner of the active cell or cells. Move the white cross cursor towards this box, and try to fit the upper left corner of this cross up to the lower right corner of the cell or cells where the fill handle is. When the cursor is in place, it will turn into a thin black cross (+). This is the fill handle cursor. Now, click and drag the handle out either horizontally or vertically to fill in the selected range. If the cell contains the beginning of a pattern that Excel recognizes it will fill in the range with the rest of that pattern. The names of the rest of that pattern will appear as you drag over each cell in the range in a small yellow tag called a screen tip. 

If the pattern is not recognized Excel simply copies the first cell's contents into the rest of the cells in the range. This is actually a very good way to copy the same information into a row or column of cells quickly.

Excel will recognize user-established patterns. (I.E you type 3 into a cell and then 6 into the ex cell and select both as a range, you could drag out the rest of the pattern which would be increasing by 3.

Lesson 2

Creating Formulas

Formulas are used to perform calculations or mathematic functions on cells. There are two basic ways of writing a formula. One is using Ranged Syntax and the other is using Simple Syntax. Syntax is a way of expressing or writing something. You can use both ranged and simple syntax within one formula. Typically, one uses simple syntax to perform multiple mathematical calculations on multiple cells. Ranged Syntax is usually used to perform a single mathematical function over multiple cells.

Ranged Formula Syntax:

example of syntax in a typical ranged formula:

=SUM(B2:B5)

the = sign begins every formula in excel. 

SUM is the name of the function other functions are average, min, max etc.

B2:B5 is the cell range that you are performing the function on. These should always be enclosed in parenthesis. 

Additional Symbols include:

( : ) - separates the first and last cells in a cell range. Equivalent to "through" in english

( , ) - separates items in a list equivalent to "and" in english.

( $ ) - devotes an ABSOLUTE REFERENCE (we will go over this in a later lesson) (SHIFT 4)

Simple Formula Syntax:

=B2+B3+B4+B5

the = sign begins every formula in excel

the + sign is the mathematical operator used to connect cell addresses 

B2, etc are cell addresses that you want to perform the mathematical operator on.

MATHEMATICAL OPERATORS

+ for addition (SHIFT =)

- for subtraction

* for multiplication (SHIFT 8)

/ for division

() sets up the order of operations [i.e. =C2-(8+5)*-3]

% for percentage (SHIFT 5)

^ for exponentiation (i.e. 2^4) (SHIFT 6)

Creating a Formula

Click into the cell which you want the results of the formula to appear, and then create your formula. When you are done, exit the cell to display the answer to the formula that you created. This is the concept of "contents versus display" The actual contents of the cell will be the formula, but the content displayed will be the result of the formula. To show the formulas in the spreadsheet again press the CTRL key and the ` (single left quotation mark key) this toggles the spreadsheet display between the answers and the formula calculation. 

- to create a Ranged Syntax Formula select the cell you want to enter the formula. Type the Equal Sign. Type the name of the function which you wish to perform on the cell range or ranges. Type an open parenthesis. Input the cell range or ranges which you wish to perform the function. Type a closed parenthesis and press ENTER on your keyboard. You can also, alternatively, hit the green check mark on your formula bar.

- to create a Simple Syntax Formula you start by selecting the cell into which you want to enter the formula and then type and equal sign. Type the cell address that you want to use the formula, joined with your standard mathematical operator. You can also enter standard numbers in a formula. If you want to perform a single calculation on a range of cells, it's easier to create a ranged formula instead of a simple formula. In simple formulas, the standard order of operations applies. Designated operations are performed from left to right with anything in parenthesis calculated first, then exponentiation, then multiplication and division, and finally addition  and subtraction. 

** both syntax can be used in a single formula. An example is if you wanted to sum a column of sales and then multiply the result by 20%. This would look like =SUM(A2:A11)*20%. In this case, the first calculation is a ranged example and the second calculation takes the result of that function and multiplies it by 20% using the simple syntax. 

Formula Functions

In ranged syntax formulas, you are performing a single function on a cell range or multiple cell ranges. There are many functions that you can use to can figure sines, cosines, arcs, and other complex functions. However, unless you are in a specialized field like accounting or engineering you probably won't need them all. Common functions included with Excel by default are "SUM," "Average," "Max," "Min," and "Count." IF you need specialized functions, you may need to add them to excel by using an "add in" if you select "Tools" from the menu bar and click "Add-ins…" You will be presented with the "add-ins" dialog box. You can check whichever additional set of financial or scientific functions you need to add to select them and then click OK To enable the more complex functions. 

Using Autosum

The most common function in Excel is Addition. Autosum automatically calculates any column or row of uninterrupted numbers. Place the active cell cursor at the end of the column or row that you want to add, and then click Autosum in the standard toolbar.  Excel will suggest a range for you to add. If it is incorrect, re-drag the range until it encloses the appropriate cells. Click the Autosum button again or press ENTER on your keyboard to accept the formula. 

a second way to use autosum is to click and drag over the columns or row which you want to add, leave a blank cell at the end in which you want the answer to the formula to appear. Click the autosum button once to add the selected cells and rows and display the answer in the blank cell you selected.

Using the Formula Palette

The Formula Palette Button assists you in creating and editing ranged formulas. You select the function that you want to use in the formula from a list of functions. Use a text box to select the range of cells for the function to calculate. This dialog box where you pick the cell range has a button called the "Collapse dialog" button at the right end of each "number" text box. Which you click this button, the formula palette will drop down under the formula bar. the "Name" box in the formula bar has now become a drop-down list of the most recently used functions. There is also a "more functions" button that will bring up another dialog box called the Past function dialog box where you can pick from all of the functions provided by excel. Pick the function from the list in this dialog box. Click and drag over the range of cells on which you want to perform the selected function.  Click OK to set the formula. 

* if the formula palette window is blocking the cells you need to select, hit the collapse dialog box button again to get the window out of the way. When you are done selecting you will have to hit the collapse dialog box one more time to see the OK button.

Using Paste Function

If you need assistance looking up the syntax of a function or you would like to view all of the functions that excel provides to you you can click the paste function button in the standard toolbar to view them (looks like fx). This will open up the Formula Palette so you can apply the function once you have picked them out. You can view the formulas by category or "view all" in the paste function dialog box.

Editing a Range

to Edit the cell range reference in a formula after it has been created double-click on the formula cell. It will display itself as the formula instead of the answer to the formula, and it will place a blue border around the currently selected range in the formula. If you have more than one range referenced within the formula they will also appear, shown within a border of another color like purple or green. You can then re-drag this range by moving the upper left corner of your pointer up to the lower right corner of the selected range until it turns into a small black cross, like the autofill handle. When it looks like this , click and drag to expand to enclose a new grouping of cells. When you do this, your formula changes to reflect the new range that it is calculating.  You can also move your mouse pointer on top of the blue border (anywhere except the corners-- your mouse will change from the white cross to look like a traditional pointer) and click and drag the calculated cell range. This moves the calculated cell range to a new location.

Formula Autocorrect

When you mistype a formula, excel will often try to assist you in correcting your formula syntax automatically. If you place extra operators in a formula, or extra spaces, or place them in the incorrect order, excel will display a dialog box that offers to correct this mistake for you when you try to exit the cell with the incorrect formula. To accept the solution that excel provides, click YES. If you want to fix it yourself, click NO and correct the formula manually.

Autocalculate

Autocalculate is a temporary tool that displays the results of simple calculations without having to type a formula. These are "none," "average," "count," "count nums," "max," "min," and "sum." If you select a range of cells, and then look at the status bar, you should see one of these function names followed by an equal sign and then the results of that function for the range of cells that you have selected. This can be a useful double-check for basic formulas. To change the function that Autocalculate performs, right click the box where the function is displayed. There will be a list with all of the function names listed. The current choice will appear w/ a check in front of it. Click a new function use it. IF you have selected cells already, it will display the new function's answers. To turn this off, select the "none" feature. 

Copy and Pasting Formulas

Cells that contain numeric or text entries can be copied and pasted wherever you want. Copying Formulas, however, can be harder.

When you copy a formula and paste them to a new location in the workbook, the formula automatically adjusts its cell references to "Fit" its new location. For example, if you typed a formula into cell "H5" that added cells "C5 through G5"  you could then copy and paste the formula into cell "H6." in cell "H6" the newly copied formula would then change its cell references to add cells "C6 through G6." This is an example of relative referencing. This is the default cell references that occurs when copying formulas. They will automatically adjust their references to fit their new location. 

When you cut and paste a formula in Excel, the formula will still maintain its reference to its original cells, regardless of where you paste it. The answer will not change, because it will still refer to its original cell range. This is an example of absolute referencing. 

When you have a single formula with multiple cell references within it and some references need to be relative ones while others need to be absolute it is useful to realize that you can make any cell reference in a formula an absolute reference simply by putting a dollar sign ($) in front of BOTH the column letter and the row number in the cell address. 

You can also edit a formula that you already created to change the cell referencing in the formula. Double-click the formula to edit it and then click and drag over the cell reference in the formula that you'd like to make an absolute reference. Press the F4 key on your keyboard to toggle between the three types of referencing available. "Mixed," "Absolute," and "Relative."  For example, you could toggle a reference to cell A1 (Relative) between $A1 (mixed ) and $A$1 (Absolute).

Cutting, Copying, and Pasting Data:

select the cells that you want to copy or cut and then press either the "cut" or "copy" buttons on the standard toolbar. The cells that you have selected will appear with a blinking marquee around them to indicate that they have been cut or copied. The cut or copied data is then placed on the "clipboard." 

Select the cell or cells into which you'd like to place the data that you just copied or cut. If you are cutting and copying multiple cells you will need to select the cell that you want to become the upper left corner of the pasted data. When you click "Paste" the cells will paste themselves down and to the right from the selected cells. 

Cutting and pasting formulas results in ABSOLUTE REFERENCING while copying and pasting formulas results in RELATIVE REFERENCING. 

AutoFilling Cells:

The Autofill handle can also copy formulas across rows and down columns. It functions the same as it does when copying patterns and copies formulas across rows or down columns.

Cutting, Copying, and Pasting Using Drag and Drop:

Drag and drop is a simple way of cutting, copying, and pasting information using our mouse. Select a cell or range or cells, but but not nonadjacent cells ranges. Use the "Cut" or "Copy" buttons, or keyboard shortcuts to do that. To "Cut and Paste" select the cells you want to move. Next,  click the border around the cells and drag them to their new location. A outline of the cell range will follow your mouse pointer as you drag, showing you where the range will insert itself when you let go of the mouse button.

To "Copy and paste" do the same as above, but hold down the CTRL key before you click on the border to drag and drop the cell range. Release the CTRL key as the last step.

The Undo Button:

The drop-down arrow next to the undo button on the standard toolbar contains a list of your last few previous actions that you have performed. You may reverse anything that is on this list. HOWEVER, you must reverse them in the order in which they were executed. 

If you hold your mouse pointer over the "undo" command, a little screen tip will appear to tell you which action you would be undoing by clicking it. 

The Redo Button:

The inverse of the "undo" command. It will redo an action that was undone. This is valuable if you accidentally click the "undo" button a few too many times when trying to correct a mistake that was made. The Redo button also has a menu of undone commands that it stores. You can access this list of actions by clicking the little black arrow next to the "Redo" button.

Lesson 3

Columns and Rows

Selecting Columns and Rows:

To select an ENTIRE column or row you must click the headings of the columns or rows (where the actual letters or numbers are located). You will see the entire column or row become selected including the heading which will become darkened. To select a range of adjacent columns or rows click and drag across the headings to select them. 

To select NON-ADJACENT columns or rows, click the first column or row heading and then hold down the CTRL key on your keyboard to select the subsequent row or column headings.

The box located at the intersection of the column and row headings is a button you can use to select ALL columns and rows. It is not recommended that you perform calculations on this data because it could crash the computer, however this is ideal for small tasks such as changing font size.

Adjusting Column Width and Row Height:

To change the width of columns place your cursor in between the column headings to the right of the column for which you want to adjust the size. The mouse pointer will turn into a double-pointed arrow with a separator line. Click and drag the column right or left to adjust the width. 

To adjust the row height select the row you want to adjust and place your mouse in between the row headings underneath the row for which you want to adjust the size. The mouse pointer will turn into a double-pointed arrow with a separator line. Click and drag the row either up or down to adjust the row's height. 

AutoFitting Column Width and Height:

The columns and rows can also be adjusted automatically to fit the largest entry in each. Select the columns which you want to adjust and place your pointer in between the column headings, to the right of the column you which to adjust the size. The mouse pointer will turn into a double-pointed arrow with a separator line. Double click that spot to automatically fit the selected columns to their largest entries. 

To AutoFit the height of rows select the rows for which you want to adjust the height and place your mouse in between the row headings, underneath the row for which you want to adjust the size. The mouse pointer will turn into a double-pointed arrow with a separator line. Double click that spot to automatically fit the selected rows to their tallest entries. 

Hiding and Unhiding Columns and Rows:

Columns and rows can contain sensitive data that you need for formulas but don't necessarily want to print (such as salary info), if this is the case you can hide a column or row. This conceals the columns or rows, but still calculates the data from them.  TO do this, select the columns or rows that you want to hide, right click one of the column or row headings to display the shortcut menu. Select "hide" from the menu, and click to hide the selection. 

To Unhide hidden columns or rows, click and drag to select the column or row headings on BOTH sides of the hidden columns or rows, then right click one of the selected column or row headings to display the shortcut menu. Select "unhide" from the menu to unhide previously hidden columns or rows. 

Inserting and Deleting Columns and Rows:

Two Rules for inserting columns or rows 

- The number you select is the number you insert.

- New columns insert to the left of the selected columns, and new rows insert above the current selection. 

To insert new columns, click and drag to select the # of columns that you want to insert from the right of the locations into which you want insert the new columns. Right click one of the selected column headings and click the "Insert" command in the pop-up menu to insert the number of rows that you selected above your current row selection.  You will want to double-check your formulas, they should auto-adjust but sometimes this can mess them up.

To delete columns or rows, select the columns or rows that you want to delete, and right click on the column or row headings. Click the "Delete" command from the pop-up menu that appears. Make sure you don't have any info that you need for the spreadsheet to function. Also make sure you don't delete just the cells within a column or row, as that is a very easy way to mess up a spreadsheet. If you delete the ENTIRE column or row the formulas will readjust to accommodate the deletion, just like when you insert rows or columns. 

Deleting columns, rows, or cells istn' the same as pressing DELETE on your keyboard. That corresponds to the "Clear Contents" command from the pop-up shortcut menu that appears when you right click a cell. When you choose the "delete" command  the cells themselves (Versus the cells content) are physically removed from the sheet. In that case, you have to fill-in-the-blanks in the spreadsheet with the information from the cells below or to the right that you deleted. 

Formatting Spreadsheets

Formatting Tools:

The Formatting Toolbar is one of two tools in excel used to format spreadsheets. This contains buttons that let you format numbers and text, align information in cells, merge cells, indent, and change the font and fill color of cells. To use these buttons select the cell or cell range you want to apply the formatting to and then click the appropriate button. 

(See Diagram page 64)

The Toolbar is broken into six button groupings:

1) Contains the "Font" and "Font Size" drop-downs so we can change the font style and size.

2) Contains "Bold," "Italic," and "Underline" buttons used for formatting. 

3) Contains "left align," "center align," "right align," and "Merge and Center" buttons that you can use to control the alignment of cell contents. 

4)  Contains the buttons we use to set number formatting: "Currency," "Percent," "Comma," "Increase Decimal," and "Decrease Decimal." 

5) Contains buttons for "Increase Indent" and "Decrease Indent" which sets cell indentation. 

6) Contains buttons "Borders," "Fill Color," and "Font Color" which allow you to set the color of cells and their borders. 

If a button has a small black downward arrow next to them a sub-menu will appear when you click on them so that you can specify more button settings. 

The Format Cells Dialog Box:

This dialog box lets you set any formatting to cells that you want. The formatting toolbar allows you to apply common formatting to cells, but you can use the "Format Cells" dialog box to apply any available formatting. This is the second formatting tool in microsoft excel. 

The "Format Cells" dialog box has six tabs that allow you to change cell properties. These tabs are "Number," "Alignment," "Font," "Border," and "Patterns."  The Sixth tab, "Protection," will be discussed in a later lesson. 

To Apply formatting using this dialog box, select the cell or cell range to which you want to apply formatting. Right click the cell selection and click the "Format Cells..." command from the pop-up menu or instead select "Format" from your main menu bar and then click "Cells..."  Once the dialog box is open, select the tab that contains the formatting you want to apply, change the settings, and click OK to save. 

The Number Tab:

Select the Formatting for numbers in the cell or cells you have selected. Click the different types of formatting in the list at the left to view a description and examples of the formatting. If you have applied a format using the formatting toolbar and would like to remove it, use this tab. Select the cells from which to remove the number formatting open this dialog box, and select "General" from the list on this tab to set the numbers back to "General." click OK to apply changes. 

Alignment Tab:

Set both the vertical and horizontal alignment of cell contents, set the cell indentation, rotate cell contents, and select or deselect the check boxes in the "Text control" section to shrink the text to fit the cell, merge the cells, or wrap text to fit in a cell. Select the formatting you want and click OK to save changes. 

To unmerge a range of cells, select the merged cell, open this dialog box and select the "Alignment tab." The check box for "merge cells" will be checked. Remove the check mark and unmerge the cells. Click OK.

Font Tab:

Set all the font options, even adding special effects.  Use the "Font" drop down to change the font. Use the "Font Style" drop down to change the style of the font, like bold or italics. Use the "size" drop down to set the font size. Select a style of underlining from the "underline" drop down. Use the "Color" drop down to set the font color. In the "Effects" section check or uncheck any of the three special effects (strikethrough, superscript, and subscript). You can use the preview window to preview the font you created before applying it. When the font is how you like it, hit OK to apply the changes.

Border Tab:

Set the border options for your selected cells. Change the type of preset border for your cell, clear cell borders by clicking the "None" option, add individual border lines, and change the border style and color. Click on a line style in the "Line" section at the right tab to select a line style. Click the "Color" drop down to set the color for the lines. The preview of the cell border will help you determine where the selected line will be applied if you click it. When you are done hit OK. If you want to apply one of the preset borders you can click the "none," "outline," or "inside" buttons to apply them. 

The Patterns Tab:

Set the fill color and/or pattern for any selected cells. Pick the fill color you'd like to apply form the available palette to apply the selected fill to the selected cells. To apply a pattern use the "pattern" drop down to select the pattern you want to apply. If your cell has content, it may not be a good idea to apply a pattern because it will probably be extremely difficult to read. Patterns are usually only used on cells that have no content. When you have finalized how you want the cell to look, click OK. 

Clearing All Formatting from Cells:

Select the cell or ranges of cells then select "Edit" from the main menu bar and then hit "Clear" and then hit "Formats." The cell or cell range will have any and all formatting that was previously applied removed. 

Copying All Formatting from Cells to Another Area:

One can copy Just the Formatting for a cell or cell range, and apply it to other areas of the spreadsheet. To do this, select the cell or cell range that contains the formatting you want. Select the "Format Painter" button on the standard toolbar. Click and drag over the cell to which you want to apply the formatting you copied to apply it. The "Format Painter" turns itself off automatically. 

If you have multiple areas which you would like to paste the formatting. Follow the steps above only instead of just plainly selecting the "Format Painter" from the standard toolbar, double click it.  This locks the button on. Click and drag over any areas to which you wanted to apply the cell formatting you copied. Remember to click the "Format Painter" button again to turn it back off. 

Conditional Formatting and Autoformatting

Conditional Formatting:

Conditional formatting lets you set up criteria for cells that change the appearance of the cell depending on whether or not the cells' value meets the criteria you have set (I.E. a cell that is a red color if it is a negative number). 

To apply conditional formatting, select the cell or cell ranges you wish to apply formatting to. Select "Format" from the main menu and then click "Conditional Formatting..." to display the dialog box. 

 The dialog box lets you set up to three criteria to evaluate per cell. You have two options for the type of criteria you set. In the "Criteria" section, you can select either "cell value is" which sets up the criteria based on the cell value, or  "formula is" which allows you to enter a logical formula to evaluate. If you pick "Cell Value is" you must pick a comparison operator in the second box and fill in the criteria's value in the text box or boxes that follow.  If you pick "Formula Is" you must type a logical formula into the following text box that equates to a logical value (1=True, 0= False) and then set the formatting for the "True" condition. Once you have established this criteria click the "Format.." button for that criteria to set the formatting to apply to the selected cells when the criteria established is "true" for that cell.

To add another criteria click the "add" button. To remove a criteria click the "Delete.." button and check the box for the criteria to delete from the "Delete conditional format" dialog box that will appear. Click OK to apply. 

AutoFormatting:

Allows you to quickly format an entire spreadsheet w/ a professional style. Autoformatting works best on spreadsheets that contain standard column and row headings, detail data, and a total row. 

Select the Cells to which you want to apply the autoformatting and then select "format" from the main menu bar and then "autoformat..." to show the AutoFormat Dialog box. Click a style in the "Table format" list to see it applied in the "Sample" area. Click the "Options>>" button to display a drop down box of option check boxes. Click any option type to select or deselect it. They are: "Number," "border," "font," "patterns," "alignment," and "width/height." Click OK to apply.

Lesson 4

Setting Spreadsheet Layout

Working with Page Breaks

When you get past the end of a printable page, Excel will insert an automatic page break. The page breaks may occur in places you'd rather them not be. They may leave data on a second page, away from the rest of our spreadsheet, for example. You can adjust the page breaks so that all the data you want will be scaled to fit on a single printable sheet. 

To change where the page breaks occur in a spreadsheet, you can go to "Page break preview." Click "View" on the main menu bar, and then click "Page break Preview." This brings up a view of your spreadsheet, showing the page breaks in your spreadsheet. Page breaks are represented in this view by dashed blue lines. Click and drag the dashed lines and drop them in the location where you want the page to break. Make sure the breaks are where you want them to be located, and then select "View" from the main menu and click "normal" to return to your usual view of the spreadsheet. 

Viewing the Header and Footer

All of the spreadsheets contain a space for a header and footer by default. To view this area, select "View" from the main menu bar and then click "Header and Footer." This opens the "page setup" dialog box, within the dialog box is the "header/footer" tab. You may use the header and footer drop down boxes on the tab to select from pre-made standard heading information or you can click the "Customer Header.." or "Custom Footer.." buttons to create one with your own data.

If you create a customer header or footer then the "header" or "footer" dialog box will appear. You can click into either the Left Section:, the center section:, or Right section text boxes to enter information into the space provided. Excel also gives you buttons to allow you to insert pre-created fields of information into your headers and footers. They are "font," "Page #," "# of Pages," "Date," "time," Path and File," "File," and "Tab." Click on any of these to insert them into your headers and footers. You can also select text that you have typed and click the "Font" button to modify the font and font size. You can't change the font color of header and footer text.

Once you have created your custom header or footer, click OK in the "header" or "footer" dialog box. The data will appear at the top of your printed spreadsheets in the header/footer" tab of the "page setup"

 dialog box. Click "OK" to apply your selected header and footer information when you are finished. 

The Page Setup Dialog Box

To Adjust the settings of the workbook for printing purposes, you will use the "Page Setup" Dialog box. To access it, go to "File" in the main menu and click "page setup…" The Page Setup Dialog box lets you make changes to the printed layout of the spreadsheet. It has four tabs: "Page," "Margins," "Header/Footer," and "Sheet." 

On the "Page" tab, you can change the orientation of the page from portrait to landscape in the orientation section. In the Scaling section you set the amount of scaling of the text of the spreadsheet. You can increase it to make the printout more easily readable or scale it down to fit more data on the page. The Adjust to: option lets you set a percentage that controls the default size of the information prints out. When you are adjusting the page breaks in the "page break preview" you are adjusting the value of the "Adjust to" so it can fit more data on a piece of paper. Fit To: button lets you enter a number of pages across and down in the two spinner boxes that follow that option in the "Scaling" section. Below the scaling section, you can use the paper size drop down to select the size of paper which you will be printing on. The Print Quality drop down lets you set a print quality in dots-per-inch. The "First Page number" text box lets you set a number for the first page of your spreadsheet to start at. I.E. Maybe you want the spreadsheet to start from zero instead of one etc. 

The "Margins" tab lets you set the print margins for your spreadsheet. The spinner harrows at the right of each margin's text box set the "Top:," "Right:," "Bottom:," and "Left" margins width in inches. You can set how far in from the top or bottom edge the header and footer data will print by setting the value into "Header:" and "Footer" text boxes. Check the checkboxes under the "center on page" section to center the spreadsheet data horizontally and vertically on the printed page.

The "Header/ Footer" tab lets you set the header and footer info that we discussed in the last lesson. If you did not set the header and footer earlier, it is useful t note that you can set it while using the page setup dialog box while printing. 

The "Sheet" Tab allows you to set additional spreadsheet options for printing. The "Print Area" text box lets you set a cell range to print. You can click the "Collapse Dialog" button at the right end of this text book to collapse the dialog box down. Click and drag over the cells you want to print  to set them as the print area. You he to click the "Expand Dialog" button at the right end of the text box to expand the dialog box again. Be careful when assigning a print area, once set, it will always and forever print only that selected cell range. Be sure you delete the entry that you make in this text box before saving your spreadsheet. You can also set a column or row to repeat at the left and top of each printed page. Simply Click the "Collapse Dialog" button at the right end of the "Rows to Repeat at top" text box and then click anywhere into the rows that you want to repeat at the top of each printed page. You can do the same w/ the "columns to repeat at left" text box to set columns to repeat at the left of each printed page. 

The "Print" section of the "Sheet" tab lets you check off boxes to print "gridlines," "black and white," or "Row and column headings." Finally, in the page order section you can set the printer order to "over, then down" or "down, then over" for every large spreadsheets. Click OK to set page setup options. 

Printing Spreadsheets

Using Print Preview

Before printing you want to make sure that the spreadsheet is properly setup using the "page setup" dialog box. Once you finish this, you want to check the way your spreadsheet will print without having to waste paper. Excel has a view called "Print preview" to show how your spreadsheet will actually print on paper according to the specifications you have set in the Page setup dialog box.

To view the currently open spreadsheet in print preview click the button for print preview on the standard toolbar (it looks like page w/ a magnifying glass) or select "File" from the main menu and click Print Preview… The current page of your spreadsheet will appear in print preview. The mouse pointer will look like a magnifying glass. IF you put it over the spreadsheet area you want to magnify and click w/ your mouse, that part of the spreadsheet will zoom into view.

The Print Preview toolbar will also appear. To exit Print Preview you must use the "close" button on this toolbar to return to your normal view without closing the entire document. 

Printing Your Spreadsheets

To Quickly print one copy of your entire spreadsheet, press the "Print" button on the standard toolbar. To print multiple copies, collated copies, or to print only a section of the spreadsheet, either select "File" from the main menu and click Print… or press CTRL and P on your keyboard.  This brings up the Print dialog box. 

At the top of the dialog box is the "Name" drop-down or select the printer you want to print the spreadsheets. Inside the print dialog box in the "Print range" section you can either select the "All" option to print all of the sheets, ore you can select "Page(s) From:" and then type a starting page #. Under "To:" Type an ending page #. 

In the "Print What" section you can select the desired options button to decide whether to print the "Entire workbook," "selection," or "active (Selected) sheet(s)." Note that the "Selection" option would only appear as an active choice if you selected a cell range before selecting "File/Print" from the menu bar to arrive at this dialog box. 

In the "Copies" section enter the number of copies you would like. Click the check box next to the word "Collate" to print them collated. When you are done click "OK" to begin printing. 

Helping Yourself

Using Screen Tips: 

The help function in excel contains a searchable database, an index, and a general listing of help contents. The Microsoft Office Assistant is also available to help w/ questions. One of the most simple help tools available are screen tips, they provide you with a brief description of a feature or object in Excel. Screen Tips for toolbars are activated by default in Excel. They are the yellow boxes that display when you hover over a button or feature.

Using the Office Assistant

The Office assistant is a small animated graphic that pops up w/ a balloon of potential help topics attached to it. It gives tips on using Excel and will handle help questions. If you click on it it prompts it to ask if you need help. Fill in the white text box with the question, and then click "Search"." The Office Assistant will display a list of possible related subjects with blue bullets next to them. Click on the one that most relates to the optic you want to look up. The Help Topic will display in a new window.  IF you want to continue looking up help topics, or need to view more help topics because this wasn't the answer you wanted, click the "Show" button on the far left of the help toolbar. This will display the "Contents," "Answer Wizard," and "Index" Sections of the Help Window. 

The Contents  Tab provides you with a list of general topics that you can read to gain more insight into that particular function of excel. Double clicking an entry will give you more information on that topic or show the information you requested.  When the info displays, it opens in a separate window.

The Answer Wizard page of provides you with a screen like the office assistant's question prompt. Type the question you want to find additional info on in the box at the top and hit Search. 

The Index tab will show you an alphabetical index of available help topic.s Use the first box on this screen to type in the name of the function that you're interested in. The second box displays "keywords" that you can use to assist yourself in refining the search. The bottom box will display matching results. Once you select the material you want, it displays in the right window of the help screen.

SEE PAGES 94 & 95 IN YOUR TEXT BOOK FOR COMMON KEYBOARD SHORTCUTS

3D Formulas

Creating 3D Formulas

Creating a single formula using data gathered from multiple spreadsheets is called a 3D formula. When we use 3D formulas we have to expand our syntax of formulas. Additional punctuation marks are used in order to tell Excel specifically which cells from which spreadsheet we will be using. 

! is used to separate the sheet name from the cell reference. 

$ used to denote an absolute value

: used to separate sheet names in ranged 3D formulas.

, used to separate individual sheet/cell references from each other. 

3D Formula Syntax

Simple 3D Formula:

=Sheet1!B2+Sheet2!B2+Sheet3!B2

Ranged 3D Formula:

=SUM(Sheet1:Sheet3!B2)

or

=SUM(Sheet1!B2,Sheet2!B2,Sheet3!B2)

All of these formulas display the same answer. 

Creating 3D Range References:

In Example #2 you can see a 3D cell range reference used to create a formula. A 3D range is one cell range that spans several sheets deep. In the above example the range is cell B2, but it is cell b3 in all of the spreadsheets from "Sheet1" Through "Sheet 3." 

When Referencing 3D ranges in formulas, use the colon to separate names of the first and last sheets in the range, followed by a single cell range reference. The cell reference given is then used as the selected cell range through all of the worksheets listed. 

In 3D ranges, the given cell range cannot change from one sheet to another. Otherwise it is just another 3D formula, and you should use one of the other two syntaxes. 

Creating a Simple 3D Formula:

1) Select the cell into which you want to enter the 3D formula

2) Type an equal sign to begin the formula

3) Type in the name of the worksheet (on the worksheet tab) of the first page you want to select a cell range from, followed by an exclamation point (!)

4) Enter in the cell range address from the sheet that you want to enter into the formula, followed by the mathematical operator.

5) Select the next sheet you want to select a cell range from, followed by an exclamation point. 

6) Enter in the cell range address from this sheet you want to enter into the formula, followed by the mathematical operator you need

7) Repeat 5 & 6 until you have entered all of the ranges from all of the spreadsheets that you want.

8) Press ENTER on your keyboard

Creating a Ranged 3D Formula:

1) Select the cell where you want to type in your ranged 3D formula.

2) Type in the equal sign followed by the function name you want to use and an open parenthesis.

3) Type in the name of the sheet (on the sheet tab at the bottom left corner of the spreadsheet window) that contains the first cell(s) that you want to include for the function, followed by an exclamation point. 

4) Type in the cell address for the sheet (separated by a colon if it is a cell range) and then followed by a comma to separate this sheet/cell reference from the next sheet/cell reference. 

5) Repeat steps 3 and 4 for the other sheets/cell references you need for your ranged 3D formula. Do NOT type in a comma after the last sheet/cell reference but instead close the 3D Formula with a closed parenthesis

6) hit ENTER on the keyboard.

Creating a 3D Range Formula:

1) Select a cell into which you want to enter the 3D formula

2)  Type an equal sign to begin the formula, followed by the function name that you want to calculate at 3D cel large for, and an open parenthesis. 

3) Type in the name of the worksheet (on the worksheet tab) of the first page, a colon, and then the last work sheet name followed by an exclamation point.

4) Enter a cell range reference for the selected sheets into the formula.

5) Type a closed parenthesis 

6 ) Press ENTER on your keyboard.

Named Ranges

Naming Ranges

Instead of having to type in a cell range formula you can use  name ranges. But, only if you first name a particular cell range. 

To name a range, select the cell range that you want to name, first. Then click into the "name Box" in the formula bar. Type in the name for the new range and hit the ENTER key on your keyboard to set the range name. Remember that there are no spaces allowed in range names use the underscore character instead if you need a space. Now, anytime that you re-drag that specific range, the range name will appear in this name box in the formula bar. 

When you make a cell range, you are making a specific reference to particular cells on a particular sheet. The reference that is created when you make a named range is absolute, you cannot copy and paste a formula that contains a range name and expect it to adjust to its new location. They will always refer back to their original cells (the ones defined as being the cells in the range name) no matter where you copy or paste the formula. 

Moving to a Named Range:

Once you have named the ranges on your spreadsheet, they are contained in the drop-down list that appears when you click the "Name Box" drop-down in the formula bar. Select the desired name from the drop down menu and this will make the range appear selected and the name will appear in the name box. 

Using Named Ranges in Formulas:

You can use named ranges instead of cell range addresses in formulas. Type the name range instead of the specific cell address. One can also change the existing formulas by inserting the named ranges from the spreadsheet into them. First, create the named range that is an exact substitute for a pre-existing cell range reference in one of your formulas. Then, click on the formula cell that contains the cell range reference that you want to replace w/ a named range. Select "insert" from the main menu bar then click "name" and "Apply…" This launches the "Apply Names" dialog box. Click on the names of the ranges to substitute into the selected cells. Click OK to apply.

Creating Names from Headings:

The column and row headings can be used in a structured spreadsheet to name a range of cells. Select the cell range, including any titles that you want to use as the names for the ranges that will be created. Next, select "insert" from the main menu bar and then click "Name" and "Create…" In the "Create Names" dialog box make sure that the titles you want to use as the range names are marked as being in the correct location. USually, the dialog box will not need to be changed. When the correct location of the cells that you want to use as the range names is checked in the "Create Names" dialog box, click OK to create them. Move them by selecting the drop-down at the right end of the "Name Box" and choosing one of the newly created range names from the drop down list that appears.

Deleting Named Ranges:

Once you have made a named range, it is stored with the spreadsheet so you can use it at a later date. IF you no longer need the named range, you can delete it from a list of named ranges in the spreadsheet. To see the list, select "Insert" from your main menu bar and click "name" and then "Define…" This launches the "Define Names" Dialog box. Select the named range that you want to delete from this box, press the "Delete" button to remove the named range from the spreadsheet. Click OK when you are done. 

Naming 3D Ranges

3D ranges can also be named./ As long as the cell range referenced is the same for all of the different spreadsheets in a group, you can name it. There is no way you can "Name" a range that refers to different cell ranges on different spreadsheets. 

Select "Insert" from the main menu and click "Name" and then click "Define…" This brings up the "Define name " dialog box. In the "Names in workbook:" text box, type in the name that you want to give to your 3D range. There are no spaces allowed in range names! In the "refers to:" text box, click the "Collapse dialog" button at the right end of the text box. Click the sheet name tab of the first sheet in the 3D Cell range. Hold down the SHIFT key on your keyboard, and then click the sheet name tab of the last worksheet that you want to include in the 3D range. Click and drag over the cells in the spreadsheet which you want to use as the cell references for the 3D Range. Next, click "expand dialog" button at the right end of the "Refers to:" text box again to expand it. In the "Define Name" Dialog box, click the ADD button. You should see the new range reference appear in the list of range names. Click OK when you are done. 

*Note: 3D Range names will not show up in the Name Box. 

Lesson 5

Spreadsheet Tools

Moving between spreadsheets

To move from one spreadsheet to another, click the tab w/ the name of the spreadsheet you'd like to view from the lower left corner of the main screen area. When you have many spreadsheets in your workbook , or if your spreadsheets have very long names, you won't be able to see all of the titles of the spreadsheets as they will begin to slide underneath the horizontal scroll bar on the right. If this happens, use the first, previous, next, and last buttons on the left of the spreadsheet name tabs. You can also right-click on any arrow button in that group and then select the name of the spreadsheet that you wish to view from the small pop-up menu that appears. This will move you to the selected sheet.

Selecting Multiple Spreadsheets

When multiple spreadsheets are selected at the same time, changes you make to one are applied to all members of the group. You can create a grouping of a spreadsheet from either adjacent sheets or non-adjacent sheets. To select multiple adjacent spreadsheets, click on the first spreadsheet tab you want to group and then hold down the SHIFT key on your keyboard, then click on the last spreadsheet you want to group and let go of the SHIFT key. This selects all of the spreadsheets between the first and last tab you have chosen.

To select non-adjacent spreadsheets, click the first spreadsheet tab you want to include in a group and then hold down the CTRL key and click on other spreadsheet tabs you want to include. Let go of the CTRL key when you're done.

Inserting and Deleting Spreadsheets

To insert select the number of spreadsheets you want to insert as a group first, the number of spreadsheets that you select is the number of sheets you receive. Then select "Insert" from the main menu bar and click "worksheet." The new spreadsheets appear to the left of the selected sheet (in bold). 

To delete spreadsheets select the name tabs for the sheet or sheets you want to delete, and then right click on the sheets. A pop-up menu will appear and you can click the "Delete" command on it. 

Renaming Spreadsheets

o rename a spreadsheet double click on the name tag of the spreadsheet you want to rename. Type the new name in and press enter to set the name.

Switching to Full Screen View

Full screen view eliminates the toolbars and formula bar from the screen. They aren't closed, they are just hidden when you are in this view. To toggle between normal view and full screen view select "View" from the main menu bar and click "full screen." If you select it again, you will go back to normal view.

Splitting the Screen

You can split excel windows into different panes. There are two ways to create a split screen. One way is to select either a column heading or row heading, go to "Window" in the main menu bar and then click "Split." The split will appear above the row selected or to the left of the selected column. Alternatively, you can select the cell in the spreadsheet you want the split to appear above, horizontally, or to the right of, vertically. Double-click the small "Split" buttons at the right end of the horizontal bar (to create a vertical split) or at the top of the cervical scroll bar (to create a horizontal split). 

To remove a split, double click it or select select "Window" from the main menu bar and click "Remove split."

Freezing Panes

Freezing panes are much like split screen views except the panes do not scroll. To freeze the panes, select the cell that is below the row and to the right of the column that you want to freeze. Select "Window" from the main menu bar and then click "Freeze Panes." 

You may also freeze just the top or left pane by selecting the column heading of the column to the right of the columns you want to freeze, or by selecting the row heading of the row below the rows you wish to freeze. Select "Window" from the main menu bar and then click "Freeze Panes" This is a toggle command, so clicking on this command again will unfreeze the panes. 

Copying and Moving Entire Worksheets

To Copy a spreadsheet or spreadsheets hold down the CTRL key on your keyboard and click to select the ones you want to copy. Click and drag the first worksheet tab you selected (its title will appear bold) and drop it where you'd like the new spreadsheets to be pasted. Release the CTRL key. AS you click and drag, the mouse pointer will turn into a white piece of paper with a plus sign next to it. The place which you will be pasting the spreadsheet will be indicated by a small downward pointing arrow as you drag the pointer over the spreadsheet tabs.

To move a spreadsheet from one place to another select the spreadsheets you want to move. Click and drag the first worksheet tab you selected and drop it where you'd like the new spreadsheet(s) to be placed.

You can also right click on a selected spreadsheet tab or tab grouping and then select the "move or copy.." command from the pop-up menu. In the "Move or Copy" dialog box, use the "to book:" drop-down to select either the current workbook, a new workbook, or any open workbook into which you want to paste the sheets. In the "Before Sheet:" list box, click on the name of the sheet in the selected workbook before which you want to paste the currently selected sheet(s). IF you want to copy the sheets vs move the sheets, then check the "Create a copy" checkbox. When you are ready, click OK.

Printing Multiple Spreadsheets

 Select the sheets you want to print as a group. Select "File" from the main menu and then click "Print.." This will launch the print dialog box where you will need to check that the radio button for "Active Sheets" in the "Print What" section of the dialog box is selected. Set any other printing options you need and click OK. 

Hiding Spreadsheets

To hide a spreadsheet within a workbook select the sheet to hide and then go to "Format" in your main menu bar, click "Sheet" and then select "hide". This hides the active sheet or sheets. To view them again, select "Format" from the main menu bar, go to "Sheet" again, and click "unhide…" This launches the Unhide dialog box. Select the name of the sheet to unhide and click OK. 

Using Workspaces

Creating Workspaces

If you have related workbooks that you open at the same time to view different information between them, it might be a good idea to save them as a workspace.  A workspace is a saved definition of what workbooks should open and how to arrange them onscreen. 

To create a workspace, open up the workbooks that you want to save as members of the workspace. Make sure you do not have any workbooks open that you do not want saved in the workspace, including blank ones. Once you have opened all the workbooks you want to include in your workspace, select "File" from the main menu bar and then click "Save workspace…" This opens the save workspace dialog box. Use the "Save in:" drop down to select where you want to copy the workspace and type a name for the workspace into the "File name:" text box. Click Save to save all of your open workbooks as a single workspace.

Now you can open all of the workbooks easily by only selecting the single workspace you saved with the open dialog box. Note that, the workspace only stored the definition of which workbooks to open and how to arrange them- it doesn't store the contents. 

Opening and Closing Workspaces

Workspaces are opened the same way normal Excel workbooks are open, except their icons look slightly different. You will have to set your file type to "all files" or "workspaces."

To close a workspace or to close multiple workbooks at the same time, hold down the SHIFT key on your keyboard and select "File" from the main menu and click "Close all." This closes all open workbooks in excel, release the SHIFT key. 

Selecting Workbooks in a Workspace

There are three ways to select workbooks in a workspace:

1) The active workbook is always on the top, and it's title bar is in color (not grayed out). If you have the windows in the "restore" size (not maximized) you can make any open workbook the active one by clicking the title bar. That workbook will then move to the top. 

2)You can also activate a workbook by selecting its title on the windows taskbar at the bottom of the screen. 

3) By using the "Window" command in the main menu bar; at the bottom of the drop down is a listing of all the open workbooks. The currently selected one appears w/ a check mark in front of its name. If you select the workbook's name from the list, it will become the active workbook. 

Organizing a WorkSpace:

The window command from the menu bar can also organize the onscreen display of your open workbooks. If you select "window" from the main menu bar and then click "arrange…" you will launch the arrange windows dialog box. Select an option button to choose the organizational layout for your open workbooks that you prefer to use. The options are "Tiled," "horizontal," "vertical," or Cascade." Once you have decided, Click OK. 

Tiled workbooks break the main excel screen into smaller tiles of individual workbooks. The "Horizontal" and "Vertical" choices are like the "Tiled" option, but will only stack the open workbooks either horizontally or vertically across the main excel screen. "Cascading" helps you to quickly switch between workbooks while maximizing the amount of viewable space for each. It cascades the open workbooks from the upper left corner of your screen to the lower right, overlapping each other slightly so you can see the title bars.  The Active workbook is the lowest and foremost in a cascaded stack. 

Paste Special

Using Paste Special

Paste special allows you to cut or copy information and then paste only certain elements of the original selection to a new location. I.E. you could copy a formula from a cell and then select to paste it's value, or format only. You can also copy and paste values from cells and perform mathematical operations as you paste the values to new cell locations: adding, subtracting, or multiplying them with the value(s) that already existed in the destination cell(s)

If you copy data from one worksheet and paste it to another worksheet, any changes you make to the original data will not automatically be reflected in the workbook to which you had pasted a copy of the data. Paste special fixes this by allowing you to paste a link between the tow cells so that when the original data is changed in the first worksheet, the new value is also passed to the linked cell in the second worksheet. This also will work between two separate workbooks.

To use the Paste Special function, begin by copying a cell as usual. Select the destination cell. Go to "Edit" in the main menu bar and select "Paste Special…" to launch the "paste special" dialog box. In the dialog box select the option button that represents what to paste from the original cell selection in the "paste" section at the top of the dialog box. Note that if you select the "Values" option, you can then also select a mathematical operation from the "operation" section to perform with the value you are pasting and the value that currently exists in the cell to which you are pasting. Click OK to paste the data. 

Another way to get to the "Paste Special" dialog box is to click the small drop-down arrow to the right of the Paste button in the standard toolbar. This gives a list of the most commonly selected options of Paste special. You can also get to the Paste Special Dialog box by clicking the command at the bottom of the drop-down menu. 

Paste Special Options

In the "Paste" section at the top of the dialog box you can select "all" to paste everything that you copied. This is the same thing as simply choosing "paste " from the standard toolbar. You can select "formulas" to paste the formula only. This is also the default behavior of a formula when you normally copy and paste. Selecting "values" copies the current values displayed in the cell or cells you copied. If you select the "Values" option and select a destination cell into which to paste the copied value that has a pre-exiting value in it, you can then select a mathematical operation to perform with the two values from the "Operation" section. You can choose "none" to overwrite the destination cells' value with the copied value. You can also select "add", "subtract", "multiple", or "divide" to perform those calculations on the two values. 

In the "paste" section, you can select to paste the "Formats" of the copied cells. This pastes only the formatting of the copied cells and will perform the same function the the "Format painter" button performs. You can select "Comments" to copy cell comments from one cell to a new cell. You can select "validation" to copy the cell validation rules from one cell to another. If you select "all except borders" you will paste all of the copied cell contents and formatting, except for the borders. IF you select "Column widths" you will paste the copied cell's column width into the selected cell's column. You can also select "Formulas and number formats" to paste only the cell values with the number formatting to the destination cells.

You can check the "Skip blanks" checkbox to not replace cell data in the destination cells where there are blank values in the copied cells. Checking the "Transpose" checkbox will switch the data in the columns to data that will now display across the rows, and vice versa. 

Click OK once you have selected the appropriate options

Pasting Linked Formulas

To paste a link between two cells so that when the value in the copied cell is changed, the value displayed in the linked cell reflects the change as well start by copying the cell you want to link into another worksheet or workbook. Then select a destination cell to link. Select "Edit" from the main menu and then hit "Paste Special…" click the "paste link" button located in the bottom left corner of the dialog box. This will automatically paste a link between the two cells. 

If you look at the contents of the linked cell, you will see that when we paste a link between cells it actually pastes a formula into the destination cell. The formula states that the cell's value is to be equal to the value displayed in the cell address shown in the formula. 

If you use this feature to paste links between data in separate workbooks you need to make sure you do not rename or move one of these workbooks. Doing so will break any links in the formula references created by the "paste link" button. You will either have to then delete the links and recreate them, or edit the formula references within each linked cell.  If you do not move or rename any workbooks, you can freely change the value in the cell which you copied and save changes. The next time you open the workbook that contains the linked cells, you will see a dialog box that alerts you that the workbook containing links to data in other workbooks has been changed. It then asks if you want to update the values in the linked cell to reflect the changes. Click the "Update" button to update the data in the linked spreadsheet. 

Sharing and Reviewing Workbooks

Sharing Workbooks

A workbook can be shared to allow users on a network to access the workbook and make changes simultaneously. You would set the workbook to be shared and place it on a shared network drive that others can access. 

after sharing a workbook, you will not be able to insert of apply charts, pictures, objects (including objects made w/ the drawing toolbar), hyperlinks, scenarios, outlines, subtotals, data tables, PivotTable report, workbook and worksheet protection, and macros.  You will have to insert or apply these changes prior to sharing the workbook.

To share a workbook, go to tools in the main menu bar and select "Share Workbook…" This will launch the "share workbook" dialog box. In this dialog box, click the editing tab, and check the "allow changes by more than one user at the same time" checkbox. 

If you click the advanced tab you can set up additional sharing options. IN the "Track Changes" section, you can select the option button for "key change history for:" and type a number (in days) to track any changes to the workbook. If you don't want to track changes, select "Don't keep change history." In the "Update Changes" section, you can select the "When file is saved" option to update the workbooks' changes every time that it is saved. You could also select the "automatically every:" option and enter a # (in minutes) that it should save the changes automatically. If you select this option, you can also specify to "Save my changes and see others' changes" or "just see other users' changes." 

When two users edit the same cell at the same time a conflict will arise when excels goes to save the workbook. In the "conflicting changes between users" section, you can set how to resolve those conflicts. You can select either the option for "Ask me which changes win" or the "Changes being saved win." In the "include in Personal View" section, you can include what to put in your custom view of the shared workbook. By default, the view includes any filter or printer settings you make, or you can use the original printer and filter settings. When you open up the shared workbook in the future, it will display using the settings of your custom view, so that each user can have their own view of the data. You can check the "Print settings" and "filter Settings" checkboxes to includes those in the default view.