Off to production

I'm delighted to announce that Excel Cookbook has entered production! It will be a little while before the book is generally available, but in the meantime, you can read the early release.

The early release now includes a draft version of every chapter. As well as the areas mentioned previously, there are now three more chapters covering the following areas:

• Using Power Pivot to create more advanced PivotTables. For example, you can create PivotTables based on multiple related tables, insert measures and KPIs, and define more flexible layouts by converting a PivotTable to cube formulas.

• Writing LET and LAMBDA formulas. The LAMBDA function is a relatively new addition to Excel 365, and it lets you create custom functions without having to write macros or VBA. The book shows how to use this function, as well as LAMBDA helper functions such as BYROW, MAP, REDUCE, and SCAN. It also includes LET, which helps make complex formulas more efficient and readable.

• Using Developer options. If you want to record macros or write VBA code, the book's final chapter shows you how. It also includes areas such as using Form and ActiveX controls and their differences.

You can read the early release (and the full version when available) on the O'Reilly learning platform, which also hosts my live training events. If you don't already have a subscription, you can try a 30-day free trial.

Excel Cookbook update

The Excel Cookbook's early release has recently had a major update. It now includes Chapters 1 to 15—just over 80% of the completed book!

The early release now includes the following areas (and more):

• General features, including AutoFill, Flash Fill, conditional formatting, and custom data validation.

• A wealth of functions and formulas covering text manipulation, dates and times, lookup functions, and dynamic array formulas.

• Dedicated chapters for financial analysis, statistics, and math and engineering.

• PivotTables, including using different aggregations, filters, adding custom calculations, and working with the PivotTable cache.

• Using charts and graphics, including sparklines and 3D Maps.

• Tools such as the Analysis ToolPak, Forecast Sheets, Scenario Manager, Goal Seek, and Solver.

• Getting and transforming data with Power Query, including custom columns and functions.

You can read the early release on the O'Reilly learning platform. The platform also hosts my live online training courses—see my training page for upcoming dates, with more courses on the way!

Adding a timestamp column using Power Query

During my most recent Foundations of Microsoft Excel course, someone asked me how they could copy data from one worksheet to another each day and automatically add a new column containing a timestamp. Since this is a daily routine, you can achieve this with Power Query as follows:

1. Select the data and choose Data => Get Data => From Other Sources => From Table/Range. If this option isn't available, choose Data => Get Date => From File => From Excel Workbook, follow the wizard's steps to select the data, then transform it.

2. In the Power Query Editor, choose Add Column => Custom Column.

3. In the Custom Column dialog box, type a name for the new column and type DateTime.LocalNow() in the custom column formula box, then click OK.

4. Choose Home => Close & Load to load the data to Excel.

5. In Excel, format the column as a date/time.

The data (including the timestamp) will update each time you refresh the query.

You'll be able to find these tips and more in the Excel Cookbook Power Query chapter.

Importing data from files in a folder

One of my favorite Power Query features is getting data from multiple files in a folder with the same schema and loading it to a single Excel table. You can even specify any files--or types of files--to exclude, and since it's Power Query, update your results by refreshing the data. This facility can be handy if, for example, you need to generate monthly reports by gathering similarly structured data from multiple departments or teams.

Here's how to use it:

1. Create a new Excel workbook and choose Data => Get Data => From File => From Folder.

2. Browse to the folder containing the files you want to load data from, then click Open; if you want to import data from multiple folders, select the parent folder.

3. Power Query displays a list of the folder's files. Click the Combine button's dropdown arrow and select one of the options. For example, to combine the data and load it straight into Excel, choose Combine & Load.

4. Select a sample file in the Combine Files dialog box, which Power Query will use as the basis for its queries. Then select which data to import, place a check in the "Skip files with errors" checkbox, and click OK.

When you click OK, Power Query creates a query (and some helper queries) that combines the data.

What if you want to exclude some of the files?

If you only want to combine some of the files in the folder, you can specify which ones to exclude as follows:

1. Launch the Power Query Editor and navigate to the main query created in Step 4 above.

2. Click the Source step in the Query Settings pane

3. The Editor displays a list of the folder's files, which you can now filter. For example, you can filter by file name, extension, folder path, or the last modified date.

4. When you've applied the filters, choose Home => Close & Load to load the data to Excel.

I include a demo of combining files from a folder in my Foundations of Excel course, and you'll be able to find these tips and more in the Excel Cookbook Power Query chapter.

Why learn Power Query?

Power Query is one of the most important tools available in Excel due to its power and versatility. You can use it to:

• Import data from various sources, including files, websites, and databases. For example, if you have many Excel files with the same structure in a single folder, you can use the From Folder wizard to combine the data from each one in a single Excel table.

• Transform data using the Power Query editor. Use it to remove duplicate rows, split columns by delimiter, extract codes, add extra columns, apply complex filters, and more.

• Automate repetitive data transformations without using macros or VBA. Power Query saves all the steps you take when transforming data, so if the underlying data changes, you can quickly rerun those steps at the click of a button.

• Load data to an Excel table, PivotTable, or PivotChart, and (optionally) add it to the data model.

This month, I've been putting the finishing touches to my new live online course: Mastering Power Query with MS Excel. It's filled with practical examples showing how to use Power Query and exercises for you to try techniques out for yourself. You can find out more on the training page, including how to register.

I've also started gathering recipes for the Excel Cookbook Power Query chapter. I hope to share some of these with you in separate articles.