Excel University Volume 2 Now Available!

I am pleased to announce the immediate availability of Excel University Volume 2. The next installment in the series covers a task common to accounting and finance professionals: reporting. Since there are many different types of reports in Excel, let’s dig into the details a bit.

This book focuses on how to build hands-free formula-based reports. Let’s unpack that statement. Firstly, a hands-free report is a report whose amount values update automatically when new data is pasted into the workbook, such as updated trial balance, or an updated export from an accounting system. A hands-free report will immediately pull the updated data into the report so that the amount values are updated.

A formula-based report is a report that is built with formulas, rather than a feature of Excel, such as the PivotTable feature. Since it is built with formulas, there are essentially no structure or format limitations, such as those imposed by the PivotTable feature.

Contents

Building on the content covered in the first volume, this book opens with multiple condition summing and how to use the remove duplicates feature to create a unique list of report labels.

Then, we move into lookups, and cover, in great detail, the cornerstone of Excel’s lookup functions, VLOOKUP. We discuss its strengths, and in all fairness, its limitations. Then, we walk through several methods for how to improve the VLOOKUP function, and ultimately, how to move beyond it altogether. We discuss the benefits of the INDEX/MATCH combo, and how it allows us to return values that are left of the lookup column.

We then work through error trapping, and how to use lookup functions to perform list comparisons. We talk about performing multicolumn list comparisons, and how the SUMIFS function makes a wonderful lookup function. We talk about date and text functions, and how to determine the last day of the month.

After working through the individual Excel skills, we bring them together in the final section of the book. We discuss the importance of building dynamic report headers, how to set up horizontal reports, the critical idea of mapping tables, how to integrate data validation into digital reports, and how to improve error checking with Boolean values and the logical AND function. The final chapter illustrates a reporting workbook, and uses 10+ independent Excel features, functions and techniques covered previously. This is one of the benefits of the progressive nature of the Excel University series, that the content builds and enables us to use many features together at once in the Excel files.

For a full Table of Contents, check out the book page.

Availability

Today, it is available in paperback through Amazon and digitally in iTunes. The other variations, including the Kindle version, will be processed shortly and available within the next few weeks.

I am very satisfied with the content and the way that the book flows. I love how this volume turned out…and I hope you love it too. Most importantly, I hope it helps you get your job done more quickly.