For tutorial training in Excel, open an Excel file, click on "Help" in the upper toolbar, then click on "Microsoft Office Online", then type what you are seeking in the search engine (i.e. "Excel 2003 Tutorial"). I was able to access several audio courses on different aspects of using Excel 2003, and others are available for different editions.

To Change Text to Upper Case (Capitals)

If you have typed your prospect list and realize that you didn't set your caps lock, or if you are compiling your prospect list from the Data Capture Tool and need to change to all caps, you can use the "Upper" formula in Excel.

  1. Insert two columns next to the one you need to correct, making sure they are in General format.
  2. In the blank cell next to the cell you wish to correct, enter "=UPPER(cell#)" using the specific cell # that you are correcting and hit return. For example, if you are working on the Wife's First Name and have clicked that column and inserted two blank columns to the left of it to work in, you would probably be referencing cell C2 for correction.
  3. Click back on the cell you just entered (B2) and the cell will have a box surrounding it, with a small square in the lower right corner. If you click on that square and drag it down the column to the bottom of the data that you are correcting, Excel will copy the formula for those cells.
  4. Copy/paste the column title from the column you were correcting to the top of your corrected data.
  5. You have now created a correctly-formatted column of data which can be used for your prospect list. Click and drag to highlight the content of this column, then hit CTRL+C to copy it. Click on the second blank column that you will be filling, now click the drop-down arrow under "paste" in the Clipboard section of your Home toolbar, and select Paste Values. Your all caps data should copy into the column. When you have pasted the values into the new column, you can delete the original column and the formatting column, being sure to leave the data in the correct column for the prospect list format.
  6. Repeat these steps for each column that needs to be replaced with all CAPS.

To Prepare A Prospect List for Upload to SCC Connect

Copy the content of your prospect list into a new spreadsheet and close the original prospect list file.

Follow the 6 steps outlined above for changing your text to upper case, but use "=PROPER(cell#)" as your formula. Once you have completed conversion of the Name, Address, and City fields, you can further simplify the upload process by deleting columns that will be unnecessary in SCC Connect. You may choose how much information you wish to keep, but the deleted columns could include extra address columns, country, and the 4 columns at the end that pertain to SCC and event numbers, and survey responses.

When you are satisfied that the content of your spreadsheet is ready for SCC Connect, then select "Save As", name your file (I recommend including a reference to SCC Connect in the name so that you know it is different from your normal prospect list), and select Comma Delimited (.csv) format for saving the file. Now you are ready to upload this data into SCC Connect.

To "Split" 10-Digit Phone Number for Preview

For those of us who find it difficult to proofread the 10-digit phone numbers you can use this trick to help. Be sure that you change the format before you submit it to processing in Assist.

Before you begin typing your contacts, click at the top of column K to highlight the entire column for phone numbers > right click and choose "Format Cells" > then under "Number" tab choose "Special" as the category and "Phone Number" as the "Type".

After doing this, you'll be able to type the 10-digit phone number continuously (no dashes), and when you hit enter, the numbers will automatically separate into phone number format so you can see more easily if you've typed the number correctly - for example, type 8598584307, hit enter and it will look like this: (859) 858-4307.

When you are finished with inputting data and you want the numbers back in the acceptable Sonlight format, just select the whole column again, right click, and change the category from "Special" to "Number", selecting 0 decimal points. All of the numbers will reformat to be continuous.

To Freeze Columns/Rows

When viewing a wide spreadsheet, such as the SCC Analysis, it may be helpful to freeze the first columns of the spreadsheet so that you can scroll across to columns on the far right of the document without losing identification of the events that you are viewing. To do so:

  1. Select the column to the right of column you wish to freeze (where you want the split to appear).
  2. On the Window menu, click Freeze Panes.
  3. To unlock, click Unfreeze Panes on the Window menu.

To Hide Columns/Rows

Another helpful approach when viewing a spreadsheet containing a wide variety of information and needing to focus on one section is to hide columns or rows to simplify the view. To do so:

  1. Select the column(s) or row(s) you wish to hide by clicking on the alphabetic header (for columns) or numerical label (rows) and dragging the mouse to the right or down, respectively, to highlight the columns or rows that you wish to hide.
  2. On the Format menu, point to Column or Row, and then click Hide in menu that appears to the right.
You will be able to detect hidden columns or rows by noting missing letters (columns) or numbers (rows) in the headers. To unhide, highlight the columns or rows on either side of the hidden ones, and on Format menu, point to Column or Row and click Unhide in menu that appears to the right.

To Resize the View of a Worksheet

When a worksheet, such as the 2007 Standards & Goals Comparison, is slightly bigger than can be viewed on the screen, you can resize the view by clicking Zoom in the View menu, and selecting a reduced magnification percentage until you have the view you desire. If you must reduce the view to a level that can't be read clearly, then hiding or freezing portions of the worksheet might be a better approach.

To Tally Responses to Marketing Questions

Set up a COUNTIF parameter to have your spreadsheet tally the Y/N responses to the marketing questions from your contact cards. This will help in responding to the questions on the PEQ concerning the percentage of SL users or new homeschoolers that visited your booth. I generally skip a line after the last entry and put these formulas into the cells below the particular columns.

To have it total the No answers for question #1 in the P column: you would enter =COUNTIF(P2:P__,"N") filling in the number of the last line where I have left a blank. Totalling the Yes answers would be a similar entry, except with "Y" at the end =COUNTIF(P2:P__,"Y").

To total the answers for question #2 in the Q column, you would use a similar entry, but substitute "Q": =COUNTIF(Q2:Q__,"N") and =COUNTIF(Q2:Q__,"Y").

To Change the Text in a Full Column

Primarily directed for use when you realize that you have entered something incorrectly in your Contact List, i.e. Country, Event Date, etc., or when you want to be able to quickly add this detail after you complete the whole Contact List.

  1. Go to the top entry in the column
  2. Type the correct entry in that cell and hit return
  3. Click on the cell that you just corrected, then Ctrl + C to copy the text in the cell.
  4. Click on the second cell in the column and drag the mouse down the column to highlight the cells down to the bottom row of data
  5. Release the mouse button (highlighting should remain), then Ctrl + V to paste the text into the column