Microsoft 365 Excel: The Only App That Matters

Table of contents

1. Microsoft 365 Excel: The Only App That Matters
2. Dedications
3. About the Author
4. Acknowledgements
5. Introduction
6. Who This Book Is For
7. PC Versus Mac Excel
8. Following Along with Excel Files
9. Chapter 1: Why Excel 365?
10. The Worksheet Formula Calculation Engine
11. Array Formulas
12. New Array Functions: UNIQUE and FILTER
13. The New XLOOKUP and LET Functions
14. Power Query and the M Code Formula Language
15. Power Pivot, the Data Model, and DAX Formulas
16. Power BI Desktop for Creating Visualizations and Reports
17. Key Concepts in Chapter 1
18. Chapter 2: Why the Spreadsheet Was Invented
19. Key Concepts in Chapter 2
20. Chapter 3: What Does Excel Do?
21. Key Concepts in Chapter 3
22. Chapter 4: Key Concepts Used to Create Excel Solutions
23. Key Concepts in Chapter 4
24. Chapter 5: The Structure of Excel Files and Worksheets
25. The Two-Way Grid: Cells, Worksheets, and Workbooks
26. Worksheet Navigation
27. Excel Data and Default Alignment in Cells
28. The Excel Command Environment
29. The Quick Access Toolbar
30. The File Menu and Backstage View
31. File Management
32. Key Concepts in Chapter 5
33. Keyboard Shortcuts Learned in Chapter 5
34. Chapter 6: Keyboard Keys and Shortcuts
35. Keyboard Shortcuts
36. Key Concepts in Chapter 6
37. Keyboard Shortcuts Learned in Chapter 6
38. Chapter 7: Worksheet Efficiency Tricks
39. Using Worksheet Mouse Cursors
40. Entering Content into Cells with Five Different Keyboard Shortcuts
41. Selecting a Range of Cells
42. Jumping Around in a Worksheet
43. Key Concepts in Chapter 7
44. Chapter 8: Worksheet Formulas, Formatting, and Setup to Solve Problems
45. Creating a Sample Worksheet
46. Using the Proper Dataset Format
47. Adding Style Formatting
48. Entering the Data
49. Using Worksheet Formulas, Functions, Cell References, and Aggregate Operations
50. Checking Your Work and Verifying Your Formulas
51. Using Relative Cell References
52. Adding Number Formatting
53. Using the Format Painter
54. Inserting Rows into a Worksheet
55. Calculating Percentages
56. Understanding Absolute Cell References
57. Documenting Your Work
58. Spilling Dynamic Array Formulas and Array Operations
59. Using the Excel Table Feature
60. Sorting and Filtering
61. Setting Up a Page for Printing
62. Adding New Data to a Dynamic Solution by Using a Text File
63. Converting a Solution to a PDF File
64. Key Concepts in Chapter 8
65. Keyboard Shortcuts Learned in Chapter 8
66. Practice Problems for Chapter 8
67. Chapter 9: A Golden Rule for Building Excel Models
68. How Violating the Golden Rule Can Get You into Trouble
69. Benefits of Following the Golden Rule
70. Example 1: Commission Pay Worksheet Formula
71. Example 2: Monthly Expense Formula with Hard-Coded Formula Input
72. Example 3: COUNTIFS Worksheet Function to Count with a Condition
73. Example 4: UNIQUE Array Function and Data Validation List
74. Example 5: Conditional Formatting
75. What-If Analysis and Excel Models
76. Key Concepts in Chapter 9
77. Keyboard Shortcuts Learned in Chapter 9
78. Practice Problems for Chapter 9
79. Chapter 10: Knowing When to Use the ROUND Function
80. The ROUND Function Rule
81. Example 1: Using the ROUND Function to Get Accurate Totals for Tax Deduction Calculations
82. Example 2: Rounding to the Dollar
83. Example 3: Rounding for a Sales Invoice
84. Example 4: Rounding a Percentage
85. Key Concepts in Chapter 10
86. Practice Problems for Chapter 10
87. Chapter 11: Date and Time Number Formatting and Formulas
88. Date Number Formatting, Date Serial Numbers, and Date Formulas
89. The Format Cells Dialog Box and Custom Date Number Formatting
90. Calculating the Difference Between Two Dates
91. Counting Workdays with NETWORKDAYS.INTL
92. Adding Days to Dates and Subtracting Days from Dates
93. Time Number Formatting, Time Serial Numbers, and Time Formulas
94. Example 1: Calculating Hours Worked
95. Example 2: Calculating Hours Worked When There Is a Lunch Break
96. Example 3: Using the Time Number Format to Show Hours Greater Than 24 Hours
97. Example 4: Calculating Hours Worked for a Night Shift
98. Example 5: Rounding a Time Value to Within 5 Minutes with the MROUND Function
99. Keyboard Shortcuts and Custom Date and Time Number Formatting
100. Key Concepts in Chapter 11
101. Keyboard Shortcuts Learned in Chapter 11
102. Practice Problems for Chapter 11
103. Chapter 12: Worksheet Formula Types and Formula Elements
104. Worksheet Formula Types
105. Worksheet Formula Elements
106. Order of Operator Precedence in Worksheet Formulas
107. My Golden Rule, the ROUND Function, and Number Formatting As a Façade
108. Example 1: Number Formula for Average Customer Ratings
109. Example 2: Using a Text Formula to Join First and Last Names in a Cell
110. Example 3: Using a Logical Formula to Determine Whether Accounts Are in Balance
111. Examples 4 Through 6: Using a Cost of Goods Sold Formula with a Spilled Array, Relative Cell References, or Table Formula Nomenclature
112. Example 7: Dynamically Extracting the Top N Values by Using the Comma Reference Operator
113. Example 8: Adding the Top Three Boomerang MTA Scores and Ranking with the RANK.EQ Worksheet Function
114. Example 9: Looking Up the Price for Invoicing by Using the XLOOKUP Worksheet Function
115. Example 10: Looking Up All the Rows in a Column by Using the XLOOKUP Function
116. Example 11: Looking Up a List of Student Classes by Using the FILTER Function
117. Example 12: Using the SUMIFS Worksheet Function to Create a Sales and Costs Report by Product
118. Example 13: Conducting a Customer Credit Analysis with the AND Function and Mixed Cell References
119. Example 14: Knowing When Mixed Cell References Are Required
120. Example 15: Conditionally Formatting a Row with a Logical Formula and Mixed Cell References
121. Example 16: Extracting Records from an Excel Table with the FILTER Function and Defined Names
122. Example 17: Conditional Formatting for Dynamic Spilled Array Formulas
123. Example 18: Building a Stock Value Table with Data Types and the Dot Operator for a Stock Data Type
124. Example 19: Building Formulas Between Worksheets by Using Worksheet References
125. Examples 20 and 21: Building Formulas Between Workbooks by Using Workbook References
126. Examples 22 and 23: The Space Operator for Intersecting Values and the Better Alternative, XLOOKUP
127. Example 24: Using the Colon Reference Operator and 3D Cell References to Add Across Multiple Worksheets
128. Example 25: Using Table Formula Nomenclature Absolute and Mixed References
129. Key Concepts in Chapter 12
130. Keyboard Shortcuts Learned in Chapter 12
131. Practice Problems for Chapter 12
132. Chapter 13: Logical Tests and Related Functions (AND, ISNUMBER, IF, IFS, FILTER, SUMIFS, and More)
133. Comparison Operators
134. Logical Tests
135. Example 1: Creating a Logical Test with a Single Condition
136. Example 2: Using Math Operations to Convert TRUE to 1 and FALSE to 0
137. Example 3: Using IS Worksheet Functions
138. Types of Logical Tests: Single Condition, Contains, NOT, AND, BETWEEN, and OR
139. Logical Functions That Deliver a Single Scalar Boolean Value: AND, OR, and NOT
140. Example 4: Using an AND Function to Run an AND Logical Test
141. Example 5: Using an OR Function to Run an OR Logical Test
142. Example 6: Creating an AND Boolean Math Formula to Run an AND Logical Test
143. Example 7: Creating an OR Boolean Math Formula to Run an OR logical Test
144. Example 8: Nesting the OR Function Inside the AND Function to Run a Complex Logical Test
145. Example 9: Using a None Are True Logical Test or a NOT NOT Logical Test
146. Example 10: Using a BETWEEN Logical Test
147. Example 11: Conditionally Formatting a Row with an AND Logical Test and Mixed Cell References
148. Example 12: Creating a Test Where Any Nonzero Number Is Considered a TRUE Value
149. Example 13: Using the SEARCH and ISNUMBER Functions in a Contains Logical Test
150. Example 14: Using Formula Wildcards to Create a Contains Logical Test Inside the COUNTIFS Worksheet Function
151. Logical Worksheet Functions That Deliver Values: IF, IFS, IFNA, IFERROR, and FILTER
152. Example 15: Using the IF Function to Deliver One of Two Number Values: 750 or 0
153. Example 16: Using the IF Function to Deliver One of Two Text Values: Over or Under
154. Examples 17 and 18: Using the IF Function to Deliver a Formula or Show Nothing
155. Examples 19 and 20: Using the AND and OR Functions Inside the IF Function
156. Example 21: Creating an AND Logical Test That Can Spill Results
157. Example 22: Using the IFS Function When You Have Three or More Things to Put in a Cell
158. Examples 23 and 24: Using the IFNA Function to Replace the #N/A Error with Something Different
159. Examples 25: Using the IF Function Instead of IFERROR
160. Examples 26: Using the IF Function Inside an Aggregate Function to Make a Conditional Calculation
161. Examples 27: Putting a Boolean AND Logical Test Inside the FILTER Function to Filter a Dataset
162. Examples 28: Putting a Boolean OR Logical Test Inside the FILTER Function to Filter a Dataset
163. Example 29: Comparing Two Lists and Extracting Results Using XMATCH and Other Functions
164. Using IFS Aggregate Functions (COUNTIFS, SUMIFS, AVERAGEIFS, MINIFS, and MAXIFS) to Calculate Based on an AND Logical Test
165. Example 30: Using IFS Aggregate Functions to Make Calculations with Three Criteria
166. Example 31: Taking a Closer Look at Using the SUMIFS Function to Run an AND Logical Test with Three Criteria
167. Example 32: Using the SUMIFS Function with Three Criteria in a Cross-Tabulated Report
168. Example 33: Using a BETWEEN Logical Test with SUMIFS and MAXIFS in a Monthly Sales Report
169. Example 34: Using the SUMIFS Function to Do an OR Logical Test on a Single Column
170. Example 35: Carrying Out Aggregate Operations with an OR Logical Test on a Single Column
171. Example 36: Conducting Aggregate Operations with an AND and OR Logical Test
172. Example 37: Conducting Aggregate Operations with an OR Logical Test on Two Columns
173. Example 38: Using Database Worksheet Functions
174. Key Concepts in Chapter 13
175. Practice Problems for Chapter 13
176. Chapter 14: Worksheet Lookup Functions and Formulas
177. Exact Match Versus Exact Match or Next Smaller Item Lookups
178. The XLOOKUP Function
179. Example 1: Using the XLOOKUP Function to Look Up Product Price Based on an Exact Match Lookup and Data Validation List
180. Example 2: Using the XLOOKUP Function to Look Up Tax Rate Based on an Exact Match or Next Smaller Item Lookup
181. Example 3: Using the XLOOKUP Function for an Exact Match or to Find the Next Biggest Value
182. Example 4: Using the XLOOKUP Function to Do a Wildcard Lookup
183. Example 5: Using the XLOOKUP Function to Get the First Item When There Are Duplicates
184. Example 6: Using the XLOOKUP Function to Get the Last Item When There Are Duplicates
185. Example 7: Using the XLOOKUP Function to Look Up All the Rows in a Column
186. Example 8: Using the XLOOKUP Function to Look Up a Record (All Columns in a Row)
187. Examples 9 and 10: Using the XLOOKUP Function to Do a Two-Way Lookup
188. Example 11: Using the XLOOKUP Function with Two Lookup Values
189. Example 12: Using the XLOOKUP Function to Do a Lookup Left
190. Example 13: Using the XLOOKUP Function to Perform a Vertical or Horizontal Grade Lookup
191. Example 14: Using the XLOOKUP Function with a Lookup Cell Reference
192. Example 15: Using the XLOOKUP Function to Look Up a Picture
193. Example 16: Using the XMATCH Function to Deliver the Relative Position of an Item in a List
194. Example 17: Using the XMATCH Function to Add Conditional Formatting When Comparing Two Lists
195. The INDEX Function
196. Example 18: Looking Up Random Data with the INDEX and RANDBETWEEN Functions
197. Example 19: Performing a Lookup and Rearranging Columns with the INDEX, SEQUENCE, ROWS, and XMATCH Functions
198. The FILTER Array Function
199. Example 20: Using the FILTER Function with One Lookup Value to Return Multiple Records
200. Example 21: Using the FILTER Function with Two Lookup Values to Return Multiple Records
201. LOOKUP: The Original Lookup Function for Spreadsheets
202. Example 22: Using the LOOKUP Function to Do Half Vertical/Half Horizontal Lookups
203. Example 23: Using LOOKUP to Create Approximate Match Lookup Formulas More Quickly Than with XLOOKUP
204. Example 24: Determining Whether to Use Multiple Lookup Tables and the SWITCH or FILTER Function
205. The SWITCH Function
206. Example 25: Using SWITCH with Lookup Formulas
207. Example 26: Combining Columns with the CHOOSE Function
208. Example 27: Using Data Types and the Dot Operator to Perform a Lookup
209. Key Concepts in Chapter 14
210. Keyboard Shortcuts Learned in Chapter 14
211. Practice Problems for Chapter 14
212. Chapter 15: Worksheet Array Formulas: Just Enter!
213. What Is a Worksheet Array?
214. Defining a Worksheet Array Formula
215. Other Important Array Formula Details
216. Array Operation Configurations, the Evaluation Process, and the Size of the Resultant Array
217. Examples 1 Through 3: Doing Financial Calculations Using the Direct Array Operation Column * Row * Table
218. Example 4: Using Array Formulas to Build More Compact Excel Solutions
219. Examples 5 and 6: Using the SUMPRODUCT Function to Multiply Same-Size Arrays and Add the Resultant Array
220. Examples 7 Through 12: Using the SUMPRODUCT Function for Weighted Average Test Scores
221. Examples 13 and 14: Using the MMULT Array Function for a Total Costs Formula
222. Example 15: Using the MMULT Array Function to Spill Row Totals for a Table
223. Example 16: Using MMULT to Find the Standard Deviation of Expected Portfolio Returns
224. Example 17: Using the SEQUENCE Array Function Inside MMULT to Get Dynamic Spilled Row Totals
225. Array Functions
226. Examples 18 Through 23: Using the FILTER Array Function with Different Logical Tests in the include Argument
227. Examples 24 Through 28: Using the UNIQUE Array Function in Different Ways
228. Examples 29 Through 33: Using the SORT Array Function in Different Ways
229. Example 34: Using the SORTBY Array Function
230. Sorting Mixed Data
231. Example 35: Finding the Top Three Scores, Including Ties, with the FILTER, LARGE, and SORT Functions
232. Examples 36 Through 39: Using the SEQUENCE Array Function
233. Frequency Distributions
234. Example 40: Using SORT, UNIQUE, and COUNTIFS to Create a Qualitative Frequency Distribution
235. Example 41: Using the FREQUENCY Array Function to Create a Quantitative Frequency Distribution
236. Example 42: Removing the Last Category by Using the INDEX, SEQUENCE, and ROWS Functions
237. Example 43: Using FREQUENCY in Formulas to Create Upper Limits
238. Example 44: Using Formulas to Create Logical Labels for Counting Categories
239. Example 45: Using the COUNTIFS Function to Create Custom Counting Categories for a Quantitative Frequency Distribution
240. Key Concepts in Chapter 15
241. Practice Problems for Chapter 15
242. Chapter 16: The LET and LAMBDA Functions
243. The LET Worksheet Function
244. Examples 1 Through 3: Using the LET Worksheet Function When There Are Repeating Formula Elements
245. Examples 4 Through 7: Using the LET Worksheet Function to Create Single-Cell Reports
246. The LAMBDA Function
247. Examples 8 and 9: Using the LAMBDA Worksheet Function to Create Reusable Functions for Common Calculations
248. Examples 10 and 11: Using the LAMBDA Worksheet Function to Simplify Complex Formula Reports
249. New LAMBDA Helper Functions That Allow You to Use a LAMBDA Function Value in the Worksheet
250. Example 12: Three Examples of LAMBDA Helper Functions
251. Key Concepts in Chapter 16
252. Practice Problems for Chapter 16
253. Chapter 17: Data Analysis Basics: Standard Pivot Tables, Sorting, Filtering, Visualizations, and More
254. The Standard PivotTable Tool
255. Example 1: Using a Standard PivotTable to Create a Product Sales and Costs Report
256. Example 2: Creating a Frequency Distribution from Customer Survey Data with the Standard PivotTable Tool
257. Example 3: Using the Sort Tool to Sort the Fastest Race Times to the Top for Each Track
258. Example 4: Sorting by Color
259. Example 5: Using the Filter Tool to Extract the Blue Quad Sales Records
260. Example 6: Filtering to Extract Student Records Based on AND and OR Logical Tests
261. More About the Fast and Easy Standard PivotTable Tool
262. Example 7: Changing the Functions in a Standard PivotTable by Using Summarize Values By
263. Example 8: Using the Standard PivotTable Group Feature to Create a Yearly Sales Report
264. Example 9: Filtering a Standard PivotTable Report Using a Slicer
265. Example 10: Creating Seven Standard PivotTable Reports with a Single Click
266. Example 11: Creating a Cross-Tabulated Report and Visualization Using a Standard PivotTable and PivotChart
267. Visualizing with Excel Charts
268. Example 12: Visualizing with a Clustered Column Chart or a Stacked Column Chart
269. Example 13: Visualizing with a Clustered Bar Chart or a Stacked Bar Chart
270. Example 14: Creating a Frequency Distribution and Histogram Chart with a PivotTable and a PivotChart
271. Example 15: Visualizing with a Line Chart to Show Changes and Trends over Time
272. Examples 16 and 17: Using an X-Y Scatter Chart to Visualize the Relationship Between Two Quantitative Variables
273. Example 18: Analyzing Data Using Artificial Intelligence to Create a Beautiful X-Y Scatter Chart
274. Example 19: Visualizing Tables of Data
275. Example 20: Using PivotTable Styles
276. Example 21: Using Conditional Formatting to Add a Heat Map or Color Scales to a Table Report
277. Example 22: Using Conditional Formatting for a PivotTable Report to Show the Top 5 Values
278. Example 23: Using Data Bars Conditional Formatting to Make an In-Cell Histogram
279. Examples 24 and 25: Using Worksheet Date Formulas to Create Date Attribute Fields
280. Cleaning and Transforming Data by Recognizing Patterns in the Data: Worksheet Formulas, Flash Fill, Power Query, or DAX?
281. Example 26: Using Worksheet Text Formulas to Clean and Transform Data and Create Text Attribute Fields
282. Example 27: Using Flash Fill to Perform One-Time Cleaning Tasks Where the Pattern Is Consistent
283. Example 28: Using Dashboards to Gauge Activity as New Data Arrives
284. Key Concepts in Chapter 17
285. Keyboard Shortcuts Learned in Chapter 17
286. Practice Problems for Chapter 17
287. Chapter 18: Advanced Data Analysis with Power Query, Power Pivot, Power BI, M Code, and DAX
288. Useful Information: Summary Reports, Visualizations, and Dashboards
289. Microsoft’s Data Analysis Tools
290. Important Data Analysis Definitions and Concepts
291. Data Modeling Basics
292. The Data Model Tool in Excel Power Pivot and Power BI Desktop
293. The Process of Turning Data into Information: The 10-Step Data Analysis Process
294. Data Analysis Examples
295. Example 1: Using Worksheet Formulas, a Standard PivotTable, and an Excel Chart to Create a Product Sales Report and Visualization
296. Example 2: Using the Excel App with Power Query, a Power Pivot Data Model PivotTable, and an Excel Chart to Create a Product Sales Report and Visualization
297. Comparing M Code, DAX Formulas, Worksheet Formulas, and Standard PivotTable Calculations
298. Example 3: Using the Power BI Desktop App to Create a Product Sales Report and Power BI Online for Sharing and Collaboration
299. Key Concepts in Chapter 18
300. Practice Problems for Chapter 18
301. Chapter 19: Data Analysis Examples: Big Data and Complex Data Analysis
302. Project 1: Converting 12 CSV Files with 35,000 Rows of Data into a Report with Four Key Metrics
303. Project 2: Importing SQL Big Data, Building DAX Formulas, and Creating an Interactive Power BI Desktop Report
304. Project 3: Combining Data from Multiple Excel Files and Creating a Summary Report—All with Power Query!
305. Project 4: Combining All Excel Table Data in the Current Workbook File into a Standard PivotTable Report
306. Project 5: Using Power Query M Code to Fix Inconsistent Datasets from Multiple Excel Files So They Can Be Combined
307. Key Concepts in Chapter 19
308. Keyboard Shortcuts Learned in Chapter 19
309. Practice Problems for Chapter 19
310. Chapter 20: The Macro Recorder and the VBA Editor
311. Using the Excel Workbook .xlsm File Extension to Allow VBA Code in an Excel File
312. Showing the Developer Tab in the Excel Ribbon
313. Using the Macro Recorder and a Form Control Button to Help a User Refresh a Query
314. Using the Macro Dialog Box and the VBA Editor Window to View VBA Code
315. Using the Macro Recorder and the Use Relative References Button to Write VBA Code That You Can Use for Repetitive Tasks
316. Using the Personal Macro Workbook to Create Universal VBA Macros
317. Copying VBA Code from the Internet and Pasting It into a VBA Module
318. Chapter 20 Key Concepts
319. Keyboard Shortcuts Learned in Chapter 20
320. Practice Problems for Chapter 20
321. Chapter 21: Financial Worksheet Functions
322. Financial Terms and Variables Used in Financial Cash Flow Analysis
323. The FV Function
324. PV Function
325. PMT Function
326. NPER Function
327. RATE Function
328. NPV Function
329. XNPV Function
330. Chapter 21 Key Concepts
331. Practice Problems for Chapter 21
332. Chapter 22: Simple Linear Regression Worksheet Functions
333. Simple Linear Regression Variables and Definitions
334. Excel Worksheet Functions for Simple Linear Regression
335. Chapter 22 Key Concepts
336. Practice Problems for Chapter 22
337. The End
338. Index