Would you like to:
- Create your new reports, analyses, forecasts, and other Excel work quickly?
- Update your Excel work with one command, without using VBA?
- Slash the errors in your new Excel work, and then ratchet any remaining errors down to zero?
That is, would you like to be highly productive in Excel?
No problem! It’s all a matter of using good Excel plumbing.
With good plumbing, water flows from distant sources, through water pipes, to your water glass—almost instantly.
With good plumbing, data flows from distant sources, through data pipes, to your reports, analyses, forecasts, and models—almost instantly.
Here’s a typical plumbing diagram for your Excel work:
Your Source Data could be just about anywhere: In your company’s data warehouse. On the Web. In text or CSV files. In Excel. Etc. For example, the figure below shows data from the Federal Reserve Economic Database (FRED), which Power Query downloads from online text files.
Power Query (PQ) was the name of an add-in for Excel 2010 and 2013, one that imported data to Excel, while cleaning and shaping it. Beginning in Excel 2016, PQ’s features were included in the Data tab of Excel’s Ribbon, in its Get & Transform and its Queries & Connections groups. But most Excel users still call that bundle of features Power Query.
Excel Tables are where Power Query sends your data. Using Tables allows us to enhance and use the data easily.
Excel Formulas can use most of Excel’s worksheet functions to summarize and transform your data. Common functions include SUMIFS, INDEX, MATCH, and so on. Often we use those formulas in staging tables, where we summarize the data in various ways to make our presentations easier to create and manage.
Reports, Plans, Dashboards, Analyses, Etc. are supported by formulas that typically are linked to Excel Tables.
Therefore, when Power Query refreshes your Excel Tables—which you typically do with one command—your data flows automatically from the Tables, often through staging tables, to your Excel work.
Best Practices are the key to making your Excel plumbing work smoothly.
For years, I struggled with my Excel plumbing, looking for a logical and consistent way to flow my data to my reports, update them automatically, and modify them quickly.
Finally, in just the past several years, I worked out the best practices needed to create and update reports like my Economic Update report and training, which includes these four charts.
I built this report to update every weekday, with one command, in about seven seconds.
To learn more about the various ways you can use Excel databases in your Excel work, check out my article, What the Heck is an Excel Database?
