Five Excel Tips & Techniques

I Didn’t Know I Could Do That! Five Excel Tips & Techniques

July 12, 2011

Authored by Alana Smith, TeamCain

Steve Adair, a Microsoft® Excel Expert from our partner, Global Software, made it look so easy. By “it” I mean using simple techniques to make working in Excel a lot easier. I sat in on a webinar that he presented with TeamCain, where he showed five simple techniques to those who use Excel on a daily basis. As I was sitting there taking my notes all I could think about was “I can do that? I can use my curser like that for fast data entry?

It wasn’t until I tried it out for myself that I realized using these five techniques can make working with Excel a whole lot easier.

Tip Number One: Sort by Month & DaySorting by month and day for employee birthdays
I’m not sure when I would need to use this technique but Steve gave a great example and it’s really beneficial for those who work in Human Resources. Let’s say you had a data table with a date field that you want to sort by month, then by day regardless of the year – like a personnel list by month and day of the employee’s birthdates. All you do is add a column beside the birthday and insert a formula. Assuming the first birthday is in cell D4, the formula would look like this: =MONTH(D4)*100+DAY(D4). Next all you have to do is double-click fill handle to fill to end-row of adjacent column and then it’s just a matter of sorting the column from A to Z and then custom formatting the cells so they look like 00-00.

Tip Number Two: Copy/Paste Visible Cells Only
If you had a spreadsheet that is summarized with subtotal lines and you want to copy only the summary to another worksheet, you just look at the summarized worksheet, copy the range and paste it into another worksheet, right? Wrong! If you did that you would get all of the detail rows.

This is what happens when you copy and then paste in a new workbook...

Instead these are the steps you should take. First, prepare the worksheet so that only the cells you want are displayed (the subtotal lines only). Select the range of cells to copy and paste. Press F5 display the “Go To” dialog box and then click the Special button. After that, click Visible Cells Only. Copy to the clipboard and paste into the desired location. Voila! Now you get only your summarized data in a new workbook and you don’t have to worry about all the “in-between” data.

An example of how you can Copy/Paste visible cells only

Tip Number Three: Fast Data Entry

Excel’s Fill List feature, located in the top right corner under editing (at least it is in Excel 2007), is very handy in eliminating manual data entry. I had discovered the Fill List feature for the first time last year when I was filling the word “Yes” in a spreadsheet. Before that, I went through every cell and typed Y-E-S. Steve has once again showed me a way to save time in Excel by displaying how one can just drag “fill handle” to fill in the spreadsheet. I found that a visual overview of this technique best displays how it can save you time.

Let’s say we are a cat lady who wanted to take an inventory of her cats, all of whom are conveniently black cats. Instead of typing the word “black” under the colouring column header multiple times, or highlighting the cell that the word “black” is in and then clicking on the Fill List Down feature, we can drag the fill handle.

Fast data entry for cat inventoryFast data entry for cat inventoryFast data entry for cat inventory

You simply click on the cell and place your mouse curser to the bottom right of the cell. While holding the left mouse button, you simply drag down to where you want to go. When you let go of the left mouse button, the information is automatically filled in (and notice how the curser changed as well?).
Fast data entry - fill days
You can also drag and use the right mouse button to fill in days, weekdays, months, and even years. Steve uses the example of filling in dates over a certain period of time and it is well worth a watch to see what he does and how Excel automatically knows that you want to increment by a certain number (see the images of the cat ages increasing by 2).

Fast data entry for cat inventory ageFast data entry for cat inventory ageFast data entry for cat inventory age

Tip Number Four: Quickly Copy/Paste Cells

If you are like me, you rely on the old tried and true way of copying and pasting – you select the range you want to copy, click copy, go to your new page, and click paste. However, there is a quicker way of doing this with keyboard shortcuts and I’m not talking about Ctrl-C and Ctrl-V. I’m talking about Ctrl-D and Ctrl-R. In the webinar, Steve shows the audience how by using these shortcuts as opposed to the old way of doing things, you can quickly build your reports and carry on with your job. The example is using a report with one company that has multiple accounts. Simply by highlighting the first row of data and the area you want to fill, then using Ctrl-D, the top row in the range is copied down to the bottom of the range. All you have to do after that is change the account number (see images).

Quickly copy and paste cells - down

The same can be applied to if you wanted to take the data and expand it over different periods only this time you are using Ctrl-R and you just have to update the periods (or use your handy new trick of dragging the fill handle with your right mouse button clicked so you can fill in the series).

Quickly copy and paste cells - to the right

Tip Number Five: Use Excel Tables

Since seeing this last tip, every time that I am working in Excel, I am using it. Tables were introduced into Excel in 2007 and they are very simple to add to any Excel document. All you have to do is click on the left column header of your “table”, go to Insert and then Tables. That’s it. Now your range of data is now a table. If you click on any cell in the table, you gain access to the Table Tools contextual tab on the Ribbon where you can change the colour of your table, total rows, and you can always convert back to a range (say if you had to send your table to somebody using an older version of Excel). Tables are a great way to sort data or filter your table to show only specific rows. You can quickly eliminate duplicates in your data when using tables and tables support calculated columns so a single formula in a column is automatically produced to all cells in the column.

If you are interested in automating the process of building reports (using the tips that Steve suggested), you can always check out Spreadsheet Server, which lets you build the report once in Excel and then whenever you need to access it or look at a new period of data, it’s there for your exploration.

I’ve tried to give you an overview of the webinar and the techniques that Steve mentioned but you really have to see to believe with these tips, especially with all the neat short cuts that Steve mentions. You can check out the webinar recording here.