While the role of today's total rewards professionals has become more strategic and business-oriented, some fundamentals of the job will always hold true. One such fundamental is math. Focusing on how to use Excel for compensation work, Excel guru and WorldatWork faculty member, Dianne Auld continues to pack this latest edition full with powerful tools so you can tackle even your biggest data projects. This book includes the ever-useful compensation and benefits formulas presented in previous editions, PLUS discover how to put dynamic arrays and power query to work! This new edition reflects the most current versions of software: Excel 2019 and Microsoft 365. You'll gain tips, tricks and formulas so Excel works for you, employing ASAP utilities, array formulas, power queries, etc. when building charts, regression analyses, pivot tables and more. This book is a must-have, go-to resource for every compensation and benefits professional. Now with Power Query and Dynamic Arrays! Gain tips, tricks and formulas so Excel does the math for you when building charts, regression analyses, pivot tables and more. Reflects current versions of Excel 2019 and Microsoft 265 including Power Query and Dynamic Arrays.
Author(s): Power Query; Dynamic Arrays
Publisher: WorldatWork
Year: 2020
Language: English
Commentary: Excel Tips and formulas for Excel 2019 and Microsoft 365.
Pages: 606
Tags: Excel Tips and formulas for Excel 2019 and Microsoft 365.
CONTENTS
EXCEL TIPS
TIP 1 Customizing the Quick Access Toolbar and Other Efficiency Tips
CONVERTING DATA
TIP 2 Converting Data from One to Two or More Columns
TIP 3 Converting Data from Two or More Columns to One Column
CONVERTING TEXT
TIP 4 Converting Text to Numbers
TIP 5 Converting Text Dates to Formatted Dates
WORKING WITH DATES
TIP 6 Formatting Dates
TIP 7 Calculating Age and Length of Employment
TIP 8 Calculating Working Days Between Dates
TIP 9 Using LEFT, RIGHT and MID Formulas
TIP 10 Using LEN and TRIM Formulas
TIP 11 Using Initial Caps, Lowercase, Uppercase Formulas
TIP 12 Using DOLLAR and TEXT Formulas
TIP 13 Using Rounding Formulas
TIP 14 Using the VLOOKUP Formula
TIP 15 Using the HLOOKUP Formula
TIP 16 Naming Ranges
AVOIDING COLUMN COUNT ERRORS IN LOOKUP FORMULAS
TIP 17 Using the COLUMNS Formula with a VLOOKUP Formula
TIP 18 Using the LOOKUP Formula
TIP 19 Using the INDEX and MATCH formulas
TIP 20 Using the OFFSET formula
TIP 21 Using the INDIRECT and SUBSTITUTE Formulas
TIP 22 Using the CHOOSE Formula
TIP 23 Using IF and Nested IF
TIP 24 Using IF(AND), IF(OR), IFS and SWITCH
TIP 25 Using IFERROR and IFNA
TIP 26 Using COUNTIF and COUNTIFS
TIP 27 Using SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS, MINIFS AND MAXIFS
STATISTICS
TIP 28 Using COUNT, COUNTA, COUNTBLANK
TIP 29 Measuring Central Tendency/Averages
TIP 30 Using SUMPRODUCT to Calculate a Weighted Average
TIP 31 Using Measures of Location
TIP 32 Calculating Measures of Variance
TIP 33 Calculating Z-Scores, and 2 and 3 Sigma Boundaries
TIP 34 Determining Skewness and Kurtosis
TIP 35 Descriptive Statistics
TIP 36 Calculating Correlations
TIP 37 Using the Histogram Function to Create a Distribution Table and Chart
TIP 38 Creating a Distribution Table Using an Array Frequency Formula
PIVOT TABLES
TIP 39 Creating and Changing the Layout of a Pivot Table
TIP 40 Sorting Data, Selecting Specific Fields
TIP 41 Using the Filters Box and Show Report Filter Pages Function
TIP 42 Using Slicers in Pivot Tables
TIP 43 Using Value Field Settings Options
TIP 44 Refreshing Data, Changing Data Source and Grouping Data
TIP 45 Showing Source Data in a Pivot Table, Creating Pivot Charts
TIP 46 Using the Data Filter
TIP 47 Using Number and Text Data Filters and Search
TIP 48 Extracting Unique Records
CONDITIONAL FORMATTING
TIP 49 Conditional Formatting Using Rules
TIP 50 Conditional Formatting Using Color Scales, Data Bars and Icon Sets
TIP 51 Using Conditional Formatting Based on Formulas
CHECKING DATA
TIP 52 Using Formula Auditing
TIP 53 Checking Sets of Data Against Each Other
TIP 54 Setting Up Data Validation from a List
TIP 55 Setting Up Other Types of Data Validation
TIP 56 Setting Up Data Protection
SUBTOTALS AND SORTING
TIP 57 Using the Sort and Custom Sort Functions
TIP 58 Using Subtotals
TIP 59 Using Nested Subtotals
TIP 60 Creating Different Statistics in One Subtotal Table
REGRESSION ANALYSIS
TIP 61 Linear Regression Using a Scattergram
TIP 62 Linear Regression Using Multiple Formulas
TIP 63 Linear Regression Using Add-In Regression Function
TIP 64 Exponential Regression Using a Scattergram
TIP 65 Adding Market Data, Pay Ranges to a Scattergram
TIP 66 Multi-Linear Regression
TIP 67 Pay Discrimination Testing Using Multiple Regression Residuals
CHARTS
TIP 68 Creating Column and Bar Charts
TIP 69 Creating a Comparative Column and Line Chart
TIP 70 Creating a Pie Chart
TIP 71 Creating a Pay Range Stacked Column Chart of Pay Ranges
TIP 72 Pay Range Values in Pay Range Stacked Column Chart
TIP 73 Creating a Combined Scatter Chart and Pay Range Stacked Column Chart
TIP 74 Creating Pay Range Charts Using Stock Charts
TIP 75 Creating a Dual-Axis Graph
TIP 76 Creating a Range Penetration Graph
TIP 77 How to create a Waterfall Chart Using Office 365
TIP 78 Editing Charts
WHAT-IF FUNCTIONS
TIP 79 Using Goal Seek
TIP 80 Using Solver
TIP 81 Using Scenario Manager
TIP 82 Using Data Table
DATA CONSOLIDATE
TIP 83 Consolidating Data Using 3-D Reference Formulas
TIP 84 Consolidating Data by Position Using Data Consolidate
TIP 85 Consolidating Data by Category Using Data Consolidate
ARRAY FORMULAS
TIP 86 Introduction to Array Formulas
TIP 87 Single-Cell Array Formulas
TIP 88 Multi-Cell Array Formulas
TIP 89 Z-scores, COUNTIF, SUMIF, AVERAGEIF Array Formulas
TIP 90 Using Array Formulas to Calculate Minimums, Maximums, Medians, Quartiles and Percentiles
FINANCIAL FUNCTIONS
TIP 91 Future Value (FV) Calculation
TIP 92 Present Value Calculation (PV)
TIP 93 RATE, NPER and PMT
TIP 94 Application to Salaries and Pay Ranges
TIP 95 Straight-Line and Declining-Balance Depreciation
TIP 96 Double-Declining Balance and Sum-of-the-Years-Digits Depreciation
MACROS
TIP 97 Recording Macros
TIP 98 Running Macros
TIP 99 Editing and Deleting Macros and Changing Macro Security Settings
FORM CONTROLS
TIP 100 Form Controls: Introduction and Macro Button
TIP 101 Form Controls: Combo Box
TIP 102 Form Controls: Option Button and Group Box
TIP 103 Form Controls: Check Box
TIP 104 Form Controls: List Box and Spin Button
TIP 105 Form Controls: Label and Scroll Bar
PASTE OPTIONS
TIP 106 Paste Values, Format Painter Brush, Paste Special: Transpose
TIP 107 Paste Special: Operation Functions
CONTROL KEY SHORTCUTS
TIP 108 Control Key Shortcuts A to K
TIP 109 Control Key Shortcuts L to Z
FUNCTION KEY SHORTCUTS
TIP 110 Function Key Shortcuts F1 to F6
TIP 111 Function Key Shortcuts F7 to F12
ASAP UTILITIES FUNCTIONS
TIP 112 ASAP Utilities: Introduction, Favorites & Shortcut Keys, Vision Control and Select Tools
TIP 113 ASAP Utilities: Sheets Tools
TIP 114 ASAP Utilities: Further Sheets, Import and Columns & Rows tools
TIP 115 ASAP Utilities: Further Sheets and Range Tools
TIP 116 ASAP Utilities: Fill Tools
TIP 117 ASAP Utilities: Columns & Rows, Objects and Comments Tools
TIP 118 ASAP Utilities: Format and Numbers & Dates Tools
TIP 119 ASAP Utilities: Text tools
TIP 120 ASAP Utilities: Formulas, Information, System and Export tools
DATA TYPES (OFFICE 365)
TIP 121 Geography and Stocks Data Types
POWER QUERY / GET & TRANSFORM DATA
TIP 122 Introduction to Power Query / Get & Transform Data
TIP 123 Getting Data from an Excel, Text or CSV File
TIP 124 Getting, Transforming and Loading Data from the Web
TIP 125 Getting, Transforming and Loading Data From a Folder or Multiple Excel Sheets
TIP 126 Power Query Editor Home Menu
TIP 127 Power Query Editor Transform Menu
TIP 128 Power Query Editor Add Column Menu
TIP 129 Duplicating, Referencing, Appending and Merging Queries
TIP 130 Working with the Advanced Editor and M Language / M Code
XLOOKUP AND XMATCH FORMULAS
TIP 131 Using the XLOOKUP Formula
TIP 132 Using the XMATCH Formula
DYNAMIC ARRAYS
TIP 133 Introduction to Dynamic Arrays
TIP 134 Using the UNIQUE, SORT and SORTBY Dynamic Array Formulas
TIP 135 Using the FILTER and SEQUENCE Dynamic Array Formulas
