Using Excel’s Fill Handle

September 28, 2011 by

The Fill Handle is very useful for copying the contents of a cell or extending a series of data.  It is represented by a little blue square in the lower right corner of a selected cell.

To fill in a series of data:

  1. Enter the first part of your data set.
    • For instance, “1, 2, 3″ or “Monday, Tuesday, Wednesday”
  2. Select the data you just entered.
  3. Place your cursor over the Fill Handle, your cursor will change to a black plus sign.
  4. Click-and-drag the Fill Handle to extend your data set.

You can use these same steps to copy the contents of a single cell over a range of cells.

[Read more...]

Filed Under: Colby Technical Training, GTWO, How-to Tagged With: , , , , ,

Restricting Editing to Individual Cells in Excel 2007

April 29, 2010 by

What if you only want to protect a single, or several single cells, in an entire Excel worksheet and allow users to edit or input data in the rest of the sheet?

Let’s say you have a worksheet like this:

protect-4

You would like your department members to edit the “data” in the black text cells, but you want to protect the “data” in the red text boxes from accidental changes.

First, you need to unprotect the entire worksheet. That sounds counterintuitive.
But Excel protects all cells in the worksheet by default whenever you set protections, so we need to turn that off in order to only protect individual cells.

To do this:

Click on the worksheet selection cell at the top-right corner of the worksheet to select the entire worksheet protect-5
Click the down-arrow on the Format icon on the Cells tab protect-1      
Click on Format Cells…
This will display the Custom Lists dialog.
Uncheck the “Locked” checkbox, then click “OK”.protect-6 		 protect-2
Now select the cell or cells you wish to protect.
(Select multiple cells by holding down the SHIFT key for contiguous cells or the CTRL key for non-contiguous cells.)		 protect-7
Click the down-arrow on the Format icon on the Cells tab again and select “Lock Cell” protect-1
In the above example, we have now locked 3 cells.Now, Click on the Format icon again and this time click on “Protect Sheet” and uncheck “Select locked cells“. Then click “OK”

This prevents users from selecting – and editing – the cells that we locked in the previous step.
Be sure to leave “Select unlocked cells” checked – or your users will not be able to edit any cells at all!

protect-8

 protect-2

Save your worksheet and close it.

Now anyone who opens the worksheet will only be able to select and edit those cells that are not locked.

 

Filed Under: Colby Technical Training, GTWO, How-to Tagged With: , , , ,

Concatenating Text Columns in Excel

April 22, 2010 by

Adding “@colby.edu” to a List of Simple Usernames

What do you do if you need to send out a mailing and all you can have is an excel list of simple usernames: no @ sign, no “colby.edu”. 

Will you have to edit each one by hand? No!

These easy steps will save you a ton of work:

1. You will need 4 empty columns to the LEFT of the column of usernames.
 It should look something like this:		 email-2
2. Insert the “@“ sign in the top cell of the second column – then copy and paste it into all the remaining cells of that column. email-3
 
email-4
3. Now enter “colby.edu” into the top cell of the third columns – then copy and paste that text into all the remaining cells in the column. email-5
 
email-6
4. Enter the function: =CONCATENATE(A2,B2,C2) into the top cell of the fourth column and press Enter.
Note: You will need to adjust the cell identifiers (A2, etc) to match the positioning of the data in your spreadsheet.		 email-7
5. Copy and paste the function into the remaining cells. email-8
 6.  Now you need to copy the VALUES of the function in the last column into a new column that you will use for the mail merge.WHY?Since the text in the last column is actually the result of a FUNCTION, it:

1. May not insert correctly into your mail merge document. It might carry over as the “=CONCATENTATE” formula rather than the email address itself.

2. Will revert to an error if you delete either of the two work columns you created to create the email addresses

To Solve This:

Just COPY all the newly created email addresses and PASTE them into a new column using the Paste Values option under Past icon at the top left of the ribbon.

You can now delete all 3 extra columns you created since nothing is now pointing to any of them. And you will have usable email addresses.

  email-9
Filed Under: Colby Technical Training, GTWO, How-to, Knowledge Base Tagged With: , , , ,

Hyperlinking Between Worksheets in Excel

March 29, 2010 by
You probably create links to web sites in Excel all the time. But did you know that you can create a hyperlink to different worksheets in the same workbook? Internal hyperlinks will save you time and – if you create an “Overview” sheet at the front of workbooks you want to share with team members – enable users to quickly link to the exact place they need to go without hunting through multiple worksheets.

Creating an Internal Hyperlink

  • Launch Excel and open the workbook to which you want to add an internal hyperlink.
    For demonstration purposes, we will have a workbook with four worksheets: (Resources, Team Members, Budget and an Overview page like this:

hlink-1

  • Select the cell you want to make your hyperlink.

hlink-2

  • Insert > Hyperlink from the Ribbon
    Click Place In This Document on the left
    Specify the text you want the linked cell to display in the Text To Display text box
    (Go to Worksheet)
    Select a worksheet
    Click OK

hlink-4

  • Your sheet will now look like this:

hlink-5

  • Clicking on the link will take you to the top-left of the Resources worksheet.
    If you wanted to go to a specific cell on that worksheet, such as D12, you can specify that cell in the hyperlink dialog:

hlink-6

  • Now you sheet would look like this:

hlink-7

  • Clicking on the cell link will take you to that exact cell on the referenced worksheet
  • Hyperlinks can also be used to quickly move around a single sheet which has many entries and categories
Filed Under: Colby Technical Training, GTWO, How-to Tagged With: , ,

Converting text Case in Excel

March 18, 2010 by

The Problem: You have an Excel spreadsheet of names of clients/students/alumnae, and they are all in UPPER CASE.

The Task: You need to convert them all to “Proper Case” so you can use the list in a mail merge (for example). And, of course, there are about 2,000 names!

The Good News: It’s not as tedious as you might think.

Converting all uppercase text to proper case (as in proper names) is a 3-step process.

Step 1: Making Room for the Conversion

Create a blank column next to the column that contains the upper case text. If both first and last names are in the same cell, you only need 1 extra column. If the first name is in one column and the last name is in another, you will need 2 extra columns.

Here is a 2-column example:

case-2

Step 2: Converting the Text

  • Position your cursor in the blank column, in the cell immediately to the right of the first name you need to convert
  • Click on down-arrow in the Function Icon

    case-3

  • Click on More Functions…
  • This will display the Insert Function dialog box

    case-4

  • If the PROPER function is not listed (as it is in the above example) type the phrase “all caps” in the Search for a function text box.
  • Click on PROPER
  • Click OK
    This will display the Function Arguments dialog

    case-5

  • Click on the selection grid icon

    case-6

  • Then click on any cell containing all UPPERCASE text
  • Press ENTER
    The dialog should now look something like this:

    case-7

  • Click OK
  • Now the two cells should now look like this:

    case-8

  • Next, we will populate this function into all the new empty cells
  • Copy the single converted cell CTRL-C or Ctrl-OpenApple-C (You are actually copying the function)
  • Position your cursor in the top-most cell that remains to be converted and drag your mouse to select all the remaining items in the column that need to be converted

    case-9

  • Paste the function into all selected cells CTRL-V or Ctro-OpenApple-V
  • Now all your text should be converted

    case-10

  • Copy a converted cell and repeat the paste operation in the second column if you have one
  • This example now looks like this:

    case-11

Step 3: Converting the Function into Plain Text

The problem we have now, is that if we delete the upper case column, the Proper Case text in the new column will also disappear. This is because the contents of the column is actually the function that is converting the text – not the text itself – and the function is referring to that upper case column.  For example, if we were to select one of the converted cells, we will see in the text entry box of the spreadsheet a function statement – not the text of the name. (as shown below)

case-12
So we have to do something to convert the function into plain text.

 To do this:

  • Select all converted text
  • Copy it CTRL-C or Ctrl-OpenApple-C
  • Click on Paste > Paste Special >Values
  • Click OK

    case-13

This will replace the function with just the value that it returns. So instead of PROPER(M16), the cell now contains “Doe”. If we were to click on the cell containing converted text, we see the actual text in the text entry box as shown below:

case-14

Now you can delete the columns containing all caps and use the text in any way you wish.

case-15

Filed Under: Colby Technical Training, GTWO, How-to, Knowledge Base Tagged With: , , , ,

Excel

February 3, 2010 by

Gridlines and Borders in Excel are Not the Same

They may look the same visually, but they do not act the same.

Gridlines are used to make cell boundaries visible. You can change their color under Office Button>Excel Options>Advanced. You can set them visible or hidden.

grid-1

You can also set them to Print.

borders-3

However – as soon as you change the color or border of a cell – or of any adjoining cell – the gridlines will be lost. So you may end up with a very uneven application of lines and colors within a worksheet.

Borders can have different colors, line types and widths. You can assign different borders to multiple cells in a worksheet. Borders will always print and will not disappear if you change the color of a cell. 

Borders-2

Borders-1

 To set borders for a single cell or a group of cells:

1. Select the cell or group of cells

2. Click on the Border Icon

This will display the borders dialog box (at left)

3. Select the type of border you want

4. Select the type and color of the line

5. As soon as you click outside the dialog box, the border selection will be applied.

 

NOTE: If you sort the data, border and background colors of cells WILL NOT transfer with the sorted data.

Filed Under: Colby Technical Training, GTWO, Knowledge Base, Tutorial Tagged With: , , ,

Tracking Changes in Excel 2007

January 15, 2010 by
1 Click on the “Review” tab on the Ribbon in Excel 2007
  review-tab
2 The “Changes” group is on the far right hand side of the Ribbon
  changes-group
3 Click on “Track Changes” and “Highlight Changes”
  Track-changes
4 Check “Track changes while editing.  This also shares your workbook.”
  highlight
5 Select what you want highlighted
  highlight-2
6 Click OK

(source: The New PaperClip)

Filed Under: Colby Technical Training, GTWO, How-to Tagged With: , ,

Extracting Data by Cell Values in Excel

December 22, 2009 by

 Let’s say you have a large workbook of names and addresses and you need to find only those folks listed with area codes of 207 and 613. You could sort the data by area code and copy and paste after each section – or you could use the FILTER function.

Here’s how:

ExtData-1 Select the entire range of data you want to filter for a specific value(s)
SortFilter Click the SORT&FILTER icon on the right side of the Ribbon
Filter Select “Filter“
dropdown The selected range should now have drop-down arrows at the head of each column
SelectFilter Click the down-arrow on the column that you want to Filter on.Uncheck the “Select All” box.Check the values you want to filter for.Click OK
NumberFilters Note: If you click on “Number Filters“, a list of options will display. The default is “Equals“.If you wish to use another strategy, select it here.
Outcome The outcome of this action is shown to the left
Clear To clear the filter, click on the down-arrow again and select “Clear Filter…”The range will return to normal
text If you had chosen to sort the text column instead, you would have been presented with text options as shown to the left.
Filed Under: Colby Technical Training, GTWO, How-to, training Tagged With: , , , , ,