Tag Archives: excel spreadsheet

Fixing Excel when it stops scrolling

Sometimes I track system health for huge networks with huge Excel spreadsheets. And every once in a while, I run into problems with the Excel spreadsheet not scrolling. I move the cursor, and the cursor moves, but the sheet doesn’t, so I can’t see.
The problem is freeze panes. I always have the top row frozen, since I’m typically trying to track 14 or 15 things and the rows tend to blend together if I don’t freeze my header row. But sometimes Excel gets confused and freezes more than I initially told it to freeze–like, an entire screen’s worth of data. The solution is to unfreeze everything, then freeze the top row again. Then Excel behaves again.

What to do when the layoff comes

An IT pro I went to high school with–he was a year or two ahead of me, so we weren’t quite classmates–got a layoff letter this past week, along with the rest of his department. It was a large, successful company making purely a financial decision to offshore a bunch of jobs, and unfortunately he got caught in the crossfire. It reminded me that I’ve been meaning to write about this for a while, so now’s as good of a time as any.

The details about his layoff and my layoffs are unimportant. What’s more important is what to do next. There are definitely things I know now that I wish I’d known years ago, so I’ll share them now.

Continue reading What to do when the layoff comes

What to do when your Excel worksheet won’t scroll

I have a monster Excel spreadsheet with tens of thousands of rows, correlated. Its gigabytes of data taught me a lot. Including things it wasn’t supposed to, like what to do when Excel won’t scroll.

This thing is pretty fragile. Among other things, the largest of the sheets will stop scrolling. The scrollbar on the right scrolls, but the display doesn’t move. I can’t scroll down, I can’t scroll right, or do anything useful with the data.

But I stumbled on a quick solution.

Excel won't scroll
When Excel won’t scroll, the solution is hiding under this button on the ribbon.

For some reason, Excel randomly freezes the panes on these worksheets from time to time. So, when I click on a tab and the screen won’t scroll, the solution is to click on View, select Freeze Panes, and select Unfreeze Panes. Now you’ll be able to scroll in Excel again, like magic.

Then, since I want the top row frozen, I scroll to the top, click on the top row, click Freeze Panes again, and select Freeze Top Row.

For the record, I don’t think avoiding use of freeze panes really prevents this problem. It’s a useful feature; it’s just that sometimes it gets enabled with goofy settings that cause a problem. Once you know the workaround, it’s still annoying but not a terribly big deal.

If you have the same problem, hopefully this solves it for you.

If this tip helped you, I have a collection of a few dozen more Office tips and fixes I’ve collected over the years here.

One in five Macs has malware–but read the fine print

Sophos claimed today that 20% of the people who’ve installed their free Mac antivirus has malware.  That’s not altogether surprising, but it’s also not nearly as big of a problem as it sounds.

One in 36 systems has Mac malware, which means the Mac has an infection that could actually be harming the system itself. That number is low but believable. In my experience, the people who seek out antivirus software are usually the ones who need it the least.
Continue reading One in five Macs has malware–but read the fine print

How to make persistent headers in Excel

My boss and I are compiling a huge Excel spreadsheet that summarizes everything our organization has ever done. It’s as big of a pain as it sounds. What makes it worse is having to scroll all the way back to the beginning to view the headers. The solution: make persistent headers in Excel.

The trick to making a persistent header that shows all the time, even after scrolling, hides in the View tab in Excel 2007.
Continue reading How to make persistent headers in Excel

Buying a new TV that won’t kill your electric bill

As television technology improves, they become more energy efficient. Generally speaking, at least. The CRT TV ranging in size from 26-32 inches that was common in living rooms for most of my lifetime used around 130 watts. But some of the monster TVs people are buying these days use more power than the fridge. Continue reading Buying a new TV that won’t kill your electric bill

Mac “superiority” and cheap PC hardware sources

Dave flying solo. Sorry about not getting the post up there yesterday. So here’s two days’ worth, divvied up however I want.
Inherent Mac superiority… or something. When Steve Jobs unveiled the new dual G4s, he loaded up Photoshop 5.5 on a 1 GHz P3, a 500 MHz G4, and a dual G4 and applied a filter. The 500 MHz G4 finished faster than the P3, and the dual G4 finished in less than half the time. The dual G4 is faster than a 2 GHz P3, not that you can buy one, Jobs boasted.

OK Steve, let’s try a real-world test here. What’s more common, Photoshop or MS Office? The rest of us use Office more frequently. So let’s rumble. The objective: A 700-record mail merge, using Excel and Word. The contenders: A 350 MHz G3 with 192 MB RAM, a 266 MHz G3 with 256 MB RAM, and a 333 MHz P2 with 64 MB RAM.

On record #596, both Macs abort with out of memory errors, even if I crank up the amount of memory both apps can have beyond 32 MB. (Macs don’t have dynamic memory allocation.) Time elapsed: 5 minutes on the 350, 6 minutes on the 266. (I tried it on the second Mac in case there was something wrong with the first one.)

The PC zips through the job in 30 seconds without errors. (Oh yeah, and I had two Internet Exploiter windows in the background, and an extra Excel spreadsheet loaded, mostly because I was too lazy to close everything extraneous down on the PC in order to make the test fair.)

So I guess by Jobs’ logic, my 333 MHz P2, which isn’t even made anymore, is faster than a dual 2 GHz G4, not that you can buy one…

Not that I’m a Microsoft zealot by any stretch of the imagination, but I just found this amusing. It turned out the fastest and most reliable way for one of my Mac users at work to do a mail merge on her Mac is to save the Excel “database” to an NT share, then go log onto a neighbor’s NT box to do the job.

A place for potential bargains. I found a source for surplus computer gear that pretty consistently has good deals advertised at www.softwareandstuff.com. Caveat emptor: I haven’t ordered anything from them myself yet, and they have a 5.1 rating on resellerratings.com but only four evaluations. Given that, I’d say they’re somewhat promising but I’m not going to explicitly recommend either for or against them based on just that.

A sampler: They have an Athlon 550 (Slot A, non-Thunderbird) on a Soyo KT133 mobo for $150. Soyo’s not my motherboard maker of choice but that’s not a bad deal for an inexpensive system with some kick. IDE CD-ROM drives are in the $25 range. WinChip 200 CPUs (outstanding for upgrading Socket 5 systems cheaply–the 1.5X multiplier becomes a 4X multiplier with the WinChip, so a Winnie-200 is a drop-in instant replacement for a P75, and you can get it running at 200 MHz on a 66 MHz system bus if your board supports a 3x multiplier) are $30. Plextor 12X/20X CD-ROM drives are $60. If you want a cheap speed boost for your Win9x box, Fix-It Utilities 99 is $10. Norton Utilities 2000 is $20. Nuts & Bolts Platinum is $15.

Certainly an intriguing vendor. That Athlon bundle has me thinking, and that Winnie would be nice in my P120.

From: Robert Bruce Thompson

Yep. I understand that even a lot of younger hams have never built anything. That’s sad. And probably not good news.

—–

Agreed. But I don’t know what anyone can really do about it.

Binary file editing and hardware compatibility

Binary file editing. I’ve recovered many a student’s term paper from munged disks over the years using Norton Disk Edit, from the Norton Utilities (making myself a hero many times). Usually I can only recover the plain text, but that’s a lot better than nothing. Rebuilding an Excel spreadsheet or a QuarkXPress document is much harder–you have to know the file formats, which I don’t.
But at any rate, I’ve on a number of occasions had to run NDE to recover meeting minutes or other documents at work. The sheer number of times I have to do this made me adamantly opposed to widespread use of NTFS at work. Sure, the extra security and other features is nice, but try telling that to an irate user who just lost the day’s work for some reason. The “technical superiority” argument doesn’t hold any water there.

Enter WinHex (www.winhex.com). Now it doesn’t matter so much that the powers that be at work didn’t listen to my arguments. 🙂 (NDE from vanilla DOS would still be safer, since the disk will be in suspended state, but I guess you could yank the drive and put it in another PC for editing.)

For those who’ve never done this before, you can recover data using a brute force method of searching for known text strings that appeared in the file. For example, I once worked on recovering a thesis that contained the line “I walk through a valley of hands.” Chances are, if I search for that, I’m gonna find the rest of the document in close proximity. A Windows-based editor makes this kind of data recovery very nice–search for the string, keeping Notepad open, then copy and paste the strings as you find them.

Knowledge of the underlying filesystem (FAT or NTFS) is helpful but not essential, as is knowledge of the file format involved. If worse comes to worse, you can recover the strings out of the file and have the app open to re-enter it (being aware that you run the risk of overwriting the data, of course).

I found some useful links on the WinHex site detailing certain file formats.

This is a program I suspect I’ll be buying soon, since my need for it is probably more a matter of when rather than if.

———-

From: “James Cooley”

Subject: Tip for tat?

Hi Dave,

I waded through all your views (That’s where all those hits came from!) and I like your style and learned a great deal. Here’s another tip I didn’t see mentioned: in autoexec.bat, add the following: set temp=C:\temp set tmp=C:\temp set tmpdir=C:\temp

You could use the ramdisk drive you mention, of course. I don’t know if this speeds things up, but it sure helps minimize the clutter from most installs when you clean the temp directory periodically. I use C:\temp2 for those disposable downloads because some programs hate extracting into their own directory. Norton Anti-Virus comes to mind: if you run the updates from C:\temp it hangs.

I ordered _UNIX in a Nutshell_ from a recommendation on your site, but got a 500 page tome instead of the 92 pages you mentioned. If you recall the O’Rielly book I’m talking about, could you give me the exact name so I needn’t hunt it down again?

Hope your hands are healing.

Regards,

Jim

———-

Thanks. I’m glad you enjoyed it (but isn’t that an awful lot of reading?)

I’ve seen the tmpdir trick; fortunately not a whole lot of programs use it anymore but that is useful. Thanks.

And yes, as you observe it’s a good idea to use a separate dir for program installs. I try to avoid hanging it directly off the root for speed considerations (a clean root dir is a fast root dir)–I usually stick it on the Windows desktop out of laziness. That’s not the best place for it either, but it’s convenient to get to.

The 92-page book is Learning the Unix Operating System, by Jerry Peek and others. It’s about $12. The 500-page Unix in a Nutshell is useful, but more as a reference. I’ve read it almost cover-to-cover, but I really don’t like to read the big Nutshell books that way. Information overload, you know?

———-

From: “al wynn”

Subject: MAX screen resolution for Win95/98/2000

Do you know the MAXIMUM screen resolutions for Win95/98/2000 (in pixels) ? Which operating systems can support a dual-monitors setting ?

NEC 15′ MultiSync CRT monitors max out at (1280 x 1024 @ 66Hz); for 17′ CRT’s, it’s usually (1600 x 1200 @76Hz). Do you know any 15′ and 17′ models that can handle denser resolutions ? (like (1792 x 1344 @68Hz) or (1920 x 1440 @73Hz) ?

Also, which Manufacturer/Model do you prefer for flat-panel LCD’s ? Which 15′ or 17′ LCD models boast the highest resolution ?

———-

I believe Windows’ limit is determined by the video drivers. So, if a video card ships someday that supports some obnoxious resolution like 3072×2560, Windows should support it. That’s been the case in the past, usually (and not just with the Windows platform–it holds true for other systems as well).

Windows 98 and 2000 support dual monitors.

I’ve never seen a 15″ monitor that does more than 1280×1024, and never seen a 17″ that does more than 1600×1200. I find anything higher than 1024×768 on a 15″ monitor and higher than 1152×864 on a 17″ strains my eyes after a full day of staring at it.

As for flat-panels, I don’t own one so I can’t speak authoritatively. I’d probably buy an NEC or a Mitsubishi if I were going to get one. The price difference between an off-brand flat-panel and a big name is small enough (relative to price) and the price high enough that I’d want to go with someone I know knows how to make quality stuff–I’m not gonna pay $800-900 for something only to have it break after two years. I’m totally sold on NEC, since I bought a used NEC Multisync II monitor in 1990 that was built in 1988. It finally died this year.

A 15″ flat-panel typically does 1024×768, while a 17″ does 1280×1024.