1 Session #: 510 Excel 201: Elevate Your Skills to the Next Level Download session documents at: Presenters Name: David Pena, Hrair Tcholakian

2 Thank You for joining us at ADP Meeting of the Minds 2017!Session 501 will help you with advanced topics such as VLOOKUP, pivot tables, nested functions, drop down lists, and selected cell protection! You can visit us at the Partner Pavilion – Kiosk #28.

3 Agenda Drop down lists Selected cell protection Common Excel FunctionsPivot tables and charts VLookup Nested functions Patti: Introduce Agenda

4 Drop Down Lists

5 Drop Down Lists Drop down lists allow you to control the data that can be entered into a cell Rita: Drop downs are lovely little things that allow you to have some measure of control over data entry – which will help ensure accuracy and consistency across your Team, Group or Your Organization!

6 Drop Down Lists: When do you need?Typically used on a form provided to users or managers To restrict the values entered into a cell To provide a list of valid values, such as departments, job codes, or actions and reasons When would you use a drop down list? Drop down lists are typically found on forms that users or managers need to complete. Drop downs restrict the available values a user can enter in a cell, providing only those values used within your organization….otherwise know as “valid” values! Drop Down lists ensure that all users use the appropriate data in cells; they eliminate misspellings, multiple abbreviations for the same entry and values that do not exist or are no longer “valid” due to changes within your organization.

7 Drop Down Lists: ExamplesYou haven’t rolled out MSS yet, so you are using an Excel form to capture transactional information, such as salary changes, from managers To avoid “garbage” data, you use drop down lists with “valid” values User forms, such as Personnel Action Forms or benefits enrollment forms. Drop downs can be easily changed to eliminate old codes due to internal reorganizations or changes to annual information, such as benefit plans or life insurance levels.

8 Drop Down Lists: How do you do it?Create a list in cells A1:A8, options are to Enter the items in a single row, such as A1:H1 Enter items in a column A1:A8 Create lists on a separate worksheet Select the cell where you want the drop down list, E3 Choose Data Validation from the Data Tools menu Here we will create a drop down list from valid values entered in a column. Keep in mind that you can create the list using either rows or columns. Put the curser in the cell you want the drop down list to appear Go to the Data Tab and choose Data Validation

9 Drop Down Lists: How do you do it?Choose List from the Allow option's drop-down list The Data Validation dialog box will open. On the Settings tab, you will tell the system the validation criteria or what is allowed in that particular field. Your choices are whole numbers, decimals, a list, a date or time etc. So, from the Allow drop down list, choose “List” Be sure to: check “Ignore blank” to ensure blanks do not become an option in your drop down box; check “In-cell dropdown” to tell the system that you want the drop down list to be available in the cell where the data entry will occur;

10 Drop Down Lists: How do you do it?Click the Source box, highlight cells where your drop down list values are (A1:A8) or enter the cell range: =$A$1:$A$8 Make sure “In-Cell Dropdown” is checked or the user will not see a drop down list Click OK, and your Drop Down List is created The “Source” icon tells the system where to find the “valid values” you have stored. Click on the icon and highlight ONLY the valid values. Note the red $ signs in the cell range; these are required, but in earlier versions of Excel were not programmed to appear – so you would need to enter them manually.

11 Drop Down Lists: Sample ResultThis is what the dropdown will now look like….when a User enters the curser in the field, the dropdown icon will show; click on the dropdown icon and the list of valid values is available for the User to chose one of the listed options. It’s that easy!

12 Selective Cell Protection

13 Selective Cell ProtectionSelective cell protection allows you to make specific cells read-only, while allowing input into other cells Now I’m going to show you how to protect your document so users can’t change information while keeping certain cells in that same document available for input.

14 Cell Protection: When do you need?Protect valid values in drop down list Compensation modeling for managers Budget templates Anytime you have a spreadsheet where you want some fields to be unavailable for change.

15 Cell Protection: How do you do it?Cells A1:A8 contain a list of valid values that are used in Cell E3 You need to allow users to choose a value in Cell E3, but not allow them to change the valid value list in A1:A8 Let’s say you’ve created a template, such as the Personal Action Form from the previous section. You’ve created cells with drop down values to select but there are other cells on the form that you don’t want users to be able to change.

16 Cell Protection: How do you do it?To disable data entry, lock sheet Select the range (A1:A8) you want to lock Right-click the selection and select Format Cells Select the Protection tab To lock the sheet, check the Locked option Click OK To enable data entry, unlock cells Unprotect the sheet Highlight cells to be re-opened, remove the Lock option First, you’ll highlight the cells you want to lock, right click, select Format Cells, then click on the Protection Tab. Inside the Protection tab are two options: locked cells and hidden cells. Click on ‘Locked’ and click ok. Then Protect the sheet. If you need to make a change say you selected a cell that you do want users to modify, just unprotect the sheet, highlight the cell where you want to remove the lock, right click, Format Cells, Protection and remove the checkbox next to lock cells. Click ok and re-protect the sheet.

17 Cell Protection: How do you do it?Click the Locked check box if highlighted cells need to be locked, un-check the box if cells need to be available for entry Here’s a picture of the locked cell page under Format Cells, Protection tab. Note the locked checkbox.

18 Sheet Protection: How do you do it?Select the Review tab In Changes group box, click Protect Sheet button Check the “Protect worksheet and contents of locked cells” check box After locking or unlocking your cells, you’ll need to protect the sheet for the locked option to be active. Under the Review Tab at the top of your page, click on Protect Sheet. Be sure the Protect Worksheet and Contents of Locked Cells is checked.

19 Cell Protection: How do you do it?In the Password to Unprotect sheet box, type password Click OK Locked cells are now protected and cannot be edited Unlocked cells are available for data entry Users cannot remove the sheet protection without your password Enter a password in the Unprotect Sheet field. Another field will appear for you to verify the password. Click ‘OK’ Your form is now protected against unwanted entry while allowing entry in appropriate fields. As a note – use a password you’ll remember or write it down. There’s no way to unprotect the sheet if you lose the password!! Ok – now that you’ve learned how to lock cells and protect your worksheet, Rita is going to share some cool Excel functions with you.

20 Functions

21 Functions: What is a “Function”?A coding/programming “sentence” that carries out a specific task What is a Function? Basically – it a programming sentence that caries out a specific task.

22 Excel Functions IF functionReturns one value (a) if a condition is true and another value (b) if it's false. Example Use: To find Highly Compensated Employees (HCEs) in a list of all employees, if the salary is greater than $199,999.99, then return “HCE”, otherwise(else), leave blank. =IF(A2>$199,999.99,“HCE",“ ") Read from slide. In this sentence – the task is: if A2 data fits the criteria, then enter HCE, otherwise/else, leave the cell blank.

23 Excel Functions IsBlank functionReturns “true” if the value refers to an empty cell. Example Use: To determine which employees have benefit enrollment forms, if the enrollment date field blank, return “true”, otherwise, returns “false” =ISBLANK(A2) Read from slide. Finish with: In this sentence, there are only two options – true or false.

24 Excel Functions WEEKDAY functionReturns the day of the week corresponding to a date. The day of the week is given a number, from 1 (Sunday) to 7 (Saturday) Example Use: Use timesheet data to determine who worked on the weekend for gift card =WEEKDAY(A2) (Sunday to Saturday) =WEEKDAY(A2,2) (Monday to Sunday) Read from Slide. This sentence provides one result – what number day of the week is that date.

25 Pivot Tables

26 Pivot tables Pivot tables When do you need them? How do you do them?Slicing and dicing examples Pivot charts We’re going to talk about Pivot Tables. I’m going to show you some examples of when a pivot table would be useful and how to use them, then finish with how to create a chart representing the data from your table.

27 Pivot tables: When do you need it?When you need to slice and dice a large amount of data to view in a more meaningful summary format Summarize pay, earnings and hours by employee View participation in all or a single benefit, by employee type, location, age When you need to do data modeling Pivot tables are very useful when you have a large amount of data that you need to summarize in a particular fashion. For example, you might need to organize earnings during a specific period to show who earned what or how much was paid each pay period. Or maybe you need to identify all which employees had earnings for a specific earnings code, over a specific period. Pivot tables will help you summarize the data needed quickly.

28 Pivot tables: How do you do it?Show earnings by check date by active employee within specific paygroup Select the data on the spreadsheet Select all cells (CTRL+SHIFT+END) or specific cells 1 Let’s say you’ve been asked to provide a report, by paygroup and employee status, showing gross earnings by checkdate. You’ve created an ADPR report of all earnings and also included the employee status field. You now have a very large report containing all the information needed. Save the report as an excel file. This is your master data file.

29 Pivot tables: How do you do it?INSERT > Pivot Table > click OK 2 First highlight all of the rows of data you want included in your report, then click Insert and click on Pivot Table. A box will open with the data range you highlighted shown in the Table Range box. In the next section, New Worksheet should be checked. As a note, you always want to select new worksheet so your master data file remains unchanged. After verifying the range and new worksheet, click OK

30 Pivot tables: How do you do it?Choose fields to add to report Drag fields to row, column, filter, or value 3 4 A Layout box opens where you will define the layout of your report. For our example, we know we might need to report data separately by paygroup and employee status so we’ll drag those fields to the Report Filter box. We want to list earnings by check date so we’ll drag ‘check dt’ to the Column Label box. Since we want to show earnings by employee, we’ll drag File Nbr to the Row Label Box, and then, since we want to display earnings, we’ll ‘Earnings’ to the Value box. The pivot spreadsheet will create as you drag fields to the Layout box. Note: check the data in the values section of the spreadsheet. If it needs a different calculation, right click in the data, choose ‘Summarize Values By’ then select the calculation desired (sum, count, average, etc.).

31 Pivot tables: How do you do it?Looking at the result, you’ll see the weekly earnings have been summarized by employee, by check date. Also, you’ll see Paygroup and Empl Status in the top left column with the current selection.

32 Pivot tables: Slice and diceSelect additional or different values to slice and dice further Drill down of earnings by employee So let’s say you provide the report and they want more data! Now they want to know what the detail of earnings and request that hours be added. In the Pivot Table Layout box, drag earncd into the Row Labels box, right under file number, and drag AL_hours into the Values box. Be sure that the sum is selected!!

33 Pivot tables: Slice and diceNow the report displays the detail of the earnings and hours by employee for each check date.

34 Pivot tables: Slice and diceDrill down employees within earnings and hours Ok – so you’ve presented that and now management is really excited. Can you provide a report identifying the employees who were paid specific earnings each week? No problem. Back to your Layout box, just move erncd on top of file_nbr which changes the primary sort to earnings code, then employee. The report now provides detail of employees who were paid specific earnings, by week.

35 Pivot tables: Charts and graphsSelect the data on the spreadsheet Select all cells (CTRL+SHIFT+END) or specific cells INSERT > Pivot Chart > click OK Choose fields to add to report Drag appropriate fields to Legend fields Axis Value Now, after you’ve created your report, you’re asked to provide a chart showing total earnings by check date for an executive management meeting. No problem. Again, select the data on your master data spreadsheet, click on Insert and select Pivot Chart. A new Layout box will open.

36 Pivot tables: Charts and graphsSince you may want to filter by paygroup, drag Paygroup into the report filter. We want to show total earnings by check date so we’ll drag check date to the Axis field, to list the dates across the bottom of the chart, and then drag Earnings to the Values field.

37 Pivot tables: Charts and graphsThe completed chart displays total earnings by check date, filtered by paygroup. As you can see, pivot tables and charts can help you provide meaningful reports in multiple different groupings, using the same data source. Now Rita is going to show you another handy tool – Vlookups!!

38 V Look Up

39 Vertical Lookups Vertical Lookups allow you to check if a value in a cell exists in another Excel worksheet – same sheet, different sheet, different file Vertical Lookups allow you to look up values from one spreadsheet and enter that value on another spreadsheet, based on a common identifier Vertical Lookups – or VLookups as they are commonly called, come in handy when you need to: comparing files to determine what might be missing in one of them bring missing data into your spreadsheet from a different file, workbook or spreadsheet validate data accuracy

40 VLookup: When do you need it?Add data not included in a file Bonus file has EMPLID, but no PR file number Validate the existence of data between files Pay group and PR file number need to be validated on bonus file provided by Compensation Received revised full file of merit increases and only need to identify the people not already in the pay batch Vendor provides active participants and you need to load only those employees not already set up Examples allow you to add data – such as PR file number for bonus files; vlookups also work well for validation of data from a master file to a working file. (Read from slide)

41 VLookup Function: How do you do it?Returns information from a column in another spreadsheet for inclusion in main spreadsheet or to validate data Example Uses: find and include employee’s PR file number in a Bonus file or ensure all PR Files numbers are correct =VLOOKUP(A2,'Lookup Data'!$A$3:$B$22,2,FALSE) Read from slide. False searches on the exact value; true provides the closest value (where data is sorted numerically, alphabetically, etc.). This sentence looks up the data in a cell in another spreadsheet.

42 VLookup: How do you do it?Bonus File received with emplid and amount Spreadsheet containing emplid and file number In this example, we have EmplID on a Bonus file, but not PR File number, which is needed for payroll processing. We will use two spreadsheets to pull the file number over and add it to the EmplID/Bonus spreadsheet.

43 VLookup: How do you do it?Click on Formulas > Lookup and Reference > VLookup Click on the Formulas Tab, then Lookup and Reference, then Vlookup; The function arguments dialog box looks like this. Scary? Not really. Let’s look at the individual components and what they mean.

44 VLookup: Function ValuesThe dialog box will ask for the following: Lookup Value: common identifier used to lookup data Table Array: validation data range in the lookup spreadsheet/tab (use $ for absolute values) Col_Index_Num: column number with desired data in lookup spreadsheet Range_Lookup: enter FALSE to force an exact match Lookup Value – is the “what am I looking for?” and must exist in both places (worksheet, spreadsheet, workbook). Table Array – is where you look for the value (above) – where do you want me to look? Absolute values are exact matches Column Index Number – what column in the Table Array do you want to be returned? Range_Lookup – especially in cases like this, “false” tells the system it must look for an exact match!

45 VLookup: How do you do it?Example of a completed formula So here we go – Look up the data in cell A2 – which in this field is Tell it where to look: Workbook labeled “Lookup Data” in fields A3 through B39 Then give me the information in column 2 in the Table array, outlined above. False in the Range_lookup for an exact match. Click OK

46 VLookup: Sample resultThe Bonus File now shows the Payroll file number Viola! Oh wait a minute….#N/A?!?!?!

47 VLookup: Result valuesDesired data from second worksheet #N/A if the lookup value is not found #REF! if the value in Col_Index_Num is greater than the number of columns selected in Table Array So you can get three “results” from a Vlookup: The desired data moved into the main spreadsheet #N/A if the data was not found, and, #REF! if the value in the in the return column is not within the listed Table Array

48 Nested Functions

49 Nested Functions: When do you need?You need to combine multiple “functions” to produce the value you need Any time you have additional conditions which you need to test Patti: Use nested function if you need combine more steps than offered in any single function.

50 Nested Functions: Example usesYou need to know who worked on Saturday and Sunday during a given pay cycle To create a cell that contains all lines of a mailing address but only want to show Street2 if there is a value in it and display the leading zeroes in Zip Codes Patti: For examples, we’ve picked one simple nested function and another one that is a bit more complex that will allow you to learn the approach to using more complex nested functions. Ready?! Read examples from the slide.

51 Nested Function #1: Worked Sat or SunFormulas used: IF, ISBLANK, WEEKDAY Column E is date worked from a timesheet Cell E1 contains the value 4/5/2014 In column F1, enter the formula IF(ISBLANK(E2),"Blank", IF(WEEKDAY(E2)=1,"Sunday”,IF(WEEKDAY(E2)=7, "Saturday","Mon-Fri"))) Copy formula to all subsequent rows in Column F Patti: So – let’s look at the first sample. Read from slide; note for each open paren., you need a close paren. -- If the date cell E2 is blank, enter the word blank or, if E2 is isn’t blank, then check for the day of the week (or WEEKDAY) and tell the system what day of the week is the first day of the week – in this case, Sunday is the first day; if it isn’t Sunday, check to see if it is Saturday; if it isn’t Saturday either, enter Mon-Fri. It is important to note that we considered that if it isn’t Sun or Sat, then it must be Mon-Fri. It is important to consider the most efficient way to set up this string….we don’t need to go from Sunday through Monday to Tuesday, etc., the shortest is if it isn’t sat or sun, then it has to be Mon-Fri.

52 Nested Function #1: Sample resultAnd here is the resulting spreadsheet. You can see the nested function at the top of the tool bar. So – if the date field is blank and if the weekday is equal one, enter Sunday, OR if the weekday is equal to 7, enter Saturday, otherwise enter Monday through Friday.

53 Nested Functions #2: ConcatenateColumn A: Street1, Column B: Street2, Column C: City, Column D: State, Column E: Zip Code Column E a numeric field maintaining leading zeroes Values in the columns A1:E1 are: 123 Main St, blank, Newark, NJ, 7021 In column F1, enter the statement =CONCATENATE(A1,IF(B1=" ",", ",CONCATENATE(", ”,B1,", ")),C1,", ”,D1," ",TEXT(E1,"00000")) Result: 123 Main St, Newark, NJ 07021 Copy the formula to all subsequent rows, Column F Rita: Example #2 is concatenate. Big word – simple meaning: 1. link separate items together – specifically character strings. How often have you looked at data and it isn’t pulled together in a way that is “useable” to you. In this example, we’ve taken multiple address fields and linked them – concatenated them – into one field. We’ve also taken the liberty of adding a couple of “extras” to give you an idea of how to increase your control over the outcome of some of the data. Read from slide Extra credit! #1: note that the second address line is blank; while this field could be used to include a floor, suite or apartment number, the information is not always necessary. In our example, we do not show a second address line and don’t need/want it to show up as a blank. Extra credit! #2: you have likely recognized that leading zeros, such as the leading zero in the Newark, NJ, Zip Code do not show in Excel. In this example, however, they are an important part of a mailing address. In order to do this, we add a NEW function: TEXT, indicating that the field must contain five digits, represented by the “00000”. And Voila! We now have a five digit zip code!!

54 Nested Functions #2: Sample resultyou have eliminated blank lines and made sure leading zeros are in the zip code!

55 Questions Patti

56 You can download this presentation at: http://www.rcm-hrs.comRCM contact information David Peña Vice President office Hrair Tcholakian Senior Director Hrair office You can download this presentation at:

57 DON’T FORGET TO COMPLETE THE SESSION EVALUATION ON THE ADP MOTM CONFERENCE APP.Thank You Session #: 510 Excel 201: Elevate Your Skills to the Next Level

58 Don’t forget! Download your Continuing Education Certificates from the Conference App!View, download, and your CE certificate right from the ADP MOTM Conference App. Select ‘CE Certificates’ from the App’s Main Menu. Available CE Certificates will appear after your session attendance. them to yourself straight from the app! Don’t have a smartphone? Use the Web App instead – Certificates will be available for two (2) months after the conference.