Question: We have a spreadsheet that is a several thousand lines made up of three types of rows: User:, Patient:, and those that start with a plus sign. Do you know a way to remove (delete) all of the rows that start with a plus sign without changing the order of the rows?
Answer: You may select the sort/filter icon from the Home tab in the ribbon (or from Data tab). Then click the filter down arrow on the heading of that column and unselect the plus sign on the + entries and keep checks in the User and Patient fields. This filters out the plus signs and only the User and Patient rows will display. You can then copy and paste those records to a new file, just in case you need to refer back to the original data.
Question: How can one merge excel currency cell into Word letter mailing to print correctly? All things that I have tried prints the amount as15897 instead of $15,897. I just wondered if you have a quick fix so I don’t have to go in manually and fix all of the amounts.
Answer: I have a fix, but it isn’t a real quick fix, but it is much faster than manually entering hundreds of values. And once you have it set up in your Word options, you just have to remember to select the correct conversion for all formatting to convert properly. Follow the steps below for Word 2007/2010.
- The most important step is to change your Word options (File > Options) Click Advanced and in the General settings place a check mark on “Confirm File Format conversion on Open” and click OK.
- You must re-create your merge file (I prefer to use the Mail Merge Wizard pane under Mailings > Start Mail Merge).
- Indicate your document type and click Next. of and browse for your Excel file to select.
- You will be prompted with a screen “Confirm Data Source” with OLE DB database files. Do NOT select this. Instead, check Show All, which will display several data sources.
- Scroll to select MS Excel Worksheets via DDE (*.xls) and the entire spreadsheet or a specific spreadsheet if the workbook contains more than one.
- You’ll see your columns display (not necessarily in the same order as it was in Excel) and the currency format (as well as other cell formatting) should be retained.
- Continue your mail merge.
For help with general mail merge steps, go to Microsoft free online training. http://office.microsoft.com/en-us/word-help/mail-merge-i-use-mail-merge-for-mass-mailings-RZ010277841.aspx?CTT=5&origin=HA010215566
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.
Question: I have an Excel spreadsheet with 3000 names that I would like to create folders for on my shared network drive. We need to keep files and other information for these people and would like to have the names stored in folders by As, Bs, etc. . Is there an easy way to do that?
Answer: There may be a procedure to do that, but I think a more efficient way to keep track of names and subsequent information for them would be to create a OneNote2010 notebook on a shared network drive. This is a perfect project to show the powerful capabilities of OneNote.
Create sections of the OneNote notebook with the Letters of the Alphabet and then create pages for each student. Within these pages you can create subpages which can hold files, notes, images, recording, etc. You can create this in just a few minutes by Importing the list from Excel into Outlook 2010 Contacts, and then select the “A” contacts and click OneNote in the ribbon. This will send the Contact information to OneNote pages. Do this for each section. See more detailed steps below:
- In your Excel file, make sure your workbook contains only one worksheet with headings at the top. It is best to have headings Name and E-Mail for it to import correctly into Outlook.
- Name your data range in Excel and save the file in 2003 (.xls) format.
- Go to your Outlook contacts view and and right click on Contacts to create new folder – call it something like “Students”
- Click File > Open > Import > Import from another program or file.
- Verify that the names imported correctly into your Outlook Contacts.
- Create a OneNote Notebook on your shared network drive.
- Create sections for the letters of the Alphabet.
- Go back to your Outlook contacts and select the names you wish to place in the A section (using Shift or Ctrl to select the names) and click OneNote in the ribbon and place in the appropriate notebook section. Continue this for each section of names. You could also select all Contacts and click OneNote, sending all names at once. Then create new pages for each letter of the alphabet and indent the contact pages by dragging slightly to the right, creating subpages.
- You can now add notes, drawings, images, files, Outlook messages, appointments and even recordings into OneNote pages. You can also create lists or tags which you can search on.
Learn more about OneNote 2010 in our basics class coming up 4/22 from 1-3 p.m. which you can sign up for by going to www.unmc.edu/its/training.htm . You can find several other tutorials and resources on OneNote at Microsoft, including:
Question: I’m having problems using a file from Excel to merge into a Word Letter. I am able to select the Excel file as the data source, but when I select the table nothing displayed and an error said “unable to open the data source.” The Excel file was created by someone who is no longer here, so I don’t know if there was any kind of protection preventing this, but we are able to go in and edit the file.
Answer: This is a mystery. After looking at the file, I noticed there were several blank rows, but those would normally merge as blank records. I tried copying the data to another worksheet within the workbook and saved the file. Then closed the file (the Excel Worksheets needs to be closed to allow the merge) and then selected the new worksheet, and the merge worked fine. I wasn’t able to see any protection on the original worksheet, but copying the data to a new worksheet was a way of working around the problem.