Questions and Answers

Text to Columns and Concatenate

Teri Asked: What delimiter do you select when the list is last name, first name -- They have used both a comma & a space?

Answer: In the Text to Columns Wizard, you can choose more than one delimiter.

Karen Asked: Is there a way to use a hard return as a delimiter when splitting columns? I exported an address book from Outlook and all lines of the address block ended up in one cell, separated by hard returns (looks like a square).

Answer: When you export Outlook contacts to Excel, be sure to use Outlook’s Import and Export Wizard. Choose to export directly to an Excel file. As you walk through the wizard, click the button to Map Custom Fields and click the Default Field Map button. This assures that all of Outlook’s fields are exported correctly. If you follow these steps, you should have no problem with the address block splitting correctly. The only issue you might have is with two line addresses. You’ll have to manually edit these.

Nancy Asked: How were adding a comma in the formula?

Answered: To add a comma when using Concatenate, put the comma, and space if you want one, in quotes. For example, =D2&", "&D3

Graciella Asked: ìf you want commas you put them in front of the quotes?

Answer: No, you put the comma between the quotes. The quotes identify any text you want to include in the result.

KCALSI Asked: Once two columns have been combined will Excel sort this data or only recognize formulas?

Answer: You can sort data that has been combined but it no longer sees it as two or more values. You would be sorting on the first character of the combined value.

Linking Pictures and using the Camera

Christy Asked: Will changes to the original data be shown when you use the camera?

Answer:Yes it is a dynamic link.

N Asked: Is "camera" available in 2002?

Answer: Yes, the Camera is available in all versions of Excel.

Vickie Asked: Where is the camera again on the customization drop down

Answer:

1.      Go to Tools Ø Customize.

2.      On the Commands tab, choose Tools in the Categories list.

3.      Scroll down to find Camera

4.      Drag the Camera to a toolbar.

Carla Asked: can one picture-link between two workbooks instead of worksheets?

Answer: Yes, you can use Picture Link to link dynamically between two workbooks.

Mark Asked: Can the paste picture link reference cells in another spreadsheet?

Answer: Yes, but both need to be available to the user.

Sharing Workbooks and Tracking Changes

Pam Asked: Do you get a warning or email when history is going to be deleted?

Answer: No, there is no warning that the change history is being deleted.

Mary Asked: Can change history be kept in a workbook that is not shared?

Answer: No, the change history is a feature of a shared workbook. However, you can share a workbook and keep it to yourself in a private folder.

Teresa Asked: Do you have to share the workbook to track the change?

Answer: Yes. But you can store it in a location that other users don’t have access to.

Chrissy Asked: Can workbooks be shared if you are on a peer-to-peer network vs. a network with a server?

Answer: Yes, workbooks can be shared on any kind of network as long as the other person has access to the workbook’s location.

Gloria Asked: Is there anyway you tell who the last user was?

Answer: You can't tell if someone opened it and didn't save it, but you can see who last modified the file. Choose File > Properties, click the Statistics tab and you'll see the name of the last user to modify a file.

Crystal Asked: Is there a workbook administrator that can control the properties or can anyone change properties?

Answer:  The person who shares the workbook controls the settings. Other users can, however, unshared the workbook and share it with different settings.

Karen Asked: So WHERE do you find the change history. I mean is there something like an audit log with timestamps? (I'm a database person). Is there a log that tracks *access* to an XLS?

Answer: After you've shared the workbook, return to Tool > Track Changes and enable the List Changes on a New Sheet checkbox, and Excel will create the audit log you're looking for.

Debbie Asked: We've got a shared report that is used by 10 people. We've been experiencing problems where an individual has made their changes but when it is opened a day or so later, sometimes the changes are not reflected. I can't figure out what is causing this. What tips can I give staff to prevent this from happening? Is it possible to get that information back through the Change History?

Answer:  A suggestion: double check the conflict setting (Tools > Share Workbook to open the Share Workbook dialog box, then click the Advanced tab). Make sure Ask Me Which Changes Win is checked. If it isn't, when two users both have the workbook open, the last person who saves "wins". If this setting is correct, make sure that all users are using the same workbook (for example, make sure that your user who's "losing" changes isn't opening a local copy on their hard drive.). Sounds silly, but I've seen this happen before. If it's neither of these issues, turn on change history, log in it a worksheet (see Karen's question above) so you use the log to figure out what's happening. 

Vanessa Asked: Will the track changes work only if a workbook is shared?

Answer: Yes, Sharing must be turned on to use Track Changes.

Donna Asked: To share a worksheet, do I need to be using SharePoint or does Excel ask the name of the person who can share the file?

Answer: Neither. When you place the workbook in a shared folder, anyone who has access to the folder can access the workbook.

List

Dave Asked: Which pull down was List under?

Answer: Under the Data menu, but only in Excel 2003.

Julie Asked: How do I turn on auto filter?

Answer: Click in your database (list) and choose Data > Filter > AutoFilter

Cheryl Asked: Can you select rows to view?

Answer: You can select rows based on criteria you select using the AutoFilter drop-down lists.

Toni Asked: What if you wanted a total of just flights out of Detroit?

Answered: When filtered on Detroit, the Subtotal formula that List displays shows only the total for flights out of Detroit.

Erin Asked: How do you get the drop down arrow in your header?

Answered: It's a filter -- you get it automatically if you turn on a list (Data > List > Create List) or choose any cell in your database and choose Data > Filter > AutoFilter

Views

Linda Asked: Could you use the Ctrl button to select all the columns you want to hide at once?

Answer:  You bet! Choose the first column, then hold Ctrl while you click on each of the additional columns.

ArturoMeza Asked: Customs View option is disabled, why?

Answer: One possibility: if Header and Footer and Comments were also disabled, you may have been editing a cell when you went to the menu.

Kyle Asked: Can you use the custom view with an Access Datasheet view when working with Access?

Answer:  The custom view only works in Excel.  

Debby Adams Asked: when creating customized views, does that "save" with the file for others to use too?

Answer: Yes, Custom Views are saved with the workbook so anyone who opens the workbook has access to the views.

Michelle Asked: If the program is exited and there are custom views saved, will they stay saved for when I reopen it?

Answer: Yes, Custom Views are saved with the workbook.

AndieC Asked: If you change data in the original view, does the data change in the custom views?

Answer: Yes, the views save only what cells are displayed, not the content of the cells.

Robyn Asked: on custom views, can you put a formula in on the right column that will sum (or other functions) only those columns that displayed?

Answer: Not without writing some code. Hiding a column doesn't hide it from Excel, just from users! This would be a nifty function, though.

Miscellaneous Questions

Paste Special

Lauren Asked: I recently was trying to transpose a column to a row and followed the directions as indicated by Microsoft Help. (copy, paste, right click the button next to the paste icon) There was supposed to be an option for Transpose, but I didn't have that as an option.

Answer: Click Edit > Paste Special. You'll find Transpose on the Paste Special dialog box.

Ross Asked: Is there an easy way of converting the formula to a value?

Answered: Copy the formula result, click Edit > Paste Special and then click Paste Values.

Printing

Yvaine Asked: Will it print also the titles of the row (1, 2, 3...) & colums (A, B, C...)

Answer: You can print column and row headings by selecting Row and Column Headings on the Sheet tab of Page Setup (File > Page Setup).

Manon Asked: Is there a shortcut to copy data and formulas to additional worksheets in a workbook?

Answer: You can copy an entire worksheet, including data and formulas. Right click the sheet tab and choose Move or Copy Sheet, or hold Ctrl while you drag and drop the tab. 

Cynthia Asked: Can excel do outline with columns?

Answer: I'm not sure what you're asking. Excel has a group and outline feature that will automatically outline a selection based on the formulas used in the selection. If you have formulas that span columns (like row totals), Excel will outline them.

Abbe Asked: Does Microsoft have a special program to keep track of purchase orders and invoices?

Answer: Yes, they have small and large business accounting packages. For a small businesses, Microsoft Small Business Accounting 2006 can be used to track purchase orders and invoices.

Debra Asked: I have a workbook with several tabs, I want all columns to fit a page in landscape, all tab pages the same width & columns the same width. Each new page to use the top three rows of information to identify columns.

Answer: Use the Sheet tab in Page Setup on the File menu to identify the rows you want to print (Rows to Repeat at Top). You could also set the Fit to One Page Wide option on the Page tab of the Page setup Dialog box to assure that each worksheets prints on only one page. Column widths you’ll have to set manually.

Miscellaneous

Mary Lou Asked: Is there a way to make envelope labels from an excel sheet of names and addresses.

Answer: Yes, use Word’s Mail Merge feature to create the labels from the Excel data.

Julie Asked: How do you link a list of names, phone numbers to other worksheets?

Answer: That depends on why you want to link them. If, for example, you want to be able to enter a name and return a phone number, look for information on VLOOKUP or the Lookup Wizard Add-in in the Excel Help file. If you want to just view them, you could use the Camera (or Paste Picture Link) to display the list on other worksheets.

Carol Asked: how can you extend green shading when you selected all data to be added and click the AutoSum button?

Answer: you can use conditional formatting to format every other row (or every third row) green, or you can manually color the rows (or use the Format Painter to copy the formatting to new rows).

Sandy Asked: If you are copying a document from excel to a Doc file, can you change the Excel file and will it automatically change the doc file?

Answer: If you copy data from Excel and paste it into Word as a linked object, the Word document will be updated when the Excel workbook is saved. See Paste Link in the Excel Help file for more information.

Joanna Asked: Where do you find the fill handle?

Answer: Lower right corner of the selected cell.