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

Survey Loading Process

It's getting to be that time of year when many survey publishers release the results from their fall and winter data collection.

Here's a diagram of the overall process of data loading in NextComp. I've labeled the various steps with the primary area of responsibility in a typical setup. This varies by organization as some subscribers do all of their data loading without involvement from NextComp.

You can also download a PDF version of this file.


Click to view larger.

You may also be interested in these documents:

• Aging and factoring methodology - version 3 (download PDF)
• Compensation glossary (download PDF)

User's Group Meeting, Market Pricing, Salary Planning and Twittering

It's been a busy week and I'm having difficulty believing it's Friday already. The week started off kind of slow with the kids home from school and then the excitement built as Tuesday morning approached. Wow! What an incredible day for America. I had to work that day, but I caught up on all the events later that night on cspan. It was also fun reading all the tweets and blog posts from around the world. So many people were excited for the new President to take office. I'm also very pleased that my kids were all able to watch the inauguration at school. They watched it online projected on the screens in their classes. Very cool! I know that Tuesday will be a day I remember for a long time to come.

Closer to home, it's been a busy week here at NextComp. Here's a summary of what we've been up to this week.

User's Group Meeting

I've been quietly planning a user's group meeting in the Seattle area, all the while working feverishly to implement Version 3 of NextComp before the meeting. Well it looks like I'll have Version 3 completed and ready by the end of February. So I'm working on the details to have the User's Group meeting in Issaquah sometime the first two weeks of March. I'm still nailing down the space availability. I'll have that finalized early next week, so the blog post next week will have more details.

Salary Planning

This past week I was working extensively on an update to the NextComp salary planning application. I re-wrote the database engine to enhance performance and scalability. This tool was originally designed to run on smaller networks and in smaller to medium sized companies. One client is running it on a larger network with over 500 managers having access the system. Of course, before the re-write, the database crumpled under the load. I think smoke might have been coming out of the server! :-) After a series of late night programming sessions, I was able to redesign the database to effectively scale up to over 10,000 employees and over 500 managers. Whew, that was some serious work. It's done now, and I'm moving on to create a cool Quicktime training video.

Here are some screenshots...








Of course that's not the only project that's going on. Never a dull moment here in Issaquah.

Version 3 Update

I've been working on NextComp Version 3 with the goal of having it completed by the end of February in time for several of our clients to transition to the new application before the WorldatWork Conference being held in Seattle in June. I've been getting really helpful feedback from a couple of larger subscribers that have volunteered to transition early to Version 3, while still using Version 2 primarily. Thank you to them! More updates next week on Version 3.



The Tweet In Review

Continuing on the post from last week, I've been trying to tweet on any interesting articles that I run across during my blog reading. I use NetNewsWire on the Mac to consolidate the blog posts into one list of updated information. I have over 100 blogs that I read on a regular basis, and the list keeps growing. I enjoy everything from compensation related blogs, to listserve type blogs, to art and photography blogs. Here are some highlights from this last week.

Sitting on top of my roof taking Xmas lights down http://twitpic.com/13z8v. Nice view from up here! 1:20 PM Jan 17th from twitterrific

AMD cutting 1,100 jobs and cutting salaries by 5% to 15%. http://tinyurl.com/7r4g2g 10:01 AM Jan 19th from TweetDeck

@annemareemoore Here's a blog with a copy of the President's speech in PDF format... http://leifbrostrom.com/?p=153 1:58 PM Jan 20th from web in reply to annemareemoore

Over 7 Million Workers to See Lowest Pay Raise in 30+ Years http://tinyurl.com/9ch9ub 9:44 AM Jan 21st from TweetDeck

iPhone development webinars, five total for $97 each. Good deal! http://tinyurl.com/885fc8 10:16 AM Jan 21st from TweetDeck

Just received an e-mail: There are just 130 days, 2 hours and 50 minutes until the 2009 WorldatWork Total Rewards Conference and Exhibition. 12:40 PM Jan 21st from TweetDeck

Check out the resolution of the Canon EOS 5D Mark II http://tinyurl.com/bhhn8c What an awesome place for skiing too! 10:44 AM yesterday from TweetDeck

Inauguration in Tweets: http://projects.flowingdata.com/inauguration/ about 18 hours ago from TweetDeck

New ski related blog post http://photomatto.typepad.com about 16 hours ago from twitterrific

President Obama is a good Dad too... http://tinyurl.com/8q3yp9 about 5 hours ago from TweetDeck

"Four sure fire ways to get laid off" http://tinyurl.com/bte4zc about 1 hour ago from TweetDeck

Links to information on the Leadbetter Fair Pay Act that looks to pass Congress soon. http://tinyurl.com/d4xhz4 http://tinyurl.com/bbzstn about 1 hour ago from TweetDeck

"Washington’s three largest insurers were down a combined $194M in October" http://tinyurl.com/dms7jv 43 minutes ago from TweetDeck

Thanks to "The Industry Radar" http://www.theindustryradar... for all the great links.
42 minutes ago from TweetDeck



Well that wraps up another week here in the cold, damp and foggy Northwest. We're supposed to get some snow over the weekend. Oh boy! Well at least that will help the ski resorts.

Until next week, have a great weekend!

Screen recording software

I've discovered a new screen recording software for Mac OS X.

It's called ScreenFlow and it looks really cool. There's a demo on the Telestream website.

I'm going to download this and give it a try for the "Import Survey" demo that I've been working on. I'll let you know how it goes after my tests today.

I hope finish that demo today or tomorrow!

UPDATE:

So here's a sample movie. It has the ScreenFlow logo on it, but that's ok for the test. I think this will prove to be a good solution as it has some new features, like the red circle that appears when I click, and the ability to zoom in on parts of the screen.