Excel Intermediate - Day 2 - Note Sheet

Instructor: Robin Wood - robin 'at' wctrain dot net

Supporting Files:

allformulas.xls

Home Entertainment Systems Worksheet.xls

Formulas

Total Cost (D5) =B5*C5

Average Unit Price (E5) =round(c5/(1-.65),2)

Total Value (F5) =B5*E5

Profit Potential (G5) =F5-D5

Saving a Document as a Template

Click File > Save As

Type the file name and select the Save as type drop down arrow. Select Template from the list of file types.

You should notice that the Save in box automatically changes to the Templates folder on Drive C:\. It's important that templates are saved to this folder. They will then be available when you click File > New.

The path to the Template folder may vary on systems, but should look similar to the image on the right. The first time you save a template you should make a note of the path to the template folder on your computer.

Formatting Cells

Select the cells to format and click Format > Cells. Select the desired cell formatting. It's a good idea to use the same type of formatting throughout the worksheet; this will ensure that all decimal points line up. For this example we will apply currency style with the $ symbol to the first row of numbers and the total row and currency style without a $ symbol to the remaining cells. Alternatively, you can apply Custom formatting; this is handy if you want to apply your own negative number coloring.

Creating a New Workbook from a Template

Make sure that the original template is closed. Click File > New. The new dialog box will appear. Because the file was saved as a template in the Templates folder it will now always be available from the File > New menu. Select the template and click OK. A copy will be created of the original template so that it will remain unedited. If you ever want to edit the original template you will have to click File > Open and navigate to the template folder and open the file. Make the editing changes and resave the file.

Inserting a Blank Worksheet

Right click the Sheet 2 tab and select Insert > Worksheet > OK.

Copying Data from One Worksheet to Another

Select sheet 1 and Press CTRL + A to select the entire worksheet. Press CTRL+C to copy the entire worksheet.

Click the sheet tab to the right of Sheet 1. Hold down the Shift key on the keyboard and select the last sheet in the workbook. This will select all of the sheets that you want to copy to you will notice that the sheet tabs turn white when selected. Press CTRL+V on the keyboard to Paste the copied data to all of the selected sheets. All worksheets in the workbook should have the exact same data.

Drilling Down Data

There may be times where you want the same data to appear on all sheets of a workbook. If multiple sheets are selected in a workbook data that is typed into a cell will appear on all of the selected sheets. You can use the Shift+Click method to select the first sheet and the last sheet in a workbook and this will select all of the sheets between. Alternatively, you can CTRL+Click worksheets to only select a couple of sheets in a workbook. You can also right click any worksheet tab and select Select All Sheets to select every sheet in the workbook. It is important to remember that when multiple sheets are selected data that is typed into a cell on one worksheet will appear on ALL selected sheets. When you no longer want to use the drill down feature, right click any sheet tab and select Ungroup sheets.

Average Unit Cost Drill Down Data

Please be sure to remember to ungroup the sheets before you attempt to make changes to individual worksheets.

Editing the Contents of a Cell

Double click the cell or press F2

Data for Individual Sheets

Pittsburgh Units on Hand

Indianapolis Units on Hand

Phoenix Units on Hand

Creating a 3-D Reference

3-D references allow you to pull data from multiple worksheets; this will allow you to create a summary sheet that totals all worksheets.

Select the sheet that you want to use as the summary sheet.

Select the cell that you want to create a 3-D reference in.

Click the AutoSum tool .

Click the first sheet tab that you want to gather data from and click the same cell that was selected on the summary sheet.

Hold down the Shift key and select the last sheet in the Workbook

Press the Enter key on the keyboard to complete the formula.

These formulas can be copied using the Fill tool, but I would recommend that you create the formulas individually until you feel that you have mastered the technique.

Inserting WordArt

Please see the Word Intermediate Class Notes.

Inserting Comments

Select the cell that you want to insert a comment into. Click Insert > Comment and type the desired comments. A cell that contains a comment will have a small red triangle in the corner of the cell .

You can change the way that comments display on the worksheet in Tools > Options > View.

Select Comment and Indicator if you want to print the comments.

Headers & Footers

Are outlined on the Excel Basics Day 2 handout.