January 25, 2021 - by Bill Jelen
Challenge: The NETWORKDAYS and WORKDAY functions can take a list of company holidays as the third argument. If you store the list of holidays in AZ1: AZ10, there is a chance that someone will inadvertently delete a row, so you want to move the range of company holidays to a named range.
Solution: There is an easy way to convert the range of holidays to a named range. Follow these steps:
Calculate Workdays for 5, 6, and 7 Day Workweeks »
January 22, 2021 - by Bill Jelen
Calculate how many workdays fall between two dates. Excel’s NETWORKDAYS function does this if you happen to work the five days between Monday and Friday inclusive. This topic will show you how to perform the calculation for a company that works 5, 6, or 7 days a week.
Find the First Non-blank Value in a Row »
January 20, 2021 - by Bill Jelen
You want to build a formula to return the first non-blank cell in a row. Perhaps columns B:K reflect data at various points in time. Due to the sampling methodology, certain items are checked infrequently.
Excel Custom Functions Straight to the Point »
Excel Custom Functions can be used like any other native functions or user defined functions in Excel. This book shows the process of creating Excel Custom Functions.
Microsoft Excel 2019 Pivot Table Data Crunching »
Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.
Excel Data Cleansing Straight To The Point »
This Straight to the Point guide provides an introduction to data cleansing, which also goes by names such as data munging and data wrangling. Whatever the name, it basically means doing what needs to be done to make data useful and trustworthy.
MrExcel 2020 - Seeing Excel Clearly »
This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.
Microsoft Excel 2019 Inside Out »
Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.
MrExcel LX – The Holy Grail of Excel Tips »
A book for people who use Excel 40+ hours per week. Illustrated in full color.