Excel Agility - Payroll Analysis Techniques
Webinar Details $219
- Rated:
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: ATAAA 1.5, ATATX 1.5, ATAOP 1.5, CPE 2.0
-
If you’re a payroll professional, you’ll appreciate learning about the many features and functions Excel offers that can improve the accuracy and efficiency of payroll production. Excel expert David Ringstrom, CPA, explains: mathematics for employee timesheets, date and time formatting and stamping, conditional formatting to highlight human resources (HR) requirements, password protection for sensitive payroll files and worksheets, salary information formatting, using pivot tables for HR reporting and analysis tasks, and more.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast.
Who Would Be Interested in This Course:
Payroll professionals who wish to learn practical Excel fundamentals to improve the accuracy and efficiency of payroll production.
Topics Covered:
- Improving the integrity of Excel pivot tables with the Table feature.
- Redacting portions of Social Security numbers by way of Excel’s TEXT worksheet function.
- Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting.
- Removing Conditional Formatting when it’s no longer needed within a spreadsheet.
- Using Excel’s Text to Columns feature to convert Social Security numbers to values and then apply Excel’s Social Security number format.
- Expanding reports horizontally by adding columns to pivot tables.
- Transforming a column of salaries into an instant heat map by way of Excel’s Conditional Formatting feature.
- Discerning the nuance involved in making pivot tables present data in tabular form.
- Exploring options for recovering lost passwords for Excel spreadsheets.
- Preventing users from drilling down into pivot table amounts.
- Discovering four different ways to remove data from a pivot table report.
- Adding fields to a blank pivot table to create instant reports.
Learning Objectives:
- Recall how to reformat Social Security Numbers using Excel’s Flash Fill feature.
- Identify how to recover lost passwords for Excel spreadsheets.
- Describe how to format salary information.
Level:
Intermediate
Format:
On-Demand Webcast
Instructional Method:
Self-Study
NASBA Field of Study:
Computer Software & Applications (2 hours)
Program Prerequisites:
Experience with Excel Spreadsheets
Advance Preparation:
None
Introduction
Excel Versions 00:00:30
Scrubbing Social Security Numbers 00:01:30
Flash Fill (Excel 2013+) 00:06:55
TEXT Function for Social Security Numbers 00:14:12
Introduction to SUMPRODUCT 00:22:26
Adding Time Values 00:26:06
Using DATEDIF to Calculate Tenure 00:30:01
NETWORKDAYS.INTL (Excel 2010+) 00:36:14
EOMONTH Function 00:40:24
Semi-Monthly Series of Dates 00:43:16
Calculating Preceding Friday 00:45:55
Semi-Monthly Payroll Tax Due Dates 00:48:53
Heat Mapping Salaries 00:52:47
Remove Conditional Formatting 00:56:39
Conditional Formatting - Top 10 00:58:10
Conditional Formatting - Equal To 01:01:43
Initiating a Pivot Table 01:06:00
Pivot Table Interfaces 01:09:54
Adding Fields to a Pivot Table 01:13:11
Pivot Table Drill-Down 01:14:39
Preventing Pivot Table Drill-Down 01:19:45
Introduction to RANDBETWEEEN 01:23:09
Choosing Random Sets of Employees 01:26:58
Managing Pivot Table Layouts 01:30:46
Remove Table Feature From Worksheet 01:33:25
Free Payroll Templates 01:35:00
Password Protect the Workbook 01:37:55
Recovering Lost Passwords 01:38:41
Presentation Closing 01:39:57
- CHOOSE Function 00:48:55
- Conditional Formatting 00:52:54, 00:56:59
- DATEDIF 00:30:04
- Drill Down 01:15:16, 01:19:51
- EOMONTH Function 00:40:26
- Field 01:13:23
- Flash Fill 00:07:13
- Heat Mapping 00:53:01
- IF Function 00:44:13, 0046:16
- NETWORKDAYS.INTL 00:36:17
- Pivot Table 01:06:04, 01:19:45
- RANDBETWEEEN 01:24:00
- Subtotal Feature 01:31:37
- SUM 00:26:26
- SUMPRODUCT 00:22:30
- Table Feature 01:07:11
- TEXT Function 00:14:26
- Text to Columns Wizard 00:03:04
- Top 10 Filter 00:59:30
- Total Row 01:15:30
- Workbook 01:38:00
CHOOSE Function : The CHOOSE function allows you to return a specified item from a list, but in certain cases, it also can be used to have VLOOKUP return data from the left of its criteria column.
Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.
DATEDIF: A worksheet function in Excel that works in any version of Excel but that mysteriously doesn't appear in Excel's online help documentation. DATEDIF has three arguments: Date1, Date2, and Interval. Keep in mind that DATEDIF does not count the starting period, so you may need to add 1 to its result.
Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.
EOMONTH: The Microsoft Excel EOMONTH function calculates the last day of the month after adding a specified number of months to a date. The result is returned as a serial date. The EOMONTH function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel.
Field: In a PivotTable or PivotChart, a category of data that is derived from a field in the source data. PivotTables have row, column, page, and data fields. PivotCharts have series, category, page, and data fields.
Flash Fill: Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note: Flash Fill is only available in Excel 2013 and later.
Heat Mapping: To create a heat map in Excel, simply use conditional formatting. A heat map is a graphical representation of data where individual values are represented as colors.
IF Function: Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. So an IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False.
NETWORKDAYS.INTL: The Microsoft Excel NETWORKDAYS.INTL function returns the number of work days between 2 dates, excluding weekends and holidays.The NETWORKDAYS.INTL function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the NETWORKDAYS.INTL function can be entered as part of a formula in a cell of a worksheet.
Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.
RANDBETWEEEN: The Microsoft Excel RANDBETWEEN function returns a random number that is between a bottom and top range. The RANDBETWEEN function returns a new random number each time your spreadsheet recalculates.
SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet
SUMPRODUCT: The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.
TEXT Function: The TEXT function enables you to convert a number in Excel to any number of text formats. For instance, the format code mmmm d, yyyy would transform the date 1/1/2018 into January 1, 2018.
Table Feature : The Table feature in Excel 2007 and later is an improvement on the List feature in Excel 2003 and earlier. The Table feature provides enhancements that make it much easier to analyze lists of data.
Top 10 Filter: Use the Top 10 filter feature in an Excel pivot table, to see the Top or Bottom Items, or find items that make up a specific Percent or items that total a set Sum. You can summarize your data by creating an Excel Pivot Table, and then use Value Filters to focus on the top 10, bottom 10 or a specific portion of the total values in your data.
Total Row: A Total row appears below the data where each column has access to several automatic formulas. The default selection for the Total Row is none, meaning no function is selected when you first turn on the Total Row on your Table.
Workbook: In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.
