How to sort data in Excel

One of the most compelling reasons for having a database is the ability to quickly search or sort the data. But not many people are inclined or able to manage (let alone master) a say SQL Database or even a simple database like Microsoft’s Access (part of the Office Suite).

Well, never fear. If you have used Excel long enough and if you have been creating tables and lists of data in the spreadsheet, you would have felt the need to sort the data alphabetically or by value (largest to smallest, or vice versa). And you can do that easily in Excel!

For example, if you have a contact list that you have been updating as you go along, you would have a mess of a list over time with all the names in no particular order. Wouldn’t it be nice if you can sort that by alphabetical order?

Or how about a list of your travel expenditure when you spent a whole week in Europe and now you have lots of souvenirs that you bought as gifts. You want to sort them by value, from the most expensive to the cheapest, so that you know what to give to who.

OK, let’s see how to sort those two lists.

1. Contacts List

Say you want to sort the names by alphabetical order.

unsorted names

 

Highlight the cells whose data you want to sort, from B3:C13 (include the header “Name” and “Phone” but not the No. )

sort_contacts

Go to the Home top menu, Sort & Filter at the top extreme right. Click for drop down menu and select Custom Sort.

custom sort

In the Sort Table that opens up, Sort by Column “Name”, Sort on “Values” and Order “A to Z”.

sort

And here’s the sorted Contacts List by names in alphabetical order.

sorted names

2. Travel Shopping List

Similarly for the travel shopping list:

unsorted items

Highlight the cells for the Items and Prices that are to be sorted:

sort_items

Then go to the Home top menu and click on the Sort&Filter for the drop-down menu, select Custom Sort.

Sort by the Column Price, Value and Order from Largest to Smallest:

sorted prices

Now you know the value from the largest to the smallest, for you to decide the recipients of your gifts.

 

How to center text across multiple columns in Excel

This feature is most useful when you have text in a cell that you use as a label or title for a range. By centering the text across the range, it makes it easier to see that the label or title applies to the entire range.

center text across cells

To center text across multiple columns, do the following:
1. Select a range that consist the text and the cells across which you want to center this text.

2. On the Home tab, in the Alignment group (or Merge & Center), click the dialog box launcher:

3. In the Format Cells dialog box, on the Alignment tab, click the Horizontal list and then select Center Across Selection in it:
4. Click OK.

centered text

How to number the rows sequentially in Excel

start values
Select the first cell in the range that you want to fill.
Type the starting value for the series.
Type a value in the next cell to establish a pattern. …

Select the cells that contain the starting values.

 

Drag the fill handle across the range that you want to fill.

IMG_8088c filled values

 

Samples of Series

various values

 

How To Protect HDMI Inputs From Being Toasted By Lightning

So you have a super duper Home Theatre system complete with a jiffy HD Projector and a top-of-the-line AV Receiver linked by a 50-foot (15-metre) HDMI cable that snakes overhead in the ceiling. One end is connected to the HDMI input port of your overhead HD Projector while the other end is connected to the HDMI input port of your AV Receiver across the room.

What happens when there is a severe thunderstorm and there is one or more lightning strikes nearby? Does your system go on the blink? If yes, then you share the same bad experience as I have. The close proximity lightning strike has induced a large voltage spike  in your long HDMI cable that completely toasts your HDMI ports. If you’re “lucky” then maybe only either the Projector’s or the AV Receiver’s  HDMI port gets toasted. More likely, both will be zapped.

After that has happened to me 3 times in as many years, I decided to be proactive as I was getting embarrassed submitting my home insurance claim every year. Not to mention the inconvenience of the downtime pending repairs. I installed my solution early this year and having survived 2 severe thunderstorms since the installation, I am confident this is a viable solution. Read on.

hdmi-switchThe solution calls for two HDMI Switches. I searched for a simple 2-to-1 HDMI Switch but the simplest I found is this 3-to-1 HDMI Switch. I wanted a mechanical switch but finally settled for this electronic switch because it has a remote. This makes the switching convenient when the switch is mounted at the Projector, ceiling-high.

projector-hdmi-switchInstall one of the HDMI Switches to the HDMI Ouput of the AV Receiver. In my case, I connected Port 3 of the switch to the AV Receiver’s HDMI Output. The switch’s output port is then connected to the long HDMI cable. The other end of the AV-receiver-hdmi-switchHDMI Cable connects to Port 3 of the second HDMI Switch mounted at the overhead Projector. The output port of this switch connects to Projector’s HDMI Input port. That’s it. When the system is on, I use the remote to switch to Port 3 of both switches. When I have finished viewing, I switch to the un-used Port 1 of both switches, before shutting down the system.

The cost? Only RM80.00 (USD24.00) for each HDMI Switch.  I reckon that in the event of a really bad lightning strike, it’ll be a USD24 fuse. But so far, neither switch has failed.

Does this help you? Share your experiences here.

Rip, Compile and Burn Audio CD

Rip, Compile and Burn Audio CD

At some time, if you are a music buff, you may want to compile your own awesome playlist on an audio CD. And I don’t mean the MP3 compilations as you know MP3 is a lossy format. No sir, you want a compilation of the original lossless tracks, which means you have to rip or download or copy a track in either FLAC or WAV or WMA, which are lossless formats. Now, at this point if you start asking which of the three formats is the best to use, you’d be opening up an endless debate. But if you were to ask which one I use, I’d say all three but for the last-but-one stage I use WAV. Why WAV? And what about the final stage? Read on….

Disclaimer: What follows may not be the best or optimum way to rip, compile and burn an audio CD. However, it documents the methodology that I have finally settled on, after countless coasters made from useless CDs. Hopefully this will spare you the frustrations that I had endured up to now.

The free Tools I use:

1. Ashampoo Burning Studio

2. VLC Media Player

3. aTube Catcher

4. Audacity

5. iTunes

6. Windows Vista Acer Notebook with CD/DVD player/burner

1. To rip an audio track from a music CD, I use Ashampoo Burning Studio and rip it in the lossless WAV format.

2. To get lossless FLAC high quality audio tracks from the Internet, use Bit Torrent to search and download.

3. Use aTube Catcher to record streaming MP3 tracks and convert to WAV (yes, I know…the MP3 tracks are already lossy…but if no choice, then MP3 will do. Convert to WAV just to keep all the files in same format).

A great website to download MP3 songs is http:/grooveshark.com/

4. Two ways to convert FLAC to WAV.
Use VLC Media Player to convert the downloaded FLAC files to WAV.
Media->Convert/Save->Add (the Flac, MPEG4 files)->Convert/Save as Audio-CD (WAV).

5. Or use Audacity to tidy up the files (cut off excessive silence front and back; and also to normalise the track to avoid clipping due to excessive amplitude). Open the FLAC files, edit and Export Audio as WAV.

6. Steps 4 and 5 convert the tracks to WAV as iTunes does not recognise FLAC. Transfer the final WAV tracks to iTunes’ Library as a Playlist.

7. Use iTunes (the icons at the lower left corner) to burn Playlist to CD as Audio CD.

8. Caution:

a. Check capacity of the blank CD and the total tracks size. iTunes does not check this and will give an error (the dreaded 4450 code) at the end!

b. Avoid the “Burn At Maximum Speed Possible”…..again iTunes only gives the error at the end, if your CD brand cannot handle the high speed. After iTunes has made me a bundle of coasters, I wised up and throttled the burn speed down to 8X, which worked for me.

c. If you want the track names to be displayed by your CD Player (if it has this feature), then be sure to check “Include CD Text” before burning.

Footnote:

Why not just use Ashampoo Burning Studio to rip tracks from CDs to WAV or WMA format and burn the compilation as WMA (or WAV)? Unfortunately not all CD player plays WAV/WMA formats. That’s why I use iTunes to burn an Audio CD for full compatibility in all CD players.

If your CD Player supports FLAC, WAV or WMA, and you don’t intend to play your CD anywhere else nor share with anybody else, then just use a Burn Software (eg. Ashampoo Burning Studio, Nero, etc..) to rip the tracks into WAV or WMA format and burn the CD with either one of the formats to cut down the complexity of all the foregoing convoluted steps.

Remove a WiFi Network From Your Computer

One day you may suddenly experience a jerky, intermittent streaming of your favourite FM station on your PC. What happened? After all you have the whole 10Mbps bandwidth to your greedy self in your home castle. Read on for a possible scenerio.

Now you recall…a week ago, your service provider failed and your kind neighbour offered you the temporary use of his WiFi, which you can access from your house. One week later, you may have forgotten and your PC still automatically latches on your neighbour’s WiFi because depending on where you are sitting in your house, your neighbour’s signal may be stronger than your own WiFi. Now recall further that he subscribes to a different service provider and he has a slower connection ( 4 Mbps? ) compared to your 10 Mbps. Ah, so that’s why you experience such poor audio streaming of your favourite FM station. So how to remove that slower WiFi Network?

1.  Go to your Windows Control Panel and click on Network and Sharing Centre.

2.  Click on “Manage Wireless Networks” on the left hand menu.

3.  Right-click on the wireless network that you want to remove.

4.  The pop-up context menu offers you the option to “Remove Network”. But hang on….maybe you should just click on “Properties” and remove the automatic connection. Then you still have the option to manually connect if you need to.

Time to have tea now.

 

 

 

How To Fix A Plastic Bolt Without A Washer

While this Tip shows a quick-fix to a problem with securing a toilet seat, the same method can be applied to similar problems with other plastic situations.

seatA toilet seat, usually with a hinged cover, is bolted to the toilet bowl for a flush system. The bolt is usually made of plastic with one end flared to secure the seat to the bowl. The problem is, very often the flared end of the plastic bolt slips right through the hole of the hinge’s base. You can get a metal washer but it would rust in no time. Anything else (stainless steel washer, plastic washer, etc) would be too much of an effort to find/buy, if at all available.

 

 

seat-3A view of the component parts. The seat, the hinge, the plastic bolt and nut.

 

 

 

seat-5aSee the bolt inserted in the hinge but is able to slip out of the hole.

 

 

 

seat-6aAll that’s required. A chisel-like tool ( a large screwdriver will do fine ) and a hammer ( or a rubber mallet, if you are a stickler about using the right tool ).

 

 

seat-7Use the screwdriver+hammer to make indentations in the plastic bolt’s flared end.

 

 

 

seat-8Compare the two bolts: one has the indentations completed.

 

 

 

seat-9Both completed. Just a minor tip here: in a paired situation ( two bolts, two lamps, two batteries…etc.) always repair both/change both together. If one has failed, most likely the other will fail soon too.

 

 

seat-10The bolts now stay well in place in the hinge. Re-bolt the seat to the toilet bowl and we’re done..

Hide Site Title

WordPress is now an incredibly popular way to build a fully functional website from the traditional blog base. In fact WordPress.org is now a full-blown CMS in its own right. With lots of templates, many of which are free, it is easy to see why it is so popular.

header-text600opt

Let’s assume you now use a template (say, Twenty Twelve) to create your website. While it allows you to write your Site Title and Site Description (Figure 1), chances are that you will want to replace it with a snazzy logo title image (Figure 2). Why would you  want to do it? Well, the most compelling reason is there for you to see. Which one looks better?

header1-logo600opt

But why don’t you just select the desired font type and size and colour instead of creating an image? Simple answer is that you want to be sure that all visitors on all machines with all browsers (ok, ok….almost all) will see the same  snazzy logo title image. If you just changed the text font type….some machines and/or browsers may not have that font installed and they may end up seeing your title in (probably!) boring Arial or Times Roman! Certainly not what you intended in your site’s design.

OK, now you have replaced the text title with your image logo-title. So that’s simple enough, what’s the problem? Well…you soon learn that without the text site title, your SEO sucks. For your SEO ranking, the appropriately named site title and site description are critical because they are picked up in the meta title tags. So what’s the solution? An easy obvious way is to choose (in the template) not to display the header text. That way, the  site title is hidden and yet it is still picked up in the meta title tags. But the tagline is part of the header text and gets hidden as well. The solution is simple! Display the header text (site title and tagline) but hide just the site title instead. How? Colour the site title text the same as your background (eg. white, or whatever). The search engines’ crawlers do not care what the colour is, they will still pick up the site title text. (Figure 3). So you can have your cake and eat it too! By choosing to display the header text, the site’s tagline or description remains visible, which is normally desired.

header2-logo-text600opt

 

Of course, you should customise your template using a Child Theme and the code chnage in its CSS style sheet as follows:

.site-header h2 a {
color: #ffffff;
display: inline-block;
text-decoration: none;
}

 

Why auto Spell Correction is a bad, bad idea

Why auto Spell Correction is a bad, bad idea on a smartphone. Most of the time when we make a spelling mistake on “normal, common” words, the recipient would still be able to make out what we mean, even without the Spelling Correction. However, when we want to use an acronymn, an abbreviation or a foreign word, we do not want the Spell Checker to offer a “correction” and make it without us noticing it. That’s disastrous! If you have ever been caught in an embarrassing or difficult situation because of this, you will know what I mean. Therefore, I make my case for “No Spell Checker” as the default, unlike the present situation where it’s already implemented as the default and very often we don’t even know how to turn it off.

Two cases to illustrate:

1. I typed and sent a URL to a friend by SMS. He kept saying the link doesn’t work. I checked and it worked on my original document. Much later I realised that when I typed the abbreviations GMBO, the not-so-smart-phone kept auto-correcting it to GUMBO. And of course I didn’t realise that at the time.

2. A friend texted me to say that his friend would be calling me. I texted back to ask whether that friend could speak my dialect or English. He replied, “Try hollowness”. Of course, I sent a burst of ???? to him. He then realised that his phone had auto-“corrrected” the dialect “hokkien” to “hollowness”.

I rest my case.

 

 

 

 

 

My Travel Advisory!

Boy, do I have some exciting things to share with you!

I am planning my family’s European Holiday for August 2013. I started planning this more than a month ago, so I have the time to experiment and explore various options.

First, the “technical” part….which travel Apps should I use? Here are the (super) apps that I’ve settled on…you can check them out:

1. Tripit

Tripit will automatically (if you allow auto import) consolidate your flight and hotel reservations and prepare the complete itinerary for you, on your PC and on your smartphone. How cool is that?

2. Frommer’s

Frommer’s Travel Tools include currency converter, time translator, tip calculator, flashlight, packing list, postcards and guides. How convenient!

3. MapQuest Travel Blogs

Arguably the best travel blog app for you to record your trip. Don’t trust your memory; record your trip details as you go along.

4. TripAdvisor and Expedia

I use these two in tandem… I use the Expedia map to view the cluster of hotels around the main train station. Then I use TripAdvisor to read the reviews of the hotels to shortlist them. Finally, book through Expedia.

5. TripAdvisorCityGuides and fotopedia

Both give excellent free travel guides for various cities.

6. Frommer’s, Lonely Planet and Rough Guides

Other excellent travel guides but only some samples are free…..mostly chargeable. But check them out.

You can easily find all those Apps by name in your iTunes Store or Google Play. Check them out!

5. For the budget airlines, I look at easyJet, RyanAir and Vueling. Due to time constraint, I am not taking the intercity trains (which can be more expensive than the budget airlines). The cities are London-Venice-Rome-Barcelona-London in 9-days. Of the three, easyJet is the most generous with the hand luggage (bigger size and no weight limit).

8. And a most useful tip…. Do NOT buy in advance your airport transfers (bus); buy only when on board your bus as the flight may be delayed. Duh! Perhaps that’s too obvious.

Here’s wishing you a safe, pleasant, happy Holiday..wherever you may go.