Jan 19, 2009

Excel tips - courtesy of ASAP utilities

ASAP utilities is suite of over 300 excel addons that help you make the most out of Excel. First of all, this suite of addons is free only for non-commercial use, otherwise there's a price tag of $49. Beside the lift of the non-commercial use restriction, the paid version also lets you receive priority support and doesn't force you into updating it twice a year (not that it bothered me that much). Anyway, every now and then I have to do something in Excel that would either obligate me to create a macro, or to do a tedious work - and this is where ASAP Utilities helped me several times, enough to convince me to choose ten Excel tips to share with you. First of all, the suite of addons is available here and once installed it will show in Excel (2003 or 2007) as a new tab: Download ASAP Utilities (~10MB)

Excel tip #1 - Empty duplicate values in a selection

There's not much to explain here, as the title says exactly what this will do. Practically you have to:

  • Select a range of cells you want to check for duplicates
  • Click on the ASAP Utilities tab, go to the Range section and select Empty duplicates in selection
  • You'll get a prompt if "you want to clear the contents of all duplicate values in the selected xx cells" which will process them when you press OK (the first value that is found is kept, other duplicate ones are deleted)

Excel tip #2 - Create a list of clickable links to all sheets in a workbook

It happened to me that I had a workbook with about 20 sheets in it, and I wanted to have a clickable list with all of them on one sheet. Then found this option, that gave me the option to have 20 links on one page that link to each sheet in the workbook. To create such an index in an Excel workbook with multiple sheets:

  • Go to a new sheet where you want the index placed
  • In the ASAP Utilities tab go to Sheets and click on Create an index page with all sheets (clickable)
  • You can choose additional options, such as to order the links alphabetically ascending/descending, keep original order, include links to charts too
  • Once you're ready, click on Create index sheet


Excel tip #3 - Remove all empty rows / Remove all empty columns

This is useful when you have a lot of rows/columns with data in them, but here and there you have an empty row or column. I know it sounds easy to select manually the empty rows/columns and delete them, but imagine if you have 10000 rows and you have to go through all of them to select the empty ones? Not that easy afterall my friend. This is how ASAP Utilities does it ... ASAP:

  • Select the range of rows/columns you want to filter
  • In ASAP Utilities go to Columns & Rows->Remove all empty rows (if it's rows you're removing) or Remove all empty columns
  • You'll get a prompt Are you sure man? and you'll have to click on Sure, there's no sugar OK

Excel tip #4 - Copy cells with formulas and paste in their values

This basically does the same thing you could achieve by selecting a cell containing a formula, copying it, going to Edit-Paste Special, selecting Values and hitting OK, just that it does all of this in one selection and click. To do it:

  • Select the formulas you want to paste in as values
  • In the ASAP Utilities tab go to Numbers and click on Convert formulas to their values
  • Click OK on the prompt

Excel tip #5 - Convert text to uppercase or lowercase

In Microsoft Word there's an option that lets you select a bunch of text and hit Shift+F3 and have that text converted to lowercase or uppercase. In Excel there isn't such an option, but, ASAP Utilities lets you do it by:

  • Selecting the cell that contains text to be upper/lower-cased
  • Going in the ASAP Utilities tab and selecting Text and then the option Convert to UPPERcase or Convert to lowercase
  • Voila

Excel tip #6 - Convert dates to other formats

In America the date has a specific format (month/day/year) in some countries in Europe has another format (day/month/year) so if you have a lot of dates in one of these formats and you want to convert them to the other format, you're in a pickle. Except that ASAP Utilities let you convert dates in a gazillion formats with the push of a click. To do so:

  • Select the cells that have the dates you want converted
  • In ASAP Utilities go to Text and select Convert Dates
  • A new window will show up that lets you input the Current date-format, and lets you select what to Convert to, as well as showing an example of before and after on the right side. Not only that, they will let you input the character separator between the original/converted date. Smoking!
  • Hit OK when you want the actual conversion to start


Excel tip #7 - Extract hyperlinks from hidden links & Activate hyperlinks

Figure this, you copy a table from a webpage where each text in each column actually is a hidden hyperlink to a webpage. Now, you want to extract those hyperlinks in Excel and have them show up so you'll be able to print them. You can do it manually by right clicking each hidden link in excel, and copying/pasting to a new location, but, ASAP Utilities lets you do this in a snap:

  • Select the cells that contain hidden links
  • In ASAP Utilities go to the Web section and select Extract hyperlinks
  • A prompt will show up that lets you select where you want the links to be pasted (the cell itself or the one next to it) as well as if to do this to the selected cells or the entire sheet
  • Click OK to do it


Another option in this same area is to convert a bunch of links (that are not clickable) to clickable ones. To do so just select them and go (in ASAP Utilities) to Web and select Activate hyperlinks.

Excel tip #8 - Merge several text files into a single one and import it in Excel

This is useful too. Think that you have 10 different text files with data in them that you want to Import in Excel. You can do it the old way, meaning you import each text file manually, or you can use an ASAP utilities feature that lets you merge them all in one document and import it in Excel. You can access this feature by:

  • Going to ASAP Utilities and selecting Import and then Merge files together into a new one
  • A new window will show up that lets you select what files to merge (Excel, dBase, Lotus 123, Quattro Pro, CSV, Text files)
  • After you've chosen what file types you want merged and imported select where to Import them in (this or a new workbook) and click the Pick files and merge that will do exactly what it says


Excel tip #9 - Conditional select or delete columns or cells

Another excellent option is this one, as it lets you select rows, columns or cells based on matching options. For instance you can select all cells in a range that contain the word "text" or that contain values equal to one you're entering, as well as many other matching options:

  • Select the cells/columns/rows that you want to apply this option to
  • In ASAP Utilities go to Select and choose Conditional row and column select, hide or delete
  • Choose the matching options you want and hit OK

Excel tip #10 - Transpose cells (can contain data or formulas) to another location

This is useful as it lets you transpose vertical cells to horizontal ones (among other possibilities). To do so:

  • Select the cells you want to transpose
  • In ASAP Utilities go to Sheets and select Range, Transpose data with formulas
  • Select where you want to place the data you're transposing

Last but not least there's an utility that lets you display funny error messages. No productivity value, but good to prank a friend working on an important Excel file.


Jan 18, 2009

Windows 7, seven, Seven, SEVEN!

Ever since they've switched the name from Windows Vienna to Windows 7, I thought of the small tutorial Monica (from Friends) gave Chandler about the 7 erogenous zones on women (wait, there are seven?). Yes, there are 7, like there are 7 wonders, 7 days, and 7 Windows versions. Point is, being a long time Friends fan and having watched it for at least 10 times (the whole show) everytime I'll hear Windows 7 I'll think of the above youtubed video.

Anyway, joke aside, Windows 7 leaves the impression that it will be what Vista never managed to become, a stable and fast operating system. I say this based on the impression that Windows 7 Beta left since it was publicly released for downloading on January 7, 2009 (yet a leaked version was available since December 2008). It boots and shuts down faster than XP or Vista, and has (or the final version will have) several new features:

  • improved taskbar - you can move the icons in the taskbar around, and they'll stay in the order you move them, plus that you can see previews of the minimized windows by hovering over the icons
  • a jump list feature - this is an improved version of the Documents history, where now they're grouped by application and by they're frequent/recent usage
  • easy handling - the windows auto-resize depending on how you drag them, you can make them transparent, you can minimize them all with one click, ...
  • new programs - Internet Explorer 8 and Windows Live Essentials (which basically contains Windows Mail - former Outlook, Windows Calendar, Movie Maker, Photo Gallery) that needs to be downloaded separately
  • HomeGroup - a new networking system, as well as other improvements

So far so good, it's like a 5th service pack for Vista.

If you want to test it you'll have until January 24th to download Windows 7 Beta (build 7000), at least officially, because unofficially there are other ways. Windows 7 download information:

  • Download Windows 7 Beta (official link)
  • Download Windows 7 Beta torrent - unofficial, includes some activation keys Microsoft sends by default to testers (same keys to everyone, so you're not doing anything illegal by downloading it from here)

Ok, but will it bend? I mean will it work on your computer. It will if yours has at least these minimum requirements:

  • 1 GHz 32-bit or 64-bit processor
  • 1 GB of system memory
  • 16 GB of available disk space
  • Support for DirectX 9 graphics with 128 MB memory (to enable the Aero theme)

The official Windows 7 release date is not yet official, but since they've started in 2007 and they've established a 3-year framework, the final release of Windows 7 should be available most likely in 2010.

If you want to have a bit of Windows 7's look on your XP/Vista computer, you can download Windows 7 wallpapers (there's some available but I didn't find them impressive, just do a search and you'll see what I mean) or some Windows 7 themes for XP and Vista:

I'm really not a big fan of this vista/aero/7 look, so I'll pass on it, but if you're wanting to have your system look like Windows 7, above are your solutions. There are a bunch of Windows 7 transformation packs available too, but they're old (at least 6 months) and don't really look like Windows 7 - now that the beta is out a newer/better transformation pack will probably be released too.

Last but not least is the list (nice word game) of Windows 7 compatible applications. We can find such a list on the Microsoft site, NOT. Because not many developers started testing their programs on Windows 7, but as time goes by they will make their applications compatible with Windows 7. Anyway, I thought of gathering, in time, a list of Windows 7 compatible software, so if you find out one such program is working post a comment. Until now, only 3 antivirus programs are officially compatible with Windows 7, but others work "unoficially". Anyway, the list.

Antivirus software compatible with Windows 7:

Anti-spyware software compatible with Windows 7 (their latest versions work):

  • Windows Defender
  • Spybot Search and Destroy
  • Lavasoft Ad-Aware
  • Spyware Blaster
  • CCleaner
  • HijackThis
  • Autoruns

Other programs compatible with Windows 7:

  • Winamp
  • VLC Player
  • K-lite
  • Itunes
  • Winrar
  • Skype
  • Firefox 3
  • Office 2007
  • uTorrent
  • BitTorrent
  • BitLord
  • Adobe Reader
  • Adobe Dreamweaver CS4
  • Adobe Photoshop CS4
  • FileZilla
  • Thunderbird
  • Nero (v9)

Games compatible with Windows 7:

  • Silent Hill 5
  • Call of Duty - World at War
  • Tomb Raider - Underworld
  • Command and Conquer 3
  • Red Alert 3
  • F.E.A.R
  • Crysis Warhead
  • Left 4 Dead
  • Eve Online
  • Call of Duty - Modern Warfare
  • World of Warcraft
  • Brothers in Arms - Hell's Highway
  • FarCry 2

Programs/games that don't work with Windows 7 (or run with problems):

  • Daemon Tools
  • Comodo Firewall
  • Acronis True Image 2009
  • Creative Audigy drivers
  • Grand Theft Auto 4
  • FarCry
  • Bully
  • Final Fantasy 8

I'll keep this posted as I find more compatible programs.

Jan 11, 2009

Golden Globe winners in 2009?

[Update] The Golden Globe awards finished, you can see a list of all the winners here: http://movies.aol.com/golden-globes/nominee-winner (there are also trailers of the winning movies). While I was right on some guesses (Slumdog Millionaire, Sally Hawkins - Happy-Go-Lucky, Heath Ledger, Wall-E, Waltz with Bashir and very close on the Mickey Rourke guess), I must say I never expected to see Kate Winslet win an award. Somehow I was right, as she didn't win an award, she won two.

Today, January 11 - 2009, is a big day for the entertainment industry because it takes place the 66th edition of the Golden Globe Awards. Presented annually by the HFPA (a Hollywood foreign press association), it recognizes achievements in the foreign and US entertainment industry (movie and television) in a tradition that started in 1944 to award Golden Globes to those that are worth it. I think this is as important as the Oscars, and the Golden Globe winners have a good chance of getting Oscars too.

There are still about 6 hours until the Golden Globe winners are announced (on the Golden Globes official website), but until that happens here are the nominees for the most important of the Golden Globe awards, with my guess on who might win the award for each section:

Best Motion Picture - Drama:

  1. The Curious Case Of Benjamin Button - starring Brad Pitt and Cate Blanchett, this is one of the best movies I've seen this year, an adaptation from the 1920s story by F. Scott Fitzgerald. Some say it's a metaphor for the soul, which is born old and it gets young as time passes until you die, but for others (like me) that don't really believe in souls it's just a good movie about a boy that is given a second chance and manages to live his life when almost no one thought he would stand a chance. The curious part about Benjamin is that his body is old when he's born (but his mind is young), and dies when his body is young (but his mind is old). 5 Golden Globe nominations.
  2. Frost/Nixon - Another movie with 5 Golden Globe nominations. For those that don't know about Watergate, it was a series of American political scandals during the presidency of Richard Nixon involving crimes, campaign frauds, espionage/sabotage, illegal break-ins, improper tax audits, illegal wiretapping, slush fund and more. Nixon resigned in 1974 because of this. It's a good revealing movie for those that want a better insight to the Watergate story, though I doubt it will win the Best motion picture.
  3. The Reader - this one has "only" 4 Golden Globe nominations, having as protagonists Kate Winslet and Ralph Fiennes. Ralph tells the story of how he had a summer affair when he was young with Kate, only to find her 8 years later in a trial where she was accused of Nazi war crimes (she was an SS guard) and he uncovers a deep secret... Best quote of the movie is "It doesn't matter what I think, it doesn't matter what I feel, the dead are still dead", but I don't see this movie as the winner.
  4. Revolutionary Road - Based on a novel by Richard Yates, it has as protagonists Kate Winslet (again) and Leonardo DiCaprio. No, it's not Titanic, but it presents a 50s family story where they both want to escape from "the idea that you have to settle down and resign from life".
  5. Slumdog Millionaire - and the winner is... This movie has 4 Golden Globe nominations, but between it and "The curious case of Benjamin Button", I'm pretty sure one will win the Best Motion Picture - Drama. In my opinion this is even better than the Benjamin one, because it shows a reality from the Indian suburbs. Jamal, the main character, is about to win half a million dollars on India's favorite show, "Who wants to be a millionaire". Right before the final question, he is taken by police under suspicion of cheating, and in trying to prove he is not cheating he starts telling the story of his life, and how each answer he's given is actually a part of an adventure that took part in his life. The best part of the movie is until he and his brother grow, because it shows how 2 Indian kids struggle to survive and actually do it very good. I'm not and Indian and don't actually know much about India, but the story is touching.

So my guess for the winner of Golden Globe's Best Motion Picture (drama) is Slumdog Millionaire.

Best Performance by an Actress in a Motion Picture - Drama:

  1. Anne Hathaway for her performance in Rachel Getting Married. It's something about her mouth that makes me love her performance in every movie. The Anne Hathaway from Ella Enchanted is anything a man could ever want, a girl with Anne's mouth and the gift of obedience. In Rachel Getting Married, she's a troubled kid and she's acting quite good.
  2. Angelina Jolie for her performance in Changeling, Clint Eastwood's movie where beside Angelina the great John Malkowich stars too. A story about Angelina's struggle to get back her son that disappeared (movie takes place in 1928), and her fight with the LAPD that brings her back a boy who is actually not her son. It's not the Angelina we've all been used to see, but her acting is good.
  3. Meryl Streep for her performance in Doubt. Meryl Streeps acts brilliant in a movie involving a church school scandal, maybe because her role is that of a conservative nun who confronts the priest being accused of abusing a black children. In my opinion, she's got the best chances of winning this award.
  4. Kristin Scott Thomas for her performance in I've Loved You So Long (Il y a longtemps que je t'aime), a foreign movie (French) that follows the story of two newly found sisters. I didn't enjoy this movie very much, and I don't think Kristin will win.
  5. Kate Winslet for the performance in Revolutionary Road.

I consider the winner will be either Angelina or Meryl, and I incline to believe Meryl Streep will actually go home with the award.

Best Performance by an Actor in a Motion Picture - Drama:

  1. Leonardo DiCaprio for his role in Revolutionary Road. Leonardo acts the same way as in all his movies, nothing extraordinary about it though.
  2. Frank Langella for the role in Frost/Nixon. I don't think he'll win, because it's a movie focused on a series of interviews, so not too much acting.
  3. Sean Penn for his role in Milk, a true story about Harvey Milk. Now, I don't want to comment anything about this movie because I might be considered short-sighted, but seeing two guys kiss really ruins a movie, at least from my point of view. I wish this movie was focused entirely on the political side of Harvey's life, without adding the guy romance. However, it was a tough-role for Sean (he's straight) and a great performance which makes him a favorite.
  4. Brad Pitt for his role in The Curious Case Of Benjamin Button. He acts good, though I don't think it should count as his acting at the beginning, since that seems a kid with a mask, nor at the end of the movie where it's another kid. Still, he acts as discovering things for the first time (much like Joe Black) quite good and he's got his chance in winning this award.
  5. Mickey Rourke for the role from The Wrestler. Don't think this is a movie as Rocky Balboa was, because it's indeed an excellent movie. Between Rourke's performance and Marisa Tomei's one (man she's still a hottie), you've got a movie that's worth every penny and Rourke is my favorite in winning this.

This award should be split between Sean Penn and Mickey Rourke, though my feel is that Sean Penn will win the Golden Globe award for best performance by an actor in a motion picture.

Best Motion Picture - Musical Or Comedy:

  1. Burn After Reading - add John Malkovich, George Clooney and Brad Pitt in a comedy, and you've got a winner.
  2. Happy-Go-Lucky - a British comedy, but (in my opinion) not as good as How to lose friends and alienate people.
  3. In Bruges - Colin Farrell, a hitman hiding in ... guess where.
  4. Mamma Mia! - a mother (Meryl Streep), a daughter and three possible dads. Nice but Meryl acts better in Doubt.
  5. Vicky Cristina Barcelona - an Woody Allen movie I actually enjoyed and I hope it wins the award. And no, I don't wish it wins just because I have a crush on Scarlett Johansson, but because I've had a crush on Penelope Cruz for some time now. Excellent movie.

And the winner is (in my opinion) Vicky Cristina Barcelona, though Burn After Reading I think has pretty much the same chances at winning the best motion picture (musical or comedy) Golden Globe award.

Best Performance by an Actress in a Motion Picture - Musical or Comedy:

  1. Rebecca Hall for her role in Vicky Cristina Barcelona.
  2. Sally Hawkins for her role in Happy-Go-Lucky.
  3. Frances McDormand for her role in Burn After Reading.
  4. Meryl Streep for her role in Mamma Mia!
  5. Emma Thompson for her role in Last Chance Harvey.

Maybe it's because of the British accent, but I think Sally Hawkins has a good chance in winning this. At first I thought why the hell wasn't Penelope Cruz nominated, but after that I've seen that her role is actually a "supporting role".

Best Performance by an Actor in a Motion Picture - Musical Or Comedy:

  1. Javier Bardem – Vicky Cristina Barcelona
  2. Colin Farrell – In Bruges
  3. James FrancoPineapple Express
  4. Brendan Gleeson – In Bruges
  5. Dustin Hoffman – Last Chance Harvey

I have no favorites in this award, I incline a bit towards Javier Bardem but because I liked the movie, not necessarily because I liked his acting.

Best Performance by an Actress In A Supporting Role in a Motion Picture:

  1. Amy Adams – Doubt
  2. Penélope Cruz – Vicky Cristina Barcelona
  3. Viola Davis – Doubt
  4. Marisa Tomei – The Wrestler
  5. Kate Winslet – The Reader

Why oh why should I chose between Penelope Cruz and Marisa Tomei. I like them both and I like the way they acted, and I really hope one of them would get the award. I think Marisa should get it. No, Penelope. Actually Marisa. Well it's too damn hard to choose one.

Best Performance by an Actor In A Supporting Role in a Motion Picture:

  1. Tom CruiseTropic Thunder
  2. Robert Downey Jr. – Tropic Thunder
  3. Ralph FiennesThe Duchess
  4. Philip Seymour Hoffman – Doubt
  5. Heath LedgerThe Dark Knight

Tropic Thunder was an awful movie, but Tom Cruise acted hilariously. Heath Ledger was phenomenal in The Dark Knight, and unfortunately died (in the real life). So on one hand you have Tom who has the bad karma for the scientology thingy, on the other hand you have Heath whose performance was excellent too and is now dead. I really think Heath Ledger will win this award.

Best Animated Feature Film:

  1. Bolt
  2. Kung Fu Panda
  3. Wall-E

The winner is wall-e (again, in my opinion), the way they managed to transpose emotions in a robot is amazing, and the movie is excellent. Wall-E should win in my opinion.

Best Foreign Language Film:

  1. The Baader Meinhof Complex (Germany)
  2. Everlasting Moments (Sweden, Denmark)
  3. Gomorrah (Italy)
  4. I've Loved You So Long (France)
  5. Waltz With Bashir (Israel)

Waltz with Bashir has good chances too, because of the current conflict Israel is involved in now with the Palestinians, but Gomorrah is a tough competitor too. Gomorrah is about the mob in Naples, and as a guy who lived there for 3 months (me) I can tell you it's a pretty scare city and Gomorrah captures that situation quite good.

There are a bunch of other Golden Globe awards too (most of them for television), but I don't have any opinions on those since I barely watched any other tv shows this past year beside Lost and Prison Break. The Golden Globe winners for 2009 will be posted soon here: HFPA - Golden Globe Winners 2009 and I'll see if I guessed correctly.