WorldatWork Excel Tips
 RSS Feed for Excel Tips -- (How to add / read RSS Feed)
Excel Tips for Total Rewards Professionals is a new column dedicated to those who use Microsoft Excel. These tips are intended to be useful when working with compensation data and may save hours of time, if used effectively. Topics covered in this column include efficient formatting of data, statistics, sub-totals, pivot tables, data validation and graphs.
| Financial Functions -- FV (Future Value Calculation) - week 93 | | August 31, 2010 | The functions to be covered are useful to rewards practitioners at an organizational level for analyzing the return on retirement or other benefit fund investments or the cost of raising capital, and at an individual employee level for financial planning around loans and investments.
| |
| Go To Function -- Special Options Continued - week 92 | | August 24, 2010 | The first option we will look at is Go To Special, Precedents. Remember that to access Go To, you can press Control G or the F5 key, This will take you to a Go To window, and you then click Special to access the Go To Special window.
| |
| Go To Function -- Special Options Continued - week 91 | | August 17, 2010 | This week we will explore some more of these functions, starting with Go To, Special, Blanks. Remember that to access Go To, you can press Control G or the F5 key, which will take you to a Go To window, and you then click Special to access the Go To Special window.
| |
| Go To Function -- Special Options - week 90 | | August 10, 2010 | Following on from the Paste Special functions discussed in the last five weeks, the next three columns will cover the Go To function. As with the Paste function, Go To has a Go To Special window available, with a number of very useful options for rewards work.
| |
| Paste Special: Operation Functions - week 89 | | August 3, 2010 | In this final week of the Paste Special functions series, we will look at how to apply the very useful and time saving Paste Special Operation functions in rewards work.
| |
| Paste Options: Paste Link, Paste as Hyperlink, ASAP Utilities Sheet Index - week 87 | | July 20, 2010 | This week we will look at where the Paste Link and Paste as Hyperlink options are valuable in rewards work, and examine how to create an index sheet with hyperlinks to each sheet using ASAP Utilities. Paste Link and ASAP Utilities are available in both 2003 and 2007, but Paste as Hyperlink is only available in Excel 2007.
| |
| Introduction to Array Formulas - week 80 | | June 1, 2010 | Array formulas can perform multiple calculations on one or more of the items in an array, and can return either multiple results or a single result.
| |
| How to Consolidate Data Using Formulas - week 77 | | May 11, 2010 | If you are managing a department or project budget, you also often have to amalgamate data from different members of the department or project team into a consolidated budget. This can be time consuming and error prone ...
| |
| How To Use Rounding Formulas - week 74 | | April 20, 2010 | There is often confusion in Excel around formatting data to no decimal places and rounding data. When Excel formats data to no or a number of decimal places, the data appears to be rounded, but it is not; Excel still retains the original data with all its decimals.
| |
| Using Dollar and Text functions - week 73 | | April 13, 2010 | This week we will look at the Dollar and Text functions, which convert a number to a specific text format, and are very valuable when doing a mail merge from Excel to Word. They can also be used to create headings in Excel based on data in tables.
| |
| Using Lower, Proper and Upper functions - week 72 | | April 6, 2010 | Often text data extracted from the payroll or from salary surveys appears as uppercase data. This is harder to read than proper case data, where just the first letter is capitalized, and also takes up much more room on the spreadsheet.
| |
| Using Len and Trim Formulae - week 71 | | March 30, 2010 | With the Left, Mid and Right functions, you specify how many characters you want to extract from the left, middle or right of a field. These work well if the field is a consistent length, or if the data you want to extract is always a consistent number of digits on the left or the right ...
| |
| Using Left, Mid and Right Formulae - week 70 | | March 23, 2010 | When analyzing compensation data, you generally extract data from the payroll, the HR system, electronic surveys or other sources. Or, if consulting, data is sent to you by clients who have extracted it into Excel.
| |
| How To Create a Range Penetration Graph - week 69 | | March 16, 2010 | A range penetration graph shows where employee salaries are positioned within the pay scale range. It is useful to look at it together with the employee¿s performance rating or with a rating of their experience/competence ...
| |
| How to Edit Charts in Excel 2003 - week 67 | | March 2, 2010 | In the graphing series so far we have covered a number of ways to edit graphs. It is impossible (without writing 100 columns!) to cover in detail all the ways that you can edit charts in Excel, as there are endless possibilities.
| |
| How to Create a Dual Axis Graph - week 66 | | February 23, 2010 | Sometimes we wish to examine the relationship between two sets of data by graphing them together on one graph, even though they have completely different scales.
| |
| How to Create a Pie Chart - week 62 | | January 26, 2010 | Pie charts are very useful when you want to illustrate the percentage share of the total of each element that you are graphing, and when the order of the data is not important.
| |
| How to Create Column and Bar Charts In Excel 2003 - week 59 | | January 5, 2010 | Excel has extensive graphing capabilities, but we tend to use certain types of graphs more than others depending on the work that we do. So, in this series, I will concentrate on those types of graphs that I think are most useful for total rewards work.
| |
| How to do Multi-Linear Regression - week 57 | | December 8, 2009 | In rewards work, multi-linear regression is used primarily to predict pay based on multiple factors. It is appropriate where multiple factors can provide a better prediction of pay than just one factor.
| |
| How to Add Market Data and Pay Ranges to a Scattergram - week 56 | | December 1, 2009 | Last week we created a scattergram and exponential trend line for a set of grades and basic salary data. We then plotted the trend line get a base pay policy line, but acknowledged that this would provide a good fit for the internal data, but did not take into account any market data.
| |
| How to do Exponential Regression Using a Scattergram - week 55 | | November 24, 2009 | Often in pay, we are looking not for a linear regression, but an exponential regression, as pay increases by a percentage per level, not by a constant amount, and the relationship between grades and pay is therefore normally exponential.
| |
| How to do Linear Regression Using Formulae - week 53 | | November 10, 2009 | Last week we used linear regression to create a base pay policy line/pay scale midpoints for a set of salaries and evaluation points. We did this by creating a scattergram and linear trend line for the data set, and using the formula generated by the trend line to plot the policy line.
| |
| How to do Linear Regression Using a Scattergram - week 52 | | November 3, 2009 | Regression Analysis, while it sounds complex, is really just about finding a line of best fit for a set of data. The line of best fit may be straight or curved, and the data set may involve one or more variables. In rewards work, regression analysis is used for three main purposes.
| |
| How To Use Data Tables - week 51 | | October 27, 2009 | The last function in the What-If suite that we will look at is the Data Table function. This is one of the most powerful of the What-If functions, as it enables you to substitute a range of values in a formula and determine the results of the formula.
| |
| How To Use the Scenario Manager Function - week 50 | | October 20, 2009 | The third function in the What-If suite of functions is Scenario Manager. Scenario Manager allows you to create a number of scenarios, each scenario being a set of values for a worksheet. Once you have saved the scenarios, you can switch to any of the scenarios ...
| |
| How to Use the Solver Function - week 49 | | October 13, 2009 | Last week we examined how Goal Seek can help you achieve an end result by adjusting a variable used to achieve that end result. The add-in function, Solver, performs a very similar function, but allows for multiple input variables and a variety of conditions.
| |
| How To Use Goal Seek - week 48 | | October 6, 2009 | Excel has a range of What-If functions that enable you to carry out a variety of what if analyses: Goal Seek, Scenario Manager, Data Table and Solver. They are very useful for modelling alternative scenarios ...
| |
| How to Use SUMIF, SUMIFS, AVERAGEIF AND AVERAGEIFS Formulae - week 47 | | September 29, 2009 | In this last column of the IF formulae series, we will look at how to use the SUMIF, SUMIFS, AVERAGEIF and AVERAGEIFS range of formulae, which are of great value in rewards work. The sum and average formulae work in identical ways, so it is useful to look at them together.
| |
| How to Use COUNTIF and COUNTIFS formulae - week 46 | | September 22, 2009 | COUNTIF counts how many values in a data range are greater than, less than, equal to or not equal to a particular value. COUNTIFS performs the same function, but allows you to use multiple criteria.
| |
| How to Use IFERROR and IF(ISERROR) Formulae - week 45 | | September 15, 2009 | When you are working with vlookup formulae and with average calculations in large spreadsheets, you often get a #N/A message where the vlookup formula cannot find an exact match, or a #DIV/0! message where Excel is trying to calculate an average and there is no data in the cells being averaged.
| |
| How to Use IF(AND) and IF(OR) Formulae - week 44 | | September 8, 2009 | Because all three of these conditions must apply for an employee to be eligible for a share grant, we can use the IF(AND) formula to calculate eligibility. The IF(AND) formula checks if a number of conditions apply. If they all apply, the value if true is placed in the cell, otherwise the value if false is placed in the cell.
| |
| How to Use IF and Nested IF Formulae - week 43 | | September 1, 2009 | Starting with the basic IF formula, I have found that the best way to ensure you are using the correct logic in the formula is to use the IF function wizard, rather than simply trying to write the formula.
| |
| How To Use Subtotals - week 40 | | August 11, 2009 | The Subtotal function on Microsoft Excel is an invaluable function for total rewards professionals, as it enables you to insert subtotals using a variety of statistics for as many columns as you wish and based on any field in your database.
| |
| How to Set up Data Protection - week 38 | | July 28, 2009 | This week we will look at how to protect the form to ensure consistency and prevent users from deleting your formulae or settings, how to hide supporting sheets that you don¿t want users to see and how to protect the workbook ...
| |
| How to Set Up Data Validation From a List - week 36 | | July 14, 2009 | When you design forms or documents for other people to complete, you want the data entered to be as consistent as possible. This is particularly important if you are using the data for Vlookup formulae, or ...
| |
| How to Check Sets of Data Against Each Other - week 35 | | July 7, 2009 | Continuing with our two column series on checking data, we will look this week at how to check two sets of data that should match against one another, and how to check two sets of data to determine if they contain the same or different records.
| |
| Techniques for Checking Data - week 34 | | June 30, 2009 | There are a number of techniques in Excel that are very useful for checking data or comparing sets of data that should match. In the next two columns, we will look at some of the techniques that can be used to check ...
| |
| How to Use Conditional Formatting - week 32 | | June 16, 2009 | Conditional formatting is a very useful tool that enables you to highlight data that meets certain criteria. The tool is interactive, so if the data changes, the data highlighted will change too.
| |
| How to Extract Unique Records - week 31 | | June 9, 2009 | We often need to extract unique records from a list of data. For example, we may wish to obtain a list of all the job titles that exist in the organization, but there are many people on the payroll with the same job title.
| |
| How to Validate and Protect Data - week 29 | | May 26, 2009 | We need to ensure that the data submitted to and extracted from salary surveys is correct and in the right format, and that the data used for increases and incentive calculations is correct and up to date.
| |
| Conditional Sum - week 20 | | March 17, 2009 | Excel has a very helpful function called the Conditional Sum Wizard that enables you to sum values in a column based on certain criteria. For example, you might want to calculate the sum of performance bonuses...
| |
| Correlations - week 19 | | March 10, 2009 | It is often useful in compensation work to establish the strength of the relationship between two sets of data. This can be done by drawing up a scattergram of the two sets of data and/or by establishing the statistical correlation...
| |
| Descriptive Statistics - week 18 | | March 3, 2009 | Excel has a very helpful function called Descriptive Statistics, which calculates most of the statistics covered in the last five columns, and a few additional ones, all at one time.
| |
| Skewness and Kurtosis - week 17 | | February 24, 2009 | When you have a sample of data, for example company or survey data in a grade or job category, you may wish to examine the distribution of this data to determine if it is skewed or evenly distributed.
| |
| Measures of Variance - week 16 | | February 17, 2009 | There are a number of formulae that can be used to measure the variance in a data set. These can either be measured directly or can be derived from ...
| |
| Measures of Location - week 15 | | February 10, 2009 | Excel contains a number of formulae that measure location or positions in a data set. The most valuable of these for compensation data are the minimum, maximum, lower quartile, upper quartile and percentile.
| |
| Measures of Central Tendency - week 14 | | February 3, 2009 | Excel contains a number of formulae that measure central tendency or different types of average. The most valuable of these for compensation data are the average, median and mode.
| |
| Statistics and Compensation Data - week 13 | | January 27, 2009 | There are four count formulae in Excel 2003 and five in Excel 2007. We will cover the four that are common to both versions, count, counta, countblank and countif.
| |
| How To Name Ranges - week 11 | | January 13, 2009 | You can make the table / range absolute by using the F4 key to add dollar signs before the column letters and row numbers. However, another way to make the table / range absolute ...
| |
| How To Use the hlookup Formula - week 10 | | January 6, 2009 | Hlookup (the h stands for horizontal) performs exactly the same function as vlookup, i.e. it enables you to take data from a separate table, spreadsheet or workbook and incorporate it into your original data.
| |
| How To Format Dates - week 6 | | December 1, 2008 | In order to format a date, select the cell or cells with the dates in them. The dates must be Excel dates with an underlying serial number.
| |
| How To Convert Text Dates To Formatted Dates - week 5 | | November 24, 2008 | What is important to understand about dates is that Excel can format them in many different ways, but regardless of the formatting, the date is actually stored as a sequential number called a serial value.
| |
About the Author
Dianne Auld
Auld Compensation Consulting
Dianne Auld has more than 20 years of compensation experience. She runs her own consulting practice, Auld Compensation Consulting, in Cape Town, South Africa. She consults in all areas of compensation, runs a number of her own training courses, and trains the GRP courses in Cape Town, Johannesburg and Angola. She also works part time for Pick n Pay, a leading retailer, where she advises on compensation policies and practices. Dianne runs a course, Excel Skills for Remuneration Practitioners, which served as the inspiration for this column.
|
Print this Page