Analytics Excel Handout - SHRM

[Pages:3]HR Analytics: Using Data to Drive HR Excellence

Microsoft Excel Functions Cheat Sheet 1. Calculating the Average (Mean)

a. Using an empty cell, type "=" into that cell b. A list of equations beings to populate. Select "Average".

c. A parentheses will appear. Select the data, or column of data you wish to calculate the average.

d. You can do this by clicking on the cell and dragging the mouse, or typing the cell numbers (the first and last in the set, separated by a colon) in parentheses.

e. Once you click outside the cell, the average will appear.

2. Using the Sort Function

a. First, select the "Data" tab in Excel. b. Then highlight the column or row you wish to sort

c. Once the data is collected, you can choose to sort the data from A-Z, Z-A, or low-high, high-low

1

? SHRM 2016

HR Analytics: Using Data to Drive HR Excellence

3. Creating Pivot Tables in Excel

a. First, select the "Insert" tab in Excel b. There, you will find the Pivot Table

function

c. Once you click on "PivotTable," a new

box will appear

d. Excel will

automatically select data for you (signified by dotted green lines), it also automatically populates the "Table/ Range" for you.

2

? SHRM 2016

HR Analytics: Using Data to Drive HR Excellence

e. You can change this simply by

highlighting the Table/Range values already entered, and then using your mouse to reselect the new data on the spreadsheet.

f. Once you've selected the right

data, choose where you want Excel to place the Pivot Table, on the existing spreadsheet, or on a new one.

g. Then, when you click "OK", you

should see the information (displayed to right)

h. By choosing the fields and

adding them to the areas below, you can tabulate the data however you want.

3

? SHRM 2016

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download