Excel: Convert seconds to minutes

You’d think that converting seconds to minutes in an Excel spreadsheet would be simple, but it’s not. You can’t just divide by 60 — the result of that will be minutes and decimal fractions of a minute, not minutes and seconds. For example, if you divide 78 seconds by 60, you get 1.3, but 1.3 is NOT the same as 1:18 (1 minute and 18 seconds). Likewise, 247 seconds divided by 60 is 4.11, whereas 247 seconds is actually 4 minutes and 7 seconds. If you only have a couple to do, no big deal — just figure it out in your head. But if you have hundreds or thousands of these conversions, you need a formula for doing that.

To convert seconds to minutes, you have to divide the seconds by the total number of seconds in a day, then tell Excel to display the result in mm:ss format. Simple, huh? ;-)

Here’s what I did…

1. Column G in the screen shot below has a long list of seconds. Because I need to reference column G and put the results into a new column, I inserted a new column (H) and called it mm:ss.
   
2. However, inserting a new column isn’t enough — I have to format the cells in that column so that they display the result in minutes and seconds. To format the column, select the column header, right-click on it, select Format Cells, select Custom on the Number tab, then select mm:ss from the list of types. Click OK.
   
3. Go to the first cell in the new column that pairs with a cell in the seconds column. In my example, that was H4.
4. In the formula bar, type =G4/86400 then press Enter. The seconds convert into minutes and seconds! (But where does that 86400 come from? That’s the number of seconds in a day — 60 x 60 x 24 = 86400. And the G4 was the reference cell for my H4 cell.)
   
5. Now, click in the H4 cell and ‘grab’ the bottom right handle of the cell marker and drag it down the other cells in column H. (See below for how to apply it to ALL cells in the column.)
   
   
6. When you release the mouse, all those cells you dragged this formula over will be converted to minutes and seconds.
   

To apply this formula to the entire column:

1. Copy (Ctrl+C) the result in the first cell with the formula (H4 in my example).
2. Select the entire column (column H in my example) by selecting the column header.
3. Paste (Ctrl+V).
4. You’ll have to rename the column back to mm:ss, but you’ll have that formula now applied to every row of your spreadsheet.

These sites helped me figure out what to do:

• 
  http://www.mrexcel.com/archive/Dates/307.html
  
• 
  http://www.pcreview.co.uk/forums/convert-seconds-minutes-and-seconds-excel-t1772961.html
  
• 
  http://answers.yahoo.com/question/index?qid=20080804112727AA9LUBh
  

[Links last checked April 2012]

Excel doesn’t allow you to view or specify keyboard shortcuts

One of the things I like about Word is that you can specify your own keyboard shortcuts for your macros and for functions that don’t already have a key combination. Or you can modify those that are already assigned in Word (though I wouldn’t advise it, because if you have to reinstall Word or you use someone else’s machine your keyboard shortcuts won’t work the same as the ones you’ve set up for your installation of Word).

Assigning keyboard shortcuts has been a feature in Word for years, and I just assumed that the other Office products would have similar functionality. Not so. I was alerted to this lack because of a comment on one of my blog posts — Ctrl+Shift+C activates the Format Painter in Word, but doesn’t do so in Excel, even though Excel also has the Format Painter function.

Microsoft Excel 2010 (and likely earlier versions), does NOT have any option available to view existing key combinations or to create your own. And on further investigation nor does PowerPoint 2010, Visio 2010, or Outlook 2010 (I didn’t check all the Office programs).

For example, here’s the Word 2010 screen with the option to assign a keyboard shortcut (found under File > Options > Customize Ribbon):

Word 2010 allows you to assign a keyboard shortcut

And here’s the same screen in Excel 2010 — nothing!:

Excel 2010 doesn't let you to assign a keyboard shortcut

I think this is a really big oversight by the Microsoft Office team. Those who use these products all day, every day, can be incredibly efficient using keystrokes instead of the mouse. I can’t believe that Microsoft has denied users the ability to add their own to existing functions.

Note: I understand that you can still assign a keyboard shortcut to a macro you create in Excel etc.

See also:

• 
  http://superuser.com/questions/212583/how-do-you-customize-excel-2010-keyboard-shortcuts
  
• 
  http://answers.microsoft.com/en-us/office/forum/office_2010-customize/excel-2010-customize-keyboard-shortcuts/6b707c53-cbca-451e-8343-86b719e418ca
  

[Links last checked January 2012]

Excel was around before electricity!

How else can you explain this from the Excel 2007 recovery window? 1601? Really??

Excel: Force a line break in a text cell

Need to force a line break in an Excel cell?

Just press Alt+Enter where you want the line break to go.

This also works for Excel data used in Word mail merge fields — you can force the text in the mail merge to go onto another line by adding the line break in the Excel cell containing the data. Very handy for a multi line address when you only have, say, two or three cells for the address fields but need four or five lines for some addresses.

Excel: Save a worksheet as an image file

You can save an Excel worksheet to several formats, but, surprisingly, you cannot save it — or a selection from it — as an image. I would have thought this simple function would have been an option in Excel 2007 or 2010, but it’s not.

Saving to PDF (and then to an image format) is an option if the worksheet is not particularly wide, but as soon as the worksheet stretches over more than one page, PDF is not suitable unless you want to cobble together several PDF files to make one image.

After trying several methods and searching the internet, I figured out the best way to do it; this method works in Excel 2003, 2007 and 2010:

1. Select the worksheet cells containing the data, chart, etc. you want to save as an image (turn off grid lines if you don’t want them too).
2. Copy the selection to the clipboard (Ctrl+C).
3. Open a graphics editor. My personal preference is SnagIt, but Microsoft Paint works just as well.
4. Paste the selection into your graphics editor (Ctrl+V).
5. Save the pasted image as a GIF (suitable for simple line drawings), JPG (suitable for photos only), or PNG (suitable for almost anything).

From my internet searching, it sounds like this something many people want to do, so it would be good if Microsoft could offer a ‘save as image’ option from Excel.

Aside: Some wit on the internet had this to say about how to save an Excel worksheet as an image:

Take a photograph using your phone, send it to your home email,  then sketch an artist’s impression of said Excel sheet based on the photo content, scan that into your hard drive, send back to work and save it.

Another had this method:

1. Stick a sheet of tracing paper to your monitor.
2. Then make a brass rubbing of the Excel table.
3. Remove the tracing paper and pour plaster of paris over the top.
4. Cover in spray mount and glitter.
5. Shake off the excess and voila!

[Link last checked June 2011]

Excel: Reference lists in another worksheet

Scenario

I was asked to set up a mail merge letter for my client. I knew I could create a ‘database’ in Excel and refer to that for the mail merge fields in the Word document. However, some of the fields would have data in them that would be used in every letter, and this data was ideal for a drop-down selection list.

Such selection lists simplify the process for the person entering the data in the Excel spreadsheet, save them time, and make the data entry more accurate as they can’t make spelling errors or typos.

For example, one of the mail merge fields I created was for the type of document that was referred to in the letter; the only acceptable document types were:

• Appendices
• document
• Plan
• Program
• Report
• Supplement

I knew I could create the data lists for the drop-downs in Excel (thanks to a great session on just this by Michael Hughes at the 2009 WritersUA Conference), but I wanted them on their own worksheet, and I wasn’t sure how to reference them from the data entry sheet (I’m an Excel novice!).

Solution

In this tutorial, I’ll show you how to set up a list in Excel 2007 and how to reference it from cells on the main data entry worksheet.

Bottom line: Reference cells in another worksheet by adding the worksheet name plus an exclamation mark between the = sign and the cell range (e.g. =Lists!$A$2:$A$7 for a range of cells on the Lists worksheet).

Step 1: Name the worksheets

1. Open a new Excel document.
2. Change the name of Sheet1 to MailMergeData — this will be the data entry sheet.
3. Change the name of Sheet2 to Lists — this sheet will contain the list items for the various fields.
4. Save the document. For this example, call it MailMergeData.xlsx.

Step 2: Name the data columns

1. Go to the MailMergeData sheet.
2. In Row 1, enter the names of the data fields that you’ll use in your mail merge letter — one in each cell. Don’t use spaces, symbols or punctuation in the names, and don’t duplicate any names — each has to be unique. Try to make the names meaningful and recognizable; these names will be listed in the mail merge field selection list, so you need to be able to figure out what they are. (In the screen shot below, I’ve used RecFirstName and RecLastName for the recipient’s first and last names, RecOrganization for the recipient’s organization, DocType for the type of document.)
3. For ease of reading, format Row 1 so that the field names are obvious (e.g. increase the font size, add a background color, make the font bold, etc.).
4. Save the document.

Step 3: Set up the list items for the selection lists

1. Go to the Lists worksheet.
2. In Row 1, Column A, type the name of the data field for that particular list (this name is only for you/someone else to identify at a later date — it isn’t used in the mail merge nor is it critical to the drop-down lists you create; I tend to use the same name as that in the MailMergeData sheet, but this isn’t essential.)
   
3. Move to the next column in Row 1 and type the name of the next data field that will have a drop-down list.
4. Repeat step 3 for as many lists as you need to create; remember, not every data field in your mail merge dataset will have a selection list.
5. For ease of reading, format Row 1 so that the field names are obvious (e.g. increase the font size, add a background color, make the font bold, etc.).
6. Underneath Row 1, Column A, type each list item, one per cell. Try to keep them in alphanumeric order as neither Excel nor Word sorts them automatically.
7. Add the list items for the other data fields.
8. Save the document.

Step 4: Find and select the list item range

Next you’ll select each list item range to find out their row and column values.

1. Go to the Lists worksheet.
2. On the Data tab, click the Data Validation button, then select Data Validation. This opens the Data Validation dialog box.
3. In the Allow field on the Settings tab, select List.
   
4. Click the icon to the right of the Source field to open a small Data Validation window.
   
5. Select all the items in one of your lists by clicking and dragging your cursor over those cells.
6. As you do this, the selection is shown in the Data Validation field as something like =$A$2:$A$7, which indicates everything in column A, rows 2 to 7 inclusive, as shown in the example below.
   
7. Press Enter to show the full Data Validation dialog box. The cell range is displayed in the Source field.
8. Select everything in the Source field and copy your selection to the clipboard.
9. Click Cancel as you don’t need this dialog box again for this list.

Step 5: Add the list item range to the mail merge field

In this step, we’ll select the field in the MailMergeData worksheet and use data validation to link to the other worksheet’s item list.

1. Go to the MailMergeData worksheet.
2. Select the first empty cell in the column where you want the drop-down list to go. In the example below, I selected the first cell under the DocType heading.
3. On the Data tab, click the Data Validation button, then select Data Validation. This opens the Data Validation dialog box.
4. In the Allow field on the Settings tab, select List.
5. In the Source field, paste in the range you copied to the clipboard a few seconds ago.
6. Put your cursor IMMEDIATELY after the = sign, and type Lists! You should end up with something like: =Lists!$A$2:$A$7 (Lists is the name of the worksheet you are referencing and the ! tells Excel to look for that cell range on the Lists worksheet, not the current one. Make sure you type the worksheet name correctly, otherwise it won’t work — List is not the same name as Lists.)
   
7. Click OK to save your changes and close the Data Validation dialog box.
8. As soon as you click OK, you should see a drop-down arrow next to the cell you’re in.
9. Click the arrow to test that the list displays as you expect it to.
   
10. If it works as you expect, apply this range to other cells in this column by dragging the little square icon at the bottom right of the cell down the column to cover as many rows as you want. For example, if your mail merge dataset typically has 20 individuals, then drag it down 20 rows.
    
11. Save your changes.

Quick way to link to the same list range but for different columns

If you have several lists set up on the Lists worksheet, then instead of going through many of the steps above, you can just do the steps once, then, once you have your initial range (e.g. =Lists!$A$2:$A$7), you can copy that into other Data Validation dialog boxes and change the column letter and row numbers to reflect the other list. For example, let’s say you have another set of items for a drop-down list that are in column E, rows 2 to 12 — change the ‘A’ in =Lists!$A$2:$A$7  to ‘E’ and the ’7′ to ’12′, giving you =Lists!$E$2:$E$12. Leave everything else as it is.

Alternative method

Based on the post above, Peter Sanders made this short video on using Range Names instead:
http://www.screencast.com/t/jKKSD7zeDzac
Being an Excel novice, I knew there would be a more elegant solution than mine, and Peter has provided it. Thanks for your contribution, Peter!

See also:

• 
  http://www.homeandlearn.co.uk/excel2007/excel2007s8p7.html
  

[Links last checked June 2011; thanks to @michaelhughesua for his conference session related to this, and to Peter Sanders for the video]

Word: Change date formats from Excel data

When you’re using an Excel spreadsheet for mail merge data in Word, any dates come in in the ‘native’ Excel date format even if you’ve changed the date format for the relevant cells in Excel. I use the date format dd mmmm yyyy in Excel, but it comes into a Word mail merge date field as mm/dd/yyyy, which is not what I want at all.

If you want the date in the mail merged document to be displayed differently, then you have to add a switch to the mail merge field in Word.

Here’s how you do it:

1. Insert the mail merge field for the date into the Word document as normal.
2. Right-click on the mail merge field, and select Toggle Field Codes.
3. Put your cursor after the closing “ of the field name (e.g. “Date”) and before the closing } then add a space at that position.
4. Type in the switch: \@ “d MMMM yyyy” (this switch converts the Excel date into something like 25 December 2011; if you want 25 Dec 2011, then enter “d MMM yyyy”).
   
5. Right-click on the mail merge field again, and select Toggle Field Codes.
6. Save the document. The next time you run a mail merge, the date will be in the format you entered at Step 4. (You can quickly test it works by clicking the Preview Results icon on the Mailings tab [Word 2007].)

You can get more information from Word’s online Help for other options for this  field switch — search for Date-Time Format switch in the Help.

See also:

• Word: Change decimals from Excel data:
  http://cybertext.wordpress.com/2009/06/18/word-change-decimals-from-excel-data/
  
• 
  http://cybertext.wordpress.com/2009/06/27/word-change-date-format-from-excel-data/
  

[Links last checked May 2011]

Word: Change date format from Excel data

When you’re using an Excel spreadsheet for mail merge data in Word, any dates come in in the ‘native’ Excel date format even if you’ve changed the date format for the relevant cells in Excel. From what I can gather the ‘native’ Excel date format is the US date format of m/dd/yyyy (e.g. 9/30/2009 for September 30, 2009).

If you want the date in the mail merged document to be displayed differently, e.g. UK/Australian date format dd MMMM yyyy (30 September 2009), then you have to add a switch to the mail merge field.

Here’s how you do it in Word 2003 (Word 2007 is probably the same, though I haven’t tested it):

1. Insert the mail merge field for the date into the Word document as normal. It will look something like this (where StartDate is the name of the mail merge field in this example):
   
2. Right-click on the mail merge field, and select Toggle Field Codes.
   
3. Put your cursor after “StartDate” and before the closing } and add a space.
4. Type in the switch: \@ “dd MMMM yyyy”
   
5. Right-click on the mail merge field again, and select Toggle Field Codes.
6. Save the document. The next time you run a mail merge, the date will be in the format you entered at Step 4.

This example shows just one date format switch — experiment with other combinations to get the date format you want. For example, “MMMM dd, yyyy” for September 30, 2009; “dd-mm-yy” for 30-9-09, etc.

Word: Change decimals from Excel data

When you’re using an Excel spreadsheet for mail merge data in Word, any numbers — such as those for currency — come in in the ‘native’ Excel number format even if you’ve changed the number format for the relevant cells in Excel. From the experimentation I did, a number such as 13733.735 displayed in an Excel cell as 13733.74 but came into Word as 13733.735000000001 — not a very friendly number to work with (and what’s with the 12 decimal places, and where did that 1 come from?).

Here’s how it looks in Excel:

And here’s how it came into Word:

If you want the number in the mail merged document to be displayed differently, e.g. to two decimal places, then you have to add a switch to the mail merge field.

Here’s how you do it in Word 2003 (Word 2007 is probably the same, though I haven’t tested it):

1. Insert the mail merge field for the number into the Word document as normal. In this example, the merge field is called Amount.
   
2. Right-click on the mail merge field, and select Toggle Field Codes.
3. Put your cursor after the closing “ of the field name (e.g. “Amount” and before the closing } and add a space.
4. Type in the switch: \# 0.0x
   
5. Right-click on the mail merge field again, and select Toggle Field Codes.
6. Save the document. The next time you run a mail merge, the number will be in the format you entered at Step 4. (You can quickly test it works by clicking the View Merged Data button on the Mail Merge toolbar.)
   

This example shows just one number format switch — experiment with other combinations to get the number format you want. For example, \# 0 for whole numbers; \#0.00x for three decimal places etc.

For example: displays as .

You can get more information from Word’s online Help for all sorts of other things you can do with these field switches — search for numeric picture switch in the Help and expand the Picture Items icon at the end of that topic.

Word 2003: Import an Excel 2003 table

A client wanted to know the best method of getting Excel 2003 tabular data into a Word 2003 table, so I spent an hour or so testing the various methods available from the normal Copy, Save As and Print options in Excel and the Insert and Paste options in Word. I did not test any third party specialist programs.

I won’t go through every method I tried as some were so bad they’re not worth talking about (how does a single paragraph with all the cell contents in it sound?? Or one line for every piece of cell data with NO formatting?).

The testing I did was on a reasonably simple table in a single worksheet, not on something with linked worksheets, formulas etc. so the method I recommend here may not be the best one if you have a complex Excel spreadsheet.

I also didn’t test capturing the Excel data as an image as the authors needed to be able to change it once it was in Word.

Bottom line: The methods recommended by Microsoft in their Help topic “Copy Excel data and charts to Word or PowerPoint” are the best, particularly the option to paste and format to match the destination table style.

That said, you may still have a little bit of work to do once the table is  in Word, but it shouldn’t be too onerous.

Here are the steps I used to get the data from Excel into Word:

1. Copy the Excel data (just the cells you want to include in the table).
2. In Word, paste the copied data.
3. When you see the little ‘paste options’ icon (bottom right of the pasted table) for what to do with the copied data etc., click it and select Match Destination Table Style.

Once the table is in Word, you’ll may still have to do some of these:

• Apply any special formatting to the header and the rows (Format > Borders and Shading and/or Table > Table Properties)
• Set the table header to repeat (Table > Heading Rows Repeat)
• Autofit the table to the window (Table > AutoFit > AutoFit to Window)

If the author has added ‘pseudo’ bullets and line breaks to the Excel data, you may have a bit more to do.

You can’t add paragraph marks to an Excel cell — you use a workaround [Alt+Enter] to insert a soft line break. If the author has used these, you may need to replace the soft line breaks with paragraph marks:

1. In Word, select the table you just inserted.
2. Open the Find/Replace window (Alt+H).
3. In the Find what field, type ^l (that’s a lower case L for Larry).
4. In the Replace with field, type ^p (the p must be lower case).
5. Click Replace All.

This looks for the soft line breaks (^l) and replaces them with paragraph marks (^p). The caret (^) is important, as is lower case for the letters in the find/replace strings. These steps will clean up the soft line breaks and make them proper paragraphs.

Now for the ‘pseudo’ bullets…

You can’t add bullets in Excel 2003 — instead you fudge around it by inserting a symbol and choosing something that looks like a bullet, typically a small filled in disc symbol. Unfortunately, there’s no real option for these except to find them with your eyes and manually delete them. Selecting the pseudo bullet symbol (e.g. a filled disc) and copying it into Word’s find/replace changes it into a lower case O (for orange), so that’s not a solution as you don’t want to replace the letter ‘o’ everywhere it appears in the table! The only time you might be able to use find/replace would be if the author had consistently added two spaces after the ‘bullet’ in Excel, then you could look for “o<space><space>” and replace it with nothing, or if the table only had numbers not letters.