Dianne Auld's Excel Tips: Featuring Compensation and Benefits Formulas Third Edition

By Dianne Auld

While the role of today's total rewards professionals has become more strategic and business-oriented, some fundamentals of the job will always hold true. One such fundamental is math. Focusing on how to use Excel for compensation work, Excel guru and WorldatWork faculty member, Dianne Auld continues t

• Language: English
• Publisher: WorldatWork
• Release date: Oct 30, 2020
• ISBN: 9781579633929

Dianne Auld

Dianne is the founding member and principal consultant of Auld Compensation Consulting, operating out of Cape Town, South Africa. She consults across all areas of reward, and develops and runs a wide range of reward training courses. Dianne is a WorldatWork faculty member and teaches the GRP courses in Southern Africa. Dianne has developed and recorded several e-learning products for WorldatWork, including Excel Skills and Pay Structures. She designed REMeasure for PwC, an internet based job evaluation system, which is used by over 100 organizations. Dianne is an honorary life member of the South African Reward Association (SARA), an accredited Master Reward Specialist, and in 2013 received the SARA president's award for her outstanding contribution to the reward profession.

Book preview

Customizing the Quick Access Toolbar

The ribbon in Excel is comprised of tabs, each of which has a series of topically relevant icons. While the needed icon can always be accessed by clicking on the appropriate tab, having certain icons permanently available on the Quick Access Toolbar saves time. The Quick Access Toolbar can be placed either below or above the ribbon. To change its location, right-click on the ribbon and then click Show Quick Access Toolbar Below the Ribbon or Show Quick Access Toolbar Above the Ribbon, according to your preference. Initially, the toolbar will have a limited number of icons.

To place additional icons on the Quick Access Toolbar, right-click on the ribbon and select Customize Quick Access Toolbar. Popular Commands appears on the left and the icons currently on the Quick Access Toolbar appear on the right. See Screenshot 1.1. If you want to see all commands, click the down arrow next to Popular Commands and select All Commands or Commands Not in the Ribbon. They will be displayed alphabetically.

Screenshot 1.1

Setting Column Width and Row Height for the Entire Sheet

When setting optimum width for several columns, it is inefficient to do so one column at a time. Click the top-left corner of the worksheet between Row 1 and Column A, or select Cell A1, and press Ctrl+A. Either method selects the whole worksheet. Next, double-click on the line to the right of Column A (or any other column). This sets the optimum column width for the whole worksheet.

While the whole worksheet is selected, all columns can be made the same width by dragging the line to the right of Column A (or any other column) to the desired width. Likewise, you can make all rows the same height by dragging the line below Row 1 (or any other row) to the desired height. The font, alignment or any other formatting option for the whole worksheet also can be set this way.

Efficient Copy/Pasting of Data

The following example shows the most efficient way to copy/paste down a table: To calculate annual basic cash for all employees, when monthly basic cash data for all employees is in Column A, in cell B1 type =A1*12. While still in Cell B1, move the cursor to the bottom-right corner of the cell until it forms a thin black plus sign like this: +. Double-click the cell and the formula will copy and paste down as far as the data in Column A extends. This method is faster than any other copy/paste method.

CONVERTING DATA

Converting data from one column to two or more columns can make the data easier to work with or use in a sort. For example, first name and surname may be in one column, but may need to be sorted alphabetically by surname. To do this, the surname needs to be extracted into a separate column. Another example is when payroll cost codes need to be split into separate components (e.g., division, department, section code). There are two methods to convert the data.

Method 1: Data, Text to Columns

To convert the data from Column A to two or more columns if, for example, there is a list of employee names or payroll cost codes in Column A with a heading in Cell A1, as in Screenshot 2.1:

1.Ensure there are two blank columns (or as many as needed) to the right of Column A, or insert two (or more) columns to the right of Column A.

2.Select the data in Column A to be converted. (To quickly select a long list of data, click on the first cell, press Shift+End+Down Arrow or Control+Shift+Down Arrow.)

Screenshot 2.1

Screenshot 2.2

3.For employee name data with a space between first name and surname, click Data, Text to Columns, Delimited, Next. Check the Space box. Make sure no other boxes are checked. Click Next. Change Destination to Cell B2 (so it does not overwrite the original data). Click Finish. See Screenshots 2.2, 2.3 and 2.4.

4.For data with no spaces (e.g., payroll cost code) to be separated, click Data, Text to Columns, Fixed Width, Next. Insert a line at the appropriate point(s) in the data (e.g., after the third and sixth digits of the cost code). Click Next. Change Destination to Cell B2 (so it does not overwrite the original data). Click Finish. See Screenshots 2.5 and 2.6.

Screenshot 2.3

Screenshot 2.4

Screenshot 2.5

Screenshot 2.6

Method 2: Data, Flash Fill (Excel 2013 and later versions)

1.Working with the same employee name data as in Method 1, type the first name Mark in Cell B2 and the surname Davids in Cell C2.

2.Select Mark in Cell B2, click Data, Flash Fill or press Ctrl+E.

3.Do the same with Davids in Cell C2.

Excel will look for a pattern in the data and separate it into columns based on this pattern as far as the data in Column A extends. In this case, it will place all the first names in column B and all the surnames in column C. Note: Flash Fill must be done one column at a time. Also, be cautious when using this function to separate numbers, such as cost codes, as Flash Fill sometimes creates a series from the numbers rather than separating the numbers as intended. Flash Fill can also be used to combine columns (See Tip 3) and to change case (See Tip 11).

Converting data from two or more columns into one column / merging text from different columns can be helpful. For example, when names are divided into first name and surname using a space as a separator, some surnames might have two or more parts (e.g., De Niro, Von Trap) that need to be combined into one column. Or you might want to combine first name and surname into one column called name. There are three methods for doing this.

Method 1: Using Text Formulas

In Excel 2016, Microsoft introduced two new formulas to make merging data from different cells easier, CONCAT and TEXTJOIN. Both the new formulas allow selection of a range of cells for merging, whereas the earlier CONCATENATE formula allows selection of only single cells for merging. The CONCATENATE formula is still available for use in all versions. All three Text formulas are covered in Method 1.

Screenshot 3.1

CONCATENATE (All versions)

1.For data in two columns (e.g., Columns A and B) with a heading in the first row, ensure there is a blank column to the right of Column B, or insert a column.

2.In Cell C2 click Formulas, Text (2013 and earlier versions) or More Functions, Compatibility (2016 and later versions), CONCATENATE.

3.In the Function Arguments box, next to Text1, select Cell A2.

4.Next to Text2, press the space bar once. After clicking next to Text3, Excel will put quotation marks around the space to indicate it is text.

5.Next to Text3, select Cell B2.Click OK. See Screenshot 3.1.

6.Copy and paste the formula down Column C using the double-click method to merge data from the two columns of the table.

Note: The previous steps insert a space between the two fields. If you do not want a space between the fields, select Cell B2 next to Text2. Click OK. If you would like to merge further cells, select these and type in delimiters next to Text4, Text5, etc.

CONCAT (2016 and later versions)

1.The CONCAT function enables you to join data from a range of cells but does not allow a delimiter/separator.

2.Assuming you wish to merge data, with no delimiter/separator, from two columns, A and B, with a heading in the first row, ensure there is a blank column to the right of Column B, or insert a column.

3.In Cell C2, click Formulas, Text, CONCAT.

4.In the Function Arguments box, next to Text1, select Cells A2:B2.

5.Click OK. See Screenshot 3.2.

6.Copy and paste the formula down Column C using the double-click method to merge data from the two columns of the table.

Note: In this formula, Text2, Text3, etc. would only be used if you needed to select non-adjacent ranges of data for merging.

TEXTJOIN (2016 and later versions)

1.The TEXTJOIN function enables you to join data from a range of cells with a common delimiter/separator between each cell and allows empty cells to be ignored.

2.Assuming you wish to merge data, with a space delimiter/separator, from two columns, A and B, with a heading in the first row, ensure there is a blank column to the right of Column B, or insert a column.

3.In Cell C2, click Formulas, Text, TEXTJOIN.

4.In the Function Arguments box, next to Delimiter, press the space bar once. After clicking next to Ignore empty, Excel will put quotation marks around the space to indicate it is text.

5.Next to Ignore empty, leave the argument blank. This will default to TRUE, which means that Excel will not insert a delimiter between cells where one or more of them is empty.

Screenshot 3.2

6.Next to Text1, select Cells A2:B2

7.Click OK. See Screenshot 3.3.

8.Copy and paste the formula down Column C using the double-click method to merge data from the two columns of the table.

Note: In this formula, Text2, Text3, etc. would only be used if you needed to select non-adjacent ranges of data for merging.

Screenshot 3.3

Method 2: Using Ampersands

To use ampersands (&) to combine Cells A2 and B2 with a space in between, in Cell C2 type =A2& &B2. If you do not want a space between the fields, type =A2&B2. Copy and paste the formula down Column C using the double-click method to merge data from the two columns of the table.

Method 3: Using Flash Fill (Excel 2013 and later versions)

Say first names are in Column A and surnames in Column B, with headings on both columns. You want to combine the first and surnames into Column C. The first name in Column A is Mark and the first surname in Column B is Davids.

Type Mark Davids in Cell C2, then click Data, Flash Fill or press Ctrl+E. Excel looks for the pattern in the data and uses this to combine the data into one column as far as the data in Column B extends. Note: This method works well if there is a consistent pattern to the data. If there is not a consistent pattern (e.g, names have two or three parts to them), the best method to use is TEXTJOIN.

Converting Formulas to Values

For Methods 1 (Text Formulas) and 2 (Ampersands), the combined data is still in formulas after copying down the table. To convert the formulas into values (the result of the formula), select the whole column by selecting the letter C at the top of the new column. In the Home tab, click Copy, then the down arrow below Paste and select one of the 123 icons under Paste Values.

Each icon converts the data to values. The first option pastes as values only; the second pastes as values but retains the number formatting; the third pastes as values but retains the source formatting. If you are copying and pasting into the same cells, choose the first option. If you are copying and pasting as values into different cells, then choose the preferred option based on the desired formatting.

CONVERTING TEXT

Numerical data downloaded into Excel from a payroll or other source is sometimes imported as text instead of a numerical value, thus preventing calculations from being performed on the data. If data (e.g., an employee number) is formatted in one place as text and in another as a number, a VLOOKUP function connecting the two data sets will not work.

Excel automatically flags any numbers stored as text by showing a green triangle in the top-left corner of the cell. Click on the cell and an exclamation point appears on the right side of the cell.

There are three methods to convert the data to text:

1.Select all cells with numbers stored as text. If it is a large selection, use the scroll bar on the right to scroll back up to the first cell in the selection. Select the down arrow next to the exclamation point icon. Click Convert to Number.

2.Select all the cells in one column with numbers stored as text. Click Data, Text to Columns, Fixed Width, Next, Next, Finish. Repeat for other columns with numbers stored as text.

3.Assuming the numeric data formatted as text is in Column A with a heading, type =A2*1 in Cell B2.Copy and paste this formula down Column B using the double-click method.

With all methods, the data in the column becomes numeric and can be used in calculations.

Screenshot 4.1

Formatting Numbers with Zeroes in Front

For numeric data to retain one or more zeroes at the front (e.g., telephone number, bank account number, employee number), select the data, right-click, select Format cells, and in the Number tab select Custom.

For an employee number format that always has six digits, under the word Type, enter 000000. See Screenshot 4.1. An employee number entered as 1023 will then have the format 001023. This way, the data looks like text but is stored as a number. The custom format can be set up with as many digits as needed, and can be set up with spaces, hyphens, slashes, parentheses or any other punctuation in between the zeroes.

Excel can format dates in many ways but, regardless of the formatting, the date is actually stored as a sequential number called a serial value. For example, Jan. 1, 1900, is stored as serial number 1, and Dec. 1, 2020, is serial number 44,166 because it is 44,166 days from Jan. 1, 1900. Excel stores time as decimal fractions because time is considered a portion of a day. The serial value enables Excel to sort dates correctly.

Sometimes, when data is exported into Excel from other programs (HRIS, payroll or group enterprise systems), the date is formatted as text. In this case, the date will not have an underlying serial number; therefore, it will not sort correctly and cannot be used in date calculations. There are three methods to correct this.

Method 1: DATEVALUE Formula

Insert a column to the right of the dates and select the cell to the right of the first date. Click Formulas, Date & Time, DATEVALUE. In the Function Arguments box, next to Date_text, enter the cell reference of the first date. Click OK. Copy this formula down the table. Note: This formula works in some cases, but with certain date formatting, the formula yields the #VALUE! error.

Method 2: Data, Text to Columns Function

Select all the cells in one column with the dates stored as text. Click Data, Text to Columns, Fixed Width, Next, Next. In the last screen (Step 3), check the button for Date and choose the date format appearance of the dates, in this case, YMD. Click Finish. Repeat as necessary for other columns. See Screenshot 5.1.

Method 3: ASAP Utilities

The third method is to install a software download for Excel called ASAP Utilities (it can be downloaded from www.asap-utilities.com). It installs itself as an additional menu tab and provides more than 300 additional time-saving functions, one of the most useful being the date-conversion function. (See Tips 112-120 for all ASAP Utility tips).

Screenshot 5.1

Screenshot 5.2

After downloading the utility, select all the dates to be converted. Click ASAP Utilities, Numbers & Dates or Text, then Convert/recognize dates.

In the Function Arguments box, enter the date format appearance of the current dates under Date format in selected cells: and the desired format under Convert to:. Click OK.

The value of the ASAP function is that it caters for any type of date format -- the date elements (month, day, year) can be in any order, in any date format and with any type of punctuation or spaces between them. See Screenshot 5.2.

In all three methods, once the dates have been converted, they are stored as serial numbers and can be formatted in the desired date format using Format cells. They will sort correctly and can be used in date calculations.

WORKING WITH DATES

Total rewards professionals often need to work with dates, formatting them in different ways and calculating age, length of service, time in job or number of days worked.

Excel offers a broad range of options for formatting dates, but there are limited options shown under Date when Format cells is selected. To appreciate the flexibility offered, you need to use the Custom option.

To format a date, select the cell or cells with the date(s) in them. The dates must be Excel dates with an underlying serial number. If they are not, refer to Tip 5.

Right-click and select Format cells or press Ctrl+1. Select the Number tab. Select Custom (the last option under Category:). Enter the desired date format in the white box under Type:. You can use any combination of the days, months and years below with anything in between them (e.g., space, -, /). See Screenshot 6.1 for an example.

The date Monday, June 1, 2020, could be formatted as any of the following:

Screenshot 6.1

Depending on what you entered, the date Monday, June 1, 2020, would be displayed as:

HR and compensation practitioners regularly need to calculate age, length of service or time in job for employees. There are two useful formulas in Excel that perform this calculation.

YEARFRAC

Age can be calculated in Excel either as at a specific date or as at today. To calculate age as at a specific date, enter the date in a cell. To calculate age as at today, type =TODAY() in a cell. This enters today’s date whenever the spreadsheet is opened.

For either calculation, first insert a column to the right of the birth dates and select the cell to the right of the first birth date. Click Formulas, Date & Time, YEARFRAC.

In the Function Arguments box, next to Start_date, enter the cell reference of the first birth date. Next to End_date, enter the cell reference of the cell with the TODAY() formula or the specific desired date in it.

Make the End date cell absolute (i.e., put dollar signs in front of the cell and row reference). An easy way to do this is to press F4. This is important, as the formula must always reference this cell as it is copied down the sheet. Leave Basis blank to accept the default day count basis used by the formula. Click OK. See Screenshot 7.1.

Screenshot 7.1

The age will now be calculated for the first employee, and this formula can be copied down the sheet.

The Start (birth) dates will change in each successive row, but the end-date cell reference for today’s date or a specific date will remain the same.

Length of service can be calculated the same way as age. Age or length of service are usually rounded down. For example, the age 30.6 should show as 30. Length of service of 5.8 years should show as 5 years, if used for leave, LTI or benefits eligibility.

Screenshot 7.2

To round age or time down, insert a column to the right of the age or length of service calculation and select the first cell in this column. Click Formulas, Math & Trig, ROUNDDOWN.

In the Function Arguments box, next to Number, enter the cell reference of the first age or length of service calculation. Next to Num_digits, type 0. See Screenshot 7.2. The age or length of service will then be rounded down to the nearest whole number. Copy this formula down the table.

DATEDIF

DATEDIF performs a similar calculation to YEARFRAC but has the advantage of rounding numbers down all in one formula. It also can be used to measure years, months or days between two dates. The formula does not appear under the Date & Time formula options, so has to be written rather than using the Function Arguments box.

To use the formula, first set up the starting dates in a column. For age, these would be birth dates; for length of service, hire dates; and for time in job, date of appointment to current job. In another cell in the worksheet, either specify an end date for the calculation or type the formula =TODAY(). This enters today’s date whenever the spreadsheet is opened.

Assume that you want to calculate age as at today. Birth dates are in Column A, with the first birth date in Cell A2, and the TODAY() formula in Cell C2. Ensure that Column B is formatted as a number with no decimal places. In Cell B2, write the formula =DATEDIF(A2,$C$2,y). Note that the y must be in quotation marks and it is not case sensitive.

Use the double-click method to copy this formula down the worksheet and age will be calculated for all employees. Note that the birth date cell (A2) is a relative cell address, but the TODAY() date cell (C2) is an absolute cell address, (i.e., it has dollar signs in front of it). This is so that, as the formula is copied down the worksheet, it reads each successive date but always uses the cell with the TODAY() formula.

To calculate age at a specific date, enter the date in Cell C2 rather than the TODAY() formula.

As written, this formula calculates the number of years between two dates and rounds the years down. The same formula can be used to calculate the months (rounded down to whole months) between two dates by changing the y to an m, or to calculate the days between two dates by changing the y to a d.

The DATEDIF formula also can be used to calculate years and months between two dates by using the formula with y in the first column and ym in the second column. The formula in the first column calculates the whole years between two dates, and the formula in the second column calculates the months in addition to the whole years already calculated.

For example, if the starting date in Cell A2 is Jan. 1, 2015, and the end date in Cell B2 is July 1, 2020, the formula DATEDIF(A2,B2,y) would show 5, and the formula DATEDIF(A2,B2,ym) would show 6, to indicate 5 years and 6 months between the two dates.

Excel has two date and time formulas to calculate working days between days. NETWORKDAYS provides for a standard weekend of Saturday and Sunday, and NETWORKDAYS.INTL allows for a variable weekend. These formulas can be used, for example, to calculate the number of working days during a vacation period, an extended training period or the number of working days worked by a contractor.

NETWORKDAYS

Before starting the calculation, check for any public/observed holidays falling on workdays between the start and end dates and enter these in an array of cells. These may include state, federal and floating holidays.

To calculate the working days between two dates, click Formulas, Date & Time, NETWORKDAYS. In the Function Arguments box, next to Start_date, enter the cell reference of the vacation, training or work start date. Next to End_date, enter the cell reference of the vacation, training or work end date.

Next to Holidays, enter the array of cells with the public/observed holidays. Make this absolute (i.e., put dollar signs in front of the cell and row references). An easy way to do this is to press F4. This is important, as the formula must always reference these cells as it is copied down the sheet. Click OK. See Screenshot 8.1.

Working days between the start and end dates will now be calculated for the first employee, and this formula can be copied down the table. As the formula is copied down, the start and end dates will change but the cell reference for the public/observed holidays will remain the same.

NETWORKDAYS.INTL

NETWORKDAYS.INTL can be used to calculate working days in countries with different weekend days, for example in the Middle East, or where the weekend only constitutes one day. The formula has an additional stage called Weekend, which comes before Holidays.

Click Formulas, Date & Time, NETWORKDAYS.INTL and enter the same information for Start date, End date and Holidays as entered for NETWORKDAYS. See Screenshot 8.2.

Screenshot 8.1

Screenshot 8.2

In the Funct ion Arguments box, when next to Weekend, click on Help on this function at the bottom of the window. Excel Help will show the seventeen different numerical options that can be entered for weekends. See Screenshot 8.3 for some of these options. For example, 6 is Thursday, Friday, which is common in the Middle East. As used in this formula, 11 is Sunday only and is useful when employees’ normal weeks include Saturday. Choose the desired option and then proceed with the formula as described for NETWORKDAYS.

TEXT FORMULAS

Compensation data in Excel is usually extracted from the payroll, the HRIS system, survey data or other sources. Or, if consulting, data is provided by clients who have extracted it into Excel. Either way, the data is often not in the format needed for calculations. This is where Text formulas are useful, as they can be used to massage text data into the desired format for calculations or reporting. Tips 9 through 12 address the most useful Text formulas for total rewards work.

Screenshot 8.3

The LEFT, RIGHT and MID formulas are useful when data is sitting in one field/column but needs to be split into different fields/columns to be used in calculations. The LEFT function extracts characters from the left of the field, RIGHT from the right of the field and MID from the middle of the field.

Screenshot 9.1

Let’s say a company has grades B1-4 up to G1-4. This company is broadbanded, so the letter refers to the broadband and the number to the type of job in the band (e.g., 1 is line management, 3 is specialist). Benefits are linked to the band while pay ranges are linked to the band and job type. See Screenshot 9.1. Assume that with this data you need to:

■ Extract the left character (the letter of the band) and use this in a VLOOKUP formula to determine various benefits. (See Tip 14 .)

■ Extract the left and right characters (the band letter and the job type number) and use this in a VLOOKUP formula to determine the applicable pay range.

There are three methods for doing the extraction. The first two methods, using Data, Text to Columns, Fixed Width function and using Data, Flash Fill, are outlined in Tip 2. The third method is to use the LEFT and RIGHT formulas. The advantage of using formulas is that, if the source data changes, the extracted data also changes. Data, Text to Columns and Data, Flash Fill both produce data as values with no links to the source data.

LEFT Formula

In Cell C2, click Formulas, Text, LEFT. In the Function Arguments box, next to Text, select Cell B2 (the first grade). To extract one character on the left of the field, leave Num_chars blank or type 1. To extract two or more characters from the left of the field, type the appropriate number. Click OK. See Screenshot 9.2. There will now be a B in Cell C2. Use the double-click method to copy this down the worksheet. See Screenshot 9.3.

RIGHT Function

To extract the job type number on the right, in Cell D2, click Formulas, Text, RIGHT. In the Function Arguments box, next to Text, select Cell B2 (the first grade). To extract one character on the right of the field, leave Num_chars blank or type 1. To extract two or more characters from the right of the field, enter the appropriate number. Click OK. See Screenshot 9.4. There will now be a 1 in Cell D2. Use the double-click method to copy this down the worksheet. See Screenshot 9.5. In this example, there is only one type of each grade. But, in the actual database of employee data, there would typically be hundreds or thousands of records with different grades.

Screenshot 9.2

Screenshot 9.3

Screenshot 9.4

Screenshot 9.5

Screenshot 9.6

Screenshot 9.7

Working again with the grade and job type data shown in Screenshot 9.5, the next step is to combine the band and job type into one field and use this data in a VLOOKUP formula to find the appropriate pay range. (See Tip 14.) This can be done using the CONCATENATE formula, the CONCAT formula or by joining the fields with ampersands (See Tip 3.)

To combine the fields, in Cell E3, click Formulas, Text, CONCAT. In the Function Arguments box, next to Text1, select Cells C2:D2 (the first band and the first job type). Click OK. See Screenshot 9.6. You will now see B1 in Cell E2. Use the double-click method to copy this down the worksheet. See Screenshot 9.7. Another way to join the fields together is to type =C2&D2 in Cell E2. Or, instead of doing the formula in stages, the LEFT and RIGHT formulas could be written together in one formula, joining them with ampersands: =LEFT(B2)&RIGHT(B2).

MID Formula

The MID formula is useful for extracting the middle part of a field. For example, assume a survey provides job description information in an Excel spreadsheet. The industry, discipline, sub-discipline and job code (a number between 1 and 1,500) are shown in separate columns, but the salary-extract data combines all this data into one job code in one column. A sample of the codes is shown in Screenshot 9.8. The first job code appears as GenFincAcct1414, but in the job description table it is shown in separate columns as Generic, Finance, Accounting, 1414.

Screenshot 9.8

The 1- to 4-digit numeric job code at the end needs to be extracted from this data for use in a VLOOKUP formula to pull data from the job description file and combine this with the salary-extract data. The RIGHT formula cannot be used because the number of digits in the numeric code varies between 1 and 4. But, for this example, the MID formula is ideal.

To use the MID formula, in Cell C2, click Formulas, Text, MID. In the Function Arguments box, next to Text, select Cell B2 (the first job code). Next to Start-num, specify the character number from where to start the extract. In this case, extract from the 12th character onward because the first 11 characters represent the industry, discipline and sub-discipline. Next to Num_chars, enter the number of characters to extract, in this case 4. Click OK. See Screenshot 9.9.

Screenshot 9.9

Screenshot 9.10

Note: It does not matter if there are fewer characters than the number specified; the MID formula still works because it extracts up to four characters. You will now see 1414 in Cell C2. Use the double-click method to copy the formula down the worksheet. See Screenshot 9.10.

Note: The job code numbers are still a text formula at this stage. To convert the fields from a formula to the result of the formula, select Column C. Click Copy, then the down arrow under Paste, and select the first 123 icon under Paste Values. The data will now have green triangles to indicate the numbers are stored as text. Select all the data, click the arrow to the right of the exclamation mark next to the first cell, and select Convert to Number. The data can now be used in a VLOOKUP formula.

An alternate method to extract the numeric job code is to use Data, Text to Columns, Fixed Width, specifying a break after the 11th character. (See Tip 2.)

With the LEFT, RIGHT or MID formulas, the number of characters to be extracted from the left, right or middle of a field are specified in the formulas. This works well if the field is a consistent length or if the data is always a consistent number of digits on the left or right.

However, sometimes the field to be extracted is a variable length and number of words. For example, a company may combine job title and grade data in one field. The job title may need to be extracted into one column and the grade into another column, as both fields need to be used in pivot tables and lookup formulas in the compensation analysis. This is where the LEN formula comes in handy.

LEN Formula

To illustrate, let’s work with the job titles and grades shown in Screenshot 10.1. This data cannot be separated into job title and grade by using Data, Text to Columns, with space as a delimiter, because the job title varies between one and three words, so the grade may end up in the second, third or fourth column. Data, Text to Columns, Fixed Width will not work either because the length of the job title is variable. The grade can be extracted into a column using the RIGHT formula, taking the right two digits, as demonstrated in Tip 9. However, the job title still needs to be extracted from the field. The solution lies with the LEN formula, which measures the number of characters in a field.

Screenshot 10.1

Screenshot 10.2

Screenshot 10.3

First, you need to measure the number of characters in the Job Title Grade field as follows: In Cell C2, click Formulas, Text, LEN.

In the Function Arguments box, next to Text, select Cell B2 (first Job Title/Grade). Click OK. See Screenshot 10.2. You will now see the number of characters (including spaces) of the first Job Title/Grade, 22, in Cell C2. Use the double-click method to copy this down the worksheet. See Screenshot 10.3.

Now that the length of the field is known, the job title can be extracted because the job title is always (from the left) the number of characters in the field less three (the space and the two-digit grade on the right of the job title). To extract the job title, in Cell D2, click Formulas, Text, LEFT. In the Function Arguments box, next to Text, select Cell B2 (Job Title/Grade). Next to Num_chars, select Cell C2 (length of the field) and type -3. See Screenshot 10.4.

Screenshot 10.4

Screenshot 10.5

You will now see the job title in Cell D2, minus the space and grade at the end of the field. Use the RIGHT formula to extract the grade into Cell E2 and the double-click method to copy both formulas down the worksheet. The data will then look like Screenshot 10.5, with job title and grade separated. The LEN and LEFT formulas can also be incorporated into one cell, in which case the formula for the Job Title field would look like this: =LEFT(B2,LEN(B2)-3).

TRIM Formula

The TRIM formula trims excess spaces from words, either at the end of a word or where there is more than one space between words. This is valuable when using job title in a VLOOKUP formula (e.g., to look up a survey job code). See Screenshot 10.6.

Screenshot 10.6

The first two job titles look identical and they both have correct VLOOKUP formulas to read the job code from the Job Title-Survey Job Code Match table on the right. Yet, for the second job title, the VLOOKUP formula yields #N/A (not available) because there is a space at the end of Accounts Supervisor; therefore, the formula cannot find an exact match. The space is not visible but causes the VLOOKUP formula to fail. The same applies to the first Sales Administrator job title, and the first Creditors Clerk job title has an additional space between Creditors and Clerk. The TRIM formula is ideal to sort out this problem.

To use the TRIM formula, insert a column after Job Title. In Cell C2, click Formulas, Text, TRIM. In the Function Arguments box, next to Text, select Cell B2 (first job title). Use the double-click method to copy this down the worksheet. See Screenshot 10.7.

The job titles without the additional spaces are now in Column C. Change the first VLOOKUP formula in column D to =VLOOKUP(C2,$F$2:$G$10,2,FALSE) so the formula reads the trimmed job titles rather than the original job titles. Copy this formula down the worksheet to obtain the data shown in Screenshot 10.8, with all the VLOOKUP formulas working correctly.

Screenshot 10.7

Screenshot 10.8

Screenshot 11.1

Text data extracted from payroll or salary surveys often appears as uppercase. This can be difficult to read and takes up more room in a spreadsheet. Converting data to another case is easy to do with Text formulas. Let’s use the data in Screenshot 11.1 as an example.

The Survey Job Title is shown in capital letters in the job description table to identify it as the most important field in the data set, but when the data is pulled into a job comparison analysis, you might prefer the data with initial caps. To convert the data to initial caps, or Proper case, insert a column next to the field to be converted (in this case, the data in Column E). In Cell F2, click Formulas, Text, PROPER. In the Function Arguments box, next to Text, select cell E2 (the first job title). See Screenshot 11.2. The job title will now be converted to initial caps. Use the double-click method to copy this down the worksheet. See Screenshot 11.3.

Screenshot 11.2

Screenshot 11.3

The only problem with the conversion is that CREDIT ANALYST II appears as Credit Analyst Ii. To fix this and other Roman numerals, first convert the initial caps job title column from a formula to the result of a formula. To do this, select the whole column, click Copy, then the down arrow below Paste and select the first 123 icon under Paste Values. Column E can then be deleted because Column F is no longer linked to it.

Next, select the whole column of initial caps job titles and press Ctrl+H for the Replace formula. Alternatively, click Home, Find & Select in the Editing group, then Replace. In the Find and Replace window, next to Find what:, type Ii. Next to Replace with:, type II. Click Replace All. See Screenshot 11.4.

Screenshot 11.4

Screenshot 11.5

Leave the Find and Replace window open. Next to Find what: type IIi (because you have partially fixed the Iii with the previous replace). Next to Replace with: type III. Click Replace All. Leave the Find and Replace window open. Next to Find what: type Iv. Put a space in front, so you don’t replace part of a job title as well (e.g., iv in the word driver). Next to Replace with: type IV also with a space in front. Click Replace All. The data now matches Screenshot 11.5. The Replace formula can also replace other abbreviations, such as It with IT, Hr with HR and so on. Excel also has two other case formulas, Upper and Lower. These work the same way as Proper and convert text to all uppercase or all lowercase.

Another way to convert to a different case in Excel is to use the add-in ASAP Utilities formula, available as a download from www.asap-utilities.com. (See ASAP Utilities Tip 119.) This converts all the text to initial caps without having to insert columns and apply formulas.

A third method is to use Data, Flash Fill (See Tip 2.) To use this method, type the first survey job title in the desired case in a column to the right of the original data. Select this job title and click Data, Flash Fill or press Ctrl+E. Excel looks for the pattern in the data and uses this to change the case of all the job titles.

The DOLLAR and TEXT formulas convert a number to a specific text format and are valuable when doing a mail merge from Excel to Word. These formulas can also be used to create headings in Excel based on data in tables.

Screenshot 12.1

Screenshot 12.2

DOLLAR

When doing a mail merge, first set up an Excel database with the merge data. Say you have the data shown in Screenshot 12.1 that you want to use in a mail-merge letter to sales representatives informing them of their salary, commission and travel expense claims paid for the past month.

Because the data is formatted to no decimal places, it looks as though there are no decimals in the data. However, when it is merged to Word, any decimals in the data will appear in