Transposing Spreadsheets

Ever started creating a spreadsheet, only to realize that you’ve messed up and the rows should really be columns and vice versa? If you work with spreadsheets a lot, it probably happens more than you’d like. It seems like a small annoyance, but it takes a surprising amount of time to correct. Here’s the fast way.

I personally tested this tip in both Microsoft Excel 2003 and LibreOffice Calc; information about Excel 2007 and 2010 was pulled from here.

 

In LibreOffice or Excel 2003 or below (if you have menu bars, this is you):
  1. Copy the cells you want to transpose. Often this will be the whole spreadsheet if you just started, but sometimes you might only need part of it. Depending on your exact software, a dotted line may start moving around the copied region.
  2. Move to a new blank cell below everything else in the spreadsheet. Don’t worry, you can move it to wherever you want later. (For some reason, the option to transpose is occasionally deactivated if you try to paste into an area overlapping the current text.)
  3. Choose Edit –> Paste Special.
  4. Select the Transpose checkbox and paste.
  5. Delete the incorrectly positioned data and move the transposed cells to where they belong.
In Excel 2007 or above (if you have a ribbon instead of menu bars, this is you):
  1. Copy the cells you want to transpose. Often this will be the whole spreadsheet if you just started, but sometimes you might only need part of it. Depending on your exact software, a dotted line may start moving around the copied region.
  2. Move to a new blank cell below everything else in the spreadsheet. Don’t worry, you can move it to wherever you want later. (For some reason, the option to transpose is occasionally deactivated if you try to paste into an area overlapping the current text.)
  3. Go to the Home tab of the ribbon, click the little arrow below the Paste button, and select Transpose.
  4. Delete the incorrectly positioned data and move the transposed cells to where they belong.
Soren “scorchgeek” Bjornstad
http://www.thetechnicalgeekery.com

 

If you have found an error or notable omission in this tip, please leave a comment or email me: webmaster@thetechnicalgeekery.com.

 

Copyright 2012 Soren Bjornstad.
Verbatim copying and redistribution of part or all of this article
is permitted, provided this notice is preserved.

Using the Internet to Recall Sayings and Quotations You’ve Forgotten

Ever remember fragments of a saying? Or just part of a quote? You’re not alone. The trick is figuring out how to search for it. Here are two searching tricks to rescue you from that annoying feeling of remembering just part of something.

1: The Asterisk
There’s a really handy Google trick that almost nobody seems to know about: an asterisk (*) matches any single word. This lets you easily search for quotes that you can only remember part of. For example, “one death is a * a million deaths is a *” will easily bring up Joseph Stalin’s fairly well-known statement, “One death is a tragedy; a million deaths is a statistic.” (I have this posted on my wall right in front of me, so I’m not liable to forget it, but I couldn’t think of a better example.)

 

This trick works wonders; there are only so many possible sentences with that precise structure, and if it’s something reasonably well-known, the first page of hits is liable to give you a unanimous verdict on what you were trying to think of. If you get irrelevant results (which has actually never happened to me), you can try putting the whole thing in quotes. It’s also possible that you remembered the part that you knew wrong; try adding more asterisks in places you weren’t sure of.

 

2: Searching Books on the Internet
This is what happened to me the other day. I remembered the following phrase: “You may be the world champion tetris player, but eventually….” That was it. For the life of me, I could not figure out where it came from, or indeed any information about it. So I went to Google and just typed that in. Guess what? There was exactly one hit, a Google Books result taking me to exactly the passage I was looking for.

 

To test whether this was a transferable trick, I grabbed five books from my bookshelf, flipped them open to a random page, and typed in a quote from each. A search for the quoted passage (only ten words long or so, and devoid of any specific names) brought up the correct title of the book in five out of five cases.

 

The uniqueness of fairly mundane English sentences is truly surprising. Some things that worked:
  • “It lay out in the open, several feet away and unreachable” (Once Upon a Time In the North by Philip Pullman, 1 result)
  • “nudging people to use the standard cursor keys” (Windows XP Annoyances for Geeks by David Karp, 3 results)
  • “For the last six months we have exhausted every means of locating you” (East of Eden by John Steinbeck, 3 results).
So next time you can’t remember the source of a quote you’re thinking of, forget about thinking hard and scanning through books–just type it into Google. In most cases the websites also let you see a couple of pages of context, so you probably don’t even have to go get the book. Remember to use quotation marks around the quote, assuming that you’re fairly sure your words are exactly right–you’ll get much more relevant results.

 

Many sites and devices that you might use have their own search features (for instance, I can search all the books and documents on my Kindle from the main screen), but with the success rate of this technique, I’d only recommend using that if you don’t have immediate access to Google.

Avoiding Unnecessary Typing When Entering URLs

Repeatedly typing http://www.google.com gets old really fast. Here are three ways to avoid typing any more of a web address than you need to.

1: Simplify the URL
Most people probably already know this trick, but every modern web browser will fill in the http:// for you if you omit it. You also don’t normally need to include the www., but in this case a few stupid websites will return an error if you omit it. (When I attempted to contact my school corporation about the fact that their website did this, I discovered that nowhere on the webpage was there a webmaster email.)

 

So all you really need to type is google.com. (Additional Note: If your website has something else before the dots, like this website, tips.thetechnicalgeekery.com, that’s called a subdomain, and you cannot omit that–it leads to a different page than if it was missing.)

 

2: Ctrl-Enter
If you type just google and press Ctrl-Enter, the .com and .www parts will be filled in for you. Some browsers support additional modifiers of Ctrl and Shift to allow you to also use this trick for .org and .net websites as well–try it and see if yours does.

 

3: Autocomplete
Google Chrome has a really nice feature where beginning to type runs an incremental search through websites you visit frequently. So all I have to do to get to Google is press Ctrl-T to open a new tab, type a g, and google.com will appear in the address bar. Then I just press Enter and I’m there. This can save you an enormous number of keystrokes, and is one of the primary reasons that I use Chrome. If you don’t have Chrome, you might consider trying it out.

 

—–
Soren “scorchgeek” Bjornstad
http://www.thetechnicalgeekery.com

 

If you have found an error or notable omission in this tip, please leave a comment or email me: webmaster@thetechnicalgeekery.com.

 

Copyright 2012 Soren Bjornstad.
Verbatim copying and redistribution of part or all of this article
is permitted, provided this notice is preserved.

Prolonging the Life of Your Batteries

Everyone has heard as much advice as they could need about how to keep their rechargeable batteries running the best for the longest. The trouble is that everyone’s advice contradicts everyone else’s. Some people say you should keep your batteries fully charged at all times; others say you should fully discharge your battery before recharging it again. Making the situation more confusing is the fact that there are at least four distinct kinds of rechargeable batteries (lead-acid, nickel-cadmium, nickel-metal hydride, and lithium ion), all of which work best with different conditions.

The following article applies to any lithium-ion (Li-ion) battery. Basically all portable computers, cell phones, mp3 players, etc., use lithium-ion batteries. If you’re not sure, you may be able to physically check the back of the battery pack, look in the manual, call the manufacturer, or just Google it (depending on how common the device is). If your device turns out to have a nickel-metal hydride (NiMH) battery (or an ancient-technology nickel-cadmium, or NiCd, battery), then you shouldn’t pay attention to these tips with that particular device, as what is good for one type of battery may well be directly harmful to another.

 

The following myths, facts, and statements about lithium-ion batteries are in no particular order.

 

You should periodically discharge your battery completely.
False. This belief generally comes from people who worked with old NiCd batteries, which had “memory,” meaning that if you discharged them only partially a few times, they’d soon refuse to charge to their maximum capacity. This could be remedied by completely discharging and recharging the battery a couple of times. Li-ion batteries do not have “memory,” so they do not need to be completely cycled–in fact, unnecessarily discharging them will shorten their life.

 

Leaving your laptop continuously plugged into the power grid is bad for the battery.
True. There is not a complete explanation for why this happens, but it certainly does. We do know that storing a Li-ion battery at 100% charge is stressful for it (keep reading for more on that), so it would make some sense that continuously using it at 100% charge would be bad as well. Additionally, laptops are hot, and Li-ion batteries do not like heat. If you mostly use your laptop at your desk, it makes sense to remove the battery and store it, then put it in and charge it up right before you do need to travel.

 

It is dangerous to leave a fully charged battery plugged in.
False. While batteries and chargers are never a sure bet, nearly all chargers will properly shut off current when the battery is fully charged. Depending on your charger, you may end up keeping your battery unnecessarily warm, which is not good for it. However, there is no safety risk in this practice.

 

Li-ion batteries can spontaneously explode.
Sort of. Batteries rarely truly explode, but they can occasionally catch fire. However, it is extremely rare, and is not something you generally need to worry about. If your battery begins to heat up excessively or show other signs of failure, you should take it out of the device immediately and put it somewhere with adequate ventilation. Even if it cools down normally, you should replace it or at least have it looked at by a qualified technician before reusing it. If they actually catch fire, lithium-ion batteries can be safely extinguished with a dry-chemical or foam fire extinguisher.

 

Li-ion batteries permanently lose capacity with every cycle.
True. This happens with many types of batteries. The capacity lost, however, depends on the depth of discharge, or how far you allow the battery’s charge to drop before recharging it. See the next statement.

 

Li-ion batteries last longer if they are not fully discharged.
True. The lower the discharge, the less stress is put on the battery. Completely discharging your battery 500 times may have the same effect on your battery as discharging it a quarter of the way 2,500 times! You should charge your battery more often and only use it completely when you really need to. Don’t, however, let this trick you into leaving your laptop plugged in all the time; as we should know by now, that’s not good for it either.

 

Li-ion batteries last longer if they are not fully charged.
Sort of. They will generally last longer if lower charging voltages are applied, which results in a lower capacity when the charge cycle is complete. However, simply charging your battery for part of the time with the full voltage will not accomplish this, and there are few to no commercially available chargers that use lower voltages. So when you’re deciding what to do with your battery to maximize its life, this isn’t really a consideration.

 

Electronic battery meters are accurate.
Sort of. Many modern battery meters are quite smart. However, batteries are not like gas tanks, which have a precise amount of gas remaining at all times–they use electrochemical processes, and they’re extremely unpredictable. Furthermore, their capacity changes as they age. Battery meters can often adjust themselves to give you a fairly accurate time remaining until discharge, but you still shouldn’t rely on them to give you a perfect estimate. (Furthermore, electronic devices consume different amounts of power depending on what you’re doing on them, providing a further obstacle to accurate battery metering, especially when the time provided is on the order of eight or ten hours in the future.)

 

You should discharge your battery completely if you need to calibrate the battery meter.
True. Some battery meters can better estimate the current state of the battery when they have a chance to see what happens to the battery as it fully discharges and then charges again. If you think your battery meter is doing a poor job, it might be worth a try.

 

Li-ion batteries do not like heat.
True. Especially when being stored, heat decreases the performance of Li-ion batteries. You should strive to keep them cool when possible. Many devices keep batteries quite hot, especially laptops; making sure there is clearance between the bottom of your laptop and whatever you’re resting it on can help.

 

You should store a Li-ion battery fully charged.
False. Roughly 40% is the magic number (many people say simply halfway charged). It is difficult to measure the exact percentage of capacity a battery is at (see the thing about battery meters), but running it roughly halfway down will certainly help a lot. In one test, a fully charged battery stored at 77 degrees Fahrenheit permanently lost 20% of its capacity in one year, whereas the same battery stored at 40% charge lost only 4% of its capacity.

 

It does not matter where you store a Li-ion battery.
False. Lithium-ion batteries should be stored in a cool, dry place to minimize permanent capacity loss. In one test, storing a properly charged battery (see above) at 77 degrees Fahrenheit as opposed to 104 degrees saved 11% of the battery’s capacity from being permanently lost, and storing it at freezing allowed it to lose only 2% of its capacity in an entire year.

 

If a Li-ion battery is left uncharged, it can permanently stop working.
True. Li-ion batteries have something called a protection circuit, which helps to prevent the battery from overheating or overcharging. The protection circuit needs some juice to keep watching the battery, which it takes from the battery (logically enough), so if the battery is about to die completely, the protection circuit will disable the battery before going under. This is called “sleep mode,” and regular chargers can never wake up a battery that has gone into sleep mode.

 

It is okay to allow a Li-ion battery to discharge completely before storing.
False. As discussed above, if you store an empty battery for more than a short length of time, the protection circuit can permanently disable the battery. Always charge a battery (preferably to about halfway charged) before putting it into storage. If your device dies, recharge it as soon as possible.

 

Li-ion batteries stop working before they are really out of juice.
Sort of. The protection circuit typically cuts off power to the device when each cell is at about 3 volts (the maximum is usually 3.8). However, here’s the “sort of”: Most devices can’t run off less than 3 volts/cell anyway. Furthermore, the protection circuit needs to leave itself some power to keep the battery safe until it’s recharged.

 

It is possible to short-circuit a Li-ion battery by carrying it in your pocket.
True. While just carrying a battery in your pocket won’t do anything, most people are liable to carry other things in their pockets as well, like keys and change. Metallic objects can touch the contacts and cause a short circuit, which could potentially cause the battery to overheat and catch fire. Admittedly, this doesn’t happen often, but to be safe you should always place electrical tape over the contacts and/or place the battery in a plastic bag when transporting loose batteries, whether you put them in a pocket, purse, or luggage.

 

You cannot bring Li-ion batteries on airplanes.
Mostly false. You cannot check Li-ion batteries. However, you can keep them in your carry-on, as long as they are properly isolated from things that might cause a short (see above). This policy is because a fire that starts in the passenger compartment can be easily put out (Li-ion battery fires can be extinguished in a matter of seconds with ordinary fire extinguishers), but nobody can do anything about a fire that starts in the hold (if they even notice it in the first place).

 

You can throw away Li-ion batteries in the trash.
Sort of. Lithium-ion batteries aren’t nearly as toxic or dangerous as nickel-cadmium or lead-acid batteries, so it is not considered taboo to throw one in the trash. However, you still can and should recycle them if you have any option to (there are a lot of stores and municipalities that accept used Li-ion batteries, so it shouldn’t be terribly difficult unless you live in the middle of nowhere).

 

Summary
DO:
  • Recharge batteries as often as possible, and avoid discharging them completely.
  • Remove batteries from your laptop if you are not using them for a long period of time.
  • Keep batteries cool.
DON’T:
  • Discharge batteries further than necessary.
  • Allow batteries to remain discharged for extended periods of time.
  • Constantly leave your laptop plugged into the power grid with the battery sitting at 100%.
  • Rely on a battery meter being perfect.
  • Carry loose batteries without properly insulating them, or place them in checked baggage.
When you store a battery:
  • Store your battery anytime you’re not using it for more than a couple of weeks (if it’s removable).
  • Discharge or charge your battery until it’s about 40-50% full. Do not store your battery fully discharged under any circumstances.
  • Place the battery in a plastic bag to avoid the chance of shorts and keep it dry, and store it in a cool place.
—–
Soren “scorchgeek” Bjornstad
http://www.thetechnicalgeekery.com

 

If you have found an error or notable omission in this tip, please leave a comment or email me: webmaster@thetechnicalgeekery.com.

 

Copyright 2012 Soren Bjornstad.
Verbatim copying and redistribution of part or all of this article
is permitted, provided this notice is preserved.

While I relied on my memory and personal experience in a couple of places, for the most part I drew the information for this tip from Battery University’s website. Some of the pages are highly technical and not interesting to most of us, but I do highly recommend the one about how to prolong the life of lithium-based batteries.

EDIT: (Also, my bad! In my email to the list, I claimed there were 19 myths and facts, when in fact there are only 18. Only a problem if you’re particular like me.)