electronic translators, electrical exam prep, scanners, spy gadgets, dvr, hidden cameras, weather radios
Bookmark and Share
Products • Articles • Book Reviews • Brainpower Newsletter • Contact Us   •   Home • Search

Excel Tips and Secrets

Excel Tip #1
To change the name of a worksheet, double-click on its tab. You can also hit the F2 key if your cursor is over the tab. To prevent errors in formulas that reference sheets, use the underscore symbol instead of a space. For example, 980415_stats would be 1998 April 15 statistics.

Excel Tip #2
If you use Excel for an electronic expense report that tracks YTD expenses weekly, you'll have 53 sheets. Rather than name them 01 through 53, name them Jan1, Jan 2...Feb1, Feb2, etc.  It's hard to correlate week 35 with your calendar, but you can correlate the month easily.

Excel Tip #3
You can format individual characters within a cell. Hit the F2 key, then highlight the character to format (using your cursor). Then, use the FORMAT drop down menu to do what you need to do.

Excel Tip #4
You can format cells so they have wordwrap. You can align their contents vertically. You can shrink them to fit. Explore the options in the FORMAT menu--it'll be worth your while if stale is not what you want.

Excel Tip #5
Can't see everything in your spreadsheet? You can hide columns or rows you don't need (FORMAT "Hide"), you can change the zoom (type in what you want, if the drop down sizes don't cut the mustard with you), or use the WINDOW "FreezePanes" command.

Excel Tip #6
To select noncontiguous cells in a worksheet, hold down the CTRL key and click on the cells you want to select.

Excel Tip #7
To enter a zip code or some other string that begins with a zero, simply type the apostrophe key first.

Excel Tip #8
To enter a series of data (e.g., 1, 2, 3...), find the AutoFill handle and drag on it. This handle is a little square located in the cell's lower right hand corner. Your pointer will turn into a crosshair. Excel will recognize noncontiguous series, also (e.g., 2,4,6,8...).

Excel Tip #9
Don't you hate it when your data are in rows and you want them in columns? Highlight the cells that have your data, then right mouse click. Check the "Transpose" box. Voila!

Excel Tip #10
You can align text in just about any orientation you want. You can rotate it, make it run vertically, angle it right, angle it left, you name it. Use the FORMAT Cells command (the drop down menu or the right mouse button) and explore the options Excel gives you..

Excel Tip #11
Excel does some neat things with formatting, as you can see from Tip #10. In addition to static formatting, Excel also does dynamic formatting. What this means is you can, for example, set conditions for a background color to change when your formula returns a value outside a predetermined range. Highlight a cell (for example, one that stores the sum of a column of prices). Select FORMAT, Conditional Formatting. Choose your conditions. Then set the cell to change borders, background colors, fonts, whatever.

Excel Tip #12
A new editing feature helps you handle those formerly painful formulas. When you first enter a cell, you may see that the cell references in your formula are different colors. The colors of the references  correspond to the border colors of the cells to which they point. This may be marginally helpful, but once you set colors up with specific meanings, you'll be golden.

Excel Tip #13
Use the fill handle. Experiment with the location of your pointer on the edges and corners of cells. Click and drag on ranges of cells, and see what happens. You'll find a ton of timesavings, here.

Excel Tip #14
An even better way to make sense of formulas is to use heading names instead of grid references. Say cell A1 is "Interest" and B1 is "Payment." Under the old way, you'd enter this formula in C1: "=A1+A2".  Easy enough, until you start adding in a dozen cells, or so. Under the new system, you'd enter this formula in C1: "=Interest+Payment ". What is cool about this is that if you change the cell headings, the formulas will automatically rename their references. So, if you shorten "Payment" to "PMT" you won't hose your formulas. Is this way cool, or what? Now, when you go to make a mortgage table or some other table where you need to copy the same formula over and over, just move your pointer over the formula cell's lower right corner until you see a +.  Drag down as far as you need, and the formula will self-replicate, keeping references intact.

Excel Tip #15
You can also name ranges. See Excel's help menu about this trick. It's a way of grouping cells together, and it is really slick. For a complex spreadsheet that does "what if" modeling, this is indispensable. But be careful--Excel's interface on this is confusing and it's easy to mess things up. Just remember, though, you can use "natural language" range names, such as "January's rent." We purists don't do this. We never leave an empty space in a formula or use punctuation in a formula name. We would say, "rent_jan"--all lower case, with no spaces, and the more critical word first.


MY BEST EXCEL TIP:

Buy a book on Excel or Microsoft Office. You can find books and tapes by clicking on the software link at the top of this page. This tip is not a cheap sales trick. The reason I suggest you buy a book is I could list at least 1500 Excel tips and the Internet is just is not the format for that. The capabilities of this program go far beyond what those unfamiliar with it could dream of. It is truly a masterpiece of a program.
 

Computer Resource Quicklinks

Working the Windows Desktop

The whole desktop approach ignores the fact that a computer's hard drive(s) are the electronic version of a paper filing cabinet. It also ignores the fact that people store a huge amount of files in that system. And it ignores a few dozen other facts relevant to using a computer. It's just a bad approach.

The desktop assumes you don't care what files you actually work on. It opens apps, not files, and this is the pathway to problems. You can inadvertently be revising the wrong thing, if you can even find it in the first place.

What you should do instead is use Windows Explorer. Microsoft tends to hide this, but it should be your standard interface with your computer, unless you don't mind working blind.

You can always right mouse click the Start button to invoke it, but you should add Windows Explorer to your Quick Launch bar and several other menus in Windows.

The default settings for Windows Explorer defy logic. Change these so you can actually see what files you are looking at. Enable it to show you the file extensions (unclick the insane "Hide extensions" box that is, stupidly enough, checked by default though actually there is never any reason to ever check this box). Select the option to show details. Now, you will be able to see your file size, file date, and other useful information. If you right click around a bit, you can find quite a bit of functionality in Windows Explorer.

If you haven't been using this interface previously, make a point of using it now. If you always open files from within Windows Explorer, you will always be able to see all available files and select the right one.

Use Windows Explorer to set up your filing system as if it's a paper filing cabinet. Save all files either on the data drive (dual hard drive machine) or in a folder on a single hard drive machine. Do NOT save files to the default locations. These never make any sense. They are typically within your applications, which is a dumb place to save them. That's how you end up with corrupted data files and it also makes file backups difficult.

For single-drive users, an easy solution is to create a folder called 0files as your top-level data folder. The zero means it will show up at the top of your file list, making things easy for you. Below this folder, create you filing structure. Never store anything at the root of this folder. Think of it as the shell of a five-drawer filing cabinet and don't toss stuff in the bottom. Always put files in folders that are in drawers.

With a good filing structure in place, you will always be able to find your files by simply clicking right to them. So think this out as you go and follow a good taxonomy. It's a much more effective way to work than how the zombies at Microsoft envision people working.

 

Recovering hard drive space

Even with today's huge drives, people sometimes run out of hard drive space. The steps below can recover wasted space.

  • Do a search for *.tmp files. Delete all of them. Then defrag your drive.
  • Do a search for *.bak files. Delete all of them. Then defrag your drive.
    •
  • If you have any *.bmp files, change the format to *.jpg. This will result in radically smaller files. You need an image tool for this; if you don't have one installed already, then skip this step.
  • Set the Properties for the drive to compress files. In Windows Explorer, right mouse click the drive name. Then select Properties. Then select Compress to Save Drive Space. This could take a while, so unless you want your machine tied up for a few hours do this process one folder at a time and then do it for the whole drive.
  • If this issue is for your programs drive, then remove any programs you aren't using. Go to Control Panel | Remove Programs.
  • On your data drive, zip files you aren't using.

There are other steps you can take, but if, at this point, you are still low on disk space you really need to add another hard drive or upgrade one that's in your system. Or, another very cool option, is to add a My Ditto system. See our Review of the Dane my Ditto network server.

   

PC Mag has a nice collection of Excel tips: http://www.pcmag.com/category2/0,4148,2143,00.asp

Articles | Book Reviews | Free eNL | Products

Contact Us | Home

This material, copyright Mindconnection. Don't make all of your communication electronic. Hug somebody!