Two Excel functions to save time in compensation files

Two Excel functions to save time in compensation files

Two functions in Excel can help you save time when performing analyses on big compensation files : conditional formatting and sorting.

Imagine you have big file with salary information and you want to find out who are the lowest paid of managers in the organisation.

But titles are all over the place, not specifically tied to specific grades or other criteria and usually contain words before and after “manager” so you can’t use a straightforward alphabetical sort to identify the managers.
You don’t want to sort manually by creating a dummy column and reading the job titles one by one to find out who the managers are, and then doing a second manual sort to find out which ones are the lowest paid.

Conditional formatting

Conditional formatting, well, does what its name says : it formats cells based on specific conditions that you indicate.
In our example, select all cells on the file, then click on conditional formatting > highlight cell rules > text that contains >type in the text you want to identify (eg : manager) and chose the colour and style the cells will show in the file eg : lighgreen with dark green font.
Once done, all cells containing the word manager will be highlighted in green and you can see them quickly.
The visual effect can be very useful in many circumstances, for example if you are working on submitting data to a compensation survey and you want to check if there are some empty cells, which of course there shouldn’t. A conditional formatting putting the empty cells in red will help you quickly see (literally) where you may have an issue of lack of data.

Filtering

You can use the Sort and filter function in more advanced ways than the usual, regular sort from smallest to largest or from largest to smallest.
In our example, first let’s do a filter on cell colour. Select all cells > Sort and filter > Custom sort.  Make sure to select“My data has headers” so that you can use the titles of the columns in your sorting.
Select by : job title > sort on : choose cell colour > select the colour of managers (in our example, green) >on top (because you want managers at the top of the file).
You can also filter on multiple levels, simply by click on Add a level. In our example, you would sort by current basic pay > sort on : values > smallest to largest.
What matters is for you to think in advance about how you need to filter in order to get to the result you want to achieve. The trick is to go from the largest subgroup to the smallest one. For example, you want the lowest paid managers in HR and Finance with less than 2 years tenure in the company. You will sort in this order : managers – in HR and Finance – less than 2 years in the company – with lowest paid at the top.
For those of you who like visuals, I have put together a Powerpoint presentation with the same example :
I hope you find these functions useful in helping you saving time when you use Excel for manipulating large compensation files.

Enjoyed this post ? Why don’t you subscribe by email to receive my posts direct to your inbox 3 times per week ? Simply enter your email in the box at the top of the column on the right !

Related posts :

Print Friendly, PDF & Email

Trackbacks

  1. […] Two Excel functions to save time in compensation files Copyright protected by Digiprove © 2012 Sandrine BardotSome Rights ReservedOriginal content here is published under these license terms: X License Type:Non-commercial, AttributionLicense Summary:You may copy this content, create derivative work from it, and re-publish it for non-commercial purposes, provided you include an overt attribution to the author(s).License URL:http://creativecommons.org/licenses/by-nc/3.0/ Filed Under: Sunday Skills Tagged With: Compensation Surveys, Excel tips […]

Speak Your Mind

*