MORGB Blog

Wednesday, October 02, 2013

Filtering Data in Excel

Spreadsheet software has many valuable functions, but one that I find most useful (and use most frequently) is the data filter (aka “auto filter”). This post will be about how to use the data filter in Microsoft Excel (the function is similar in most other spreadsheet programs). For each example, I’ll be using a sample data set of medals won by Olympic athletes available here.

First, you have to turn on the filter. To do this, highlight the row containing column names for your data set, then select Filter from the Data tab in the ribbon:

For Excel 2003 and prior, click the Data menu, then point to Filter, and click AutoFilter.

Once you’ve done this, you’ll see a button to the right of each column heading. Clicking this button will give you a menu of options for sorting and filtering your list based on data in that column:

As an example, we’ll look for only records of gold medals in archery, ordered by year (ascending). First, we’ll filter on the Sport column (uncheck Select All, then check Archery to do this quickly):

Next, filter the Gold Medals column for values greater than zero (there are multiple ways to do this, but I’m demonstrating the quickest for this particular data set):

Finally, sort the Year column ascending (smallest to largest):

This gets us to our result. To remove the filter on any column, select the menu for that column and then select Clear Filter From <Column Name>:

As you can see, this is a simple but very powerful tool. Enjoy!

Monday, September 16, 2013

Remove All Formatting in Excel

Let’s say you have a spreadsheet that someone built for printing or looks. It might have gridlines, background colors, special fonts, and the like:

All you want out of this is some nice rows and columns for analysis, and the formatting is not helpful for this purpose. How do you quickly simplify this data without updating every single column or cell? First, select the entire worksheet (or whatever area you want to update). Under the home tab (far right side), select Clear Formats:

Once you’ve done that, your data will be ready for use:

There’s only one drawback to this approach that I’ve found so far. If you have date fields in your data, you’ll have to re-apply the date format to the affected columns/fields. Otherwise, the column will display the date serial number because it has no format assigned. Other than that, this method works great for quickly removing formatting so that you can focus on the data.

Monday, July 01, 2013

Registry Hack - Add "Edit With..." Shortcut to Context Menu (Using Notepad++)

I've had to do this several times now, so it's time to write it down. Here are the issues:

1. The existing 'Edit with Notepad++' context menu link generates an error on execution
2. Worse, it doesn't display at all through Multi-Commander

No like!

So here's how to fix it. First, get rid of the existing context menu entry, which you'll find under HKEY_CLASSES_ROOT/*/shellex/ANotepad++64. Now you're ready to add the new entry:

1. Go to HKEY_CLASSES_ROOT\*\shell.
2. Right-click shell and add a new key called "Edit with Notepad++" (or whatever you want to call it).
3. Now Right-click on the key you just created, and add a new key beneath it called "command". The end result should look like this:

4. Within the command key, modify the default string to the full path of the Notepad++ executable in quotes followed by %1. For example:

5. Go back to your original key (Edit with Notepad++ or similar) and add a new string value called "Icon". Modify the value to the full path of the Notepad++ executable in quotes followed by ,0. For example:

The changes will take effect immediately, and you should now have the item in your right-click context menu:

SQL Server 2012 Linked Server for MS Access

Here's the scoop: I've installed SQL Server, but I want to use Management Studio (SSMS) to query an Access Database. I followed the directions, and also found out that the Jet OLEDB 4.0 provider is only available for 32-bit. But now I'm getting this error:

Text:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "[server name]".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "[server name]" returned message "Unspecified error". (Microsoft SQL Server, Error: 7303)

There are some solutions to similar errors, but none of them are working. What now? Try running SSMS as an administrator. In fact make sure it always runs with administrative rights by right-clicking the shortcut/executable, selecting properties, and checking the "Run this program as an administrator" box:

Once you do this, SSMS will have full rights to connect to your MS Access files (and any others you'd like to query).

Remove All Images From Excel Worksheet

So I have a spreadsheet I've extracted, and it has two columns of images in it (which I don't want):

Ok, so I'll just delete the columns like I would with any other data, right?

Hm. That didn't work. Are you really telling me that I have to select and delete these one-by-one? NO WAY!

Thankfully, there's a quick macro you can run to get rid of all images in a worksheet like this:

Dim s As Shape
For Each s In ActiveSheet.Shapes
s.Delete
Next

Running this will remove all these pesky images in one shot. No fuss, no muss.

Happy deleting!

Quickly Line Up Rows/Columns in Excel

Have you ever pasted a table into Excel and gotten results like this?

No like!

Wouldn't it be nice to line up and autofit all the rows and columns in one click? Here's a quick macro that will allow you to do just that:

Cells.Select

Selection.ColumnWidth = 255

Cells.EntireRow.AutoFit

Selection.ColumnWidth = 9

Cells.EntireColumn.AutoFit 'fit the columns

Basically, it selects all the cells, makes the column extremely wide, then autofits the rows and columns. The result is more like this:

Much better. Enjoy!

Friday, August 10, 2012

Rob Peter to Pay Paul

The idiom "Rob Peter to pay Paul" is fairly well known and most people understand it to mean solving one problem by causing another. In many cases, people use it to mean paying a debt by incurring another debt. Here are some examples:

...to discharge one debt by incurring another (http://www.phrases.org.uk/meanings/rob-peter-to-pay-paul.html)

Why borrow money to pay your bills? That's just robbing Peter to pay Paul. (http://idioms.thefreedictionary.com/rob+Peter+to+pay+Paul)

Jacob: I think I'm going to apply for another credit card so I can pay off some of my bills.
David: Robbing Peter to pay Paul, eh?! Just be carful not to get into debt. (http://www.urbandictionary.com/define.php?term=Robbing%20Peter%20to%20pay%20Paul)

For example, They took out a second mortgage on their house so they could buy a condo in Florida--they're robbing Peter to pay Paul. (http://www.answers.com/topic/rob-peter-to-pay-paul)

We all get what these examples are trying to say, but they are not a completely accurate use of the idiom. Imagine robbing a bank to get money to pay your bills. If you get away with it, you're out of debt and you've solved your financial problem. If you don't get away with it, you go to jail. You could say that you've incurred a social debt, or you could imagine monetary fines being assessed, but none of these are what the above examples were talking about.

If we were to use a completely accurate metaphor, we would say in these cases that we are "Borrowing from Peter to pay Paul".

How important is this? Not very. As I said, everyone understands what the idiom represents, even if the language is not precise. However, I think it is an interesting mental exercise to challenge assumptions, including habitual use of words that may or may not be exactly correct.

Tuesday, July 10, 2012

Why Is Education Useful?

When required to learn or practice something we don't really enjoy, we often ask "When am I ever going to use this in real life?". The question is somewhat rhetorical in that we have already likely judged the activity as useless and expect an answer that will justify that position. However, even if the answer to that question is "You won't", the process of education and the topic itself are still very useful. Why?

Consider exercise, particularly strength training: push-ups, pull-ups, squats, bench press, you name it. When will you be asked to perform any of these activities in the course of a "real life" activity or job? You won't. However, these exercises will make you strong and prepare you to better perform real life activities like lifting objects, climbing, walking, or even just sitting up straight. That is why they are useful.

Now consider education. When will you have to use truth tables in "real life"? When will you have to write term papers? When will you have to recite a list of US Presidents, or world capitals? You might come up with answers for some fringe jobs or tasks, but for most people the answer is "You won't". However, education makes your mind strong and prepares you to better perform real life activities of every type. That is why it is useful.

Education is like mental exercise. The more of it you can get on a regular basis, the better.

Thursday, June 28, 2012

MS Office Clipboard Manager

Using the clipboard - copying, cutting, and pasting - is one of the most fundamental ways to increase productivity and efficiency in your workday. For most uses, keeping only the most current copied item is just fine - until it's not. Maybe you copy one thing, then copy another, overwriting the first copy you haven't yet pasted.

A coworker recently had something like this occur. They copied the text of an entire document, went to paste it into a new document...and it was gone. When I got the call, I shared this tip with them.

To find the clipboard manager, look on the 'Home' tab of the ribbon on the far left, and click on the box in the corner:

This will display the clipboard pane. If you want Office to collect clipboard items whether the clipboard pane is visible or not, click Options at the bottom of the pane and select 'Collect Without Showing Office Clipboard'.

Items on the clipboard will survive until you clear them or until you close all Office programs.

If you are working on a low-resource (RAM) machine, you can also open up more memory by clearing your clipboard through the clipboard pane.

Wednesday, June 27, 2012

Starting Back Up

I'll be starting this blog back up again. Yes, I do still have the Tumblr blog, and that will stay as a quick place to post links to interesting things. This blog will be a place to post more detail on tips and interesting tricks I pick up over time - like the successful Jasper Reports series (part 1 / part 2) from last year.

The reason I am doing this is to practice writing and communication, which I think is important. If I can help someone out along the way, all the better.

To start off, here's a tip about keyboard shortcuts.

Ctrl+Shift Keyboard Shortcuts in Excel (and Windows)

While experimenting with some Excel (2010) keyboard shortcuts (as mentioned in this article), I discovered to my dismay that the Ctrl+Shift+0 (unhide) shortcut didn't seem to be working. Ctrl+0 worked just fine to hide, but Ctrl+Shift+0 just did not respond (and yes, I had the appropriate rows/columns selected).

What gives, Excel?

Well, it turns out not to be Excel's problem at all. Windows (Vista and 7) are to blame in this case. These versions of Windows use the Ctrl+Shift combo to switch keyboard layouts (by default, even if you don't have multiple layouts). Here's how to fix the issue (see this MS KB article):

1. Click Start, and then click Control Panel.

2. Double-click Regional and Language Options.

3. Click Keyboards and Languages, and then click Change keyboards.

4. Click Advanced Key Settings, and select Between input languages.

5. Click change Key Sequence.

6. For Switch Keyboard Layout, select Not Assigned.

7. Click OK to close each dialog box.

Once you've done this, Windows will no longer hijack Ctrl+Shift, which will allow Excel and any other program to accept this key combo for use in keyboard shortcuts.