Excel Agility - Payroll Analysis Techniques
Webinar Details $219
- Webinar Date: May 10, 2024
- Webinar Time: 12:00pm - 1:40pm EDT live
- Webinar Length: 100 Minutes
- Guest Speaker: David Ringstrom
- Topic: Business Administration, Business Skills, Finance, Software, Taxation and Accounting
- Credit: CPE 2.0, ATAPR 1.5
-
-
In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide participants through various payroll-related Excel techniques. Topics covered include contrasting using Flash Fill versus the TEXT function to reformat Social Security Numbers. You'll see how to calculate total payroll and total payroll taxes with the SUMPRODUCT function for data analysis, and understand the nuance of adding up time values in Excel. David will also show how to calculate employee tenure with the DATEDIF function, optimize work schedules with the NETWORKDAYS.INTL function, and applying heat mapping techniques to salary data. He'll also contrast using VLOOKUP in any version of Excel versus XLOOKUP in Excel 2021 and Excel for Microsoft 365 for looking up data from lists. Attendees will gain valuable insights and skills to enhance their Excel proficiency and efficiency.
David is the author of “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.
Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.
Who should attend:
Professionals seeking to use Microsoft Excel more effectively.
- Utilizing the RANDBETWEEN worksheet function to create a series of random numbers.
- Gleaning the nuances of adding time values together in Microsoft Excel.
- Improving the integrity of Excel PivotTables with the Table feature.
- Discovering four different ways to remove data from a PivotTable report.
- Adding rows to a blank PivotTable to create instant reports.
- Using Flash Fill to quickly insert dashes into a column of Social Security or telephone numbers.
- Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting.
- Preventing errors from the start by choosing from thousands of free Excel spreadsheet templates.
- Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts.
- Removing Conditional Formatting when it’s no longer needed within a spreadsheet.
- Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates.
- Determining how to calculate the last day of the current month, as well as future or prior months, with the EOMONTH function.
- State which XLOOKUP argument causes an alternate value to be displayed in lieu of #N/A.
- Recall the print scale threshold below which a print-out typically becomes difficult to read.
- Name the menu tab that contains the Remove Duplicates feature
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.
