The Detail Department

You are here: Home / Archives for Excel

Productivity Podcast

by

I did a podcast with Cameron Reilly on the Productivity Show on Friday. We did it at a cafe in Melbourne, so the sound is probably really crap, and I will not be surprised if he doesn’t even put the Podcast up. But here are some things I learnt about doing the podcast.

  • My motivation was not self promotion (as Cameron will probably say in the podcast) but it was from another podcast that I was listening to the other day where Cameron said he had an “obligation to share his knowledge” or something like that. It just inspired me so much, as I too feel that, since I know lots of stuff about Microsoft stuff and productivity, I too have an obligation to share my knowledge. – So I need to do some more blogging on Excel
  • I was prepared, had 4 pages of notes in my small notebook but I was very nervous and all the preparation went out the window. It is very intimidating sitting at a cafe on a sidewalk on a busy street with a huge microphone in your hand whilst trying to eat lunch, drink coffee AND sound intelligent and entertaining – hats off to you Cameron, you are a bloody legend!
  • I talked about crap and did not leave time for the main thing I wanted to talk about which was my Excel tips, so I will have to make up for that by blogging about that here. I talked too much about myself, and no one is interested in that.
  • I forgot to attribute!!!! Bad internet faux pas!!! I had it in my notes to attribute that I found the David Seah excellent Emergent Task Tracker by reading the blog of GTDWannabe – an excellent Blog – but in my nervousness I forgot, so I apologise.
  • Podcasting is very different to lecturing and speaking – Podcasting is a conversation, so it is very different to what I am more familiar with and feel way more at ease with. I can stand up in front of a group of people and talk about a topic for hours and feel confident and capable, but in the more intimate setting of a recorded conversation, I was crap. I think I need to stick with what I know.

Well, I hope this podcasting thing is out of my system now, but my passion for sharing my knowledge is not, so I will continue to share my knowledge in writing – not podcasting. But after all is said and done, I had a blast doing it, and it was worth doing, just to experience it. So my advice to you is shout Cameron a lunch (or buy some TPN advertising on Ebay) and get yourself on a Podcast, because you just might be a great podcaster but don’t yet know it.

Thanks Cameron!!

Excel Comparison Tools Reviewed

by

I downloaded and trialed 5 excel comparison tools this week and thought I’d share my thoughts on them – they seem to fit into 3 camps, text based, link based and excel based. My review is going to be skewed toward my criteria as it was very specific. Here’s my criteria

  • As my spreadsheets are HUGE (this one has over 30 sheets, but one I have has over 200 sheets in it), I needed one that would cope with big and cumbersome spreadsheets (don’t worry they will be FIXED on the next project).
  • I just needed to be able to compare values, not formula as i had checked most of the formula’s previously and there seemed to be no major formula consistency issues.
  • I needed to have the results either appear in the spreadsheet OR link to the spreadsheet as I needed to see the differences in context of the surrounding data.

Here are the products I tried:

I quickly discounted Diff Doc – it had a great slick website but it was a text based comparison tool that meant it converted the spreadsheet to text first before comparing. I just tried a simple summary spreadsheet and it showed all the columns in the results window not lined up, so it was basically impossible to use.

ExcelDiff and 4 Tops re-create the spreadsheet into a HTML report with hyperlinks back to the spreadsheet – useful but it doesn’t help to show the context of where the differences are and not having the underlying spreadsheet’s formatting is not helpful.

Excel compare is similar to above but presents the differences in Excel, but still lacks the ability to see your whole spreadsheet in i’s original context with the differences shown.

DiffEngineX has a great feature that allows you to insert rows and / or columns to make the 2 spreadsheets layouts the same. It’s almost worth buying it for that. It also shows the differences directly in the spreadsheet. I’m not sure why I discounted this one, it may be just that Synkronizer did 95% of what I wanted.

So, Synkronizer was my winner. On comparing one sheet it took just a few seconds then displays the differences hilighted in the colours you choose – different colours for inserted and deleted rows and columns, different values and even different formula’s (this could come in handy). The program removes all your cell hilights first but keeps all other formatting in place. When finished it shows a navigator bar to navigate through each change (handy for a small number of changes but no so handy for my 3000+ changes that it eventually found).

I’m not yet 100% convinced that it finds EVERY change, and if you’ve inserted a row and changed something in the same region it doesn’t pick it up, but those are small issues that are outweighed by the excellent results it produces.

In my 30+ sheet workbook it only took under 5 minutes to run and then I had two spreadsheets fully highlighted with all the differences, AND a separate map with all the changes hyperlinked back to the source spreadsheed.

I would suggest when using this, to save your two workbooks as different names before using it. You can also merge the values to the two spreadsheet but I wouldn’t recommend it as it only does values, not formula’s so I would be very careful with this feature.

So for 69 Euro I think it’s a bargain and I’m going to buy it to run it on my spreadsheets at the end of inputting data each month as a double check that everything I have changed is what I was expecting to change.

A Big Win for Productivity

by

I had a big productivity win this week. A few weeks ago my boss told my Junior colleague to scrap the work he had done over the month in our mega spreasdsheet and start again. I thought what a waste of time, but it’s not my problem (one of my stress reduction techniques). Well it became my problem because by starting the spreadsheet again he had just wiped out 6 hours of my work fixing all the mistakes that was made in the previous month!!! I was quite furious when I found out. But after I calmed down I took it as a challenge, to fix it all in less time than the first time. So I quickly figured out a way to wrangle the 800 links down to 200 easy to enter links – and it took me just over 1 hour!!! That was the first productivity win. After that, I decided that there is a better way than re-entering the data if you don’t trust it – compare it with last month’s spreadsheet. So I downloaded and checked out a few comparison tools and found one that was fantastic – see my review to come. It took less than 5 minutes to compare the two month’s spreadsheets and find out where the differences are – now it’s just a simple matter of looking only at the differences to see where the problem lies – the product costs around $100 and when I showed the Junior what it did, he said he would happily part with the $100 out of his own pocket as it would have saved him a week’s work in re-entering the data again. When I showed the boss he loved it and told me to buy it straight away. Win 2 for Productivity!!!

The moral to this story is – In Excel, if it seems to hard, it is too hard. There is usualy already a simpler way to do it, or an add-in that someone else has written to help with your sticky Excel situations.

Excel vs Word

by

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.

Some Gotcha’s

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.

My Favourite Excel Tips

by

Why would I want to post some Excel tips when there are so many people that do Excel tips well? There are many Excel tips that I read on a regular basis and ones that I go back to again and again as they do an excellent job. Have a look at my del.icio.us tag on Excel. Some of my favourite excel tipsters are

  • John Walkenbach’s Excel Page
  • OzGrid – the Authors of the O’Reilly Book Excel Hacks – and they are Aussie
  • Allen Wyatt author of the Excel Tips weekly email
  • The Excel Addict – a weekly Excel tips email from Canada
  • The old favorite Office Watch (aka Woodys Watch), now run by an Aussie

So rather than re-invent the wheel, which is not productive, I will be linking to other sites for exactly how to do stuff, my tips are about what I reckon you can do in Excel to increase your productivity.

Excel Productivity

by

I’m a bit of an Excel wiz. One of my interests is to help people learn how to get the most out of Excel. It is really topical for me at the moment as I am working on some monster spreadsheets at work that are very badly designed an therefore quite dangerous as it is very easy to get the wrong results. One of the spreadsheets I’m working on has over 800 individually entered links in it – and there is a way to simply modify it to enable users to have 1 link then reference all the other links from that one link – the productivitity increases will be amazing – I just need to convince everyone now…

So, I would like to do a bit of a series in my blog on my best productivity tips for Excel. The reason I got into programming is that I HATE typing (no hate is definitely not a strong enough word here), so I would come up with macro’s and learn tips to make it easier to get the data in to the spreadsheet. I’ve been using Excel since 1991 and Excel 2.1 (it came with runtime version of windows and ran from DOS). I conquered the Excel 4.0 macro language then they went and changed to VBA and I had to learn all over again because they use very different approaches.

I would love to know if any of my tips help you to learn something about Excel or make using Excel easier. Over the next few weeks, I am going to take one topic at a time and expand on it a bit. So here are my top Excel productivity tips.

  • Know when to use Excel and more importantly when NOT to use Word.  
  • One thing in one place, once – more of a database tip, but if you have to re-type anything there is a better way. If something seems too hard to do, there is an easier way. Lots of little tips on data entry, text formulas and my favorite – concatenation.
  • Set up your spreadsheet like a database – have column headings, no blank columns and definitely no blank rows and use Freeze Panes for row and column headings. You can then use such excellent gems as Filtering, Pivot Tables an database functions.
  • Learn your keyboard shortcuts – this is a big productivity increase – especially keyboard shortcuts like ctrl+” (copy from above) and ctrl+; (today’s date) – try them out, and I will explain a bit more later.
  • Set up autocorrect and autotext – being in construction it need to type quantities in metres squared and metres cubed frequently – so I set up an autotext to turn m2 into m².
  • There are at least 3 additional tools that need to be on your toolbar – I will explain what they are and what they do – it’s probably 2 posts, one on outlining and one on pictures and drawing objects.
  • Excel is not only for accountants – so don’t use the accountant’s currency format (the $ sign tool) which pushes the dollar signs to the left of the column (more of a fastidious proper way to do things tip, but I will throw in a few of these too).
  • I’ve invented a few excellent navigational tools using the Ctrl+Arrows key combinations that I will share with you.
  • Know how to use the page set up options and page break preview, and also know when to use manual vs automatic page breaks.
  • Using the Autofill handles effectively.
  • Don’t tag things with colours because you can’t use the colours for adding and counting etc without using a macro – use tags and Conditional Formatting and the formula’s CountIf and SumIf instead.
  • Use Hyperlinks to navigate around a big sheet.

That’s probably enough to start off with, but I will probably think of some more as I go along. After this, I will do a series on Word also.