Digital Streets

If you have text in two or more columns that you wish to join (concatenate) in another column, this can be easily done by creating a formula that utilizes the ampersand (&). I have found this to work in Microsoft Excel, OpenOffice’s Calc, and iWork’s Numbers.

For our example, we will use a scenario where a list of users’ first and last names are given to you and your boss wants them to be in a single column instead of two.

You could take the time to copy and paste the names together in a column, but there is a much easier way. We can go to C2 and input the formula: =A2&” “&B2 we would then get the concatenated string of Bob Smith in the new cell.

We could use =B2&”, “&A2 and produce Smith, Bob in the new cell.

Copy the formula to the rest of the cells in the column to concatenate the desired cells.

If you use Microsoft Excel, then you’ve run into the situation where the information in a worksheet is needed in another workbook.  You could go through and copy and paste the cells from one worksheet to another, but if you are working with a large spreadsheet, this is way too painful.  Excel makes it easy to copy (and even easier to move) worksheets from one workbook to another.

Copying a worksheet to another workbook:

1. Open both the workbook that has the desired worksheet and the one that you want the worksheet copied to.  (in my example, we will use Book3 and Book4).
2. Go to the workbook with the desired worksheet.  Right-click on the tab of the target worksheet (in this case, test sheet).

   

3. Select Move or Copy from the context menu.

   

4. When the Move or Copy window appears, use the To book dropdown to select the workbook that you want to copy the worksheet to.  Then select the existing sheet that you want the copy to be placed in front of.  Finally, check the Create a copy checkbox.

   

5. Click OK.  You will now have the same worksheet in both workbooks.

Moving a worksheet to another workbook:

You can use the above steps and simply exclude checking the Create a copy checkbox, but there is a simpler and quicker way to do this:

1. Have both workbooks open.  Do not maximize either one, you want to be able to see both of them.

   

2. Left click and drag the tab of the target worksheet to the desired workbook.

   

3. When you release the mouse button, the worksheet will be in the targeted workbook and no longer in the original spreadsheet.

I often run into situations that I need to use the Windows Calculator while working in Excel.  This is mainly due to needing to run some figures while not wanting to modify the existing spreadsheet.  It is very handy to add the Calculator to the Excel 2007 Quick Access Toolbar so that it is a simple click away instead of using the normal method of opening the program.

To add Calculator to the Quick Access Toolbar, simply follow these steps:

1. Open Excel 2007 and go to the Quick Access Toolbar at the top of the window.

   

2. Click the downward point arrow to the right of the toolbar and select More Commands.

    

3. From the dropdown menu, select Commands Not in the Ribbon.

   

4. Select Calculator from the list and click the Add button.  You will see it appear in the list on the right side.

   

5. Click OK.
6. The Calculator icon will now appear in your Quick Access Toolbar.  Now you can easily get to it when you need it.

   

Comments can be a useful aide for your worksheet by helping others interpret your spreadsheet. They can also be a great help for you if you only access the sheet from time-to-time and need little reminders to remember what your thought process was. To add a comment to a cell:

1. Select the cell which you’ll add a comment.

2. Click Insert then select Comment.

3. Type the comment into the box.

4. After you have finished typing the comment, click outside the box..

Cells with comments will have a red triangle in the upper right corner. To view the comment, place the mouse pointer in the cell. The comment appears to the right of the cell. The comment will disappear once the pointer is moved away from the cell. If you wish to edit the comment, simply right-click the cell and select Edit Comment.

Change The Function of the Enter Key

Normally, when you press the Enter key, Excel goes to the next cell down. The Enter key can be made to move to in any direction (up, down, left, right) or leave you in the same cell. To change this, go to Tools | Options and select the Edit tab. Change the value in the dropdown for Move selection after Enter direction setting or uncheck the box to remain in the same cell.

Change The Number of Worksheets That Excel Starts With

Excel, by default, creates new spreadsheets with three sheets. To change this number go to Tools | Options and select the General tab. Set the number for Sheets in New Workbook.

Other Tips:

How To Bypass Table Recalculation

How To Hide Formulas from Other Users

How To Copy Excel Column Widths

Copy Formatting from One Cell or Range to Another

How To Copy Formatting From One Chart To Another

How To Email an Excel Workbook

Calculating Elapsed Time

How To Reference Cells in Other Worksheets

In Excel, you can work on two worksheets (from two different workbooks) by placing them in vertical, side-by-side windows. When you have two workbooks open at the same time, select Window, Compare Side by Side With.  After you select this command, a floating Compare Side by Side toolbar will open in Excel. The toolbar includes the Close Side by Side button, which you can use to close the windows as soon as you’re done comparing or transferring data between the two. While the two workbook windows are in open in Excel, you can select different worksheets and scroll to different regions in either one by using its sheet tabs and scroll bars that appear at the edge of the window. To make workbook active, just click the title bar or one of the cells of its worksheets.

When linking an Excel worksheet to another MS Office file (Word document, PowerPoint slide, or another Excel worksheet) use a range name (Insert - Name - Define) rather than a range reference. The range name can tolerate changes to the worksheet that contains it, whereas a range reference remains tied to the specific cells it references. For example, if you delete rows from the worksheet, a range reference will deliver the wrong data, but a range name will deliver the right data.