Monthly Archives: March 2016

Productivity tip: Excel tables!

I was recently asked to give a 5-minute presentation on any Excel topic for a “Lunch and Learn” session.  Five minutes?!?!?  That is not much time.  And besides, the Internet is chock-a-block full of Excel productivity tips, so how could I narrow it down to just one tip?  Also, I knew very little about the audience except to assume they were professionals who were not daily users of Excel.  They’re not beginners, but they only use Excel occasionally.

I decided the one feature in Excel that most people could be using but aren’t, is Excel tables.  Here’s why you’ll want to use this fButton_FormatTableeature:

  • It is so simple to implement. Just hit Ctrl-T from the keyboard.  (Or use the “Format as Table” button from the Excel ribbon.)
  • Automatically sizes itself to accommodate existing rows and columns (right up to the first fully blank row and blank column).
  • Automatically expands to accommodate a new row or column when you type in cells adjacent to the existing table.
  • Automatically formats your list of data using alternately shaded rows, and a contrasting header row, for easy reading and a pleasant look. (If you don’t like the shading, you can use the Ribbon to change to a different Table Style.)Excel_tables_banded_rows
  • Automatically includes Auto-Filter features (shown as little triangles in each column header).
  • Automatically uses meaningful cell reference names in formulas. Instead of the old cryptic A2 – B2 notation, your formulas will look like [EndTime]-[StartTime].
  • Automatically assigns a range name to the table (you can either accept the default name or change it). The more you use formulas, the more you’ll appreciate range names!
  • … and many more benefits too numerous to list here.

Pretty much the only time I don’t use Excel tables is for information that is not in a list format.  In other words, if it is not in a row-and-column group of related information, for example, some financial reports that use multiple subtotal rows.

Here are a couple of really good (and short!) introductions to how to use Excel tables:
Want to become a Data God? Learn Excel Data Tables
http://chandoo.org/wp/2009/09/10/data-tables/

Or if you prefer video, here’s 8.5 minutes by Chandoo:
Introduction to Excel Tables
http://chandoo.org/wp/excel-tutorial/using-tables/

And a very thorough, concise intro over at Contextures (also includes some video tutorials):
Creating an Excel Table
http://www.contextures.com/xlExcelTable01.html

No Recycle Bin on Windows Server?

Q.  A client asked: If I’m on my local computer, and I delete a document from the server, the document disappears from the folder, but I don’t know where it goes.  It’s not in the Recycle Bin on the server or the one in my computer. Why not?

A.  The way Windows works, only local files get moved to the Recycle Bin. In other words, only files deleted by a user who is on the same computer where the files live. This makes sense if you figure that network drives would normally be getting backed up, so if you accidentally deleted a file from a network drive, you (or your IT support person) would be able to restore it from the latest backup.

Keep in mind that the way “delete” works, is that it marks the space the file was using as being available for other use.  So if you accidentally delete a file from a server, you could try these steps:

  1. Stop working!
  2. Yell to your co-workers to stop working on the server.
  3. Log on to the server and using an “undelete” utility to retrieve the deleted file. There are several simple undelete utilities (available for downloading for free from the Internet) that can recover files this way.

However, the above method is a risky way to run a business.  A more pro-active measure would be to obtain third-party software and install it on any computers with shared drives:

You can use a recycle bin replacement, such as Undelete  by Diskeeper Corporation, to get the file recovery support for shared drives and folders. These third-party programs are all-in-one deletion recovery solutions. Anything that’s deleted from a computer loaded with the program will be moved to the new recycle bin. This even included files deleted by remote network users or files you delete from flash and removable storage devices.
Excerpted fromhttp://www.webopedia.com/DidYouKnow/Computer_Science/network_file_recovery.asp

Hope this helps, or at least illuminates the issue.