The Detail Department

You are here: Home / Archives for Productivity

Moving away from Command and Control

by

Tasks are bad! Well, sometimes they may be useful, but most of the time they are abused and are used by management to try to command and control their staff. There are other ways other than using tasks.

This is the post for a presentation presented at LAST Conference. The idea for the presentation, and this post came from a client that asked me to ensure that all the tasks in their system, that had not been completed within 2 days of the due date by subordinates, were to be escalated to their manager, and then if they were not completed within 2 days by the manager, escalate to the GM. I said no, I would not be building a system that does that, there are other ways we can deal with things getting done.

Read more about Command and Control

A4/Letter – the most annoying option in any software

by

Why oh why is there ever any app that asks you whether you want to print this document or set up this document as A4 or Letter sized paper? Well, at least we should be thankful that there is even an option – how many apps do you use that are made in the USA and don’t even give you the option to change the paper size or change away from American spelling. (True story – an American colleague told me recently that she did not even know there was another paper size other than the “standard” and did not know it was called Letter). I can forgive regular users to not know there is a world outside the US of A, but I can’t forgive software developers.

Quick – do this test right now. Open Word to a brand new document, go to the Page Layout ribbon and choose Size – what is the default size that your blank documents are set to? If you don’t live in the USA and your documents are set to Letter, you have my permission to go and yell at your IT Department for not setting up your Word environment correctly – You probably still have American spelling in your documents and Inches in your Ruler and margins also!

Google Docs only very recently gave us the option of saving a default for our paper sizes and margins – yet the margins are STILL IN INCHES!!!! WHY?

Apart from having the option to change the settings very ocassionally for when you are preparing a document for a US audience, why does the setting even exist?

Google knows I’m in Australia, why can’t it determine the settings for me automatically as Metric and A4.

Microsoft Word knows my location from the Regional Settings on my PC – why aren’t my default Word Templates created as A4? (They should be if the location was set before installing Word, but I think it should be dynamically based on that setting – if you want to create specific Templates with other paper sizes, you can do that).

And worse of all, please, please, please, do NOT set the default as Letter and not even have an option for me to change it in the settings to anything else as the Default. Or on the other hand, please don’t make me change it every time I need to print. If I was in the US I would think – “why is this setting here every time I want to print a document – I would never ever change the paper size”, if I’m in anywhere else in the world I think “why oh why do these stupid American software developers make me have to choose my paper size every time I want to print a document!”. No one wins.

So, in summary:

  • Always have the option for A4/Letter paper sizes, American and other Spelling and Imperial and Metric measurements.
  • Set the default based on my location – you know my location!
  • Allow me to override the defaults if I need to (maybe I’m visiting the US of A).
  • Honour those defaults everywhere.
  • Have the ability to modify the defaults for once off cases (eg printing the same document to A4 then Letter for global audiences).

What are your thoughts? does this annoy you too?

Difficulties using Email in Salesforce

by

The subject of this post may seem a bit harsh, but hopefully by the end of this longish post, you will see where I’m coming from. My call right now, is that there is NO email solution that works with Salesforce that is without difficulties. I hope that I’m wrong. I really do. If you think I’m wrong please add to the discussion below.

When I’m talking about emails and Salesforce I am talking about the following scenarios. For the tl;dr version I’ve included my ratings for each of the scenarios:

  1. Receiving email from an existing client – PASS.
  2. Receiving email from a new client – FAIL.
  3. Receiving email from a new client about a specific case – Qualified PASS.
  4. Receiving email from an existing client about an existing case, replying to the email sent to them – PASS.
  5. Receiving email from an existing client about an existing case – FAIL.
  6. Emailing to a client – FAIL.
  7. Emailing to a client about a specific case – FAIL as above, with a MAJOR FAIL for dealing with attachments.
  8. Emailing to a third party about a specific case – FAIL.
I am focusing on Cases with this post, but there are a lot of things similar with Opportunities – the same issues you have about ensuring emails are recorded against the right Case happens with Opportunities and Leads also. How you set up these objects in Salesforce is dependant on your business processes.
The “ideal” email scenario with Salesforce is:
  • All contacts who ever email you are already contacts in Salesforce.
  • All email to those contacts are generated out of Salesforce.
  • Anyone who emails you about an existing case, will ALWAYS only ever reply to an email that has come from Salesforce.
  • Clients will reply to emails about specific cases with only content relevant to that case.
Of course that doesn’t happen in the real world – EVER.
And it definitely doesn’t happen if you are dealing with staff who work only out of their Outlook Inbox.

In this post, I am not talking about paid email syncing options that are available in the App Exchange (or this post will be very long). You can see that there are many many options, for huge dollar amounts. My thinking is that you pay enough for Salesforce, you should not have to pay more for basic functionality such as tracking emails for your client. There is one paid app that I will comment on, specifically in relation to Email to Cases. 

Receiving Email

Receiving email from an existing client

Emailing in to Salesforce to record that email against an existing contacts is pretty well handled. You can use Email to Salesforce, or the Salesforce for Outlook toolbar (which essentially does exactly the same thing). This matches the email address of the sender with an existing contact in Salesforce and then puts the content of the email in an Activity linked to the Contact record. I give this scenario a PASS.

Receiving email from a new client

I have no idea how you are meant to manage emails that you want to track for new contacts, apart from the obvious:

  • Forward the email to Salesforce (or click Add to Salesforce in Outlook)
  • Manually create a new Contact
  • Manually attach the email to the contact through the Unresolved Email Task or the Unresolved Emails screen.

This takes a number of steps and will probably not be done by most staff after about the first week. This scenario is a definite FAIL – so you probably need to ensure there is another way to create contacts first, rather than when receiving emails for the first time.

Interestingly I noted that forwarding an email and using the Salesforce for Outlook results in different behaviour for Unresolved Emails – forwarded emails are shown in a special Unresolved Emails page, whereas Emails added from Outlook only show up as Tasks. 

Receiving Email related to Cases

Setting up Email to Case

There is a great post over at Button Click Admin that goes over all the steps to Set Up Email-to-Case, and there is a good video from Salesforce showing an overview of using Email-to-Case.

Also, when setting up Email to Case, one must have app is Email2Case Attachment Reassign which takes the attachments from the email and puts them in the Attachments related list on the case.

Receiving email from a new client about a specific case

After Email to Case is set up, the logging of emails into Salesforce to create new Cases works quite well – If the client emails directly to your support email address (or you can forward the email to your support email address). For a client that does not yet exist as a Contact in Salesforce, the case will be created similar to the one shown in the Email to Case Setup Video. and you will have to do the following:

  • Manually create a new Contact
  • Manually edit the created Case to attach the Contact to the Case.
I’m going to give this scenario a Qualified PASS because if all else fails, and the Contact is not created in Salesforce, the Case can still be managed and the contact details from the client are still recorded in the description field on the Case. But it is best to create the Contact in Salesforce as they may be an ongoing client.
I have created a No Code No Cost solution to create new Contacts from Cases like this using Object Converter. 

Receiving email from an existing client about an existing case, replying to the email sent to them.

This is one of the perfect scenarios noted above. When emailing out of Salesforce to an existing client who has a Contact record created in Salesforce, the email gets a Thread ID added to it. When your client replies to that email, Email to Case sees the Thread ID and attaches the email to the Case and notifies the Case Owner that there is a new email. This scenario works effortlessly and is the way things should work in Salesforce – this gets a big PASS.

Receiving email from an existing client about an existing case

But as we know, people aren’t perfect and often your clients will email you about an issue that is related to an existing case. This scenario is more annoying and here are the steps:
  • Forward the email to Salesforce (or click Add to Salesforce in Outlook)
  • Open Salesforce and go to the record of the Contact that sent the email
  • Find the email in the Activities Related List
  • Open the Email Tasks record
  • Click Edit
  • In the Related To field enter the Case Number of the Case that this email is related to (Don’t know the Case Number? – no problem, simply open a new tab, search for the Case, copy the Case Number, come back to this tab, paste the Case Number in – easy!)
  • Save the Task
My response to this – AS IF! This scenario is a definite FAIL and there is no way that anyone except the most detailed person will do this. The end result is that Emails will either be only associated with the Contact, or not recorded in Salesforce at all. This then bypasses the whole reason for using Salesforce, which is to have “one thing in one place, once”.

Emailing Out of Salesforce

Emailing to a client

If the client is already a Contact record in Salesforce you could just BCC your email to Salesforce address as you send the email out from your email client. But that’s annoying to have to remember that each time. You can achieve a similar thing by clicking the “Send and Add” button in Salesforce for Outlook – a little easier.

The idea, however is to get people out of working from their Inboxes and work from within Salesforce as their primary tool, so you want them to generate the emails from the Contact record in Salesforce. There are a few advantages and disadvantages  with this way.

Advantages:

  • Multiple email addresses can be stored against each client record – just choose the right one to send to.
  • It looks just like regular email and has all the fields like Additional To and BCC.
  • You can do rich text formatting in the body of the email.
  • You can choose predefined Email Templates that have Merge fields in them to quickly create emails.
  • There is a spell checker built in.
  • You can attach files.
Looks and feels and smells exactly like email – right? Well just start using it for a week or so and you start to find the disadvantages are numerous:
  • Which is the right email address to send to?
  • No auto complete in the other email address fields.
  • No shortcuts or auto formatting that you are used (eg ctrl+shift+8 – Gmail or * space – Outlook to create a bulleted list; ctrl+k to insert a hyperlink).
  • No inline images in your email body (even Gmail can do this now).
  • Only one Email signature – you can’t swap email signatures as you can in Outlook.
I could go on. For hard-core email users who feel that they can’t get their job done effectively by using Salesforce this is an issue – they will just stop using Salesforce, and the emails won’t be tracked. And then there are Attachments, which is a whole other story – see below… So sending emails is a FAIL from me.

Emailing Out of Cases

For emailing out of Cases, or Opportunities or any other client related object, I’ve concentrated on Attachments. Specifically for Cases, there is an app called Email to Case Premium – this is a paid app so I will deal with the features and issues of this app in a future post.

Attachments

Attachments are where the real hell begins. So, you’ve spent all this money getting Salesforce up and running for your organisation, and you want to ensure you get the most out of it, therefore you insist that all documents related to Cases (or Opportunities, or Accounts or any other object) are attached to the Salesforce record that they relate to. (Why you would try to manually maintain matching folders on your share drive or in Box.net or similar is beyond me). Now, you want to email one of those documents to the Client or someone else – these are the steps to attach one document to an email from within Salesforce:
  • Before you email, go to the Case record, decide on the attachments you want to download.
  • Right click on the file name and download it to your local computer, or network drive (and use up your bandwidth for a second time, because you have already uploaded it once).
  • Now create the email and fill in all the details.
  • Click on Attach a file.
  • Click Choose File.
  • Browse to the File, remembering where you just saved it.
  • Click Attach to the email and wait for it to upload (using your bandwidth for the third time).
  • Click Done.
  • Now you can Send the email – Finally!
What an absolute joke! And querying this with Salesforce officially (this was a deal-breaker for a client of mine recently), they say that this is the way it is designed and the only way they can suggest you attach documents to emails, that are already attached to the record.
Yes, you could pay lots of $$ for Email to Case Premium, Loop or Conga – a few apps that will help attaching documents to emails, but that is not the point. How does Salesforce even consider this to be acceptable business practice? You know what’s going to happen, people will start saving documents onto the dreaded file share either instead of, or even worse, as well as the Salesforce record, then you have a nightmare of consistency – which one is the correct and latest version of the document. Wow! You will end up needing SharePoint just to manage the versions of the documents – and that is way overkill.
Dealing with Attachments is a MAJOR FAIL!

Emailing to Third Parties

This scenario really depends on your business, but a number of businesses would need to take documents from Clients, especially relating to a specific Opportunity or Case, and then forward them to another third party for processing or information.
Apart from dealing with the whole attachment issue there are a number of other issues:
  • You will need a place to record the Third Parties on the Case or Opportunity – your best bet is a custom child Object, because the standard functionality of Contact Roles is a bit limiting.
  • When you want to email to one third party you will need to copy the email address, create the email, then paste the email address in to the Additional To field.
  • What if you want to email two or more third parties – good luck with working out how to do that – possibly open a separate copy of the Case in a new tab, then do the email and switch back and forth between the two tabs and copy and paste the email.
  • If you want to use Text or HTML Email Templates there is a limitation that emails must be sent to a Contact or a User – so you may have to have your name in the To field, as well as the third party – this may look strange to them.

So – that is a lot of stuffing around just to send an email – so, for me, emails to third parties are a FAIL.

Summary

This is a long post and I have tried to be thorough in looking at ALL the options. There are some basic functions of emailing that get a PASS, but as soon as you take it even a little bit past the basics (I don’t think adding an attachment is anything but basic), we descend into the realm of FAIL.

I have yet to see any reasonable, even paid, solution for simply dealing with the attachment issue (without the other features of Loop or Conga). I’m sure it could be coded as a visualforce page, but I really don’t understand why someone hasn’t done it as a simple free app – so maybe I’m missing something that will mean it is difficult to build.

So, if you have any suggestions, other options or favourite apps to deal with emails, please add some detail in the comments below. In a future post, I will talk about the features and issues with one particular email App, Email to Case Premium.

Otherwise, right now, I don’t have a solution.

Getting into my Confluence Editing Groove

by

I love Confluence. If I could use Textile Markup to do any editing of documents at any time, I would. If Textile Markup was in Google Docs, I would love it (although, now that the keyboard shortcuts are more similar to Word, it’s a little easier).

Of course Confluence is more than about the editing, but in this post I’m going to be concentrating on the editing features of Confluence – especially Wiki Markup. I do not use anything other than Wiki Markup when editing in Confluence*. Even though the Rich Text Editor has improved in later releases, it is still much easier to do “* type the text” to create a bulleted list than to take your hands away from the keyboard and find the icon to apply the bullet style.

(* I do use Rich Text editor when working with large tables – that is my only concession).

I am currently writing a very large help system in Confluence for a web based App that I work with. There is so much to do, that I need to be quick and have systems in place to do things without repetition.

The one thing that is difficult about editing in Confluence as much as I do, is that it is slow – this is the nature of web based applications It is slow to get into edit mode, and even slower to save the page and display the finished content again. There is Word connectors and WebDav, but they can be a bit of a pain and I just want to quickly edit text in a simple text editor. I can guarantee it that every time you think that a Confluence page is finished – there is always one tiny edit that needs to be done, one comma missing or a spelling error – it is this continuous editing that takes so much time.

What I also want most of all is syntax highlighting in Wiki Markup mode – so I can concentrate on the text, and ignore the macro’s and links etc, or quickly find that h2. line with the bit of text I need to edit.

The Confluence Plugin “Confluence In Place Editor” (CIPE) is great as it allows you to edit just the section of the page that is within a heading, and the main advantage of it, is that a text popup window pops up instantly – no more waiting to launch into edit mode.

So I have come up with my workflow for editing Confluence pages – using a number of tools, and I think, even though it is a bit clunky it is faster and cool, because I now have syntax highlighting.

My Apps

These are the apps that I have open when writing my Help System
  • My app in the browser depending on which one I’m writing about (generally Firefox).
  • Confluence in Firefox (with Text Area Cache plugin enabled – this is a godsend – just do not use any Web based editing eg Confluence, WordPress etc without it).
  • Notepad ++ (yes, I’m a Windows user, you could do similar with TextMate for Mac).
  • Snagit (yes, Snagit is now available for Mac also).
  • Dropbox (definitely the best thing since sliced bread).
  • The Confluence In Place Editor Plugin

Notepad++

I Created a User Defined Language for Confluence – based on this site and refining it myself.  Text I have formatted in different colours are:

  • ! for images
  • * for bold and bullets
  • # for numbered lists
  • Numbers
  • {text between braces} for macros
  • h1. h2. h3. etc
  • [Links to Pages] in blue

I’m sure there are more things I could colour, but that is  a good start and makes the Wiki Markup much more readable.

When editing your document, save the file in dropbox so it’s updated constantly and backed up (just as it would be if you were editing in Confluence).

Snagit

All screen shots of my App are done in Snagit. I don’t upload them to Confluence until the very end as there is always one last minute change you want to do. I put numbering on the screen shot to point my help text at the correct location on the screen – I just use the default Snagit numbers.
It is very helpful to have the text file and Snagit side by side so you ensure that the numbering in the text is the same as the screen shot. (Win Key + Left and Right arrows FTW!)
I also upload my .snag files and my .png files to Confluence, so I don’t have to store them in a separate location – Confluence is my master repository for images. When I need to edit my screen shots for a new release, I just download the .snag file, modify it, create the .png file again, and upload both files back to Confluence.
I use the idea of the _includes page as an image library as shown in this post.

My Workflow

Here is my workflow for creating and editing my Confluence pages:

  • Create the screen shots and work out what needs to be written about.
  • Add any numbering or other enhancements to the screen shot in Snagit.
  • Create the text in Notepad++, viewing the Snagit screenshot side by side with the text to get the text right.
  • Add the Screen shot link into the document based on the name you have called it in Snagit and the name of your Image Library page.
  • When the text looks right, paste it into Confluence and have a look at the layout and structure of the document. (Note, there will be no screen shots visible yet – this makes it a bit cleaner to focus on just the words).
  • Display the Confluence page and the Notepad++ document side by side on the screen, so as you see things in the Confluence page that need fixing, you can quickly edit the Notepad++ document. (You can do this with two browser windows open side by side also and use the normal Confluence editor).
  • Check the spelling in Confluence as it uses your built-in browser spell checker with the words underlinked (Notepad++ spelling checker is not that great).
  • Paste the edited text back in to Confluence when you are done. Using the CIPE plugin helps with that as it makes it much quicker to load than going into edit mode.
  • Once all the text is correct, drag and drop your image files to the Image Library page.
  • Refresh and check the completed page in Confluence to make sure everything looks great.

So, that’s my workflow. What is yours? Do you have any great ideas that will help streamline my processes, or will my ideas help streamline your processes? Let me know in the comments.

5 Things Vista is Missing

by

Having just got a new Vista PC at both Work and Home I’m a bit dissapointed about the few key tools that Vista seems to be missing. If it had these 5 tools, I think it would be a very well rounded and functional OS. Don’t get me wrong, I’m loving vista, but it’s just lacking in the following areas:

1. Path Copy
Yes, if you hold down shift then right click on the file you get a menu option called “Copy as Path”, but it has quotation marks around it. I love Ninotech Path Copy becuase it has many options and I often need UNC paths for linking files from sharepoint. Unfortunately I can’t work out how to install an .inf file so can’t install Ninotech at the moment.

UPDATE: not sure what I was doing but I was able to install Ninotech Path Copy. But I also found this one which is much simpler to use – I love Ninotech becuase of it’s flexibility but this one is better for it’s simplicity –  ClipboardPath

2. Taskbar Shuffle
Why oh Why can’t you move taskbar items in Vista – surely this is a no-brainer! Fortunately we have Taskbar Shuffle which is a godsend for a tidy and ordered desktop.

3. Print Screen
The useless old print the whole screen or the current window options went out with the dark ages – I do a lot of help files and emails and always do screen shots of the bits they need to know about. Having used Snagit previously, now I just use Gadwin Print Screen because it’s free and works really well – My default setting is rectangular window to clipboard as a jpg and no confirmations – quick and easy.

UPDATE: Oops I forgot about the new Snipping tool in Vista… however the snipping tool only installs with all the other tablet tools, and since I don’t have a tablet and wanted a very clean install, I did not originally install the tablet tools… I still prefer Gadwin over Snipping any day.

4. File Renamer and 5. Copy Explorer Columns
I know you can re-name multiple files at once with 1, 2, 3 etc on the end, but I want MORE. I know you can copy Paths in explorer but again I want MORE. So as much as I tried hard to not install any add-ons and explorer replacements, and as GREAT as the new explorer is in windows it just doesn’t cut it and I’ve had to install xplorer2 again. There are probably many other products that do the same but I love the tabbed folder views that remember what folder I was looking at yesterday and its excellent file re-naming features are great also. The Copy Columns feature is great, as I often need an excel spreadsheet with the file name in one column and the folder in the next, xplorer2’s copy column feature does that for me.

So with these little gems of software added to my new Vista PC’s I feel that I’ve got the best of both worlds – a UI that is excellent and my tools to help me get the job done.

One final point that will help with productivity – if you have Vista and not Office 2007, Add your Links folder to the My Places Bar in word and Excel. The Links folder is great and replaces the need for “My Network Places” and adding it to My Places means that you can access your Links shortcuts from anywhere.

Creating a Community Web Presence for Free

by

I’ve been elected as secretary of a community group that I’m involved in (the Feldenkrais Interest Group). The organisation has been around for 20+ years but no one would know it existed becuase of a very anologue way of doing things (ie posting out newletters, putting flyers up at the local community centre and relying on word of mouth). So I said to the group “We need an on-line presence”. They said, “We can’t do that, we have no money to spend on things like that”. I said, “Well what If I could do it all, and do it for free – website, email, calendar, everything – all Free”. So I was elected on the platform of bringing the group into the 21st Century.

I want the following items for my group’s web presence

  • A home page
  • Email
  • A blog – they don’t need to know it’s a blog – to them it’s just a website that can be easily updated with the latest news, minutes, notices etc
  • A central calendar of all group activities
  • A place to store group photos – not for general public, only for members
  • A way for people to pre-register for events via an online form
  • A place to put flyers that people can download and print out
  • A database of members
  • Group Financial data
  • Group To Do Lists
  • Document Creation
  • A Document Storage Library

I’m a big google user, but I wanted to use Yahoo! as I thought it would be a bit more user friendly for the non PC users of the group. So I did some quick reasearch on the following Yahoo! tools

  • Yahoo! Mail
  • Yahoo! Calendar
  • Yahoo! Geocities
  • Yahoo! 360
  • Yahoo! Groups
  • Yahoo! Photos

What I was looking for was one place to do the lot, but Yahoo! is a bit fragmented still. 360 seems great but more in the mySpace and personal or family blogging arena. Geocities is a hangover from days gone by and is mainly about static pages. Groups now has 360 combined but seems a bit limiting, and I did not like the user interface.

Then TechCrunch mentioned JotSpot and I played around with setting that up. I absolutely love it and would use it as it fits ALL my requirements and more, but it has the following problems

  • As TechChrunch says, why call it a Wiki? It can still have the same functionality without the word Wiki plastered all over it – I think the term Wiki and Blog can turn a few non-techo’s off
  • The layout is a bit all over the place
  • The JotSpot logo is always there – you can’t remove it on the free version
  • The worst thing is, you can only have 10 pages on the free version, and to fulfil my list of requirements, I needed at least 12 or so.

I think if Jotspot could re-think their marketing a bit and allow 30 or so pages and at least 1 logo on the front page instead of the jotspot logo for their free sites, it would definitely be a goer for small groups and would beat Yahoo! Groups hands down. Jotspot’s killer feature is the ability to add the plug ins that you want – ie a Project Management plug in or a Contact Management or a To Do list plug in, so you can customise it to just the features you want.

So, after all my research, here is my solution. It’s a bit haphazard but it’s scalable and most of the parts are changeable if we don’t like them after a while.

  • Home page and Blog – WordPress.com – Easy to use, looks good, can have the logo on the front page (it’s not there yet) – check it out at http://fignsw.wordpress.com
  • Email – Gmail of course – can’t beat it for ease of use and storage size fignsw@gmail.com
  • Calendar – Google Calendar – see link from blog. Still got a few issues but great for publishing the calendar live
  • Photo’s – Probably Flickr but it’s not high on my priority list at the moment, so something else may come up.
  • Online Forms – I placed a few candidates on my del.icio.us account Wufoo looks promising, as does JotForm, so I will try them out and report back.
  • Static Pages – Google Pages – I can build a flyer online and have the same flyer for printing out and on-line
  • Members Database – Probably Google Spreadsheets for now – but I will try Gmail Contacts first
  • Financials – Defintely Google Spreadsheets – Love it – it just needs a few more tweaks like being able to freeze the top rows and left columns.
  • To Do Lists – for it’s sheer simplicity, but with heaps of features, I like Voo2do – Backpack is great but a little bit of overkill for what we need
  • Document Creation – Writely when it’s up and running.
  • A Document Storage Library – At the moment it will probably be Gmail, but I’ve also looked at Openonmy and of course when OmniDrive comes that will be great too.

What I will do is put a set of links for the commitee on the blog to the spreadsheets, ToDo lists etc, and they will all have the passwords that the committe knows. For members only stuff like photos I can email the passwords for that to the members, and everything else, like calendar and notices can be public domain.

If anyone else has suggestions on ways to create an on-line presence for community groups for free, I would love to hear your feedback.

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!!

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 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.