Posts Tagged ‘Excel’

h1

Excel table into Word — quick and easy method!

August 25, 2014

I’ve written before about some methods for getting the data in an Excel spreadsheet into Word (see the links below).

Recently, one of my colleagues had a BIG Excel spreadsheet table (all text) that had to be inserted into a Word document. In this instance, inserting a link to the spreadsheet was not an option, and saving it as multiple images and inserting those was problematic too (fuzzy, almost unreadable text mostly). So in despair my colleague asked me for some help.

In the process of testing many options I stumbled on a solution, but then I forgot which of the many combinations of things I tried was the one that worked ;-) So after a bit more testing I got it to work again.

Here’s how:

  1. Save the Excel spreadsheet as either a MHT or HTM file (doesn’t matter which – both work).
    excel_word01
  2. Open this saved file in Internet Explorer. NOTE: This method DOES NOT work if you open it in another browser, such as Firefox or Chrome — you must open it in Internet Explorer.
  3. In Internet Explorer, go to File > Edit in Microsoft Word, which opens the spreadsheet in Word as a table.
  4. You may have a little bit of fiddling to do (e.g. remove blank columns, resize columns, change font size), but it shouldn’t be much.
  5. Select the reformatted table, copy it, then paste it into your main Word document (or save it as a new document if you aren’t inserting it into another document).

See also:

[Links last checked August 2014]

h1

Word: Getting an Excel table into Word

July 10, 2013

Typically, you have two choices when you want to insert an unlinked Excel table into a Word document:

The choice you make will depend on what you need to do – or not do – with the data once it’s in Word. If you don’t need to modify it, then insert it as an image; if you do need to modify it, then insert it as a Word table.

Related articles:

For inserting a linked table and other options see: http://office-watch.com/2016/putting-excel-into-word/

[Links last checked July 2103; based on a Writing Tip I wrote for my work colleagues]

h1

Excel: Convert seconds to minutes

April 27, 2012

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:

[Links last checked April 2012]

h1

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

February 1, 2012

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 postsCtrl+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

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

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:

[Links last checked January 2012]

h1

Excel was around before electricity!

September 8, 2011

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

h1

Excel: Force a line break in a text cell

September 5, 2011

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.

h1

Excel: Save a worksheet as an image file

June 29, 2011

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 is 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]