Microsoft Excel 2010 Tip, How to Dedupe Data Easily

Excel 2010 contains a useful feature – data deduplication. In Excel you can select a column and Excel will automatically remove duplicate rows from your spreadsheet based on the value of that column.

Just create your column of data then go to the “Data” tab and click “Remove Duplicates”

Excel data deduping

Now select the column you want to deduplicate on. If the same value appears more than once in that column, then the second, third etc.. time it appears the entire row containing that duplicate value will be deleted.

Quite handy for turning a long list into something you know to contain only unique values. Of course, this doesn’t do sophisticated deduping based on similar names (Dave vs David) but it is a handy Excel feature.

Microsoft Excel tip – How to change a row of data to a column

Sometimes in Excel you want to convert horizontal data (a row) into vertical data (a column) or do the reverse and convert vertical data into horizontal data. The good news is it’s very easy.

  • select the data you want to convert from horizontal to vertical
  • right click and select Copy
  • now go to a new cell where you want your converted data to appear
  • right click and choose the special paste option “transpose”

  • Your data should be pasted into the spreadsheet the other way round.


Qlockwork Time Tracking for Outlook Tips – How We Tell When You are Away

Qlockwork is a tool for automatically tracking time spent at your PC in Microsoft Outlook. We are often asked if Qlockwork just always tracks your open applications or if it can tell which application you are working on and whether you are actively working on it or away from your PC having a coffee!

The good news is that Qlockwork can tell and it only tracks the application you are actively working on. It does this by talking directly to Windows using something called the WinAPI (Windows Application Programming Interface). Through this Qlockwork can talk to your mouse and your keyboard to find out if you are still there and which applications you are using, so it just tracks the time you are active.

If you’d like to know more, just give Qlockwork a try, it’s very quick and easy to set up.

Qlockwork Time Tracking Tips, How To Find Lost Files

Sometimes you just forget the folder where you filed a Microsoft Word document or Excel spreadsheet. That’s not too bad unless you’ve also forgotten the name of the file! Fortunately Qlockwork can help you find out the name of the file. But let’s start with the simple situation where you remember the name.

You Know the Filename

If you know the name of a missing file you can find it with Windows search. In Windows 7 go to the search box in the start menu and just enter the name of your lost file:

How to search for files on Windows 7

Windows will display all the matching files, emails etc.. on your PC.

In Windows 8 you can search by just typing straight onto the Start screen.  A search box will appear on the right. By default Windows 8 searches apps. To search for files instead click on “Files” in the right hand menu.

You Don’t Know the Filename

If you have forgotten the file name then things get a little trickier. Fortunately Qlockwork can help.

  • Go to Qlockwork and choose “View Activities in a List”

List all activities in Outlook and sort by type

  • Now click on the top of the “Type” column.
  • This will sort your activities by application in alphabetical order.
  • If you are looking for a Word document, scroll down to type = “Microsoft Word”.
  • Qlockwork will list every Word document you have worked on, in date order.

List of all the Microsoft Word Documents you have worked on

  • You can now look down the list until you find the document you are looking for. If you know roughly when you worked on the file that will help narrow the search because the files are listed in order of when you worked on them.
  • You can use the same technique to find missing Excel spreadsheets. Just scroll down to Type = Microsoft Excel. You can use this technique to find any file, as long as you know what application you would have used it in.

Once you have found the name of your missing file, you can use Windows search (see above) to find where you saved it.

Outlook 2010 tips, How To Delay Sending Emails In Outlook

In our last post we showed you how to automatically reply to emails in Outlook using rules. In this post we’ll look at how to delay sending certain emails. We can then combine these 2 techniques into a way to send automated replies with a delay.

The downside is we can only introduce a delay of 120 minutes using standard Outlook rules. To delay longer than that we need to do something a little more involved, we’ll cover that in a future post.

We can introduce a simply delay in sending emails to all or just specific emails using standard Outlook rules.

  •  Start to create an email rule as normal by going to Home->Rules->Manage Rules and Alerts

manage rules and alerts

  • Click on “New Rule”
  • Under “Start from a blank rule” select “Apply rule to messages I send” and hit Next

apply rule to emails I send

  • Now select a criteria, for example emails to a specific person or with a specific subject and hit Next
  • For the action check “Defer Delivery by a number of minutes”
  • Then click on the “a number of” link in the Step 2 section
  • In the popup, specify how many minutes you want to defer delivery (up to 120) and press OK

select number of minuites to defer email

  • Click Next to continue and then enter any exceptions and click Next again
  • Finally, name your rule and hit Finish

You now have a client-side rule that will defer the delivery of any emails you send according to a rule (recipient, subject etc..)

This is all fairly basic, though useful.

You may wish to combine this technique and the one described in the last post ( how to automatically reply to emails in Outlook using rules) to automatically send follow up emails after a delay. For example, if you receive an email automatically when someone downloads a whitepaper from your site, you may wish to send an automated reply after 60 minutes asking them how they are getting on. This would give them time to look at the whitepaper before you contact them.

Alternatively you could just use this technique to put a delay on sending any emails to your boss to ensure you have chance for second thoughts ;-)  Let’s call it a cooling off period ;-)


Outlook 2010 tips, How to Automatically Reply to Emails using a Rule

Setting up a client-side rule to automatically reply to received emails is very easy in Outlook.

  • First of all, we need to create a reply template. Open a new email message as normal and fill it out as you want your reply email to look.

Email reply template

  • Now save this as a template by choosing File->Save As on the email and selecting to Save as type: “Outlook Template”

saving an email as a template

  • This will save the template into the default Outlook template folder. Just remember the name of the template file you have created. You can now close your email and you don’t need to save it as a draft, you already have your template.
  • Now go back to your main Outlook window. Start to create an email rule as normal by going to Home->Rules->Manage Rules and Alerts

manage rules and alerts

  • Click on “New Rule”
  • Under “Start from a blank Rule” select “Apply rule to messages I receive” and hit Next

Outlook start from a blank rule

  • Choose your criteria (emails from a specific person or with a specific subject, for example) and hit Next
  • For the action select “Reply using a specific template”

Choose reply action for Outlook rule

  • Now in the Step 2 section click on “a specific template”

Select a template for an email reply

  •  Choose “User Templates in System” from the Look in dropdown

choose user template

  • Now choose the email reply template you created right at the start and click Open

select your template

  • You should now return to the rules wizard and see your template has been populated in the Step 2 section. Hit Next
  • Add any exceptions you want and hit Next
  • Finally name the rule and Hit Finish

name your rule

You have now created a rule that will automatically reply to specific received emails straight away with the text you want.

In our next post we’ll be looking at ways to delay your email reply. After that we’ll look at ways to personalise the reply.





Windows 8 tips, How to Turn Off your PC in Windows 8

How do you turn off Windows 8? The easiest thing to do is press CTRL+ALT+DEL and Windows will show you a screen where you can choose to “Lock”, “Switch User” or “Sign out”.

In the bottom right of this screen is an on/off switch icon. Press that to switch off. I may find other ways, but this works.

Window 8 Tips, How to Customize the Windows 8 Start Screen

In our last-but-one post we talked about how to cheaply and rapidly install Microsoft Windows 8 (how to upgrade to Windows 8). In the next few posts I’m going to talk about how to make Windows 8 more usable in a work environment.

The first thing you’ll notice about Windows 8 is the start screen. This is what you have instead of the nice discrete Start menu from earlier Windows versions.

Microsoft Windows 8 Start Screen

I don’t want news stories popping up on my work PC. Life is already too distracting! So, I’m going to personalize and clean up my Windows 8 Start screen.

  • The first thing I want to do is remove or “unpin” some tiles.
  • I want to get rid of all the ones that flash unnecessary info at me.
  • I position my mouse over the tile I want to remove and right click. A menu appears at the base of the screen. I click on “Unpin from Start”.Customizing the Windows 8 Start Screen
  • I unpin all the unnecessary tiles to leave me with a cleaner start screen.
  • This doesn’t uninstall the applications, but it does take them off my start screen.

Now I have a smaller number of tiles on my Start screen but I want to arrange them better. I just click on a tile and drag it where I want. If you hit “Enter” on the Start Screen then Windows 8 will execute the top left hand tile. I want this to be the desktop because I’ll go there most frequently, so I drag the desktop tile to the top left position.

Nearly empty Windows 8 start screen

Now I want to add some of my commonly used applications to the Start screen so I can launch them quickly like I used to do with the start menu.

  • In the Start Screen I right click with my mouse.
  • A menu appears across the bottom of the screen. In the far right corner it says “All apps”.

Find all your applications from Windows 8 Start screen

  • Clicking on “All apps” shows me all my installed applications.
  • I mouse over the application I want to add to my Start screen and right click.
  • A menu appears at the base of the screen. I click “Pin to Start”

Add new applications to the Windows 8 Start screen

Now I have a clean Start screen and I can easily move to the desktop by hitting “Enter” or by clicking on any of my commonly used application tiles, which launch the application on my desktop. I can get back to the start screen by hitting the Windows key or mousing over the bottom left of the screen and clicking on the start screen thumbnail that appears (see How to get back to the Start screen in Windows 8). Not bad for now.

My Custom Windows 8 Start Screen

At Qlockwork we’re Outlook and Windows developers with a keen interest in office productivity and usability. To find out more about how you use your time on your PC, try the Qlockwork free trial. Qlockwork automatically tracks how you spend your PC time.


Windows 8 Tips, How to Get Back to the Start Screen

After spending several fruitless minutes trying to get back from an application to the start screen in Windows 8. Here are 2 things that seem to work:

  1. In any view, hover your mouse over the very bottom left of screen (where the start menu used to be). A thumbnail of the start screen will appear. Click on it and you return to the start screen.
  2. Press the Windows key on your keyboard (The key with the wobbly Windows icon). That also seems to return you to the Start screen.

If you are interested in tracking how much time you spend in Windows applications, try our Qlockwork time tracking add-in for Outlook.


The Cheapest and Quickest Way to Buy and Install Windows 8

Microsoft Windows 8 is now out! Let’s buy it and  install it as cheaply as possible on an old PC.

Microsoft Windows 8 Start Screen

According to Microsoft we can upgrade an old XP, Vista or Window 7 PC to Windows 8 Pro for $39.99 (or $69.99 if we want the CDs) plus sales tax.

I’m going to skip the CDs and go for the cheapest and fastest option of $39.99 and download the software directly to my PC.

  • First thing to check is that my old PC is powerful enough to run Windows 8. According to Microsoft, the Windows 8 requirements are:
    • Processor: 1 gigahertz (GHz) or faster with support for PAE, NX, and SSE2
    • RAM: 1 gigabyte (GB) (32-bit) or 2 GB  (64-bit)
    • Hard disk space:  16 GB (32-bit) or 20 GB (64-bit)
    • Graphics card: Microsoft DirectX 9 graphics device with WDDM driver
  • These aren’t very tough requirements and our 3 year old XP PC meets them easily.
  • You can check the processor and RAM on your PC by going to Start->Control Panel->System

Windows System

  • You can check the graphics card by going to the PC’s Start menu and in the “Run” box entering “dxdiag” (without the quotes) then hitting OK. The System tab will tell you your DirectX Version.

dxdiag screen on Windows

  • If in doubt, you can always just try running the upgrader and see if it complains.
  • Before we start, if there is anything you care about or want to keep on your PC (files, photos etc..) BACK THEM UP NOW on a separate device.
  • My next step is to go to the Microsoft site Windows 8 buy page (Microsoft Windows 8 upgrade buy page).
  • I do this using IE on the PC I want to upgrade.
  • On the Microsoft purchase page, I click on the green button to Download Pro for $39.99

Download WIndows 8

  • I’m asked if I want to save or run the upgrader. I choose Run
  • I’m then asked if Microsoft has permission to do stuff on my PC, I say Yes.
  • Now the dark blue Windows 8 Upgrade Assistant runs for ages “Checking apps and devices”….
  • Eventually it gives me a list of all the applications that I have installed that are not compatible with Windows 8. I decide can live without these applications so I hit Next.
  • The next thing I am asked is what files I want to keep on my PC. I don’t care about keeping any files on this PC so I select “Nothing” and hit Next.
  • On the next screen I’m prompted to buy Windows 8 in my local currency (that’s £24.99 because I’m in the UK, plus Sales Tax or “VAT” in the UK). I click the Order button to buy.
  • I proceed through the checkout and choose the 2GB download without the DVD.
  • Once I’ve finished and bought the upgrade, Microsoft sends me an email with my product key and the upgrader starts downloading the new OS onto the PC I am upgrading. That’s going to take a while, so time for a coffee!
  • Once the download is complete, the uploader asks me if I want to install the OS now or later. I choose now.
  • I’m asked if I want to proceed with my settings (install now, save no files). I choose yes and the install continues. I’m warned this may take some time. Time to do something else for a while and come back.
  • After a couple of reboots I’m asked to choose a colour scheme and a name for the PC, I do so and click Next.
  • On the next screen I choose the “Express Settings” and click Next.
  • On the next screen I enter my email address and click Next.
  • On the next screen I enter a password for my Microsoft account (I already have a Microsoft account so I use that password) and hit Next.
  • On the next screen I enter my phone number for password recovery and hit Next.
  • After a few more minutes of churning away with no further questions it’s done! Windows 8 is installed successfully and quite painlessly.

Of course, Windows 8 doesn’t really feel like Windows and I hate the lack of a start menu. Next post will be about how to navigate around Windows 8 with no start menu to use…

At Qlockwork we are Outlook and Windows developers with a keen interest in productivity. If you want to find out how you spend you time at your PC why not give a free trial to Qlockwork, our time tracking software for Office and Outlook. Try it free for 10 days from our downloads page.