Category Archives: MS Excel

Printing Comments in Excel

Posted by on February 28th, 2013
Leave a reply

Question: I would like to print comments in an Excel spreadsheet. I can’t seem to find a way to do this. Any ideas?
Answer: Yes, you may need to verify that your comments are showing on your spreadsheet first (go to File > Options and click Advanced. Then scroll down to Display and select Comments and Indicators under “For cells with comments show.” Then click ok.

Then to print comments, go to File > Print and click Page Setup (bottom) and click the Sheet tab. Change Comments: to show “As displayed on sheet.” Click OK, and your comments should print.

Counting patterns in Excel columns and Tiny comments in Word

Posted by on February 14th, 2013
Leave a reply

Today I had a couple of questions that stumped me and relied on my best friends, Microsoft Office Support Community and Google to provide the answers. Thanks to these great resources!

Question 1: I was working on a Word document for my boss and can’t read the tiny comments.

Solution: Somehow the styles for the balloon got changed. To change it, go to your styles (click the diagonal arrow in the styles section) and click the Manage Styles icon in the bottom of the Styles window. Sort Alphabetical and find Balloon Text and click Modify to change to a larger font such as 8 or 10 and click OK. This one was on 1 pt, making it impossible to read. For complete instructions with screen prints, see this link.

Question 2: I need to count a series of patterns in an Excel column containing only 0s and 1s. I’m looking for patterns in a column with many rows where there is a 0 and then a 1 in the next row below it. It doesn’t matter if it is followed by several 1s. I just need to count all of the 0′s followed by at least one 1.

I submitted the question to Microsoft Online Community and got 3 answers within an hour.

Solution 1:

Assuming the data is in column A, this is the formula:

=SUMPRODUCT(($A$1:$A$998=0)*($A$2:$A$999=1))

Solution 2:

I like Jeeped’s solution (solution 1) - just remember that if you use it, the formula itself cannot be in one of the cells in the addresses in it.  In other words if you wanted the total at the end of the group you’ve shown in your question, and assuming that goes from row 2 to 17, your formula (in cell on row 18 in same column) would look something like:

=SUMPRODUCT(($A$2:$A$16=0)*($A$3:$A$17=1))

Just as an option, I’ll give you a User Defined Function (UDF) to do the same thing.  A UDF is a special type of macro that can be used in a worksheet formula just like built-in Excel functions such as SUMPRODUCT().

I called this one CountPairs.  And you would use it on a worksheet like this:

=CountPairs(A2:A17)

assuming your list of 1s and 0s is in column A from row 2 through 17.

Here is a web page that tells how to put the code into a regular code module for use:

http://www.contextures.com/xlvba01.html#Regular

Here is the code for the UDF:

Function CountPairs(listRange As Range) As Long
‘place formula such as
‘ = CountPairs(A2:A16) to get count
‘of zeros followed by a 1 in the group
Dim rowPtr As Long
Dim tempCount As Long

For rowPtr = listRange.Cells(1, 1).Row To _
(listRange(listRange.Cells.Rows.Count, 1).Row – 1)
If Cells(rowPtr, 1) = 0 _
And Cells(rowPtr + 1, 1) = 1 Then
tempCount = tempCount + 1
End If
Next
CountPairs = tempCount
End Function

Copying Outlook Groups to Excel lists

Posted by on February 11th, 2013
Leave a reply

Question: I frequently need to send a list of names from a group to Excel. I’m not able to copy and paste directly from the group into Excel. I also tried exporting but all that transfers is the group name. I used to do this all the time with Lotus Notes, but Outlook is driving me crazy on this.

Answer: The procedure outloined below is the easiest way I have found:

  1. From the Contacts view, click to select the group, then select File > Save As and from the File Type drop-down arrow, select RTF format and save it with a name and in a location that you can find easily.
  2. Open that file in Word. The group will probably display in a list with names and email addresses separated with semicolons or tabs.
  3. Select the data and convert it to a table – Insert > Table > Text to Table.
  4. Sometimes selecting the default options works fine, or you may have to modify the columns and delimiter, such as “tabs” to get the names and email addresses to appear in separate columns.
  5. Select and copy (CTRL C) the table (or columns desired) and paste (CTRL V) into an Excel worksheet.

NOTE: You can also “Save as” a text ( .txt) file and open in Excel, then use the Data > Text to columns feature in Excel. But I found the RFT into Word worked better.

Text to columns in Excel

Posted by on January 3rd, 2013
Leave a reply

Question: In order to analyze my worksheet and compare it with another, I need to split cells apart into separate columns. I have been trying to use if formulas, but can’t seem to get it to work.

Answer: I try to avoid complex formulas whenever possible, so the easiest way to do this, as long as there are consistent characters or spaces, is to use Excel’s text to columns feature.

  1. Determine how many columns you will need (including the current cell) and insert them to the right of your cell that needs to split. If you need 3 columns for your data, you will need to add 2 columns.
  2. Select the cells you want to split
  3. Click the Data tab and select the Text to Columns button in the data tools group. The text to columns wizard appears.
  4. Select delimited if you have consistent text. If you have a certain number of characters, you can select fixed width. Click Next.
  5. If you selected delimited, choose the character to use to separate your text. (such as comma) If you don’t see the character, select Other and type in the character). If you selected Fixed Width, click the ruler where you want to separate text.
  6. Assign data type to the new columns (if necessary)
  7. Click finish.

In this example, they needed two digits, so to add zeros before single digits in the new columns. We used the Replace All (found under the Home Tab – Find/Select) to each single digit 1, 2, 3, etc. and replaced all with ’01 or ’02 or ’03, etc. That allowed the zero to display in a text field.

Question of the week: Merging Excel data into Word

Posted by on December 3rd, 2012
Leave a reply

Question: I have data in an Excel spreadsheet that I would like to merge into a Word table. How can I do that?

Answer: First of all, the data has to be in a format that will transfer properly. This may require a lot “clean up.”  You would need to remove any worksheet headings, blank rows and columns, and make sure the first row contains unique column headings without being wrapped or no special symbols, and the data should be in rows without totals or subtotal rows. Any formulas would import in as values.

Your Word document must be set up as a mail merge document, using the column headings as your field codes. There are excellent tutorials at Microsoft on using Word’s Mail Merge features:

Using Mail Merge for Mass Mailings and Using the ribbon to perform a complex mail merge. In addition, ITS is offering a Word Mail Merge session on Wednesday, Dec. 5 from 10-11:30 in the ITS training room (8011 Wittson Hall) in which you’ll learn how to merge Excel data into Word for labels, envelopes, letters and tables. Click here to register.