Delete duplicate rows from a list in Excel

Delete duplicate rows from a list in Excel

The following article describes how you can use an advanced filter in Excel to show only unique items in a list, allowing you to remove or delete duplicate rows.

Before starting, please ensure you have a recent backup of the file you are going to remove the duplicate rows from.

  1. In the list you want to filter, select all of the rows including the column headers by clicking the top left cell of the range, and then dragging to the bottom right cell.
  2. Go to the Data menu > Filter > Advanced Filter.
  3. Select Filter the list, in place.
  4. Select the Unique records only check box and click OK.

What you are now left with is the filtered list that has no duplicate cells/rows. You can now either copy and paste the filtered list into a new worksheet/workbook or you can:

  1. Copy the filtered list to the clipboard.
  2. Disable the filter – Data menu > Filter > Show All.
  3. Press Delete on your keyboard to remove the original list.
  4. Paste the new list in from the clipboard.
Posted in Applications & Software, Microsoft Office | 1 Comment

Remove blank/empty rows in Excel

Remove blank/empty rows in Excel

Although there are several ways to remove blank rows or blank cells in Excel, this is probably the easiest. NB – If you have multiple columns containing blank rows or cells then you will need to repeat this process for each column.

Before starting, make sure you have a backup of your spreadsheet.

  1. Select the entire column by clicking on the column heading above the first row.
  2. Press the F5 key on your keyboard (or select Edit, Goto).
  3. Click the Special button.
  4. Select the Blanks option and click OK.

At this point, you have now selected all of the blank cells in this column. All you need to do now is right click and delete or choose Edit > Delete from the menubar. When asked what you want to delete, select the relevant option (probably shift cells up in most cases) and click OK.

Repeat as necessary for all columns that contain blank cells.

Posted in Applications & Software, Microsoft Office | Leave a comment

How to Defragment Windows 7 | Vista | XP

How to Defragment Windows 7 | Vista | XP

Over time, the files on your hard disk drive in Windows become fragmented (or split up). The most noticeable effects of this are that the computer takes longer to startup and programmes load slower, causing Windows to run slow. To combat this, it is important to regularly defragment the files on your computer’s hard disk drive.

To defrag, is the process of piecing together all of the file fragments into contiguous blocks of data. Your Windows based computer contains a built in utility to perform this defragging.

Instructions are given below for the three most current and popular versions of Windows – XP, Vista and Windows 7.

To begin, open the run dialog box. i.e. Click on Start > Run or alternatively, press the Windows Key and R on the keyboard together. Then type into the run box (without the quotes),

Windows XP – “dfrg.msc”
Windows Vista – “dfrgui”
Windows 7 – “dfrgui”

Then click the OK button and wait for the application to load up. Once loaded, select your operating system drive (usually your C: drive) and select the option to defragment.

This process may take several hours if it has not been run for a while (or ever!), but if you perform this weekly, it should only take a matter of minutes to complete. NB – you can still use your computer whilst this process is running, however, your computer will be much slower whilst it’s defragmenting.

Alternatively, you can download and install a great free application called Defraggler which provides a nice graphical representation of the state of your hard disk drive and tends to be a bit more efficient at defragmenting than the standard Windows utilities.

Posted in Windows, Windows 7, Windows Vista, Windows XP | Leave a comment

Repair Corrupt PST file using Scanpst – Microsoft Outlook

Repair Corrupt PST file using Scanpst – Microsoft Outlook

Microsoft Outlook is a personal information manager that is used primarily as an email client and contact manager and is part of the Microsoft Office suite of applications. Outlook stores your email, appointments, tasks and contacts in a PST file (you may have multiple PST files). Sometimes these files can become corrupted.

If you experience crashes whilst opening Outlook or error messages asking you to run the inbox repair tool, then the first step in resolving this problem is often to run the Scanpst tool. The location of this tool varies depending upon the version of Microsoft Office you are using as well as the version of Windows. If you are using a 64 bit version of Windows, then you will need to check the Program Files(x86) folder as well.

For Outlook 2003, scanpst.exe can usually be found in:

  • C:\Program Files\Common Files\System\Mapi\1033\
  • C:\Program Files\Common Files\System\MSMAPI\1033

For Outlook 2007

  • C:\Program Files\Microsoft Office\Office12

For Outlook 2010

  • C:\Program Files\Microsoft Office\Office14

Once you have determined the version of Microsoft Office that you have installed on your computer, browse to the respective location above by opening My Computer or Computer from the desktop or start menu. Launch the scanpst tool by double clicking it, then click the Browse button and navigate to the location of your PST file. Click the Open button and wait for it to finish.

If your PST file isn’t too big then it should only take a few moments to fix the PST file – hopefully without any loss of data. NB you may be informed of errors with your PST file. These will need to be fixed if you want to continue using Outlook with this PST file.

It’s probably a good practice to run this inbox repair tool on all of your PST files every time Outlook crashes. This will minimise the chance of data loss.

Posted in Applications & Software, Microsoft Office, Windows | Leave a comment

How to Export Firefox Bookmarks

How to Export Firefox Bookmarks

If you want to backup your bookmarks in Firefox to an HTML file which can later be imported onto another computer for example, then simply:

  1. Open Firefox
  2. Open the Library (used to be the Bookmark Manager) by selecting Bookmarks > Organize Bookmarks
  3. Click on Import and Backup (near the top of the Window) > Export HTML
  4. Save the bookmark.html file to a location of your choice
Posted in Applications & Software, Firefox | 1 Comment

Hide User from Windows 7, Vista, XP Welcome Screen

Hide a user account from the Windows Welcome Screen

When a user account is created within Windows, it will automatically be visible on the Welcome screen where you logon. Perhaps you don’t want that account visible there. Maybe it’s the Administrator account and you don’t want to be “tempted” to use it? Or perhaps you don’t want another computer user to see it for security or personal reasons. Well there’s an easy registry tweak that will allow you to hide or show any of the user accounts (standard or administrator) from the Welcome screen.

Before we begin, I must warn you to be careful when editing the registry and that you should create a backup prior to making any changes. See my article on Windows registry and regedit basics for further details.

So to begin.

  1. open up regedit – Start > Run > regedit
  2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon\SpecialAccounts\UserList
  3. Create a new 32-bit DWORD value in the UserList subkey
  4. Name the value exactly the same as the user account you want to hide
  5. Double click the value and set it to 0
  6. Close regedit and log off

To show the user account

If you decide that you want the hidden user account to be visible again, then perform the same steps as above but change the DWORD value of the respective user account to 1.

Posted in Windows, Windows 7, Windows Vista, Windows XP | Leave a comment

Windows Vista | Windows 7 – Classic Logon

Windows Vista | Windows 7 – Classic Logon

Since Windows XP, Windows has by default used the welcome screen for the user logon. However, there has always been the choice to change to what is known as the classic logon. So instead of getting a screen with a list of active user accounts and a little picture next to them, you get a simple username and password prompt in which you have to enter your logon credentials.

Sometimes, this is not required. Perhaps you have lots of user accounts or you don’t want other users to see the names of the other accounts. To change to classic logon and ensure that the previous logon name is removed, you need to make 2 simple changes to the system.

NB – This following method only works if you have Windows Vista Business/Ultimate or Windows 7 Professional/Ultimate/Enterprise. If you have the Home Premium version of Windows Vista or Windows 7 then scroll down the page to the using regedit section.

Using Secpol

  1. Go to start (the orb) > Run > and type in secpol.msc
  2. Go to Local Policies > Security Options > and set the following options as per the screenshot

Windows Classic Logon Secpol

Using regedit

If you do not have much experience using regedit, then see the following article on how to edit the registry using regedit. But please ensure you have a valid backup before completing this step!

Open regedit, and browse to HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Policies\System. Then set the dontdisplaylastusername and DisableCAD values as follows.

Windows Classic Logon Regedit

Now Log off

Once you log off Windows, you should now be prompted to press CTRL-ALT-DELETE. After doing so, you will be asked for your username and password.

Posted in Windows, Windows 7, Windows Vista | Leave a comment

Disable / Enable Windows Shortcut Icon Arrow

The following article describes how to add or remove the shortcut arrow from the left hand corner of a Windows icon – not just desktop icons and shortcuts. This tweak applies to shortcuts throughout the whole of the Windows operating system and it works with Windows XP, Vista and 7, as well as Server 2003 and 2008.

Enable / add the shortcut arrow to Windows icons

This tweak will put the shortcut arrow back in the left hand corner of all Windows icons.

Shortcut Arrow Disabled=Shortcut Arrow Enabled or Added for Icon

  1. Open regedit (start > Run > regedit)
  2. Navigate to and highlight “HKEY_CLASSES_ROOT\lnkfile”
  3. Right click on the right hand pane and select “New” > “String Value”
  4. Name the new value “IsShortcut” (without the quotes)

Or you can save this reg file and then import it (open it and confirm when prompted).

Disable / remove the shortcut arrow from Windows icons

This tweak will remove the shortcut arrow from the left hand corner of all Windows icons.

Shortcut Arrow Enabled or Added for IconShortcut Arrow Disabled=

  1. Open regedit (start > Run > regedit)
  2. Navigate to and highlight “HKEY_CLASSES_ROOT\lnkfile”
  3. Delete the “IsShortcut” value in the right hand pane

Or you can save this reg file and then import it by opening it and confirming when prompted.

Points to note

If you click either of the links to the reg files and it opens in a new window/tab in your browser then go back a page and try right clicking on the link and “Save Link As…” or “Save Target As…” (depending on your Web browser).

You will need to log off and back on again (or restart) for the new settings to take effect.

Posted in Windows, Windows 7, Windows Vista, Windows XP | Leave a comment

Windows Registry & Regedit Basics

Windows Registry & Regedit Basics

The Windows Registry is a database within Windows that stores important computer wide settings from applications and the Windows operating system itself as well as information about the computer hardware and individual user settings and preferences.

Within Windows XP, Windows Vista and Windows 7, the registry is split into user registry hives and system registry hives. The system registry hives (which store global or computer settings that apply to the whole of the computer and Windows) are stored in various files within C:\Windows\System32\Config. The user registry hives are specific to each user and are stored in each profile folder – e.g. C:\Documents and Settings\User1 or C:\Users\User1 (where User1 is the name of the user).

Editing the registry

Since the Windows registry stores various settings that control how Windows operates, it is important to first create a backup of any portions of the registry that you plan on changing before you make those changes!

To edit and backup parts of the registry, we use the builtin Windows tool called “regedit”. This can be accessed by typing in “regedit” into the Windows Run box (start > run or “Windows” + “R” on the keyboard) and then clicking “OK”.

Windows Regedit Run Box

Exporting and Backing up parts of the registry

Now that you have opened regedit, you must browse to the respective part of the registry that you would like to edit (firstly we will back it up). Then highlight the Hive, Key or subkey in the left hand pane and select File > Export or “right click” and Export. Browse to where you would like to save the file, give it a meaningful name, ensure the file type is set to “Registration Files (*.reg)” and click Save.

Windows Regedit Export and Backup and Save

You can now (relatively) safely make the registry change you need to and should you need to revert the registry back to what it was before you made your change, you can do so by “double clicking” your saved .reg file or “right clicking” and selecting “Merge”.

Posted in Windows, Windows 7, Windows Vista, Windows XP | Leave a comment

Windows 7 Keyboard Shortcuts

Windows 7 Keyboard Shortcuts

These are a selection of the new keyboard shortcuts that are now supported by Windows 7. Some of them are pretty useful.

Alt+P – Display/ hide the Explorer preview pane
Windows Logo+G – Display gadgets in front of other windows
Windows Logo++ (plus key) – Zoom in, where appropriate
Windows Logo+- (minus key) – Zoom out, where appropriate
Windows Logo+Up – Maximise the current window
Windows Logo+Down – Minimise the current window
Windows Logo+Left – Snap to the left hand side of the screen
Windows Logo+Right – Snap to the right hand side of the screen
Windows Logo+Home – Minimise/ restore everything except the current window

Posted in Windows, Windows 7 | Leave a comment