Excel - FAQ

****************************************
To Add Two Cells--
A3: =A1+A2

also--
A10: =SUM(A1:A9)
****************************************
To do a running total add the cell above with the one to left like this--
A1: 1000
A2: 3000
A3: 4000
B1: =if(A1 <> 0, A1, "")
B2: =if(B1 <> 0, B1+A2, "")
copy the formula in B2 down the column
****************************************
Cross totals work like this--
A1: 1000
B1: 4000
C1: 11000
D1: =SUM(A1:C1)
****************************************
Adding various (non-contiguous) cells--
D20: =D4+D8+D13+C19
****************************************
You can link to other cells in another worksheet or other workbook.  Try this--
1. open two workbooks
2. click on the cell that you want to pull the data into
3. type =
4. choose Window > theOtherWorkbook
5. choose the sheet tab that you want from the other workbook that is now visible
6. click on the cell that you are trying to pull in
7. enter  a plus sign
8. click on another cell that you want to add to the first one
9. press the enter key.
****************************************
If you want to change the column width--
1. Drag the column separator between the column letters to the right.
****************************************
To change a cell to show no decimal places--
1. Right click on the cell and choose format cells.
2. Click on the general tab.
3. Choose the category 'number' and make it 0 decimals.
4. Press OK
****************************************
How To Create A Sequence Of Numbers--
In cells A3:A5 enter 1, 2, 3.  Highlight only these cells (A3:A5). Drag the little square "period" in the bottom right hand corner of the bounding rectangle down to the last student in the list.  Numbers will be sequentially generated.  If you want column headers A, B, C etc., you can use the same technique with letters of the alphabet.
****************************************
To create a merged cell, perform the following steps--
1. highlight two or more cells (left click and drag from one cell to another)
2. Right click in the highlighted area and choose Format Cells.
3. Choose theAlignment tab
4. Put a check in the merged cells checkbox.
5. Press OK
You can now apply formatting to the cell such as centering the text and it will work accross both cells that have been merged.
****************************************
To determine if there are duplicates in a list--
1. Enter 0,8,2,3,4,5,6,7,8,9 in cells A1:A10
2. In cell B1 enter
=IF(SUM(IF($A$1:$A$10=$A1,1,0))>1,"Y","")
and do not press enter yet.
3. Hold down the control + shift and then press enter one time.  The formula will look like this--
{=IF(SUM(IF($A$1:$A$10=$A1,1,0))>1,"Y","")}
4. Copy the formula down to cell B10
A Y will now appear wherever there is a duplicate.
****************************************
To add the drop list arrow for each cell with validation, perform the following steps--
1. Create a new blank workbook
2. Enter a list of items on Sheet2
3. Choose Insert > Name > Define from the menu and enter a name for this range starting with a letter and no spaces.  I used myNamedRange for this example.
4. Select range by pressing the little button to the right of the 'Refers To' edit box.
5. Use the mouse and highlight a range of cells.
6. Press the little button next to the 'Refers To' edit box again to bring the Define Name dialog back.
7. Press the OK button.
8. Return to the worksheet that you want to validate cells on (Sheet1 for this example).
9. Select the cells to apply validation to.
10. Choose Data > Validation > Settings.
11. Choose List from the Allow options.
12. Enter =myNamedRange in the Source edit box.
13. Press OK.
****************************************
To copy from one worksheet to multiple sheets--
1. select the cells to copy.
2. click on the first worksheet to copy to
3. either hold down the control key and click on each individual worksheet tab or go to the last one, hold the shift key down and click on the last tab.  This selects a range.
4. click on the first sheet tab. (this will not unselect worksheets)
5. click on the target cell.
6. select edit > paste special values and choose formats, col widths or whatever you want to copy and press OK
All the worksheets are now done.
****************************************
To delete rows that have a specific string in one of the columns--
1. Assuming it's the A column, highlight the A column to the bottom of the data
2. Choose Edit > Find and enter the string to search for
3. Press the Find All button.  This provides a list of cells.
4. Click on one of the cells in the list and press control + a to highlight them all.
5. Close the Find Dialog box
6. Choose Edit > Delete and select 'Entire Row'
7. Press OK.
8. Scroll back to the top of the page.
****************************************
To copy a table from Word to Excel--
1. Open the Word document
2. Click in the first cell in the table.
3. Choose Table > Select > Table
4. Choose Edit > Copy
5. Open a blank Excel workbook
6. Click on the first cell at the top
7. Choose Edit > Paste
****************************************
To convert a single row of email addresses to a huge string delimited by semi-colons--
1. From Excel, File > Save As and choose comma separated values (CSV).
2. Open in Word
3. Choose Edit > Replace
4. Enter ^p in the first edit box
5. Enter ; in the second one
6. Press replace all.
****************************************
To Freeze the top row of cells--
1. Choose Window > Split.
2. Drag the horizontal bar to the first or second row.
3. Drag the vertical bar off screen to get rid of it.
4. Choose Window > Freeze Panes
****************************************
To protect a worksheet so that all but a few cells are editable--
1. First select all the data on the sheet.
2. Next right click and choose format cells.  We're interested in the protection tab and uncheck the lock cells box.  Now the sheet is opened up.
3. Individually select the cells to lock by holding down control + left mouse click on cells.
4. Right click on any one of the selected cells and choose format cells.
5. Choose the protection tab and click on the locked cells check box.
6. Finally, choose Tools | Protection | Protect Sheet and enter a password and press OK.
Individual cells are now locked.

To protect a sheet so that only a few cells are editable do the following--
1. First select the entire sheet.
2. Right click in the selected area and choose 'Format Cells'.
3. Select the protection tab and put a check in the box marked 'lock cells'.
4. Now the entire group of cells is locked.  Click OK to apply this.
5. Next select the individual cells to unlock by holding down the control key and clicking on them.
6. When all the ones you want are selected randomly about the worksheet, right click in any one of the selected cells and choose 'Format Cells'.
7. Select protection tab again and this time uncheck the 'locked cells' item.  Press OK to commit this change.
8. Finally, choose Tools | Protection | Protect Sheet and enter a password and press OK.
Individual cells are now open for editing while the rest of the sheet is locked.
****************************************
To change cell colors--
1. Right click on a cell
2. Choose Format Cells
3. Choose Patterns
4. Select the background color for the cell
****************************************
To modify the color palette--
1. Select Tools > Options > Color
2. Click on one of the color swatches that you would like to change.
3. Press the 'Modify' button
4. Select a color from either palette-- standard or custom
5. Press OK and OK

Return to the cell formatting dialog and the new color is available.

****************************************
To create a chart on another worksheet than the one the data is in--
1. Start on the worksheet with the data
2. Select the data.
3. Select Insert > Chart
4. Choose the chart type and press Next until you are at the step 4 of 4 panel.
5. Choose 'As Object In...' and select the other worksheet.
6. Press Finish
****************************************
To add a data validation list--
1. On a different sheet from the main one, we typed in a list of names from A1 to A6
2. Highlighted A1 to A6
3. Clicked Insert > Name > Define
4. Typed "namelist" without quotations into the box, clicked Add
   (for a different list use a different name)
5. Select the original worksheet (where we want the list)
6. Select the cell (or range of cells) to contain the dropdown list
7. Clicked Data, Validation
8. Changed Allow to "List"
9. in source, typed "=namelist" without quotations and pressed OK
****************************************
To Collapse a List on Unique Names--
1. Choose Data > Filter > Advanced Filter
2. Select the names as the list range.
3. Select the names as the criteria range.
4. Check 'unique names only'.
5. Press OK
****************************************
Also, to select and copy only those cells that are selected in an auto filter list--
1. Filter on a column.
2. Highlight the range of cells that you can see
3. Select Edit > Goto.
4. Press the 'Special' button.
5. Choose 'Visible Cells Only'
6. Press OK.
7. Select Edit > Copy
8. On a new worksheet, Select Edit > Paste
****************************************
To copy a worksheet to a new workbook--
1. Create a new workbook
2. Open an existing workbook
3. Right click on the worksheet tab to copy from
4. Choose rename and give the sheet tab a meaningful name (optional)
5. Right click on the worksheet tab to copy from
6. Choose the workbook to copy to
7. Choose move to end
8. Press OK
****************************************
To Get Page Numbers to Flow Accross Multiple Sheets--
1. Open a workbook with multiple sheets.
2. Hold down the control key and click on all the worksheet tabs that you want to include in a single print job.  This will turn all the tabs white (selected).
3. Choose File > Print
****************************************
To Add a Line Across a Column Chart--
1. Open a new blank spreadsheet
2. In cells A1:A5 enter 4, 5, 2, 1, 9
3. In cells B1:B5 enter 6, 6, 6, 6, 6
4. Highlight cells A1:A5
5. Select Insert > Chart
6. Choose the Column chart type and use the first optional sub type.
7. Press Finish
Now you have a chart to start with
8. Right click on the white area of the chart and choose Source Data.
9. Choose the Series tab.
10. Under the series list box, press the 'Add' button
11. Highlight the new series name in the listbox (it should already be selected).
12. Click the button to the right of the values edit box to put this dialog into cell selection mode.
13. Highlight cells B1:B5
14. Unpress the cell selection button now so the dialog reappears.
15. Press OK
16. Right click on the new columns that were just inserted into the chart next to the existing ones.
17. Choose Chart Type
18. Choose Line for the Chart Type and the first sub type.
19. Press OK.
You now have a line running through your column chart.  You can do this mutiple times.  Right click on the line and choose format data series to change it's properties.****************************************
****************************************
Create Multiple Columns From a Single List--
This is most easily done using Word; however, I'll provide the Excel method after I show the Word technique.
1. From within Excel, select the entire range of phone numbers.
2. Choose Edit > Copy.
3. Open a new Word document
4. Choose Edit > Paste Special > Unformatted Text
5. Now select them in Word and Choose Edit > Cut
6. Choose Format > Columns
7. Choose Three and Press OK (You may need to choose the first item in the clipboard)

Here's how in Excel--
1. Enter a list of numbers on Sheet1:A1:A100 from 1 to 100
2. Enter the following on Sheet2--
A1: =INDIRECT("sheet1!a" & (ROW()*3-2))
B1: =INDIRECT("sheet1!a" & (ROW()*3-1))
C1: =INDIRECT("sheet1!a" & (ROW()*3-0))
A2: =INDIRECT("sheet1!a" & (ROW()*3-2))
B2: =INDIRECT("sheet1!a" & (ROW()*3-1))
C2: =INDIRECT("sheet1!a" & (ROW()*3-0))
A3: =INDIRECT("sheet1!a" & (ROW()*3-2))
B3: =INDIRECT("sheet1!a" & (ROW()*3-1))
C3: =INDIRECT("sheet1!a" & (ROW()*3-0))
****************************************
Here's a formula that always gets the data from the cell above even if you insert a row in between--
1. Enter 1-5 in cells A1:A5
2. In cell A6, enter =INDIRECT("A"&ROW()-1) and press enter
3. Right click on row number 6 and choose insert.
4. Type the number 6 in the new A6 that's blank
The formula in A7 will have the value that's in A6.
****************************************
To Change Links or Formulas Into Their Values
1. select the range of cells with the links
2. choose edit > copy
3. choose edit > paste special
4. select the values radio button
5. press OK
****************************************