SmartSite Technology Header

Tech Tips      

As we enter the Dog Days of summer, this month's installment of the "Cool" newsletter summer series focuses on MS Excel. I hope that you find these tips as useful as the MS Word tips in last month's newsletter. Thanks to all of you who let me know that you found the information helpful!!
 
Since this is vacation time, many of us will be traveling in the next few weeks. This month's Tech Tutor column takes a look at some of the things you should do to prepare to take your laptop on the road.

I hope you get to enjoy some lazy summer days...stay "cool" !!
RoadMap: "Cool" MS Excel Tips and Tricks
Roadmap
 
1--Select All with one click
There is a very easy way to select an entire worksheet. Click on the little gray box in the top left-hand corner of the sheet above the row numbers and to the left of the column letters. You do not have to be at the beginning of the worksheet for this to work...any time you click this box, you will select the entire sheet. You can also select your entire worksheet by clicking [Ctrl]A or by going to Edit|Select All on the menu.

2--One click data mining using AutoFilter
Go to Data|Filter|AutoFilter. When you click on AutoFilter, Excel will add drop down arrows to the first cell in each column of data in your work sheet. When you click on any of those arrows, Excel will show a list of all unique entries in that column. Select one of the unique entries to limit the display of records. When you do this, the drop down arrow will change from black to blue to remind you that your list has been filtered by a selection from that column. You can filter by multiple columns and Excel will display only those rows which match your selections from each column. To turn AutoFilter off, either click on blue arrow over each of the columns where you made the selection, and click (ALL), or go to Data|Filter|AutoFilter and click on AutoFilter again.

3--Generate a unique list of entries in a column that can be used for Copy/Paste
Click on the column letter to select the entire column (you need to include the column header) that contains your data. Paste the data in a new work sheet. Make sure that the data is still selected. Go to Data|Filter|Advanced Filter. By default Excel will suggest that you filter the data in place, which will work, but it is often preferable to put the new list into another column so that you can compare the lists side-by-side. Select the "Copy to another location" option, select the "Unique records only" checkbox, and type "C1" (or whatever column you want to paste the new list into) in the "Copy To" field. You now have a unique list of values for the column to use as you wish.

4--Transpose data from a row to a column (or vice-versa) without rekeying
After working with a table, you decide that you would rather have the current set of row labels running across the columns...don't rekey the data. You can easily make this change using Paste Special. Select the table data that you want to transpose. Go to Edit|Copy, then click on a new location in your work sheet. Go to Edit|Paste Special and select the Transpose check box. Click OK. Excel will place a transposed copy of your data at the new location.

5--[Ctrl] key magic
The [Ctrl] key makes short work of a number of tasks...
  • Navigate quickly. When you press [Ctrl] and any arrow key, you jump to the last populated cell in that direction. This is much easier than scrolling through the workbook, using page down or tabbing. Also, if you hold down the Shift key with any [Ctrl]Arrow combination you select all of the cells between where you are and where you jump with the control key.
  • Make non-contiguous selections. If you need to select non-contiguous cells, hold down the [Ctrl] key while you are making the selections. As long as you hold down the [Ctrl] key, you can click and select as many cells or groups of cells as you want.
  • Fast Data Entry. If you need to put the same data in multiple cells, select all of the cells you need to populate, type the data you want to put into the cells but do not hit enter. Instead hit [Ctrl]Enter and Excel will put the data you typed into all of the selected cells.
6--Keeping row and/or column labels in view when browsing through a worksheet
Use Freeze Panes to select specific rows and columns which will be locked in place to remain visible when you are scrolling. To lock rows, select the row below where you want the split to occur. To lock columns, select the column to the right of where you want the split to occur. To lock both rows and columns, select the cell below and to the right of where you want the split to occur. Then go to Window|Freeze Panes. Click on Freeze Panes. To undo this operation, go to Windows|Unfreeze Panes and click on Unfreeze Panes.

7--Print worksheet row and/or column labels on every page

To print column headings, click anywhere on the sheet and go to File|Page SetUp. Click the Sheet tab. Under "Print Titles" click the little icon at the end of the "Rows to Repeat at Top" text box. Select a cell in the title row. The range $1:$1 appears in the text box. Click on the little icon at the end of the text box. Select Print. When you click Print, row 1 will be printed as the horizontal title row on each page. To print row labels, follow this same procedure but use the "Columns to Repeat at Left" text box.

8--Split one field into two fields or get rid of unneeded data in a field
When importing data, you may have a data field that contains more data than you need. You may need to either split the data into two fields or to simply discard the extra data. To split the field, add a blank column next to the column with the extra data. Highlight the column with the extra data. Go to Data|Text to Columns and a dialog box will pop up. In the Original Data Type box, select either Delimited or Fixed Width. Click Next. Either select the delimiter or set the field widths. Click Next. Select the Column Data Format for each column you are creating. Click Finish and the data will be split into separate columns. If you simply want to discard some of the data in your field, you do not need to create the new column, and instead of selecting a Column Data format for this extra data, just select "Do not import column (skip)" instead of a data format. Click Finish and the extra data will be gone.

These MS Excel Tips and Tricks were tested in Excel 2003. If you find these "cool" tips useful and would like more information about using MS Excel more effectively, SmartSite Technology can provide training documents or a live class for your employees. For additional information, contact us.
SmartSite Technology serves as a virtual IT Department for small and medium sized businesses and non-profit organizations.  We provide all of the information technology services that a business needs to compete in today's marketplace.
 
Our goal at SmartSite Technology is to help each of our clients to be a success!! We know that technology is not a core competency for many small business owners and non-profit professionals. We believe that every organization benefits when they automate their business processes. We work closely with all of our clients, even those who are uncomfortable with computers and technology, to help them introduce automated processes into their operations. 
 
Contact us today to discuss your technology needs.
Sincerely,
 
Dixie Groutt
SmartSite Technology
 
Copyright 2009 SmartSite Technology. All rights reserved.
July 2009
In This Issue
RoadMap: "Cool" MS Excel Tips and Tricks
Your Tech Tutor Says...
 
Have Laptop,
Will Travel
tutor pic number 1
 
You are ready for vacation and trying to take care of the loose ends before you leave the office. We have all experienced the chaos that ensues during the last couple of days prior to vacation. You surely don't need any additional to-dos! However, if you are taking your laptop with you, there are a couple of things that you need to take care of before you go.

  • Backup the data on your laptop.
  • Make sure that your virus software is up-to-date.
  • Make sure that the security updates for your operating system are up-to-date.
  • Check your modem even if you usually connect via Wi-Fi.
  • Make sure that you pack a phone connector so that you can use the modem if necessary.
  • Test your connections to company email.
  • Test your connections to the corporate network.
  • Don't forget to take a mouse--you will be very glad to have it if you have to work on the laptop for any length of time.
This is by no means an all-inclusive list, but taking these few steps will make your remote computing safer and more comfortable.

Have a wonderful vacation!!
Quick Links
 
 
Join Our Mailing List
Safe Unsubscribe
This email was sent to dgroutt@gmail.com by dixie@smartsitetech.com.
SmartSite Technology LLC | 2265 Vistamont Drive | Decatur | GA | 30033