So many times, I see people using Word when they could get the job done much quicker and easier in Excel, and sometimes it’s the other way around. Here are my tips for when to use each product.
Use Excel when:
- You are typing in rows and columns with numbers – especially dollar signs – why type in the $’s and the comma’s and full stops in a number when excel does it for you automatically
- if you find yourself using decimal tabs, (or more to the point typing figures without using decimal tabs), it’s probably easier to use excel – most people don’t know how to use tabs properly.
- If you have any calculations at all – yes Word does do calculations but do you know how to do them – they are not obvious and easy to use. Also see my next tip on Calcuators and why to avoid them.
- If you have rows of text with repeating words in some of the rows – see my next tip on Excel lists to find out why this is important
When to use Word:
- If you have paragraphs of text – excel is not good at creating space before paragraphs (you can do it, but with a macro), so word is better at this
- If you want fancy headers or footers – Excel has limited options for headers and footers (see my tip in Office Watch from back in 2002 about this topic)
- If you have a document with complex layout – eg Portrait one page then a table in landscape on the next page – Word Sections work well
- if you have numbers within sentences, then you are going to have to type the dollar signs in anyway, so Word is best for this
Using Excel and Word Together
Sometimes you are in a word document and you need a table of numbers or dollars – you can do that part in Excel, format it to look like the word document, but use all Excel’s calculation features so there is no nasty addition errors, then paste or link the contents into Word. There are a few ways to do this
- In Excel, select the area then Copy; in Word, Paste – it pastes the contents as text
- As above but Paste Special… then choose Paste Link – inserts it as a Microsoft Worksheet Object – just make sure your Excel workbook is saved first so you can find it easily. This way the cells are editable but the numbers and text are linked.
- Paste Special as a Microsoft Worksheet object – similar result as above but you can’t edit the cells individually – you need to double click on the object and it opens back up in an excel window right inside your Word doc. Can be a bit cumbersome
- Paste Special as a Picture – This is my favourite way – you can’t edit anything in the word document so you know that it is never corrupted, and the formatting is exactly as it was in Excel. To change it you need to go back to the Excel Spreadsheet, re-copy and then re-paste it as a picture in Word. I use this exclusively in my quotations – I have my costing sheet password protected and only the picture in Word, so no one can change the quotation to a lower price without me knowing about it.
- In Word you can insert a spreadsheet directly within the page Insert > Object… Microsoft Excel Spreadsheet – but I find you get more control if you set up the spreadsheet separately and use one of the paste methods above.
Pasting an Excel spreadsheet into a Word document doesn’t work very well for a large Excel table that will span over a page break in Word. For this scenario, I find it easier to keep the Excel table as an addendum in the document that you can insert into the printed or PDF version before sending the document out.