Excel Tips and Tricks for salary survey data

In this video I demonstrate the most common keyboard shortcuts I use when working with salary survey data files, a really cool and fast technique for converting data from annual to hourly (or vice versa), and a useful formula for combining data from two or more cells into one cell.



For reference, here are the keyboard shortcut combinations that I demonstrate in the video.

Moving around the worksheet:

control and down arrow (move to bottom of a list)
control and up arrow (move to the top of a list)
control and right arrow (move to the right of a table)
control and left arrow (move to the left of a table)

Selecting cells:

shift and control and arrow key (selects a contiguous set of data)
shift and arrow key (expands or contracts a selection of cells)

Copy and paste:

control and c (copy)
control and v (paste)

Converting values:

paste special -> operation -> divide (divide a range of values by the copied value)
paste special -> operation -> multiply (multiply a range of values by a copied value)

Converting formula results to values:

paste special - as Values (takes the results of a formula and pastes it as the value)

Combining data from multiple cells into one cell:

If you have three cells of data that you want to combine into one cell, you can use this formula

= cellref1&" - "&cellref2&" - "&cellref3

In this example, cellref is the cell reference for the cells that you want to combine.
If cellref1 = 1.01 and cellref2 = Administrative Assistant and cellref3 = the job's summary, then the result of the formula will be 1.01 - Administrative Assistant - job summary

DEMO - Version 3 - Search Surveys

We're busy preparing for the 2009 WorldatWork show. There are lots of details to keep track up, including ordering booth giveaways, making sure the booth furniture is ordered, and of course finishing up Version 3 development.

We're making great progress and I had a chance yesterday to make a short demo of the new Search Surveys page in Version 3. I wanted to share the process with you, just in case you wonder how these types of demos are made.

It starts with a general idea of the flow of the demo. I ask myself questions such as:

• What do I want to show?


• What do I not want to show for this demo (trying to keep it short and on point)?


• What do I show first, second, and third, etc.?

I hand write some notes and then I run through the demo, without recording it, a few times. I usually end up practicing the demo between three and five times. Then I'll setup a camera and actually video record myself making the introduction to the video and the closing comments.

Here's my high tech camera stand! I forgot my tripod, so I made do with a box and some books. It worked just fine though.



It was interesting this time since we're in a new office. I had to try a few different locations and angles of the camera. I didn't want too much clutter in the background. Notice that I covered up a few boxes with some artwork, in the background on top of the closed cabinets.



It's little stuff in the video that I'm looking for at this stage.

Once I've found the right angle, I'll actually record the introduction and wrap-up. I may do this a few times to get the wording correct and the framing of the video correct.

Next I'll switch to the screen recording. I'm using a tool called ScreenFlow. It's awesome for screen capturing. It's Mac only, but since I'm running Parallels virtual machine I can screen capture my Windows desktop if needed.

The next step, once the screen capturing is complete, is to add the zoom and pan effects to the captured video. This is a fun exercise and really lets me focus in on the important aspects of the demo. It's also really easy to accomplish with ScreenFlow.

I'll watch the video a few times and I may edit out some segments that seem off point or don't add to the demo. Then I export the movie for importing into iMovie 09.



iMovie 09 is where I combine the live video introduction and wrap-up with the screen capture. I add transitions to the video and titles. This is another fun part of the process because I can be creative with the titling.

Finally I export the finished movie to iTunes in three sizes: mobile, medium and large. The mobile version runs on iPods and iPhones the medium and large versions are used for uploading to the web for embedding in web pages.

The large size video is uploaded to Viddler where it's converted to a Flash video format. I can then embed the video in my web pages for viewing in any web browser.

That's about it. It takes some time, but hopefully the end result is worth the effort. We have many more demos planned and they will all be available on the Help Page in Version 3 under the Videos section and some of them will be available on the Demos page of our public website.

Here's the finished video.